Change Data Capture (CDC)

You often need to copy a table from an app database into the warehouse. This chapter uses one interview prompt: get orders from Postgres into the warehouse. The warehouse may be at most five minutes behind.

Change data capture (CDC) means you capture each change to a table as its own event. The change can be an insert, an update, or a delete.

What you already know

• An incremental load reads the rows where updated_at is newer than a watermark.
• A merge (upsert) updates a row if it exists and inserts it if not. Running it twice gives the same result.
• Batch jobs run on a schedule. A stream handles events one by one, as they arrive.
• From week 8: checks before publish, schema changes, and data contracts.

What you will learn

1. What a watermark query cannot see.
2. Three ways to capture changes.
3. How the database log gives you change events.
4. How to pick the right event when events arrive late or twice.
5. How to apply events with MERGE.
6. How to do the first load, then keep going with the stream.
7. What happens when the table changes.
8. When CDC is too much.

We use the shop. Some changes on 2026-01-22 are made up for the examples. Each section says so.

Polling means you ask the database again and again: what is new? The watermark load from the incremental-load chapter is polling.

Say your job loaded all orders into the warehouse at 10:00. It runs again at 10:05.

These changes happened in between. They are made up for the example:

• 10:01: order 105 changes from pending to paid, amount 22.00.
• 10:02: order 110 is created for customer 2. It is pending, amount 30.00.
• 10:03: order 106 is deleted.
• 10:04: order 110 changes to paid.

The source table has an updated_at column. The app sets it on every change. The watermark is 10:00, and this is the query:

SELECT order_id, status, amount
FROM   orders
WHERE  updated_at > '2026-01-22 10:00'
ORDER  BY order_id;

The query found 105 and 110. Order 106 is gone from the source, so no row has a new updated_at for it. You cannot query a row that does not exist.

Now compare the source with the warehouse after you merge these two rows:

There are three common ways to capture changes. This table compares them:

A trigger adds work to every write, so it can slow the app. Log-based capture avoids that. Most CDC tools, such as Debezium, read the log.

Before a database changes a table, it writes the change to a log. Postgres calls it the write-ahead log (WAL). The database uses the log to recover after a crash.

The WAL is written for the database, not for people. Postgres can turn it into simple row changes: this row was added, changed or removed. This feature is called logical decoding.

A CDC tool reads these row changes and turns each one into an event. Debezium is one common open-source tool. It usually writes the events to Kafka, a tool that stores events in order.

Here are the four changes from 10:00 to 10:05 as events. The before and after values are short here:

Each event has four parts you need:

• op is the operation: c for create, u for update, d for delete.
• before is the row before the change. It is empty for a create.
• after is the row after the change. It is empty for a delete.
• pos is the position in the log. It gives the order. Postgres calls it the LSN (log sequence number). Real ones look like 0/16B3748.

Look at event 503. The delete is an event, and it holds the old row. The poll in the first section had nothing to see.

Debezium also uses op r for rows it copies in the first load. You meet r in a later section.

Events can arrive late, out of order, or twice. The position tells you the true order. The rule is simple: for each order, the event with the highest position wins.

You first save the events you read in a staging table called changes. If the CDC tool restarts, it can send some events again.

Look at the arrived column in the input: 504 came twice, and 502 came last. This query keeps one event for each order:

SELECT pos, op, order_id, customer_id, status, amount, order_date
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY pos DESC
         ) AS rn
  FROM   changes
) AS ranked
WHERE  rn = 1
ORDER  BY order_id;

Compare this with "the last row that arrived". For order 110 that is event 502, which says pending. That is wrong.

The highest position says paid, which is right.

The three rows from the last section are now a table called latest. You apply them to the warehouse with one MERGE.

You add a column last_pos to warehouse.orders. It holds the pos of the last event that changed the row.

The first load copied all six orders at position 500, so each row starts with 500. You see that first load in a later section.

The MERGE has three rules. Look at the code, then at the result:

MERGE INTO warehouse.orders AS t
USING latest AS c
  ON t.order_id = c.order_id
WHEN MATCHED AND c.op = 'd' AND c.pos > t.last_pos THEN
  DELETE
WHEN MATCHED AND c.pos > t.last_pos THEN
  UPDATE SET customer_id = c.customer_id,
             status      = c.status,
             amount      = c.amount,
             order_date  = c.order_date,
             last_pos    = c.pos
WHEN NOT MATCHED AND c.op <> 'd' THEN
  INSERT (order_id, customer_id, status, amount, order_date, last_pos)
  VALUES (c.order_id, c.customer_id, c.status, c.amount, c.order_date, c.pos);

Here is what each rule did:

• Order 106 matched, op is d, and 503 is above 500, so the row was deleted.
• Order 105 matched and the event is newer (501 is above 500), so the row was updated.
• Order 110 did not match and op is not d, so the row was inserted. Its create (502) was dropped in the last step, so the newest event is an update. That is why the insert rule only checks that op is not d.

Postgres MERGE stops with an error if two source rows match the same warehouse row. Keeping one event per order also prevents that.

Now run the same MERGE again. Order 105 matches, but 501 is not above 501, so it is skipped. Order 110 is skipped for the same reason.

Order 106 has no row, and op is d, so nothing happens. The table does not change, so a re-run is safe.

The reader gets only changes made after its replication slot is created. It does not get the six orders that are already in the table. So a new CDC pipeline starts with a snapshot.

A snapshot is a full copy of the table at one moment. The tool notes the log position at that moment, then streams every change after it.

The snapshot rows are op r (read). This is what the warehouse receives, in order:

The stream starts at the snapshot position, so no change falls in a gap. Sometimes, for example after a restart, a change shows up in both. The position rule from the MERGE makes that safe.

To turn this on for Postgres, a database admin does three things:

1. Set wal_level to logical. This needs a restart.
2. Create a publication: a list of tables whose changes Postgres shares. Add orders to it. Then create a replication slot. The slot is a bookmark: the database keeps log files until the reader says it has read them.
3. Create a user for the CDC tool. Give it the replication permission and read access to orders.

Source tables change. From week 8: adding a column is usually safe, but a rename or a drop breaks readers.

A data contract is an agreement. The app team tells you before it renames or drops a column.

Say the app team adds a column called coupon to orders. This is made up. The next create event, for order 111, has the new field:

Tools differ here. For Postgres, Debezium shows the new column only in the next row event. Check the guide of your tool.

This table shows what the events show for each kind of change, and what to do:

CDC costs setup and care. Often a simpler load is enough. This table shows which choice fits which need:

CDC also brings costs:

• Setup on the source, and permission from the team that owns it.
• More parts to run and watch: the reader, the queue, the apply job.
• A reader that stops for too long. Some databases, such as MySQL, delete old log files after a set time. Postgres can do the same with max_slot_wal_keep_size. Then you need a new snapshot.
• Copies of every change. A request to delete a person must reach every copy.

Six points make a strong answer:

1. Ask first: do deletes matter, how big is the table, and can you change the source settings?
2. If there are no deletes and updated_at is reliable, a watermark load every minute can be enough.
3. If deletes or every change matter, use log-based CDC. Debezium reading the Postgres log is one example.
4. Start with a snapshot, then stream from the snapshot position.
5. Apply with MERGE: keep the highest position for each key, delete on op d, and skip older events. A re-run is safe.
6. Watch the lag and the slot, and plan for schema changes with a data contract.

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.