Case Study 1: The Stored Procedure That Was Right for Eleven Years and Wrong for Nine

"We found the bug in week two. It took until week fourteen to be allowed to fix it, and the argument was never about whether it was a bug."

Executive Summary

sp_load_orders — 1,420 lines of PL/pgSQL, no owner, no tests, five undocumented dependencies — had been loading Kestrel's order facts since 2015.

Shadow running found that it excluded orders whose first line item was a gift card, because of a WHERE clause written to filter test orders in 2016 and never revisited.

orders excluded per day, on average         31
period the bug existed                      9 years
total orders never loaded                ~101,900
revenue never reported                  $7,726,850

The new pipeline was correct. The old one was wrong. Fixing it was a twelve-week argument, and the argument was never about the facts.

The resolution — emitting both numbers in two columns until the business owner decided — is the transferable part, and it took the migration from blocked to shipping in an afternoon.

Skills applied: shadow reconciliation (§37.6); the legacy-is-wrong decision (§37.7); the two-column technique; and the political problem of a correction that produces a visible step change.

Background

sp_load_orders scored 16.6 on §37.4's ordering — fifth of twelve — with the highest difficulty in the estate (21.7) and the second-highest reach (20).

What was known before reading it: seven consumers, no owner, no tests, 1,420 lines, five inputs not named in any configuration, 52 minutes of runtime, business-critical.

What reading it produced, after two weeks: a specification, and the observation that roughly a quarter of it handled cases that no longer arise.

The rebuild was three weeks — a staging model, a fact model, and Chapter 23's assertions — and shadow running began in week five.

The Problem

Day two of shadow running: the new pipeline reported more orders than the old one.

day        legacy      new    delta
2026-04-02  6,541    6,573      +32
2026-04-03  6,602    6,631      +29
2026-04-04  6,488    6,522      +34
...
mean                            +31

A stable +31 orders per day, which per Chapter 34 Case Study 2's lesson is a suspicious shape: real differences vary; a constant offset means two systems disagree about a rule.

Finding it took an afternoon. The excluded orders were pulled and inspected:

of the 31 excluded orders on 2026-04-02:
  31 had a gift card as the FIRST line item
   0 had any other property in common

Then the legacy code:

-- inside sp_load_orders, added 2016-03
AND NOT EXISTS (
    SELECT 1 FROM order_lines ol
     WHERE ol.order_id = o.order_id
       AND ol.line_number = 1
       AND ol.sku LIKE 'TEST-%'
       OR ol.sku LIKE 'GC-%'          -- <-- see below
)

⚠️ Failure Mode — an operator-precedence bug that was correct when it was written

The OR binds looser than the ANDs. The condition parses as:

sql (ol.order_id = o.order_id AND ol.line_number = 1 AND ol.sku LIKE 'TEST-%') OR (ol.sku LIKE 'GC-%')

The second clause is unqualified — it matches a gift-card line on any order, not on the order being filtered. Combined with the NOT EXISTS, it excludes every order that has a gift card as its first line.

And the part that makes this a case study rather than a bug report: it was correct when it was written. In 2016, Kestrel sold gift cards through a separate system, they never appeared in order_lines, and the second clause matched nothing. The intent — exclude test orders — was met, and the latent defect was invisible because the data could not trigger it.

Gift cards moved into the main catalog in April 2017. From that day the clause excluded real orders, and nothing noticed for nine years, because:

