Chapter 14: Change Data Capture

"Stop asking the database what changed. It already wrote it down."

Overview

Every transactional database maintains a durable, ordered log of every change it makes, because it has to — that log is how the database survives a crash and how replicas stay in sync.

Change data capture is reading that log.

That one move solves problems batch extraction cannot solve at all. Deletes appear as events rather than as absences (Chapter 13 §13.5). The watermark disappears entirely, and with it all four of Chapter 13 §13.4's lies. Every intermediate state is captured rather than only the value at poll time. And the load on the source is a log reader rather than repeated scans.

It is, on the merits, better than batch extraction for mutable tables. This chapter will recommend it for Kestrel's orders and order_items.

It is also the most operationally demanding thing in Part III, and the chapter is deliberately weighted toward that. Two sections are about failure: §14.5, the initial snapshot, which people underestimate because it is not the interesting part; and §14.6, operating a replication slot, where a stopped consumer fills a disk and a PostgreSQL primary with a full WAL disk stops accepting writes — which means checkout stops. Chapter 7 §7.8 flagged this; here it gets the treatment it deserves, because a data pipeline component taking down the storefront is the worst outcome in this book.

§14.11 is the counterweight: five conditions under which CDC is the wrong choice, including two that are common.

In this chapter, you will learn to:

• Explain what a replication log contains and why reading it beats querying.
• Name the four problems CDC solves and the price of each.
• Distinguish log-based, trigger-based, and query-based CDC.
• Read a Debezium envelope and extract the four things that matter.
• Plan an initial snapshot without locking the source or losing changes.
• Operate a replication slot safely.
• Turn a change stream into a queryable table.
• State the five conditions under which CDC is wrong.

Who needs this chapter: the Streaming and Platform paths, in full. On Quick Start, read §14.2 and §14.11 so you know when to reach for it.

14.1 What the Log Actually Contains

A database writes to its log before it modifies data pages — write-ahead logging — so that a crash mid-write can be recovered by replaying the log. That log is:

• Ordered. A total order over all changes, by log sequence number (PostgreSQL's LSN, MySQL's binlog position).
• Complete. Every insert, update, and delete. Nothing is missing, because if it were, crash recovery would be wrong.
• Durable. Flushed before a commit is acknowledged.
• Physical, by default. It records "page 4,182 byte 96 changed from X to Y," not "customer 8841's region became Oregon."

That last point is why CDC needs logical decoding: a mechanism that translates physical log records back into row-level change events. This is the wal_level = logical setting that Chapter 5 §5.6 put in the compose file nine chapters early, precisely because changing it requires a restart.

   application                       PostgreSQL
   UPDATE customers                  ┌──────────────────────────────┐
   SET region='OR'  ────────────────▶│ 1. write WAL record          │
   WHERE customer_id=8841            │ 2. modify the page in memory │
                                     │ 3. ack the commit            │
                                     └──────────────┬───────────────┘
                                                    │ WAL
                    ┌───────────────────────────────┼───────────────┐
                    ▼                               ▼               ▼
            physical replica              logical decoding      archive
            (byte-for-byte)               ↓                     (backup)
                                     {op: u, before: {region: CO},
                                      after: {region: OR}, lsn: ...}
                                          ↓
                                     Debezium → Kafka

In words: the same write-ahead log serves crash recovery, physical replication, and — through logical decoding — change data capture. CDC is not an extra thing the database does for you; it is a different reader of something it was already writing.

14.2 What CDC Solves That Batch Cannot

Four things, each with a price.

1. Deletes. A delete is an event with a before image. Chapter 13 §13.5's entire section collapses to "the delete arrives." And critically — Chapter 13's second case study makes this point — it is the only delete strategy that requires nothing from the source team. No soft-delete column, no schema change, no negotiation.

Price: the operational burden of §14.6.

2. The watermark disappears. No updated_at, so none of Chapter 13 §13.4's four lies. Position is tracked by LSN, which is assigned by the database in commit order and is monotonic by construction.

Price: the LSN is now a piece of state you must not lose, and losing it means re-snapshotting.

3. Every intermediate state. A row that goes pending → paid → shipped → delivered between two hourly polls yields one row to a batch extract and four events to CDC. For an order-status funnel or an SCD Type 2 dimension (Chapter 20), the intermediate states are the data.

Price: volume. Four events where you had one row.

4. Lower, steadier load on the source. A log reader is cheap and constant; repeated scans are expensive and spiky.

Price: the load never stops. A batch extract's load is bounded and scheduled; CDC's is continuous, which is usually better and is a different profile.

📐 Design Decision — CDC for mutable tables, batch for the rest

The temptation on adopting CDC is to move everything to it. That is a mistake, and the reasoning is Chapter 13 §13.2's in reverse.

CDC earns its operational cost when a table is mutable, high-value, and inconvenient to reload. Kestrel's orders and order_items qualify: they mutate constantly, they carry the revenue number, and they hard-delete.

