Case Study 1: The Quarantine Nobody Drained

"We didn't delete the bad rows. We put them somewhere safe. For fourteen months."

Executive Summary

Kestrel added a quarantine table in 2025: rows failing validation would be diverted rather than blocking the load, so that one bad record could not stop a night's revenue.

It worked exactly as designed. Bad rows went to quarantine.order_items with a reason attached, good rows proceeded, and the pipeline stopped failing on individual records.

Nobody ever looked at it. Fourteen months later it held 41,900 rows, of which 38,104 were perfectly valid orders diverted by a validation rule that had been wrong since the day it was written — a quantity BETWEEN 1 AND 100 bound that a wholesale distributor routinely exceeds.

$$38{,}104\ \text{order lines} \times \$28.09 = \$1{,}070{,}341\ \text{of revenue never reported}$$

The quarantine was not a safety mechanism. It was a DELETE with a paper trail, and the paper trail was what made it feel responsible.

Skills applied: quarantine and its four obligations (§23.9); thresholds that fire on good days (§23.7); ownership (§23.12); the distinction between detection and action.

Background

Why it was built. In early 2025 a single malformed row — a quantity of -1 from a returns integration — failed the nightly load, and the mart was six hours stale on a Monday. The postmortem's action item was reasonable: one bad row should not stop the pipeline.

The implementation, which is textbook:

-- Bad rows are diverted, with a reason, and a timestamp.
INSERT INTO quarantine.order_items
SELECT *, 'quantity_out_of_range' AS reason, current_timestamp AS quarantined_at
  FROM staged
 WHERE quantity < 1 OR quantity > 100;

-- Good rows proceed.
INSERT INTO silver.order_items
SELECT * FROM staged WHERE quantity BETWEEN 1 AND 100;

The bound came from a SELECT at the time it was written:

SELECT MIN(quantity), MAX(quantity), COUNT(*) FROM staged;
-- 1, 84, 17,691

Maximum observed 84, so the bound was set at 100. Which is §23.5's profiler antipattern, written by hand: it encodes the data rather than the contract, and it does so with a headroom that feels generous and is arbitrary.

What nobody did was ask a business partner what the maximum legitimate quantity is. The answer, which took one message to find out in the eventual investigation, is that there is no maximum — wholesale customers order pallets.

The Problem

There was no problem. That is what took fourteen months.

The pipeline succeeded every night. Revenue reconciled inside its 0.5% tolerance (Chapter 20 Case Study 2). No test failed. The quarantine table grew at about 90 rows a night and nothing measured it.

The discovery came from coverage.py. Running the six-box matrix after the audit in Chapter 23 §23.10 produced a list of empty boxes, and working through them raised a question nobody had asked:

"What is in quarantine.order_items?"

SELECT reason, COUNT(*), MIN(quarantined_at), MAX(quarantined_at)
  FROM quarantine.order_items GROUP BY 1 ORDER BY 2 DESC;

reason                      n        first          last
quantity_out_of_range   38,122   2025-06-02     2026-08-14
null_customer_id         2,911   2025-06-02     2026-08-14
future_ordered_at          844   2025-07-19     2026-08-11
negative_revenue            23   2025-09-04     2026-02-28
                        ------
                        41,900

The Analysis

Step 1: are the quarantined rows actually bad?

SELECT quantity, COUNT(*) FROM quarantine.order_items
 WHERE reason = 'quantity_out_of_range' GROUP BY 1 ORDER BY 1;

quantity      n
      -1     18        ← genuinely bad. The original incident.
     101  1,204
     120    889
     144  2,317        ← a pallet
     288  1,996
     ...
   1,440     41

Eighteen rows are negative. 38,104 are large. And a large quantity is not an error — it is a wholesale order, and Chapter 21 Case Study 1 established that Kestrel has a distributor placing consolidated orders at 43.8 lines each.

The validation rule was wrong from the first night, and it was wrong in the direction that produces no complaint: it silently removed the largest orders.

Step 2: what did it cost?

$$38{,}104\ \text{lines} \times \$28.09\ \text{per line} = \$1{,}070{,}341$$

And the revenue reconciliation could not have caught it, which is the part worth sitting with.

Chapter 20 Case Study 2's control compares warehouse revenue to the payment processor. Wholesale customers are invoiced on net-30 terms; they do not pay by card, and the processor has never heard of them. So the quarantined revenue was missing from the warehouse and missing from the reference system, and the two agreed perfectly.

