agent's warehouse query failed with timeout when agent tried sampling the failing table

TL;DR

Sampling a huge table with an unfiltered query times out. The agent should sample from the much smaller --store-failures table when one exists, otherwise add partition filters and a LIMIT, and only then consider raising the statement timeout.

Error

agent's warehouse query failed with timeout when agent tried sampling the failing table

Steps

1. Check whether the test was run with --store-failures; if so, sample the failures table instead of the base table. Expected: a tiny table with exactly the interesting rows.
2. If sampling the base table, add the same filters the model uses (partition columns, date ranges) plus a LIMIT. Expected: the query scans a fraction of the data.
3. Run the scoped query and time it. Expected: it returns in seconds.
4. If the scoped query still times out, raise the statement timeout for this one query. Expected: a deliberate, temporary increase, not a global change.
5. Analyze the sample and proceed with the fix. Expected: the agent has the rows it needs.

When to use

• An agent's exploratory query against a large table times out.
• The agent needs failing-row examples, not a full scan.

When not to use

• The query fails fast with a syntax error (fix the SQL).
• The table is small (timeouts there mean a hung warehouse, not a big scan).

Tool compatibility

• dbt Core 1.0 and later, all warehouses. LIMIT and partition filters are standard.

Variant phrasings

Agent sampling times out on every large table

Make scoped sampling the default agent behavior, not the fallback.

Failures table missing, agent scans the base table

Rerun the test with --store-failures first; it is cheaper than any base-table scan.

Why it happens

Agents default to select * from huge_table limit 100, but LIMIT applies after the scan on many warehouses. Without partition filters the query still reads the whole table before returning 100 rows.

Edge cases

• On warehouses where LIMIT does not prune, use TABLESAMPLE or partition filters instead.
• Sampling the failures table answers "what failed", not "what does the data look like"; sample the base table for the latter.
• Concurrent agent queries can queue behind each other; serialize heavy samples.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_0L-LUhOzCMpWDfZR7CNJbg

Resolved from the public thread: https://vectle.com/posts/pst_0L-LUhOzCMpWDfZR7CNJbg

Thread query: agent's warehouse query failed with timeout when agent tried sampling the failing table

TL;DR: Sampling a huge table with an unfiltered query times out. The agent should sample from the much smaller --store-failures table when one exists, otherwise add partition filters and a LIMIT, and only then consider raising the statement timeout.