Text-to-SQL:
what it is and where it fails in production

Text-to-SQL turns a question into a database query

Text-to-SQL is the technique in which a language model receives a question in natural language and returns a SQL query that, once run, brings back the answer. The problem has been studied for decades in natural language to SQL research. In 2018, the Spider benchmark gathered human-annotated questions over databases from many domains and became the reference for measuring how well models generate queries on schemas they have never seen.

With LLMs, the most common pipeline has four steps:

1. the table schema, with column names and types, goes into the prompt;
2. examples of questions and their correct queries are added to guide the pattern;
3. the model generates the query, which is run on the database;
4. the result goes back to the model, which presents it as text, a table or a chart.

The technique works well for analysts on well-modelled databases

The favourable scenario has two conditions. The database has tables and columns with clear names, documented keys and few implicit rules. The person asking can read the generated query and recognises an odd result.

An analyst who needs an ad hoc cut of a well-designed data mart saves real time. The query comes out in seconds, she reviews the join, adjusts a filter and moves on. In this use, text-to-SQL speeds up people who already know SQL.

In production, text-to-SQL fails on queries that run without error

The scenario changes when the question comes from someone who does not read SQL and the database reflects years of operation. The most frequent errors do not produce an error message. They return a number:

• Joins across different databases: the sales target lives in the planning database and the sale in the ERP, and the model joins the two on the salesperson code, which changed format in the middle of the year.
• Legacy column names: the net amount sits in a field called VL_AUX2, created in an old migration, and the model picks the VL_TOTAL field, which looks more suitable.
• Ambiguous period: September sales can be counted by order date, invoice date or payment date, and each choice returns a different number.
• Granularity: the targets table is monthly by region and the sales table is daily by salesperson, and comparing them directly multiplies or dilutes values.
• Double counting in a one-to-many join: when orders are joined with instalments, each order's amount repeats for every instalment, and the sum inflates silently.
• Heavy queries: an open question becomes a full scan of the events table and slows down the transactional database at peak hours.

In these cases, the rule that decides the right answer lies outside the schema. It lives in the finance team's habits or in a reconciliation spreadsheet. Text-to-SQL picks the most likely interpretation, the query looks right to anyone who does not know the operation, and the number goes into the report with no warning.

The generated query can read beyond the asker's permissions

Many text-to-SQL pilots connect the model to the database with a broad-access service credential. A query generated for one salesperson can then read another's accounts or the commissions table, because the database sees the service credential and ignores the person.

There is also the risk of injection. Text inserted into the question, or stored in a data field that goes into the prompt, can lead the model to generate write commands or queries against sensitive tables. A read-only user, an allowlist of tables and an execution time limit reduce the damage. Per-person permissions remain unsolved in this design.

The schema in the prompt makes every call more expensive

To get it right, the model needs the schema, examples and notes on columns. In an ERP with hundreds of tables, this material takes up a large part of the prompt and is sent again with every question. Table selection techniques reduce the volume and add a step that can also go wrong.

The cost per answer grows with the number of systems included. In an attempt to reduce errors, teams move to larger models, and the cost grows again.

In the Company Brain, text-to-SQL works on an approved vocabulary

The Company Brain semantic layer records metrics and entities with a definition, an owning team and named sources. The business ontology defines which date counts for the month's sales and which field is the net amount. The knowledge graph resolves how the regional target connects to the salesperson's sale.

The model receives this vocabulary via MCP, instead of the raw schema. The generated SQL is restricted to the approved metrics and entities, and the query runs with the permissions inherited from the source system. The prompt shrinks, the task fits a lower-cost model and every result points to the definition and source it used.

Reliable text-to-SQL starts with questions whose answers have been checked

The semantic layer covers what has been defined. A question outside the approved vocabulary has to be declined or passed to a person, and the definitions need maintenance by the teams that own the data. For analysts' exploratory questions, free text-to-SQL over the schema is still useful, as long as the query is reviewed before the number circulates.

The first practical step is to pick out the questions that reach the data team every week, record the correct answer to each one and turn the metrics involved into the first definitions. That set then measures every change of model, prompt or source.