SQL / Analytics Agent

An agent that turns English questions into SQL over a 400-table warehouse; the whole design targets one failure: a query that parses, runs, and returns a plausible wrong number.

The contract and the failure

• Input: one English sentence over 400 tables. Output: the answer, the exact SQL, the row count, and bytes scanned. Always show the SQL, never optional.
• Schema grounding: the model’s picture of tables comes from the warehouse itself, not its guesses.
• The four gates a generated query passes, and who catches a failure:

Parses?      no -> syntax error        (self-announcing, free)
Executes?    no -> unknown col / type  (self-announcing, free)
Plausible?   absurd -> 0 rows, 1e14    (heuristically catchable)
             plausible -> WRONG NUMBER (no signal exists)  <- budget goes here

• The database supervises gates 1-2 for free with better error messages than you’d write. SQL sanitization spends effort on gates already guarded for free.

Silent errors

• Fan-out (double counting): a one-to-many join fans one order row into N line rows; SUM(o.amount_cents) above the join counts each order once per line. Multiplier is the amount-weighted avg basket size (e.g. 2.3x), not round, not a magnitude spike, moves with seasonality, so nothing catches it.
• Grain = “one row of this table represents what?” amount_cents is additive only at order grain. Fix: aggregate at line grain: SUM(i.qty * i.unit_price_cents).
• Other silent errors that all parse, execute, and return believable numbers:

Schema strategy

• Branch on table count: under 30 put all in context + cached prefix; 30-400 retrieve relevant tables per question; 400+ or multi-tenant build a curated semantic layer (~20 hand-built views).
• Retrieval isn’t about capacity: 400 tables x ~120 tokens DDL = ~48k tokens, fits a 1M window. It’s about attention (the U-shaped lost-in-the-middle sag); cutting to ~8 tables improves accuracy and pushes the question to the high-recall end.
• Semantic layer beats retrieval on consistency: metric defined once, grain fixed, fan-out eliminated for pre-joined views. Costs 2-4 weeks of analytics engineering. Two disagreeing answers destroy trust faster than one wrong answer.
• Retrieval rules: embed descriptions, not names (dense search chokes on fct_ordln_agg_v2); add BM25 over raw names for verbatim lookups. Show sample rows (3 rows ~60 tokens) and the grain line, not just column names, rows reveal that status holds 'A'/'C'/'P' not 'active'.

Declare grain before SQL

• Structured output uses constrained decoding: fields generate in order, so field order = decision order. Put grain and fanout_risk before sql so the model commits before writing a single SQL token.
• SQLPlan fields: tables_needed, grain, metric_definition, fanout_risk: bool, sql (last).
• Gives a machine-readable claim to cross-check: fanout_risk: false next to an aggregate over a one-to-many join is a hard contradiction, not a heuristic.

The real guardrail: credentials

• Six defences, strongest first; the boundary is the DB account, not the query text:

• SQL parsing is weak because dangerous queries are valid SELECTs: pg_read_file, lo_export, dblink, a 3-way cross join (10^18 rows), none contain a banned keyword. Parsers also bypass via comment splicing, dialect quirks (DO $$..$$), and Unicode homoglyphs.
• Parsing earns its place only for good repair error messages and a table allowlist. Use sqlglot to ask the parse tree structural questions (immune to homoglyph/encoding tricks).
• Prompt injection comes from the user, not the model. Prompt hardening is probabilistic and eventually fails; RLS + allowlist don’t, because they aren’t made of text.

The loop and repair

• Cache hit -> re-run stored SQL (no model call). Miss -> retrieve schema -> generate plan+SQL -> static validation -> EXPLAIN + scan-budget check -> execute (read-only, LIMIT, timeout) -> sanity checks -> answer.
• One model call in the normal path, sometimes two; everything else is deterministic, testable, free.
• Two retries: validation rejection and DB error both append error text and loop. Feed the DB error back verbatim, Postgres HINT (Levenshtein over its column list) fixes ~85% on first repair; paraphrasing to “invalid column” throws it away.
• Cap at 3 rounds: each repair resends the whole conversation, so tokens grow quadratically; a query still failing after 3 self-corrections usually wants a column that doesn’t exist.

Sanity checks

• Deterministic code over question + plan + rows; annotates, never blocks. Flags: empty result, exact-zero or >1e12 aggregate, aggregate above a one-to-many join when fanout_risk: false, NOT IN subquery, inequality without IS NULL, time question with no date filter.
• The grain check is structurally strongest: it cross-checks the model’s fanout_risk against the DB’s declared FK graph (a non-model source). Limits: needs declared FKs, and misses GROUP BY.
• Pattern to generalize: make the model commit to something checkable, then check it in code.

Memory and cost

• Four layers: working (this turn), semantic (metric defs -> consistency), episodic (verified question->SQL pairs -> few-shot + cache), procedural (warehouse quirks). Episodic compounds: accuracy climbs steeply over the first few hundred verified queries by teaching your warehouse’s idioms.
• Verified cache also fixes reproducibility (temperature 0 isn’t deterministic). Invalidate via stored referenced-table list + DDL audit-log subscription.
• Model spend is small; the warehouse tail is the risk. Filtered “last quarter” query ~4 GB = $0.02; drop the date filter and it scans 2 TB = $10 (~230x the model call). That’s why EXPLAIN runs before every execution and a scan ceiling exists.
• Cost ranking (each alone vs $0.20 baseline): semantic layer 2.0x, prompt caching 2.4x, both 4.4x, both + cache hit 16x. Semantic layer wins twice (smaller schema + fewer repair rounds).

Evals

• Grade on what the query returns (execution accuracy), never on query text. String similarity ranks a wrong query (0.97, dropped DISTINCT) above a correct rewrite (0.62); a metric that prefers wrong to right is worse than none.
• Compare values not column names; compare as sorted multisets; exclude gold queries returning zero rows (everything matches empty).
• Run integration cases at N=3 majority (identical input isn’t identical output).