Case Study 1: The Extract That Was Right Until Black Friday

"It had been correct for four hundred and eleven nights. Then it lost nine hundred rows in one, and the reason it lost them was the reason it had been correct."

Executive Summary

Kestrel's customers extract used a watermark on updated_at with a > comparison. It ran nightly for fourteen months without losing a row.

On Black Friday 2025 it lost 914 rows, and on Cyber Monday another 402. The mechanism was §13.4's third lie — second-level timestamp granularity — which is harmless at 6,575 writes a day and is not harmless at 41,300.

This case study is about a failure whose probability scales with load, which is a category worth recognizing because the day it fires is the day you can least afford it. It is also about the distinction between > and >= in a watermark, which is the smallest possible code change and the difference between two failure modes.

Skills applied: watermark granularity (§13.4, lie 3); the three safe-watermark properties (§13.4); overlap windows and idempotent writes (§13.4, Chapter 4 §4.5); peak-to-average ratios (Chapter 3 §3.5).

Background

The extract, written in 2024 and reviewed at the time:

watermark = read_watermark("customers")
rows = replica.execute(
    "SELECT * FROM customers WHERE updated_at > %s ORDER BY updated_at",
    [watermark])
land(rows)
write_watermark("customers", max(r["updated_at"] for r in rows) if rows
                             else watermark)

Three things about it were correct, which is why it passed review:

  • It reads a replica, not the primary.
  • It orders by the watermark column.
  • It handles the empty case without the else now() bug that Chapter 4's Case Study 2 describes.

The one thing wrong is invisible at normal volume: updated_at is a TIMESTAMP with one-second precision, and the comparison is >.

Why it did not matter for fourteen months. At 6,575 orders a day and a similar rate of customer updates, roughly 0.08 writes per second. Two customer rows sharing an updated_at second is uncommon; three is rare. When the extract read up to 2025-06-14 03:41:07 and stored that as the watermark, the probability that another row also carried 03:41:07 and had not been read was small.

Small, and not zero. It had almost certainly lost a handful of rows over the fourteen months, and nobody had a check that would have seen it.

The Problem

Black Friday: 41,300 orders, 6.28× the daily average, concentrated into a few hours. Customer updates — address changes at checkout, marketing opt-ins, new registrations — spiked with them.

At the peak, roughly 3.4 customer writes per second.

 updated_at            rows sharing this second
 ---------------------  ------------------------
 2025-11-28 14:22:07             4
 2025-11-28 14:22:08             3
 2025-11-28 14:22:09             5
 ...

The extract runs hourly. Each run reads up to some second, stores it, and the next run starts strictly after it.

run at 15:00
  reads ... up to 2025-11-28 14:22:09
  the source has FIVE rows at 14:22:09; the query returned three of them,
  because the chunk boundary fell inside that second
  watermark := 2025-11-28 14:22:09

run at 16:00
  WHERE updated_at > '2025-11-28 14:22:09'
  the two unread rows at 14:22:09 are excluded. Forever.

914 rows across the day. No error. Row counts were up — it was Black Friday, everything was up — so nothing looked wrong.

⚠️ Failure Mode — A bug whose probability scales with load

This is a category worth naming, because it has a property that makes it especially dangerous: it fires on the day you can least afford it.

The probability of two rows sharing a timestamp second scales with the square of the write rate (roughly, by the birthday-problem argument). At 6.28× the write rate, collisions are far more than 6.28× more likely.

Other members of the same family, all of which appear elsewhere in this book:

  • Chunk boundaries falling inside a group — this incident.
  • Skew that only manifests at volume — Chapter 4's Case Study 1.
  • A queue with no backpressure — fine until the producer outruns the consumer, Chapter 4 §4.7.
  • A retry storm — harmless at low concurrency, self-sustaining at high, Chapter 4 §4.7.
  • Lock contention — negligible until it is not.

The shared property: they are all invisible in testing, because tests run at low volume, and they all fire during peak, when the business impact is highest and the team's attention is most divided.

The general defense is to reason about the failure at 10× rather than to test at 1×. For every pipeline, ask: what breaks if the write rate is ten times higher? It is a design-review question and it takes two minutes.

The Analysis

Detection came eleven days later, from the monthly reconciliation — which found a customer count in the warehouse 914 lower than the source for November.

That the reconciliation caught it at all is worth noting: a row-count monitor could not have. Warehouse customer counts rose sharply on Black Friday, as expected. A 914-row shortfall inside a day that added tens of thousands is not visible as a volume anomaly. Only a comparison against the source finds it.

Diagnosis took ninety minutes once someone looked, using §13.4's third detection query:

SELECT updated_at, COUNT(*)
  FROM customers
 WHERE updated_at::date = DATE '2025-11-28'
 GROUP BY 1 HAVING COUNT(*) > 1
 ORDER BY 2 DESC LIMIT 10;

 updated_at            | count
-----------------------+-------
 2025-11-28 14:22:09   |     5
 2025-11-28 14:19:44   |     5
 2025-11-28 13:58:02   |     4
 ...

Then, decisively:

-- rows in the source, absent from bronze, at a timestamp we have passed
SELECT COUNT(*) FROM customers c
 WHERE NOT EXISTS (SELECT 1 FROM bronze.customers b
                    WHERE b.customer_id = c.customer_id)
   AND c.updated_at <= (SELECT watermark FROM extract_state
                         WHERE tbl = 'customers');
-- 1,316

