Data
Change data capture
Change data capture (CDC) is a technique that detects every insert, update and delete in a database and delivers each change to other systems as it happens.
Teams adopt change data capture when a nightly copy of the database is no longer fresh enough, and running bigger queries against production is no longer acceptable. CDC delivers every change as it happens, without asking the tables anything.
Why does change data capture matter?
- Fresh data. Downstream systems are seconds behind the source, not a day.
- Low load on the source. Reading the log does not compete with the application’s queries.
- Every change. Deletes and intermediate states are captured, which a nightly snapshot misses.
- One feed, many uses. The same stream keeps a warehouse, a search index and a cache in sync.
What are the parts of a CDC pipeline?
The diagram at the top of this page shows a log-based pipeline from left to right.
- Source database. The system of record the application writes to.
- Transaction log. The ordered record of every committed change that the database keeps for recovery and replication: the write-ahead log in PostgreSQL, the binary log in MySQL.
- Connector. A process that reads the log from a saved position and converts each entry into a change event.
- Change events. One message per row change, with the operation, the row before and after, the table and the log position.
- Event log. Where the events are published, usually a Kafka topic per table. Kafka architecture covers how it stores and orders them.
- Sinks. The consumers: a data warehouse, a search index, a cache or another service.
How does log-based CDC work?
Follow one change through the hero diagram. The application marks order 1041 as paid. The database writes that change to its transaction log, as it does for every commit.
The connector, which is tailing the log, reads the entry and emits an update event carrying the row before and after the change. The event is published with the order id as its key, so every change to that order stays in sequence. The warehouse applies the update to its copy, the search index reindexes the order and the cache drops its stale entry.
Two details make this reliable. A new connector first takes a consistent snapshot of the tables, then switches to the log from the matching position, so nothing is missed in between. And it records how far it has read, so after a restart it resumes from that point.
What are the methods of change data capture?
| Method | How it detects a change | Sees deletes | Load on the source | Use it when |
|---|---|---|---|---|
| Log-based | Reads the transaction log | Yes | Low | You can get access to the log, which covers most production databases |
| Trigger-based | Triggers copy each change into a change table | Yes | Extra work on every write | The log is not available |
| Query-based | Polls for rows with a newer timestamp | No | Repeated queries | Tables are small and minutes of delay are fine |
Log-based capture is the default because it is the only method that costs the application nothing and misses nothing. Query-based capture is the simplest to start with, but it sees only the latest state of a row, so two updates between polls look like one.
How does CDC fit into a data pipeline?
Change data capture is the streaming way into a data pipeline architecture. It turns an application database into a stream, alongside the events that applications publish themselves. In an ETL pipeline it replaces the scheduled extract. The changes land in the staging layer of a data warehouse and are merged into the modeled tables from there.
The same feed serves operational uses. Services react to changes in another service’s data without calling it, which is how many teams move toward an event-driven architecture without rewriting the system that owns the data.
How do you set up change data capture?
- Choose the tables. Capture what a consumer needs, not the whole database.
- Enable log access. Turn on logical replication or the equivalent, and create a dedicated user with only the rights it needs.
- Plan the first snapshot. Large tables take hours to copy, so schedule it and watch the load.
- Key events by primary key. Changes to one row then stay in order, even with parallel consumers.
- Make the sinks idempotent. Delivery is at least once, so apply each change by key and log position.
- Decide how schema changes flow. A new column should reach consumers deliberately, through a schema registry or a contract.
- Monitor lag and log size. Alert when the connector falls behind or the retained log grows.
What are common change data capture mistakes?
- A stalled connector. The database keeps every unread log segment, and a full disk takes production down.
- Polling and calling it CDC. Timestamp queries miss deletes and intermediate updates.
- Sinks that are not idempotent. A replayed event double-counts a payment.
- Raw tables as a public contract. Consumers break when an internal column is renamed. Publish designed events through an outbox.
- Forgetting history. A new consumer needs the existing rows, not only the changes from today.
What tools help with change data capture?
- The database itself. Most relational databases expose their log through logical replication or a built-in change feed.
- Connector frameworks and managed services. Open-source connectors that publish changes to an event log, and cloud services that replicate a database into a warehouse.
- AI diagramming tools. Tools like Eraser turn a description of your source, connector and consumers into a data flow diagram you can keep editing as text, and a sequence diagram of what happens to one change.
For more on this kind of diagram, see data flow diagrams.
Frequently asked questions
-
What is the difference between CDC and ETL?
An ETL pipeline usually extracts data in batches by querying the source on a schedule. Change data capture is a way of extracting: it delivers only the rows that changed, continuously. The two fit together, and CDC often replaces the extract step so that a warehouse is minutes behind its sources instead of a day.
-
Does change data capture slow down the source database?
Log-based capture adds very little load, because it reads a log the database writes anyway and never queries the tables. Triggers add work to every write, and polling adds repeated queries. The risk with log-based capture is different: if the connector stops, the database keeps the unread log and its disk fills up.
-
How does CDC handle deleted rows?
Log-based and trigger-based capture emit a delete event that carries the row's key and its last values, so downstream systems can remove the row or mark it as deleted. Query-based capture cannot see a delete, because the row it would have polled is gone, unless the application marks rows as deleted instead of removing them.
-
What is the outbox pattern?
An application writes its business change and a row describing the event into an outbox table, in one transaction. Change data capture then publishes the outbox rows as events. The database and the event stream cannot disagree, and consumers see a designed event instead of raw table changes. It is common in an event-driven architecture.
