Text-to-SQL

Turning a question in plain English into a SQL query that runs against a database and returns the answer.

DefinitionTurning a question in plain English into a SQL query that runs against a database and returns the answer.

Text-to-SQL is a technique in which a language model reads a question such as "revenue by region last quarter," looks at the database schema and writes the SQL query that answers it. The database does the arithmetic, which is its strength. The weakness is that a query can run cleanly and still answer a different question from the one asked.

How it is computed

The question and a description of the tables and columns go to the language model, which returns a query. The query runs, and the result is checked before it is shown: row counts, totals against a known control figure, and the filters actually applied.

Example

Orders for the quarter total $600,000. A generated query joins orders to a contacts table in which every account has two contacts, so each order appears twice and the query returns $1,200,000. A control-total check against $600,000 catches it.

Where it goes wrong

Fan-out joins like that one, wrong date columns, and ambiguous business terms such as "active customer." The full guide is text-to-SQL.

Articles that use this term

AI and self-service analytics

AI data analysis: what AI can do with your numbers, and what it should never do

What AI data analysis is, split into the part the AI model does well (reading the question, choosing the method, explaining the result) and the part it must leave to deterministic tools (the arithmetic). A worked gross-margin fall split into mix and rate shows the split in practice, with the checks that make an answer trustworthy and a five-question test for any tool.

Oct 1, 20266 min read AI and self-service analytics

AI data analyst: what it does, where it stops, and what it cannot replace

What an AI data analyst tool actually does with a question, worked through on a CRM export to find which region's win rate fell most. It covers the checks that prove the answer, where such a tool has to stop, how it compares with a human analyst and a BI tool, what the BLS outlook says about analyst jobs, and five tests for choosing one.

Oct 1, 20266 min read How-to guides

OLAP explained: cubes, slicing and dicing, and what replaced them

OLAP (online analytical processing) summarizes transaction data across several dimensions at once. This page explains cubes, dimensions and measures, works the five OLAP operations on an 8-row sales cube, shows the same thing in SQL with GROUP BY ROLLUP and CUBE, and covers MOLAP, ROLAP and HOLAP and the columnar and semantic-layer tools that now do the job.

Oct 1, 20267 min read How-to guides · Finance and FP&A teams

SQL for data analysis: 12 queries analysts use, on one sales table

SQL for data analysis in twelve numbered queries, all run on the same eight-invoice sales table: filtering, grouping, HAVING, joins, finding gaps with a LEFT JOIN, CASE bands, month truncation, running totals, share of total, month-over-month change with LAG, deduplication with ROW_NUMBER and a CTE that chains the steps. Every result is shown, with the check that each summary sums back to the table.

Oct 1, 20268 min read How-to guides · Finance and FP&A teams

SQL PIVOT: turn rows into columns, with SQL Server and portable examples

How to turn rows into columns in SQL: the SQL Server PIVOT operator, the portable conditional-aggregation version that runs in PostgreSQL, MySQL and SQLite, dynamic PIVOT for column values you do not know in advance, and UNPIVOT to go back. Every query runs on one ten-row sales table, with the result and the control-total check.

Oct 1, 20266 min read AI and self-service analytics

Text-to-SQL explained for business users: how it works and where it goes wrong

What text-to-SQL is, how accurate it is on the Spider and BIRD benchmarks, and a worked query on two tables, revenue by region for customers with more than 10 orders, with the check that proves it. Then the same question going wrong three ways, the alternative of mapping questions to tested calculations, and when text-to-SQL is the right tool.

Oct 1, 20266 min read