• The order count fell by 0.47%, once, in a week when several things changed.
• Nothing reconciled fct_order_line against an independent source (Chapter 36 Case Study 1's finding, in a different system).
• Everybody's baseline was the legacy number, so the reports were internally consistent forever.

The generalizable warning: a condition that matches nothing today is not dead code. It is a trap armed for the day the data changes, and it is invisible to review because it does nothing. The only thing that finds it is a second implementation — which is what a migration is.

The Analysis

Quantifying it required a decision about how far back to look.

gift cards entered the catalog       2017-04
orders excluded, per day, mean           31
days since                            3,287
orders never loaded                 ~101,900
mean order value                     $75.83
revenue never reported            $7,726,850

Then the harder question, which took the rest of the twelve weeks: what does that mean?

🔎 Read the Plan — three things the number did not mean, and one it did

The engineering finding was straightforward. Interpreting it was not, and the team's first draft of the memo made three claims that were wrong.

"We under-reported revenue by $7.7M." Not exactly. The orders were real, the money was collected, and it appeared in the payment processor's totals and in the general ledger. What was under-reported was gold.daily_revenue and everything derived from it — which is the analytics platform, not the accounts.

"Nine years of reports were wrong." True and less alarming than it sounds, because the error was a stable 0.47% and every trend, comparison, and year-over-year figure was computed on the same basis. A consistent 0.47% understatement distorts a level and preserves a trend, and most decisions made on that data were about trends.

"Fixing it corrects nine years of history." No. Fixing the pipeline corrects the future. Correcting history is a separate, larger project — a backfill of nine years of facts — and conflating the two is what made the initial conversation go badly, because the business heard "restate nine years of reports" when the proposal was "load these orders going forward."

What it did mean, and this is what the second memo said in one line: "from the cutover date, daily order counts will rise by about 31 and daily revenue by about $2,351, permanently, and this is a correction rather than growth."

That sentence is the deliverable. It is small, it is specific, it warns the people whose dashboards will show a step change, and it does not ask anybody to relitigate nine years.

The Decision

Twelve weeks, and the shape of the delay is the lesson.

week  2   bug found
week  3   quantified; memo v1 written
week  4-9 the memo circulates. Finance, analytics, and the exec sponsor
          each ask a version of "what does this do to my numbers?"
          -- the migration is BLOCKED on this job
week 10   memo v2: the one-line version above
week 11   the two-column proposal
week 12   agreed; migration resumes
week 14   cutover

Six weeks were spent with the migration blocked on a decision nobody was refusing to make. Every party wanted the same thing — to understand the impact on their own numbers — and none of them could, because the impact was described in a memo rather than shown.

📐 Design Decision — emit both numbers, and let the decision stop blocking the engineering

The unblocking move was to stop asking for a decision and start providing the data the decision needed.

sql -- gold.daily_revenue, during the overlap SELECT order_date, revenue_cents AS revenue_cents, -- legacy basis revenue_cents_corrected AS revenue_cents_corrected -- with gift cards FROM ...

Two columns. The legacy-compatible one keeps every existing dashboard working, unchanged, on the basis it has always used. The corrected one is available to anyone who wants to look.

Four things this bought, and the fourth is the one that generalizes:

• The migration stopped being blocked. The engineering shipped; the decision continued.
• The impact became visible rather than described. Finance could look at both columns for sixty days and see exactly what the change did to their close.
• It removed the deadline from the decision, which removed the defensiveness. Nobody was being asked to approve a step change under time pressure.
• It converted an argument into an observation. The memo said "revenue will rise by about $2,351/day"; the two columns said what it actually did, per day, for two months.

The cost is that two columns is a temporary state that must be ended, and temporary states persist. Kestrel set the removal date in the same ticket as the cutover and removed the legacy column eleven weeks later, on schedule — because the decision had, by then, actually been made.

The general form: when a correctness change is blocked on a business decision, ship both and let the decision be made from data. It works for a renamed metric, a changed definition, a corrected calculation — anywhere the blocker is "I do not know what this does to me."

And the second decision: history was not restated. The nine years remain on the legacy basis, with a note in the catalog (§30.2's gotchas) recording the date of the change and its size. A backfill was scoped at four weeks and judged not worth it, because no decision would be made differently.

What Happened

Three building, eleven deciding. The team recorded that ratio deliberately, and it is the number they cite when scoping a migration: for any job whose output is a reported number, budget more time for the conversation than for the code.

Two things found afterwards, both by the same mechanism:

The same operator-precedence pattern appeared twice more in the estate, in sp_refund_recon and in one Informatica workflow. Neither was currently misfiring — both had the unqualified clause and no data to trigger it — and both were fixed during their own migrations, as traps rather than as bugs.

And the reconciliation that would have caught it in 2017 now exists: gold.fct_order_line against Postgres orders, daily (Chapter 36 Case Study 1's independent check). It would have shown a 0.47% gap the week the gift cards moved.

Lessons

1. A stable, constant difference is a rule disagreement, not a real difference. +31 orders every day is not variance — Chapter 34 Case Study 2's lesson, in a different system.

2. ⚠️ The bug was correct when it was written. An operator-precedence error whose second clause matched nothing until the data changed, eleven months later.

3. A condition that matches nothing today is not dead code — it is a trap armed for the day the data changes, and it is invisible to review because it does nothing. The only thing that finds it is a second implementation, which is what a migration is.

4. It survived nine years because nothing reconciled against an independent source, and because everybody's baseline was the legacy number, so the reports were internally consistent forever.

5. 🔎 Three things the finding did not mean. Not that the money was lost — the ledger was right. Not that every report was wrong — a constant 0.47% distorts a level and preserves a trend. Not that history would be restated.

6. Conflating "fix the pipeline" with "restate history" is what made the first conversation go badly. The business heard a nine-year restatement when the proposal was a forward-looking correction.

7. The deliverable was one sentence: "from cutover, order counts rise by ~31/day and revenue by ~$2,351/day, permanently, and this is a correction rather than growth."

8. 📐 Emit both numbers. It unblocked the engineering, made the impact visible rather than described, removed the deadline from the decision, and converted an argument into an observation. Set the removal date in the same ticket as the cutover.

9. Three weeks building, eleven weeks deciding. For any job whose output is a reported number, budget more time for the conversation than for the code.

10. History was not restated, and that was the right call: a four-week backfill that would change no decision. Documented in the catalog instead.

11. The same pattern was found twice more, both currently harmless, both fixed as traps during their own migrations.

Questions for Discussion

1. The bug was invisible for nine years because everything downstream used the same baseline. What else in your platform is internally consistent and externally unchecked?

2. The two-column technique removed a six-week block. What are its failure modes — where would shipping both numbers be worse than deciding?

3. History was not restated. Construct the case where it must be, and say what changes.

4. "A constant 0.47% distorts a level and preserves a trend." Is that a reassurance or a rationalization? When does it stop being true?

5. Two more instances of the same latent pattern were found and fixed as traps. How would you search for them without a migration?

6. Three weeks building, eleven deciding. Should the team have started the business conversation before the shadow run, in anticipation? What would that have cost?

7. The independent reconciliation now exists and would have caught this in 2017. What is the equivalent check you do not currently run?