Case Study 2: Six Days of Green

"Every model built. Every test passed. The DAG was green for six days, and the customer dimension had not changed since Tuesday."

Executive Summary

Kestrel's customer dimension load — a nightly snapshot, separate from the CDC stream that feeds the facts — failed silently on a Tuesday night. It kept failing for six days.

dbt never noticed, and could not have. dbt transforms what is in the warehouse; it has no way to know what should have been. All 90 models built. All 312 tests passed, including the relationships test whose entire purpose is to catch orphaned foreign keys.

During those six days, 1,082 orders from customers who did not yet exist in the dimension were joined to the "Unknown" member — the standard, correct, Chapter 6 §6.6 handling for an unmatched dimension key — and written into fct_order_item at $82,048.06. That is 2.7% of the period's GMV, which is precisely the size that hides: too small to disturb any total, large enough to hollow out every analysis cut by customer.

The load was fixed on the seventh day. The data was not. fct_order_item is incremental, those rows had already been written, and resuming the dimension load does not revisit them. Everyone involved believed the incident was over.

Skills applied: source freshness (§19.8); why every dbt test passes on stalled data (§19.8); the unknown-member pattern (Chapter 6 §6.6); the difference between fixing a pipeline and fixing the data (Chapter 20 §20.4).

Background

Two loaders, one warehouse. Kestrel's facts arrive by CDC (Chapter 14); the customer dimension arrives by a nightly full snapshot, because the source system's customer table has no reliable updated_at and Chapter 13 §13.4's fourth way updated_at lies applies to it exactly.

02:00  cdc-connector          → bronze.orders, bronze.order_items    ✓ running
02:15  snapshot-customers     → bronze.customers                     ✗ FAILED
03:00  dbt build              → 90 models, 312 tests                 ✓ green
06:00  executive dashboard                                           ✓ correct

The failure. The snapshot job's service account credential expired. The job exited non-zero and its alert fired into a Slack channel that had been archived during a workspace cleanup eleven weeks earlier. Nobody received it.

Why this is worth a case study rather than a paragraph is what happened next: the failure of the alert was the smallest part of the problem. Even with no alerting at all, a data platform should not be able to run six days on a frozen dimension without anything noticing.

The Problem

The dashboard was correct. Revenue was correct. Order counts were correct. The 6am SLA was met on all six days.

The discovery came from the growth team on the following Monday:

New customers acquired, by week
──────────────────────────────────────
2026-04-06    1,204
2026-04-13    1,187
2026-04-20    1,232
2026-04-27        0        ←

Zero is not a plausible number, and it was escalated within the hour. The dimension load was identified, the credential rotated, and the job resumed at 14:20. Total incident duration, from detection to resolution: three hours.

That is the story everybody told. It is also wrong in a way that took another five weeks to surface.

The Analysis

Part one: why every test passed.

The relationships test is the one that should have caught this:

- name: customer_id
  tests:
    - relationships:
        to: ref('dim_customer')
        field: customer_id

It asserts that every customer_id in the fact has a match in the dimension. During the incident, 1,082 orders had customer IDs that were not in the frozen dimension. The test should have failed 1,082 times.

It passed, and here is the model:

-- models/marts/finance/fct_order_item.sql
SELECT
    oi.order_id,
    oi.line_number,
    -- Chapter 6 §6.6: an unmatched key resolves to the Unknown member
    -- rather than a null, so downstream aggregations do not silently
    -- drop rows.
    COALESCE(c.customer_key, -1) AS customer_key,
    oi.net_revenue_cents
  FROM {{ ref('int_order_items_deduped') }} oi
  LEFT JOIN {{ ref('dim_customer') }}       c ON c.customer_id = oi.customer_id
                                             AND c.is_current

The COALESCE is correct. It is the recommended pattern, it is in this book, and it exists for a good reason: a LEFT JOIN producing nulls means a downstream GROUP BY region silently drops those rows, which is worse.

And it is what defeated the test. The relationships test is written against customer_key, which is never null and always resolves — to -1, the Unknown member, which is a row in dim_customer.

⚠️ Failure Mode — the unknown member converts a loud failure into a quiet one

This is the sharpest thing in the chapter and it is a genuine design tension, not a mistake to avoid.

Without the unknown member: an unmatched key produces a null. Aggregations drop the rows. Revenue by region does not sum to total revenue. It is wrong, and it is obvious.

