Text-to-SQL- Almost Working?

Text-to-SQL has been almost-working for a good few years. Every demo is spectacular: ask “what was revenue last quarter,” (or something to do with taxis…) get a SQL query, get the right answer. But every production deployment is fragile in ways that aren’t obvious from the demo. The reasons are interesting and they tell you something about why the “LLM does analytics” pitch is not just accepted by those of us with a deep understanding of the risks.

What text-to-SQL actually has to do

A user asks a question in business English. The system needs to figure out which tables and columns hold the answer, write a SQL query that reads them correctly, handle the inevitable ambiguities, and return a result the user can trust.

Figuring out the schema is the hard part, and it’s the part demos hide. In the demo, the dataset has 12 tables, all clean, with obvious column names. In production, the warehouse has 8,000 tables, half of them deprecated, with column names like amt_2 and flag_x, and three different tables that look like they might contain “revenue.”

The schema problem

LLMs aren’t bad at writing SQL. They’re good at writing SQL when given the right schema. The thing they’re bad at is finding the right schema.

If you dump every table’s schema into the prompt, you blow the context window and confuse the model. If you embed the schemas and retrieve the most relevant ones, you’re back to a RAG problem with all the chunking and embedding issues from earlier posts. If you let the model browse the catalog with tool calls, you’re in agent territory, with its own latency and cost. There’s no clean answer here.

And even when the model finds “revenue,” it might find the wrong one. The recognised revenue from the GL, the booked revenue from CRM, the net revenue after refunds in the data warehouse, the gross revenue in the OLTP. All four are real tables. All four have plausible names. The right answer depends on what the user actually meant, which the user usually didn’t say.

The semantic gap

This is the deeper problem. Business questions are asked in business language. Databases are designed in technical language. The gap between “revenue” (business) and fact_invoice_line.amt_local_ccy (technical) isn’t bridgeable by an LLM reading the schema alone. The LLM doesn’t know that amt_local_ccy is in local currency and that converting to USD requires joining dim_currency_rate on invoice_date. The LLM doesn’t know that you’re supposed to filter is_deleted = 0. The LLM doesn’t know that the official revenue metric excludes refunds, which means joining fact_refund and subtracting.

These aren’t bugs in the model. They’re facts about the data that exist in human heads and SQL files but not in the schema. Until they’re explicit somewhere the model can read, text-to-SQL keeps almost-working.

The semantic layer answer

The answer most credible teams converge on: don’t let the LLM write raw SQL against the warehouse. Let it write against a semantic layer where the business concepts are first-class, the joins are pre-defined, the filters are baked in, and the meaning is explicit. The LLM’s job becomes “translate the question into a semantic query,” not “figure out the data model from scratch.”

dbt’s semantic layer, Cube, Looker’s LookML, and the various semantic-layer-as-a-feature offerings from the warehouses (Snowflake Cortex Analyst, Databricks Genie) all do versions of this. The LLM doesn’t see fact_invoice_line. It sees revenue as a metric, with a documented definition. It picks the metric, applies the requested filters, the semantic layer compiles it to SQL.

This works far better than naive text-to-SQL. It also requires the organisation to have built the semantic layer, which is the part most haven’t done. There’s no shortcut. The semantic layer is the precondition; text-to-SQL on the semantic layer is the natural consequence.

Where text-to-SQL still fails

Even with a semantic layer, some queries are hard:

• Queries that need multi-hop reasoning. “Show me customers who bought X and then churned within 30 days, segmented by acquisition channel.” The LLM has to plan the query, not just translate it.
• Queries that need data not in the semantic layer. Anything ad-hoc that the warehouse can answer but the semantic layer didn’t anticipate.
• Queries that need to reason about time. “Year-over-year change” sounds simple; specifying it precisely requires choices the LLM might make wrong.
• Queries with implicit context. “The usual metrics” means different things to different people.

What works in production

The text-to-SQL deployments I’ve seen work share characteristics:

1. A semantic layer with real coverage of the questions users actually ask. Not a shim over raw tables.
2. An LLM that writes against the semantic layer, not the warehouse.
3. An “I don’t know” path that surfaces clearly when the question can’t be answered. Not a guess that looks confident.
4. Verification of the generated query before execution. Show the user what was generated; let them refine.
5. Result explanations. The numbers come with a description of what they mean and where they came from.
6. Tight evaluation. A standing test set of business questions with known answers, run on every change.

None of this is impossible. None of it is what the “ask your data” demos show. The gap between demo and production is the work of building the semantic layer, instrumenting the eval, and being honest about what the system can and can’t do.

Text-to-SQL will keep almost-working until organisations build the layer beneath it. Then it’ll start actually working, on a narrower surface than the marketing implies, but reliably. That’s the trajectory the next few years will trace.