What is Change Data Capture (CDC)?

How it works

There are four common ways to capture changes:

• Log-based: a connector reads the transaction log the database already keeps for recovery and replication (the PostgreSQL WAL, the MySQL binlog). In PostgreSQL this uses logical decoding and a replication slot that records how far the consumer has read. Debezium, usually run on Kafka Connect, is the best-known open-source tool; managed services such as AWS DMS do the same job.
• Query-based: poll WHERE updated_at > last_watermark. Simple, but it misses hard deletes and collapses several updates between polls into one.
• Triggers: a trigger writes each change to a change table inside the same transaction, at the cost of extra work on every write.
• Outbox: the application writes a business event to an outbox table in the same transaction, and CDC publishes that table, giving consumers a stable contract instead of the internal schema.

Each change event carries an operation (create, update, delete, or read during the initial snapshot), the row before and after the change, and its position in the log. A consumer applies events to the target, usually with an upsert keyed on the primary key, and keeps only the latest event per key in each batch. Because connectors deliver at least once, applying events must be idempotent: order by the log position and ignore events older than what the target already holds.

Example

A batch of change events is collapsed to the newest event per key, then merged into the target, with deletes applied too (PostgreSQL 16).

CREATE TABLE customers (id int PRIMARY KEY, email text, lsn bigint);
INSERT INTO customers VALUES (1, '[email protected]', 100), (2, '[email protected]', 110);

CREATE TABLE changes (id int, op char(1), email text, lsn bigint);
INSERT INTO changes VALUES
  (1, 'u', '[email protected]', 120),
  (3, 'c', '[email protected]', 130),
  (3, 'u', '[email protected]', 140),
  (2, 'd', NULL, 150);

MERGE INTO customers t
USING (
  SELECT DISTINCT ON (id) id, op, email, lsn
  FROM changes ORDER BY id, lsn DESC
) s ON t.id = s.id
WHEN MATCHED AND s.op = 'd' AND s.lsn > t.lsn THEN DELETE
WHEN MATCHED AND s.lsn > t.lsn THEN UPDATE SET email = s.email, lsn = s.lsn
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT VALUES (s.id, s.email, s.lsn);

SELECT * FROM customers ORDER BY id;

 id |      email      | lsn
----+-----------------+-----
  1 | [email protected]   | 120
  3 | [email protected] | 140

Running the same MERGE again changes nothing, because every event’s position is no newer than the row it would update.

Common mistakes

• Choosing query-based CDC for a table that hard-deletes rows, then wondering why deleted records live on in the warehouse.
• Letting a PostgreSQL replication slot sit unconsumed while the connector is down; the database keeps WAL for it and the disk fills.
• Applying events without ordering by log position, so a retried old event overwrites a newer value.
• Exposing row-level CDC of internal tables to many consumers, so a column rename in the application breaks every pipeline.