Case Study 2: Four Hundred Deleted Orders Nobody Deleted

"The warehouse said we had 412 more orders than the source. Finance wanted to know which 412 were real. Nobody could tell them."

Executive Summary

Kestrel's checkout allows a customer to edit an order before payment. Editing a line hard-deletes the old order_items row and inserts a new one. This is a reasonable application design and it had been in place since 2019.

The data platform's incremental extract used updated_at and had no delete handling. Over eighteen months, 412 order lines that had been deleted in the source remained in the warehouse, inflating fct_order_item and every measure derived from it.

The discrepancy was under 0.01% and it was found because reconciliation is exact rather than tolerant — the same property that made Chapter 7's three-cent variance a finding.

This case study is about the deletes problem in its most ordinary form, about why the obvious fix made things worse, and about a subtle interaction between the delete-detection mechanism and the overlap window that took three attempts to get right.

Skills applied: hard deletes (§13.5); reconciliation (§13.5, Chapter 1 §1.7); the overlap-window interaction (§13.5's 🔁 callout); soft deletes as a source-system change (§13.5).

Background

The application behavior. A customer editing a cart line before payment:

BEGIN;
DELETE FROM order_items WHERE order_item_id = 8841022;
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents, ...)
VALUES (...);
COMMIT;

The engineering reason is sound: an edited line is a different line, and reusing the row would complicate the pricing history the checkout service maintains. It is also invisible to any timestamp-based extract, because the deleted row simply stops existing.

The volume. Roughly 0.4% of order lines are edited before payment — about 26,000 a year. But the large majority of those edits happen within minutes, before the hourly extract has seen either version, so the extract only ever observes the final state. The problem is confined to the small minority where the extract's timing straddled the edit.

412 rows in eighteen months. A tiny number, and it is not the number that matters.

The Problem

The warehouse contained rows for order lines that no longer existed in the source. Three consequences, in ascending order of seriousness:

1. Revenue was overstated by the value of the deleted lines — about $31,400 over eighteen months against $273M of GMV. Immaterial.

2. Reconciliation could never pass. Chapter 1 §1.7's acceptance criterion is exact, and it had been failing by a small, varying amount for eighteen months. The team had rationalized it as timing noise, which is Chapter 4's Case Study 2's exact failure — a persistent one-signed variance explained away.

3. Nobody could say which rows were wrong. When finance asked, the honest answer was "412 of 6.5 million, and we cannot tell you which." That is the consequence that mattered, because it makes every downstream number carry an unquantifiable asterisk.

⚠️ Failure Mode — The variance that was always one-signed

The reconciliation had been off by between $400 and $2,900 a month for eighteen months, always in the same direction: the warehouse always had more than the source.

The team's standing explanation was timing — the source snapshot and the warehouse load happen at slightly different moments, so small variances are expected. That explanation is correct and it predicts variance in both directions.

Chapter 4's Case Study 2 established the rule and this incident is it recurring:

Timing noise varies in sign. Loss or duplication does not.

The check that would have caught it in month one is not a tighter threshold. It is a sign persistence check:

sql SELECT COUNT(*) AS consecutive_same_sign FROM (SELECT SIGN(variance_cents) AS s, ROW_NUMBER() OVER (ORDER BY check_date DESC) AS rn FROM recon_history WHERE table_name = 'fct_order_item') WHERE rn <= 12 AND s = (SELECT SIGN(variance_cents) FROM recon_history WHERE table_name = 'fct_order_item' ORDER BY check_date DESC LIMIT 1); -- 12 of 12 the same sign: alert, regardless of magnitude.

Kestrel had added exactly this check after Chapter 4's incident — on orders, and not on fct_order_item. The control existed and had not been applied everywhere. That is its own lesson: when you install a check for a class of problem, apply it to the class, not to the instance.

The Analysis

Diagnosis was quick once the sign-persistence check was extended and fired. Two days:

Day 1. Identify the extra rows:

SELECT g.order_item_id
  FROM gold.fct_order_item g
 WHERE NOT EXISTS (SELECT 1 FROM staging.order_items s
                    WHERE s.order_item_id = g.order_item_id);
-- 412 rows

Day 2. Establish that they had been deleted rather than never having existed. The bronze layer answered this in one query, because bronze retains everything:

SELECT order_item_id, MIN(_ingested_at) AS first_seen,
                      MAX(_ingested_at) AS last_seen
  FROM bronze.order_items
 WHERE order_item_id IN (...)
 GROUP BY 1;

Every one had been seen, once, and then never again. Bronze proved the deletion had happened, which is the answer to Chapter 9 §9.4's "why keep raw data" question in its most concrete form.

The Decision — Three Attempts

Attempt 1: nightly key reconciliation

The textbook answer (§13.5, approach 2): extract the full key set nightly, tombstone what is absent.

INSERT INTO bronze.order_items_deleted (order_item_id, detected_at)
SELECT b.order_item_id, now()
  FROM bronze.order_items b
 WHERE NOT EXISTS (SELECT 1 FROM staging.order_item_keys s
                    WHERE s.order_item_id = b.order_item_id);

It worked, and it broke something else.

The key extract ran at 02:00. The incremental extract runs hourly with a five-minute overlap window (§13.4, property 2). On the 02:00 run, the overlap re-read rows from 01:55 onward — and tombstoned rows that had been deleted between 01:55 and 02:00 were re-inserted by the overlap.

This is §13.5's 🔁 callout, encountered in production rather than in a book. Two independently correct mechanisms, jointly wrong.

The symptom was worse than the original problem: a row would be tombstoned, resurrected, tombstoned again the next night, and resurrected again — flickering in and out of the warehouse nightly, which is far more confusing than being consistently wrong.

