Exercises: Change Data Capture

Part D(e) — watching a replication slot fill a disk in a controlled setting — is ten minutes and it is the only way to take §14.6 seriously. Do it even if you skip everything else.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

14.1 † What four properties does a database's write-ahead log have that make it suitable for CDC?

14.2 Why does CDC need logical decoding rather than reading the WAL directly?

14.3 † Name the four problems CDC solves that batch cannot, and the price of each.

14.4 Which delete strategy requires nothing from the source team, and why does that matter?

14.5 † Distinguish log-based, trigger-based, and query-based CDC. When is trigger-based the right choice, and what does it cost the source?

14.6 Name the four things in a Debezium envelope that matter. Which one is your deduplication key, and why not the timestamp?

14.7 † What does REPLICA IDENTITY DEFAULT put in the before image, and what breaks silently because of it?

14.8 Name the four controls required to operate a replication slot safely. Which one fires at the moment of the crash?

14.9 † Why is an incremental snapshot preferred over a consistent snapshot for a large table, despite being slower?

14.10 Give three of the five conditions under which CDC is the wrong choice.

Part B — Standard ⭐⭐

14.11 For each of Kestrel's twelve source tables, decide CDC or batch, using §14.2's 📐 criteria. Justify each in one sentence. Then compare your split to Kestrel's five-and-seven and explain any difference.

14.12 † Set REPLICA IDENTITY FULL on orders in your local Kestrel database and measure the WAL impact: record pg_current_wal_lsn() before and after running a fixed update workload, with DEFAULT and with FULL. Report the percentage increase and compare to Kestrel's measured 31%.

14.13 Write the consumer that turns bronze.orders_cdc into silver.orders, implementing all three load-bearing elements of §14.8's merge. Then write the test that proves idempotency by replaying the same events.

14.14 † §14.11 explains that heartbeat.action.query prevents a connector on a low-volume table from filling a high-volume database's disk. Explain the mechanism in your own words, then design the experiment that demonstrates it locally: what you capture, what you write to elsewhere, and what you measure.

14.15 Debezium emits two messages for a delete. Write a consumer that handles both correctly, then write the version with the common bug (assuming every message has a value) and show the exact exception it raises.

14.16 † Compute the volume implication of §14.2's third point. A Kestrel order transitions pending → paid → picked → shipped → delivered over about four days. At 2.4M orders a year, how many CDC events does orders generate annually versus how many rows an hourly batch extract would capture? At ~400 bytes per event, what is the annual bronze volume difference, and what does it cost at the frozen S3 rate?

14.17 Take §14.9's schema-change table and, for each row, write what your bronze consumer should do and what your silver model should do. Where they differ, say why.

Part C — Deeper ⭐⭐⭐

14.18 §14.7 notes that changes to different rows are not ordered relative to each other, even within one source transaction. Construct a Kestrel scenario where this produces a visibly wrong result — an order and its payment arriving out of order — and design the fix. State what the fix costs.

14.19 † Design the full operational runbook for the slot-fills-disk scenario. It must cover: detection (which alert fires first), triage (how you tell this from other disk-full causes), the immediate action, the recovery, and — the hard part — the decision about whether to drop the slot, which breaks CDC and requires a re-snapshot. State the threshold at which you would drop it.

14.20 §14.10's fourth condition is "nobody can operate it." Write the honest assessment for a team you know or can imagine: list every component CDC adds, and for each, name who could debug it at 3am. Then state whether you would adopt CDC and why.

14.21 † Compare Debezium's incremental snapshot to the DBLog design it is based on. What problem do the low and high watermark events solve, and what would go wrong without them? Cite the source.

Part D — The Kestrel Platform ⭐⭐⭐

14.22 — Increment 14: CDC.

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

(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.

(c) Land every event to bronze.orders_cdc, append-only, partitioned by ingest_date. Nothing collapsed.

(d) Build silver.orders with §14.8's merge, then prove idempotency by replaying offsets.

(e) Watch the disk fill. Set max_slot_wal_keep_size. Stop the connector. Run a write workload. Watch pg_replication_slots.retained_bytes grow — leave it long enough to see the number move meaningfully. Then restart and watch it drain. Record both numbers and how long each took.

(f) Add the slot monitoring query as a scheduled check with the active = false alert.

14.23 † Write platform/ingest/cdc/slot_monitor.py: reports every slot with its retained WAL, active state, owner (parsed from the name), and the projected time until it hits max_slot_wal_keep_size at the current WAL generation rate.

That last field is the one that makes it actionable. "Slot at 41 GB" prompts nothing; "slot will be invalidated in 6 hours at the current rate" prompts a response.

Reflection

A. §14.6 describes a data pipeline component that can stop a storefront. Does that change how you feel about adopting CDC? What would you want in place before you were comfortable?

B. The chapter recommends CDC for five tables and batch for seven, deliberately running two mechanisms. Where else in engineering is a mixed approach better than uniformity, and what makes the difference?