It does not earn it for products (47,000 rows, full load in 12 seconds), categories, or warehouses. Putting them on CDC adds connectors to monitor, slots to watch, and snapshots to manage, for tables where a full load is guaranteed correct and takes less time than reading this paragraph.

What the mixed approach costs: two ingestion mechanisms to understand and operate rather than one, and a boundary that new engineers have to learn. That is real, and it is smaller than the alternative.

Kestrel's split: CDC on orders, order_items, payments, returns, and inventory — the mutable five. Batch full loads on the other seven.

14.3 The Three Kinds of CDC

Log-based is what "CDC" means in modern usage and is what this chapter covers.

Trigger-based is worth knowing because it is what you fall back to when you cannot get replication access — a managed database with the feature disabled, a vendor system, a DBA who says no. A trigger writing (op, before, after, ts) to an audit table gives you a change stream you can then extract in batch. It doubles the write cost of the source table, which is why it is a fallback rather than a choice.

Query-based is Chapter 13. It is here for completeness and because vendors sometimes market it as CDC, which it is not — it cannot see deletes or intermediate states.

14.4 Debezium and the Event Envelope

Debezium is the standard open-source CDC platform. It runs as a Kafka Connect connector, reads the source's log, and publishes one Kafka message per row change.

The envelope

{
  "before": {"order_id": 88214, "status": "paid",
             "updated_at": "2025-11-28T14:22:07.481Z"},
  "after":  {"order_id": 88214, "status": "shipped",
             "updated_at": "2025-11-28T14:41:19.002Z"},
  "source": {
    "version": "2.7.0.Final", "connector": "postgresql",
    "name": "kestrel", "ts_ms": 1764340879002, "snapshot": "false",
    "db": "kestrel_app", "schema": "public", "table": "orders",
    "txId": 4182993, "lsn": 55016704, "sequence": "[\"55016640\",\"55016704\"]"
  },
  "op": "u",
  "ts_ms": 1764340879184,
  "transaction": {"id": "4182993:55016704", "total_order": 3,
                  "data_collection_order": 1}
}

Four things in there matter and the rest is context.

op — the operation: c create, u update, d delete, r read (a snapshot row). Your consumer branches on this.

before and after — the row images. before is null on a create; after is null on a delete. before is the thing batch extraction can never give you, and it is what makes SCD Type 2 and change auditing possible.

source.lsn — the position in the log. This is your ordering key and your deduplication key, and §14.7 is built on it.

source.ts_ms versus the top-level ts_ms — when the change happened in the database, versus when Debezium processed it. Chapter 4 §4.6's event time and processing time, in one message. Use source.ts_ms for anything analytical.

⚠️ Failure Mode — before is null when you expected a row

PostgreSQL's REPLICA IDENTITY setting controls what goes into the before image, and its default is DEFAULT, which includes only the primary key.

So on an update you get:

json {"before": {"order_id": 88214}, "after": {"order_id": 88214, "status": "shipped", ...}}

The before image is just the key. Every SCD Type 2 build, every "what changed" audit, and every delta computation silently gets nothing.

The fix is one statement per table:

sql ALTER TABLE orders REPLICA IDENTITY FULL;

And it is not free. FULL means the entire old row is written into the WAL on every update, so WAL volume rises — substantially for wide tables. Kestrel measured a 31% increase in WAL generation on orders after enabling it.

The decision, per table: FULL where you need before-images (orders, customers — anything feeding an SCD2 dimension), DEFAULT where you do not (order_items, which is effectively append-only in analytical terms).

This is the single most common Debezium misconfiguration, and its symptom is not an error — it is a downstream model that quietly produces nothing useful.

14.5 The Initial Snapshot

The part people underestimate, because streaming is the interesting bit and the snapshot is not.

The problem: the log contains changes from now onward. It does not contain the rows that already exist. Before streaming is useful you need a consistent starting image of the table, and it must join to the stream without a gap and without duplication.

The three approaches

1. Locking snapshot. Take a table lock, read everything, note the LSN, release, stream from there. Correct, simple, and it locks a production table for the duration — unacceptable on orders.

2. Consistent-snapshot-without-lock. Debezium's default on PostgreSQL: open a REPEATABLE READ transaction, note the LSN, read the table within that snapshot, then stream from the noted LSN. No table lock.

It has a cost that is not obvious: a long-running transaction, which is Chapter 7 §7.3's bloat problem — a snapshot of a 340 GB table holds a transaction open for hours and prevents vacuum throughout. The mechanism that took down checkout in Chapter 7's Case Study 1 is exactly this.

3. Incremental snapshot. Debezium's chunked approach (based on the DBLog design): read the table in key-range chunks, interleaved with the live stream, using low and high watermark events in the stream itself to resolve conflicts between a snapshot row and a concurrent change to the same key.

This is the one to use for large tables. No long transaction, no lock, resumable, and it can be triggered on demand — including to re-snapshot a single table without restarting the connector.

📏 Scale Note — Sizing the snapshot, which is the part that surprises people

Kestrel's orders and order_items at full scale: 2.4M orders and 6.48M lines, ~28 GB.

Approach	Duration	Source impact	Failure behavior
Locking	~40 min	table locked	restart from scratch
Consistent, no lock	~40 min	40 min of vacuum blocked	restart from scratch
Incremental, 10k chunks	~70 min	chunk-sized, throttleable	resumes

The incremental snapshot is 75% slower and the only one that is operationally acceptable, which is the trade to internalize: for a snapshot, restartability and bounded impact beat speed, because the failure mode of the fast options is "start again from the beginning on a table that takes 40 minutes."

At 10× Kestrel — 280 GB — the locking and consistent options stop being options at all: a seven-hour transaction is not something you can hold on a production primary, and a seven-hour job that restarts from scratch on any failure will not finish.

Plan the snapshot before you plan the stream. It is the part that determines whether CDC is feasible on a given table, and it is the part every tutorial skips.

14.6 Operating a Replication Slot

The section that matters most operationally, and the one where a data pipeline can stop the storefront.

What a slot guarantees, and what that costs

A replication slot records how far a consumer has read and guarantees the primary retains WAL until the consumer confirms it. That guarantee is what makes CDC reliable: a consumer can be down for an hour and resume exactly where it left off.

It is also the danger. If the consumer stops and the slot remains, WAL accumulates without bound.

  Debezium crashes, Friday 19:40
      │
      │  the slot remains. The primary CANNOT recycle WAL past restart_lsn.
      ▼
  Saturday 04:00   WAL 41 GB    nobody is looking
  Saturday 22:00   WAL 118 GB   disk 61% full
  Sunday   14:00   WAL 232 GB   disk 96% full
  Sunday   15:20   DISK FULL
                   ↓
      PostgreSQL cannot write WAL → CANNOT COMMIT → the storefront stops.

A PostgreSQL primary with a full WAL disk stops accepting writes. Not degraded — stopped. Checkout returns errors. This is the worst outcome in this book and it is caused by a data pipeline component.

The four controls, and all four are required

1. max_slot_wal_keep_size (PostgreSQL 13+). A ceiling on retained WAL per slot. When exceeded, the slot is invalidated — the CDC stream breaks and you must re-snapshot.

That trade is correct and should be made deliberately: a broken pipeline is bad, and an outage is worse. Kestrel sets it to a value that gives roughly 48 hours of headroom at normal write volume, which covers a weekend.

2. Monitor slot lag, with an alert well before the ceiling:

SELECT slot_name, active, active_pid,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
         AS retained_wal,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
  FROM pg_replication_slots
 ORDER BY retained_bytes DESC;

Warn at 25% of the ceiling; page at 50%.

3. Alert on active = false. A slot that should have a consumer and does not is a five-minute problem that becomes a weekend outage. This is the highest-value alert in the chapter — it fires at the moment of the crash rather than when the disk is filling.

4. Drop slots you are not using. An abandoned slot from an experiment is a landmine with no owner.

🏭 From the Pipeline — The slot from the proof of concept

A team evaluated Debezium, created a slot named debezium_poc, decided against it for the time being, and shut down the connector. Nobody dropped the slot.

Eleven weeks later the primary's disk filled. The database stopped accepting writes at 02:14 on a Tuesday and was down for fifty minutes.

Three things made this worse than it needed to be:

• Nobody recognized the cause. The on-call engineer was a backend developer who had never heard of a replication slot, and the error — a write failure with a disk-space message — pointed at storage rather than at replication.
• The slot's name meant nothing to anyone still there. The engineer who created it had changed teams.
• The fix is one statement — SELECT pg_drop_replication_slot('debezium_poc'); — and it took forty of the fifty minutes to find it.

Three practices that prevent the whole class:

1. Slots are named with an owner and a purpose: cdc_orders_dataeng, not debezium_poc.
2. A weekly report of every slot, its retained WAL, its active state, and its owner. Any slot with no named owner is deleted after a week's notice — Chapter 9's Case Study 2's archive discipline, applied to a different resource.
3. max_slot_wal_keep_size is set, so the worst case is a broken stream rather than an outage. This one alone would have prevented it.

14.7 Consuming a Change Stream

Three problems: ordering, duplicates, and turning events into a table.

Ordering

Kafka guarantees order within a partition (Chapter 4 §4.6). Debezium keys messages by the row's primary key, so all changes to one row land in one partition and are ordered relative to each other.

That is the guarantee you need and it is narrower than it sounds. Changes to different rows are not ordered relative to each other, even within one source transaction, unless they happen to share a partition. If your consumer needs transactional consistency across rows, you need Debezium's transaction metadata topic and buffering — which is a genuine complication and is usually unnecessary for analytics.

Duplicates

At-least-once, always. A connector restart re-emits from its last committed offset. Chapter 4 §4.5's rule applies unchanged: at-least-once plus idempotent writes.

The deduplication key is the LSN, not the timestamp. Two changes can share a millisecond; they cannot share an LSN.

-- The standard shape: keep the highest LSN per key.
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id
                               ORDER BY lsn DESC) AS rn
    FROM bronze.orders_cdc
   WHERE ingest_date = :dt
)
SELECT * FROM ranked WHERE rn = 1;

