Case Study 1: Ninety Models and One Missing ref()

"It was on the known-flakes list. Somebody re-ran it, it went green, and everyone got on with their morning."

Executive Summary

For eleven weeks, Kestrel's nightly dbt build produced a fct_order_item that was missing the most recent day — on eight nights out of seventy-seven, apparently at random.

The cause was one line in one model. int_order_items_deduped read analytics.stg_orders by name instead of through {{ ref('stg_orders') }}. The model compiled, ran, and returned correct results every time it was tested. It simply had no edge in the DAG, so dbt was free to run it before stg_orders had been rebuilt — and did, whenever the graph's shape or the thread scheduling made that ordering convenient.

Because it was intermittent, it was classified as flakiness. The morning routine became "if the revenue tile is blank, re-run the job." On one of the eight nights nobody re-ran it, and finance closed the month on a month-to-date total that was one day short: $498,630.14.

Skills applied: ref() and DAG construction (§19.3); node selection and dbt ls (§19.12); manifest auditing (§19.3, code/manifest_audit.py); the difference between a test passing and a model being correct.

Background

The project. 90 models, 312 tests, a nightly dbt build at 03:00 UTC feeding the 6am SLA from Chapter 1 §1.7.

The model. int_order_items_deduped applies Chapter 18 §18.7's deduplication to the CDC stream:

-- models/intermediate/int_order_items_deduped.sql
{{ config(materialized='table') }}

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id, line_number
                              ORDER BY updated_at DESC, cdc_lsn DESC) AS rn
      FROM {{ ref('stg_kestrel__order_items') }}
),
orders AS (
    SELECT order_id, status, ordered_at
      FROM analytics.stg_orders               -- ←  here
)
SELECT r.*, o.status, o.ordered_at
  FROM ranked r JOIN orders o USING (order_id)
 WHERE r.rn = 1

One of the two references is a ref(). The other is not. That detail matters more than it looks: the model does have an edge to stg_kestrel__order_items, so it appears connected in the lineage graph, appears in dbt ls --select stg_kestrel__order_items+, and looks entirely normal in the docs site. Only the stg_orders edge is missing.

How it got there. Git blame found the answer immediately, and it is depressingly ordinary. The model originally read only order items. Six months later someone needed status on the line, opened the file in a hurry, and wrote the reference the way they would write it in a SQL client — because in a SQL client, analytics.stg_orders is what you type. It was reviewed by two people. The diff shows a JOIN being added and looks exactly like a correct one.

The Problem

The symptom, on eight mornings out of seventy-seven:

06:00 executive dashboard
──────────────────────────────────────
Revenue, yesterday        (no data)
Revenue, month to date    $9,214,880.32
Orders, yesterday         (no data)

And on the other sixty-nine mornings, everything was correct.

The pattern had no obvious period. It was not weekends, not month-ends, not deploy days. It was recorded on the team's known-issues page as:

fct_order_item occasionally lands empty for the current day. Re-run dbt build --select fct_order_item+ and it resolves. Root cause unknown — suspect warehouse contention.

That note is the incident. Everything after it is consequence.

⚠️ Failure Mode — "intermittent" is a category, not a diagnosis

Classifying a failure as flaky is a decision to stop investigating, and it is almost always made implicitly, by someone who has a working remediation and a full morning.

The remediation is what makes it stick. A failure with no workaround gets escalated. A failure with a thirty-second workaround gets a wiki entry, and the wiki entry converts a bug into a chore.

The tell that this has happened to you: a runbook step whose trigger is a symptom rather than an alert, and whose text contains the phrase "root cause unknown" or "just re-run it."

What "intermittent" almost always actually means, in a data system, in rough order of frequency:

• An ordering that is unconstrained rather than deterministic — this case study.
• A tie with no tiebreaker — Chapter 18 §18.7.
• A boundary crossed by some runs and not others — Chapter 18 §18.9.
• A timeout that is close to the median duration, so load decides the outcome.

None of those are random, and all four are found by asking "what differed about the runs that failed?" — which is the question a flaky classification stops you asking.

