Text-to-SQL: The Promise and the Limits

Show a demo audience "just point AI at the database and let it write SQL," and it usually works on the first try. Someone types "what were sales last month," the model produces a clean SELECT SUM(amount) FROM orders WHERE date BETWEEN ..., and the room is impressed. Then someone tries it in production, and it falls apart on the first real question.

Not because the model suddenly writes worse SQL. The SQL is often syntactically perfect. It fails because the question that sounded simple ("what were our sales last month") has no single correct query behind it in a real company. There are three columns that could plausibly be called revenue, two tables that join in more than one defensible way, and an asker who should not see some of the rows that answer would return.

Text-to-SQL is a capability that converts a plain-language question into an executable SQL query against a database, so someone can ask "what were sales last month" and get back the query that answers it without writing any SQL by hand. The capability is real. What separates a clean demo from a shaky production rollout is business context, and no amount of prompting supplies that on its own.

Why the demo works

Most text-to-SQL demos run against one table, or a handful of clean, well-named ones: orders, customers, products. Column names spell out what they mean. There is one date field, one amount field, and one obvious way to filter. A model given that schema and a plain-English question has almost nothing to get wrong. The ambiguity that trips up real systems has been engineered out before the demo starts.

Production schemas look nothing like that. A single enterprise resource planning system can carry a revenue column, a net_revenue column, and an amt_2 column left over from a migration three years ago, and the difference between them can be the discount treatment, the tax treatment, or which region's numbers got backfilled wrong that one time. The model has no way to know which of the three is the number your CFO reports to the board. It will pick one, confidently and silently. That gap between a demo schema and a production one is the same gap data readiness work is meant to surface: not whether data is clean, but whether anyone has agreed what it means.

The gap shows up in the numbers. On BIRD, a published text-to-SQL benchmark built on 95 large real-world databases, the strongest systems reach around 82% execution accuracy while human data engineers reach roughly 93%, and that is before a single question of permissions or contested metric definitions enters the picture.

Which of your three revenue columns is the revenue?

This is the sharpest version of the problem: ambiguity that does not look like ambiguity. A wrong join returns an obviously broken result: a row count that's absurd, a null where there shouldn't be one. A wrong metric definition returns a plausible number. Nobody double-checks a number that looks right, so the wrong definition survives, gets repeated, and eventually gets challenged in a meeting where two people ran "the same" query and got different answers.

The same ambiguity shows up in joins. "Show me sales by distributor" assumes a specific path through the schema: which table owns the distributor relationship, whether a distributor can belong to more than one region, whether a returned order still counts as a sale. None of that is written in the schema. It lives in the heads of the people who built the system, and a model reading table and column names has no access to it.

What is this person allowed to see?

The third failure mode is not about correctness at all. It is about exposure. A model that can write arbitrary SQL against a live database can, in principle, write a query that returns rows the asker has no business seeing: another region's margins, another department's headcount costs, another user's customer list. Naive text-to-SQL executes whatever query it generates with whatever access the underlying connection has, which in practice is usually broader than any one user's actual permissions.

This is not a hypothetical edge case; it is the default behavior of the naive setup. A staff member asking a plain question should get an answer scoped to what they are allowed to see, the same way a dashboard or report would scope it — and that scoping has to happen whether or not the person's phrasing reveals they are trying to see more than they should.

Dialect drift and the moving target underneath

There is a quieter failure mode too: schemas change. A column gets renamed, a table gets split, a business adds a new region with its own quirks. SQL dialects differ across databases in ways that matter for date arithmetic, string handling, and pagination. A model regenerating SQL fresh against a schema it re-reads each time will eventually generate a query that runs, returns a result, and is wrong in a way nobody notices until the numbers don't reconcile. Regeneration without a stable layer of meaning underneath it just moves the failure mode — it doesn't remove it.

What actually makes text-to-SQL reliable?

None of this means text-to-SQL should be abandoned. It means the model needs something underneath it that a bare database connection cannot supply: a layer that already knows which revenue column is the revenue, how tables join in your business's terms, and what this specific asker is allowed to see — before a single query gets written. Research approaches to this problem lean on schema-linking, the step of mapping the entities named in a question to the exact tables and columns that hold them. That mapping is far more dependable once a semantic layer has fixed the definitions than when the model has to infer them fresh on every query.

That is the job of a semantic layer: the shared dictionary of metric definitions, entity relationships, and system-of-record rules that settles the ambiguity a model cannot resolve on its own. With that in place, the model's job narrows from "guess what the business means" to "translate a question into a query against a meaning that is already defined" — a much smaller, much more reliable task. It's the same reasoning behind comparing RAG, fine-tuning, and an intelligence layer honestly: grounding an answer in something defined ahead of time beats hoping the model infers it correctly at question time.

Governed execution matters just as much as the definitions. Every generated query should run through the same permission checks a human-facing report would, scoped to the asker's role, before it ever touches the database. And a trustworthy answer shows its work: which tables it queried, which definition it used, so a skeptical CFO can verify it rather than take it on faith. This combination of definitions, governed execution, and visible sources is what an AI intelligence layer is built to provide, with text-to-SQL as one capability inside it rather than the whole architecture.

Naive text-to-SQL vs. governed query generation

When a saved metric beats regeneration

Here is the honest caveat: even governed text-to-SQL is not always the right tool. If a question gets asked every week in the same shape ("what was net revenue by region last month"), regenerating a query for it each time is doing avoidable work and reintroducing a small chance of drift with every run. For well-defined, recurring questions, a saved metric that executes the same vetted query on demand beats asking a model to reconstruct it from scratch. Text-to-SQL earns its keep on the questions nobody thought to define in advance — the ad hoc, the exploratory, the "wait, why did this number move" follow-up. Recurring reporting deserves a metric, not a regeneration.

Where Nalar fits

Nalar treats text-to-SQL as one tool inside a governed intelligence layer, not a standalone trick. Its agents generate queries against a semantic model that already defines your metrics and entity relationships, execute them under the same permissions your dashboards enforce, and show which sources and definitions produced an answer, with recurring questions served as saved metrics rather than regenerated from scratch every time.

If you want to see how that plays out on a realistic mock enterprise, the interactive demo is open to try. If you are not sure whether your own data and definitions are in shape for this yet, BARI, our AI-readiness diagnostic, will tell you honestly — including when the answer is "not yet."