Text-to-SQL

Also known as: NL2SQL · natural language to SQL

On this page 8

Text-to-SQL is the task of translating a natural language question into an executable SQL query that answers it against a specific relational database. Also called NL2SQL or natural language to SQL, it is one of the most commercially important AI applications, because it lets analysts, managers and support staff query structured data without writing code, while the database engine still produces exact, auditable results.

In artificial intelligence, the field moved from hand-written grammars and template systems to neural sequence models and, more recently, to large language models prompted with the database schema. Modern systems rarely fail on SQL syntax; they fail on meaning: choosing the right table, the right join path and the right business definition of a metric.

How a Text-to-SQL pipeline works

A Text-to-SQL pipeline works by grounding the question in the schema, generating a candidate query, validating it and then executing it under restricted permissions. The language model never reads the full database; it sees a compact description of tables, columns, keys and sample values, and it reasons over that description.

Production systems add several stages around the model call. Schema linking maps words in the question to columns and values, so that “revenue last quarter” becomes a filter on an order date and a sum over a net amount column. Self-correction feeds database error messages back to the model, and execution-guided decoding discards candidates that return errors or empty results when a result is clearly expected.

The following table lists the typical stages and the failure each stage is designed to prevent.

StageWhat it doesFailure it prevents
Schema retrievalSelects relevant tables and columnsContext overflow on large schemas
Schema linkingMaps question words to columns and valuesWrong column or misspelled value
Query generationProduces one or more SQL candidatesMissing logic in a single attempt
Static validationParses SQL and checks dialect and permissionsSyntax errors and forbidden statements
Execution and repairRuns the query and retries on errorRuntime errors and type mismatches
Answer renderingShows the table, chart or summaryMisreading raw output
Stages of a production pipeline

Approaches and model architectures

Text-to-SQL approaches differ mainly in how much structure the system imposes on the model. Early research systems parsed questions with grammars; benchmark-era neural models such as sequence-to-sequence parsers learned from paired questions and queries; current systems mostly rely on in-context learning with general-purpose language models, sometimes combined with fine-tuning on a company’s own query logs.

The table below compares the main families and where each still makes sense.

ApproachStrengthMain limitation
Rule and template systemsPredictable output for known questionsBreaks on unseen phrasing
Fine-tuned seq2seq modelsFast and cheap at inferenceNeeds thousands of labeled pairs
Prompted large language modelsHandles varied phrasing and new schemasHallucinated columns and joins
Agentic multi-step systemsExplores data and repairs its own queriesHigher latency and cost per question
Semantic layer generationUses governed metric definitionsLimited to modeled metrics
Families of Text-to-SQL systems

A semantic layer deserves special attention. Instead of generating raw SQL over physical tables, the model selects governed metrics and dimensions, and the layer compiles the SQL. This sharply reduces ambiguity about definitions such as “active customer”, at the cost of covering only what data teams have modeled.

How accuracy is measured

Accuracy in Text-to-SQL is measured mainly by execution accuracy, the share of questions whose generated query returns the same result as a reference query. Exact match, which compares SQL text, underestimates quality, because many different queries are semantically equivalent.

Public benchmarks such as Spider and BIRD drove progress, and leading systems report high scores on them. Those numbers transfer poorly to enterprise warehouses, which have hundreds of tables, cryptic column names, undocumented business rules and dirty values. Teams should therefore build an internal evaluation set of real questions with verified answers and track it over time.

MeasureWhat it checksCaveat
Execution accuracyResult sets match the referenceCan pass by coincidence on small data
Exact matchSQL text matches the referencePenalizes valid alternative queries
Valid SQL rateQuery parses and runsSays nothing about correctness
Human acceptance rateUsers accept the answerUsers may not notice subtle errors
Common evaluation measures

Text-to-SQL is narrower than AI data analysis, which also cleans data, builds charts and writes Python, and it differs from retrieval-augmented question answering, which searches unstructured documents. Its distinctive property is that the final answer is computed by a deterministic database engine, so the result can be inspected and reproduced.

It is also close to AI code generation, since the output is code. The practical difference is that SQL is read-only in most deployments and is judged by its result, whereas application code is judged by tests, maintainability and integration.

Deployment practices and security controls

Safe deployment of Text-to-SQL depends on least-privilege database access rather than on instructions in the prompt. A model can be manipulated through prompt injection, including text stored in the data itself, so the database role must make harmful statements impossible regardless of what the model generates.

The decision table below summarizes recommended controls for common situations.

SituationRecommended controlReason
Self-service analyticsRead-only role on curated viewsBlocks writes and hides raw tables
Customer-facing chatRow-level security per tenantPrevents cross-customer data exposure
Large warehouseQuery cost and row limitsStops runaway scans and bills
Regulated dataColumn masking and audit loggingSupports privacy and compliance reviews
Business reportingShow generated SQL to the userEnables review of the logic
Recommended controls by situation

Good systems also ask clarifying questions when the request is ambiguous, for example when “sales” could mean gross bookings or recognized revenue, rather than silently choosing one interpretation.

Limitations and common failure modes

The central limitation of Text-to-SQL is that a query can run successfully and still be wrong. Silent errors are more dangerous than syntax errors, because the user receives a confident number with no warning.

  • Join fan-out: joining a one-to-many table before summing inflates totals.
  • Wrong business definition: the model counts all accounts instead of paying accounts.
  • Date and time zone errors: fiscal quarters and UTC timestamps are misread.
  • NULL handling: averages and counts change when missing values are ignored.
  • Stale schema context: renamed columns produce errors or wrong fallbacks.

Mitigation combines curated documentation of tables and metrics, verified example queries in the prompt, result sanity checks and a clear path for users to report wrong answers.

Explained without jargon

Company data usually sits in databases that only understand a precise query language called SQL. Text-to-SQL lets a person type an ordinary question, such as “How many orders did we ship to Canada last month?”, and an AI writes the database query for them.

The database then does the actual counting, so the numbers are exact once the query is right. The catch is that the AI can misunderstand what you meant. The key takeaway is to check the logic, especially for important numbers, just as you would double-check a new colleague’s first report.

A typical use case

A regional retail chain gives its store operations team a chat tool connected to a read-only copy of its sales warehouse. The data team documents 40 curated views, defines “net sales” and “same-store growth” in a semantic layer and adds 150 verified question and query pairs as examples.

A district manager asks which stores had falling weekend sales over the last eight weeks. The system generates SQL with a weekend filter and a weekly comparison, shows the query and returns a ranked table in a few seconds. Requests that previously waited two or three days in the analyst queue are answered immediately, and analysts shift to reviewing flagged answers and extending the documented metrics.