Case Study 2: The Compatible Change That Miscounted Orders

"Everybody did everything right. The registry approved it, the policy was followed, the notice was given — and for eleven days we reported cancelled orders that were not cancelled."

Executive Summary

Kestrel's checkout team added a new order status, awaiting_stock, for orders held pending replenishment. They bumped the contract's MINOR version, gave 30 days' notice in the agreed channel, and shipped.

The schema registry approved it, correctly: adding an enum value is a compatible change under Avro's rules.

The data platform's silver.orders model had a CASE statement enumerating known statuses with an ELSE 'cancelled' fallback. For eleven days, every awaiting_stock order was reported as cancelled — about 340 orders, which showed up as an apparent 5.2% spike in the cancellation rate and triggered a merchandising investigation into a problem that did not exist.

This case study is §17.2's enum footnote and §17.9's second residual, at full length. It is here because every process worked and the outcome was still wrong, and because the fix is nine lines of SQL that belong on every categorical column.

Skills applied: enum compatibility (§17.2); the semantics section (§17.4); consumer-side assertions (§17.5 point 3, §17.9); fallbacks that produce plausible answers (Chapter 14, Case Study 2).

Background

The contract, agreed and in force:

semantics:
  status: "One of pending|paid|picked|shipped|delivered|cancelled|refunded.
           Transitions are forward-only EXCEPT refunded, reachable from
           delivered. New values require a MINOR version and 30 days notice."

The change. A new status for orders where an item is out of stock and the customer has elected to wait. Genuinely useful and clearly not any existing status.

What the checkout team did, and every step was correct:

  1. Bumped the contract to 2.2.0 — MINOR, per the policy.
  2. Updated the semantics section with the new value and its meaning.
  3. Posted 30 days' notice in #data-eng, per the policy.
  4. Registered the new schema. The registry approved it.
  5. Waited 30 days.
  6. Shipped.

What the data team did: nothing, because nobody read the message.

The Problem

silver.orders mapped raw status to a modelled one:

CASE o.status
    WHEN 'pending'   THEN 'pending'
    WHEN 'paid'      THEN 'paid'
    WHEN 'picked'    THEN 'in_fulfilment'
    WHEN 'shipped'   THEN 'in_fulfilment'
    WHEN 'delivered' THEN 'complete'
    WHEN 'refunded'  THEN 'returned'
    ELSE 'cancelled'          -- ← the bug
END AS order_state

The ELSE 'cancelled' was written in 2024 by someone reasoning correctly: the only remaining status was cancelled, and an explicit WHEN 'cancelled' plus an ELSE NULL would leave a null that downstream models would have to handle. Mapping the remainder to cancelled was simpler and, at the time, exactly equivalent.

It is a fallback that produces a plausible answer, which is Chapter 14's Case Study 2's exact shape in a different language.

Day 0   awaiting_stock ships. ~31 orders/day enter it.
        Every one is reported as cancelled.
Day 3   cancellation rate up from 2.1% to 2.4%. Inside normal variation.
Day 8   rate at 5.2%. Merchandising opens an investigation into a
        suspected checkout problem.
Day 11  a checkout engineer, asked about cancellations, says
        "cancellations are flat — are you counting awaiting_stock?"

Three days of a merchandising investigation into a problem that did not exist, plus eleven days of wrong cancellation reporting.

⚠️ Failure Mode — ELSE is a decision to be silently wrong about the future

An ELSE branch in a CASE over a categorical column is a commitment about values that do not exist yet. The author cannot know what they will be, so any non-null ELSE is a guess about the unknown, applied forever.

Three ways to write it, and only one is safe:

sql ELSE 'cancelled' -- ⚠️ silently wrong about every future value ELSE NULL -- ⚠️ better; nulls propagate and MIGHT be noticed ELSE ERROR('unknown status: ' || o.status) -- ✅ loud, immediate, specific

The third is not always available — not every engine has an error function usable in an expression, and dbt models generally cannot raise mid-query. Where it is not available, the assertion in §17.9 is the substitute, and it is what Kestrel adopted.

