Case Study 1: Four Failed Reconciliations

"Each time it failed we found a rule that existed in somebody's head, in the SQL, and nowhere in between."

Executive Summary

Kestrel's capstone reconciliation failed four times before it closed.

None of the four was a bug in the reconciliation. Each was a business rule that was real, was implemented somewhere, and had never been written down — so the gold layer implemented three of them and the reconciliation implemented four, or the reverse.

attempt  gap                    cause
1        $1,198,204.71 over     timezone: Nov 1 in America/New_York vs UTC
2        $41,208.00 over        the `is_test` filter was missing in gold
3        $911,154.83 over       gift cards counted as revenue
4        $187,442.19 under      refunds netted against the SETTLEMENT month
                                rather than the month sold
5        $0.00                  closed

The four gaps are worth studying as a set, because they fail in four different ways: one is a boundary, one is a missing filter, one is an accounting rule an engineer decided alone, and one is a rule that was implemented correctly and pointed at the wrong month.

The last is the hardest, was found last, and is the only one that would have survived a code review.

Skills applied: the acceptance criterion (Chapter 1 §1.7); the four rules (§38.5); why R4 differs in kind from R1–R3; and Chapter 37 §37.7's finding that a difference is usually a rule nobody wrote down.

Attempt 1: The Timezone

The gap was $1,198,204.71, and R2 — the cancelled-order rule — removes $1,204,882.35. The two are within 0.55% of each other, which is close enough that the team read it as a match and concluded R2 had been applied twice.

It had not been. The near-match was a coincidence, and it cost a day on the wrong hypothesis.

⚠️ Failure Mode — a suspicious coincidence that was one

This book has argued three times that a constant, exact difference means a rule disagreement. It is a good heuristic and it is not a proof, and here it sent the investigation in the wrong direction for a day.

What made it seductive: the gap was within half a percent of a known rule's total, and the explanation ("R2 applied twice") was simple, testable, and would have been satisfying. Nobody asked why it was not exact — a double-applied filter removes exactly the same rows twice, so the match should have been to the cent, and 0.55% off is not "close," it is wrong.

What killed it: the line count. Applying R2 twice would have removed exactly 42,907 more lines; the count was off by 39,114. The money nearly matched and the rows did not.

Which is §38.8's argument, arriving as an accident: the second reconciliation — on row counts — falsified a hypothesis the money-only reconciliation had supported. If Kestrel had reconciled money alone, the day would have been longer.

Two honest lessons. First, "a near match means a shared cause" is a weak inference dressed as a strong one: a mechanical cause produces an exact match, so an inexact one is evidence against the hypothesis, not for it. The team rounded 0.55% to "basically the same" and lost a day to it.

Second, the way to test it is a second, independent measurement, not a stronger conviction — which is §38.8's argument arriving by accident, since the row-count reconciliation existed only because somebody had added it for a different reason.

The actual cause: the source query used placed_at >= '2026-11-01' against a timestamptz column, which Postgres evaluated in the session timezone (America/New_York), while the gold model partitioned on a UTC date. The first four hours of November 1 UTC fell into October on one side.

The fix is one line and the lesson is Chapter 34 Case Study 2's: an ambiguous timestamp boundary is a business rule wearing a parse's clothes. Both sides now state their timezone explicitly, and the reconciliation asserts they match before comparing anything.

Attempt 2: The Missing Filter

Gap: $41,208.00 over — exactly R1's total.

The gold layer was not filtering test orders. The rule was documented in the reconciliation, the checkout service set is_test correctly, and silver.stg_orders passed the flag through — and no model ever used it.

How it survived thirty-seven chapters: test orders are 1,412 lines of 856,117 — 0.16% — and every downstream number was 0.16% high, consistently, forever. Chapter 37 Case Study 1's exact structure: a small, stable error that nothing independent was checking.

🏭 From the Pipeline — the rule that was written down in the wrong place

R1 existed in three places and was implemented in none of the ones that mattered:

text the checkout service SETS is_test correctly silver.stg_orders CARRIES is_test correctly the reconciliation EXCLUDES is_test correctly gold.fct_order_line does not mention it

Everybody who touched it did their part. The flag is produced, propagated, and respected by the check — and the thing the business actually reads was never told about it.

This is the failure mode that a reconciliation exists to catch, and it caught it. But notice when: at the capstone, after the model had been in production for the length of the build. A reconciliation written in week one (§38.14) would have caught it the day fct_order_line was first built, because it would have failed immediately and specifically.

The generalizable warning: a rule that is carried but not applied looks exactly like a rule that is applied. The column is there, the value is right, and every code review sees a model that clearly knows about test orders. The only thing that distinguishes them is a check that computes the number both ways.

Attempt 3: Gift Cards

Gap: $911,154.83 over — R3's total.

Gift card lines were counted as revenue. They are not: a gift card is a liability at sale and becomes revenue on redemption.

Nobody on the data team knew this, and there was no reason they should have. It is an accounting rule, it is not written in any system, and the engineer who built fct_order_line made a reasonable decision — a line item with a price is revenue — that happens to be wrong.

📐 Design Decision — three of the four rules belong to finance, and that is the finding

When the four rules were finally written down, their ownership came out like this:

text R1 test orders #data-platform a platform artifact R2 cancelled #finance "cancelled" could mean two things R3 gift cards #finance an accounting rule R4 refunds netted #finance a recognition rule

Three of four are decisions the data team is not qualified to make, and the capstone found all three by making the wrong one first.

The uncomfortable implication: every one of those decisions had already been made — silently, by an engineer, in SQL — and had been producing a number the business relied on. Not through negligence: there was no point in the process at which somebody was asked.

What Kestrel changed, and it is one line in a template: the pull-request template for any model in gold/ now asks