Attempt 2: tombstones win

Make the upsert refuse to insert a key that has a tombstone.

It fixed the flicker and introduced a new problem. Kestrel's checkout has a rare path where a deleted line is restored — a customer undoes an edit within the session. About twenty times a year, a legitimately restored line was permanently blocked from the warehouse by its own tombstone.

Twenty rows a year is smaller than 412 in eighteen months, and it is a different kind of wrong: the first was an artifact of a missing mechanism, the second was an artifact of a mechanism actively refusing correct data. The team judged the second worse, on the grounds that a system that rejects valid data is harder to reason about than one that lacks a feature.

Attempt 3: version the rows

Every row carries a monotonically increasing version from the source — here, the log sequence number that CDC would later provide, and in the interim a (updated_at, order_item_id) pair. A tombstone is a version. The upsert takes the highest version, whatever it is.

MERGE INTO bronze.order_items t
USING staged s ON t.order_item_id = s.order_item_id
WHEN MATCHED AND s.row_version > t.row_version THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

-- a delete is a row with is_deleted = true and a version, competing on equal
-- terms with any other version of the same key

Correct in every case, including the restore: a restored line arrives with a version higher than its tombstone and wins.

📐 Design Decision — Why not just ask for soft deletes?

§13.5 says soft deletes are the right answer and a source-system change worth asking for. Kestrel asked, in week one, and the answer was no — for a defensible reason.

The checkout service's pricing logic depends on order_items containing only live lines. Adding a deleted_at column means every query in the checkout path gains a WHERE deleted_at IS NULL, and the team's assessment was that they would eventually miss one and charge a customer for a removed item. That is a worse failure than anything in this case study, and they were right to weigh it that way.

The counter-proposal — a partial index and a view — was declined on the grounds that the service is on a deprecation path toward an event-sourced order model (Chapter 36) and they did not want to invest in the current one.

What this illustrates: "ask the source team for soft deletes" is correct advice and it has a refusal rate. The refusals are frequently well-reasoned, and the data team's job is then to solve it on their own side rather than to escalate.

Kestrel's eventual answer was CDC (Chapter 14), which captures the delete from the replication log and requires nothing from the application. That is the strongest argument for CDC in this book: it is the only delete-handling strategy that does not require the source team to agree to anything.

What Happened

Attempt 3 shipped in month two and has been correct since.

Four months later order_items moved to CDC (Chapter 14), which made the key reconciliation redundant — the delete arrives as an event, in order, with no detection delay. The reconciliation was kept anyway, weekly rather than nightly, as an independent check on the CDC pipeline. A second, different mechanism agreeing with the first is worth more than either alone.

The sign-persistence check was applied to every reconciliation, not just the two that had had incidents. It found one more problem within a month: a small persistent negative variance on fct_shipment, traced to shipments created and cancelled within the extract window.

The 412 rows were corrected by a bounded rebuild of the affected partitions.

Three observations from the review:

The variance had been visible for eighteen months and explained away. The team's own note: "We had the data. We had the check. We had, in another table, the exact control that would have caught it. What we lacked was the discipline to apply a lesson beyond the incident that produced it."

Bronze proved the deletion. Without a raw layer, "were these deleted or did they never exist?" would have been unanswerable, and the fix would have been designed against a guess.

The first fix made things worse. Attempt 1 was the textbook answer and it interacted badly with a mechanism installed for a different reason. That interaction is documented in this book because it cost Kestrel three weeks, and it is not obvious from either mechanism's description.

Lessons

  1. Hard deletes are invisible to every timestamp strategy. A deleted row leaves nothing to carry a timestamp.

  2. Timing noise varies in sign; loss and duplication do not. Eighteen months of one-signed variance was the finding, and no threshold would have caught it.

  3. When you install a check for a class of problem, apply it to the class. The sign-persistence check existed on orders and not on fct_order_item.

  4. Key reconciliation and an overlap window interact badly, producing rows that flicker in and out nightly — worse than being consistently wrong.

  5. A mechanism that refuses correct data is worse than one that lacks a feature. Tombstones-win fixed the flicker and permanently blocked legitimate restores.

  6. Versioning the rows is the general answer. A tombstone is a version, and the highest version wins — correct including restores.

  7. "Ask for soft deletes" has a refusal rate, and the refusals are often well-reasoned. The data team's job is then to solve it on their own side.

  8. CDC is the only delete strategy that requires nothing from the source team. That is the strongest argument for it in this book.

  9. Bronze answered a question no other layer could: had these rows been deleted, or had they never existed?

Questions for Discussion

  1. The variance was rationalized as timing noise for eighteen months. What would make a team re-examine a standing explanation? Is there a process, or does it require someone new?

  2. The sign-persistence check existed on one table and not on the others. Design the mechanism that applies a new check to every table it should cover, and estimate what it costs to maintain.

  3. Attempt 1 was the textbook answer and made things worse. Would you have predicted the interaction? What kind of test would have caught it before production?

  4. The checkout team declined soft deletes for a good reason. Write the two-paragraph response the data team should have sent — accepting the refusal and stating what they will do instead.

  5. The reconciliation was kept after CDC made it redundant, as an independent check. When is a redundant check worth its maintenance, and when is it clutter?

  6. 412 rows over eighteen months is 0.006% of order lines and $31,400 of $273M. Defend spending three weeks on it to a manager who sees only those numbers.

  7. This case study and Chapter 4's Case Study 2 have opposite signs — extra rows and missing rows — and the same detection failure. Write the single reconciliation design that catches both.