1,316 rows total — 914 from Black Friday, 402 from Cyber Monday.

🔎 Read the Plan — The query that finds watermark loss

That last query is the single most useful diagnostic in this chapter, and it is worth having in the runbook for every incrementally extracted table:

sql 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 a row your extract has permanently skipped.

The <= is the whole point. It asks: is there anything in the source, at a position I have already passed, that I do not have? By definition the extract will never look at those rows again, so any non-zero result is permanent loss.

Run it daily. It is cheap — one anti-join on an indexed key — and it is the only check that finds all four of §13.4's lies with one query, because it does not care why a row was missed.

The Decision

The fix is three lines, and the team considered two options before choosing.

Option A — change > to >= and deduplicate. Re-reads the boundary second on every run, producing duplicates that the idempotent write absorbs.

Option B — a composite watermark, (updated_at, customer_id), ordering and comparing on the pair. Exact, no duplicates, and it requires that the ordering be stable and that the comparison be expressible — which for a composite means a row-value comparison the source must support.

They chose A, plus the overlap window, plus the bounded range. The reasoning:

Option B is exactly correct and it depends on the source supporting row-value comparison, on the secondary key being stable, and on nobody later changing the ORDER BY. Option A is approximately correct in a way that cannot fail, because it deliberately re-reads and relies on a property — idempotency — that we need anyway for four other reasons.

That is the same trade as Chapter 4 §4.5's at-least-once-plus-idempotency, appearing at the row level rather than at the message level. Prefer the fix that leans on a property you already require.

The corrected extract, with all three of §13.4's properties:

upper = primary.execute(                                 # property 1: an authority
    "SELECT now() - interval '30 seconds'").fetchone()[0]
lower = read_watermark("customers") - timedelta(minutes=5)   # property 2: overlap

rows = replica.execute(
    "SELECT * FROM customers "
    " WHERE updated_at >= %s AND updated_at < %s "        # half-open, and >=
    " ORDER BY updated_at, customer_id",
    [lower, upper])

land_idempotently(rows)                                   # absorbs the duplicates
write_watermark("customers", upper)                       # property 3: the RANGE

The >= on the lower bound and the < on the upper are the specific change. A half-open interval [lower, upper) means a row exactly at upper is read by the next run, never by both and never by neither — which is the property the original > comparison lacked at both ends.

What Happened

The 1,316 lost rows were recovered by a bounded backfill over the affected dates — possible because customers retains full history, and it is worth noticing that this recovery was again a property of the source, not of the platform (Chapter 4's Case Study 2 made the same point).

Three controls were added:

1. The watermark-loss query, from the 🔎 callout, run nightly on every incrementally extracted table, alerting on any non-zero result. It has fired twice since — both times on orders, both times because a manual data fix had written rows with a backdated updated_at (§13.4's lie 4).

2. A peak-multiplier review. Every scheduled pipeline is now reviewed against the question "what breaks at 10× the write rate?" — a two-minute design-review item. The first pass through existing pipelines found three more timestamp-granularity issues and one unbounded in-memory buffer.

3. Black Friday is a rehearsal, not a surprise. Kestrel now runs a load test at 8× normal write rate against a staging copy in early November. The 2026 rehearsal found two new problems, both introduced during the year, both fixed before the day.

That third control is the one the team considers most valuable, and it is the least technical. A failure whose probability scales with load can only be found by generating the load.

Lessons

  1. A watermark comparison must be half-open: >= lower AND < upper. A > on both ends drops rows at a boundary; >= on both duplicates them; half-open does neither.

  2. Timestamp granularity is a function of write rate, and second precision is fine at 0.08 writes/sec and not at 3.4.

  3. Some bugs have a probability that scales with load, and fire on the worst possible day. Chunk boundaries, skew, missing backpressure, retry storms, lock contention.

  4. Ask "what breaks at 10× the write rate?" in design review. Two minutes, and it found four more problems on the first pass.

  5. A row-count monitor cannot find this. 914 missing rows inside a day that added tens of thousands is not a volume anomaly. Only a source comparison finds it.

  6. The watermark-loss anti-join finds all four lies with one query, because it does not care why a row was missed — only that it is at a position you have already passed.

  7. Prefer the fix that leans on a property you already require. >= plus idempotency beat an exactly-correct composite watermark that depended on three things staying true.

  8. A load-scaled failure can only be found by generating the load. The November rehearsal is the most valuable of the three controls and the least technical.

Questions for Discussion

  1. The extract passed review in 2024 and three things about it were correct. What would a reviewer have to know to catch the fourth? Is that reasonable to expect, or does it need a checklist?

  2. The team chose the approximately-correct fix over the exactly-correct one. Argue for Option B. Under what circumstances would you insist on it?

  3. The 1,316 rows were recoverable because the source retains history. List Kestrel's sources and say, for each, how far back a recovery could reach. What should the platform do about the short ones?

  4. Control 2 asks "what breaks at 10× the write rate?" Why 10× and not 100×? What does the multiplier depend on?

  5. The November rehearsal runs at 8× against staging. Estimate what it costs to run, and what it would cost to run at full production scale. Is the cheaper version enough?

  6. The watermark-loss query has fired twice, both for backdated manual fixes. Is that a false positive or a true one? What should the alert say to make the answer obvious to whoever gets paged?

  7. This incident, Chapter 4's Case Study 2, and Chapter 1's duplicate-rows incident are all watermark or idempotency failures with different signs. Write the single design-review question that would have caught all three.