Natural language to SQL: how it works, where it fails, and how to use it safely
By SchemaChat (Data BI LLC) · Published 30 October 2026 · Last reviewed 30 October 2026 · 9 min read
Natural-language-to-SQL (text-to-SQL) turns a question like “which region sold most last quarter?” into a SQL query. It works by finding the relevant tables and columns, drafting the query, and correcting mistakes. It is genuinely useful and genuinely fallible: published benchmarks swing from over 90% on simple tests to about 21% on realistic enterprise tasks, so every generated query should be visible and checkable.
What it is
Text-to-SQL lets someone who does not write SQL query a database in plain language. A language model reads your question and your schema (table and column names, types, sometimes sample values) and writes a query, which a system then runs. It does not understand your business; it matches your words to your schema and writes plausible SQL.
The pipeline
- Schema linking — decide which tables and columns the question is about. This is where many failures start: a wrong table or column makes everything after it wrong.
- Query generation — draft SQL in the right dialect (PostgreSQL, MySQL, BigQuery, Snowflake and SQLite differ).
- Validation — check the query is read-only and well-formed, and limit how much it can return.
- Execution and self-correction — run it; if the database returns an error, feed the error back and retry.
- Explanation — summarise the result and show what was done.
Why it fails
- Ambiguity: “revenue” might mean gross, net or recognised revenue, and several columns could fit.
- Large or messy schemas: hundreds of columns, duplicated meanings, abbreviations.
- Hidden filters: the model adds a condition you did not ask for.
- Dialect traps (below).
- Invented content: Thoughtworks' Technology Radar (Hold, November 2025) notes that language models frequently hallucinate when they have limited understanding of the schema or domain, and that non-deterministic output makes errors hard to debug and audit.
Dialect traps we have hit in testing
| Trap | What happens | Fix |
|---|---|---|
| Integer division (SQLite) | 7 / 2 returns 3; averaging gross ÷ pieces on whole-number columns returned 786.98 instead of 787.23 | Store numbers as decimals or multiply by 1.0 before dividing |
| Month labels stored as text | strftime on “Jun-21” returns NULL, so grouping and ordering collapse into one group | Convert the label to a sortable key (2021-06) before ordering |
| Column names with spaces | A bare multi-word column such as math score fails unless it is quoted (backticks in BigQuery and MySQL, double quotes in PostgreSQL and SQLite) | Quote every identifier that contains a space |
| Filtering on a window result | HAVING rnk = 1 on a ranking in the same SELECT is an error | Filter one level up: SELECT * FROM (...) WHERE rnk = 1 |
How accuracy is measured — and why scores differ so much
Published benchmarks show a wide spread. The Spider 2.0 paper (arXiv, submitted November 2024, revised March 2025) evaluates 632 real-world enterprise text-to-SQL workflow problems and reports that its code-agent framework built on o1-preview solved 21.3% of tasks, compared with 91.2% on Spider 1.0 and 73.0% on BIRD.
Those numbers use different benchmarks and set-ups and are not directly comparable, and models have improved since the paper. The lesson is about difficulty: realistic enterprise schemas — wide tables, several SQL dialects, multi-step workflows — are far harder than classroom examples. A tool's demo on a clean sample table tells you little about your database. Test on your own data.
Using it safely
- Connect with a read-only database user, preferably to a read replica.
- Require the tool to show the SQL, and have someone who can read SQL review the queries that matter. Thoughtworks recommends human review of all generated queries.
- Refuse anything that writes or alters data.
- Check filters, row counts and missing-value handling for every number you will repeat.
- Prefer tools that say “I can't answer that from your data” over tools that always answer.
- For governed company-wide metrics, define them once (a semantic layer or a metrics list) so the model reuses the same formula.
Three kinds of tool
| Kind | Example approach | Trade-off |
|---|---|---|
| Framework you run yourself | Open-source text-to-SQL libraries | Maximum control; you own testing and security |
| SQL editor with AI help | Drafts SQL for people who can judge it | Assumes SQL skill |
| Hosted AI analyst | Answers, dashboards and alerts for non-SQL users, with the SQL shown (SchemaChat is one) | Less control; check its receipts and refusal behaviour |
A worked example
Question: “Which customers account for the top 10% of revenue?” A sound answer shows a query that totals revenue per customer, ranks customers, and keeps those whose cumulative share is within 10% — and a receipt stating which table, which filters and how many customers were considered. If your table has refunds as negative rows, the answer must say whether they were included. That one sentence is often the difference between a right and a wrong number.