Case Study 2: The Rebuild That Would Not Reproduce

"The reconciliation closed. Then we rebuilt the whole platform from bronze and it closed to a different number, and both runs were green."

Executive Summary

Kestrel's November reconciliation closed to the cent (§38.7). Chapter 34 §34.9's verification rebuild — the whole graph from bronze into a scratch schema — produced a different answer.

production gold, net revenue      $20,430,983.06
rebuilt gold, net revenue         $20,430,241.88
                                  ──────────────
difference                               $741.18   over 42 orders

$741.18 on $20.4M is 0.0036%, it is below any materiality threshold anyone would set, and it means the platform is not a function of its inputs.

Three causes, found in order of difficulty:

1. a non-deterministic dedup                 38 orders   Ch 20's tie-break
2. a model reading a hand-created table       3 orders   Ch 34 CS1's finding
3. a projection calling an external service   1 order    Ch 36's replay rule

The third took nine days and is the one worth the case study, because it is the only one that could not be found by reading code.

Skills applied: rebuild verification (§38.9); Chapter 20's deterministic tie-break; Chapter 34 Case Study 1's hidden dependency; Chapter 36 §36.10's purity requirement; and the argument for chasing a difference that is immaterial.

The Problem

The temptation was to stop.

$741.18 is 0.0036% of the month. It is a hundred times smaller than Chapter 37's materiality threshold, it affects 42 orders of 289,389, and the production number was the one that had closed.

The argument for stopping was made in the room and it was not stupid: the reconciliation passed, the rebuild is a belt-and-braces check, and 42 orders is noise.

📐 Design Decision — chase an immaterial difference, because the size is not the finding

The counter-argument that won, in three parts:

The number is not the defect. $741.18 is what the defect happened to produce this month, on this data. The defect is "the platform's output depends on something other than its inputs," and that statement has no size. Next month it produces a different number, and there is no reason it has to be small.

It invalidates every other verification. Chapter 34's rebuild, Chapter 36's replay, Chapter 27's CI against a fixture, and the backfill procedure in Chapter 24 all assume that running the same models on the same inputs gives the same output. If that is false, none of those four checks means what the team believes it means — and that is a much larger exposure than $741.18.

And it is a compounding claim. A platform that reproduces is one you can rebuild, replay, backfill, and audit. A platform that reproduces to within $741 is none of those things, because every one of those operations requires an exact answer to "is this the same?"

The rule Kestrel wrote down afterwards:

A reproducibility failure has no materiality threshold. Any non-zero difference between a run and its rebuild is a defect, regardless of size.

