dbt Debug & Fix

The reasoning

dbt derives the DAG from ref calls, and a DAG cannot have a cycle. It detects this while parsing — before any connection is opened — which is why the failure is instant and no SQL appears in `target/run/`. The message names the edge it closed the loop on, which is a starting point but not always the model at fault.

The usual cause is a filter that grew. Someone needed `fct_orders` to exclude flagged orders, so they refed the flags model; the flags model was already built from `fct_orders`. Each change was reasonable on its own and the cycle appeared at the join.

The fix is almost always extraction: pull the part both need into a third model. Here, an `int_orders` model with the order rows, then `fct_order_flags` on top of it, then `fct_orders` selecting from `int_orders` and anti-joining the flags. Three nodes, no cycle, and each has a grain you can state.

Two things not to do. Do not replace one ref with a hardcoded relation name to hide the edge from the parser — the cycle still exists at runtime, you have just made dbt unable to see it, and the build order becomes luck. And do not reach for `{{ this }}`: it does not create an edge, so it compiles, but a model reading its own previous state outside an incremental materialization is a different and worse bug.

The reasoning

A **Parsing Error** happens before dbt knows what your project contains: malformed YAML, a Jinja block that never closes, a config it does not recognise. It usually names a file and often a line, and nothing else in the project has been looked at yet — so the fix is always local to that file.

A **Compilation Error** means the project parsed and dbt could not turn a node into SQL. A ref to a model that does not exist, a cycle, a macro raising, an undefined variable, a contract mismatch. Nothing has been sent to the warehouse, so the problem is entirely in your Jinja and your graph — and `dbt compile --select <model>` reproduces it in isolation.

A **Database Error** is the warehouse rejecting what you sent: a syntax error in the generated SQL, a missing column, a permission problem, a type mismatch. This is the one where you open `target/run/<model>.sql` and read what was actually issued, because the model file will not show you the materialization wrapper or the resolved relation names.

A **Runtime Error** means the statement was accepted and failed while executing — a division by zero, a cast that failed on row nine million, a timeout, an out-of-memory. These are data-dependent, which is why they pass in CI on a small schema and fail in production, and why the fix is usually a defensive expression rather than a syntax change.

The reasoning

The first and most common cause: your dev schema is not empty. You built the whole project last week, so `dbt run --select fct_orders` finds `stg_orders` sitting there. CI starts from an empty schema, so the same selector has nothing to select from. The fix is to select the ancestry — `--select +fct_orders` — or to build the project.

The second: slim CI without deferral configured correctly. `--select state:modified` builds only what changed, which is right, but any unchanged parent has to come from somewhere. `--defer --state <manifest>` tells dbt to resolve unbuilt refs against the production relation instead. Without it, an unmodified parent is simply missing.

The third is permissions or a schema-name difference: CI's role cannot see the schema, or `generate_schema_name` behaves differently under the CI target so the relation exists under a name nothing is looking for. Check the compiled SQL — the fully-qualified relation name in `target/compiled/` tells you immediately which schema dbt expected.

The general lesson worth stating: a dev schema accumulates state, and that state hides ordering bugs. Building from empty occasionally — a fresh schema, or `dbt build --full-refresh` in a scratch target — is how you find them before CI does.

The reasoning

On an incremental run dbt builds your select into a temporary relation and then merges or inserts into the existing target. The target's columns were fixed when it was first created, and `on_schema_change` decides what happens when the two disagree. The default is `ignore`: use the target's columns, silently discard the rest.

The four values are worth knowing exactly. `ignore` drops new columns silently. `fail` raises an error, which turns the silent loss into a run failure you will notice. `append_new_columns` adds new columns to the target, leaving historical rows null for them. `sync_all_columns` adds new ones *and* drops removed ones, which is the closest to "the target should look like the model" and the most destructive.

My default is `append_new_columns` for models where new columns are expected and history can be null, and `fail` for anything with a contract or a downstream consumer, because a schema change on those should be a human decision. `ignore` is defensible only when you are deliberately pinning the schema, and then you should not be using `select *`.

Two related points. `select *` is what makes this invisible — an explicit column list would have failed to compile when the column was missing, or done nothing when it was added, either of which is more predictable. And after switching to `append_new_columns`, historical rows still have null for the new column; if you need it populated, that is a full refresh, not a config change.

