Key Takeaways
- Natural language to SQL fails on real schemas, not on hard SQL — a code agent on o1-preview solved 91.2% of Spider 1.0 tasks but only 21.3% of Spider 2.0's enterprise problems, where databases routinely exceed 1,000 columns (Spider 2.0, ICLR 2025)
- Treat it as a six-stage pipeline — intent classification, schema retrieval, generation, static validation, sandboxed execution, narration — because each stage is independently testable and independently loggable
- Schema linking is the accuracy bottleneck: the model needs your column semantics, enum values, join paths, and business definitions, not just a
CREATE TABLEdump- Safety is a database problem before it is a prompt problem — a read-only role, statement timeouts, forced
LIMIT, and a parsed-AST allowlist bound the blast radius regardless of what the model writes- Score with execution accuracy against a golden query set, not string similarity. BIRD puts human expert execution accuracy at 92.96% (BIRD, NeurIPS 2023) — that is the bar a self-service tool is judged against
Every conversational BI demo works. You point a language model at a database, ask "how many users signed up last month", and it writes clean SQL on the first try. Then you put it in front of a sales lead who asks about "active accounts in Q3" against a 400-table warehouse, and it confidently returns a number that is wrong by an order of magnitude — with no indication anything failed.
The model did not get the SQL wrong. It got your business wrong, because nothing in the schema told it what "active" means.
Natural language to SQL works by grounding a language model in your schema rather than asking it to guess: retrieve the relevant tables, inject verified column semantics and business definitions, generate SQL against a read-only connection, validate it before execution, and return the query alongside the answer so a human can audit it. The architecture matters more than the model.
Why Natural Language to SQL Breaks in Production
Natural language to SQL systems break on real schemas, not on complex SQL. A code agent framework built on o1-preview solved 91.2% of Spider 1.0 tasks but only 21.3% of Spider 2.0's enterprise problems, where databases often exceed 1,000 columns and span BigQuery and Snowflake (Spider 2.0). Syntax was never the bottleneck.
Four failure modes account for most of the gap, and each needs a different fix:
Ambiguous business vocabulary. "Active user", "churn", "revenue", and "Q3" are company-specific definitions that exist in a metrics doc or an analyst's head, not in the DDL. A model asked to resolve them will pick something plausible and never flag the ambiguity.
Schema overload. Dumping 400 tables into the prompt buries the three relevant ones and pushes the useful tokens into the middle of the context, exactly where retrieval accuracy degrades most. Schema selection is a retrieval problem in its own right.
Silent join errors. The model picks a technically valid join path that fans out rows, double-counting the aggregate. The query runs, returns a number, and looks entirely healthy.
Dirty values. status = 'Screen Failed' versus screen_failed versus enum 4. The model guesses the string literal and gets zero rows back — which reads as "no results", not as an error.
Notice that three of these four produce confidently wrong answers rather than errors. That is what makes this a different engineering problem from a typical RAG pipeline failure, where a bad retrieval usually shows up as a vague answer rather than a precise falsehood.
The Six-Stage Architecture for Conversational BI
A production natural language to SQL pipeline is six stages, not one prompt: intent classification, schema retrieval, query generation, static validation, sandboxed execution, and result narration. Each stage is independently testable and independently loggable — which is the difference between a system you can debug at 2am and a demo that fails silently.
| Stage | Job | Failure it prevents |
|---|---|---|
| 1. Intent classification | Route the question: metric lookup, aggregation, drilldown, or out-of-scope | Answering questions the data cannot support |
| 2. Schema retrieval | Select the 3–10 relevant tables from the full catalog | Schema overload and lost-in-the-middle degradation |
| 3. Generation | Produce SQL from question + retrieved schema + semantic layer | Business-vocabulary mistranslation |
| 4. Static validation | Parse the AST: verify tables, columns, read-only, inject LIMIT | Destructive statements and hallucinated columns |
| 5. Sandboxed execution | Run on a read-only role with a statement timeout | Runaway scans and data modification |
| 6. Narration | Return the answer plus the SQL and the row count | Unauditable, unverifiable results |
Stage 6 is the one teams skip, and it is load-bearing for trust. Always show the generated SQL. A data-literate user who can see WHERE status = 'enrolled' will immediately catch that they meant to include screened participants too. Hiding the query converts a correctable misunderstanding into a wrong decision.
Stage 2 is where most of the engineering effort actually goes. Embed each table and column — name, type, description, sample values — and retrieve the top candidates per question, the same pattern as any other retrieval system. The chunking decisions that apply to documents apply here at table and column granularity, and pgvector on PostgreSQL is usually sufficient, since a schema catalog is thousands of rows, not millions.
Schema Linking Is the Accuracy Bottleneck
Schema linking — mapping the words in a question to the correct tables, columns, and literal values — determines more of the final accuracy than model choice does. A model given three correct tables with annotated columns and verified enum values will outperform a stronger model handed a raw 400-table CREATE TABLE dump. The fix is a curated semantic layer.
A semantic layer for natural language to SQL carries four things the DDL does not:
Column descriptions in business terms. dt_enr is not self-describing. dt_enr — date the participant was enrolled at the site; NULL for screen failures is.
Canonical metric definitions. One authored definition per metric: screen failure rate = screen_failed / (screen_failed + enrolled), per site, excluding withdrawn. This is the single most valuable artifact in the system, and it has to be written by someone who owns the metric — not inferred.
Enum and value maps. The actual distinct values for every low-cardinality column, so the model writes 'Screen Failed' and not 'screen-failed'.
Declared join paths. The intended join between two tables, with its grain, so the model does not invent a fan-out path that silently double-counts.
Version this layer in git and review it like code. When an answer comes back wrong, the fix is almost always a clarified definition rather than a prompt tweak — and a versioned definition file makes that a one-line, reviewable change. Prodinit treats the semantic layer as the deliverable that outlasts any single model choice.
Guardrails That Make Text-to-SQL Safe to Expose
Safety for a text-to-SQL layer is a database problem before it is a prompt problem. Prompt instructions are advisory; a PostgreSQL role with SELECT-only grants is enforced. Assume the model will eventually generate something wrong or hostile — prompt injection through a text column is a real path — and bound the damage in the infrastructure.
The controls that matter, in order of value:
- A dedicated read-only role with
SELECTon an explicit allowlist of views. Expose curated views, not base tables — this doubles as access control and as schema simplification. - Static AST validation before execution. Parse the generated SQL, reject anything that is not a single
SELECT, and verify every referenced table and column exists. This catches hallucinated columns before the database does. - A forced row limit. Inject
LIMITif absent. Non-technical users do not need 40 million rows, and an unbounded result set is the most common way a self-service tool takes down a production database. - A statement timeout (
statement_timeout) plus a per-user rate limit. A cartesian join should die in 10 seconds, not run for an hour. - Row-level security where the answer depends on who is asking. A site coordinator querying enrollment should see their site.
Log every stage: the question, retrieved tables, generated SQL, row count, latency, and whether the user re-asked. Re-asks are the cheapest quality signal available — a rephrased question almost always means the first answer was wrong. Feeding these traces into the same LLM observability stack you use for other LLM features turns silent failures into a reviewable queue.
How to Evaluate a Natural Language to SQL System
Score natural language to SQL on execution accuracy — does the result set match a human-authored reference query — not on how closely the generated SQL resembles the reference. Two very different queries can be equally correct. BIRD puts human expert execution accuracy at 92.96% (BIRD), the practical bar for a tool non-analysts are told to trust.
Build the harness before the feature:
- Collect 50–200 real questions from the people who will use it — Slack requests to the data team are the best source. Do not write them yourself; your questions will be too clean.
- Have an analyst author the correct SQL for each. This golden set is the asset. It outlives every model you swap in.
- Compare result sets, not strings — normalise column order and row order, then diff.
- Track refusals separately from errors. A system that says "I can't answer that from this data" is behaving correctly; scoring it as a failure trains you to build something worse.
- Segment by question type. Single-table lookups score far higher than multi-table aggregations, and a blended average hides exactly the cases that damage trust.
Wrong SQL is a hallucination with a number attached, so the hallucination detection and evaluation rubric practices that apply to generative features apply here with higher stakes — a prose answer reads as vague when it is unsure, while a query result reads as authoritative whether or not it is right.
What This Looked Like in Production
Prodinit built a conversational BI layer over live clinical trial data for a multi-site mental health research group, pairing Amazon QuickSight dashboards with a natural-language-to-SQL interface on a single PostgreSQL source. Summary-view generation dropped from 1–2 days to real-time across 7 active trial sites, and sponsors query live data without filing a request with the data team.
Three decisions did most of the work. Both layers read the same live PostgreSQL database, so a dashboard chart and a typed answer can never disagree — no ETL lag, no separate analytics store to reconcile. The translation layer was grounded in the actual trial schema and its domain vocabulary, so "screen failure" resolved to the right enum value instead of returning zero rows. And every question ran through a read-only connection, which is what made it safe to hand to clinical operations staff rather than analysts.
The scope discipline mattered as much as the architecture. The system answered enrollment, demographic, and outcome-score questions well because those were the questions stakeholders actually asked, captured up front and encoded in the semantic layer. Start with one well-modelled subject area and 50 real questions, ship it to the team that asked, then expand — a narrow conversational BI layer that is right is worth considerably more than a warehouse-wide one that is plausible. The same principle holds for the dashboard layer sitting on top.
Get Prodinit's AI engineering guides in your inbox
Deep-dives on production LLMs, voice AI, and MLOps — published weekly. No sales emails.
Frequently Asked Questions
Natural language to SQL is the translation of a plain-language question into an executable SQL query against a real database. A production implementation retrieves the relevant schema, grounds the model in business definitions and enum values, validates the generated SQL statically, and executes it on a read-only connection — returning both the answer and the query.
Accuracy depends far more on schema quality than on model choice. Published benchmarks show the gap clearly: a code agent on o1-preview reached 91.2% on Spider 1.0 but 21.3% on Spider 2.0's enterprise schemas. Expect high accuracy on simple lookups and materially lower accuracy on multi-table aggregations until a curated semantic layer exists.
Yes, if safety is enforced in the database rather than the prompt. Use a dedicated read-only role granted SELECT on curated views only, parse and validate the generated SQL before execution, inject a row limit, and set a statement timeout. Those four controls bound the damage regardless of what the model generates.
For anything beyond a demo, yes. The semantic layer holds column descriptions in business terms, canonical metric definitions, enum value maps, and declared join paths — the context that determines whether "active users last quarter" resolves correctly. Version it in git and review changes like code, because most accuracy fixes are definition changes.
Use execution accuracy against a golden set of 50–200 real user questions with analyst-authored reference SQL, comparing result sets rather than query strings. Segment scores by question type so simple lookups do not mask weak multi-table aggregations, and track refusals separately — a correct "I can't answer that" is not a failure.