Text-to-SQL: Why Letting AI Query Your Database Is Harder Than It Looks

Ask a large language model to turn "show me our top customers by revenue last quarter" into SQL, and it will happily produce a query. Run that query against a demo database with three clean tables, and it will probably work. Run the same request against a real enterprise data warehouse — the one with 400 tables, five different columns that could plausibly mean "customer," and a revenue field that's actually stored across three joined views with a currency conversion buried in a stored procedure — and the model's confidence doesn't survive contact with reality.

That gap between demo and production is the story of text-to-SQL right now. The idea is old, the language models are new and genuinely capable, and the result is a technology that looks solved in a sales pitch and looks unfinished the moment it touches a real schema.

What text-to-SQL actually is

Text-to-SQL is the task of converting a natural-language question into a syntactically correct, semantically accurate SQL query that, when executed, returns the answer the person actually wanted. It's been an active research area since long before generative AI became mainstream — academic benchmarks like Spider and WikiSQL date back to 2017-2018 — but large language models changed the practical baseline. Instead of training a narrow model on a fixed set of query patterns, you can now hand a general-purpose LLM a database schema and a question, and it will generate plausible SQL with no task-specific training at all.

The basic pipeline looks like this:

1. Schema retrieval — identify which tables and columns are relevant to the question, since most real databases have too many tables to stuff into a single prompt.
2. Prompt construction — assemble the question, the relevant schema (table names, columns, types, sometimes sample rows or foreign key relationships), and instructions into a prompt.
3. Generation — the LLM produces a SQL query.
4. Execution and (ideally) validation — the query runs against the database, and the system checks whether it executed successfully and returned something reasonable.
5. Repair loop — if the query errors out or looks wrong, some systems feed the error back to the model and ask it to try again.

Each step sounds mechanical. In practice, steps 1 and 2 — figuring out what the database means, not just what it's shaped like — are where most real-world failures originate.

Why schema alone isn't enough

A column named status with values 1, 2, 3 tells the model nothing about what those numbers represent. A table named tbl_ord_hdr gives no hint that it's the order header table. Two tables might both have a customer_id column that means different things depending on whether it was populated by the CRM system or the billing system. LLMs are excellent at pattern-matching against clean, well-named schemas because that's what most public training data and benchmarks look like. Production schemas, built up over years by different teams under different naming conventions, look nothing like that.

Why this matters right now

The headline number making the rounds in data engineering circles is stark: raw text-to-SQL — an LLM pointed directly at a database schema with no additional context — accuracy on real enterprise schemas is often around 40%. That's not a benchmark score on an academic dataset; it's the failure rate teams see when they try to ship the "just ask your data a question" demo into production and let real users type real questions.

The same reporting shows a very different number when a semantic layer sits between the LLM and the raw schema: accuracy in the 85-95% range. That's not a marginal improvement — it's the difference between a feature that's unreliable enough to be actively dangerous (a wrong number presented with total confidence looks identical to a right one) and a feature that's trustworthy enough to put in front of business users.

That 40% vs. 85-95% gap is the whole story of where text-to-SQL is heading. The interesting engineering work isn't making LLMs better at writing SQL syntax — they're already good at that. It's building the layer of business context, disambiguation, and guardrails that sits in front of the model so it isn't guessing about what your data means.

Why raw schema-to-SQL breaks down

It helps to be specific about what fails, because "it's hard" undersells the problem and "just use a better model" oversells the fix.