This is the one place in the book where "to the cent" is not about money. Materiality is a reasonable concept for a reconciliation against an external source, where genuine irreducible differences exist (Chapter 37 §37.7's 50.2%). It is not a reasonable concept for a system compared against itself, where the correct difference is zero by construction and anything else is information you do not have.

Cause 1: The Non-Deterministic Dedup

38 of the 42 orders, found in two days.

silver.stg_orders deduplicates CDC rows, keeping the latest version per order_id:

-- the wrong version
SELECT * FROM (
  SELECT *, row_number() OVER (
             PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM bronze.orders_raw
) WHERE rn = 1

updated_at is not unique. When a CDC batch contains two rows for one order with the same updated_at — which happens when a transaction updates a row twice in the same millisecond — row_number() picks one arbitrarily, and "arbitrarily" means whatever order the scan produced, which depends on file layout, partition pruning, and the number of executors.

Chapter 20's exact problem, and Chapter 20 also gives the fix:

ORDER BY updated_at DESC, cdc_lsn DESC     -- a total order, not a partial one

⚠️ Failure Mode — the tie-break that was tested and was still wrong

stg_orders had a uniqueness test, and it passed on every run: order_id was unique in the output.

Uniqueness was never the problem. The model produced exactly one row per order, every time — a different one. A test that asserts "one row per order" is satisfied by any choice among the candidates, and there is no ordinary data quality assertion that catches this, because the output is individually valid on every run.

The only thing that detects it is running twice and comparing, which is what the rebuild does.

And it had been reported before. Chapter 34 Case Study 1's quarterly rebuild found a non-deterministic dedup fourteen months earlier, in a different model, and it was fixed there and not searched for elsewhere. The fix was applied to the sighting rather than to the class.

What Kestrel did this time, and it is Chapter 30's lesson about auditors rather than fixes:

sql -- an assertion over the PROJECT, not over the data -- every row_number() / rank() over a partition must have a tie-break -- that is unique within the partition. 4 of 31 window functions failed.

Four of thirty-one. Three had been latent for over a year, in models whose ties were rare enough never to have surfaced. Grep the pattern, not the instance — the same conclusion Chapter 30's agent-briefing lesson reaches from a different direction.

Cause 2: The Hand-Created Table

3 orders, found in an hour once someone thought to look.

gold.fct_order_line joined to analytics.channel_mapping — a 41-row lookup table created by hand by an analyst in 2024, never in the dbt project, never in version control, and not recreated by a rebuild into a scratch schema.

The rebuild resolved the reference to the production table, because the scratch schema had no channel_mapping and the search path fell through. So the rebuild was not, in fact, a rebuild — one of its inputs was production.

This is Chapter 34 Case Study 1's third rebuild failure, exactly, and Kestrel had it in its own retrospective. The mapping table was rebuilt as a seed — a versioned CSV in the dbt project — in about twenty minutes.

The interesting part is why it produced a difference at all. The table had been edited three days before the rebuild, adding two channel mappings. Production gold had been built before the edit; the rebuild picked up the new rows. Both were correct given their inputs, and one of the inputs was not an input anybody had declared.

Cause 3: The Projection That Called an API

1 order, and nine days.

Everything else reproduced. After fixing causes 1 and 2, the rebuild differed by $18.40 on one order — and the team spent nine days on eighteen dollars, which requires the §"Design Decision" argument to justify and was justified by it.

🏭 From the Pipeline — the enrichment that was not an input

gold.fct_order_line computes a tax_cents column for orders in one jurisdiction, using a rate that had changed.

```python

the model's Python pre-hook, added in 2025

rate = requests.get(TAX_SERVICE + "/rate?region=" + region).json()["rate"] ```

The rate is fetched at build time. Production gold was built in November with November's rate; the rebuild ran in December, after a rate change, and got December's rate.

The model is a pure function of its declared inputs and is not a pure function of the world, which is Chapter 36 §36.10's rule stated for projections and applying identically here: a model must be a pure function of the data, and anything it fetches at runtime is data it has not declared.

Why it took nine days:

  • The difference was one order out of 289,389, so no aggregate showed it.
  • The code reads as configuration, not as a dependency. A requests.get in a pre-hook looks like setup; nobody scanning for inputs looks in a hook.
  • It only differs when the rate changes, which is roughly annually. The rebuild had been run three times before and had reproduced perfectly, which is worse than never having run it — it had established confidence the model did not deserve.

How it was finally found: by elimination. The team diffed the two runs column by column, found tax_cents differing on exactly one row, and traced it. That is a crude technique and it was the only one available, because every faster method assumes you know what the inputs are.

The durable fix is a check rather than the fix:

```python

platform/ci/purity.py -- runs on every PR

a model may not contain: requests, urllib, socket, boto3, now(),

current_timestamp, random, or read a table outside the project.

```

It found two more: a model calling a currency API and one using current_date for a window boundary instead of the run's logical date. Neither had produced a wrong number yet.

What Happened

Before After
Rebuild difference $741.18 over 42 orders $0.00
Window functions without a unique tie-break 4 of 31 0
Inputs outside the project 1 (a hand-made table) 0
Models with a runtime external call 3 0
Reproducibility check in CI none purity.py, blocking
Time to find all three — 12 days

Twelve days for $741.18, and the team's assessment is that the trade was correct and would have been far cheaper had the rebuild been run from week one rather than at the capstone.

Two further consequences:

The quarterly rebuild became a monthly one, because the marginal cost is $198.96 and the three defects had each been latent for over a year. Monthly detection was judged worth $2,387 a year.

And the rebuild's success criterion was tightened. It had been "the reconciliation closes on the rebuilt schema" — which is a weaker check, because a rebuild can produce different intermediate values and still reconcile. It is now a row-level diff of every gold table, which is what found cause 3.

🔎 Read the Plan — three defects, three latent periods, one common property

text defect latent for surfaced when non-deterministic dedup 14+ months a rebuild ran twice hand-created input table 2 years the table was edited external call in a model 1 year the tax rate changed

None was found by a test, a monitor, a review, or an incident. Each required a specific, uncommon event to become visible — and each had been silently wrong, or silently at risk of being wrong, for over a year.

The common property: all three break the assumption that output is a function of declared input, and that assumption is not asserted anywhere in a normal platform. Chapter 23's register tests the data; Chapter 27's CI tests the code; Chapter 25's monitors test the running system. Nothing tests reproducibility except reproducing.

Which is the argument for the rebuild, stated as precisely as this book can state it: it is the only check that tests a property of the whole graph over time, and it costs $198.96. Everything else in this book verifies a component or an output. The rebuild verifies that the platform is a function.

Lessons

  1. The reconciliation closed and the rebuild did not. $741.18 on $20.4M — 0.0036% — and it means the platform is not a function of its inputs.

  2. 📐 A reproducibility failure has no materiality threshold. The size is what the defect produced this month; the defect itself has no size. And it invalidates the rebuild, the replay, CI against a fixture, and the backfill procedure — all four assume the property that just failed.

  3. Materiality is reasonable against an external source and is not reasonable for a system compared against itself, where the correct difference is zero by construction.

  4. ⚠️ A uniqueness test does not catch a non-deterministic tie-break. The model produced one row per order every time — a different one. No ordinary assertion catches it; only running twice does.

  5. The same defect had been found fourteen months earlier and fixed at the sighting. The project-wide audit found 4 of 31 window functions without a unique tie-break, three latent over a year. Grep the pattern, not the instance.

  6. A rebuild that resolves a reference to production is not a rebuild. A hand-made lookup table outside the project silently made production an input to its own verification.

  7. 🏭 A model must be a pure function of its declared inputs, and anything fetched at runtime is an undeclared input. Chapter 36 §36.10's rule for projections, identical here.

  8. The rebuild had reproduced three times before, because the tax rate had not changed — which is worse than never having run it, because it built confidence the model did not deserve.

  9. It was found by a column-by-column diff, a crude technique that was the only one available, because every faster method assumes you know what the inputs are.

  10. The durable fix is a check, not a fix. purity.py found two more undeclared dependencies, neither of which had produced a wrong number yet.

  11. 🔎 All three defects had been latent for over a year and none was findable by any other control. The register tests data, CI tests code, monitors test the running system — nothing tests reproducibility except reproducing.

  12. The rebuild's criterion was too weak. "The reconciliation closes" passes on different intermediate values; a row-level diff of every gold table is what found cause 3.

Questions for Discussion

  1. The argument to stop at $741.18 was made and was not stupid. Reconstruct it as strongly as you can, then say what defeats it.

  2. "A reproducibility failure has no materiality threshold." Is that absolute? Construct the case where you would accept a non-zero rebuild difference.

  3. A uniqueness test passed while the output was non-deterministic. What other tests in your platform would pass on a differently-wrong answer each run?

  4. The tie-break defect was fixed at the sighting fourteen months earlier. What would have prompted the project-wide audit then?

  5. The rebuild reproduced three times before failing, building false confidence. How would you have known those three successes were weak evidence?

  6. purity.py bans requests, now(), and random in models. What legitimate use does that block, and how would you handle it?

  7. The rebuild went from quarterly to monthly at $198.96 a run. What would make it worth running nightly — and what would you have to change for that to be affordable?