Case Study 2: Thirty-One Green Nights

"Everything was green. That was the problem. If one thing had been red we would have found it in an hour." — from the Kestrel incident review, April 2025

Executive Summary

Between 2025-03-02 and 2025-04-01, Kestrel's warehouse reported revenue that was 11.4% too high. The cause was a backfill script that inserted rows without first deleting the rows it was replacing, scheduled nightly and forgotten. It ran thirty-one times. Every run succeeded.

This case study reconstructs the incident in detail: what the script did, why every layer of monitoring passed it, why the investigation went down the wrong path for two weeks, what it cost, and what changed afterward. It is the most important story in this book, and it recurs in Chapters 20, 23, 25, and 26 as the motivating example for four different practices.

The reason to study it closely is that nothing about it was exotic. No race condition, no distributed systems subtlety, no vendor bug. A competent engineer wrote a reasonable script under time pressure, made one omission, and the omission was invisible to every check the company had. That is the modal serious data incident. The exotic ones are rare and get written up; this shape happens constantly and gets quietly fixed.

Skills applied: idempotency (§1.6, 🔁 Idempotency Check); the silent-failure asymmetry (§1.2, ⚠️ Failure Mode); accountability boundaries (§1.4); and the argument that trust is the product (§1.6).

Background

The setup. On 2025-02-27, a load of fct_order_item failed for three consecutive nights because of a schema change upstream — a promotions table gained a column, and the transform's SELECT * broke on the type mismatch. The fix was straightforward and shipped on 2025-03-01. But three days of data were missing from the warehouse.

An engineer wrote a backfill. It is worth reading:

# backfill_order_items.py
# Repairs fct_order_item for the last N days. -- one-off, 2025-03-01
import os
import psycopg

DAYS = 3

SQL = """
INSERT INTO gold.fct_order_item (
    order_item_id, order_id, customer_id, product_id, placed_at,
    quantity, gross_revenue_cents, net_revenue_cents
)
SELECT oi.order_item_id, oi.order_id, o.customer_id, oi.product_id, o.placed_at,
       oi.quantity,
       oi.quantity * oi.unit_price_cents,
       oi.quantity * oi.unit_price_cents - oi.discount_cents
  FROM staging.order_items oi
  JOIN staging.orders o USING (order_id)
 WHERE o.placed_at >= CURRENT_DATE - INTERVAL '%s days'
"""

with psycopg.connect(os.environ["KESTREL_DW_DSN"]) as conn:
    with conn.cursor() as cur:
        cur.execute(SQL % DAYS)
        print(f"inserted {cur.rowcount} rows")

It is not bad code. It uses an environment variable for the connection string. It prints what it did. It has a comment. It ran, it printed inserted 54,203 rows, and the gap was filled.

The mistake that turned a fix into an incident was not in the script. It was the next decision: the engineer added it to the nightly schedule "for a while, just in case the upstream problem comes back," and closed the ticket.

The Problem

The script has no DELETE. Every night it inserted the last three days of order lines — on top of the last three days of order lines that were already there, and the three days before that had already been inserted twice, and so on.

The arithmetic of the resulting inflation is worth walking through, because it explains why the error was 11.4% rather than 200%.

An order line placed on day $d$ is inserted by the backfill on nights $d$, $d+1$, and $d+2$ — three times — in addition to the regular nightly load that inserted it once on night $d$. So each order line in the affected window ends up present four times instead of once, once the window has fully passed over it.

But the window only covers the most recent three days, and the table holds history going back years. The inflation applies to thirty-one days of data inside a table containing far more than that. Reported revenue for March specifically was inflated far more than reported revenue for the trailing twelve months, and different dashboards, looking at different windows, were wrong by different amounts. The 11.4% figure is the inflation in the measure that mattered — trailing-quarter net revenue, which is what the operating review used.

That variability is a large part of why the diagnosis took two weeks. Two people looking at two dashboards saw two different magnitudes of error and reasonably concluded they were looking at two different problems.

The Analysis

Why every check passed

Kestrel was not an unmonitored company in March 2025. It had monitoring. The monitoring was of the wrong things, and cataloguing exactly which is the most useful part of this case study.

Six checks. All green. And the crucial pattern: every one of them was a check on the pipeline, and none was a check on the data.

The check that would have caught it in under a day did not exist:

-- Would have failed on 2025-03-03.
SELECT COUNT(*) AS rows,
       COUNT(DISTINCT order_item_id) AS distinct_keys
  FROM gold.fct_order_item
 WHERE placed_at >= CURRENT_DATE - INTERVAL '7 days';

A uniqueness test on the declared grain of the table. fct_order_item is documented as one row per order line. If COUNT(*) exceeds COUNT(DISTINCT order_item_id), the table is not what its own documentation says it is.

🔁 Idempotency Check — Three questions that would each have caught it

Ask these of any pipeline you write, at design time, in writing:

1. What happens if this runs twice? Here: rows are duplicated. That answer alone should have stopped the schedule change.
2. What is the declared grain of the output, and what test enforces it? Here: one row per order_item_id, and no test enforced it.
3. If this ran every night forever, would the table converge to a correct state or diverge? Here: diverge, without bound. A one-off script promoted to a schedule is a different program with different requirements, and nobody re-reviewed it as one.

Chapter 20 §20.3 gives the four ways to make a load idempotent. Chapter 23 §23.4 makes the grain test automatic. Chapter 27 §27.5 adds the review gate that catches a one-off being scheduled.

Why the investigation went wrong

On 2025-04-01, a finance analyst reconciling Q1 against the payment processor's settlement report found a gap they could not explain and filed a ticket. From there, two weeks:

Days 1–4: definition hunting. The team's first hypothesis was a definitional change, because that is what these discrepancies usually are and because the team had been through exactly this in 2023 (Case Study 1). They audited the net_revenue_cents logic, the promotions join added in February, and the treatment of cancelled orders. All three were correct.

Days 5–8: the source. Second hypothesis: the source data itself changed. They compared staging against kestrel_app directly. Clean match. This step was correct and also, in retrospect, the moment the answer was available — staging matched the source, and gold did not match staging. Nobody ran the third comparison, because the second one had been clean and the working assumption was that the problem was upstream of the warehouse.

Days 9–12: the promotions change. Third hypothesis, and a strong one: a promotions-table join had been added in February, and a fan-out on a many-to-many join is a classic cause of exactly this inflation. Two engineers spent four days on it. The join was correct — it had a DISTINCT and a proper grain guard.

Day 13. A third engineer, brought in fresh, asked: "is order_item_id unique in the fact table?" Ninety seconds later they had the answer.

🏭 From the Pipeline — Ask the dumbest question first

Twelve days of expert investigation. Two competent engineers, four days on a sophisticated hypothesis about join fan-out. And the answer came from the least sophisticated question anyone could have asked about the table.

There is a specific reason experienced teams skip these questions, and it is not carelessness. It is that the dumb checks feel like they must already have been done — by the tests, by the loader, by somebody. The more mature a platform looks, the more its engineers assume the basics are covered.

Two habits fall out of this, and they are cheap:

• Start every data investigation with grain and volume. Row count, distinct count of the primary key, min and max timestamp, null rate on key columns. Four queries, under a minute, before any hypothesis.
• Bring in someone with no context on day two, not day twelve. The value of a fresh engineer is precisely that they have not yet ruled anything out.

What it cost

Direct. Two engineers × two weeks of investigation ≈ 160 hours. Correction and republication of Q1 reporting after distribution. One board deck reissued.

Indirect, and larger. Two full quarters afterward, every unexpected movement in a metric triggered "are we double-counting again?" The data team spent measurable time proving negatives. One planned project — a self-serve metrics layer — was deferred for two quarters because leadership was not prepared to widen access to numbers they had recently learned to doubt.

Trust is the product. The table was fixed in an afternoon. The credibility took two quarters and a visible, automated reconciliation to rebuild.

The Decision

The incident review produced four changes. They are deliberately unambitious — all four were implemented within three weeks, which is why all four are still in place.

1. Grain tests on every fact and dimension table. Every table declares its grain in its documentation, and a test enforces it in CI and on every production run. In dbt terms, a unique test on the declared key of every model — nine lines of YAML across the whole warehouse. This is now Chapter 23 §23.4.

2. Every load is idempotent, and it is a review requirement. The pull request template gained one question: "What happens if this runs twice?" An answer is required. Chapter 20 §20.3.

3. No one-off script may be scheduled without re-review. A script written to run once and a script written to run nightly are different programs with different requirements. The transition between them is now a code review, not a scheduler edit.