The general rule, and it is the same as Chapter 14's: a fallback that produces a plausible value hides its own cause. Ask of every ELSE, every COALESCE default, and every except: pass: is this producing an answer that looks right? If so, make it loud.

The Analysis

The registry was correct. Under Avro's resolution rules, adding a symbol to an enum is backward compatible for a reader that can handle unknown symbols, and Kestrel's status was carried as a plain string rather than an Avro enum — so from the registry's point of view nothing changed at all.

This is the gap §17.2 warns about. The compatibility check answers can this reader parse this data, and the answer was yes. It does not and cannot answer does this reader handle this value meaningfully, which is a question about the consumer's logic, not about the data's shape.

The notice was given and not read. The 30-day notice went to #data-eng, which at the time carried roughly 40 messages a day. The team's own assessment:

"The process worked. The notice was posted, in the agreed place, 34 days ahead. Nobody read it, and 'read the channel more carefully' is not a control."

🔎 Read the Plan — A notice is not a control

This is the most transferable finding in the case study.

A process that depends on a human reading a message and acting on it is not a control. It is a hope with a paper trail. It fails when the person is on leave, when the channel is busy, when the message is well written but not urgent-looking, and when the reader does not know that they are the one who has to act.

A control is something that fails the build.

Notice Control
Depends on someone reading and acting nothing
Fails when attention is elsewhere never silently
Cost to author writing a message writing a check, once
Cost when it fails an incident a red build

Notices are still worth giving — they provide context, and they are how a human learns why rather than that. But a change policy whose only enforcement is a notice will fail, and the failure will look exactly like this one: everyone compliant, nobody at fault, the outcome wrong.

The question to ask of any agreed process: what fails if nobody reads it? If the answer is "the data," you have a notice where you need a control.

The Decision

Three changes, and the first is nine lines.

1. An assertion against the contract's declared enumeration, running on every load of silver.orders:

-- Fails the build on any status the contract does not enumerate.
-- Nine lines, and it converts a silent miscount into a red build.
SELECT status, COUNT(*) AS n
  FROM {{ ref('bronze_orders') }}
 WHERE status NOT IN ('pending','paid','picked','shipped','delivered',
                      'cancelled','refunded')
 GROUP BY 1
-- dbt: this is a singular test. Zero rows = pass.

2. The enumeration is generated from the contract, not hand-written.

This is the part that makes it durable. A hand-written list is a second place the enumeration lives, and second places drift.

# platform/contracts/generate_assertions.py
#
# Reads every contract's semantics section and emits a dbt test per
# enumerated field. Run in CI; the generated tests are committed, so a
# contract change that adds a value produces a visible diff in the PR.
for field, spec in contract["semantics"].items():
    if "one of" in spec.get("description", "").lower():
        values = parse_enumeration(spec["description"])
        emit_dbt_test(model=contract["consumers"]["silver_model"],
                      column=field, accepted_values=values)

Note what this achieves beyond correctness: when the checkout team bumps the contract to add a value, the generated test changes, which appears as a diff in a pull request, which someone reviews. The notice becomes a code review.

3. The ELSE was replaced.

CASE o.status
    WHEN 'pending'        THEN 'pending'
    WHEN 'paid'           THEN 'paid'
    WHEN 'picked'         THEN 'in_fulfilment'
    WHEN 'shipped'        THEN 'in_fulfilment'
    WHEN 'delivered'      THEN 'complete'
    WHEN 'refunded'       THEN 'returned'
    WHEN 'cancelled'      THEN 'cancelled'
    WHEN 'awaiting_stock' THEN 'held'
    -- No ELSE. An unmapped status produces NULL, and the not_null test
    -- fails the build. Ch. 17 §17.9.
END AS order_state

Every value explicit, no ELSE, and a not_null test on the output. The null is the loud failure the ELSE was suppressing.

📐 Design Decision — Should the registry have caught this?

The retrospective asked whether a different schema design would have prevented it: model status as an Avro enum rather than a string, so the registry sees the change and a stricter compatibility mode rejects it.

