dbt test

Not specified by the original author.

TL;DR

The not_null test found null values in orders.customer_id. Re-run the test with --store-failures so dbt saves the offending rows to a table, query that table to see which rows are null, then fix the model logic or the upstream data that produced them.

Error

Database Error in test not_null_orders_customer_id

Steps

1. Re-run just this test and store the failures: dbt test --select not_null_orders_customer_id --store-failures. Expected: the run completes and reports a failures table location.
2. Query the failures table in your warehouse (it lands in the test schema, named after the test). Expected: you see the actual rows where customer_id is null.
3. Check whether the nulls come from the source or from model logic: compare against the raw table and look for joins that drop the key. Expected: you identify the step that introduces the nulls.
4. Fix the cause: add a coalesce, filter bad rows, or repair the upstream source. Expected: the model logic no longer produces null keys.
5. Re-run dbt test --select not_null_orders_customer_id. Expected: the test passes with zero failing rows.

When to use

• dbt test fails on a not_null generic test with a Database Error.
• You need to see the failing rows, not just the failure count.

When not to use

• The test fails at parse time with an "Invalid test config" error (a YAML problem, not a data problem).
• The test name is misspelled in your --select (dbt reports no matching tests).

Tool compatibility

• dbt Core 1.0 and later, all adapters. --store-failures needs a test schema your role can write to.

Variant phrasings

Database Error in test notnull[model]_[column]

Same pattern for any column: the test found nulls, and the fix is in the data or the model.

Failure in test not_null ... got 5 results, configured to fail if > 0

The long form of the same failure, with the failing row count included.

Why it happens

not_null compiles to a query that selects rows where the column is null. Any nulls in the built model, whether from the source or introduced by a join or a case expression, fail the test.

Edge cases

• Empty strings are not null; if your nulls are actually '', the not_null test passes and you need a separate check.
• Tests run against the model's target schema; make sure you rebuilt the model before re-testing.
• On BigQuery, the failures table lands in the test dataset configured in your profile.

Provenance

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

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

Thread query: "Database Error in test notnullorderscustomerid"

TL;DR: The not_null test found null values in orders.customer_id. Re-run the test with --store-failures so dbt saves the offending rows to a table, query that table to see which rows are null, then fix the model logic or the upstream data that produced them.

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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.

5. 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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Create the staging relation before the test run, in the right schema: the test queried a staging table that the run never built, usually because the test ran standalone, the model was deselected, or the schema macro resolves differently under the test target. A test cannot create its model. Running dbt test alone against a target where the staging model was never materialised produces exactly this database error.

how to fix dbt error "database error: relation staging table does not exist" during test run

Use this when

• The check or run fails with this symptom: a dbt run fails with database error relation staging table does not exist during a test run.
• You are working on a dbt project running tests against a target where the staging model may not have been built and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A production incident where the staging table was dropped by another process, which needs ownership and permissions work rather than a build order fix.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Check whether the staging relation exists in the target schema the test ran against. Verify: the relation is absent in that schema, or present under a different schema name
2. Review the command that ran: a test-only invocation, a selection that excluded the model, or a fresh target. Verify: the reason the model was never built is identified
3. Run dbt build or dbt run for the staging model and its parents in the same target. Verify: the relation exists with current data
4. Rerun the tests in the same invocation pattern the pipeline uses. Verify: tests pass against the built relation
5. Make the pipeline entry point a build or a run-then-test sequence so the standalone test case cannot recur. Verify: the job definition shows the ordered steps

Variant phrasings

dbt database error relation staging table does not exist test

The shorter phrasing focused on the named failure.

dbt test relation does not exist staging

The symptom phrasing a searcher tries next.

dbt staging table missing during test run fix

The variant naming the failing stage or tool directly.

Compatibility: dbt Core 1.5 and later, dbt build and test selection behaviour

Why it happens

Relation existence depends on target, schema macros, and what ran before. Ephemeral models, custom schema names, and test-only CI steps against empty schemas all produce a missing relation that looks like a data problem but is a build order problem.

Edge cases / pitfalls

• Custom generateschemaname macros often resolve differently for the default target and CI targets; compare the compiled relation name across targets.
• An ephemeral model has no relation at all; a test or a downstream query that expects a physical table needs the materialisation changed.

Provenance

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

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

Thread query: how to fix dbt error "database error: relation staging table does not exist" during test run

TL;DR: Create the staging relation before the test run, in the right schema: the test queried a staging table that the run never built, usually because the test ran standalone, the model was deselected, or the schema macro resolves differently under the test target. A test cannot create its model. Running dbt test alone against a target where the staging model was never materialised produces exactly this database error.