With the unknown member: the rows survive, totals reconcile, nothing is dropped. It is wrong, and it is invisible — the badness has been moved into a bucket labelled "Unknown" that appears on no dashboard because nobody charts the Unknown row.

You want the unknown member. The alternative is worse. But adopting it transfers a responsibility: the referential-integrity failure that used to announce itself now has to be monitored deliberately.

The assertion the pattern requires, and that almost nobody writes:

yaml - dbt_utils.expression_is_true: expression: "count(*) < 50" config: where: "customer_key = -1 and ordered_at::date = current_date - 1"

Unknown-member volume is a health metric. A few a day is normal — genuinely late-arriving dimension rows. Two hundred a day means a load is broken, and no other test in the project will say so.

The general principle: every graceful-degradation mechanism moves a failure from loud to quiet, and therefore creates a monitoring obligation. Retries, defaults, COALESCE, fallbacks, circuit breakers — all of them. If you add one and do not add the corresponding metric, you have not made the system more robust; you have made it quieter.

Part two: source freshness was configured and never ran.

_sources.yml had it, correctly:

- name: customers
  loaded_at_field: _snapshot_at
  freshness:
    warn_after:  {count: 26, period: hour}
    error_after: {count: 30, period: hour}

dbt source freshness would have errored on day one. It was never invoked — not in the nightly job, not in CI, not anywhere. The YAML had been written during the project's setup, verified once by hand, and never wired to anything.

This is not carelessness; it is a structural property of the feature. dbt build does not run freshness checks. They are a separate command, they produce no output when things are fine, and there is nothing in a normal working day that reminds you they exist.

🏭 From the Pipeline — configuration that is not invoked is documentation

Audit your own project for this class right now. It is more common than any bug in this chapter.

Things that are commonly configured and commonly not run:

• dbt source freshness — a separate command, not part of dbt build.
• Tests with severity: warn — they run, they report, and warnings scroll past in a log nobody reads. A warning that nothing consumes is a comment.
• Alert routing to a channel that no longer exists, which is what happened here. Slack channels get archived; alert destinations are configuration written years earlier.
• A --store-failures table nobody queries.

The test for all four is the same, and it is one question: what would I see if this fired?

Then go and make it fire. Break something deliberately in a staging environment and follow the signal to a human. A monitoring path that has never been exercised end to end is a hypothesis.

Kestrel now runs a quarterly "alert fire drill": one deliberate failure per critical path, verified to reach a person. The first drill found three of eleven alert routes dead.

Part three — the part that took five more weeks.

The load was fixed on day seven. Everyone moved on.

fct_order_item is materialized incremental. Its filter is:

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

The 1,082 rows written during the incident were never reprocessed. Their updated_at values were now below the watermark, so the incremental run skipped them permanently. They sat in the fact table with customer_key = -1 — a correct value for a row loaded on day three, and a wrong one from day seven onward, because by then the customer existed.

Resuming the dimension load repaired the dimension. It did nothing at all to the facts.

This surfaced five weeks later, when a cohort analysis showed an unexplained hole in late April signups. The arithmetic reconciled immediately once someone counted the Unknown-member rows:

Kestrel acquires about 1,204 new customers a week — 172 a day — and a customer becomes new by placing a first order, so the orders that cannot match a frozen dimension are the first orders of those six days plus the handful of in-window repeats:

$$(172 \times 6) + 50 = 1{,}082\ \text{orders} \quad\Rightarrow\quad 1{,}082 \times \$75.83 = \$82{,}048.06$$

