What Is Text2SQL? Definition, How It Works, and Its Limits

Text2SQL — also written text-to-SQL or NL2SQL — is the technology that converts a question written in plain language into an executable SQL query. Ask "what was total revenue for Product A in Q3 2023?" and a text2sql system identifies what is being measured, maps those terms to real tables and columns, writes the SQL, and runs it against the database. The user never sees a join condition unless they want to.

Modern text2sql is powered by large language models, but the model is rarely what determines whether it works. Accuracy is set by how much the system knows about your specific schema and business rules — which table is canonical, which rows are test data, and what your company means by "revenue". This article explains the mechanics, the failure modes, and how accuracy is actually measured. If you are ready to compare products rather than understand the technology, see the best text-to-SQL query tools.

A Worked Example

The question: "What was total revenue for Product A in Q3 2023?"

A reasonable generated query:

SELECT SUM(revenue) FROM sales WHERE product_name = 'Product A' AND sale_date >= '2023-07-01' AND sale_date < '2023-10-01';

Simple enough — but look at how many decisions were made silently. The system chose the sales table over sales_staging or orders. It used revenue rather than gross_amount. It treated Q3 as calendar rather than fiscal. It matched the product by name string rather than by ID. It did not exclude refunds, internal test orders, or cancelled lines. Every one of those is a place where a plausible query returns a wrong number, and none of them are visible in the answer.

That is the whole problem of text2sql in one query. Generating syntax is solved. Generating the right query for your business is a context problem.

How Text2SQL Works

1. Intent and Entity Parsing

The system decomposes the question into a measure (revenue), filters (Product A, Q3 2023), grouping (none here), and aggregation (sum). This step is largely handled by the language model.

2. Schema Linking

Entities are mapped to actual database objects: tables, columns, join paths, data types. This is where most errors originate. A warehouse with hundreds of tables and near-duplicate names gives the model many wrong options that look right. Systems typically feed the model a schema summary, sample values, and — in better implementations — curated documentation of which objects are canonical. The technical detail is covered in semantic parsing for text-to-SQL.

3. Query Generation

SQL is written in the dialect of the target engine. Snowflake, BigQuery, Postgres and SQL Server differ meaningfully on date functions, string handling, and window syntax, so dialect awareness is not optional.

4. Validation and Execution

Better systems parse the query before running it, check that referenced objects exist, and apply limits to avoid accidentally scanning a trillion rows. Execution should happen through read-only credentials.

5. Repair and Follow-Up

If the query errors, the system feeds the error back and regenerates. If the user asks a follow-up — "now split that by region" — it must retain enough state to modify the previous query rather than starting over.

Text2SQL and Its Neighbours

Why It Is Harder Than It Looks

Schema Sprawl

Real warehouses contain deprecated tables, staging copies, and three generations of the same entity. Without guidance about which is authoritative, the model picks by name similarity — and orders_v2 often looks newer than the table your company actually uses.

Business Ambiguity

"How many customers do we have?" has several defensible answers depending on trials, churned accounts, and multi-seat organisations. A system with no stored definition picks one silently. A well-designed one uses your approved definition or asks a clarifying question.

Time Handling

Fiscal versus calendar quarters, time zones on event timestamps, and month boundaries produce errors that nobody notices for weeks because the numbers look reasonable.

Hidden Filters

Nearly every production database contains internal accounts, seeded demo data, and QA orders. Every trusted metric excludes them. A text2sql system that does not know the filter will overstate every figure it produces.

Joins and Grain

Joining an orders table to a line-items table without collapsing to the right grain double-counts revenue. This is the single most common source of quietly wrong answers, and it is invisible unless someone reads the query.

How Text2SQL Accuracy Is Measured

Academic benchmarks such as Spider and BIRD evaluate models against curated schemas and are useful for comparing model capability. They are not a good predictor of performance on your warehouse, because their schemas are documented and clean and yours is not.

Two measures matter in practice:

• Execution accuracy: does the query return the correct result set? Two syntactically different queries can both be right, so comparing SQL text is misleading.

• Appropriate refusal: when a question is ambiguous or unanswerable from the data, does the system say so instead of producing a plausible number?

