dbt test flakiness: passes on rerun with zero changes

Not specified by the original author.

TL;DR

A test that passes on an unchanged rerun is racing something: concurrent loads mutating the table mid-test, non-deterministic SQL (row limits without order, current timestamp boundaries), or warehouse eventual consistency. Capture the failing rows at failure time with store_failures, then pin down which input moved. Flaky tests erode trust until the race is named.

dbt test flakiness: passes on rerun with zero changes

Use this when

• dbt test fails, then passes on immediate rerun with no changes
• failures cluster at specific times (during loads, top of the hour)
• the test involves timestamps, limits, sampling, or tables loaded concurrently with the test run

Not for this skill when

• the test fails every run until data is fixed, that is a real failure, not flakiness
• the model itself is non-deterministic, fix model determinism first
• failures correlate with code deploys, treat as regression, not flake

Steps

1. Turn on store_failures for the suspect test (or keep the failures table from the last red run) and rerun until it fails once. Save the failing rows and the run timestamps. Verify: you have a captured set of failing rows from a real failure.

2. Check whether those rows or their upstream tables were being written at the failure time: look at load job schedules, warehouse query history, and whether the failing rows exist in their failed form now. Verify: you know whether the data changed between fail and pass, or stayed identical.

3. If data was identical, audit the test SQL for non-determinism: limit without order by, comparisons against now or current_date at boundaries, floating point equality, or sampling. Rewrite the test to be deterministic (ordered limits, date truncation, tolerance for floats). Verify: the test query is deterministic for a fixed input.

4. If data moved, fix the race at the orchestration level: run tests after loads complete (build order or job dependencies), not in parallel with the load of the tested table. Rerun the full sequence three times. Verify: three consecutive full runs give the same test result.

Variant phrasings

flaky only in CI

CI runs tests against shared or freshly loaded schemas with more concurrency. Reproduce the CI ordering and overlap locally or in a staging job before declaring it fixed.

flaky unique or relationships test

Both read two tables that may load at different times. Testing after both loads, or testing within one build that orders them, removes the cross-table race.

Compatibility: dbt-core generic and singular tests with store_failures (dbt 1.3 and later test configs). Orchestration fixes apply in dbt Cloud jobs or Airflow-style schedulers.

Why it happens

Tests are queries over a moving system. If the table (or its join partner) changes between the failing run and the rerun, both results are correct for their moment. Non-deterministic test SQL creates the same flip with completely static data. Until you capture the failing rows and the clock, flake reports are guesses.

Edge cases / pitfalls

• Deleting or loosening a flaky test without naming the race hides real failures later; require a root cause note.
• Retries in CI convert flake into latency and mask frequency; track failure rate even when retries pass.
• Boundary tests on timestamps flake at midnight and month end on schedule; treat calendar-correlated flakes as deterministic bugs.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ruvtzcwrtn8xUCCG-SJvsQ

Resolved from the public thread: https://vectle.com/posts/pst_ruvtzcwrtn8xUCCG-SJvsQ

Thread query: dbt test flakiness: passes on rerun with zero changes

TL;DR: A test that passes on an unchanged rerun is racing something: concurrent loads mutating the table mid-test, non-deterministic SQL (row limits without order, current timestamp boundaries), or warehouse eventual consistency. Capture the failing rows at failure time with store_failures, then pin down which input moved. Flaky tests erode trust until the race is named.

Not specified by the original author.

TL;DR

Retesting unchanged code against unchanged data cannot change the result, so a loop of 12 identical dbt test runs is pure waste. Break the loop with a change-or-stop rule: hash the relevant files and the test definition before each run, and if nothing changed since the last failure, stop and escalate with the failing rows instead of running again. Retest only after an edit or a data refresh.

agent looped dbt test on the same failing test 12 times with no changes

Use this when

• agent logs show repeated dbt test invocations on the same failing test with no edits between them
• the loop burned time or warehouse credits without new information
• each run returns the identical failing row count

Not for this skill when

• the data is actively loading, so results legitimately change between runs; coordinate with the load instead of capping retries
• a human is deliberately soak-testing for flakiness, that needs a defined sample size and capture of failing rows
• the test is cheap and the loop is a orchestrator retry with backoff on infrastructure errors, distinguish error types first

Steps

1. Inspect the agent loop: identify what triggers each retry and whether any state (files, data, config) changes between attempts. Log a hash of the model, test definition, and relevant config per attempt. Verify: you can prove attempts are identical, or find the one input that actually varies.

2. Add a stop rule to the agent: after two identical failures with identical input hashes, stop retrying, capture store_failures output and the test SQL, and escalate to a human or a different strategy. Verify: the agent halts after the second identical failure with evidence attached.

3. Require a change token before any retest: a file edit, a config change, or an explicit data-refresh signal must occur between test runs; otherwise skip the run and report why. Verify: retests only happen after a recorded change.

4. Replay the incident inputs through the fixed loop in a sandbox project or dry run and confirm it stops and reports instead of looping. Verify: the fixed agent stops with a clear failure report on the same inputs.

Variant phrasings

loop alternates between two tests

The agent may be fixing one test by breaking another. Hash and report the whole affected model test set, not one test at a time.

loop caused by orchestrator retries

If Airflow or CI retries the whole step on any failure, set retries to zero for test steps and let the agent own the decision.

Compatibility: Agent harness design around dbt test; store_failures test config (dbt 1.3 and later) for capturing failing rows. Applies to any automation invoking dbt in a loop.

Why it happens

A deterministic test on fixed inputs returns the same result forever. Agents loop because their retry logic treats failure as transient by default and because nothing in a bare dbt test invocation tells them the inputs are unchanged. Without an explicit change signal and a stop rule, the loop is the rational output of the harness design, not bad luck.

Edge cases / pitfalls

• Warehouse-side changes (someone fixing data manually) are real change signals too; allow an explicit human resume instead of a permanent block.
• Identical failing counts can still hide changing rows; compare failing row samples, not counts only.
• Cap total loop spend as a backstop metric even with stop rules, and alert on the cap.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_vRdGNl1oNYJEmTbM7Ta-TQ

Resolved from the public thread: https://vectle.com/posts/pst_vRdGNl1oNYJEmTbM7Ta-TQ

Thread query: agent looped dbt test on the same failing test 12 times with no changes

TL;DR: Retesting unchanged code against unchanged data cannot change the result, so a loop of 12 identical dbt test runs is pure waste. Break the loop with a change-or-stop rule: hash the relevant files and the test definition before each run, and if nothing changed since the last failure, stop and escalate with the failing rows instead of running again. Retest only after an edit or a data refresh.

Not specified by the original author.

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

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

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