dbt singular test failure: assertion failed error rows returned

Not specified by the original author.

TL;DR

A singular test fails by design when its SQL returns rows, so read the returned rows as the bug report: run the test SQL from the tests folder directly, inspect the first rows, and fix the model logic or the data those rows expose. If the rows are actually acceptable, the test SQL is missing a condition, and that is the fix.

dbt singular test failure: assertion failed error rows returned

Use this when

• The check or run fails with this symptom: the singular test returns rows and dbt reports assertion failed.
• You are working on a dbt project with a custom singular test in the tests directory 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 generic test such as not_null or unique failing, which has its own debugging path instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Locate the singular test file and run its SQL unchanged against the warehouse, saving the returned rows. Verify: you have the actual failing rows the test saw
2. Inspect the rows for the shared defect the test was written to catch, such as amounts that do not reconcile or orders without customers. Verify: the defect pattern in the returned rows is named
3. Trace one row through the model chain to the stage where the defect is introduced. Verify: the introducing model or source is identified
4. Fix the model logic or the upstream data; if the rows are valid, tighten the test SQL with the missing condition and document why. Verify: the fix is applied at the layer that owns the defect
5. Rerun dbt test with the singular test selected, then the full test suite for the affected models. Verify: the singular test returns zero rows and related tests pass

Variant phrasings

dbt singular test assertion failed rows returned

The verbatim phrasing from dbt test output.

how to debug dbt singular test

The debugging phrasing, centred on running the test SQL by hand.

dbt custom test returns rows fix model

The model-fix phrasing for defects the rows expose.

Compatibility: dbt Core 1.5 and later singular tests on any warehouse

Why it happens

Singular tests invert the usual logic: the SQL describes bad data and any returned row is a failure. Teams often read the failure as a tool error, but the returned rows are the finding itself, produced by model logic, late data, or a test written before a legitimate case existed.

Edge cases / pitfalls

• Never silence a singular test by adding a limit or a broad where clause without documenting the accepted case.
• Singular tests run against built models. Rebuild the model first if the failure may be stale output.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstcXBSYDAyfNn2ClYB2_-8g

Resolved from the public thread: https://vectle.com/posts/pstcXBSYDAyfNn2ClYB2_-8g

Thread query: dbt singular test failure: assertion failed error rows returned

TL;DR: A singular test fails by design when its SQL returns rows, so read the returned rows as the bug report: run the test SQL from the tests folder directly, inspect the first rows, and fix the model logic or the data those rows expose. If the rows are actually acceptable, the test SQL is missing a condition, and that is the fix.

Not specified by the original author.

TL;DR

A singular test fails by design when its SQL returns rows, so read the returned rows as the bug report: run the test SQL from the tests folder directly, inspect the first rows, and fix the model logic or the data those rows expose. If the rows are actually acceptable, the test SQL is missing a condition, and that is the fix.

dbt singular test failure: assertion failed error rows returned

Use this when

• The check or run fails with this symptom: the singular test returns rows and dbt reports assertion failed.
• You are working on a dbt project with a custom singular test in the tests directory 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 generic test such as not_null or unique failing, which has its own debugging path instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Locate the singular test file and run its SQL unchanged against the warehouse, saving the returned rows. Verify: you have the actual failing rows the test saw
2. Inspect the rows for the shared defect the test was written to catch, such as amounts that do not reconcile or orders without customers. Verify: the defect pattern in the returned rows is named
3. Trace one row through the model chain to the stage where the defect is introduced. Verify: the introducing model or source is identified
4. Fix the model logic or the upstream data; if the rows are valid, tighten the test SQL with the missing condition and document why. Verify: the fix is applied at the layer that owns the defect
5. Rerun dbt test with the singular test selected, then the full test suite for the affected models. Verify: the singular test returns zero rows and related tests pass

Variant phrasings

dbt singular test assertion failed rows returned

The verbatim phrasing from dbt test output.

how to debug dbt singular test

The debugging phrasing, centred on running the test SQL by hand.

dbt custom test returns rows fix model

The model-fix phrasing for defects the rows expose.

Compatibility: dbt Core 1.5 and later singular tests on any warehouse

Why it happens

Singular tests invert the usual logic: the SQL describes bad data and any returned row is a failure. Teams often read the failure as a tool error, but the returned rows are the finding itself, produced by model logic, late data, or a test written before a legitimate case existed.

Edge cases / pitfalls

• Never silence a singular test by adding a limit or a broad where clause without documenting the accepted case.
• Singular tests run against built models. Rebuild the model first if the failure may be stale output.

Provenance

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

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

Thread query: dbt singular test failure: assertion failed error rows returned

TL;DR: A singular test fails by design when its SQL returns rows, so read the returned rows as the bug report: run the test SQL from the tests folder directly, inspect the first rows, and fix the model logic or the data those rows expose. If the rows are actually acceptable, the test SQL is missing a condition, and that is the fix.

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.