how to debug a failing dbt test

TL;DR

Run the single failing test on its own, read the compiled SQL dbt actually executed, then look at the failing rows instead of guessing. Turn on stored failures so the bad rows land in a table you can query directly. Most failing tests are either bad data upstream or a test that no longer matches the real business rule, and the failing rows tell you which one it is within minutes.

how to debug a failing dbt test

Use this when

• dbt test goes red and you need the actual failing rows, not just a count
• a test fails in CI but passed locally, or the reverse
• you inherited a test and cannot tell what it is checking

Not for this skill when

• the test will not compile, that is a Jinja problem, see the compilation error skill
• the model itself fails to build, fix the model before its tests
• you want to write new tests from scratch, see the writing tests skill

Steps

1. Run only the failing test to get a fast, focused failure instead of the whole suite:

dbt test --select my_failing_test

Expected output: the test fails again with a failure count. You now have a tight loop that runs in seconds instead of waiting on everything.

1. Read the exact SQL dbt executed, because it lives in the compiled artifacts and often differs from what you assume:

find target/compiled -name "*my_failing_test*"

Expected output: the path to the compiled test SQL file. Open it. This is what the warehouse actually ran, not the Jinja source you wrote.

1. Enable stored failures so the bad rows persist in a table you can inspect at leisure:

dbt test --select my_failing_test --store-failures

Expected output: dbt writes the failing rows to your audit schema and reports the row count. Check your project config for the audit schema name if you are unsure where they landed.

1. Query the failures table and actually look at the offending data URIs

SELECT * FROM dbt_audit.my_failing_test LIMIT 20;

Expected output: the offending rows. Patterns jump out here fast, like nulls clustered in one column or a date range that should never have been there.

1. Decide whether the data or the test is wrong, fix the right one, and confirm green:

dbt test --select my_failing_test

Expected output: green. If the data was bad, fix it upstream or add a filter to the model. If the rule was wrong, update the test definition. Do not tweak the test to match bad data without telling whoever owns the data.

Variant phrasings

dbt test failed but no rows shown

Default test runs only report counts, which is why debugging feels blind. Step 3 with --store-failures is what materializes the rows you need.

dbt test passes locally fails in CI

Data differs between environments, or CI runs against a stale state. Compare the failures table across targets and the difference usually explains itself.

how to see the sql a dbt test runs

The compiled SQL under target/compiled is the ground truth. Always read it before editing the test, since macros and refs can reshape the query.

Why it happens

dbt tests are just SQL queries that return the rows violating a rule, and a test fails when that query returns anything at all. Without stored failures you only see a count, which is why debugging feels blind. Once you can see the rows, the cause is usually obvious: upstream data changed, the test encodes a stale assumption, or the model logic drifted out from under it.

Edge cases

• Severity warn: some tests are configured to warn instead of error. Check the test config before panicking over a warning.
• Tests on disabled models still compile but may behave oddly. Verify the model is actually enabled.
• store_failures needs write access to the audit schema. A permission error here is a grants issue, not a test issue.
• Very wide tables make failures tables expensive to store and read. Select only key columns in custom singular tests.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pK2jRWiHiXMiEsbsHhJRfg