Change data capture (CDC) is a data engineering technique that identifies every insert, update and delete in a source database and delivers those changes, in order, to downstream systems such as data warehouses, search indexes, caches or other services. Instead of copying whole tables again and again, CDC moves only what changed, usually within seconds of the original transaction.
The output of CDC is often called a change stream or change event feed. Each event describes one row-level change, typically with the before and after values, the operation type, the source transaction and its position in the database log. CDC is the foundation of near-real-time data pipelines and of many replication and event-driven architectures.
How change data capture works
Every CDC system answers the same question: which rows changed since the last read, and in what order. There are several ways to detect changes, and they differ sharply in latency, completeness and load on the source.
| Method | How changes are detected | Captures deletes | Source load |
|---|---|---|---|
| Log-based | Reads the transaction or replication log | Yes | Low |
| Trigger-based | Triggers write changes to a shadow table | Yes | Medium to high |
| Query-based | Polls on an updated_at or version column | No, unless soft deletes | Medium |
| Snapshot diff | Compares full copies of a table | Yes | High |
Log-based CDC is the preferred method for production systems. It reads the log that the database already writes for durability and replication: the write-ahead log with logical decoding in PostgreSQL, the binary log in MySQL and MariaDB, the redo log in Oracle Database, and the built-in CDC tables of SQL Server. Because the log records every committed change in commit order, nothing is missed and the application tables are not queried.
A log-based connector keeps a position or offset in the log, such as a log sequence number, so that after a restart it resumes exactly where it stopped. Most connectors also perform an initial snapshot of existing rows before switching to streaming, because the log alone does not contain the full history.
Anatomy of a change event
A change event is the unit that CDC delivers. Formats differ between tools, but most events carry the same fields, which consumers need to apply changes correctly.
| Field | Content | Why consumers need it |
|---|---|---|
| Operation | Create, update, delete or snapshot read | Decides how to apply the change |
| Key | Primary key of the changed row | Identifies the target row and ordering |
| Before image | Row values before the change | Needed for deletes and audits |
| After image | Row values after the change | New state to write downstream |
| Source position | Log sequence number or offset | Ordering, deduplication and restart |
| Timestamp | Commit time at the source | Latency measurement and history |
Events are commonly published to a message broker such as Apache Kafka, with the row’s primary key as the message key. This keeps all changes to one row in a single partition and in order, which is essential for applying them correctly downstream.
CDC compared with batch ETL and related patterns
CDC is often contrasted with traditional batch ETL and ELT, which extract full tables or large slices on a schedule. The difference is not only speed but also what information is preserved.
| Pattern | Data moved | Typical latency | Deletes and history |
|---|---|---|---|
| Log-based CDC | Only changed rows | Seconds | Every change preserved |
| Batch ETL or ELT | Full tables or date ranges | Hours | Intermediate changes lost |
| Incremental query load | Rows with newer timestamps | Minutes to hours | Deletes usually missed |
| Reverse ETL | Warehouse data to applications | Minutes to hours | Not a capture method |
A batch job that runs nightly sees only the final state of each row; if a value changed three times during the day, two changes are invisible. CDC records all three, which matters for audits, slowly changing dimensions and fraud analysis. CDC is also distinct from the outbox pattern, in which an application writes explicit business events to a dedicated table that is then captured, giving consumers stable events instead of raw table changes.
Designing a reliable CDC pipeline
A dependable CDC pipeline requires decisions about delivery guarantees, schema changes and source retention. Most log-based tools provide at-least-once delivery, so consumers must tolerate duplicates.
- Make consumers idempotent, for example by applying changes as upserts keyed by primary key and ignoring events with an older source position.
- Plan for schema evolution: added columns are usually harmless, while renamed or retyped columns can break consumers without a schema registry or data contract.
- Watch log retention at the source; if the connector is down longer than the log is kept, it must take a new snapshot.
- In PostgreSQL, monitor replication slots, because an inactive slot forces the server to keep write-ahead log segments and can fill the disk.
- Ensure tables have a primary key, or updates and deletes cannot be mapped reliably to downstream rows.
In a lakehouse or medallion architecture, raw change events usually land in a bronze layer, and a merge step produces current-state tables in later layers.
Limitations and common mistakes
CDC captures physical row changes, not business meaning. An event saying that a status column changed from 2 to 5 does not explain that an order was shipped, so consumers become coupled to the source schema. This coupling is the main argument for the outbox pattern or a well-defined data contract.
Other frequent mistakes include enabling CDC on very high-churn tables without considering the event volume, ignoring large transactions that produce millions of events at once, and treating the change stream as a backup. CDC also requires elevated database privileges and configuration such as row-based binary logging, which must be agreed with database administrators.
In plain words
Change data capture is a way of noticing every change in a database and telling other systems about it right away. Instead of photocopying an entire filing cabinet every night, someone writes down each time a document is added, edited or removed and passes the note along.
Other systems read those notes and stay up to date within seconds. Only the changes travel, so the process is fast, light on the original system and keeps a record of everything that happened.
A real-world example
An online marketplace stores products and prices in a PostgreSQL database, while its search engine and analytics warehouse need the same data. Previously, a nightly job exported 12 million product rows, so search results showed prices that were up to a day old.
The data team introduces log-based CDC through logical decoding, publishing each product change to a message topic keyed by product ID. A search indexer consumes the events and updates individual documents, and a warehouse loader merges them into a current-state table. Price changes now appear in search within a few seconds, the nightly export disappears, and the warehouse gains a full history of price changes that analysts use to study discount campaigns. The team also adds an alert on replication slot lag after a connector outage nearly filled the database disk.