Change Data Capture
Reading committed changes out of the database's own transaction log, so downstream systems learn what happened instead of repeatedly asking what is true now.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
A row in orders changed at 03:14. How does a downstream system find out about that change — without interrogating the database on a loop, and without missing the change entirely?
Everything that needs to know a source system changed rather than what it currently holds: the raw landing zone that keeps history (The Raw Landing Zone), a search index that must reflect a rename within seconds (Keeping a Search Index in Sync), a cache invalidator, a fraud stream, and the analytical models that reconstruct order state from a sequence of changes (Event vs Snapshot Modeling).
One record in a CDC stream is one committed change to one row of one table. It is not an order and it is not an order's current state — three updates to one order produce three records and one order. Every downstream mistake in this module starts with someone counting change records and calling the result a business count (Grain: What Does One Row Represent?).
Have the application publish an event whenever it writes. The code already knows what changed, the event carries exactly the fields the consumer wants, and no extra infrastructure is involved. For a single service with one writer this is genuinely the simplest thing that works, and plenty of good systems run this way for years.
The write commits and the publish fails — or the publish succeeds and the transaction rolls back. There is no atomic way to write to a database and a broker in one step, so the two diverge under exactly the conditions you care about most (The Dual Write Problem).
- The write commits and the publish fails — or the publish succeeds and the transaction rolls back. There is no atomic way to write to a database and a broker in one step, so the two diverge under exactly the conditions you care about most (The Dual Write Problem).
- A second writer appears: a migration script, an admin console, a
psqlsession during an incident, a foreign-keyON DELETE CASCADE. None of them run the publish path, and none of their changes are ever seen downstream. - A bulk
UPDATE ... WHERE status = 'pending'touches two hundred thousand rows and emits nothing, because the emit was written per-request rather than per-row. - Deletes disappear entirely. The application publishes creates and updates because those are the interesting ones; nothing downstream ever learns that an order was removed, and the analytical table keeps counting it forever (Missing Rows).
- The publish is retried after a timeout that had actually succeeded, so the consumer sees the same change twice and has no way to tell that from a genuine second update (Duplicate Rows).
What is actually happening
- Every durable database already writes a sequential record of what changed before it changes the pages themselves — the write-ahead log in Postgres, the redo log plus the binary log in MySQL, the oplog in MongoDB. It exists so that a crash can be recovered from and so that replicas can be fed (Write-Ahead Logging, Crash Recovery).
- That log is, by construction, a complete, ordered, committed record of change. CDC is the observation that a stream you are already paying for is exactly the stream analytics wants, and that reading it is a replication problem rather than a query problem (Replication and Read Scaling, Replication Internals: WAL Shipping, LSNs, Lag and Failover).
- A log-based connector registers as a replication consumer, is handed a starting position, and reads forward. Postgres calls the durable position a replication slot; MySQL exposes binlog file and offset; Mongo hands out a resume token. In every case the position is a monotonic cursor into a retained log, and holding it is what makes the stream gapless.
- Only committed changes are published. Physical log records may include work from transactions that later abort, and the decoding layer is what filters those out and reassembles per-transaction change sets in commit order (A Transaction, Inside the Engine).
- The
beforeimage — what the row looked like prior to the change — is available because multi-version storage keeps old row versions around long enough for concurrent readers, but whether the connector actually receives it is a configuration decision on the source, not a property of CDC (MVCC: Multi-Version Concurrency Control, UPDATE, DELETE and Dead Tuples). - The connector never queries the table. It never takes a row lock, never competes with application queries for the buffer pool, and never scans an index. Its cost to the source is log retention and one replication connection, which is a completely different cost shape from polling (CDC vs Polling).
The path a committed change takes out of the database
A transaction commits. Before any data page is written to its final location, the database has already appended a description of the change to a sequential log — that ordering is what makes crash recovery possible at all, and it is not optional in any durable engine (Write-Ahead Logging).
CDC does not add a mechanism to the database. It adds a *reader* to a mechanism that was already there for durability and replication. The connector looks, from the source's point of view, almost exactly like a replica: it authenticates, it asks to start from a position, and it is streamed changes as they commit (Follow a Write Through the Engine).
What comes out is not SQL and not a row. It is a decoded change record: which table, which operation, which values, and — crucially — where in the log it happened. That last field is the only thing in the whole pipeline that knows the true order of events, and everything downstream either preserves it or loses it.
Read the diagram once forwards, for how a change reaches a consumer. Then read the dashed edge backwards: the connector's position, held at the source, is what stops the log from being recycled. That single back-edge is the reason a stalled analytics connector can fill a production disk.
What each hop actually promises
The value of writing the chain out as stages is that it forces the guarantees column to be filled in. Most CDC arguments are really disagreements about which stage is supposed to provide deduplication, and the honest answer is that none of them do unless you build it.
Notice where the guarantee is strongest and where it weakens. The source log is a totally ordered, committed, gapless record — the strongest promise anywhere in the pipeline. By the time the same changes are sitting in a partitioned topic, total order is gone and only per-key order remains, and by the time a consumer has written them to a table, even that survives only if the write was keyed and ordered deliberately (Topics and Partitions).
The failsBy column is the more useful one during an incident. Each stage fails in a characteristic way, and knowing which one you are looking at collapses the search space immediately: a gap points at retention, a duplicate points at a restart, an out-of-order state points at partitioning.
- 1Source transaction
Commits the change and appends it to the transaction log before the pages are updated.
guarantees Atomicity and durability of the whole transaction, and a total order over commits within this database.
fails by Committing logic that is itself wrong — the strongest guarantee in the chain is about storage, never about meaning.
- 2Log decoding
Reads log records from the connector's position, discards aborted work, reassembles per-row change events in commit order.
guarantees Every committed change is emitted at least once, ordered by log position, with no gaps while the position stays inside retention.
fails by Falling outside retention, at which point changes are not late — they are gone from the source entirely.
- 3Publish to broker
Writes each change event to a topic, usually partitioned by primary key.
guarantees Durability and per-partition order. Nothing across partitions, and nothing about duplicates after a connector restart.
fails by Splitting one source transaction across partitions, so a consumer can read half of it (CDC Ordering and Transaction Boundaries).
- 4Raw landing
Persists the change events untouched, as they arrived.
guarantees That what arrived is preserved exactly, so any downstream mistake is reprocessable (Keeping Raw History: The Recovery Position and the Liability).
fails by Being deduplicated or "cleaned" on the way in, destroying the only copy that could prove what the source actually sent.
- 5State reconstruction
Collapses the change sequence into current state per key, or into a history table.
guarantees Only what its ordering key asserts. Keyed on log position it is correct; keyed on arrival it is plausible and sometimes wrong.
fails by Choosing the latest change by arrival time, so an out-of-order update wins and the row holds the second-newest value.
- 6Serving table
Holds the reconstructed entity for query.
guarantees Effectively-once *state* if the write is an idempotent upsert keyed on the primary key and guarded by log position — a property of this sink, not of CDC.
fails by An append-only insert, which turns at-least-once delivery into visible duplicate rows in every aggregate (Duplicate Rows).
Read the guarantees column top to bottom. Total order and atomicity exist only in the first stage; every stage after it preserves a weaker property, and the last stage recovers correctness only by explicit design.
What CDC costs the source, compared with asking it questions
The most common objection to CDC is that it puts load on production. It does — but not the load people imagine. A connector issues no queries, acquires no row locks and evicts nothing from the buffer pool. It reads a sequential log that the database was writing anyway (The Buffer Pool).
The real cost is retention: the source cannot recycle log segments the connector has not confirmed. That converts a downstream availability problem into an upstream storage problem, which is a trade most teams accept once and then discover during an incident. A polling extract has the opposite shape — it costs the source real query work every interval and costs it nothing when it stops.
The bars below are relative and unitless. They exist to establish an ordering, not a magnitude: for CDC the dominant driver is log retention held open, and for polling it is repeated scanning. That ordering is what transfers between systems; any specific ratio would not.
CDC's dominant and most dangerous cost — it grows while the connector is stopped, which is exactly when nobody is watching it.
Polling's dominant cost, paid every interval regardless of how many rows changed, and growing with table size rather than with change volume.
Polling evicts pages the application needs; CDC touches none of them, which is the clearest architectural advantage here.
Logical decoding is real CPU on the source in some engines and offloaded to the reader in others — a genuine source-specific difference.
Negligible on its own, but it is a connection-pool slot and a privileged one (Connection Pools).
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical row-store source under a moderate change rate, shown to establish an ordering rather than to be measured against a specific system. The teaching is the shape: CDC trades query cost for retention risk, and polling trades retention risk for query cost.
How retention is bounded is source-specific and worth re-reading in current documentation before you rely on it: Postgres replication slots block segment recycling indefinitely unless a maximum slot size is configured, MySQL binary logs expire on a time or size policy independent of any reader, and a MongoDB oplog is a capped collection whose time window shrinks as write volume rises. Configuration names and defaults change between versions.
How to build it
Most important first.
- Prefer log-based capture to trigger-based and query-based capture. Triggers put your pipeline inside the writer's transaction, where a slow or broken audit-table insert becomes an application outage; query-based extraction cannot see deletes or intermediate states at all.
- Treat the connector's position as production state. It must be checkpointed durably, restored on restart, and monitored — a connector that loses its position and restarts from the log tail has silently skipped everything in between (Checkpointing).
- Land the raw change stream before transforming it. The reconstruction of current state from a change sequence is a modelling decision you will get wrong at least once, and only an untouched copy of the changes makes that recoverable (Keeping Raw History: The Recovery Position and the Liability).
- Give the connector its own database role with replication privilege and nothing else. It reads every column of every captured table, including the ones nobody meant to export (Database Privileges and Blast Radius, Least Privilege).
- Decide per table whether you are capturing it. "Capture the whole database" is how a connector ends up streaming a session table that rewrites itself every few seconds and drowning everything else.
- Where the application genuinely needs to emit a semantic business event rather than a row change, use a transactional outbox instead of CDC on the domain table — the outbox row commits in the same transaction as the write, and CDC on the outbox table then carries it (The Transactional Outbox).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- CDC guarantees at-least-once delivery of every committed change, in the source's own commit order, as long as the connector's position stays inside the retained log. Nothing weaker and nothing stronger.
- Ordering is guaranteed by source log position — LSN, binlog offset, oplog timestamp. That order survives exactly as far as the first component that partitions the stream, and no further (CDC Ordering and Transaction Boundaries).
- It does not guarantee transactional atomicity downstream. Two tables changed inside one source transaction become two independent event streams the moment they are published, and a consumer joining them can observe a state that never existed in the database (Distributed Transactions).
- It does not deduplicate. A connector restart after a checkpoint replays from the last committed position, and every change since then is delivered again (Deduplication).
- It does not guarantee that uncommitted or rolled-back work is absent from *your* side of the pipeline if you built your own decoder — that filtering is the decoding layer's job, and reimplementing it is how people accidentally publish aborted transactions.
- It guarantees nothing about schema. A DDL statement on the source may produce a change event, a differently-shaped payload, or a crashed connector (CDC and Schema Drift).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Reconcile counts and a summed measure between the source table and the CDC-derived table for a closed period — yesterday, not today — and alert on divergence. This is the only check that observes capture end-to-end rather than trusting the connector's own status (Reconciliation).
- Assert that every captured table produces at least one change per period where the business says changes happen. A connector that is running but has been silently unsubscribed from a table looks perfectly healthy (Freshness Checks).
- Both checks miss a stream that is complete but reassembled wrongly — every change present, ordered by arrival instead of by log position, so the reconstructed current state is the second-newest value. Counts reconcile; the state is wrong (CDC Ordering and Transaction Boundaries).
- They also miss anything inside the open period, which is precisely where connector lag lives, and they say nothing about columns they do not sum.
- CDC removes the schedule from ingestion. A consumer's staleness becomes a function of connector lag and broker lag rather than of a cron interval, which is a different *shape* of freshness — continuous and variable instead of stepped and predictable (Batch vs Streaming Ingestion).
- That variability matters more than the average. A batch extract is reliably one interval stale; a CDC stream is usually far fresher and occasionally much worse, because a bulk update or a connector restart produces a lag spike that a schedule would have absorbed invisibly.
- Freshness downstream is still capped by the coarsest hop after CDC. A sub-second change stream feeding an hourly transformation gives hourly data, and calling that platform real-time misleads every consumer who hears it (The Freshness SLO).
- CDC cannot be fresher than commit. A long-running transaction publishes nothing until it commits, so a batch job holding a transaction open for twenty minutes produces a twenty-minute cliff in the stream followed by a flood.
- The capture surface is the source's physical schema, which means you are now a consumer of a table shape the owning team believes is private. Every migration they ship is a change to your pipeline, whether they know it or not (Schema Leakage).
- Adding a column is usually absorbed — new field appears in the payload, existing consumers ignore it. Renaming, retyping or dropping a column is not, and the connector's behaviour ranges from an explicit schema-change event to a silently reshaped payload (CDC and Schema Drift).
- A change in *meaning* with no change in shape —
statusgaining a new enum value,amountswitching from gross to net — passes every structural check and every schema registry rule. Only a consumer who knows the domain will notice (Semantic Changes). - The durable fix is a contract between the producing team and the pipeline that names the captured columns and their semantics, so that a migration touching them is a conversation rather than an incident (Data Contracts).
- Normal recovery is resume-from-position: the connector restarts, reads its checkpoint, and replays from there. Everything after the checkpoint arrives twice, which is why downstream must be idempotent by key rather than merely careful (Idempotent Data Pipelines, Upserts and Merges).
- If the position is still inside the source's retained log, no data has been lost and the incident is about lag. If it is not, the changes are gone from the source and the only recovery is a fresh snapshot (CDC Failure Modes and the Retention Deadline).
- Replay from the downstream event log is a different and much cheaper recovery: the broker retains the changes independently of the database, so a transformation bug is fixed by rereading the topic rather than by touching the source at all (Replay from the Log).
- Never repair a CDC-derived table by hand-editing rows. The next replay will overwrite the edit, or worse, will not — leaving one table permanently inconsistent with the stream that is supposed to define it.
What can go wrong
- The connector stops and its replication slot is left in place: the source cannot recycle log segments, disk fills, and a stalled analytics pipeline becomes a production database outage. This is the single most dangerous failure in the module.
- The connector stops and its slot is dropped to save the disk: the source recycles the log, and every change during the outage is unrecoverable without a full re-snapshot.
- A bulk update touches millions of rows and produces millions of change events, saturating the broker and every consumer behind it (Backpressure).
- A long-running or idle-in-transaction session holds the log open, so lag climbs with no error anywhere and no change events flow (Replication Lag: Reads That Are Correct and Stale).
- The mitigation fails too: an alert on connector *liveness* fires only when the process dies, and the expensive failures are all cases where the process is alive and healthy and the data is not moving.
- Someone truncates a captured table. Depending on the source that is one event, no events, or an unparseable one — and the downstream table keeps every row that no longer exists.
- "CDC gives real-time data." It gives change events promptly. End-to-end freshness is decided by every hop after it, and most CDC pipelines terminate in an hourly transformation.
- "CDC is exactly-once." Delivery is at-least-once. Effectively-once *state* is achievable downstream by keying an idempotent upsert on the source primary key plus log position — that is a property of your sink, not of CDC (Exactly-Once: Input Consumption, State Update, Output Write).
- "CDC replaces the application's events." A row change is not a business event.
statusmoving frompendingtocancelledis a row change; "the customer cancelled because the payment failed" is an event, and only the application knows it (Commands vs Events). - "The connector is green, so we have every change." Connector liveness reports that a process is running. Completeness is measured by reconciliation against the source and by nothing else.
- "We can just turn it off for a while." Turning off a connector without dropping its slot fills the source disk; dropping the slot loses the changes. There is no free pause (CDC Failure Modes and the Retention Deadline).
- A CDC stream exports every column of every captured table, including columns added later by a team that had no idea a connector was watching. PII arrives in the lake by default rather than by decision (PII in Pipelines, Data Classification).
- The
beforeimage doubles the exposure: a change stream that carries the previous value of a masked or corrected field preserves the original, which is exactly what a correction was meant to remove. - A hard delete in the source is a delete event downstream, and a delete event is not a deletion. The row still exists in the raw layer, in the broker until retention expires, and in every table built from it (Deletion Requests, Data Retention).
- The connector's credentials are replication-level access to a production database. They belong in the same tier of secret management as the application's own database credentials, not in a connector config file (Secrets Management).
Operating it
- Connector lag expressed as a log position difference, not as seconds: current source log position minus the connector's confirmed position. Seconds-behind is derived and goes to zero on an idle database, which hides a stuck connector (Pipeline Metrics).
- Retained log size on the source, per replication slot. It is the metric that turns "analytics is behind" into "the database has four hours of disk left" (Saturation: The Reading Utilization Cannot Give You).
- Change events per table per interval, compared against that table's own history. Zero where there should be some is the cheapest detector of a silently unsubscribed table (Volume Anomalies).
- The distribution of commit-timestamp to arrival-timestamp gap, so a lag spike is visible as a tail rather than averaged away (Tail Latency: Why p50 Being Fine Does Not Help).
- At 10x change volume the constraint moves from the connector to the broker and the consumers behind it. Decoding is largely sequential per source log, so it does not parallelise the way a scan does (Topics and Partitions).
- At 100x, or when the source is sharded, one connector per shard becomes mandatory and cross-shard ordering ceases to exist as a concept. A consumer that relied on global order has to be redesigned rather than tuned (Partitioning and Sharding).
- Table count scales the governance problem faster than the technical one: a hundred captured tables means a hundred implicit contracts with teams who never agreed to one (Data Ownership).
- Consumer count is nearly free if consumers read the downstream log rather than the source — which is the reason to put a broker between CDC and everything else instead of writing directly to a warehouse (The Event Log).
- CDC costs the source log retention and one replication connection. It does not cost table scans, row locks or buffer-pool pressure, which is the whole architectural point (Workload Isolation).
- It costs the pipeline a per-change event rather than a per-row-per-interval snapshot. For a table with few changes and many rows this is dramatically less work; for a table rewritten constantly it can be far more.
- Retained bytes downstream grow with change volume rather than table size, and a table with a hot rewrite pattern will dominate the topic and the raw layer regardless of how small it is (What Actually Drives Data Platform Cost).
- The connector itself is almost always the cheapest process in the platform and almost always the first one someone tries to optimise.
- CDC buys completeness, deletes, intermediate states and zero query load on the source. It costs a coupling to the source's physical schema, a privileged connection into a production database, and an operational failure mode where your pipeline can take the database down.
- Log-based capture is strictly better than trigger-based on source impact and strictly worse on portability: triggers work on any database you have write access to, and replication access is often the harder thing to obtain politically.
- A stream of changes is more information than a periodic snapshot, and more information means more modelling work. Someone has to decide what "the current order" means from a sequence of changes, and that decision is now yours (Event vs Snapshot Modeling).
- Continuous capture removes the batch window that used to absorb upstream mistakes. With a nightly extract, a bad migration at 14:00 could be caught before 02:00; with CDC it is downstream within seconds.
Change capture, hop by hop
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Write-ahead log | |
|---|---|
| One record is | One physical change to one row, in commit order. |
| Promises | A totally ordered record of everything the database did, retained for as long as the source is configured to keep it. |
| Breaks when | Retention is set for crash recovery, not for you. A replication slot that stops being read holds the log open until the disk fills. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe primitive — a durable ordered log of committed changes, read by a consumer holding a position in it — is shared by every database that supports replication. What differs is the name of the position, the retention control, and how much of the row you are given.
- SOURCE-SPECIFICPostgres exposes logical decoding through replication slots that block log recycling until advanced; MySQL exposes a binary log whose retention is time- or size-bounded and which does not block on a stalled reader; MongoDB exposes a capped oplog whose window shrinks as write volume rises. The failure mode when a reader falls behind is therefore completely different on each.
- SCALE-SPECIFICBelow a few tables and one database, a scheduled incremental extract is simpler, needs no privileged connection, and cannot take production down. CDC earns its operational cost once deletes matter, once intermediate states matter, or once analytical polling starts to be visible on the source.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what "commit order" means once more than one machine is involved, and what a total order across independent logs would actually cost. CDC inherits a total order from a single source log and loses it the moment there are two sources; the depth of that argument belongs there.
- — DevOps / Production Engineering owns how a connector is deployed, versioned and rolled back, and how its configuration change is reviewed. Restarting a connector with a changed table list is a deployment with data consequences, and it deserves the same discipline as a schema migration.