Deletes and tombstones

Debezium emits two messages for a delete by default: the delete event (op: d, with a before image), then a tombstone — the same key with a null value.

The tombstone exists for Kafka log compaction: a compacted topic retains the latest message per key, and a null value tells compaction to remove the key entirely. Without it, a compacted topic retains deleted keys forever.

Your consumer must handle both, and the common bug is a null-pointer failure on the tombstone because the code assumed every message has a value.

14.8 From Change Stream to Table

The change stream is not a table. Turning one into the other is the transformation, and there are two targets.

The current-state table — one row per key, latest version:

MERGE INTO silver.orders t
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) rn
      FROM bronze.orders_cdc WHERE ingest_date >= :since
  ) WHERE rn = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd'                  THEN DELETE
WHEN MATCHED AND s.lsn > t.source_lsn        THEN UPDATE SET ...
WHEN NOT MATCHED AND s.op <> 'd'             THEN INSERT ...;

Three things in that statement are load-bearing:

ROW_NUMBER() ... ORDER BY lsn DESC collapses multiple changes to one key into the latest. s.lsn > t.source_lsn makes the merge idempotent — replaying an old event cannot overwrite a newer state. And the op = 'd' branch is the delete handling that batch extraction cannot do.

The history table — every version, which is what Chapter 20's SCD Type 2 is built from:

INSERT INTO silver.orders_history
SELECT order_id, op, lsn, source_ts_ms, before, after,
       LEAD(source_ts_ms) OVER (PARTITION BY order_id ORDER BY lsn)
         AS valid_to
  FROM bronze.orders_cdc;

Keep both. The current-state table is what most consumers want; the history table is where the intermediate states live, and it is the thing CDC gives you that nothing else does.

🔁 Idempotency Check — ORDER BY lsn DESC is not a total order, and the tie is not rare

Run the merge twice on the same batch and you should get the same table. It does, for the statement above — s.lsn > t.source_lsn guarantees it. Now run it twice on a batch containing two rows with the same key and the same lsn, and the two runs can disagree.

How a tie happens, and it is more ordinary than it sounds:

text a single transaction updating one row twice same commit LSN, two events a snapshot row and its first change event snapshot rows carry lsn = 0 or null a connector restart replaying the last batch the same event, twice a source whose LSN has lower resolution than its transaction rate MySQL binlog within one file position

ROW_NUMBER() must pick one, and with an incomplete ORDER BY it picks whichever the engine happened to read first. That is not deterministic across runs, across engines, or across a change in file layout — and a rebuild months later will silently choose differently.

The fix is one clause, and it is the difference between a pipeline that replays and one that merely reruns:

sql ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC, source_ts_ms DESC, _kafka_offset DESC)

Append a strictly increasing, always-present tiebreaker — the Kafka offset, the connector's sequence number, the file-and-position pair. Anything, so long as it is total.

Chapter 38's Case Study 2 is this omission, three years on: a rebuild from bronze produced a different silver.orders than the one in production, and the diff was 41 rows nobody could explain until somebody read the ORDER BY.

14.9 Schema Changes in a Stream

Debezium emits a schema change event when the source's DDL changes, and — more usefully — every message carries its schema, either inline or by a registry reference (Chapter 17).

What happens to your consumer depends on the change, and it maps onto Chapter 13 §13.7's taxonomy:

The advantage over batch: the change arrives as an event, in order, at the moment it happened, rather than being discovered when a downstream model behaves oddly. You can act on it in the pipeline rather than in a post-mortem.

14.10 Monitoring a CDC Pipeline

A CDC pipeline has more places to be silently wrong than a batch extract, because it is always running and because "nothing arrived" is a valid state. Six signals, and the order matters — the first three are about the pipeline, the last three about the data.

1. Slot retained WAL and active state. §14.6. The alert that fires at the crash.

2. Connector task state. Kafka Connect tasks fail individually and a failed task does not stop the connector — it stops that task. A connector reporting RUNNING with one task FAILED is capturing some tables and not others.

curl -s localhost:8083/connectors/kestrel-orders-cdc/status | jq '
  {connector: .connector.state,
   tasks: [.tasks[] | {id, state, trace: (.trace // "" | .[0:120])}]}'

Alert on any task not RUNNING, not on the connector's own state. This is the single most common CDC monitoring gap.

3. End-to-end lag. Not consumer lag — end-to-end: the difference between now and the source.ts_ms of the most recent event landed in bronze. Consumer lag tells you the consumer is keeping up with what it has been given; end-to-end lag tells you whether the whole chain — source, connector, Kafka, consumer — is keeping up.

SELECT EXTRACT(EPOCH FROM (now() - MAX(source_ts))) AS end_to_end_lag_seconds
  FROM bronze.orders_cdc;

4. Event mix. The ratio of c, u, and d events, compared to a trailing baseline. A sudden collapse in u events is either a quiet source or a broken capture, and the two look identical without the baseline.

5. Heartbeat freshness. If you have configured heartbeat.action.query (§14.12), the heartbeat table's timestamp is a direct liveness probe of the entire path — source → WAL → decoding → connector → Kafka → consumer → warehouse. A stale heartbeat means the chain is broken somewhere, and it is the one signal that covers every hop at once.

This is the most under-used signal in CDC monitoring. The heartbeat is usually configured for the slot-advancement reason and then never read.

6. Reconciliation against the source. Weekly, on row counts and on the measure that matters. CDC is not exempt from Chapter 1 §1.7's acceptance criterion; if anything it needs it more, because there is no batch run whose success or failure you can point at.

⚠️ Failure Mode — The connector that was RUNNING with a dead task

A connector captured five tables across three tasks. One task hit an unconvertible value in returns — a numeric with a scale the converter did not expect — and failed.

Kafka Connect reported the connector as RUNNING, because the connector was running. Two of three tasks were fine. The team's monitoring checked the connector state.

returns stopped updating. Nothing else did. The refunds figure in the 6am dashboard went flat — not to zero, which would have been noticed, but flat at its last value, which for a metric that moves slowly looked entirely plausible for six days.

Three lessons:

• Monitor tasks, not connectors. The connector-level state hides per-task failure by design.
• A metric that stops moving is harder to notice than one that goes to zero. Freshness monitoring on the table (Chapter 25 §25.3) catches this where volume monitoring does not.
• errors.tolerance: none was correct and insufficient. It made the task fail rather than skip the record, which is right — and nothing was watching the task.

🧪 Try It — watch a slot fall behind

bash cd part-03-ingestion/chapter-14-change-data-capture/code python slot_monitor.py --self-check python slot_monitor.py --json | python -m json.tool

Eight assertions over three slot states — abandoned, healthy, and filling. Read what each one claims before you read the code, then answer four questions:

1. Which slot is growing, and which is merely large? In a single sample they are indistinguishable; across two samples they are not. That is why the check takes --sample-seconds at all — the alert is on the derivative, not on the value.
2. Where does ~19.0 h come from? Retained bytes, growth rate, and the ceiling. That number, not the current size, is what belongs in the alert message, because it is the only one an on-call engineer can act on at 3 a.m.
3. Three of the eight assertions are about an owner, not about bytes. Why is an unparseable owner a finding? (§14.6's abandoned proof-of-concept slot.)
4. Which of the three slots would a RUNNING connector status have reported as healthy? All three — which is §14.10's whole argument.

Then set --sample-seconds to something small and run it against a real database. The gap between the ETA it computes and the one you would have guessed is the useful part.

14.11 When Not to Use CDC

Five conditions, and the first two are common enough that most teams meet at least one.

1. You cannot get replication privileges. A managed database with logical replication disabled, a vendor system, or a security policy. Common. Fall back to trigger-based CDC or to batch.

2. The table is small and immutable enough that a full load is simpler. §14.2's 📐 callout. products at 47,000 rows does not need this.

3. You need transactional consistency across many tables. CDC gives you per-row ordering. Reconstructing a multi-table transaction requires the transaction metadata topic and buffering, and if your consumer genuinely needs "these five tables as of one instant," a consistent snapshot is simpler.

4. Nobody can operate it. §14.6 is a real burden: a Kafka cluster, a Connect cluster, connectors, slots, snapshots, and a class of failure that can stop the source database. Chapter 5 §5.1's second operator is not optional here. If the honest answer to "who else can debug this at 3am" is nobody, that is a reason to wait.

5. The source is not a database. CDC reads a database log. A SaaS API, a file drop, or a message queue has no log to read — Chapter 12 §12.9's table.

🎓 Interview Angle — "When would you use CDC over a batch extract?"

The answer that stands out names the operational cost without being asked:

"CDC when the table is mutable, hard-deletes, or when I need intermediate states — a status that transitions between polls is one row to a batch extract and four events to CDC, and if I'm building an SCD Type 2 dimension those intermediate states are the data. It's also the only delete strategy that needs nothing from the source team, which matters when they've said no to soft deletes for good reasons.

What I'd raise before adopting it is the operational side. A replication slot guarantees the primary retains WAL until the consumer confirms it — which is what makes CDC reliable, and it means a consumer that stops will fill the primary's disk, and a Postgres primary with a full WAL disk stops accepting writes. So max_slot_wal_keep_size, an alert on inactive slots, and a monitor on retained WAL, before the first connector goes in.

And I'd plan the initial snapshot first, because on a large table that's what decides whether it's feasible at all — an incremental snapshot is slower than a locking one and it's the only one that's resumable."

The slot point is what demonstrates operational experience. Most candidates describe the happy path.

Operating CDC alongside the batch extract

The mixed approach from §14.2's callout — CDC on the mutable five, batch on the other seven — is the right design and it creates three problems that neither mechanism has alone. They are worth naming, because they arrive in the first month and are usually diagnosed as CDC problems.

1. The two mechanisms disagree about a moment

A batch extract reads a snapshot at 02:00. A CDC stream is continuous. So silver.orders (from CDC, current) and silver.products (from batch, as of 02:00) are consistent with each other only at 02:00, and a join between them at 09:00 mixes two points in time.

Usually harmless, and specifically harmful in one case: a foreign key to a row that does not exist yet. An order placed at 08:00 references a product created at 07:00, which the batch load will not see until tomorrow. The join drops the row, or produces a null dimension, depending on the join type — and both are silent.

-- the detection, and it belongs in the quality register (ch 23)
SELECT count(*) AS orphaned_orders
  FROM silver.orders o
  LEFT JOIN silver.products p USING (product_id)
 WHERE p.product_id IS NULL;
-- expect zero; a non-zero count that CLEARS overnight is this problem

"A non-zero count that clears overnight" is the signature, and it distinguishes this from a genuine referential integrity failure, which does not clear.

The fix is Chapter 20 §20.11's unknown member plus a nightly rebind, and the reason it belongs here is that the cause is architectural rather than a modelling oversight.

2. A schema change lands twice, differently

The batch extract's drift checker (§13.7) sees a new column on its next run. The CDC connector sees it as a DDL event, immediately, and — depending on configuration — either propagates it, ignores it, or fails.

So the same upstream change produces two different downstream behaviours at two different times, and reconciling them is somebody's afternoon. Set the connector's schema-change handling explicitly and write down which of the two is authoritative; the default is rarely what you want and is never what the batch side does.

3. Two watermarks, and only one is trustworthy

The batch extract stores a timestamp; the connector stores an LSN. They are not comparable, they advance independently, and a rebuild that needs "everything as of Tuesday" has to reason about both.

The practical resolution is to record both, per run, in one table:

run_id | mechanism | table    | position      | position_kind | at
───────┼───────────┼──────────┼───────────────┼───────────────┼──────────
 4182  | batch     | products | 2026-11-27 02:00 | updated_at | 02:04:11
 4182  | cdc       | orders   | 0/1A2B3C4D       | lsn        | 02:04:11

One table, two position kinds, and a timestamp that lets you correlate them. It is four columns and it is the difference between a rebuild you can reason about and a rebuild you attempt twice.

What this costs, stated honestly

Two ingestion mechanisms is two sets of failure modes, two monitoring surfaces, and a boundary new engineers have to learn. §14.2's callout says this and is right that it is smaller than the alternative — but "smaller" is not "small", and the three problems above are the shape of it.

The threshold at which the mixed approach stops being worth it is when the batch side's tables start mutating. A reference table that becomes editable in the application is a table that has quietly moved from column seven to column five of §14.2's split, and nothing announces the move.

💸 Cost Check — what CDC actually costs, on the frozen rate card

Exercise 14.16 prices the volume at about a dollar a year. Here is the rest of the bill, because the volume is the part people worry about and the smallest line on the page.

```text STORAGE 12,000,000 CDC events/yr x ~400 bytes = 4.47 GB $1.23 / year with REPLICA IDENTITY FULL (~700 bytes) = 7.82 GB $2.16 / year

COMPUTE Kafka Connect worker, 2 nodes, always on 2 x 720 h x $2.400 $3,456.00 / month the bronze consumer, sharing the streaming cluster included the silver merge, ~8 min nightly on a Medium wh $32.00 / month

SOURCE-SIDE WAL volume: +31% on orders with REPLICA IDENTITY FULL -- paid by the source team, in disk and in replication bandwidth a slot that must be monitored engineer attention, not dollars ──────────────── about $3,488 / month, of which $3,456 is a worker that is always on ```

99.1% of the bill is the connector being up, and it is up because CDC is continuous — which is §14.2's fourth point (lower, steadier load) seen from the invoice rather than from the source database.

Three consequences worth drawing out.

The marginal cost of adding a table to CDC is nearly zero. The worker is already running. So the decision about which tables to capture is an operational one, not a cost one — which is exactly what §14.2's design-decision callout argues on entirely different grounds.

And the fixed cost makes CDC expensive for a small platform. $3,456 a month to capture five tables at Kestrel's volume is defensible; the same $3,456 to capture two tables at a tenth the volume is not, and that is the arithmetic behind "batch until it hurts."

Finally, the source-side cost is real and is not on your bill. A 31% WAL increase is disk, bandwidth, and backup volume on somebody else's system (§7.1). It should be in the request, with the number, because a cost you do not mention is a cost you will be told about later.

🔐 Privacy & Governance — CDC captures the deletion, and then keeps it forever

This is the sharpest privacy irony in the book. CDC's headline feature is that it captures deletes — and the way it captures them is by writing an immutable, retained record containing the deleted row.

text a customer exercises their right to erasure -> the application DELETEs the row -> the WAL records the deletion, WITH the before image -> Debezium emits an event containing the person's data -> the event lands in bronze.customers_cdc, append-only, retained for two years by ADR-003 -> the Kafka topic retains it too -> and the CDC event is the ONE record that is guaranteed to contain them, because a delete's whole purpose is to say what was removed

REPLICA IDENTITY FULL makes this worse and is otherwise correct (§14.7): the fuller the before image, the more useful the stream and the more complete the retained copy of the person.

Three things to do about it, and the first is not optional:

Put the CDC bronze topic and table in the deletion manifest. They are derived stores holding personal data, they are exactly the kind that a hand-written manifest omits, and Chapter 31's generated manifest finds them only if the tables carry classification tags. Tag them at creation.

Set a retention on the topic, and make it shorter than you think. Exercise 15.14's compact,delete with a 30-day window bounds the obligation. A compacted CDC topic with no delete policy retains one record per key forever, and for a deleted customer that record is a tombstone whose previous version is the person.

And decide, explicitly, what a deletion event's payload should contain. Debezium can be configured to emit the key only. That loses §14.2's first advantage and it may be the right trade for a table whose deletions are erasures rather than business events — and it is a decision somebody should make rather than inherit from a default.

The general shape, which recurs: a mechanism designed to make history complete is a mechanism that makes forgetting hard. Chapter 36's event sourcing has the same tension and the same three resolutions — bound the retention, crypto-shred, or accept that the log is not the system of record.

🔎 Read the Plan — the connector's status is not the plan

RUNNING is a claim about a process. Here is what to read instead, in the order that resolves an incident fastest.

```bash

1. TASKS, not the connector. A connector is RUNNING with a dead task.

curl -s localhost:8083/connectors/kestrel-orders/status | jq

{"connector":{"state":"RUNNING"},

"tasks":[{"id":0,"state":"RUNNING"},

{"id":1,"state":"FAILED","trace":"..."}]} <- THE ANSWER

```

sql -- 2. the SLOT: is it active, and is it growing? SELECT slot_name, active, active_pid, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS unflushed FROM pg_replication_slots;

sql -- 3. the HEARTBEAT: a liveness probe of the WHOLE chain SELECT max(ts), now() - max(ts) AS staleness FROM debezium_heartbeat;

Read them in that order and each one eliminates a class of cause.

A FAILED task with RUNNING at the connector level is §14.10's incident: some tables are being captured and some are not, silently, and the connector's own status says everything is fine.

active = false on the slot means nothing is consuming, the retained WAL is growing, and you have a deadline rather than a problem.

retained large but active = true and unflushed small means the connector is reading and the consumer downstream is not acknowledging — a different failure with the same symptom, and the two columns distinguish them.

And a stale heartbeat with a healthy connector is the case that catches everything else: the chain from WAL to warehouse is broken somewhere between the connector and the sink, and the heartbeat is the only signal that spans all of it (§14.11).

The habit worth forming: never diagnose a CDC problem from the connector's status alone. It is the one signal that is green in every failure this chapter describes.

14.12 The Kestrel CDC Pipeline

kestrel_app (PostgreSQL 16, wal_level=logical)
   │  publication: kestrel_cdc  (orders, order_items, payments, returns, inventory)
   │  slot: cdc_orders_dataeng  (named with owner and purpose)
   ▼
Debezium PostgreSQL connector (Kafka Connect)
   │  REPLICA IDENTITY FULL on orders, customers
   │  incremental snapshot, 10,000-row chunks
   ▼
kestrel.orders.cdc.v1        6 partitions, key = order_id, compacted + 7-day
kestrel.inventory.cdc.v1     6 partitions, key = product_id:warehouse_id, compacted
   │
   ▼
consumer → bronze.orders_cdc      (append-only, every event, partitioned by ingest_date)
   │
   ├──▶ silver.orders             MERGE, latest LSN per key, deletes applied
   └──▶ silver.orders_history     every version, for SCD2 (Chapter 20)

# platform/ingest/cdc/debezium-orders.json — the settings that matter
{
  "name": "kestrel-orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "plugin.name": "pgoutput",
    "slot.name": "cdc_orders_dataeng",
    "publication.name": "kestrel_cdc",
    "table.include.list": "public.orders,public.order_items,public.payments,public.returns,public.inventory",

    "snapshot.mode": "initial",
    "incremental.snapshot.chunk.size": "10000",

    "topic.prefix": "kestrel",
    "tombstones.on.delete": "true",

    "heartbeat.interval.ms": "10000",
    "heartbeat.action.query":
      "INSERT INTO cdc_heartbeat (ts) VALUES (now()) ON CONFLICT (id) DO UPDATE SET ts = now()",

    "errors.tolerance": "none",
    "errors.log.enable": "true"
  }
}

Two of those settings prevent specific incidents and are worth understanding.

heartbeat.interval.ms plus heartbeat.action.query. This is the setting that stops a low-traffic database from filling its disk, and it is deeply non-obvious.

A replication slot's restart_lsn only advances when the connector confirms a position, and it confirms based on messages it has received. If the tables you are capturing are quiet but the database as a whole is busy, WAL accumulates for changes you do not care about and the slot never advances past them. The heartbeat writes to a table that is captured, forcing a message through and letting the slot advance.

Without it, a connector on a low-volume table can fill the disk of a high-volume database.

errors.tolerance: none. The default in some configurations is to skip records that fail to convert. Skipping a record is silent data loss — Chapter 2's fail-loudly principle. Fail, and handle it.

🧱 Kestrel Platform — Increment 14: CDC

(a) Add Debezium and Kafka Connect to docker-compose.yml. Create the publication and the slot, named with owner and purpose.

(b) Configure the connector for orders and order_items. Set REPLICA IDENTITY FULL on orders and confirm from a captured event that before is fully populated — this is the check most people skip and the misconfiguration most people have.

(c) Land events to bronze.orders_cdc, append-only, partitioned by ingest_date. Every event, nothing collapsed.

(d) Build silver.orders with the §14.8 merge. Then prove idempotency: replay the same Kafka offsets and assert the table is unchanged.

(e) The one that matters. Configure max_slot_wal_keep_size, then stop the connector and watch pg_replication_slots.retained_bytes grow. Leave it long enough to see the number move. Then restart and watch it drain. Record both numbers.

(f) Write the slot monitoring query into platform/orchestrate/ as a scheduled check, with the active = false alert.

Part (e) is the exercise. Watching the disk fill in a controlled setting is the only way to take §14.6 seriously, and it takes ten minutes.

14.13 Summary

CDC is reading the log the database was already writing. Ordered, complete, and durable — because crash recovery depends on it. Logical decoding translates the physical log into row-level events, which is why wal_level = logical had to be set in Chapter 5, nine chapters before it was used.

Four things CDC solves that batch cannot, each with a price: deletes arrive as events, and it is the only delete strategy requiring nothing from the source team; the watermark disappears, taking all four of Chapter 13's lies with it, in exchange for an LSN you must not lose; every intermediate state is captured, which is the data for status funnels and SCD2, at the cost of volume; and load on the source is lower and steadier, though it never stops.

Use CDC for mutable, high-value, inconvenient-to-reload tables, and batch for the rest. Kestrel runs CDC on five tables and full loads on seven. The mixed approach costs two mechanisms to operate; that is smaller than the alternative.

Three kinds of CDC. Log-based is what the term means. Trigger-based is the fallback when you cannot get replication access, and it doubles the source table's write cost. Query-based is Chapter 13 with a marketing name; it sees neither deletes nor intermediate states.

The Debezium envelope has four things that matter: op, the before/after images, source.lsn (your ordering and deduplication key), and source.ts_ms versus the top-level ts_ms — event time against processing time in one message.

⚠️ REPLICA IDENTITY defaults to DEFAULT, which puts only the primary key in before. Every SCD2 build and every audit silently gets nothing. FULL fixes it and raised Kestrel's WAL generation 31% on orders, so decide per table. This is the most common Debezium misconfiguration and its symptom is not an error.

Plan the snapshot before the stream — it is what decides feasibility on a large table and every tutorial skips it. A locking snapshot locks production; a consistent snapshot holds a transaction open for its whole duration, which is Chapter 7 §7.3's bloat problem and the mechanism that took down checkout. An incremental snapshot is 75% slower and the only operationally acceptable option, because restartability and bounded impact beat speed when the alternative restarts from scratch.

A replication slot's guarantee is also its danger. A stopped consumer accumulates WAL without bound, and a PostgreSQL primary with a full WAL disk stops accepting writes — the storefront stops. Four controls, all required: max_slot_wal_keep_size (a broken stream is better than an outage), a retained-WAL monitor, an alert on active = false — the highest-value alert in the chapter, because it fires at the crash rather than when the disk is filling — and dropping unused slots. Name slots with an owner and a purpose; an abandoned debezium_poc filled a disk eleven weeks later and took forty of fifty minutes to diagnose.

Consuming: order is per-key, because Debezium keys by primary key and Kafka orders within a partition; changes to different rows are not ordered relative to each other. Deduplicate on LSN, not timestamp — two changes can share a millisecond and cannot share an LSN. And handle the tombstone, which exists for log compaction and which naive consumers null-pointer on.

The merge into current state needs three things: ROW_NUMBER() ORDER BY lsn DESC to collapse, s.lsn > t.source_lsn to make it idempotent, and an op = 'd' branch. Keep a history table alongside — that is where the intermediate states live and it is what nothing else gives you.

Two Debezium settings prevent specific incidents. heartbeat.action.query forces a message through on a quiet captured table so the slot can advance — without it, a connector on a low-volume table can fill the disk of a high-volume database. And errors.tolerance: none, because skipping an unconvertible record is silent data loss.

Five reasons not to use CDC: no replication privileges (common), the table is small enough that a full load is simpler (common), you need cross-table transactional consistency, nobody can operate it — the second operator is not optional here — or the source is not a database.

What's next

Chapter 15 is Apache Kafka itself: producers, consumers, topics, partitions, offsets, consumer groups, and delivery semantics. This chapter used Kafka as a pipe; the next one opens it. It is also where the clickstream — 14 million events a day, 2,900 per second at peak — stops being a number in a table and becomes a producer you write and a consumer you have to keep from falling behind.