Skip to content

Change data capture#

Change data capture (CDC) turns every insert, update and delete in a database into an event, usually by reading the database's own transaction log, so other systems can follow its changes.

Before reading

Ingestion patterns · Kafka and Kafka Connect

Why it matters#

Incremental loads by timestamp miss deletes, miss changes that skip the timestamp column, and see only the latest state between runs. CDC captures every change, in commit order, within seconds, and without running queries against the source tables. It is the usual way to feed operational databases into a platform when completeness matters.

How it works#

There are two kinds. Query-based CDC runs queries on a schedule, such as rows with updated_at after the last run. It is an incremental load with another name and has the same gaps. Log-based CDC reads the log that the database already writes for its own recovery and replication: the write-ahead log in PostgreSQL, the binary log in MySQL, the redo log in Oracle. Every committed change is in that log, including deletes and changes made by scripts, so log-based CDC sees everything. The rest of this page is about log-based CDC.

A CDC connector, such as Debezium running in Kafka Connect, starts with a snapshot: it reads the current contents of each table and emits one event per row. It then switches to streaming, reading the log from the position where the snapshot was taken, so no change between the two is lost. From then on it emits one event per changed row, to one topic per table, keyed by the row's primary key.

flowchart TB
  APP[Shop application] -->|INSERT, UPDATE, DELETE| DB[(Orders DB)]
  DB --> LOG[Transaction log]
  LOG -->|read by| C[CDC connector]
  DB -.->|initial snapshot| C
  C -->|one event per changed row,<br/>key = order_id| T[Topic:<br/>orders-db.shop.orders]
  T --> RAW[(raw_orders)]

A change event describes one row change. It carries the row before the change, the row after it, the operation (create, update, delete, or read for snapshot rows), and source metadata: the database, table, transaction, log position and commit time. An insert has no before; a delete has no after. After a delete, the connector usually also sends a tombstone, a record with the same key and no value, so that compacted topics can drop the key.

The log holds only what the database needs for replication, so the before image can be incomplete. In PostgreSQL with the default replica identity, a delete's before image holds only the primary key, and an update usually has no before image. Setting REPLICA IDENTITY FULL on a table includes all columns, at some cost in log volume.

CDC brings operational risks. The database keeps log data until the connector has read it. In PostgreSQL this is enforced by a replication slot: if the connector stops, the log keeps growing and can fill the database's disk. In MySQL the opposite happens: the binary log is deleted after its retention period, and a connector that was down longer must take a new snapshot. Schema changes in the source flow into the events, and table renames or type changes need care. CDC also requires database permissions and configuration that the database team must agree to.

Key terms#

  • Log-based CDC Reading changes from the database's transaction log.
  • Query-based CDC Finding changes by querying a change column on a schedule.
  • Snapshot The initial read of a table's current rows before streaming changes (not an Iceberg snapshot).
  • Change event One row change, with before, after, operation and source metadata.
  • Tombstone A record with a key and no value, marking a deleted key.
  • Replication slot PostgreSQL's record of how far a consumer has read the log.

Example#

Order o-1001 has a 10.00 discount applied, then is deleted by a cleanup script. shop.orders has REPLICA IDENTITY FULL. The update event, simplified:

{
  "before": { "order_id": "o-1001", "status": "placed", "amount": 59.90 },
  "after":  { "order_id": "o-1001", "status": "placed", "amount": 49.90 },
  "op": "u",
  "source": { "db": "orders", "schema": "shop", "table": "orders", "lsn": 92847123, "ts_ms": 1793181300000 },
  "ts_ms": 1793181300412
}

The delete event, followed by a tombstone with key o-1001:

{
  "before": { "order_id": "o-1001", "status": "placed", "amount": 49.90 },
  "after": null,
  "op": "d",
  "source": { "db": "orders", "schema": "shop", "table": "orders", "lsn": 92851007, "ts_ms": 1793184900000 },
  "ts_ms": 1793184900233
}

Both events land in raw_orders as rows. Building clean_orders then means applying them in log order, so that the delete wins.

Pitfalls#

  • An unread replication slot. A stopped connector can fill the source database's disk; monitor slot lag and cap it.
  • Log retention shorter than an outage. The connector must re-snapshot, which can take hours on large tables.
  • Ordering by time instead of log position. Two changes in the same millisecond need the log position to be ordered correctly.
  • Ignoring deletes downstream. Delete events must be applied, not dropped, or deleted orders live on in reports.
  • Capturing internal tables as an interface. The events expose the source schema; consumers break when it changes. For events meant for others, see The outbox pattern.

Learn more#

Writing to Iceberg from Kafka Deduplication and merge History and slowly changing dimensions The outbox pattern Schema evolution