Exercises: Batch Ingestion

Part D is the most important set in Part III. Parts (c), (d), and (e) of 13.20 ask you to prove properties by breaking things, which is the only way to know you have them.

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

Part A — Warm-ups ⭐

13.1 † Name the seven steps of a batch extract. Which three do people omit, and what does each omission cost?

13.2 Why must the watermark and the data commit together? Describe what happens if the watermark commits first and the write fails.

13.3 † Give four things a full load gets right that an incremental load does not.

13.4 State the three conditions that force incremental extraction. Which one usually fires first?

13.5 † Name the five change-detection strategies. Which catches deletes without CDC, and what does it cost?

13.6 Why does ranging over an auto-increment primary key silently miss updates?

13.7 † Name the four ways updated_at lies. Which one produces wrong values rather than missing rows?

13.8 State the three properties of a safe watermark.

13.9 † Why is a hard delete undetectable by any timestamp strategy?

13.10 What is the difference between restartable and idempotent? Give an operation that is one and not the other.

Part B — Standard ⭐⭐

13.11 For each Kestrel table in §13.2's table, write the one-sentence justification for its strategy. Then pick the one you most disagree with and argue the other side, with arithmetic.

13.12 † Write the four detection queries from §13.4 — one per lie — against the Kestrel database. Run them. Report what you find. At least one should return a non-zero result, because the seed generator includes a timestamp in the future.

13.13 §13.4's ⚠️ callout says a watermark bug produces a loss rate invisible to volume monitoring and fatal to reconciliation. Quantify it: at Kestrel's 6,575 orders/day and a 0.005% loss rate, how many rows a night, a month, a year? What monthly revenue variance does that produce at $75.83 AOV, and would a 0.5% reconciliation tolerance catch it?

13.14 † Implement the full-compare change detection from §13.3 for customers. Compute a row hash on both sides, compare, and produce the insert/update/delete sets. Measure how long it takes at --scale small. Then estimate it at 1.9 million rows and say whether it is viable.

13.15 §13.5's 🔁 callout describes an overlap window resurrecting a tombstoned row. Write the sequence as a timeline with specific timestamps. Then implement fix 3 (version the rows) and write the test that proves the resurrection cannot happen.

13.16 † Write the schema-drift checker from §13.7. Run it against Kestrel, then add a column to the source and run it again. Record what it says. Then rename a column and run it again — note that it reports an add plus a remove, and say why it cannot tell the difference.

13.17 §13.8 says chunking orders by placed_at produces uneven chunks because of Black Friday. Quantify it: using the seed's seasonal curve, what are the smallest and largest daily chunks, and what is the ratio? Then compute the same for chunking by order_id. Which would you use?

Part C — Deeper ⭐⭐⭐

13.18 §13.10 claims throttling a backfill is "free in money and valuable in risk," on the grounds that node-hours are node-hours. Find the case where that is false — where a slower backfill genuinely costs more — and state the conditions.

13.19 † Design the reconciliation that would catch a §13.4 watermark bug within one day. Specify what you compare, at what grain, with what tolerance, and how you avoid the trap Chapter 4's Case Study 2 fell into (a threshold that hid twenty-six nights of one-signed variance).

13.20 The 🏭 callout in §13.9 describes a pipeline whose dependency was a person producing an Excel file. Design the catalogue entry for a human dependency: what fields it needs, who maintains it, and how you would find all of them at an organization you have just joined.

13.21 † §13.7 says bronze should keep SELECT *, and §13.8 says you may push a projection down to avoid landing a 40 KB blob you do not need. Reconcile these. Write the rule that governs the exception, precise enough that a reviewer could apply it.

Part D — The Kestrel Platform ⭐⭐⭐

13.22 — Increment 13: the batch extractor.

(a) Implement platform/ingest/batch/extract_postgres.py with all seven steps from §13.1. Write the helper functions yourself; §13.12's structure is the contract.

(b) Create extract_state, extract_chunks, and the lease mechanism.

(c) Prove restartability. Run it, SIGKILL it at roughly 50%, restart. Confirm from extract_chunks that completed chunks were skipped. Record how many.

(d) Prove idempotency. Run the complete extract twice. Assert the bronze row count and the revenue sum are identical. This is a different test from (c) and you need both.

(e) Prove the lag guard. Simulate replica lag and confirm the extractor refuses and says why. Record the exact message — a guard whose message does not explain itself gets disabled by the next person.

(f) Add the schema-drift checker from §13.7 and wire it into the extract.

13.23 † Add platform/ingest/batch/backfill.py, obeying §13.10's four rules: a distinct program, explicitly idempotent, chunked and resumable, and rate-limited with a configurable chunks-per-minute default that is deliberately low.

Then write the guard that Chapter 1's incident needed: the backfill refuses to run if it detects it is scheduled rather than invoked manually, and the refusal names the review it requires.

Reflection

A. §13.2 argues for defaulting to full loads and deferring incremental. That is the opposite of most engineering instinct. What makes deferring feel wrong, and is the feeling ever right?

B. Every failure in this chapter is silent. Count how many of the last ten bugs you personally found were found by an error message versus by someone noticing a number was wrong. What does the ratio suggest about where to invest?