Chapter 13 — Key Takeaways (Batch Ingestion)

The page for writing an extract, and for the night one loses rows.

The seven steps

1. DECIDE the range      → watermark bugs (§13.4)
2. ACQUIRE a lease       → two runs interleaving          ← omitted
3. READ in chunks        → source outages (Ch. 7 §7.3)
4. LAND raw              → parse-at-landing (Ch. 9 §9.4)
5. VERIFY                → an empty result you can't read ← omitted
6. COMMIT the range      → watermark ahead of data
7. RECORD a manifest     → unanswerable questions later   ← omitted

⚠️ The watermark and the data must commit together. If the watermark advances and the write fails, the range is permanently skipped and nothing errors.

Full vs. incremental

Default to full. A full load is idempotent by construction, handles deletes free, self-heals past bugs, and needs no watermark — four categories of incident that simply do not exist.

Three conditions force incremental: 1. It no longer fits the window 2. It loads the source unacceptably ← usually fires first 3. You need history the source does not keep

Kestrel keeps 4 of 6 tables on full loads at 2.4M orders/year, and alerts when a full load exceeds half its window — which turns a future emergency into scheduled work.

Change detection

Strategy Catches updates Catches deletes
Timestamp column if honest no
Monotonic id no — inserts only no
Version counter yes no
Full compare (key + row hash) yes yes
Log-based CDC yes yes

Full compare is under-considered: needs nothing but a stable key, scans two columns not the whole table, and is frequently right for a mid-size table with no reliable updated_at.

The four ways updated_at lies

# Lie Detect by
1 Set at transaction start, not commit rows below a passed watermark
2 Not maintained on every write path row-hash mismatch on a sample
3 Granularity too coarse GROUP BY updated_at HAVING COUNT(*)>1
4 Moves backwards (clock skew, manual fixes) track max seen; alert on regressions

A real source exhibits several at once. Kestrel's combined loss was ~0.005% — invisible to volume monitoring, fatal to reconciliation. That asymmetry is why the platform's acceptance criterion is reconciliation.

The three properties of a safe watermark

  1. Upper bound from an AUTHORITY — now() from the primary, never max() of what you read
  2. Lower bound OVERLAPS — trades loss for duplicates, absorbed by an idempotent write
  3. Advances even when no rows found — the bounded range makes this automatic
upper = primary.execute("SELECT now() - interval '30 seconds'")   # 1
lower = read_watermark(t) - OVERLAP                                # 2
rows  = replica.execute("... WHERE ts >= %s AND ts < %s", [lower, upper])
land_idempotently(rows)
write_watermark(t, upper)                                          # 3

Half-open [lower, upper). > on both ends drops boundary rows; >= on both duplicates them.

Deletes

A hard delete leaves no trace. Absence is indistinguishable from not-in-this-batch.

Approach Notes
Soft deletes in the source the right answer — and it has a refusal rate
Periodic key reconciliation what most pipelines do; detection delay = the interval
Full loads free
CDC the only strategy requiring nothing from the source team

⚠️ Overlap window + tombstone reconciliation = resurrection. A row tombstoned at 02:00 is re-inserted by the 02:00 overlap, flickering in and out nightly. Two independently correct mechanisms, jointly wrong, invisible in each one's own tests. Fix: version the rows. A tombstone is a version; the highest version wins — correct including legitimate restores.

Files

  1. Never process a file you saw appear — marker, rename from .tmp, or stability check
  2. Filename alone is insufficient — hash the content
  3. Alert on absence — a file that never arrives produces no error anywhere
  4. Validate the header against an expected schema
  5. Checksum for corruption

Schema drift

Change Effect on SELECT * Severity
Column added appears unannounced benign
Column dropped downstream breaks loud → cheap
Column renamed drop + add; old column nulls silent for a while
Type widened fine benign
Type narrowed/changed cast failures or silent coercion the dangerous one

Keep SELECT * in bronze and add a schema check beside it. Ten minutes, and it converts a six-week mystery into a same-day notification. The change is announced, not blocked.

⚠️ A permissive cast setting is a decision to fail silently, made once, applying forever.

Throughput

  1. Chunk and parallelize — but concurrency multiplies load on the source
  2. Choose the chunk key for even distribution — placed_at gives Black-Friday-sized chunks
  3. Push the projection down (a documented exception to "land everything")
  4. Read from a snapshot

The escalation sequence: full → incremental → parallel → dedicated replica → CDC → snapshot- then-stream. The last two are architecture changes, not performance fixes.

Backfills — worst incident record in this book

  1. A different program from an incremental load, even sharing code
  2. Explicitly idempotent — delete-insert, merge, or partition replace. Never append.
  3. Chunked and resumable
  4. Rate-limited — node-hours are node-hours, so throttling is free in money and valuable in risk

Restartable ≠ idempotent

Achieved by Prevents
Idempotent delete-insert, merge, partition replace duplicates
Restartable chunk-level checkpointing redoing hours of work

Restartability decides what to redo; idempotency makes redoing harmless. You need both, and a chunk manifest gives you both.

The one query to run daily on every incremental table

SELECT COUNT(*) FROM <source> s
 WHERE NOT EXISTS (SELECT 1 FROM <bronze> b WHERE b.<key> = s.<key>)
   AND s.<watermark_col> <= (SELECT watermark FROM extract_state
                              WHERE tbl = '<table>');
-- expect 0. Anything else is PERMANENTLY skipped.

The <= is the whole point — it asks whether anything sits at a position you have already passed. One query finds all four lies, because it does not care why a row was missed.

And the design-review question

What breaks at 10× the write rate?

Two minutes. Chunk boundaries, skew, missing backpressure, retry storms, lock contention — all invisible in testing, all firing at peak. A load-scaled failure can only be found by generating the load.