• Ambiguous business terms. "Active customer" might mean logged in within 30 days, has a non-cancelled subscription, or made a purchase in the last fiscal year — and different teams in the same company use different definitions. The LLM has no way to know which one you mean unless it's told.
• Join path explosion. In a schema with hundreds of tables, there can be multiple valid-looking paths to join two tables, only one of which returns correct results (the others silently produce duplicate rows, dropped rows, or fan-out errors that don't throw a SQL error — they just return a wrong number).
• Metric definitions baked into logic, not schema. "Revenue" often isn't a column — it's a calculation involving discounts, refunds, currency conversion, and recognition timing that lives in a BI tool or a senior analyst's head, not in any table or column comment.
• Synonyms and abbreviations. Users say "clients," the schema says accounts. Users say "this month," and the model has to correctly resolve that against the query's execution date and the company's fiscal calendar.
• Silent correctness failures. The most dangerous failure mode isn't a query that errors out — it's a query that runs successfully and returns a plausible-looking but wrong number. Nobody double-checks a dashboard that looks reasonable.
• Scale of context. You can't paste a 400-table schema into a prompt and expect useful retrieval; the model needs help narrowing down to the handful of tables actually relevant to a given question.

None of these are fixed by a bigger or newer LLM. They're fixed by giving the model — and the humans reviewing its output — reliable, curated context about what the data actually means.

The semantic layer: what closes the gap

A semantic layer is a business-meaning layer that sits between raw database tables and the tools (or models) querying them. It predates the LLM era — BI platforms like Looker and dbt's metrics layer built semantic layers to give consistent metric definitions to human analysts — but it turns out to be exactly the missing piece for text-to-SQL too.

A semantic layer typically defines:

When an LLM generates SQL against this layer instead of the raw schema, it isn't reasoning about ambiguous table names and undocumented join logic — it's selecting from a curated, disambiguated vocabulary that already encodes the organization's definitions. That's most of the accuracy jump from ~40% to 85-95%: the model's job shrinks from "reverse-engineer our data warehouse" to "pick the right pre-verified building blocks."

Other techniques that stack on top

A semantic layer is the biggest lever, but production text-to-SQL systems typically combine several techniques:

• Retrieval-augmented schema selection — instead of feeding the whole schema to the model, retrieve only the tables/columns relevant to the question, often using embeddings over table and column descriptions.
• Few-shot examples — showing the model 3-5 examples of similar questions paired with correct, verified SQL for this specific database, not generic examples.
• Query validation and self-correction — executing the generated query, checking it against constraints (row count sanity checks, type checks), and feeding errors back to the model for a repair attempt.
• Human-in-the-loop confirmation — for ambiguous or high-stakes questions, showing the user the generated query (or a plain-English restatement of it) before executing, rather than silently returning results.
• Fine-tuning or prompt-tuning on the specific dialect and schema — general-purpose models often need nudging toward a company's specific SQL dialect (Snowflake vs. BigQuery vs. Postgres) and naming conventions.

Practical implications for businesses and builders

If you're evaluating or building a text-to-SQL feature, the semantic-layer gap should change how you scope the project.

Don't budget the project as "add an LLM in front of the database." The LLM call itself is the cheap, fast part. The expensive, slow part — and the part that actually determines whether the feature is usable — is building and maintaining the semantic layer: agreeing on metric definitions across teams, documenting join paths, and keeping synonyms and business rules current as the schema evolves.

Scope the first version narrowly. Rather than "ask anything about any table," pick a bounded domain — say, sales pipeline questions, or support ticket metrics — define its semantic layer thoroughly, and expand from there. A well-scoped feature at 90%+ accuracy builds trust; a broad feature at 40% accuracy destroys it, because users can't tell a correct answer from a wrong one without checking, and most won't check.

Treat the query, not just the answer, as a first-class output. Systems that show the generated SQL (or a readable restatement of the query logic) alongside the answer let technical users catch errors and build calibrated trust over time. Systems that hide the SQL and just show a number are asking users to trust a black box with financial or operational decisions.

Build in a verification loop, not just a generation loop. Executing the query and checking that it ran without error is necessary but not sufficient — a query can execute cleanly and still be wrong. Sanity checks (row counts against expectations, comparison against a known-good historical query) catch a meaningful share of silent errors.

Expect ongoing maintenance, not a one-time build. Schemas change, business definitions change, and new questions surface edge cases the semantic layer didn't anticipate. Teams that treat text-to-SQL as "ship it and move on" tend to watch accuracy erode as the underlying data model drifts from the semantic layer's assumptions.

Assign clear ownership of the semantic layer. It's common for a data platform team to build the semantic layer initially and then have no defined process for keeping it current as new tables, metrics, or business rules appear. Without an owner, the layer that made the feature trustworthy on launch day quietly falls out of sync with the warehouse, and accuracy degrades in ways that are hard to detect until a user notices a wrong number. Treating the semantic layer like any other piece of production infrastructure — with a named owner, a review process for changes, and monitoring for drift against the underlying schema — is what keeps the accuracy gains from eroding over time.

Limitations and open questions

Even with a strong semantic layer, text-to-SQL has real, unresolved limits worth naming plainly.

• Complex analytical reasoning still trips models up. Multi-step questions involving window functions, complex subqueries, or statistical operations (cohort analysis, year-over-year comparisons with irregular calendars) remain harder to generate correctly than straightforward filter-and-aggregate queries.
• Semantic layers require organizational agreement, which is often the actual bottleneck. Getting finance, sales, and product to agree on one definition of "active customer" is a governance problem, not a technical one, and it can take longer than building the software.
• Coverage gaps degrade silently. If a question falls outside what the semantic layer models, the system either fails visibly (better) or falls back to guessing against raw schema (worse, and easy to ship by accident).
• Evaluation is genuinely hard. Unlike code generation, where you can often run tests, judging whether a SQL query "correctly" answers an ambiguous natural-language question frequently requires human judgment, which makes automated accuracy measurement and regression testing harder to get right.
• Security and access control add real complexity. A text-to-SQL system needs to respect row-level and column-level permissions per user, which means the semantic layer and the query engine both need to be permission-aware — an easy thing to get wrong quietly.

What to watch next

The trend line is toward text-to-SQL systems that look less like "an LLM writing SQL" and more like "an LLM operating a well-instrumented query interface." A few developments worth tracking:

• Semantic layers becoming a standard middleware category, with dedicated tooling (some emerging from the BI/metrics-layer world, some purpose-built for LLM consumption) rather than bespoke per-company builds.
• Better benchmarks that reflect enterprise schema complexity, since most public text-to-SQL benchmarks still use relatively clean, small schemas that overstate how well a given model will perform on a real warehouse.
• Tighter integration between text-to-SQL and existing BI metric layers (dbt metrics, LookML, Cube), so organizations don't have to build a second, parallel definition of "revenue" just for the AI feature.
• More systems defaulting to showing their work — surfacing the generated query, the tables it touched, and a confidence signal — rather than presenting a bare number as ground truth.

FAQ

What is text-to-SQL?

Text-to-SQL is the task of converting a natural-language question into a SQL query that correctly retrieves the answer from a database. Modern systems typically use large language models combined with schema context to generate the query.

Why does text-to-SQL accuracy drop so much on real databases?

Public benchmarks use small, cleanly named schemas, while real enterprise databases have hundreds of tables, ambiguous naming, undocumented join logic, and business metrics that aren't stored as simple columns. Raw text-to-SQL accuracy on real schemas is commonly around 40%, compared to much higher scores on benchmark datasets.

What is a semantic layer, and why does it help?

A semantic layer sits between raw database tables and the tools querying them, defining business entities, metric calculations, correct join paths, and terminology in one curated place. It gives the LLM a disambiguated vocabulary to work from, which is a major factor in pushing accuracy from around 40% to the 85-95% range on enterprise schemas.

Can I just use a bigger or newer LLM to fix accuracy problems?

Model quality helps with SQL syntax and reasoning, but most enterprise failures come from ambiguous business meaning, not weak SQL generation. A better model without better schema context will still guess wrong about what "active customer" or "revenue" means in your specific data.

Is text-to-SQL safe to expose directly to business users?

It's safer when the system shows the generated query (or a plain-language explanation of it), enforces row- and column-level permissions, and includes validation checks before results reach a user. Exposing raw, unverified text-to-SQL output as if it were a trusted number is where most real-world incidents come from.

How long does it take to build a reliable text-to-SQL feature?

Generation itself can be prototyped quickly, but building the semantic layer — agreeing on metric definitions across teams, mapping join paths, documenting synonyms — is usually the longer effort. Scoping the first version to a narrow, well-understood domain is faster and safer than trying to cover an entire warehouse at once.

What's the difference between text-to-SQL and a general AI chatbot with database access?

A general chatbot with raw database access is effectively unguarded text-to-SQL and inherits all its accuracy problems. A purpose-built text-to-SQL system adds schema retrieval, a semantic layer, validation, and often human review specifically to make the generated queries trustworthy rather than just plausible.

Teams building text-to-SQL or other data-facing AI features who want help designing the semantic layer and validation pipeline, not just wiring up an LLM call, can reach out to Woyce Technologies.