Text-to-SQL Explained: How Natural Language Becomes a Database Query
What trustworthy text-to-SQL requires: how a natural language database query is built from schema context, checked by a read-only validator, and shipped with its evidence.
By the Datarelix Team · Published · Updated · Last verified
Text-to-SQL, also written natural language to SQL, is the process of turning a plain-language question into a database query. In production, trustworthy text-to-SQL is not a single prompt that outputs SQL. It is a governed workflow: the model receives your question plus the structure of your schema and returns a structured plan, a read-only validator checks the generated query before anything runs, a separate execution layer runs it against your database, and the answer arrives with the evidence needed to check it.
The core distinction: an AI SQL generator vs. a governed pipeline
The naive version of text-to-SQL is a single call: send the question and some schema text to a model, get a SQL string back, execute it. That design has two structural problems. First, whatever the model writes is what runs, including a DROP TABLE it hallucinated or a join that scans a billion rows. Second, there is no record connecting the answer to the query, so nobody can check what was actually measured.
A production system separates the concerns. The model’s only job is to propose. Deciding whether the proposal is safe, running it, and recording what happened are jobs for other components that the model cannot influence.
Who this explains the mechanism for
Data leaders evaluating conversational analytics tools and wanting to know what actually happens between the question and the chart. Analytics engineers who will be asked to connect a database and need to reason about what the model is given and what it can do. Anyone who has seen a demo where English becomes SQL and wants the mechanism rather than the magic.
The pipeline, stage by stage
1. Schema context: what the model is given
The model cannot write correct SQL against tables it knows nothing about, so the first stage assembles schema context: table and column names, data types, primary and foreign keys, and any curated descriptions attached to them. This is structure only: no sample rows, no cell values. Connection credentials are not part of this context at any stage; they stay with the execution layer, which is the subject of how credentials stay out of the model.
2. Plan generation: the model proposes, nothing more
Given the question and the schema context, the model returns a structured plan rather than free-form actions. The model itself executes nothing. This matters because a plan is inert: it can be inspected, validated against a strict schema, and rejected before any part of it touches a database.
3. Read-only validation: reads only, enforced before execution
Generated statements are parsed into a syntax tree and rejected unless they are reads — how the validator works. SELECT statements, CTEs, and set operations pass; UPDATE, DELETE, INSERT, DDL, and administrative commands have no path through. Query languages that are not SQL, such as KQL and ES|QL, go through dedicated validators of their own. Automatic row limits apply to every query, so an unbounded question cannot return an unbounded result.
4. Execution: an isolated query service holds the connection
The validated query runs in an isolated query service, the only component that holds the database connection. The credential is decrypted only at execution and never enters the model’s context — the credential boundary.
5. Revise and retry, within strict limits
Real schemas produce real errors: a misspelled column, a type mismatch, a dialect quirk. When a query fails, the database error goes back to the model, which revises the query and retries automatically, within strict limits, each one passing through the same read-only validation as the first.
6. Interpretation and evidence
The result comes back carrying the query that ran, the tables it read, the row count, and the runtime — what each of the four tells you. Result tables are not passed back to the model. One exception: a single-row answer value can carry into follow-up context, so “and how does that compare to last year?” resolves — what enters context.
Why the same question can produce different SQL
Natural language is underspecified, and the model must commit to one reading. Both readings below are defensible SQL — which is exactly why the system must show you the query it chose.
| Question | Reading A | Reading B |
|---|---|---|
| ”Top customers” | Ranked by revenue: SUM(quantity * unit_price) | Ranked by volume: COUNT(DISTINCT order_id) |
| ”Last quarter” | Previous calendar quarter: Apr 1 – Jun 30 | Trailing 90 days from today |
| ”Active users” | Signed in recently: last_login_at >= … | Purchased recently: EXISTS (SELECT … FROM orders …) |
| ”Revenue” | Gross: sum of all completed order items | Net: gross minus refunded items |
None of these readings is wrong; they measure different things. A system that hides its SQL forces you to guess which one you got. A system that shows it lets you confirm the reading in one glance — the habit described in how to verify AI data answers.
From a plain-English question to SQL: a worked example
The examples below use a fictional retailer’s demo schema — customers, orders, order_items — and are illustrative, not output from a live system.
Question: “Who were our top 10 customers by revenue in Q2 2026?”
On PostgreSQL, a reasonable generated query looks like this:
SELECT
c.customer_id,
c.name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
AND o.ordered_at >= DATE '2026-04-01'
AND o.ordered_at < DATE '2026-07-01'
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC
LIMIT 10;
The same question against BigQuery produces a structurally identical query with dialect differences:
SELECT
c.customer_id,
c.name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM `demo_retail.customers` AS c
JOIN `demo_retail.orders` AS o ON o.customer_id = c.customer_id
JOIN `demo_retail.order_items` AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
AND DATE(o.ordered_at) >= '2026-04-01'
AND DATE(o.ordered_at) < '2026-07-01'
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC
LIMIT 10;
The differences are small but breaking: BigQuery qualifies tables with a dataset and backticks, timestamps often need an explicit DATE() conversion, and date functions diverge — PostgreSQL writes date_trunc('quarter', ordered_at) while BigQuery writes DATE_TRUNC(ordered_at, QUARTER) with the arguments reversed. A production text-to-SQL system has to generate for the specific dialect it is connected to, and its validator has to understand that dialect too. Support for an engine has to mean a dialect-specific generator and a dialect-aware validator.
Semantic context: why curated descriptions change results
Schema structure tells the model what exists; it does not say what things mean. A column named status could hold order states, payment states, or fulfillment states. A table named orders_v2 might be the live table or an abandoned migration. Curated descriptions close that gap: a one-line note like “status: order lifecycle — ‘completed’ means paid and shipped; use for revenue” changes which filter the model writes. The curation method — which columns to describe first, and how to keep descriptions true as the schema changes — is in why semantic context matters.
How to evaluate a text-to-SQL tool
Accuracy leaderboards measure benchmark schemas, not yours. Evaluate the workflow qualitatively:
| Dimension | What good looks like |
|---|---|
| Shows the query | The exact executed SQL is visible on every answer, not on request |
| Validates before executing | Generated statements are parsed and checked before they run, with mutating statements rejected |
| Read-only by design | The read guarantee is enforced in a validation layer, not requested in a prompt |
| Exposes sources and runtime | Source tables, row count, and runtime accompany each result |
| Handles ambiguity honestly | Ambiguous questions yield an inspectable interpretation, so you can see which reading was chosen |
| Behavior on error | Failed queries are revised and retried within strict limits, with every retry re-validated |
Where the pipeline fails
An ambiguous question runs as one specific reading, and if you do not check the query, you can carry away a number that measures something adjacent to what you meant. Answer quality tracks schema quality: cryptic column names and missing descriptions degrade results in any system. Dialect coverage is per-engine, so a system strong on PostgreSQL is not automatically strong on Kusto. And some jobs are the wrong shape for it entirely. Writing data, running migrations, and administering a database are excluded by design. Multi-step modeling work with heavy business-logic transformations still belongs in your transformation layer, with text-to-SQL querying its outputs.
Notes on the examples
Product statements describe Datarelix as shipped in August 2026; the security page is canonical. All SQL examples use a fictional retailer’s demo schema (customers, orders, order_items) and are illustrative rather than output captured from a live system. The two dialect variants are written to show where generators diverge, not to rank the engines.
Try it on your own schema
Connect a read-only database user with the connection guides, ask one question you already know the answer to, and read the query that ran. If it matches the reading you intended, you have tested the part of the pipeline that matters most.