Text-to-SQL

Overview

Text-to-SQL is the task of turning a question written in everyday language into an SQL query that a database can run. A user types "How many new customers did we get in each region last quarter?" and the system produces a query that joins the right tables, filters the right dates and groups by region. The database runs the query, and the system shows the result, often with a short explanation or a chart.

The appeal is obvious. Most people who need numbers cannot write SQL, and data teams spend a lot of time answering simple requests. Large language models are good at writing code, and SQL is a well-defined language, so they can do much of this translation. The hard part is not the SQL grammar. It is knowing which tables and columns the question refers to, what business terms mean, and how to stop wrong or unsafe queries from reaching users.

Imagine asking a librarian in a huge archive for "last year's sales letters from the northern office". A good librarian knows which room holds them, which shelf, what "northern" means in this archive, and that "last year" means the financial year. A text-to-SQL system must be that librarian for your database: the language part is easy, the local knowledge is what makes it right.

What it is

Text-to-SQL can be a single model call or an agent that explores the schema, runs exploratory queries, inspects errors and retries.

Where it came from

Natural-language interfaces to databases have been studied for decades, with research systems dating back to the 1970s. These early systems relied on hand-built grammars for one database and did not generalise.

Machine learning brought shared benchmarks. In 2017 Salesforce researchers released WikiSQL with their Seq2SQL model, a large dataset of questions over single tables. In 2018 a Yale team released Spider, a harder cross-domain benchmark with complex queries over many different multi-table databases, so models had to handle schemas they had never seen. Spider became the main yardstick for several years, with models built on sequence-to-sequence and then pre-trained transformers.

Large language models changed the field again from about 2022, when prompting general models with the schema began to rival specialised systems. Newer benchmarks raised the bar to match real use. BIRD, published in 2023, added large, messy databases and the need for external knowledge. Spider 2.0, released in November 2024, focused on enterprise workflows with very large schemas, multiple SQL dialects and multi-step tasks. Results on these harder benchmarks are far below those on the original Spider, which shows how much real-world text-to-SQL depends on context.

How it works

Step 1: Understand the question

The system may first clarify or rewrite the question, resolving relative dates ("last quarter") and spotting ambiguity. If the question is unclear, a good system asks rather than guesses.

Step 2: Link the schema

Schema linking finds the tables and columns that matter. For small databases the whole schema fits in the prompt. For large ones the system retrieves relevant tables using names, descriptions and embeddings, much like retrieval in RAG. Good table and column descriptions, often maintained in a data catalog, make this far easier.

Step 3: Add business knowledge

The prompt includes definitions such as "revenue excludes refunds and tax" and a few example question and SQL pairs. Many teams expose a semantic layer, for example metrics defined in dbt, so the model picks a defined metric instead of inventing a formula.

Step 4: Generate SQL

The model writes a query in the right dialect, such as PostgreSQL, BigQuery or Snowflake. Asking for a short plan first, or for structured output with the SQL in one field, improves reliability.

Step 5: Validate before running

A parser checks syntax and confirms the query is read-only. Rules can block full-table scans, missing date filters or access to restricted tables. A short example using the open-source sqlglot library:

import sqlglot
from sqlglot import exp

tree = sqlglot.parse_one(generated_sql, read="postgres")
if not isinstance(tree, exp.Select):
    raise ValueError("Only SELECT queries are allowed")

Step 6: Execute safely and repair

The query runs under a read-only role with row limits and a timeout. If it fails, the error message goes back to the model for a corrected attempt, usually with a cap of two or three retries.

Step 7: Explain the answer

The system returns the result with the SQL and a plain description of what was counted and filtered, so the user can spot misunderstandings.

Key concepts and terms

A worked example

An illustrative online retailer has a warehouse with 300 tables. A sales manager asks: "What was average order value by country in September, excluding cancelled orders?"

1. Retrieval finds four candidate tables: orders, order_items, customers and countries. The descriptions note that orders has a status column and that revenue is stored in order_items.
2. The business glossary says average order value means total item revenue, excluding tax and shipping, divided by the number of orders.
3. Two golden queries show how the team usually joins orders to countries through customers.
4. The model writes a query that joins the four tables, filters order date to 1 to 30 September, excludes status = 'cancelled', sums revenue per order, then averages by country.
5. The validator confirms it is a single SELECT with a date filter. It runs in 1.2 seconds and returns 18 rows.
6. The answer shows the table, the SQL and a note: "Averages use item revenue only, excluding tax and shipping, for 4,912 non-cancelled orders."