A reconciliation covers what the second system knows about, and the rows it does not cover are disproportionately the unusual ones — which are disproportionately the rows a validation rule derived from typical data will reject. The gap in the control and the gap in the rule have the same cause, and that is not a coincidence you can rely on being rare.

Step 3: why did nobody look?

The postmortem answered this precisely, and the answer is structural rather than personal:

• The quarantine had no owner. It was created by the action item, and action items produce artifacts, not obligations.
• Nothing measured its size. The six-box matrix has no "is anything accumulating" box, and neither did anything else.
• There was no replay path. Even someone who looked and understood would have had no documented way to re-admit a row, which converts a five-minute check into a project.
• Its growth was small and steady. About 90 rows a night against 17,753 loaded is half a percent, which is invisible in every aggregate anyone looks at.

⚠️ Failure Mode — a diversion feels safer than a deletion and behaves identically

INSERT INTO quarantine and DELETE differ only if somebody comes back. The row is out of the mart either way; the only difference is whether the removal is recoverable in principle or recovered in practice, and those are separated by a person with a calendar.

Quarantine is genuinely the right pattern. The alternative here — blocking the load on one malformed row — is what the 2025 postmortem correctly rejected. The mistake was not the quarantine. It was treating the quarantine as the end of the work.

§23.9's four obligations exist because each one was missing here:

	Kestrel had	Consequence
a monitored row count	✗	41,900 rows accumulated invisibly
an owner	✗	nobody was expected to look
an idempotent replay path	✗	recovery was a project, not a task
a retention decision	✗	rows aged out of the source's retention

The last row is the one that turned a recoverable problem into a partly permanent one. The CDC source retains 18 months (Chapter 20 §20.4); rows quarantined in June 2025 were still recoverable in August 2026 by a margin of two months. Another quarter and the earliest of them would have been gone from every system.

The test to apply to any quarantine, dead-letter queue, or reject file you own: what is its row count today, and who saw that number last? If you cannot answer the second half, it is a deletion.

The Decision

Four changes, matching the four obligations.

One: fix the rule, and fix it by asking rather than by measuring.

-- Quantity has no business maximum -- wholesale customers order pallets.
-- Confirmed with #finance 2026-08-15. What IS invalid is a non-positive
-- quantity, which is the failure the 2025 incident actually involved.
WHERE quantity < 1

The new rule quarantines 18 rows out of 41,900. The other 38,104 in that bucket were never invalid.

📐 Design Decision — a bound from data versus a bound from a person

The original BETWEEN 1 AND 100 came from MAX(quantity) = 84 plus headroom. That is a defensible way to write a bound and it produced a bound that was wrong on the first night.

The general problem: a bound derived from observed data encodes the range of what has happened, and validation is a claim about what is allowed. Those differ exactly where it matters — at the tails, on the rows that are unusual, which are disproportionately the rows that are interesting.

Three ways to get a bound, in descending order of quality:

• Ask someone who owns the process. "Is there a maximum order quantity?" One message. The answer here was no, which no amount of data analysis would have produced.
• Derive it from a hard constraint. A quantity cannot be negative because of what quantity means. That bound is not going to move.
• Observe it, and mark it as provisional. Acceptable as a starting point, and only if the comment says so and something reviews it.

The tell that you are in the third case and pretending to be in the first: the bound is a round number. 100, 1000, 30 days, 5%. Real constraints are rarely round, and a round bound is usually a guess with a confident face.

And the asymmetry that decides how to guess: a bound that is too tight silently discards good data; a bound that is too loose admits bad data that later tests may still catch. When you must guess, guess loose — and put the tighter number in a warning rather than a rejection.

Two: an owner, and a monitored count.

      - dbt_utils.expression_is_true:
          expression: "count(*) < 200"
          config:
            where: "quarantined_at::date = current_date - 1"

Plus a weekly report of total quarantine size by reason, to #data-eng, with a named owner per reason — because "the data team owns it" is what "nobody owns it" looks like on an org chart.

Three: an idempotent replay path, documented and tested:

# Re-admit rows whose reason no longer applies. Idempotent: it merges on
# (order_id, line_number), so running it twice is a no-op. Ch. 20 §20.3.
python platform/quality/replay_quarantine.py \
    --reason quantity_out_of_range --since 2025-06-01 --dry-run

The --dry-run is not politeness. A replay reads from a table whose rows were, by definition, not trusted; seeing the count and a sample before writing is the difference between a repair and a second incident.