The reasoning

Start with the compiled SQL, because the model file cannot show you what a filter rendered to. The usual culprits are all visible there: a var that fell back to a default and filtered everything out, an `{% if %}` that was not taken so the join disappeared, a date literal that rendered as an empty string, or an incremental filter comparing against a table that already contains today's maximum.

Then check upstream. `select count(*)` on each parent relation, or `dbt ls --select +my_model` and look at whether they built in this run at all. An empty parent gives an empty child, and if the parent is a view over a source, the source may be the empty one.

Then run the compiled query yourself with the filters removed one at a time. This is the fastest way to find which predicate is doing the eliminating, and it takes a minute because you already have the exact SQL.

The reason no test caught it is worth saying: `not_null` and `unique` on an empty table both pass — there are no rows to violate them. If empty is a failure for this model, the test is a singular test asserting a minimum row count, or `dbt_utils.fewer_rows_than` against a parent. That is a genuine gap in most projects.

The reasoning

The model claimed one row per order and the join to payments broke that claim. Any order with two payments — a card and a voucher, a partial refund, a retry — appears twice after the join, and `sum(o.amount)` adds the same amount once per payment row. Nothing errors, because summing duplicated rows is a perfectly valid query.

The general rule to state: a join to a table that has more than one row per join key changes the grain, and any additive measure from the *other* side is now overstated. The measure is fine; the row count is not. This is why the first question in a model review is "what is one row of this model", and why the grain belongs in the description.

The fixes, in order of preference. If you do not need payment columns, do not join — use `exists` or an anti-join. If you need one attribute, pre-aggregate payments to order grain first (`count(*) as payment_count`, `max(method)`) and join that, which keeps the join many-to-one. If you genuinely need payment grain, then this model is a payments fact and the revenue measure should not be summed at that grain at all.

The test that catches it is `unique` on `order_id` in the model. It would have failed on this pull request. It is a one-line addition and it is the single most valuable test in a dbt project, because it makes a grain claim machine-checked rather than a comment.

The reasoning

The filter is evaluated once against the target's current maximum, which is 10:00, so both rows pass on this run's comparison — except the maximum is recomputed from the target the *next* time, and by then it is 11:00. Anything that arrives later stamped before 11:00 is below the watermark forever. The run reports success, adds a row, and silently drops the other.

No ordinary test catches it. `unique` and `not_null` pass on the rows that are there. The absence of a row is not something a row-level assertion can see, which is why this bug lives in production for months.

What does catch it is comparing against a full refresh — rebuild the model in one pass over everything that ever arrived and compare row counts or a checksum. That is what the rebuild below does, and the gap is the missing order. Running that comparison on a schedule, or in CI on a sample, turns an invisible bug into a failing check.

The fixes, and their costs. A lookback window — `>= max(updated_at) - interval '3 days'` — reprocesses a few days each run and needs `unique_key` set so reprocessed rows update rather than duplicate; it is the standard answer and it bounds the lateness you tolerate rather than eliminating it. Filtering on an ingestion timestamp rather than a business one is better where the loader provides one, because ingestion time is monotonic by construction. And for anything where correctness matters more than cost, periodically full-refresh.

The reasoning

`is_incremental()` returns true only when the materialization is `incremental`, the relation exists, and the run is not a full refresh. Change the materialization to `table` and the first condition fails permanently, so the guarded block is never emitted. The compiled SQL is a plain unfiltered select and the DDL is a drop-and-recreate.

It usually arrives one of two ways. Someone hit an incremental correctness problem, switched to `table` as a temporary fix, and left the guard in place. Or someone copied a model as a template and changed the materialization without reading the body.

Why it survives: the output is correct. Every test passes, the reconciliation against a full refresh passes trivially, and the only symptom is the run time and the bill. On a large model that is a genuinely expensive mistake, and on a project with twenty of them it is the whole warehouse budget.

How to find them across a project: `dbt ls --select config.materialized:incremental` gives you the real list, and comparing it against a grep for `is_incremental` finds both directions of the mismatch — guards on non-incremental models, and incremental models with no guard at all, which is the opposite and worse bug because it rebuilds the full select into the target every run and duplicates or overwrites depending on the strategy.

The reasoning

Start with the compiled SQL from both environments and diff them. If they differ, the cause is in the compilation: a `{% if target.name %}` branch, a var set differently in the production job, an `env_var` with a different value, or `generate_schema_name` resolving a source or ref to a different relation. This is the fastest check and it is decisive.

If the compiled SQL is identical, the difference is state. An incremental model in production has months of accumulated rows built by whatever the logic was at the time; your dev copy was built once, from scratch, by today's logic. Those are not the same table, and if the logic has changed since, production contains a mixture. The same applies to snapshots, which record what your production runs happened to observe.

The other state difference is upstream: a source that is genuinely different — a dev database populated by a sample, or a staging copy of the vendor feed — or an upstream model that was full-refreshed in dev and is incremental in production.

The prevention is the interesting half. Keep environment branches to row limits only. Deferral (`--defer --state`) lets dev read production relations for unmodified models, which removes most of the state divergence. And when an incremental model's logic changes, full-refresh it in production, because otherwise the mixture is permanent.

The reasoning

The mechanism: `date_trunc('day', event_at)` on a UTC timestamp puts an 11pm New York event into the next UTC day. Across a month those misplacements mostly cancel between adjacent days, so daily numbers look approximately right, but at a month boundary they do not cancel — the first and last day of the month are wrong in opposite directions and the month total is off.

Before writing SQL, settle the definition: whose day? Finance almost always means a business-local day, and "local" may mean the company's headquarters, the customer's timezone, or the store's. Those are three different models and only one of them is what the report means.

Then implement it in one place. Convert to the business timezone in the staging layer, store both the UTC instant and the business date as separate columns, and have every mart bucket on the business date column. The mistake to avoid is converting in each mart, because then the conversion exists in eight places with three opinions and daylight saving will eventually catch one of them.

Two adjacent causes with the same signature, worth ruling out: a filter with `<` where it should be `<=` on the last day, and an incremental model whose watermark is in a different timezone from the data it compares against — which drops or reprocesses exactly the boundary rows.

The reasoning

The generated SQL groups the column and filters to groups with a count above one — and null handling in SQL means nulls do not form a group that counts as duplicated in the way you expect. The test's job is duplicate detection and it does that correctly; it was never a key check.

`unique` and `not_null` together are what make a key claim. Neither alone is sufficient: `not_null` allows duplicates, `unique` allows a column that is null on every row. This is why every generated dbt project template puts both on the primary key column, and why a review comment that says "add not_null" on a keyed column is always right.

The stronger version for a composite key is a `dbt_utils.unique_combination_of_columns` test on the column list, since testing each column individually says nothing about the pair. And for the null case specifically, the interesting question is usually upstream: why is the key null? A surrogate key built by concatenation goes null when any component is null, so a null key often means the key macro is the bug rather than the data.

Two related tests that hide the same way. `accepted_values` also passes on nulls unless you add `not_null`, and `relationships` passes for rows whose foreign key is null — which is often legitimate but means the test cannot tell you whether ninety percent of your fact rows failed to match.

The reasoning

`not_null` selects rows where the column is null and finds none. `unique` finds no duplicated groups. `accepted_values` finds no unexpected values. `relationships` finds no orphans. All of them return zero rows and all of them pass, correctly and uselessly.

The direct fix is a test on volume. A singular test in `tests/` — `select 1 where (select count(*) from {{ ref('fct_orders') }}) = 0` — is four lines and turns empty into a failure. `dbt_utils.fewer_rows_than` compares a model against its parent, which catches the more general case where the model is not empty but has lost most of its rows.

The better fix is usually upstream. An empty model almost always comes from an empty or stale source, and `dbt source freshness` running before the build catches it earlier and localises it better. A count that collapses without the source being stale is a logic bug, and that is where a row-count test on the model itself earns its place.

The related trap worth mentioning: a `where` filter on a test config also shrinks what the test can see. `where: "created_at >= current_date - 7"` is a reasonable way to keep a test cheap, and it also means the test passes on a table where everything older than a week is broken. Cheap tests and thorough tests are different tests.

The reasoning

A unit test runs the model's SQL against rows you supply and compares the result to rows you expect. For that to be meaningful, there has to be one query. An incremental model has two — the first-run branch and the incremental branch — and they legitimately return different things from the same input. dbt refuses to guess.

The declaration goes in the unit test: an `overrides` block setting `macros: {is_incremental: false}` to test the full-refresh path, or `true` to test the incremental path. Testing both is two unit tests, which is usually what you want, because the branch that only runs in production is the one nobody has exercised.

That is the real value here. CI builds into an empty schema, so it always takes the first-run branch — the incremental filter is exactly the logic your pipeline never tests. A unit test with `is_incremental: true` is the only cheap way to assert anything about it, and it does so without a warehouse round trip.

The wider point about unit tests: they test *logic* against fixed input, where data tests assert things about real data. A unit test is where you pin down a case statement's edge cases, a window function's tie-break, or a date-bucketing rule — the things that are hard to produce in real data and easy to get wrong.

The reasoning

`severity: warn` makes a failing test report without failing the run, which is right for something that is genuinely tolerable — a handful of orphaned rows from a known vendor issue. It becomes noise the moment nobody acts on it, and a test suite with standing warnings trains everyone to ignore the output, which costs you the tests that matter.

The configuration that makes it work is thresholds. `error_if: ">100"` and `warn_if: ">0"` means the test warns while the problem is small and fails the run when it grows. That encodes the actual policy — a few is survivable, a lot is not — instead of a binary that does not match reality.

`store_failures: true` is the other half. It writes the failing rows to a table so you can look at what is actually failing rather than at a count, and it makes a standing warning investigable months later. Combined with a threshold, you get a warning you can triage instead of a warning you dismiss.

The policy I would argue for: every warn-severity test has a named owner and a review date. If neither exists, it is either an error or it should be deleted. A test that has warned for four months has already told you everything it is going to.

The reasoning

On each run dbt compares the source against the snapshot's currently-open rows. Unchanged records are left alone. Changed records get two operations: an update setting the open row's `dbt_valid_to` to the new version's timestamp, and an insert of the new version with `dbt_valid_from` set to that same timestamp and `dbt_valid_to` null. The result is a Type 2 dimension — one row per version of each entity, with a validity interval.

The four columns: `dbt_scd_id` is a hash that uniquely identifies the version row and is the snapshot's primary key. `dbt_updated_at` is the value the strategy used to decide something changed. `dbt_valid_from` and `dbt_valid_to` are the interval, with null meaning current.

How you query it is the follow-up. `where dbt_valid_to is null` gives you the current state, which is what a dimension usually wants. A temporal join — `on f.event_at >= d.dbt_valid_from and (f.event_at < d.dbt_valid_to or d.dbt_valid_to is null)` — gives you what the customer's tier was at the time of the event, which is the entire reason to snapshot.

The boundary detail worth getting right: intervals are half-open, so the closing row's `dbt_valid_to` equals the next row's `dbt_valid_from`. Using `<=` on both ends in a temporal join double-counts events landing exactly on the boundary — which is rare, non-obvious and produces duplicated facts.

The reasoning

The timestamp strategy is a shortcut: it trusts the source to tell it when a row changed, and compares only `updated_at`. That is cheap and it is correct exactly as often as the source's timestamp is trustworthy. Plenty of systems update a row through a path that does not touch it — a bulk correction, a direct database edit, a replication tool that preserves the original value, a column maintained by application code that one code path forgot.

The check strategy compares the actual column values instead. `strategy='check'` with `check_cols=['tier', 'status']` hashes those columns and records a new version whenever the hash moves, regardless of any timestamp. `check_cols='all'` watches every column, which is the safest and the most expensive — and which will also record a version every time an irrelevant column changes.

The trade-off to state: timestamp is cheaper — one column comparison, no hashing, and it uses the source's own notion of when the change happened, so `dbt_valid_from` is meaningful. Check is more reliable and stamps `dbt_valid_from` with the run time, so your history's resolution is your run schedule and the timestamps tell you when you *noticed*, not when it happened.

How to decide: audit the source. If `updated_at` is maintained by a database trigger or by a CDC tool, trust it. If it is set by application code, check it against the columns you care about for a week before relying on it. And when you switch an existing snapshot from timestamp to check, be aware the two produce different `dbt_valid_from` semantics, so your history will have a seam.

The reasoning

dbt hashes the `check_cols` for each source row and compares against the hash stored on the open snapshot row. A different hash means a new version, regardless of any timestamp. That is why this run produces the two rows the timestamp strategy did not.

