Case Study 1: The Definition That Changed on a Tuesday

"The change was right. The review was right. The tests passed. And now net_revenue_cents means one thing before 2026-05-19 and a different thing after, and nothing anywhere says so."

Executive Summary

A pull request corrected how fct_order_item.net_revenue_cents handles partial refunds. The old logic was wrong, the new logic was right, two engineers reviewed it, every test passed, and it merged on a Tuesday.

Nobody rebuilt history. fct_order_item is incremental (Chapter 20), so the change applied only to rows built from that night onward. The table now contained two definitions of its most important column, with the boundary at whatever date the deploy happened to land on.

It was found eleven weeks later, when a year-over-year comparison showed an implausible 2.3% step in May that nobody could attribute. Reconstructing which rows were built under which definition took four days, because nothing in the table recorded it.

The fix was the deploy shape: a required pull-request field forcing the author to classify the change as additive, definitional, or structural, and — for the latter two — to write the rebuild command before merging.

Skills applied: deploy shape (§27.7); reviewing what a diff cannot show (§27.10); incremental models and history (Chapter 20 §20.4); code version recorded alongside data (§27.6).

Background

The defect being fixed was real and had been reported by finance: a partial refund on a multi-line order was being deducted from the first line rather than allocated across the lines it applied to.

-- before
net_revenue_cents = gross_revenue_cents - COALESCE(order_refund_cents, 0)

-- after
net_revenue_cents = gross_revenue_cents - COALESCE(line_refund_cents, 0)

Order totals were unaffected. Only the distribution across lines changed — which is why nothing reconciled differently and why every test passed:

• The grain test: unaffected.
• The volume floor: unaffected.
• Reconciliation against the payment processor: unaffected, because it compares order totals.
• net_revenue_cents >= 0: still true, because the total was still right.

A three-line diff, a correct fix, and a clean CI run.

The Problem

fct_order_item is materialized='incremental' with a merge strategy (Chapter 20). The night after the merge, the model processed the day's new rows with the new logic.

Every row before 2026-05-19 kept the old allocation. Every row after got the new one.

fct_order_item.net_revenue_cents
  2024-01 ─────────────────────────┬───────────────────── 2026-08
     allocated to the first line   │  allocated per line
                                   │
                            2026-05-19
                       (a Tuesday, chosen by nobody)

No error, no failed test, no alert. Chapter 20 Case Study 1's phrase applies exactly: a trap with a timestamp on it, created this time by a deploy rather than by a configuration.

The symptom, eleven weeks later: an analyst building a year-over-year view of revenue by product category found a 2.3% step in May, present in every category, absent from order-level revenue.

⚠️ Failure Mode — a correct fix that leaves the table inconsistent is worse than the bug

This is the uncomfortable claim, and it is worth defending because the instinct is to be pleased that the bug was fixed.

Before the change: the column was wrong, uniformly, in a known way. Any analysis using it was wrong by a consistent amount, and a correction could be applied.

After the change: the column is right for 11 weeks and wrong for 29 months, with no marker distinguishing them. Every analysis spanning the boundary is wrong by an amount that varies with how much of its window falls on each side, and no correction is possible without knowing the boundary, which is not recorded anywhere.

A uniformly wrong column can be corrected. A column whose definition changes at an unrecorded date cannot.

Which produces the rule: when you fix a definition, you have three options and exactly one of them is "do nothing":

• Rebuild history, and the column has one meaning. Usually right.
• Do not rebuild, and record the boundary — in the model's description, in a column, in a documented note. Acceptable when a rebuild is genuinely impossible.
• Do not rebuild and do not record it. ← this one

The third is not a decision. It is the absence of one, and it is what happens by default because shipping the code is the half of the deploy that has a button.

The Analysis

Step 1: confirm the boundary. Straightforward once suspected, because Kestrel's bronze tables carry _ingested_at (Chapter 24 Case Study 1) — but fct_order_item is a gold model and did not.

So the boundary had to be inferred, and this is where the four days went:

-- Reconstruct which rows have which allocation, by recomputing both and
-- comparing. On 6.48 million rows, for multi-line orders with refunds.
WITH recomputed AS (
  SELECT order_id, line_number, net_revenue_cents AS stored,
         gross_revenue_cents - COALESCE(line_refund_cents, 0) AS new_logic,
         ...
    FROM fct_order_item f JOIN silver.order_items s USING (order_id, line_number)
   WHERE s.line_refund_cents > 0)
SELECT ordered_at::date AS d,
       COUNT(*) FILTER (WHERE stored = new_logic) AS new_def,
       COUNT(*) FILTER (WHERE stored <> new_logic) AS old_def
  FROM recomputed GROUP BY 1 ORDER BY 1;

d             new_def   old_def
2026-05-17          0     1,204
2026-05-18          0     1,187
2026-05-19        891       312     ← the deploy, mid-day
2026-05-20      1,241         0

The boundary is mid-day on the 19th, not a clean date, because the deploy landed at 14:20 and the incremental run that night processed rows from both sides of it under the new logic while earlier rows kept the old.

Step 2: how much was affected? 3.4% of order lines have a refund; of those, 62% are on multi-line orders and therefore affected by the allocation.

$$6{,}483{,}117 \times 0.034 \times 0.62 = 136{,}664\ \text{lines with a differing value}$$

Order totals were correct throughout, so the aggregate revenue figures every report is built on were never wrong. Everything cut by product, category, or line was.

Step 3: what was reported on it? This is where the cost was, and the honest answer is partial: eleven weeks of category-level reporting, of which the team could identify four specific analyses and could not rule out others.

🔎 Read the Plan — the column that would have made this a ten-minute investigation

Four days went into inferring a boundary that could have been recorded in one column.

Add the code version to the row. Chapter 24 Case Study 1 asked for _ingested_at, _source, and run_id on bronze; this is the gold-layer equivalent and it is one column:

sql {{ config(materialized='incremental') }} SELECT ..., '{{ var("git_sha", "unknown") }}' AS _built_by, -- 40 bytes current_timestamp AS _built_at

With that column, the investigation is one query:

sql SELECT _built_by, MIN(ordered_at), MAX(ordered_at), COUNT(*) FROM fct_order_item GROUP BY 1 ORDER BY 2;

And it answers a question that comes up far more often than this incident: "which code produced this row?" — for a wrong number, a disputed figure, an audit, or a migration.

The cost is real and small. 40 bytes × 6.48 million rows is 259 MB uncompressed, and dictionary-encoded in Parquet it is a few kilobytes, because there are perhaps two hundred distinct values in a year.

The objection Kestrel raised and rejected: "the git SHA is meaningless to an analyst." True, and irrelevant — it is not for the analyst. It is for the person reconstructing a boundary at 05:00, and for them it is the difference between one query and four days.

The Decision

Four changes.

One: the deploy shape becomes a required pull-request field. §27.7:

## Deploy shape
- [ ] Additive        (a new column; history is null and that is fine)
- [x] Definitional    (existing logic changed; history is now inconsistent)
- [ ] Structural      (the grain or schema changed)

## Because this is definitional:
- Rows to rebuild:  6,483,117  (all of fct_order_item)
- Command:          dbt build --select fct_order_item+ --full-refresh --vars '{...}'
                    (dry run: ... )
- Idempotent?       yes — merge on (order_id, line_number). Ch. 20 §20.3.
- Source retention: 18 months. Covers the full rebuild. Ch. 26 §26.5.
- Who is told:      #finance, before the rebuild, because category-level
                    figures will move.

The field that does the work is "rows to rebuild." An author who cannot fill it in has not thought about the data half of the deploy, and that is the moment to discover it — not eleven weeks later.

Two: pr_report.py computes the shape automatically and posts it, so the checkbox is pre-filled and disagreeing with it requires saying why.

📐 Design Decision — the classifier is deliberately over-cautious, and that is not a compromise

pr_report.py classifies a change as definitional unless it can show the change is purely additive. It will call some additive changes definitional.

That asymmetry is chosen, and the reasoning is worth stating because "reduce false positives" is the usual instinct:

	Cost
false definitional (additive change flagged)	an author ticks a box and writes "no rebuild needed, additive"
false additive (definitional change missed)	this case study

