Blog /
Text to SQL is not the hard part
Published . Updated . Written for nlqueries-core 0.3.
text-to-sql trust validation
Ask any current frontier model to write a query against a schema you paste into the prompt, and it will do a decent job. Ask it the same question against a warehouse with four hundred tables, six years of accumulated naming decisions, and three different notions of what a customer is, and the failure is not that it writes bad SQL. The failure is that it writes plausible SQL, confidently, and nobody can tell.
This is the gap we built NLQueries around, so it is worth being precise about where it actually is.
The model is not the bottleneck
Text-to-SQL as an academic task is roughly this: given a natural language question and a database schema, produce a query that returns the right rows. Framed that way, the last few years of model progress have been dramatic, and the published benchmarks reflect it.
What the benchmarks mostly do not capture is the part of the problem that is specific to your organisation. Benchmark schemas are small, cleanly named, and documented. They were designed to be understood from the schema alone. Real warehouses were not designed at all; they accreted.
So when a text-to-SQL system underperforms in production after demoing well, the diagnosis is usually not "the model is not smart enough". It is one of these:
- The question was ambiguous and the system picked a reading. "How many active users last month" has at least four defensible answers in most companies, and no way to choose between them from the schema.
- The join was wrong in a way that still returns rows. A fan-out through a one-to-many relationship inflates a sum. Nobody notices, because the number is large and the direction is right.
- The filter that everyone applies by convention was omitted. Every analyst on the team knows to exclude internal test accounts. That knowledge is in a wiki page, or in nobody's head at all.
- The right table was not the obvious one. There are three tables with
orderin the name. Two are deprecated. The schema does not say so.
None of these are generation failures. They are all knowledge failures, and you cannot prompt your way out of a missing fact.
Correctness you cannot check is not correctness
There is a second problem underneath the first, and it is the one that decides whether a system gets used at all.
When an analyst writes a query by hand and gets a surprising number, they do something specific: they read their own query again. They check the join. They check the date boundary. They run a count. The whole practice of analysis is built on the assumption that the person who produced the number can interrogate how it was produced.
A system that returns "14,207" and nothing else removes that entirely. It does not matter that it is right most of the time. The person who has to present the number in a meeting has no way to earn confidence in it, so either they do not use the system, or they use it and stop checking. Both outcomes are bad, and the second one is worse.
This is why NLQueries always returns the SQL alongside the answer, and why we treat that as a correctness feature rather than a transparency gesture. A wrong query that is visible gets caught in about ten seconds by anyone who knows the data. A wrong query that is invisible becomes a number in a board deck.
What the work actually consists of
If the model is not the bottleneck and visibility is table stakes, the engineering effort goes somewhere else. In our experience it goes to four places.
Capturing what the schema does not say. Which tables are live, what the business definitions are, which filters are conventional, what a column actually contains as opposed to what it is called. Some of this can be written down by a person. Much more of it can be extracted from queries the team has already written, which is a subject in its own right.
Giving the model the joins your team actually uses. The difference between a query that is correct and one that double-counts is usually a join path. Leaving the model to infer the path from foreign keys and column names works until it does not, and when it fails it fails silently.
Checking the query before it runs. Validating the SQL against the knowledge base's copy of the schema catches unknown tables and columns, and ambiguous references, before a person ever sees a number.
Measuring on your data, not a leaderboard. The only accuracy figure that predicts anything is the one computed on the questions your people actually ask, against your actual warehouse.
None of that is glamorous. All of it is the difference between a demo and a tool.
The honest limit
We should say the obvious thing: a system that maps ambiguous English onto a specific query will sometimes be confidently wrong, and no amount of engineering removes that entirely. Language is underspecified. Two reasonable people asked "how many customers churned in Q2" will write different queries.
What engineering can do is make the wrongness visible and cheap. Show the query. Say in plain words how the question was read, so the assumption is on the page. Make it possible for the person reading the answer to see, in a few seconds, that the system understood "customer" differently than they did.
That is a much more modest claim than most of this category makes. It is also the claim we can actually support.