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.