4. Source-to-warehouse reconciliation, nightly, automated. Not monthly against the payment processor — nightly against kestrel_app, on the measure that matters, with a threshold and an alert. This is the check the whole book builds toward, and it is the acceptance criterion for the Chapter 38 capstone.

📐 Design Decision — What they deliberately did not do

Three things were proposed in the review and rejected. The reasoning matters as much as the changes that were adopted.

Rejected: a full audit-log table capturing every write. Real value, real cost — storage, complexity, and a new system to maintain. With four engineers, it would have displaced the four changes above. What was given up: the ability to reconstruct exactly which run inserted which row. They accepted that, judging that preventing the class of error beat forensics on instances of it.

Rejected: requiring two approvers on all pipeline changes. Would have caught this one. Would also have roughly doubled the cycle time on every routine change, in a four-person team where one person is often on vacation. What was given up: a real second pair of eyes. They took the targeted rule (change #3) instead of the blanket one, on the theory that a process cost paid on every change to prevent a rare event is usually a bad trade, and gets routed around.

Rejected: rewriting the loaders in a framework that enforces idempotency structurally. The right long-term answer, and it arrived eight months later as the dbt migration. In the moment it was a quarter of work to fix a problem that a DELETE statement fixed in an afternoon. Ship the afternoon fix, schedule the quarter.

What Happened

The immediate repair was a full rebuild of fct_order_item from staging, which took 40 minutes.

The four changes went in over three weeks. The grain tests found two additional pre-existing duplication problems within the first week — a dimension table with 340 duplicated rows from a 2024 migration, and a smaller fact table that had been double-loading every Sunday for an unknown period because of an overlapping schedule. Neither had ever been noticed. Neither had caused a visible incident, which does not mean neither had caused a wrong decision.

That is the part to sit with. The incident that got investigated was not the only one occurring. It was the only one large enough, in a measure visible enough, to be noticed. Two others of the same class were running silently in the same warehouse, and they were found within a week of installing a check that takes nine lines of YAML.

The nightly reconciliation has run since May 2025. Its variance threshold is set tight enough to be occasionally annoying, which is the correct calibration for a check that exists to catch things nobody is looking for.

Lessons

1. Monitor the data, not just the pipeline. Six green checks, all on the pipeline. The failure was in the output.

2. A declared grain with no test is a comment. fct_order_item was documented as one row per order line for two years and nothing enforced it.

3. "What happens if this runs twice?" is the highest-value question in this book. Ask it at design time, in writing, in the pull request.

4. A one-off script promoted to a schedule is a new program. Re-review it as one.

5. Start investigations with the dumbest questions. Grain, volume, freshness, nulls. Four queries, one minute, before any hypothesis. Twelve days went into sophisticated hypotheses because the basics felt like they must already have been checked.

6. Bring in a fresh pair of eyes on day two. The engineer who solved it had no prior model of the system to defend.

7. Prefer the targeted rule to the blanket one. A process cost paid on every change to prevent a rare event gets routed around. Three of the four adopted changes were narrow; that is why they survived.

8. When you install a check for a known problem, expect it to find unknown ones. It found two more in a week.

Questions for Discussion

1. The backfill script is shown in full. Assume you are reviewing it as a pull request on 2025-03-01, with the ticket title "fill 3-day gap in fct_order_item." What do you comment on? Now assume the ticket title is "add nightly backfill for fct_order_item." What changes?

2. Six monitoring checks existed and all passed. Rank them by how much they should have cost to maintain versus how much value they delivered. Would you remove any?

3. On days 5–8 the team confirmed staging matched the source and concluded the problem was not upstream. What is the reasoning error, and what would a checklist have to say to prevent it?

4. The review rejected a universal two-approver rule as too expensive for a four-person team. At what team size does that calculus flip? What would you use as the deciding factor?

5. The grain tests found two more duplication problems in a week, neither previously noticed. What does that imply about the number of undetected data errors in a typical warehouse — and what, realistically, should a four-person team do about it?

6. The indirect cost — two quarters of eroded trust and a deferred project — exceeded the direct cost. How would you make that visible to leadership before an incident, when arguing for the time to build data tests?

7. The nightly reconciliation's threshold is described as "tight enough to be occasionally annoying." Argue for and against that calibration. What happens to a check calibrated the other way, and which failure is worse?