14. Failure modes

Most dbt failures are not errors. The run succeeds, every test passes, and the numbers are wrong — because dbt did exactly what you told it to. This chapter is the catalogue.

1. The incremental model that quietly stops seeing rows

What it looks like. Revenue is slightly low. Not obviously wrong — three percent, which nobody notices for a month. Everything is green.

Why. A strict high-water-mark filter (chapter 5):

where updated_at > (select max(updated_at) from {{ this }})

Any row whose updated_at predates the last run’s maximum is never seen again. The source system that batches its writes, the timezone-shifted feed, the backfill — all invisible.

The fix. A lookback window, sized generously, with an idempotent strategy:

where updated_at > (select dateadd(day, -3, max(updated_at)) from {{ this }})

Detect it. Reconcile periodically: row counts and a summed measure, incremental model against a full rebuild of the same logic in a scratch schema. Schedule it monthly. Deviation is the alarm.

2. Duplicates from a unique_key that is not unique

What it looks like. Totals are inflated. A customer appears twice. Sometimes intermittent, because merge behaviour with a non-unique key is adapter-dependent.

Why. unique_key = 'order_id', but order_id repeats — a source that reuses ids across regions, or a compound key written as a concatenated string rather than a list.

The fix. A uniqueness test on every incremental model’s key. Non-negotiable.

      - name: order_id
        tests: [unique, not_null]

For compound keys, a list, or a hash column via dbt_utils.generate_surrogate_key.

3. The column that never arrives

What it looks like. You add a column, the run succeeds, and the column is not in the table. No error anywhere.

Why. on_schema_change defaulting to ignore on an incremental model. dbt inserted into the existing columns and dropped the rest.

The fix. Set it explicitly on every incremental model:

{{ config(on_schema_change='append_new_columns') }}

And remember historical rows get NULL — a --full-refresh is what backfills them.

4. The hard-coded relation name

What it looks like. Several failures at once, all from one cause. Models build in the wrong order. A dev run reads production. The lineage graph is missing an edge. CI passes and production breaks.

Why. Someone wrote from analytics.analytics.stg_orders instead of {{ ref('stg_orders') }}. It worked when they tested it.

The fix. dbt-project-evaluator finds every instance. Add it to CI, fix the existing ones, and make it a hard failure so no new ones land.

This is the single most expensive mistake in dbt because it disables four separate mechanisms at once — ordering, environments, lineage and state selection — while looking correct.

5. Building into production from CI

What it looks like. Production tables overwritten by a pull request. Discovered by dashboards breaking.

Why. An overridden generate_schema_name (chapter 11) with a condition too loose:

{%- if target.name != 'dev' -%}
    {{ custom_schema_name }}     -- ← CI takes this branch

The CI target is not dev, so it takes the production path and writes to production schemas.

The fix. Gate on the exact production target name, never on the negation:

{%- if target.name == 'prod' and custom_schema_name is not none -%}

And enforce it at the warehouse: the CI role should not have write permission on production schemas. Configuration errors happen; permissions are the backstop.

6. The tag that vanished

What it looks like. The nightly job stops building a model. Nothing errors — it simply is not selected any more.

Why. Chapter 11’s merge rules. A model-level config(tags=['finance']) replaces the folder-level +tags: ['nightly'] rather than merging.

The fix. Restate inherited tags when setting them in a model. And prefer selecting by path: or by folder-level config where you can, since those cannot be clobbered from below.

7. The dashboard that times out

What it looks like. A dashboard that was fine for months starts timing out. No model changed recently.

Why. A view on a view on a view. Each layer was reasonable; the warehouse now executes the whole chain on every load, and the underlying table crossed some size threshold.

The fix. Materialise something in the middle. Marts should be tables (chapter 4). dbt-project-evaluator flags chained view dependencies before they become an incident.

8. Test theatre

What it looks like. Four hundred tests. Twelve warn on every run. Nobody reads the output. A real failure appears in the same wall of yellow and is missed for a week.

Why. Tests added for coverage rather than for meaning, and severity: warn used to silence noisy ones rather than to express genuine tolerance.

The fix. A test that has warned every day for a month is not a test. Either fix the data, encode the real tolerance as a threshold (error_if: ">1000"), or delete it. Aim for an output where any yellow means something happened.

9. The snapshot with a hole in it

What it looks like. A gap in history. Three days where nothing appears to have changed, or a --full-refresh that collapsed years of history into a single current version.

Why. Snapshots are the one thing dbt cannot rebuild (chapter 6). The source only ever had current state.

The fix, which is prevention. Run snapshots on a reliable schedule with alerting on failure. Set full_refresh: false on the config. Back the tables up. Treat a snapshot table as production data, not as build output — because that is what it is.

10. Deletes that never propagate

What it looks like. Cancelled orders still counted. A customer deleted for a GDPR request still present downstream.

Why. Incremental models see inserts and updates. Absence does not arrive. No row appears saying “this row is gone”.

The fix. Soft deletes at source if you can influence it. Otherwise a periodic full refresh, or a snapshot with invalidate_hard_deletes = true. For deletion requests specifically, an explicit process — this is a compliance problem, not a modelling one.

11. The stale parse

What it looks like. You change a macro. Nothing happens. The compiled SQL still shows the old version. You change it again, more emphatically. Still nothing.

Why. target/partial_parse.msgpack (chapter 7) did not invalidate.

The fix.

rm target/partial_parse.msgpack
# or
dbt parse --no-partial-parse

Reach for this early. It costs thirty seconds and eliminates a whole category of confusion.

12. The Jinja labyrinth

What it looks like. A model file is forty lines of Jinja and no recognisable SQL. The compiled output is 1,400 lines. The person who wrote it has left. Nobody will touch it.

Why. Chapter 8’s progression, followed to its end. Each abstraction was locally reasonable.

The fix. Rewrite it as SQL, accepting some repetition. dbt’s value is that a model is a SELECT a data-literate person can read in a pull request, and heavy Jinja trades exactly that away. Repetition you can read beats abstraction you cannot.

Prevent it in review. “Could a new analyst read the compiled output and follow it?” is the whole test.

13. Stale data that passes every test

What it looks like. Dashboards show yesterday. Every model built successfully. Every test passed — because the data is internally consistent, just old.

Why. The loader broke and nothing checked. dbt transformed stale input perfectly.

The fix. dbt source freshness before the build, failing the pipeline (chapter 6). This is the most common data incident there is and it is entirely preventable with ten lines of YAML.

14. CI that thinks everything changed

What it looks like. A one-line pull request selects 1,800 models. CI takes an hour.

Why. One of: a package upgrade changing macro checksums, a dbt_project.yml edit, a dbt version difference between CI and production, or env_var() used in a config so the manifest differs by environment.

The fix. Pin dbt versions between CI and production. Re-publish the manifest after any upgrade. Keep env_var() out of parse-time config where you can. And use state:modified.body when description edits are the noise.

The mirror image — CI selecting nothing — is almost always a stale state manifest. Check that the production job is publishing it.

15. The alias collision

What it looks like. A model’s data is someone else’s data. Both runs report success.

Why. Two models with the same alias in the same schema (chapter 11). One overwrites the other; dbt does not check.

The fix. Use aliases sparingly. If you use them, a CI check for duplicate resolved relation names is a short script over manifest.json.

16. The bill

What it looks like. The warehouse invoice doubles. Nothing obviously changed.

Why. Usually accumulation rather than one event: developers rebuilding large models against full data, CI without slim CI, hourly runs of daily models, a new model with an accidental fan-out.

The fix. Chapter 13’s list, in order. target.name conditionals limiting dev data volume is normally the largest single saving and takes an afternoon.

Detect it. Cost attribution by model, from run_results.json joined to warehouse query history. Without it you are guessing.

The pattern

Reading the list back, nearly all of them share a shape:

   dbt did exactly what it was told.
   The run succeeded.
   The tests passed.
   The data was wrong.

dbt is not a system that fails loudly. It is a code generator, and a correct program that computes the wrong thing is still a correct program. The defences are all the same three things: tests that could actually fail, freshness checks at the boundary, and reconciliation against a known-good full rebuild.

What to take from this chapter

• The dangerous dbt failures are silent. The run succeeds, tests pass, numbers are wrong.
• Incremental models are the largest source: high-water-mark filters drop late data, non-unique keys duplicate, on_schema_change: ignore swallows columns, and deletes never arrive at all.
• A hard-coded relation name breaks ordering, environments, lineage and state selection at once, while looking fine. Find them with dbt-project-evaluator.
• Gate generate_schema_name on the exact production target, and make the warehouse permissions the backstop.
• Tags clobber; hooks accumulate. A model-level tag silently drops the model from folder-level selections.
• Snapshots cannot be rebuilt. Schedule them reliably, set full_refresh: false, back them up.
• Stale sources pass every test. Run dbt source freshness before the build.
• Delete the tests that always warn — they hide the ones that matter.
• Delete partial_parse.msgpack early when something inexplicably will not take effect.
• Repetition you can read beats abstraction you cannot.
• The defences are: tests that could fail, freshness at the boundary, and periodic reconciliation against a full rebuild.

Next: when to use dbt and when not — an honest comparison against SQLMesh, Spark, orchestrators, warehouse-native tooling and writing it yourself.