Design a Text-to-SQL Data-Analyst Agent
Track
Agentic System Design
Inspired by
Snowflake / dbt Labs
Scale
large schemas · execution-validated · self-correcting · read-only
Difficulty
Intermediate
"Ask your data a question in English" is a deceptively hard agentic problem: the model must map ambiguous language onto a specific, large schema and produce SQL that not only parses but returns the right answer. It's also a problem with real production mileage — Uber's QueryGPT sits in front of a platform serving ~1.2 million interactive queries a month (Uber), and Pinterest ships text-to-SQL inside its internal query tool (Pinterest). The design win is grounding in the schema and validating by execution with a self-correction loop. Walk it through the agentic project loop.
1. Clarify the task (spine: task)
- Goal: convert a natural-language question into correct SQL over a warehouse, run it, and return the answer (often with a chart/explanation).
- Correctness bar: subtly-wrong SQL that runs is the danger — a wrong number presented confidently is worse than an error.
- Read-only by default: analytics queries; writes/DDL are out of scope or heavily gated.
- Scope: NL → SQL → execute → answer. Adjacent: BI/visualization layer.
Interview signal
"A query that runs isn't a query that's correct." Emphasizing execution validation and semantic correctness (right tables, joins, filters) — not just syntactic validity — is the key insight.
Think firstGPT-4-era systems reach ~85% execution accuracy on the Spider benchmark, but GPT-4 alone scored ~55% on BIRD — against a human baseline of 93%. What's different about BIRD that erases thirty points?Commit to an answer, then revealHide
BIRD looks like a real warehouse. Spider's databases are small and clean; BIRD's 95 databases total 33.4 GB across 37 domains, with dirty values, ambiguous column names, and questions that require external business knowledge to interpret (Li et al.). The gap is precisely the production problem: not SQL syntax, but grounding language in a messy, underdocumented schema, which is why metadata and schema retrieval, not the model, dominate this design.
2. Requirements & constraints
| Dimension | Target | Consequence |
|---|---|---|
| Schema size | Hundreds/thousands of tables | Schema retrieval, not whole-schema in prompt |
| Correctness | Right answer, not just valid SQL | Execution + self-correction + verification |
| Safety | No destructive queries, cost control | Read-only role, row/scan limits, sandboxing |
| Latency | Interactive (seconds) | Bounded correction loop; cache schema |
Think firstThe warehouse has 2,000 tables. What goes in the prompt — and what's the single biggest lever on SQL quality?Commit to an answer, then revealHide
Retrieve, don't dump: embed table/column descriptions and pull only the
relevant handful, with their joins, keys, and example queries. The biggest
lever isn't the model — it's metadata quality: human-written column
descriptions, synonyms, and sampled values. Pinterest's production experience
matches: real-world accuracy diverged sharply from benchmark numbers, and
table search became a core part of the problem, solved with a vector index
over table metadata. A model can't guess that revenue lives in
fct_orders.gmv_net unless something tells it.
3. Context & tools (spine: context)
- Schema retrieval: the schema is too big for the prompt — retrieve the relevant tables/columns via embeddings over table/column descriptions, plus documented joins, keys, and example queries. Good metadata (descriptions, synonyms, value samples — Pinterest also injects low-cardinality column values so filters match real data) is the single biggest quality lever.
- Domain scoping: Uber's QueryGPT organizes context into workspaces — curated bundles of tables + vetted SQL samples per business domain (Mobility, Ads, …) — plus an intent agent that routes each question to the right workspace before generation (Uber). Scoping first, generating second.
- Tools:
search_schema,run_sql(read-only, sandboxed),get_sample_rows,explain_query. Execution is the verification tool. - Few-shot / semantic layer: curated example NL↔SQL pairs and a metrics/ semantic layer keep business definitions consistent ("revenue" means one thing).
Think firstThe model generates plausible SQL on the first try maybe 70% of the time. What system structure turns that into a reliable product?Commit to an answer, then revealHide
A loop, not a shot: execute the query (or EXPLAIN it), feed errors and
suspicious results back, and let the model correct itself — the database is a
free, grounded verifier. This is measured, not folklore: DIN-SQL's decomposed
pipeline with an explicit self-correction stage pushed Spider execution
accuracy to 85.3% from a prior state of the art of 79.9%
(Pourreza & Rafiei). Same generate → run →
observe → fix pattern as a coding agent; the environment's feedback does the
work the single-shot model can't.
4. Orchestration (spine: orchestration)
Generate → execute → observe → self-correct:
- Retrieve relevant schema for the question (route to a domain workspace if you have them).
- Generate candidate SQL — decomposing first helps: schema linking → query sketch → full SQL, the DIN-SQL recipe.
- Execute (or dry-run/
EXPLAIN): if it errors, feed the DB error back and fix. - Check the result is sensible (types, non-empty, ballpark); if the question was ambiguous, ask a clarifying question.
The ReAct loop — and why agents compound errors
An agent cycles Thought → Action → Observation, each Observation conditioning the next Thought. But errors multiply across steps: if a single step is p reliable, an n-step trajectory is only pⁿ reliable. Drag p and watch a 95%-per-step agent fall below 60% end-to-end over ten steps — the case for bounded loops, validation, and human-in-the-loop.
The execution result is the grounded feedback that turns a plausible query into a correct one — the same reflection loop as a coding agent, with the database as the environment.
Think firstTwo ways to score generated SQL against a gold query: exact string match, or “both return the same result set.” Each has a characteristic false negative or false positive. Name them.Commit to an answer, then revealHide
Exact match fails correct queries: infinitely many SQL strings compute the same answer (join order, aliases, equivalent predicates), so it undercounts real accuracy. Execution match passes wrong queries: a query can return the gold result on this database instance by coincidence — wrong filter, but no rows happened to hit it. That's why benchmarks moved to execution accuracy, ideally checked across multiple database states, and why "runs-but-wrong" needs its own production metric.
5. Evaluation (spine: evaluation)
- Execution accuracy: does the generated query return the same result set as a gold query? The standard metric on Spider — 200 databases across 138 domains, built to force cross-schema generalization (Yu et al.), and BIRD, its large-and-dirty successor where GPT-4 managed 54.89% against a 92.96% human baseline.
- Your schema is the benchmark that matters: build an in-house eval set of real questions with verified gold queries; public leaderboard gains don't transfer automatically (Pinterest's benchmark-vs-production gap).
- Robustness: ambiguity handling, correct joins, and value grounding (e.g. "California" vs "CA"). pass@k reflects the self-correction retries.
- Guard against plausible-but-wrong: track cases that run but return wrong numbers — the most dangerous failure.
Think firstWhat stops this agent from dropping a table or scanning the entire warehouse — and where must that control live?Commit to an answer, then revealHide
At the database boundary, not in the prompt: a read-only role that physically cannot execute DDL, plus scan/row caps, statement timeouts, and cost guards. Asking the model nicely to "write safe, efficient SQL" is a wish; a restricted DB user is a guarantee.
6. Guardrails & safety (spine: guardrails)
- Read-only role + limits: a restricted DB user, row/scan/cost caps, statement timeouts — the agent can't drop tables or run away with warehouse spend.
- Injection: the question and any retrieved metadata are untrusted; never let them escalate to DDL or cross-tenant access.
- PII/governance: respect column-level access controls; mask sensitive fields.
Think firstYou launch at 40% query acceptance. The model won't change this quarter. Where does improvement come from — and what did Uber's 20+ iterations of QueryGPT actually iterate on?Commit to an answer, then revealHide
Everything around the model: richer table/column descriptions, curated NL↔SQL examples harvested from accepted queries, domain workspaces that shrink the search space, better value samples, and clarifying-question behavior. Uber's iterations were largely context and routing changes — intent classification, table selection, column pruning — not model swaps. The flywheel is operational: every accepted or corrected query in production is a labeled example that improves retrieval and few-shots on your schema.
7. Operations (spine: operations)
- Tracing: log question → retrieved schema → SQL → result → corrections.
- Cost control: monitor and cap query cost; cache schema embeddings and hot results.
- Feedback: thumbs-down / corrected queries become few-shot examples and eval cases, steadily improving accuracy on your schema — the highest-leverage loop in the whole system.
8. Tradeoffs & alternatives
| Choice | Alternative | Why |
|---|---|---|
| Schema retrieval + workspaces | Whole schema in prompt | Large schemas blow the context and dilute attention |
| Execution self-correction | Single-shot generation | DB errors + results are a groundable signal (85.3% vs 79.9% on Spider) |
| Semantic/metrics layer | Raw table access | Consistent business definitions; fewer subtle errors |
| Read-only + limits | Full DB access | Analytics is read-only; caps prevent cost/destruction |
| In-house eval set | Public benchmark scores | Benchmark gains don't transfer to your messy schema (the BIRD lesson) |
9. Interviewer follow-ups
The SQL runs and returns a number, but it's wrong. How do you catch it?
Execution validity ≠ correctness. Mitigate with a semantic/metrics layer (so 'revenue' is defined once), value grounding (map 'California'→'CA' from sampled values), result sanity checks, and — for high-stakes answers — showing the query
- assumptions for human confirmation. Track 'runs-but-wrong' as its own metric.
The question is ambiguous ('top customers'). What does the agent do?
Detect ambiguity (top by revenue? count? recency? over what period?) and ask a clarifying question rather than guessing. A wrong-but-confident answer erodes trust faster than a quick clarification — the agent should know when it doesn't know what's being asked.
Why do production accuracy numbers lag the Spider leaderboard by so much?
Spider is small, clean, and well-normalized; real warehouses have thousands of underdocumented tables, dirty values, and questions needing business knowledge — the regime BIRD was built to measure, where GPT-4 dropped to ~55% vs a 93% human baseline. Close the gap with metadata curation, domain scoping, and an in-house eval set, not by chasing leaderboard prompts.
How do you stop it from running a query that scans the whole warehouse?
Enforce it at the database boundary: a read-only role with scan/row limits,
statement timeouts, and cost guards, plus a dry-run/EXPLAIN before executing
expensive queries. Never rely on the prompt to 'write efficient SQL' as the safety
mechanism.
Key takeaways
- Text-to-SQL is schema grounding + execution-validated self-correction — a query that runs is not a query that's correct.
- Schema retrieval, metadata quality, and domain scoping (Pinterest's table search, Uber's workspaces) are the biggest quality levers; the execution result is the reflection signal (DIN-SQL: 85.3% on Spider with self-correction).
- The Spider→BIRD gap (~85% → ~55%, humans at 93%) is the benchmark-to- production gap — build an in-house eval set on your own schema.
- Enforce read-only limits at the DB boundary, not in the prompt, and turn accepted/corrected queries into the improvement flywheel.
References
- Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task: Yu et al., EMNLP 2018; the cross-domain benchmark and execution-accuracy evaluation regime.
- Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs: Li et al., NeurIPS 2023 (BIRD); 33.4 GB of dirty databases, GPT-4 at 54.89% vs 92.96% human.
- DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction: Pourreza & Rafiei, NeurIPS 2023; decomposition + self-correction, 85.3% Spider execution accuracy.
- QueryGPT – Natural Language to SQL Using Generative AI: Uber engineering; workspaces, intent/table/column agents, ~1.2M queries-a-month platform, 20+ iterations.
- How we built Text-to-SQL at Pinterest: Pinterest engineering; benchmark-vs-production gap and table search via vector retrieval as a core sub-problem.