Change Data Capture

Every database you run keeps a private, perfectly ordered diary: the transaction log. Postgres calls it the WAL, MySQL the binlog, SQL Server and Oracle the redo log. It exists because the database itself doesn’t trust memory — every commit is written to the diary first, so the engine can rebuild the truth after a crash.

Change Data Capture is the simple, slightly cheeky idea of reading that diary for our own purposes: turning every insert, update, and delete into an ordered stream of events that other systems can consume. Application developers meet the shape and think “event sourcing, roughly?” — close, except nobody had to redesign the application. The events were already being written. CDC is archaeology, not architecture astronautics.

Three ways to capture change (two of them badly)

Dual-write — the application writes to the database and publishes an event. It fails the first week: one write succeeds, the other times out, and now your systems politely disagree forever. There is no transaction that spans a database and a message broker, and pretending otherwise is how reconciliation scripts are born.

Query-based polling — SELECT * WHERE updated_at > :watermark on a schedule. Honest, simple, and blind in three specific ways: it never sees deletes (the row just vanishes), it misses intermediate states (placed→paid→cancelled between polls reads as placed→cancelled), and it trusts an updated_at column that every team swears is always set. It is a fine starting point and a poor destination.

Log-based CDC — read the transaction log itself. Every committed change, in commit order, including deletes, with no load on the tables and no cooperation required from the application. This is the one the principles below assume, and it’s what the well-known tooling in this space does under the hood — whichever badge it wears on your platform.

The principles, platform-agnostic

The pipeline looks like this everywhere — only the logos change:

Whatever you build on top, five principles hold:

1. Ordering is per key, not global. Changes to the same row arrive in order; changes to different rows may interleave. Design consumers around the key, and most ordering anxiety evaporates.
2. Delivery is at-least-once. Readers crash and resume from a checkpoint; the same event can arrive twice. Consumers must be idempotent — the same tax I described in the event-driven post, collected by a different department.
3. Deletes are first-class data. Half the point of CDC over polling. A delete event — a tombstone — carries the key of what died. Downstream systems that ignore tombstones slowly fill with ghosts.
4. Snapshot plus deltas equals truth. A change stream describes transitions; you also need one consistent initial snapshot to transition from. Every real CDC setup is a snapshot phase followed by a tailing phase, stitched at a known log position.
5. Schema will change mid-stream. Columns appear; types widen. Change events should carry (or reference) the schema they were born under, and consumers should treat unknown columns as arrivals, not errors.

Now point it at a data lake

Lakes have a personality clash with CDC: object storage is immutable and batch-shaped, while CDC is a continuous drip of tiny row-level mutations. You cannot update row 4,081 of a Parquet file; you can only write new files. Modern table formats paper over this with a metadata layer, but the architecture underneath is always the same two-table pattern:

Land the raw change events in an append-only log table — bronze, in medallion-speak. Never update it; just append. Then periodically MERGE those events into a current-state table — silver — keyed on the primary key, taking the latest change per key by log position, deleting on tombstones.

Here’s the whole idea, small enough to watch. Scroll:

The merge itself is one honest statement, whatever dialect your table format speaks:

merge into silver.orders as t
using (
  -- latest captured change per key, up to this run's high-water mark
  select * from bronze.orders_changes
  where lsn <= :high_water_mark
  qualify row_number() over (partition by order_id order by lsn desc) = 1
) as c
on t.order_id = c.order_id
when matched and c.op = 'DELETE' then delete
when matched then update set *
when not matched and c.op != 'DELETE' then insert *;

What this buys you — and what it costs

The two-table pattern earns its keep three ways. Replayability: silver is disposable — a bad merge, a new modelling idea, a backfill? Rebuild from bronze by replaying. Time travel: “what did this order look like on Tuesday?” is a query over the log, not a restore from backup. Audit: the log is the audit trail, ordered and complete, deletes included.

The costs are equally concrete. A drip of tiny change files creates the small-files problem, so compaction becomes a chore you own. Late and repeated events mean the merge must be keyed and ordered exactly (that qualify clause is load-bearing). And the freshness dial tempts everyone: merging every five minutes feels heroic until you see the bill, and hourly is usually what the dashboard actually needed. Have that conversation with the consumers before the platform decides by default.

The coaching note

When I introduce CDC to a team, I don’t start with a vendor comparison. I give them the reframe this whole post has been circling: the log is the truth; every table is just a view of it, materialised to some position. Then we look at their existing lake and ask which tables are secretly views, maintained by hand, of a log nobody kept.

Engineers who internalise that reframe stop asking “how do I sync these systems?” and start asking “where’s the log, and who’s behind on reading it?” — which is the right question in almost every integration conversation they’ll ever have. One idea, twenty years of mileage. That’s a good trade for a blog post.

---

Maureen the DBA, of about-page fame, explained redo logs to me in 2009 using a pub receipt. This post is that receipt, laundered through seventeen years of production incidents.