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.

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