$$\frac{\$82{,}048.06}{39{,}450 \times \$75.83} = 2.7\%\ \text{of the period's GMV, attributed to no customer}$$

2.7% is the dangerous magnitude. It moves no total anyone watches, and it removes a seventh of the period from every cohort, retention, and acquisition analysis that groups by customer.

📐 Design Decision — "the pipeline is fixed" is not "the data is fixed"

These are different statements about different objects, and conflating them is one of the most reliable ways to leave damage in a warehouse.

A pipeline incident has two remediations and they are usually done by different people at different times:

	Fix the pipeline	Fix the data
What	restart, rotate, redeploy	recompute the affected range
When	during the incident	after
Who notices if skipped	everyone, immediately	nobody, for weeks
In the postmortem	always	frequently missing

The second one is skipped because the first one makes the symptom go away, and the symptom was what everyone was watching.

The control is a template field, not a reminder. Kestrel's incident template now has a required section:

Data impact. Which tables, which date range, how many rows, and the exact command that repairs them. If the answer is "none," say why.

A required field with a mandatory answer beats an intention. And "if the answer is none, say why" is the part that does the work — it converts an omission into a claim someone has to make.

The Decision

Four changes.

One: freshness runs, on its own schedule. dbt source freshness as a separate 02:45 job, before the build — so a stale source stops the build rather than being discovered inside it. Its failure pages.

Two: an unknown-member assertion on every fact with a dimension key. Threshold 50/day, which is comfortably above the genuine late-arrival rate of three to eight and far below a broken load.

Three: quarterly alert fire drills. Described above; found three dead routes on the first pass.

Four: the incident template's data-impact section. Required, with a command, or an explicit "none, because."

And a fifth that was rejected, worth recording: someone proposed removing the COALESCE so that unmatched keys produce nulls and the relationships test fires. It was rejected — that trades a quiet, monitorable failure for a loud, destructive one, in which aggregations silently drop rows and no total reconciles. The unknown member is right. It just is not free.

The backfill. 1,082 rows, repaired by a full refresh of the affected date range:

dbt build --select fct_order_item+ \
  --full-refresh --vars '{start_date: "2026-04-21", end_date: "2026-04-28"}'

Chapter 20 §20.4 is about why that command is more dangerous than it looks and what a safer version does instead.

What Happened

The one new test is the interesting column. 312 tests did not catch a six-day outage; 313 do. That is not an argument for more tests — it is an argument for one test of the right kind, and the right kind here was the one asserting something about rows that were missing rather than rows that were present.

Two follow-on findings from the same audit:

Three other facts had unknown-member joins with no volume assertion. One — fct_session joining to dim_device — had been running at 4.1% unknown for over a year, because a user-agent parser upgrade in the previous summer had changed a device string and nobody reconciled the dimension. No test failed for fourteen months.

Two sources had freshness: blocks with thresholds that could never fire — error_after: {count: 7, period: day} on a source loaded hourly. Someone had copied a block and not adjusted it. A threshold that cannot fire is the same failure as a check that does not run, and it is harder to see.

Lessons

1. dbt transforms what is in the warehouse and cannot know what should have been. Every model builds and every test passes on a stalled load. This is structural, not a gap to be fixed by better tests of the rows.

2. The unknown-member pattern converts a loud referential-integrity failure into a quiet data quality one. You still want it — nulls would silently drop rows from every aggregation — but adopting it creates a monitoring obligation.

3. Every graceful-degradation mechanism moves a failure from loud to quiet. Retries, defaults, COALESCE, fallbacks. Add the mechanism, add the metric — otherwise you made the system quieter, not more robust.

4. Unknown-member volume is a health metric. A threshold well above normal late arrivals and far below a broken load.

5. dbt source freshness is a separate command that dbt build does not run. Configuration that is never invoked is documentation.

6. Run it before the build, as its own job. A check inside a build that does not run has also not run.

7. A threshold that cannot fire is worse than a missing check, because it appears on the audit as present.

8. A monitoring path that has never been exercised end to end is a hypothesis. Fire drills found three dead alert routes out of eleven.

9. "The pipeline is fixed" is not "the data is fixed." Make data impact a required field in the incident template, with a repair command or an explicit "none, because."

10. Incremental models make the gap permanent. Rows written during an incident fall below the watermark and are never revisited. Chapter 20.

11. 312 tests missed a six-day outage; 313 catch it. The number of tests is not the variable. The variable is whether any of them asserts something about what is absent.

Questions for Discussion

1. The COALESCE to the unknown member is recommended practice, is in this book, and defeated the test that would have caught this. How should a book present a pattern whose correct use creates a new obligation?

2. Removing the COALESCE was proposed and rejected. Construct the strongest possible case for removing it. What kind of organization should?

3. fct_session ran at 4.1% unknown for fourteen months without a failure. What would have detected it, and would you have shipped that detection before reading this?

4. The alert went to an archived Slack channel. Beyond fire drills, what structural change makes alert destinations self-verifying?

5. The incident was reported as three hours. By what standard is that true, and by what standard is it six days plus five weeks? Which does your organization report?

6. The team added exactly one test. Argue against the framing that "the number of tests is not the variable" — when is test count a meaningful signal?

7. Design the "if the answer is none, say why" field so that it resists being answered "none" out of habit. What makes a required field actually work?