Thirty seconds against eleven weeks, so the classifier errs toward the thirty seconds.

And the false positives are not waste. Each one is an author explicitly asserting that history is consistent — which is a claim, recorded, by a person, at the moment they knew. The checkbox produces the artifact whether or not the classifier was right.

What would make this wrong: a false-positive rate high enough that the box is ticked without thought. Kestrel measured it — 9 of 71 definitional classifications in six months were overridden as additive, which is 13% and is comfortably below the rate at which a control becomes a ritual. They committed to re-measuring it annually, because that rate is the whole justification.

Three: _built_by and _built_at on every gold model. The 🔎 callout above.

Four: the historical rebuild. 6,483,117 rows, run over a weekend with the finance team told in advance, and a snapshot of the pre-rebuild category figures kept — because §27.6's point is that once you rebuild, the old numbers exist nowhere.

What Happened

The rebuild moved category-level revenue by up to 2.3% in the affected direction, and the finance team's response is the one worth recording: they asked for the pre-rebuild numbers to be kept, not because they doubted the fix but because two quarterly reports had been filed on the old figures and the reconciliation between them needed to exist.

Which is §27.6's practice arriving from the business side rather than the engineering one. Kestrel now snapshots any figure that goes into an external report into a small table of (metric, period, value, computed_at, code_version) — kilobytes a year, and the only artifact that answers "what did we say, and when?"

The audit of other models found three more definitional changes in the previous year that had not been rebuilt:

• A change to how dim_customer.segment is derived. Rebuilt.
• A change to session timeout from 30 to 25 minutes, in fct_session. Not rebuilt — the team decided the historical inconsistency was acceptable, and recorded the date in the model's description, which is the second of the ⚠️ callout's three options and the first time they had taken it deliberately.
• A change to a tax calculation. Rebuilt, and it had been wrong in the other direction for four months before the fix — meaning the fix's deploy had created a third definition boundary that nobody had noticed either.

Lessons

1. A correct fix that leaves the table inconsistent is worse than the bug. A uniformly wrong column can be corrected; a column whose definition changes at an unrecorded date cannot.

2. Three options, and exactly one is "do nothing": rebuild · do not rebuild and record the boundary · do not rebuild and do not record it. The third is the absence of a decision, and it is the default because shipping the code is the half with a button.

3. Every test passed because the change did not alter any aggregate. Order totals were right throughout; only the distribution across lines moved.

4. A definitional change on an incremental model splits the table at the deploy timestamp — which is mid-day on a Tuesday, chosen by nobody.

5. Require the deploy shape in the pull request. The field that does the work is "rows to rebuild": an author who cannot fill it in has not thought about the data half.

6. Classify over-cautiously. A false definitional costs thirty seconds; a false additive costs eleven weeks. And the false positives are not waste — each one produces a recorded claim that history is consistent.

7. Measure the override rate, because it is the justification. 13% is fine; a rate high enough to make the box a ritual is not.

8. Put the code version on the row. 40 bytes, a few kilobytes dictionary-encoded, and it turns "which code produced this?" from four days into one query. It is not for the analyst.

9. Snapshot any figure that goes into an external report, because a rebuild destroys the number a filing was made on. Finance asked for this before engineering thought of it.

10. The audit found three more, one of which had created a second unrecorded boundary while fixing a bug that had itself created a first.

Questions for Discussion

1. The fix was correct and the review was competent. What question, asked in review, would have caught this — and is it a question you would think to ask?

2. The ⚠️ callout claims a correct fix can be worse than the bug. Construct the case where it is not — when is an unrecorded definition change genuinely acceptable?

3. The classifier's 13% override rate is offered as evidence it is not a ritual. What rate would worry you, and what would you do about it?

4. _built_by costs 40 bytes a row and answers a question that arises a few times a year. Argue against adding it.

5. fct_session's timeout change was left unrebuilt with the date recorded. How would you make sure a future analyst actually encounters that note?

6. The finance team asked for pre-rebuild figures to be kept. What else in your systems would need the same treatment, and does anything currently keep it?

7. One of the three audit findings was a fix that created a second boundary while correcting a first. How many such layers might your most-edited model have?