dbt test timeout on large warehouse table

TL;DR

The test scans the entire large table and the warehouse kills it or the run times out. Narrow the test with a where config (for example the last 30 days), or run the test on a bigger warehouse. Then rerun and confirm it finishes.

Error

dbt test timeout on large warehouse table

Steps

1. Identify the timing-out test and read its compiled SQL in target/compiled. Expected: you see a full-table scan.
2. Add a where filter to the test config in the schema YAML to scope it to recent rows:

tests:
  - unique:
      config:
        where: "created_at > current_date - interval '30 days'"

Expected: the test definition now carries the filter.

1. Alternatively, split the test by partition: one test per recent partition instead of one test over all history. Expected: each test scans a fraction of the data.
2. Rerun the test and time it. Expected: it completes well within the timeout.
3. If it still times out, run it on a larger warehouse size for the test job. Expected: the bigger compute finishes the scan.

When to use

• A data test times out only on your largest tables.
• The test SQL has no filter and scans full history.

When not to use

• Every test times out (a warehouse-wide problem, not a test-scoping problem).
• The test hangs rather than timing out (check for a stuck query).

Tool compatibility

• dbt Core 1.0 and later, all adapters. where test configs are adapter-independent; date syntax varies.

Variant phrasings

Test killed by warehouse timeout, not dbt

The warehouse cancelled the query; scoping the test is still the fix.

not_null times out on a billion-row table

Same pattern; filter to recent partitions.

Why it happens

Data tests compile to scans over the built model. On huge tables those scans exceed statement timeouts or patience, even though the test logic itself is fine.

Edge cases

• A where filter changes what the test guarantees; document that old partitions are not covered.
• Partition filters must match the table's actual partitioning, or the warehouse still scans everything.
• dbt Cloud job timeouts are separate from warehouse timeouts; check both.

Provenance

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