Four: a retention decision, written down. Ninety days, after which a quarantined row is deleted — and the deletion is announced in the weekly report, so that discarding data is an event somebody sees rather than a silent expiry.

The backfill. 38,104 rows were replayed into silver.order_items and the downstream models rebuilt for the affected range. Because fct_order_item is incremental with a merge strategy (Chapter 20), the replay was a bounded rebuild rather than a migration.

Revenue was restated for fourteen months, and the restatement is the part worth noting: every restated month moved in the same direction, by between 0.3% and 0.8% of that month's wholesale revenue. A consistent one-directional bias of that size is exactly what a reconciliation is for — against a reference system that covers the rows in question.

What Happened

The audit found two more diversions nobody was watching:

A Kafka dead-letter topic (Chapter 15) with 2.3 million messages, retained 7 days — so most of its history was already gone, and there is no way to know what was in it. The consumer had been writing to it since the topic was created, and its _lag alert was on the main topic.

A _rejects/ prefix in the lake with 11,400 files from the API ingester (Chapter 16), the oldest from 2024. Recoverable, and being worked through.

Neither had an owner. Both had a count of zero people who had ever looked.

🏭 From the Pipeline — every system has a place where rows go to be forgotten

Find yours. They are called different things and they behave identically:

text quarantine tables dead-letter queues _rejects/ prefixes _errors/ directories "pending review" flags status = 'held' retry queues with no consumer NULL-ed foreign keys

For each one, four questions, and the second is usually the one that fails:

1. What is its size today?
2. Who saw that number last, and when?
3. How would someone put a row back?
4. What happens when it is old?

A place where rows go, with no answer to question 2, is not a safety mechanism. It is a data loss with good intentions, and the good intentions are what stop anyone looking.

The version of this that hurts most is the one with short retention, because by the time you ask, the evidence of what you lost is gone too.

Lessons

1. A quarantine without all four obligations — a monitored count, an owner, an idempotent replay path, and a retention decision — is a DELETE with a paper trail.

2. The paper trail is what stops anyone looking. Diversion feels recoverable, and feeling recoverable is what removes the urgency to recover.

3. A bound derived from observed data encodes what has happened, not what is allowed. They differ at the tails, which is where the interesting rows are.

4. Ask a person. "Is there a maximum order quantity?" was one message and the answer was no — which no amount of data analysis produces.

5. A round bound is usually a guess with a confident face. Real constraints are rarely round.

6. When you must guess, guess loose. A tight bound silently discards good data; a loose one admits data that later tests may still catch.

7. A reconciliation covers what the second system knows about. Wholesale is invoiced, not card-paid, so the payment processor had never heard of the missing orders and the two systems agreed perfectly.

8. The rows a reference system does not cover are disproportionately the unusual ones — which are disproportionately the rows a bound derived from typical data will reject. The gap in the control and the gap in the rule have the same cause.

9. "The data team owns it" is what "nobody owns it" looks like on an org chart. Name a person per reason.

10. A replay needs a --dry-run, because it writes from a table whose rows were by definition not trusted.

11. Announce retention deletions. Discarding data should be an event somebody sees, not a silent expiry.

12. Every system has a place where rows go to be forgotten. Dead-letter queues, _rejects/ prefixes, held statuses, retry queues with no consumer. Ask what its size is and who saw that number last — and the short-retention ones are the worst, because the evidence of the loss expires too.

Questions for Discussion

1. The 2025 postmortem correctly concluded that one bad row should not stop the pipeline. What should its action item have said instead of "add a quarantine"?

2. The BETWEEN 1 AND 100 bound came from MAX(quantity) = 84 and looked like diligence. What review comment would have caught it, and would you have written that comment?

3. The payment-processor reconciliation structurally could not see wholesale revenue. How would you audit your existing reconciliations for what they do not cover?

4. The Kafka dead-letter topic's history is gone. What is the right response to discovering an unmonitored diversion whose evidence has already expired?

5. §23.9 says a quarantine needs an owner. In your organization, who could actually be given that, and what would they need to accept it?

6. The replay was possible because the CDC source retained 18 months, with two months to spare. How would you decide a quarantine's retention given that it must be shorter than its sources'?

7. This case study and Chapter 22's Case Study 1 both involve a control that existed and was not watched. Is "add a metric" a sufficient answer, and what makes a metric one that people actually read?