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.
-- Window over ALL events first (deletes included), then drop the delete rows:
-- that way a delete closes the prior version but adds no new one.
WITH versions AS (
  SELECT
    *,
    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
)
SELECT rental_id, status, ts AS valid_from, valid_to, is_current
FROM versions
WHERE op <> 'd'
  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

Answer from memory before opening each one.

1. A teammate adds WHERE updated_at > :last_run to the nightly extract and says the warehouse now matches the source. Finance later finds 40 rentals in the warehouse that the product database doesn't have. What happened, and why can no change to that query fix it?

   Show answer

   Those rentals were deleted at the source. A query can only return rows that exist, so a deleted row has no updated_at left to compare — query-based extraction is structurally blind to deletes, however clever the predicate. You need the delete recorded somewhere: the WAL (log-based CDC) or a soft-delete flag in the source. See The snapshot problem.

2. The database writes its log whether or not anyone reads it. Why does that make log-based CDC cheaper for the source than polling every minute — and what two things does the log give you that timestamps can't?

   Show answer

   Polling runs a real query against live tables on every tick; tailing the WAL reads a file the database already produces for durability, so it adds almost no query load. The log also gives an exact commit order (the LSN) and every intermediate state, where timestamps give approximate order and only the latest state between polls. See Log-based CDC.

3. An event arrives with "before": null. Another arrives with "after": null. What is each one, and what must your current-state table do with the second?

   Show answer

   before: null is an insert (op = c) — there was no prior row. after: null is a delete (op = d), and current state must remove that key; if you ignore it, the warehouse keeps a row the source no longer has. See the event anatomy and tombstones.

4. After a connector restart, events lsn 30 and lsn 40 for rental #501 are delivered a second time — and 40 arrives before 30. Your apply step does a blind INSERT per event. What goes wrong, and which two rules from this chapter prevent it?

   Show answer

   Blind inserts duplicate the rows, and applying 30 after 40 leaves #501 as active when the source says completed. The fixes are idempotent apply — upsert/merge on the key, so replaying an event changes nothing — and order by LSN, latest wins, so a stale event can never overwrite a newer one. See Handling the change stream.

5. In the history query, the window function runs over every event and the op <> 'd' filter comes afterwards. Suppose you moved that filter into the same SELECT as the LEAD. For a key whose last event is a delete, what would the history wrongly claim?

   Show answer

   WHERE runs before window functions, so the delete row would be gone before LEAD looked for "the next event". The last real version would get valid_to = NULL and look current forever — history would say a deleted customer is still on pro. Windowing first, filtering second, is what lets the delete close the final version. Try it on the exercise below.

6. The source table behind dim_gpu — the list of GPU models — has 12 rows, changes about twice a year, never deletes, and feeds a daily report. A colleague wants it on Debezium "for consistency" with rentals. What do you recommend, and what fact would change your answer?

   Show answer

   Keep the simple extract from Chapter 05: no deletes, no latency need, and a full re-read of 12 rows costs nothing — CDC would add a connector and a stream to operate for no gain. The answer flips if you need the history of those rows (say, what a GPU type's spec was when a rental was priced) or if rows start being deleted. See Tradeoffs.

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).
-- LEAD runs over all events, THEN the delete row is dropped - so the delete still closes 'pro'.
WITH versions AS (
  SELECT *, LEAD(lsn) OVER (PARTITION BY id ORDER BY lsn) AS valid_to_lsn
  FROM cust_cdc
)
SELECT id, tier, lsn AS valid_from_lsn, valid_to_lsn
FROM versions
WHERE op <> 'd'
ORDER BY lsn;
--  -> 42 | free | 100 | 200
--     42 | pro  | 200 | 300   (closed at lsn 300 by the delete)

Takeaway

• A snapshot shows state at the moment of the read: intra-day changes collapse, history is overwritten, and a deleted row is gone.
• Query-based extraction cannot see deletes, because a deleted row has no updated_at to compare against.
• Log-based CDC tails the database's write-ahead log, so it captures every insert, update and delete in commit order with almost no query load on the source.
• A change event carries the operation, the row before, the row after and the log position, which is what lets a connector resume exactly where it stopped.
• Apply the stream by upserting the after row for current state, removing the key on a delete, and appending every event for history.
• Apply in log order and by key, so a replayed or out-of-order event cannot leave the table stale or duplicated.
• Use CDC when the source edits or deletes rows, when you need minutes-old data, or when you need full history; for an append-only table a daily incremental extract is the simpler answer.

Data now arrives with full fidelity, so the next chapter turns it into clean, tested, business-ready tables. → Transformation with dbt

Further reading

• PostgreSQL: Write-ahead logging — PostgreSQL docs, accessed 2026. Why the log exists and why it is a complete, ordered record of changes.
• PostgreSQL: Logical decoding concepts — PostgreSQL docs, accessed 2026. Replication slots and output plugins, the mechanism CDC connectors read.
• Debezium connector for PostgreSQL — Debezium docs, accessed 2026. The shape of change events and the operational limits of a Postgres connector.
• Debezium tutorial — Debezium docs, accessed 2026. A runnable end-to-end example from database to Kafka topic.