CDC and ETL describe different layers. CDC is an extraction method that reads committed row changes from a database log. ETL describes the full movement of data, including transformation and loading. Most pipelines use CDC as the extract step.
Back to glossary
Change Data Capture
Change data capture reads inserts, updates, and deletes from a database's transaction log and streams them downstream.
What is Change Data Capture?
CDC identifies row-level changes at the source and delivers them downstream as an ordered stream of events. Each event carries the operation type, the row's values, and its position in the log, so the destination is updated incrementally rather than rebuilt.
The alternative, re-reading the table on a schedule and filtering on a timestamp column, has three limits:
- Hard deletes are invisible. A deleted row cannot be selected, so the destination retains records the source has dropped.
- Intermediate states are lost. Several updates to one row inside a polling window collapse into one result, along with their order.
- Scan cost scales with table size. The query reads the same rows on every run regardless of how many changed.
CDC applies where those limits matter: deletes that carry meaning, destinations that need current state, or tables where repeated scans compete with production traffic.
How CDC reads changes from a database
Databases write committed changes to a durable log before applying them to data files, which is how they recover from crashes and how replicas stay current. A CDC reader consumes that log over the same interface as database replication and records its position so it can resume after a restart.
The log and position marker differ by engine:
- PostgreSQL: logical decoding over the write-ahead log (WAL), requiring wal_level = logical, a publication, and a replication slot that tracks consumer progress. Events are decoded by a plugin, usually pgoutput, and identified by LSN, a byte offset in the WAL.
- MySQL and MariaDB: the binary log (binlog) in ROW format, positioned by file-and-offset or GTID. binlog_row_image = FULL records before and after images; MINIMAL records the key and changed columns.
- MongoDB: change streams over the replica set oplog, resumed with a resume token. Pre-images are disabled by default, so a delete event contains only _id unless changeStreamPreAndPostImages is enabled.
- SQL Server: a capture job reads the transaction log into cdc.* change tables, which the consumer queries. DynamoDB exposes Streams, retained for 24 hours.
The log holds only writes made after the slot was created, so the initial load is a separate operation. A consistent implementation snapshots the table at a known LSN, GTID, or resume token, then streams from that position, leaving no gap.
The main CDC methods compared
Three methods are in common use, distinguished by where the change is detected.
| Method | How it detects change | Trade-off |
|---|---|---|
| Log-based | Reads the transaction log as a replication client | Requires elevated privileges and source configuration. An unconsumed stream grows log retention on the source. |
| Query-based | Polls a high-water mark column such as updated_at | Misses deletes and intermediate versions, repeats scans, and depends on every writer maintaining the column. |
| Trigger-based | AFTER INSERT, UPDATE, and DELETE triggers write to an audit table | Captures deletes and full before images, at the cost of latency inside the user transaction and roughly double the write volume. |
Log-based capture is the default where write volume is meaningful. Trigger-based capture remains in use where the log is inaccessible, such as managed instances that do not grant replication privileges.
Where CDC sits in a modern data stack
CDC occupies the ingestion layer between the transactional database and the systems reading a copy of it:
- Source and reader: the OLTP primary, or a replica that exposes the log, and the process holding the replication slot, binlog position, or resume token.
- Buffer: Kafka, Kinesis, or an equivalent queue, so a destination outage stalls the writer rather than accumulating WAL on the source.
- Destination writer: converts events into upserts and deletes against Snowflake, BigQuery, Redshift, or Databricks, keyed on the primary key. Transformation tools such as dbt then run on tables that are already current.
CDC tools differ mainly in what happens after the log read: whether events are buffered externally, how they merge into a columnar destination, and how schema changes are applied mid-stream.
What breaks in a CDC pipeline
Most CDC failures appear under production load, not during setup.
- Replication slot growth. PostgreSQL retains WAL from the oldest unacknowledged slot, so a stalled consumer can exhaust disk on the primary.
- Log retention overrun. A backfill that outlasts the binlog expiry or oplog window leaves the reader resuming at a position that no longer exists, requiring a resnapshot.
- Unchanged TOAST values. PostgreSQL omits unchanged out-of-line values from the WAL record, so a writer that treats the placeholder as data overwrites valid destination columns with nulls.
- Schema changes. Logical decoding does not emit DDL, so new columns appear as unmapped fields and type changes arrive as values the destination column cannot store.
- Failover. Logical slots did not survive promotion of a physical standby until PostgreSQL 17 introduced slot synchronization. On earlier versions, failover requires recreating the slot and resnapshotting.