To evaluate on your own data, collect 30 to 50 real questions from your BI request queue, have an analyst write the correct answer for each, and score on result match. Include underspecified questions deliberately, and re-run after a schema change to see whether the system degrades silently. A fuller methodology is in metrics to test text-to-SQL accuracy.

Where Text2SQL Is Used

• Self-serve analytics: business teams answering their own questions instead of filing requests with the data team.

• Analyst acceleration: generating a first-draft query against an unfamiliar schema, then editing it.

• Conversational interfaces: Slack and Teams bots that answer questions in the channel where they were asked.

• AI assistant access: letting Claude or another assistant query governed company data through MCP rather than working from pasted screenshots.

• Embedded product analytics: letting your customers ask questions of their own data inside your application.

Large data-platform teams have published their own internal text-to-SQL assistants, and the recurring lesson in those write-ups is the same one above: the engineering effort goes into schema context, evaluation, and guardrails, not into the model call. Building one yourself is entirely feasible — see the LangChain SQL agent walkthrough — but the maintenance is the real cost, because someone owns keeping the schema context in sync forever.

What Makes Text2SQL Production-Grade

A Context Layer That Persists

The difference between a demo and a system people trust is whether business knowledge accumulates. Querio stores joins, metric definitions, and trusted queries as plain SQL, Markdown, and Python files synced to GitHub, in the same repository as your dbt project. The agent proposes what it learns — "save this as a reusable definition?" — and only logged-in humans approve and commit it. Because the context is files, it stays usable by Claude Code or any other agent, with or without Querio. That portability matters: many platforms keep this layer inside their own system, which makes it the hardest asset to take with you. Background: what a governed context layer is.

Inspectable Output

A number without a visible query cannot be reviewed. Querio returns every answer as real SQL and Python in a reactive notebook where cells are connected and charts update automatically when the SQL changes, so a reviewer can correct one line rather than rerunning an entire analysis.

Refusal Over Invention

The costliest failure is a confident wrong answer, because it forces someone senior to check everything. Querio answers only from what is actually in the data and says so when the data is not there.

Permissions and Live Connections

Queries should run through encrypted, read-only credentials against live data — no extracts, no duplicated copies. Querio connects to Snowflake, Google BigQuery, Amazon Redshift, ClickHouse, MotherDuck, PostgreSQL, MySQL, MariaDB, Microsoft SQL Server, and MongoDB, supports role-based access and SSO, and uses OAuth over MCP so agent queries inherit each user's data permissions. It is SOC 2 Type II certified, runs annual third-party penetration tests, supports HIPAA workloads and signs BAAs.

FAQs

Is text2sql accurate enough to rely on?

For well-defined questions against a documented schema with stored business definitions, yes — and the generated SQL can be reviewed when it matters. For arbitrary questions against an undocumented warehouse, no. The variable is context, not the model, which is why benchmarking on your own schema is the only meaningful test.

Does text2sql replace analysts?

It replaces the part of the job that consists of translating a colleague's question into a query someone already wrote last month. It does not replace deciding which question matters, designing the data model, or judging whether a result is plausible. In most teams the queue shrinks and the work moves upstream.

What is the difference between text2sql and a semantic layer?

Text2sql generates the query; a semantic layer supplies the definitions the query should honour. They are complementary. Text2sql without governed definitions produces fast, inconsistent answers; definitions without a generation layer leave you back at filing requests.

Can text2sql handle multiple tables and complex joins?

Yes, when the join paths are documented. The risk is not that it cannot write a join — it is that it writes a defensible-looking join at the wrong grain and double-counts. Documented join logic in a context layer, plus visible SQL, is what makes this safe.

How does it keep up when the schema changes?

Systems that rediscover the schema each session degrade quietly. Systems with maintained context surface the mismatch. Keeping context files in the same repository as your transformations means a schema change and its definition update travel together in a single pull request.

Watch

Sources and further reading

• [4]

• [2]

• [6]

• [1]

• [5]

• [7]

• SherloQ

• [8]

• [10]

• [9]

• [11]

• [3]

• [14]

• [13]

• [12]

• [15]

• AtScale

• [16]

Related reading

• Text-to-Dashboard AI Tool: How It Works and What to Look For