"Does this model encode a rule that finance owns? If so, link the decision."

It has been answered "yes" four times in a year, and three of the four went to finance and came back changed. The fourth came back confirmed, which is also a useful outcome — the engineer's instinct was right and now it is recorded as a decision rather than as an assumption.

This is Chapter 30 §30.8's certified-definition problem at its root: the reason four definitions of active_customer appear is that four people each made a reasonable decision, alone, at four different times. The template question is where you interrupt that.

Attempt 4: The Refund Month

Gap: $187,442.19 under — the first attempt that came in low, and the hardest to find.

The refund rule was implemented. Both sides netted refunds. They netted them against different months.

gold:            refunds settled in November, against November
reconciliation:  refunds against THE MONTH THE ORDER WAS SOLD

A refund settled on 3 November against an order placed on 28 October belongs to October's net revenue — the sale and its reversal are the same transaction. Gold was assigning it to November.

🔎 Read the Plan — the rule that was right and pointed at the wrong month

This is the only one of the four that would have survived a code review, and the reason is worth being precise about.

The other three are absences. A missing timezone, a missing filter, a missing accounting rule. A careful reviewer asking "what rules apply here?" finds all three, because the answer is a list and three items are not on it.

R4 was present. The model netted refunds, the code was correct, the tests passed, and the SQL reads as obviously right:

sql -- gold.daily_revenue, the wrong version SELECT date_trunc('day', r.settled_at) AS revenue_date, -sum(r.amount_cents) AS refund_cents FROM refunds r GROUP BY 1

settled_at is the obvious column. It is when the money moved, it is what the payment processor reports, and grouping a refund by when it settled is what you would do without thinking. The correct version groups by the order's placed_at, which requires a join the wrong version does not need — so the wrong version is also the simpler one.

Three properties made it hard to find:

• It nets out over a long enough window. Across a quarter, refunds assigned to the wrong month mostly cancel; only a monthly boundary reveals it, and only in a month whose neighbours differ.
• It is small. $187,442.19 on $21.9M is 0.85% — and it was the fourth attempt, so three larger gaps had masked it entirely on attempts 1–3.
• It fails in the low direction, which reads as "we are missing rows" and sends you looking for a filter that is too aggressive rather than for a rule pointed sideways.

The general form: a rule can be present, correct, tested, and applied to the wrong dimension. The check that finds it is not "is the rule implemented" but "is the rule implemented against the same key on both sides" — and §38.5's 📐 is the reason this rule specifically is the one where that question matters: R4 is the only rule that revalues rather than filters, so it is the only one with a choice of which period to attribute to.

What Happened

Attempt 2 took twenty minutes and attempt 4 took three days, and the difference is not size — it is whether the gap matched a rule's total exactly.

When it does, the search is over. When it does not, you are looking for a rule that is present and wrong, which is a much larger space.

Four changes came out of it:

The four rules are written down, with owners, in platform/finance/rules.md, and each links to the model that implements it and the assertion that checks it.

The reconciliation asserts its own preconditions — both sides' timezone, both sides' as-of date, and that the rule set on each side is the same list — before comparing any numbers.

The row count is reconciled alongside the money, which is what falsified attempt 1's hypothesis and is now §38.8's argument.

And the pull-request question, above.

Lessons

1. Four failures, four different shapes: a boundary · a missing filter · an accounting rule decided by an engineer · a correct rule pointed at the wrong month.

2. ⚠️ "An exact match means a shared cause" is a strong prior and not evidence. Attempt 1's gap matched R2's total to the cent by coincidence and cost a day. The row count falsified it — which is the argument for reconciling more than one property.

3. 🏭 A rule that is carried but not applied looks exactly like a rule that is applied. is_test was set, propagated, and respected by the check — and never used by the model the business reads.

4. 0.16% consistently, forever, is invisible. Chapter 37 Case Study 1's structure again: a small, stable error with nothing independent checking it.

5. 📐 Three of the four rules belong to finance, and the capstone found all three by making the wrong decision first. There was no point in the process at which anybody was asked — which is a process defect, not a knowledge one.

6. The fix is one line in a pull-request template: "does this model encode a rule that finance owns? If so, link the decision." Four yeses in a year; three came back changed, one came back confirmed — and the confirmation is also valuable, because an assumption became a decision.

7. 🔎 A rule can be present, correct, tested, and applied to the wrong dimension. R4 netted refunds by settled_at rather than by the order's month, and the wrong version is the simpler one — it needs no join.

8. That one would have survived a code review, because the other three are absences and a reviewer listing the applicable rules finds absences.

9. It nets out over a quarter and only shows at a monthly boundary, it is 0.85%, and it fails in the low direction — which sends you looking for an over-aggressive filter.

10. R4 is the only rule that revalues rather than filters, so it is the only one with a choice of which period to attribute to. §38.5's distinction is not academic.

11. Twenty minutes versus three days, and the difference is whether the gap matched a known total. When it does, the search is over; when it does not, you are in a much larger space.

Questions for Discussion

1. Attempt 1's coincidence cost a day. What would have shortened it besides the row count — and would you have trusted the coincidence?

2. is_test was set, carried, and checked, and never applied. Audit one of your own gold models for a column it carries and does not use.

3. Three of four rules belong to finance. List the rules your gold layer encodes and mark the owner of each. How many are yours to decide?

4. The PR template question has been answered "yes" four times in a year. Is that too few? What would a higher number mean?

5. R4's wrong version is simpler than the right one. Where else in your platform is the simpler query the wrong one, and what makes that discoverable?

6. The refund error nets out over a quarter. What other defects does your reporting period hide?

7. Should the reconciliation have been written in week one? §38.14 says yes. What would it have cost, and what would it have been unable to check?