data agent timing out on full table scan: sample-then-scan pattern

TL;DR

The agent queries the full table for every question and times out. Make sample-then-scan the default: run exploratory queries against a small sample first, derive tight filters from what you learn, and only then hit the full table with those filters pushed down to the warehouse.

Symptom

A data agent answering questions against a large table issues full scans, times out, and retries the same full scan. Exploratory questions that need a quick peek at the shape of the data cost as much as the final answer.

When to use

• Agent queries time out on large tables
• Exploratory questions scan the entire table
• Warehouse cost or latency spikes come from agent traffic

When not to use

• The table is small enough to scan comfortably (sampling adds nothing)
• Queries already carry tight filters (the scan is already selective)
• The timeout is a network issue, not query cost (fix the connection)

Steps

1. Explore on a sample first, never on the full table:

SELECT * FROM events TABLESAMPLE SYSTEM (1) LIMIT 100;

Expected output: a quick look at columns, value ranges, and data quality. The agent learns the shape of the data in seconds instead of timing out.

2. Derive the tightest filters the question allows from the sample: date ranges, status values, id sets:

SELECT min(event_time), max(event_time), count(*) FROM events TABLESAMPLE SYSTEM (1);

Expected output: concrete bounds for the WHERE clause. Filters invented without looking at the data are usually too wide or reference values that do not exist.

3. Run the real query with filters pushed down, and confirm the plan uses them:

SELECT count(*) FROM events WHERE event_time BETWEEN DATE '2026-10-01' AND DATE '2026-10-31';

Expected output: the query finishes fast. If it still scans everything, the filter is not selective or the table lacks partitioning on that column.

4. For repeated questions, pre-aggregate in the warehouse instead of re-scanning:

CREATE TABLE daily_event_counts AS SELECT date(event_time), count(*) FROM events GROUP BY 1;

Expected output: the agent answers from the small aggregate table. One scheduled aggregation replaces hundreds of ad-hoc full scans.

Variant phrasings

agent query times out on bigquery/snowflake table

Same pattern. Samples are cheap, and most agent questions only need approximate answers for the exploratory steps.

llm agent running expensive warehouse queries

Put the sample-then-scan rule in the agent's system instructions so every generated query starts from a sample.

Why it happens

Agents generate SQL from the question alone, with no sense of table size. A human analyst glances at the table, guesses its scale, and writes selective queries from experience. The agent has neither the glance nor the experience, so it writes the naive full scan every time unless the workflow forces a sample first.

Edge cases

• Samples can miss rare values. If the question is about a rare event, the sample may show zero occurrences; widen the sample or query the full table with a tight filter directly.
• TABLESAMPLE SYSTEM is block-level and can be skewed on clustered tables. Use a larger percentage or a different sampling method for skewed data.
• Some questions genuinely need the full scan (exact distinct counts over billions of rows). Approve those explicitly rather than letting every question default to one.

Provenance

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

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

Thread query: data agent timing out on full table scan: sample-then-scan pattern

TL;DR: The agent queries the full table for every question and times out. Make sample-then-scan the default: run exploratory queries against a small sample first, derive tight filters from what you learn, and only then hit the full table with those filters pushed down to the warehouse.