Note what the validity columns now mean. `dbt_valid_from` on the new row is the moment the snapshot *ran*, not the moment the tier changed — dbt has no way to know the latter, because the source did not record it. Your history's granularity is therefore your run frequency, and a change that happens and reverts between two runs is invisible.

Choosing `check_cols`: naming the columns is precise and means an unrelated column changing does not create a spurious version, but a column you forgot to list is a change you will never see. `'all'` is safe against omission and will record a version whenever any column moves, including ones you do not care about — which on a wide table means a lot of rows recording nothing interesting.

The middle path most teams land on: `check_cols` listing the business attributes the dimension is actually about, plus a `unique` test on `dbt_scd_id` and a check that no entity has two open rows. That last one is the assertion that actually protects you, because a snapshot with two rows where `dbt_valid_to is null` will silently duplicate every fact you join to it.

The reasoning

The comparison is one-directional: dbt looks at each source row and asks whether it has changed. A row absent from the source is never examined, so its open snapshot row is never touched. By default your snapshot is an accumulating record of everything that ever existed, all of it apparently current.

The reporting consequence is concrete. `where dbt_valid_to is null` — the standard current-state query, and the basis of most dimensions built on snapshots — returns deleted customers alongside live ones. Counts are inflated, and a temporal join attributes events to entities that no longer exist.

`hard_deletes='invalidate'` makes dbt close rows that have disappeared, setting `dbt_valid_to` to the run timestamp. `hard_deletes='new_record'` instead inserts a tombstone row with a `dbt_is_deleted` flag, which is more explicit and lets a consumer distinguish "deleted" from "changed to something else". Older projects will have the equivalent `invalidate_hard_deletes=true`.

The caution that matters: with invalidation on, a source that is *temporarily* incomplete closes rows that were never deleted. A partial load, a failed extract, a filter change upstream — any of these will look like a mass deletion, and the next run reopens them as new versions, leaving fake history. So invalidation belongs on sources where you trust completeness, and freshness or volume checks belong in front of it.

The reasoning

A full refresh drops and rebuilds. For a model that is harmless: it is a pure function of its inputs and comes back identical. A snapshot is not — its rows record what the source looked like at the times you happened to run it, and those past states exist nowhere else. Rebuilding gives you one row per entity, valid from now, and every prior version is deleted.

Recovery options are all warehouse-level. Snowflake Time Travel, BigQuery table snapshots, Databricks Delta time travel, or an ordinary backup — if you notice inside the retention window. Outside it, the history is unrecoverable, which is a genuinely different category of incident from anything else dbt can do to you.

Prevention is the part worth being specific about, because "be careful" is not a control. Exclude snapshots from anything that can full-refresh: `--exclude resource_type:snapshot` in every job that passes the flag. Run snapshots as their own job with `dbt snapshot`, so no `dbt build --full-refresh` can reach them. Restrict who can run a full refresh in production at all. And a pre-hook that raises when `flags.FULL_REFRESH` is true on a snapshot is a five-line macro that makes the accident impossible.

The related habit: back the snapshot up before any change to its config. Switching strategies, changing `check_cols` or altering the unique key all change how future rows are produced and can invalidate the comparison against existing ones — and once you have run it, undoing that is the same problem as this one.

The reasoning

With invalidation on, dbt finds open snapshot rows whose key is absent from the source and closes them. The current-state query is now honest: `where dbt_valid_to is null` returns live entities only, and counts stop drifting upward forever.

The risk is that dbt cannot tell a deletion from an absence. A failed extract, a partial load, a filter someone added upstream, a source model that errored and left yesterday's smaller table behind — all of them present as "these rows are gone". The snapshot closes them, and the next successful run reopens them as brand-new versions with a fresh `dbt_valid_from`. You end up with fabricated history: a gap that never happened, and a change event for every affected entity.

That fabricated history is worse than the original problem because it is not obviously wrong. Nobody notices a dimension where five thousand customers churned and un-churned overnight until a retention chart looks strange months later, and by then the rows are indistinguishable from real ones.

So invalidation needs a gate in front of it. Source freshness before the snapshot, so a stale feed stops the pipeline. A volume check — today's source row count against a recent average — so a collapse fails rather than propagates. And running snapshots in their own job that only starts if the upstream load reported success. `hard_deletes='new_record'` is the softer alternative: it inserts an explicit tombstone rather than closing the row, which at least makes a spurious deletion visible as a row you can find and delete.