The Analysis

The engineer who found it was not investigating the flake. They were adding a model and wanted to see what would rebuild:

$ dbt ls --select stg_orders+
analytics.stg_orders
analytics.fct_order_status_history
analytics.dim_order_channel

Three models. int_order_items_deduped was not among them, and it obviously reads stg_orders — they had the file open.

That is the whole diagnosis, and it took a minute. The rest was confirming the mechanism.

Why intermittently? dbt runs the DAG with threads: 12. Within the constraints the DAG imposes, the order is whatever the scheduler produces. int_order_items_deduped had no constraint relative to stg_orders, so both orderings were legal and dbt chose freely:

a night that worked                a night that did not
────────────────────               ────────────────────
stg_orders           (t=0:00)      int_order_items_deduped  (t=0:00)
stg_..._order_items  (t=0:00)         reads analytics.stg_orders
int_order_items_dedup(t=1:40)         ← YESTERDAY's table, still there
   reads stg_orders (fresh)         stg_orders               (t=0:12)
fct_order_item       (t=3:10)      fct_order_item            (t=1:55)

On the bad nights the model read the previous run's stg_orders, which still existed and was perfectly valid — just a day old. The join then dropped every order line whose order_id was not yet in that stale table, which is exactly the current day's.

What changed eleven weeks earlier? A model was added elsewhere in the graph. It did not touch either of these models; it changed the DAG's shape enough that the scheduler's choices changed. The bug was introduced six months before the symptom appeared, which is the property that makes this class of defect so expensive.

Why no test caught it. This is the part worth sitting with:

• unique on (order_id, line_number) — passed. Fewer rows are still unique.
• not_null on every column — passed. The rows that were present were fine.
• relationships to stg_customers — passed.
• The grain test — passed.

Every one of the twelve tests on this model passed on the bad nights, because they all describe properties of the rows that are there, and none describes how many rows there should be.

🔎 Read the Plan — the tests were all about the rows present, none about the rows missing

Look at what a standard dbt test suite actually asserts:

text unique → no row is duplicated not_null → no present row has a null here accepted_values → no present row has a bad value relationships → no present row is an orphan

Every one is a statement about rows that exist. A model that lost 100% of yesterday's data satisfies all four, perfectly, and so does a model that returns zero rows.

The class of assertion nobody writes is the one about volume, and it is one line:

yaml - dbt_utils.expression_is_true: expression: "count(*) >= 3000" config: {where: "ordered_at::date = current_date - 1"}

Set the floor well below the expected value. Kestrel's daily order-line count averages 17,753 (Chapter 6 §6.9); the assertion is 3,000. It is not trying to catch a low day — it is trying to catch a zero, and a tight bound would page someone every public holiday and be disabled within a month.

A test that catches catastrophes and ignores fluctuations survives. A test that catches both gets turned off.

The Decision

Three changes, and the third is the one that matters.

One: fix the model. analytics.stg_orders → {{ ref('stg_orders') }}. One line.

Two: add the volume assertion, to every fact model, at a floor low enough to survive a bad Tuesday.

Three: make the class of defect impossible to merge. manifest_audit.py --hardcoded runs in CI and fails the build. It works from the compiled manifest rather than the source, which matters:

# The check, in essence. For each model, every schema.table in the COMPILED
# SQL must correspond to something in depends_on -- because a ref() has
# already been resolved to schema.table by the time you read compiled SQL.
for schema, table in QUALIFIED.findall(compiled_sql):
    target = relation_index.get("%s.%s" % (schema, table))
    if target and target not in node["depends_on"]["nodes"]:
        fail(...)