On the team's test set of 150 real questions with verified answers, this setup reaches about 80 per cent execution accuracy in the example. Questions that use an undefined term are routed to the data team, and their answers become new golden queries.

Strengths and limitations

Strengths:

• Lets non-technical staff ask data questions directly.
• Frees analysts from routine requests.
• Shows the generated SQL, so answers can be audited.
• Improves steadily as definitions and golden queries are added.
• Works with any SQL database the model knows.

Limitations:

• Wrong queries can return plausible but incorrect numbers.
• Large, poorly documented schemas cause wrong table and column choices.
• Business terms are often ambiguous without a semantic layer.
• Complex logic such as cohorts or window functions is error-prone.
• Security needs care: permissions, row-level access and query cost.

How it compares with alternatives

Practical guidance and best practices

1. Start with a small, well-documented set of tables, not the whole warehouse.
2. Write clear table and column descriptions and keep them current.
3. Define key metrics once, ideally in a semantic layer, and point the model at them.
4. Build a test set of real questions with verified results and measure execution accuracy; see evals.
5. Run queries under a read-only role with timeouts, row limits and cost limits.
6. Always show the SQL and a plain-language description of what was computed.
7. Let the system ask clarifying questions when terms or time ranges are ambiguous.
8. Turn corrected answers into golden examples to improve future results.
9. Apply guardrails for restricted tables and personal data.

Common mistakes

• Dumping a huge schema into the prompt and hoping the model finds the right tables.
• Measuring success by whether the query runs, rather than whether the answer is right.
• Giving the model a database role that can modify or delete data.
• Leaving business terms undefined, so each query invents its own formula.
• Hiding the SQL from users, which removes the main way to catch errors.
• Ignoring dialect differences, especially in date and string functions.

Frequently asked questions

How accurate is text-to-SQL?

It varies widely. On simple, well-documented schemas accuracy can be high. On large, messy enterprise databases it drops sharply, as harder benchmarks such as BIRD and Spider 2.0 show. Measure on your own questions.

Is it safe to let a model query production databases?

Only with strict controls: read-only access, row and time limits, restricted tables, and preferably a replica or warehouse rather than the live transactional database.

Do I need to fine-tune a model?

Usually not at first. Good schema context, definitions and examples with a strong general model often work well. Fine-tuning can help for a fixed schema with many examples, or to use a smaller, cheaper model.

What is a semantic layer and why does it help?

It is a central set of definitions for metrics, such as revenue or churn, and how to compute them. When the model uses those definitions instead of writing formulas from scratch, answers become consistent and easier to trust.

How do I handle questions the system cannot answer?

Detect low confidence, ambiguous terms or missing data, and say so. Offer to route the question to an analyst, and add the answer as a new example afterwards.

Can text-to-SQL work with tools like DuckDB?

Yes. Any SQL engine works, including local ones such as DuckDB. Tell the model the exact dialect to use.

Glossary

• SQL: Structured Query Language, used to query relational databases.
• Schema: the structure of a database: tables, columns and keys.
• Join: combining rows from two tables on a shared key.
• Aggregation: summarising rows, for example with SUM or AVG.
• Data warehouse: a database built for analytics.
• Replica: a copy of a database used to spread read load.
• Metric: an agreed business measure with a fixed formula.
• Parser: software that checks and structures code.

Summary

Text-to-SQL turns plain-language questions into SQL queries so that people without SQL skills can get answers from databases. It has evolved from hand-built grammars through benchmarks such as WikiSQL and Spider to large language models evaluated on harder, enterprise-style benchmarks like BIRD and Spider 2.0. The language model writes the SQL, but reliability comes from the surrounding system: focused schema context, clear business definitions, golden examples, validation, read-only execution and transparent answers that show the query.

Related

It belongs to LLM concepts in my stack, under Generative AI and LLMs. Others in the same group: