explain the query before running it agent pattern

Not specified by the original author.

TL;DR

Make the agent produce three things before executing: the SQL, a plain-English explanation of what each clause does, and the database EXPLAIN plan which it must read. The two explains catch different mistakes: the English one catches wrong intent, the plan catches wrong cost. It works because generation is cheap and execution is expensive, sometimes irreversibly so, and the most common agent disaster is a cross join on a billion-row table run just to check something.

"explain the query before running it"

Use this when

• An agent runs analytics queries against large tables
• Query cost or runtime is a real concern
• You want the agent's intent verified before it touches data
• Reviewing agent work and need the reasoning in inspectable form

Not for

• Trivial queries on tiny tables where the ceremony costs more than the query
• Write operations, which need stricter gates than explanation
• Engines with no EXPLAIN equivalent, where only the English half applies

Steps

1. Require the agent's response to follow the three-part shape:

INTENT: monthly revenue by product category for Q3, excluding test orders.
SQL: SELECT category, SUM(amount_cents)/100.0 AS revenue ...
PLAN CHECK: explain below, then execute only if estimated rows < 1M.

Expected output: every proposed query arrives with its intent stated separately from its implementation, so a reviewer can spot intent errors without reading SQL.

2. Have the agent run EXPLAIN and read the plan itself:

EXPLAIN (ANALYZE FALSE, COSTS TRUE)
SELECT c.category, SUM(o.amount_cents)/100.0 AS revenue
FROM orders o JOIN products p ON o.product_id = p.id
WHERE o.ordered_at BETWEEN '2026-07-01' AND '2026-09-30'
  AND o.is_test = false
GROUP BY c.category;

Expected output: the plan shows the join strategy and estimated row counts. The agent must summarize it in one sentence, hash join on 2M rows, before proceeding.

3. Check the estimate against a threshold and refuse over it:

estimated_rows = parse_plan_rows(explain_output)
assert estimated_rows < 1_000_000, f"too broad: {estimated_rows} rows, narrow the filters"

Expected output: queries estimated above the threshold never execute. The agent must add filters or aggregation instead of hoping the warehouse is fast.

4. Execute, then compare actual rows against the estimate:

-- after execution, the runner records both numbers
-- estimate: 84,000 rows | actual: 83,912 rows | ratio: 1.0  -> healthy
-- estimate: 84,000 rows | actual: 4,200,000 rows | ratio: 50 -> investigate

Expected output: a logged estimate-vs-actual pair per query. Ratios far from 1.0 mean the planner was blind, usually stale statistics, and the next similar query gets extra scrutiny.

5. Keep the explanation with the result so the answer is auditable:

ANSWER: Q3 revenue was $4.21M across 6 categories.
EVIDENCE: intent + SQL + plan summary above; actual rows 83,912.

Expected output: anyone reading the answer later can see what was asked, what ran, and what it cost, without rerunning anything.

Variant phrasings

agent SQL planning pattern

The pattern is propose, explain twice, then execute. Skipping either explanation removes one class of mistake detection.

LLM explain before execute database

The database EXPLAIN is the non-negotiable half: English explanations can be confidently wrong, but the plan is the engine telling you what it will actually do.

two-phase query agent

Phase one is pure reasoning with zero side effects. Phase two executes only what phase one justified. Agents that interleave the two are the ones that surprise you.

Why it happens

Agents generate SQL fluently and execute it eagerly, which inverts the safe order of operations: thinking should be cheap and doing expensive, but for an agent both feel equally free. Forcing the explanation first separates intent errors (wrong question, wrong filter) from execution errors (wrong cost, wrong scale), and each gets caught by a different check. The plan-reading step additionally trains the agent, over many iterations, to write queries whose plans look sane on the first try.

Edge cases

• EXPLAIN itself can be slow on some engines for very complex queries; use estimated costs without ANALYZE to keep it cheap.
• Plans lie when statistics are stale; the estimate-vs-actual check in step 4 is what catches this, so do not skip it.
• Read-only contexts still benefit: the pattern is about cost and correctness, not just write safety.
• Some agents will write a plausible English explanation for broken SQL; the plan check is the backstop, never rely on prose alone.

Provenance

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

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

Thread query: "explain the query before running it" agent pattern

TL;DR: Make the agent produce three things before executing: the SQL, a plain-English explanation of what each clause does, and the database EXPLAIN plan which it must read. The two explains catch different mistakes: the English one catches wrong intent, the plan catches wrong cost. It works because generation is cheap and execution is expensive, sometimes irreversibly so, and the most common agent disaster is a cross join on a billion-row table run just to check something.

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Agents invent column names that sound right but dont exist. Fix it by giving the agent the real schema (from information_schema or the table DDL) in its context and validating every generated query's columns against that schema before execution, rejecting or repairing hallucinations automatically.

agent's SQL referenced a column that doesn't exist: schema grounding

Use this when

• Agent-generated SQL fails on a nonexistent column
• The agent invents plausible but wrong field names
• The schema changed and the agent works from memory

Not for this skill when

• The SQL syntax is invalid (thats parsing)
• The role lacks permission (thats grants)
• The logic is wrong but columns exist (thats the query logic)

Steps

1. Pull the real schema and put it in the agent's context:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders';

Expected output: the authoritative column list. This is ground truth; the agent's memory of the schema is not.

2. Instruct the agent to use only listed columns:

You may only reference these columns: [id, customer_id, total, created_at].
If you need another column, STOP and ask instead of guessing.

Expected output: the agent stops inventing names. Explicit permission to stop beats a confident hallucination.

3. Validate generated SQL against the schema before running it:

import sqlparse  # or your parser of choice
# extract referenced columns, compare against the schema list, reject unknowns

Expected output: bad queries are caught pre-execution with a clear message naming the unknown column. The validator is cheap; the failed query against prod is not.

4. Refresh the schema snapshot on a schedule or on failure:

# on "column does not exist", refresh the schema cache and retry once

Expected output: schema drift (a renamed column) is handled by refresh-then-retry instead of looping on a stale snapshot.

Variant phrasings

agent uses the right column name but wrong table

Same grounding problem at the table level. Provide the table list too, not just columns.

works for one database, fails on another

Schemas differ between environments. Ground per environment; never share one schema snapshot across dev and prod.

Why it happens

LLMs predict plausible text, and column names are highly predictable from table names (orders probably has customerid... or is it userid?). Without the actual schema in context, the model guesses, and its guesses are confident and often wrong. Schema grounding turns generation into selection from a known list, which is what makes it reliable.

Edge cases

• Case sensitivity: quote rules differ by database; ground with the exact cased names.
• Wide tables make full schemas expensive in context; provide per-task relevant tables, not the whole warehouse.
• Views and computed columns may not appear in information_schema the way the agent expects; include view definitions where needed.

Provenance

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

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

Thread query: agent's SQL referenced a column that doesn't exist: schema grounding

TL;DR: Agents invent column names that sound right but dont exist. Fix it by giving the agent the real schema (from information_schema or the table DDL) in its context and validating every generated query's columns against that schema before execution, rejecting or repairing hallucinations automatically.