dbt test returns failed rows but warehouse shows data is clean

TL;DR

The test and your manual query are looking at different things: a different target, a stale failures table, or data that changed between the run and your check. Confirm which target ran the test, check the failures table timestamp, rerun the test fresh, and compare against the same relation the test used.

Error

dbt test returns failed rows but warehouse shows data is clean

Steps

1. Confirm which target ran the test: check the job or the command history for --target. Expected: you know whether it was dev, prod, or CI.
2. Find the failures table dbt wrote (with --store-failures) and check when it was written. Expected: the timestamp tells you whether the failures are from the run you think they are.
3. Query the exact relation the test queried: same database, same schema, same table name as the compiled test SQL in target/compiled. Expected: you are looking at the test's actual input.
4. Rerun the test now: dbt test --select [TEST NAME] --store-failures. Expected: a fresh result against current data.
5. If the fresh run passes, the old failures were stale; if it still fails, diff the fresh failures table against your manual query. Expected: the discrepancy is explained by target, timing, or a different relation.

When to use

• dbt test reports failures you cannot reproduce with a manual query.
• Failures appear and disappear between runs without code changes.

When not to use

• The test consistently fails and your query shows the same bad rows (fix the data).
• The test fails at parse or config time (no failures table exists at all).

Tool compatibility

• dbt Core 1.0 and later, all adapters. --store-failures behavior is adapter-independent.

Variant phrasings

Test fails in CI but passes locally

Classic target mismatch: CI tests the CI-built relations, you query your dev schema.

Failures table shows old rows

The table is only rewritten on runs with --store-failures; without it, you may be reading a previous run's output.

Why it happens

dbt tests run against the relations in the active target's schema. Humans usually query their dev schema or the prod tables directly. Different targets, stale failures tables, and data that changed mid-investigation all create phantom mismatches.

Edge cases

• Time travel or cloned databases can show different data to different sessions on Snowflake.
• Incremental models change between runs; a test on yesterday's build disagrees with today's query.
• Someone else's run may have overwritten the shared failures table; check the run that wrote it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst7C3tNURD-5tVWbhoqjB2Q