Text-to-SQL
Having an LLM translate a natural-language question into a SQL query, run it under a restricted role, and answer from the result.
Text-to-SQL lets someone ask 'Which carriers were late most often in September?' and have an LLM write the database query. The system runs the query and turns the result into a sentence or a chart.
It is the right tool when the answer lives in tables (counts, totals, averages), where searching documents with Retrieval-Augmented Generation (RAG) would be the wrong approach.
A data analyst who speaks plain English to the business and SQL to the database, with a supervisor checking every query before it runs.
Treating 'the SQL ran' as success. Queries that execute but compute a different metric are the dangerous failures.
A robust pipeline: classify intent; retrieve the relevant schema slice (5–15 tables with column descriptions, sample values and business definitions), because full schemas rarely fit or help; generate SQL in one dialect, often with Structured output / decoding; validate by parsing (e.g. sqlglot), allow-listing tables, enforcing read-only and adding LIMIT; execute under a database role with row-level security and a statement timeout; on error, feed the error back for one or two bounded retries.
Failures are mostly semantic: wrong join path, join fan-out that double-counts, off-by-one date boundaries, and ambiguous metrics. A semantic layer (dbt metrics, Cube or LookML) with curated definitions is the single biggest reliability gain. On the BIRD benchmark, human experts reach 92.96% execution accuracy and the best systems still trail them as of 2026. Security lives in the database, not the prompt: the generator never holds write credentials, tenancy is enforced by the role (Least privilege, Fail closed). Evaluate by execution accuracy against reference queries on a golden set of real questions.
Semantic layer definitions retrieved with the schema slice; sqlglot parse with table allow-list; read-only role with row-level security; LIMIT 1000; 30 s statement timeout; maximum_bytes_billed or warehouse resource monitor; one retry on execution error; SQL always shown to the user; 150-question golden set scored by execution accuracy.
Logistics analytics: 'Average delivery delay by carrier for September 2026 in the EU.' Schema retrieval picks shipments, carriers, regions. Generated: SELECT c.name, AVG(s.delivered_at - s.promised_at) FROM shipments s JOIN carriers c ON … WHERE s.region = 'EU' AND s.promised_at >= '2026-09-01' AND s.promised_at < '2026-10-01' GROUP BY c.name LIMIT 1000. The validator confirms read-only, the session role adds tenant_id, BigQuery maximum_bytes_billed caps the scan at 50 GB; 7 rows return in 1.4 s and the UI shows the SQL next to the chart.
Commit: semantic layer + schema retrieval + read-only RLS role + execution-accuracy evals, with the SQL shown to the user. Note that warehouse scan cost can dwarf LLM cost. Follow-ups: 'a tenant sees another tenant's rows?' (impossible by role, not by prompt), and 'the query ran but the number is wrong?' (golden set with fan-out and date-boundary cases, reconciliation against finance's canonical metrics).
Numbers worth knowing
- BIRD human execution accuracy
- 92.96%expert baseline in the BIRD paper; leading systems still below it as of 2026
- Schema slice size
- 5–15 tablesretrieved per question from a catalog of hundreds
- Statement timeout
- 30 sinteractive analytics; longer queries go to async jobs
How to decide
| When | Do this |
|---|---|
| Questions are about numbers in tables | Text-to-SQL, not RAG over exported CSVs. |
| Metrics have contested definitions ('revenue', 'active user') | Build the semantic layer first; generate against metrics, not raw tables. |
| Multi-tenant warehouse | Enforce tenancy with the DB role and RLS; never with a WHERE clause the model writes. |
Cost maths
Per question: LLM 6k in + 400 out at $3 / $15 per M (illustrative) = $0.018 + $0.006 = $0.024. Warehouse: BigQuery on-demand at an assumed $6.25 / TiB scanned; a careless full scan of a 2 TiB table = $12.50, 500× the LLM call. Partition filters plus maximum_bytes_billed = 50 GB cap the worst case at ~$0.30.
Operating it
- Execution accuracy below 85% on the golden set
- Bytes scanned p95 per question above 100 GB
- More than 20% of questions need follow-up clarification
- Execution error rate: alert above 5%
- Empty-result rate: alert on 2× baseline
- Bytes scanned per query p95: alert above 100 GB
- Golden-set execution accuracy nightly: alert at −3 points
When it goes wrong
A revenue-by-region dashboard built on the assistant showed EU revenue 30% higher than finance's report.
Month-end reconciliation by the finance team.
The generated query joined orders to shipments (one-to-many) before summing, so split shipments multiplied order amounts. It ran without error.
Revenue defined once in the semantic layer, fan-out test cases added to the golden set, and a nightly check comparing assistant metrics with canonical finance tables.
Related terms
Sources
Check yourself
Three questions. Every option is explained once you answer.