Change Data Capture

The snapshot problem

In Chapter 05 you loaded data the simplest way: run a SELECT * against the source, dump the result into the warehouse. Maybe nightly, maybe with a high-water mark so you only grab new rows. That works — until you ask what it misses. A full nightly snapshot is a photograph taken once a day. Anything that happened between two photographs is simply gone.

Picture our GPU-rental marketplace. The source has a rentals table (who rented what, and at what status) and a prices table (the hourly rate per GPU type). Both change all day long. Now look at what a once-a-day SELECT * can and can't tell you:

Four distinct things went wrong, and each is a real failure mode:

What CDC is

Change Data Capture (CDC) flips the model. Instead of periodically asking the table "what do you look like now?", you capture every individual change — every insert, update, and delete — as a stream of discrete change events, in the exact order they happened.

Compare the two mental models directly:

A snapshot is a photo; CDC is the movie. With the movie you can always reconstruct any photo (replay the changes up to a point in time), and answer questions a photo never could: how often did this row change, what was deleted, what was the value at 2pm last Tuesday. The cost is that you now have to capture and process a stream rather than run one tidy query — which is the rest of this chapter.

Log-based CDC

How do you capture every change without re-querying the table? The clever answer: the database is already writing every change down for you. Every transactional database keeps a write-ahead log (the WAL in Postgres; the binlog in MySQL; the redo log in Oracle). Before any change touches the actual table files, the database appends a record of it to this log — that's how it guarantees durability and can recover after a crash.

That log is a perfect, ordered, complete record of every insert, update, and delete that ever happened. Log-based CDC simply tails that log. A connector — the best-known is Debezium — reads the WAL, decodes each entry into a structured change event, and emits it to a stream (typically Kafka / Redpanda, which you'll meet in Chapter 09).

Why is reading the log so much better than the obvious alternative — polling the table with a query on a timer?

A single change in the WAL becomes one richly-structured event. Here is what a Debezium-style event for an update to a rental looks like — note that it carries both the before and the after image of the row, plus an op code telling you what kind of change it was:

{
  "op": "u",                       // c = create/insert, u = update, d = delete, r = snapshot read
  "ts_ms": 1718900000000,          // when the change was captured
  "source": {
    "db": "griddp",
    "table": "rentals",
    "lsn": 24563120,               // log sequence number — the exact position in the WAL
    "txId": 5512
  },
  "before": {                       // the row as it was (null for an insert)
    "rental_id": 501,
    "gpu_type": "A100",
    "status": "paused"
  },
  "after": {                        // the row as it now is (null for a delete)
    "rental_id": 501,
    "gpu_type": "A100",
    "status": "active"
  }
}

Handling the change stream

Capturing the stream is half the job; the other half is applying it downstream so your warehouse reflects the source. There are two outputs you usually want from the same stream, and CDC can feed both:

• Current state — a table that mirrors the source as it is right now. You get this by upserting: an insert or update writes the after row (merge on the key); a delete removes the key.
• Full history — a record of every version a row ever had. You get this by appending every event to a log table, never overwriting. This is exactly the slowly-changing dimension (SCD2) pattern from Chapter 03 — a CDC stream is the most natural raw material for SCD2 history.

Two details matter when you apply the stream:

Hands-on: apply a change feed

Configuring Postgres logical decoding and Debezium is a real production setup, but it's heavy for a laptop. The logic of CDC — turning a stream of c/u/d events into current state and history — is the part worth your fingers. So we'll simulate the change feed: a small table of CDC events, exactly the shape Debezium would emit, and write the SQL that applies it. Run this in DuckDB.

-- A simulated CDC feed for one rental key (501), in log order.
-- op: c = insert, u = update, d = delete.  lsn = position in the WAL.
CREATE OR REPLACE TABLE cdc_feed (
  lsn      INTEGER,
  op       VARCHAR,
  rental_id INTEGER,
  status   VARCHAR,     -- the value in `after` (NULL for a delete)
  ts       TIMESTAMP
);

INSERT INTO cdc_feed VALUES
  (10, 'c', 501, 'active',    TIMESTAMP '2026-06-21 09:00'),
  (20, 'u', 501, 'paused',    TIMESTAMP '2026-06-21 11:00'),
  (30, 'u', 501, 'active',    TIMESTAMP '2026-06-21 14:00'),
  (40, 'u', 501, 'completed', TIMESTAMP '2026-06-21 18:00'),
  (50, 'c', 502, 'active',    TIMESTAMP '2026-06-21 21:00'),
  (60, 'd', 502, NULL,        TIMESTAMP '2026-06-21 22:00');

First, build current state: for each key, take the latest event by lsn, and drop keys whose last event was a delete (the tombstone).

-- latest event per key, then filter out deletes
WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY rental_id ORDER BY lsn DESC) AS rn
  FROM cdc_feed
)
SELECT rental_id, status, ts AS as_of
FROM latest
WHERE rn = 1
  AND op <> 'd'          -- tombstone: a key whose last op is a delete is gone
ORDER BY rental_id;

Now build full history — every version key 501 ever held, with a valid-from / valid-to window. This is SCD2, derived straight from the feed:

-- each event opens a new version; the next event's ts closes it
SELECT
  rental_id,
  status,
  ts AS valid_from,
  LEAD(ts) OVER (PARTITION BY rental_id ORDER BY lsn) AS valid_to,
  (LEAD(lsn) OVER (PARTITION BY rental_id ORDER BY lsn) IS NULL) AS is_current
FROM cdc_feed
WHERE op <> 'd'          -- a delete closes the prior version but adds no new one
  AND rental_id = 501
ORDER BY lsn;

Tradeoffs & when to use it

CDC is powerful, but it is not free, and it is not always the right tool. The honest comparison against the simple incremental extraction from Chapter 05:

✓ Check yourself

• Why can a query-based nightly snapshot never capture a deleted row?
• What is the WAL, and why is tailing it cheaper than polling the table?
• In a CDC event, what do before and after hold for an insert? For a delete?
• How does the same change stream produce both current state and full history?

CREATE OR REPLACE TABLE cust_cdc (lsn INT, op VARCHAR, id INT, tier VARCHAR);
INSERT INTO cust_cdc VALUES
  (100, 'c', 42, 'free'),
  (200, 'u', 42, 'pro'),
  (300, 'd', 42, NULL);

-- (a) CURRENT STATE: latest event is a delete -> the key is GONE.
WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY lsn DESC) AS rn
  FROM cust_cdc
)
SELECT id, tier FROM latest WHERE rn = 1 AND op <> 'd';
--  -> zero rows. The delete tombstones key 42.

-- (b) HISTORY: every version the key ever held (deletes add no new version).
SELECT id, tier,
       lsn AS valid_from_lsn,
       LEAD(lsn) OVER (PARTITION BY id ORDER BY lsn) AS valid_to_lsn
FROM cust_cdc
WHERE op <> 'd'
ORDER BY lsn;
--  -> 42 | free | 100 | 200
--     42 | pro  | 200 | 300   (closed at lsn 300 by the delete)

Next

You can now move data in and capture every change to it with full fidelity. Next we turn that raw, ingested data into clean, tested, business-ready tables using the modern transformation tool. → Transformation with dbt