The case for: the registry becomes the control. Adding a value would fail registration under FULL compatibility, forcing an explicit coordinated change.

The case against, which won:

  • It makes adding a status a breaking change, requiring a MAJOR version and a new topic for what is legitimately an additive business change. That is a heavy tax on the producer for the consumer's benefit, and §17.7 warns exactly against contracts that feel like impositions.
  • It moves the failure to the wrong place. The producer would be blocked from a reasonable change because a consumer's CASE statement is incomplete. The consumer's incompleteness is the consumer's problem, and it should fail on the consumer's side.
  • It does not generalize. Most categorical columns arrive as strings from sources you do not control (§17.8), where you cannot impose an enum type at all.

The resolution: the schema stays permissive, and the consumer asserts. The producer can add a value freely; the consumer's build goes red until someone handles it. The failure lands on the party who has to act, which is §17.1's whole argument applied one level down.

What Happened

The 340 miscounted orders were corrected by a rebuild of the affected partitions once awaiting_stock was mapped.

The generated assertions cover 14 enumerated fields across four contracts. In the eighteen months since:

  • They have fired four times. Three were new values added by producers following the policy correctly — caught at build time, mapped within a day, nobody paged. One was a value nobody had announced at all, from an acquired system, which is exactly the case a notice-based process cannot cover.
  • The ELSE-free CASE plus not_null has fired once, on a null status from a source bug, which the old ELSE 'cancelled' would have silently reported as a cancellation.

A codebase audit found nineteen other CASE statements with a non-null ELSE over a categorical column. Eleven were rewritten; eight were judged safe because the domain is genuinely closed — booleans and a two-valued channel flag — and were annotated with a comment saying so, which is the part that stops the next person wondering.

That audit is Chapter 1's lesson recurring: when you find one instance, grep for the shape.

Lessons

  1. Adding an enum value is compatible on paper and frequently breaking in practice. The registry answers can this be parsed, not is it handled meaningfully.

  2. ELSE in a CASE over a categorical column is a decision to be silently wrong about values that do not exist yet. Enumerate every value and omit the ELSE; let the null fail a test.

  3. A fallback that produces a plausible answer hides its own cause. Ask it of every ELSE, every COALESCE default, and every except: pass.

  4. A notice is not a control. A process whose only enforcement is someone reading a message will fail, and the failure looks like this: everyone compliant, nobody at fault, the outcome wrong. Ask of any agreed process: what fails if nobody reads it?

  5. Generate assertions from the contract, so the enumeration lives in one place and a contract change appears as a reviewable diff. The notice becomes a code review.

  6. Do not make the producer's reasonable change breaking to protect the consumer's incomplete logic. The consumer's incompleteness is the consumer's problem and should fail on their side.

  7. When you find one instance, grep for the shape. Nineteen other non-null ELSE branches; eleven rewritten, eight annotated as deliberately safe.

  8. An eleven-day wrong number caused a three-day investigation into a problem that did not exist. The cost of a silent error includes the work it causes elsewhere.

Questions for Discussion

  1. Every process step was followed correctly and the outcome was wrong. Is the process at fault, or was this always going to require a technical control? Could the process have been designed so that a notice was sufficient?

  2. The 2024 author's ELSE 'cancelled' was correct when written. What review comment would have flagged it, phrased so as not to sound pedantic about a line that was, at the time, right?

  3. The 📐 callout declines to make the producer's change breaking. Construct the scenario where the opposite call is correct — where you should force a coordinated change.

  4. Eight ELSE branches were judged safe and annotated. How long do you expect those annotations to remain accurate? What would you do at the five-year mark?

  5. One assertion firing caught a value nobody announced, from an acquired system. What proportion of your own categorical columns come from sources that will never follow a notice policy?

  6. The generated tests turn a contract change into a PR diff. What else in this book could be turned from a notice into a diff?

  7. The merchandising investigation cost three days chasing a problem that did not exist. How would you account for that class of cost when arguing for consumer-side assertions?