A grep for ref( would not have found this model, because the model has a ref() — it has two references and only one of them is wrong. Checking the manifest catches the mixed case, which is the only case that occurs in practice, because a model with no ref() at all is obvious in review.

📐 Design Decision — a linter, or a review checklist?

The first proposal was a pull-request checklist item: "confirm all references use ref()."

It was rejected, and the argument is Chapter 17 §17.9's, applied to a code review: the two reviewers who approved the original change would have ticked that box. They were not careless. The diff showed a JOIN being added, and a JOIN being added is what a correct change also looks like. A checklist asks the reviewer to notice something whose absence is invisible.

A checklist item is a notice. The linter is a control.

The distinction generalizes and is worth keeping: when a defect is invisible in the artifact under review, no amount of reviewer diligence is the fix. Either make it visible — which is what the audit's output in the PR does — or make it impossible.

What the linter costs: 400 ms in CI, and one false positive in the first month, from a model that deliberately read a table maintained outside dbt. That was resolved by declaring the table as a source, which is what it should have been anyway. A linter's false positives are frequently a list of things that were wrong for a different reason.

What Happened

The audit found four more, in a 90-model project that two experienced engineers had reviewed every commit of:

The last two are worse than the one that caused the incident. dim_customer bypassed the staging layer entirely, so every cleaning rule in stg_kestrel__customers — the null-email handling, the deduplication, the type casts — was silently not applied to the customer dimension. fct_refund had been reading production data from every developer's laptop for four months.

Neither had produced a symptom. They were found because the fix for a different problem happened to enumerate them, which is the single best argument in this chapter for running audits you do not currently have a reason to run.

The month-end restatement. Finance had filed a month-to-date revenue figure short by one day — $498,630.14, against Kestrel's daily average of $182.0M ÷ 365. The correction was a routine adjustment in the following period, and the finance partner's comment is the one the team wrote down:

"I don't need the number to be right the first time. I need to know when it isn't."

The known-issues entry was deleted, and the team adopted a rule: an entry on that page must carry an owner and a date, and one that has been there ninety days is escalated rather than renewed. Six of the eleven entries did not survive the first review.

Lessons

1. A hardcoded table name compiles, runs, and returns correct results. It is not a syntax problem and no test of the rows will find it. What it removes is a DAG edge.

2. The failure is intermittent by construction. An unconstrained ordering is not random — it is legal in both directions, and the engine chooses. The bug can be introduced six months before the graph changes shape enough to expose it.

3. dbt ls --select <model>+ is the one-minute diagnosis. If something you know is downstream is not in the list, the edge is missing.

4. A model with a mix of ref() and hardcoded references is the case that occurs. A grep for ref( finds nothing wrong. Audit the compiled manifest.

5. Standard dbt tests all describe rows that exist. unique, not_null, accepted_values, and relationships all pass on an empty table. Add a volume assertion, with a floor low enough to survive a bad day — the aim is catching zero, not catching low.

6. A test that fires on ordinary fluctuation gets disabled. Tune for catastrophes.

7. "Intermittent" is a decision to stop investigating, and a cheap workaround is what makes it stick. Ask what differed about the runs that failed.

8. When a defect is invisible in the artifact under review, reviewer diligence is not the fix. A checklist item is a notice; a linter is a control.

9. Run the audit before you have a reason to. The two worst findings here — a dimension bypassing the staging layer, and a model reading production from developer laptops — had produced no symptom at all.

Questions for Discussion

1. Two experienced engineers reviewed the change that introduced this. What, concretely, would either of them have had to do differently — and is it reasonable to expect?

2. The volume assertion is set at 3,000 against an average of 17,753. Defend that gap. What would go wrong at 15,000? At 100?

3. The known-issues entry described the symptom and the workaround accurately and was not, in itself, negligent. What would a good version of that entry have contained?

4. dim_customer read bronze directly for months without a symptom. How would you detect a class of error that produces no symptom? What is the general strategy?

5. The linter's one false positive turned out to be a model that should have declared a source. Have you seen a lint rule whose false positives were themselves findings? What does that suggest about how to respond to them?

6. The finance partner said they need to know when a number is wrong, not for it to be right the first time. What would a data platform that took that seriously look like, and how does it differ from one optimized for correctness?

7. This defect existed for six months before producing a symptom. Argue for and against a rule that every audit in a project must run on a schedule rather than on demand.