Case Study 2: The Night That Lost More Than the Year

"It had been losing four thousandths of a percent a night for eight months. Then it lost two percent in one night, and that was the night we found it."

Executive Summary

Kestrel's fct_order_item used the textbook incremental watermark: WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}). No lookback window.

Rows whose updated_at was earlier than the highest value already loaded were skipped permanently — a transaction that began before one night's read and committed after it carries a timestamp below the watermark, and the next run's filter excludes it forever.

The loss rate was 0.037% of orders, which over 243 days is 591 orders and $44,815.53. Nobody could see it. It is below the noise floor of every reconciliation Kestrel ran.

The loss rate is not constant. It rises with write concurrency, because concurrency is what produces the commit-order inversions in the first place. On Black Friday — 41,300 orders, 6.28× a normal day (Chapter 1 §1.5) — it hit 2.1%: 867 orders, $65,744.61.

One night lost more than the preceding eight months, which is why it was finally found, and which is the property that makes this failure mode worth a case study rather than a paragraph.

Skills applied: watermarks and the four ways updated_at lies (Chapter 13 §13.4); the lookback window (§20.7); why a lookback requires a merge (§20.7); sizing a window from the maximum rather than the percentile (§20.7).

Background

The model, written when fct_order_item was made incremental:

{{ config(materialized='incremental', unique_key=['order_id','line_number']) }}

