Case Study 1: Ninety Seconds

"The runbook said four hours. It took ninety seconds, and the reason it took ninety seconds is that somebody made a configuration decision eleven months earlier."

Executive Summary

At 22:14 on a Tuesday, a deployed transformation change caused Kestrel's nightly load of gold.fct_order_item to drop roughly 40% of its rows. The 6am revenue dashboard was wrong. The on-call engineer was paged at 06:41.

The recovery took ninety seconds — a single RESTORE TABLE ... TO VERSION AS OF 117 — and the dashboard was correct before 07:00.

This case study is about what made that possible, which was not the restore command. It was a retention setting, a habit of reading DESCRIBE HISTORY first, and a decision — made eleven months earlier and argued about at the time — to accept a maintenance burden in exchange for a capability nobody could point at a use for.

It is also about the two things that nearly went wrong, both of which are more instructive than the success.

Skills applied: time travel and restore (§10.6); the transaction log and history (§10.2); vacuum retention (§10.5); loud versus plausible failure (Chapter 2, Case Study 1).

Background

The change. A pull request adding promotion attribution to fct_order_item (Chapter 6's Case Study 1). Reviewed, tested against a sample, merged, deployed at 22:00.

The bug. One clause:

-- as deployed
  FROM silver.order_items oi
  JOIN silver.orders     o  USING (order_id)
  JOIN gold.dim_promotion p ON p.promotion_key = oi.promotion_key
 WHERE o.status IN ('paid','picked','shipped','delivered')

An inner join to dim_promotion. Order lines with no promotion have promotion_key = 0, which resolves to the "no promotion" member — that part was correct. But the silver table's promotion_key was null for lines predating the promotions integration, and null does not match key 0.

4,032,499 rows written instead of 6,483,117. A 37.8% drop.

Why the tests passed. The test fixture was built from recent data, in which every line has a non-null promotion_key. The null-key rows are historical.

⚠️ Failure Mode — The fixture that is not representative

The test was good, thorough, and asked a question the data did not contain. This is one of the most common ways a data pipeline test provides false assurance, and the mechanism is worth naming.

Test fixtures drift toward the recent and the clean, for two structural reasons. Recent data is easier to obtain — you take a slice of yesterday. And clean data is easier to reason about, so the odd historical rows get filtered out when someone tidies the fixture.

The result is a fixture that represents the data as it is today and not as it is in the table.

Three defenses:

  1. Build fixtures by stratified sampling, not by taking a recent slice. Include the oldest rows, the rows with nulls in each nullable column, and the extremes of each numeric range.
  2. Test row counts across joins, not just output correctness. A test asserting that the output row count equals the input row count would have caught this regardless of fixture content — this is Chapter 2 §2.5's third defense, and it is cheap.
  3. Run the transformation against a full clone in CI. Chapter 27 §27.4. Zero-copy cloning (Chapter 8 §8.4) makes this affordable, and it is the only defense that catches the general case.

Kestrel adopted 2 immediately and 3 over the following quarter.

The Problem

06:41. The on-call engineer is paged by the freshness-and-volume monitor (Chapter 2's Case Study 1's control): gold.fct_order_item row count is 37.8% below the trailing 14-day band.

Note what did not fire: no job failed. The DAG was green. The load succeeded and wrote 4,032,499 perfectly valid rows. This is Chapter 1's silent-failure asymmetry, and the volume monitor is the only thing standing between it and a wrong number in a board meeting.

06:43. The engineer runs the first command in the runbook:

DESCRIBE HISTORY gold.fct_order_item LIMIT 5;

 version | timestamp           | operation | operationMetrics
---------+---------------------+-----------+---------------------------------
     118 | 2025-11-25 22:14:03 | WRITE     | numOutputRows=486221, numFiles=3
     117 | 2025-11-24 21:03:47 | WRITE     | numOutputRows=781380, numFiles=5
     116 | 2025-11-23 21:04:12 | WRITE     | numOutputRows=779104, numFiles=5
     115 | 2025-11-22 21:02:58 | WRITE     | numOutputRows=776922, numFiles=5
     114 | 2025-11-21 21:01:33 | WRITE     | numOutputRows=774818, numFiles=5

The problem is visible in one screen, and it took under a minute. Versions 114 through 117 climb smoothly; 118 drops 37.8%. The bad version is identified, the good version is identified, and the metrics came from the log rather than from counting rows.

🔎 Read the Plan — DESCRIBE HISTORY is the first command, not the fifth

The Kestrel runbook now opens every data-correctness incident with DESCRIBE HISTORY, and the reason is that it answers four questions at once, from metadata, in under a second:

Question Where it appears
When did this table last change? timestamp
What operation was it? operation — WRITE, MERGE, DELETE, OPTIMIZE, RESTORE
How many rows did it write? operationMetrics.numOutputRows
Is the change consistent with recent history? the trend down the column

The pre-lakehouse version of this investigation was: query the table, query yesterday's backup or a downstream copy, compare, and try to work out when it changed. Twenty minutes at best, and frequently the comparison data did not exist.

numOutputRows down the version history is a free volume monitor you did not have to build. It will not page you — that still needs the alert — and once you are paged, it is the fastest route to "which write did this."

06:45. The engineer faces the actual decision, and it is not technical.

The Analysis

Two options, both defensible, and the runbook did not cover this case.

Option A — restore to version 117. The dashboard is correct in ninety seconds. The data is Sunday's load, so Monday's orders are missing until the pipeline is fixed and re-run.

Option B — fix the query and re-run. The data is correct and complete. It requires understanding the bug, writing a fix, reviewing it, and re-running — at 06:45, by one person, alone, before 07:00. Realistically an hour at best.

They chose A, and the reasoning is the transferable part:

Restoring service and fixing the cause are different activities with different urgencies. At 06:45 the urgent thing is that the number on the dashboard is right. The bug can be fixed at 10:00 by people who have slept and can review each other's work.

Option A also has a property Option B does not: it is reversible in ninety seconds. If the restore turned out to be wrong, another restore undoes it. A hastily written fix deployed at 06:50 is not reversible in any comparable sense — it is a second deploy, at speed, by a tired person, with the first one's failure as evidence that this is a bad time to be writing SQL.

RESTORE TABLE gold.fct_order_item TO VERSION AS OF 117;

06:47. Complete. The dashboard, which caches for ten minutes, was correct by 06:58.

The two things that nearly went wrong

1. The restore was within the retention window — barely by policy, comfortably by luck. delta.deletedFileRetentionDuration was 7 days. Version 117 was written 25 hours earlier, well inside it.

Had the bug shipped on a Friday and been noticed on a Monday, version 117 would have been 72 hours old — still fine. Had it been noticed after the following weekend, it would not have been. The team's post-incident review flagged this and Chapter 26 §26.5 now carries the rule: retention must exceed your realistic detection time, and your realistic detection time includes weekends and holidays. Kestrel raised gold-layer retention to 30 days.

2. Nobody had ever tested a restore. The command was run for the first time in production, at 06:46, by a person who had read about it.

It worked. It might not have — the table had deletion vectors enabled, the engineer did not know whether restore interacted with them, and there was no time to find out. A recovery procedure that has never been exercised is a hypothesis, which is Chapter 3's Case Study 1's lesson about changelog topics, recurring.

Restore drills are now part of the quarterly runbook review.

The Decision

Four changes.

1. RESTORE is now the documented first response to a bad write on any gold table, with the reasoning written down: restore service first, fix the cause during working hours. The runbook includes the exact command and a worked example.

2. Retention raised to 30 days on gold, 7 days on bronze and silver. Costs some storage; the gold layer is small and the storage is irrelevant next to the capability.

3. Row-count assertion in the transformation, not just in monitoring:

-- in the dbt model's post-hook: fail the BUILD, not just the dashboard
{{ config(post_hook="
    {% raw %}{{ assert_row_count_within(this, ref('silver_order_items'), tolerance=0.02) }}{% endraw %}
") }}

This is the change with the most value and it is the least dramatic. It converts the failure from a bad write plus a page into a failed build plus an alert — Chapter 2's Case Study 1's fail-loudly principle, applied at the write rather than at the read. The 06:41 page would not have happened; a 22:15 build failure would have.

4. Stratified test fixtures. Every fixture must include the oldest rows, nulls in each nullable column, and the extremes of each numeric range. Enforced by a fixture-generation script rather than by discipline.

What Happened

The bug was fixed at 10:20 the next morning — a LEFT JOIN with COALESCE(promotion_key, 0) — reviewed by two people, deployed, and the correct data loaded that evening.

In the eighteen months since:

  • RESTORE has been used four more times. Twice for bad writes, once to undo an over-aggressive DELETE during a privacy request test, and once by a data scientist who had accidentally written to a shared table.
  • The row-count assertion has failed eleven times. Nine were genuine — upstream schema changes, a join fan-out, and two more null-key cases. Two were legitimate volume changes on Black Friday and Cyber Monday, which now have an exception.
  • No gold-layer bad write has reached a dashboard since, because the assertion catches them at build time.

The last point is the honest measure of the incident's value. Time travel made the recovery fast; the row-count assertion made the recovery unnecessary. The dramatic capability got the attention and the boring check did the work — which is the pattern in most of this book's incidents.

Lessons

  1. Restoring service and fixing the cause are different activities with different urgencies. Ninety seconds versus an hour, at 06:45, alone.

  2. A restore is reversible; a hastily written fix is not. That asymmetry should decide the choice under time pressure.

  3. DESCRIBE HISTORY is the first command in a data-correctness incident. It answers four questions from metadata in under a second, and numOutputRows down the version history is a free volume monitor.

  4. Retention must exceed your realistic detection time — including weekends and holidays. 25 hours was fine; 10 days would not have been.

  5. A recovery procedure that has never been exercised is a hypothesis. The first restore was run in production, under pressure, by someone who had only read about it.

  6. Test fixtures drift toward the recent and the clean, and therefore stop representing the table. Stratify: oldest rows, nulls in every nullable column, extremes of every range.

  7. Assert row counts across joins in the transformation, not just in monitoring. It converts a bad write into a failed build.

  8. The dramatic capability got the attention; the boring check did the work. Time travel made recovery fast; the assertion made it unnecessary.

Questions for Discussion

  1. The engineer chose to restore rather than fix, leaving Monday's orders missing for several hours. Construct the case for the opposite choice. What kind of data, or what kind of stakeholder, would make fixing-first correct?

  2. The runbook did not cover this case and the engineer decided alone at 06:45. What should a runbook say about decisions it does not cover? Is there a general form?

  3. The restore worked and had never been tested. Design the quarterly restore drill: what you restore, where, and how you verify it without touching production.

  4. Retention was raised to 30 days on gold. What would you set for bronze, given it is 341 GB/year and its restores would be for different reasons? Justify with both a scenario and a cost.

  5. The row-count assertion fires on Black Friday and Cyber Monday, which now have exceptions. How would you handle a genuinely new seasonal pattern — a first-ever flash sale — without either a false page or a silent gap?

  6. "The dramatic capability got the attention and the boring check did the work." Find two other examples of this pattern in the book so far. Is there a way to make teams reach for the boring one first?

  7. The fixture problem — recent and clean — applies to almost every data pipeline test. Estimate how many of your own tests would survive being run against the oldest 1% of rows in their input.