SELECT * FROM {{ ref('int_order_items_deduped') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

This is the version in every tutorial. It is what dbt's own documentation showed for years. It has a unique_key, so it merges rather than appends, which means it is idempotent — the model passes the run-twice test in §20.3 perfectly.

It is still wrong, and the wrongness is in the WHERE, which is a completeness property rather than an idempotency one. The two are independent, and passing the idempotency test provides no information about the other.

The source. PostgreSQL 16, read through a CDC stream (Chapter 14). updated_at is set by an application trigger at the moment the row is modified — not at the moment the transaction commits.

That gap is the entire bug:

t=10:00:00.000   txn A begins,  writes order 5001, updated_at = 10:00:00
t=10:00:00.100   txn B begins,  writes order 5002, updated_at = 10:00:00.1
t=10:00:00.150   txn B COMMITS                     ← visible
t=10:00:02.000   dbt reads. MAX(updated_at) is now 10:00:00.1
t=10:00:03.000   txn A COMMITS                     ← visible NOW
                 order 5001 carries updated_at = 10:00:00
                 which is BELOW the watermark. It will never be read.

Order 5001 is lost. Not delayed — lost. The next run's filter is > 10:00:00.1 and 5001's timestamp is 10:00:00, forever.

The Problem

For eight months there was no problem visible to anybody.

Kestrel ran three reconciliations, all of which passed:

0.037% against a 0.5% tolerance. The check was correctly designed, correctly implemented, and running. It could not have caught this and it was never going to.

⚠️ Failure Mode — a reconciliation tolerance is a statement about what you have decided not to see

A tolerance is not a safety margin. It is a threshold below which errors are permanent, because anything under it will never be investigated by anyone.

Kestrel's 0.5% monthly tolerance was chosen sensibly: timing differences between the warehouse's day boundary and the processor's, refunds in flight, currency rounding. Every one of those reasons is real, and together they justify roughly 0.3%.

What nobody did was ask what a 0.5% tolerance costs. At Kestrel's scale:

$$0.5\% \times \$182{,}000{,}000 = \$910{,}000\ \text{a year that could be wrong without anyone > looking}$$

That number should be on the same page as the tolerance. Not to argue for a tighter one — 0.1% would page someone every month for reasons that are genuinely benign — but because a tolerance is a budget, and budgets should be stated in currency.

Two things that make a tolerance safer without tightening it:

• Track the variance as a time series, not as a pass/fail. A variance that sits at 0.02% for a year and then moves to 0.037% and stays there is a signal, and it is invisible to a threshold check. Kestrel's variance did exactly that, in the week the model went incremental.
• Reconcile the tail separately. A count of unmatched records, at zero tolerance, alongside the aggregate at 0.5%. Sums hide compensating errors; counts do not.

The Analysis

Black Friday's reconciliation failed:

Reconciliation, 2026-11-27
──────────────────────────────────────────────────
  Warehouse gross            $ 3,066,034.39
  Payment processor          $ 3,131,779.00
  Variance                       $ 65,744.61      2.10%   ← tolerance 0.5%

The variance was 2.10%, four times the tolerance, and the number is exact: 867 orders at the $75.83 AOV.

Step 1: find the missing orders. Trivial, once anyone looked:

SELECT o.order_id, o.updated_at
  FROM silver.orders o
  LEFT JOIN gold.fct_order_item f USING (order_id)
 WHERE f.order_id IS NULL
   AND o.ordered_at::date = '2026-11-27';
-- 867 rows

Step 2: ask what they have in common. The answer took ten minutes and reframed the whole incident:

SELECT date_trunc('minute', updated_at) AS m, COUNT(*)
  FROM missing GROUP BY 1 ORDER BY 2 DESC LIMIT 5;

m                      n
2026-11-27 05:01:00   198
2026-11-27 05:00:00   171
2026-11-27 13:14:00    94
2026-11-27 13:13:00    88
2026-11-27 20:47:00    61

Clustered in minutes, not spread across the day. Those minutes are the three traffic spikes — the 5am doorbuster, the midday email send, and the evening peak. Concurrency produces commit-order inversions, and the loss rate is a function of concurrency.

Step 3: go back and look for it in the ordinary days, which nobody had done because nothing had prompted it:

-- The same anti-join, run over the preceding 243 days.
SELECT ordered_at::date AS d, COUNT(*) AS missing
  FROM ... GROUP BY 1 ORDER BY 1;

591 orders across 243 days.  Mean 2.4/day.  Max 9.  Min 0.

$$591\ \text{orders} \times \$75.83 = \$44{,}815.53\ \text{over eight months}$$

$$867\ \text{orders} \times \$75.83 = \$65{,}744.61\ \text{in one night}$$

🔎 Read the Plan — the anti-join nobody runs

The query that found this is four lines and could have been run on any day of the eight months:

sql SELECT COUNT(*) FROM silver.orders o LEFT JOIN gold.fct_order_item f USING (order_id) WHERE f.order_id IS NULL AND o.ordered_at >= current_date - 7;

Nobody runs it, because it does not correspond to a question anyone asks. The questions people ask are "is revenue right?" (an aggregate, which hides it) and "are there duplicates?" (a uniqueness test, which passes). "Is anything missing?" is a third question with its own query, and it is the one this book keeps arriving at from different directions — Chapter 19 Case Study 1's volume floor, Chapter 18 Case Study 2's second implementation, and now this.

Make it a test, at zero tolerance:

yaml - dbt_utils.expression_is_true: expression: "1=1" # the test is the WHERE, below config: where: "false" # replaced by a singular test in practice

In practice write it as a singular test returning the unmatched rows, which is what --store-failures then makes queryable. Zero tolerance is correct here because unlike a revenue sum, an unmatched order has no benign explanation. Every one is a bug.

The Decision

The fix is two changes that must be made together, and §20.7's callout exists because teams make one of them.

One: a lookback window.

{% if is_incremental() %}
  WHERE updated_at >= (SELECT MAX(updated_at) - INTERVAL '3 days' FROM {{ this }})
{% endif %}

Two: confirm the merge. The model already had unique_key, so re-reading three days updates those rows in place rather than duplicating them. Had it not, this fix would have converted a 0.037% data-loss bug into a 300% duplication bug, nightly, and Chapter 1's incident would have happened a second time — from a change made to improve correctness.

Sizing the window. The team measured rather than guessed, over 90 days of CDC arrival lag:

p50      0.8 s
p99     41 min
p99.9    4 h 12 min
p99.99  19 h
max      2 d 4 h        ← a batch load during a supplier migration

They set 3 days. The reasoning is worth quoting because it inverts the usual instinct:

The percentiles are the wrong statistic. The rows we lose are, by definition, the ones in the tail. A window at p99.9 recovers 99.9% of late rows and loses the rest permanently, which is the same failure at a smaller scale. Size it above the observed maximum, then add margin for the event you have not seen yet.

📐 Design Decision — why not a much larger window?

If bigger is safer, why not thirty days?

Because the lookback is what the merge scans, and the merge is the model's dominant cost (§20.12). A 3-day window means a USING set of about 53,000 rows; 30 days means 533,000, and — with incremental_predicates set to match — a proportionally larger target scan. Measured, the 30-day version cost $0.31 a night against $0.06, which is $91 a year for recovering nothing beyond what 3 days recovers.

And a large lookback hides the thing it is compensating for. A model with a 30-day window will silently absorb a source system whose lag has degraded from hours to weeks, and you will find out when it exceeds thirty days. A window sized just above the observed maximum fails visibly when the upstream changes, which is information you want.

So: measure the maximum, roughly double it, and alert when a recovered row's lag exceeds half the window. That last clause turns the window into a sensor.

Three further changes:

The anti-join became a test, at zero tolerance, running on every build.

The reconciliation variance became a tracked time series with an alert on a sustained shift, not only on the threshold. The 0.02% → 0.037% step change in the week the model went incremental is plainly visible in retrospect and would have been caught within a fortnight.

The fct_order_item incremental configuration got a comment recording all three of §20.2's answers, including the measured basis for the window — because the next engineer will see INTERVAL '3 days' and have no way to know whether it was chosen or defaulted.

The backfill. All 1,458 missing orders were recoverable: the source retains 18 months, and a bounded rebuild of the affected ranges (§20.4) restored them. They were lucky. Had the CDC stream been the only record, at seven-day retention, the eight months would have been unrecoverable — and that is the case §20.1 describes as making a model incremental by necessity, where this exact bug is permanent.

What Happened

Two further findings from the same audit.

fct_session had the same watermark and no lookback, and its loss rate was higher — 0.14% — because clickstream events are written by more concurrent producers than orders are. It had never been reconciled against anything, so the loss had no upper bound established. A model with no reconciliation does not have a small error; it has an unmeasured one.

One model had a lookback and an append strategy. It was bronze.api_shipments, added three weeks earlier by someone who had read about lookbacks and applied the advice correctly and incompletely. It had been duplicating three days of shipments every night for nineteen days, and the reason nobody had noticed is that shipments are counted with COUNT(DISTINCT shipment_id) in the one report that uses them.

That is the most uncomfortable finding in this case study. A defect that had been silently harmless because a downstream query happened to be written defensively — and would have become visible, and expensive, the first time anyone wrote COUNT(*).

Lessons

1. A watermark of > MAX(updated_at) assumes commit order matches timestamp order. It does not, in any system with concurrent writers. Rows below the watermark are lost permanently, not delayed.

2. Idempotency and completeness are independent. This model passed the run-twice test perfectly and lost data every night. Testing one tells you nothing about the other.

3. The loss rate scales with write concurrency, so it is smallest on the days you look and largest on the days that matter. One Black Friday lost more than the preceding eight months.

4. A reconciliation tolerance is a budget for permanent error. State it in currency: 0.5% of $182.0M is $910,000 a year that will never be investigated.

5. Track the variance as a time series, not a pass/fail. A step change from 0.02% to 0.037% is a signal; a threshold check cannot see it.

6. Reconcile the tail separately, at zero tolerance. Sums hide compensating errors; an anti-join count does not, and an unmatched order has no benign explanation.

7. Size a lookback from the observed maximum, not a percentile. The rows you lose are in the tail by definition; a p99.9 window is the same failure at a smaller scale.

8. Do not make it enormous. The lookback is what the merge scans, and a large window hides a degrading upstream. Alert when a recovered row's lag exceeds half the window — that makes the window a sensor.

9. A lookback without a merge is a nightly duplication engine. One model had exactly that, applied correctly and incompletely from good advice.

10. A defect can be harmless only because a downstream query was written defensively. COUNT(DISTINCT ...) was hiding nineteen days of duplicates.

11. A model with no reconciliation does not have a small error. It has an unmeasured one.

12. This was recoverable because the source kept 18 months. In a model that is incremental by necessity — a seven-day CDC retention — the same bug is permanent, and the bar for its assertions is correspondingly higher.

Questions for Discussion

1. The watermark used here is what dbt's documentation showed for years and appears in most tutorials. What obligation does that place on documentation, and on this book?

2. The 0.5% tolerance was chosen for real reasons and could not have caught a 0.037% error. Is the right response a tighter tolerance, a different check, or something else?

3. The variance stepped from 0.02% to 0.037% in the week the model changed. What would it take, in your organization, for that step to be noticed?

4. The team sized the window above the observed maximum and doubled it. Argue for percentile-based sizing — under what conditions is p99.9 the right choice?

5. The bronze.api_shipments duplication was invisible because one report used COUNT(DISTINCT ...). How would you find defects that are currently masked by a downstream accident?

6. fct_session lost 0.14% and had never been reconciled. How do you decide which models get a reconciliation, given that the cost is real?

7. Both of this chapter's case studies were found by a check failing for an unrelated reason — a segment report, a Black Friday reconciliation. Is there a way to design for discovery, or is this simply what luck looks like at scale?