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.
This guide explains how text-to-SQL works step by step, why accuracy drops so sharply on real schemas, how a semantic layer and other techniques close the gap, a practical implementation plan, and the limitations that remain even when everything is done well.
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:
- 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.
- Prompt construction — assemble the question, the relevant schema (table names, columns, types, sometimes sample rows or foreign key relationships), and instructions into a prompt.
- Generation — the LLM produces a SQL query.
- Execution and (ideally) validation — the query runs against the database, and the system checks whether it executed successfully and returned something reasonable.
- 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:
| Component | What it captures | Example |
|---|---|---|
| Entities | Business objects mapped to underlying tables | "Customer" → dim_customer joined with crm_accounts |
| Metrics | Precomputed, agreed-upon calculations | "Revenue" → net of refunds, converted to USD, recognized monthly |
| Relationships | Correct join paths between entities | "Order" to "Customer" via customer_id, deduplicated |
| Synonyms | Alternative terms users actually type | "Clients," "accounts," "buyers" all map to customer |
| Time semantics | Fiscal calendar, timezone, default date ranges | "This quarter" resolves to the company's fiscal Q, not calendar Q |
| Access rules | Row- and column-level permissions | Sales reps only see their own region's data |
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.
Benefits of Text-to-SQL
With a semantic layer and verification in place, text-to-SQL delivers benefits that justify the setup effort.
Answers without waiting in the analyst queue
Many routine data questions sit in a queue until an analyst has time to write the query. A reliable text-to-SQL feature lets a sales manager or support lead get the answer directly, in minutes rather than days. Analysts spend less time on repetitive lookups and more on the analysis that actually needs their judgement. Decisions that used to wait for the next reporting cycle can be made while the question is still relevant.
Consistent definitions across the company
Because answers are generated against a semantic layer, everyone asking about "revenue" or "active customers" gets the same agreed definition. That consistency is valuable even apart from the AI: building the layer forces teams to settle long-running disagreements about metrics, and the result reduces conflicting numbers in meetings. New hires also learn the official definitions faster, because the layer documents them in one place.
Wider access to data without wider access to tables
Access rules in the semantic layer let business users query data within their permissions without being given raw database credentials. A sales rep sees their own region; finance sees what finance should see. That broadens who can use data while keeping control over what each person can reach. Audit logs of generated queries also show who asked what.
Transparent queries that build data literacy
Systems that show the generated SQL, or a plain-English restatement, let users see how their question was interpreted. Technical users can catch errors, and less technical users gradually learn how metrics are built. Over time that builds calibrated trust instead of blind reliance on a number. It also turns wrong answers into teachable moments rather than hidden errors.
A reusable foundation for other AI features
A well-maintained semantic layer serves more than one interface. The same definitions can power dashboards, agents that need data access, and internal assistants. Investment made for text-to-SQL therefore pays off across several products rather than being tied to one chat feature. When definitions change, updating the layer once updates every consumer.
Text-to-SQL Use Cases
Text-to-SQL works best in bounded domains with clear metrics and frequent repetitive questions. These are common starting points.
Sales pipeline questions
Sales leaders ask the same kinds of questions constantly: pipeline by stage, deals closing this quarter, win rates by segment. These map cleanly to a small set of entities and metrics. Modelling that domain first gives a high-demand feature with a well-understood scope, and accuracy can be checked against existing pipeline reports. Sales leaders also notice quickly when a number looks wrong, which makes them good early testers.
Support and operations metrics
Ticket volumes, resolution times, backlog by queue, and SLA breaches are well defined and frequently requested by team leads. A text-to-SQL feature lets them explore these without filing requests, and the data is usually clean enough to reach high accuracy quickly. It is a good domain to prove the approach before expanding. Operations managers often become its strongest advocates once they stop waiting for weekly reports.
Product usage analysis
Product managers want to know how features are adopted, which accounts are active, and how usage changes after a release. These questions depend heavily on definitions such as "active," which is exactly what the semantic layer settles. Once defined, product teams can answer many of their own questions while analysts handle deeper investigations. Release reviews become faster because the basic adoption numbers are already available when the meeting starts.
Finance and revenue reporting
Revenue questions are high-stakes because a wrong number can drive bad decisions, so they suit a cautious rollout with the query shown and human confirmation for anything unusual. The semantic layer encodes recognition rules, currency conversion, and refunds. Finance teams typically adopt text-to-SQL for exploration first and keep formal reporting on verified dashboards. That split keeps auditable figures stable while still speeding up ad hoc questions.
Embedded analytics in SaaS products
Software vendors add natural-language querying so their customers can ask questions about their own data inside the product. Here, access rules and tenant isolation are critical, and the semantic layer usually covers the product's own data model, which the vendor controls. That makes the scope narrower and more stable than an internal warehouse.
Text-to-SQL Best Practices
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.
A step-by-step implementation plan
For a team building its first production text-to-SQL feature, this sequence tends to work:
- Collect real questions. Gather 50–200 questions users actually ask analysts today, from tickets, Slack threads, and dashboard requests. These become your scope and your test set.
- Pick one domain. Choose the subject area with the most demand and the cleanest data, such as sales pipeline or support metrics.
- Write the gold answers. Have an analyst write and verify the correct SQL and expected result for each test question.
- Model that domain in a semantic layer. Define entities, metrics, join paths, synonyms, time rules, and access rules — reusing existing BI definitions where they exist.
- Build retrieval and generation. Retrieve only relevant entities and metrics for each question, add a few verified examples, and generate queries against the semantic layer.
- Add validation and repair. Execute queries with read-only, permission-scoped credentials, run sanity checks, and retry with error feedback when needed.
- Show the work. Display the query or a plain-English restatement alongside every answer, and let users flag wrong results.
- Measure against the test set on every change. Track accuracy, refusal rate, and latency, and treat drops as regressions.
- Expand domain by domain. Add new areas only when the current one is reliable and has an owner.
Common Text-to-SQL Mistakes
Pointing an LLM straight at the raw schema
The fastest demo connects a model to the warehouse and lets it guess. It works on clean sample tables and then fails on real schemas full of ambiguous names, multiple join paths, and metrics that live outside any column. Skipping the semantic layer is the main reason projects land near the low end of accuracy and lose user trust early.
Judging success on queries that run
A query that executes without error can still be wrong: a bad join can duplicate rows, or a filter can use the wrong date. Teams that measure only execution success miss these silent failures. Build a test set of real questions with verified answers and measure correctness against it. Include tricky cases such as fiscal periods, cancelled orders, and duplicate customers, where silent errors are most common.
Launching with "ask anything" scope
Opening the feature to every table on day one exposes users to the weakest parts of the data model. A few wrong answers in prominent places are enough for people to stop trusting all answers. Start with one well-modelled domain and expand when it is reliable. Trust is much easier to keep than to win back.
Hiding the query from users
Showing only a number asks users to trust a black box. When the result is wrong, nobody can see why, and the error may drive a real decision. Display the SQL or a plain-English restatement, and give users a simple way to flag answers that look off.
Running with broad database credentials
Generated queries should run with read-only, permission-scoped credentials that enforce the same row- and column-level rules the user would face elsewhere. Using a broad service account risks exposing data users should not see and allows expensive or unsafe queries. Enforce limits in the database, not just in the prompt. Add query timeouts and row limits as well.
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.
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.
FAQ
What is text-to-SQL?
Text-to-SQL is the task of converting a natural-language question, such as "what were our top ten customers by revenue last quarter," into a SQL query that correctly retrieves the answer from a database. Modern systems typically use large language models combined with schema context, examples, and often a semantic layer to generate the query, then execute and validate it. The goal is to let non-technical users ask questions of data directly without waiting for an analyst to write each 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, cryptic or inconsistent naming, undocumented join logic, and business metrics like revenue that aren't stored as simple columns. The model has to guess what the data means, and many guesses produce queries that run but return wrong numbers. 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, synonyms, time rules, and access permissions in one curated place. It gives the LLM a disambiguated vocabulary to work from, so it selects pre-verified building blocks instead of reverse-engineering the warehouse. That's a major factor in pushing accuracy from around 40% to the 85–95% range on enterprise schemas, and it also keeps AI answers consistent with existing dashboards.
Can I just use a bigger or newer LLM to fix accuracy problems?
Model quality helps with SQL syntax, complex queries, and reasoning, but most enterprise failures come from ambiguous business meaning, not weak SQL generation. A better model without better context will still guess wrong about what "active customer" or "revenue" means in your specific data, or which of several join paths is correct. Upgrading the model is worth testing, but investing in the semantic layer, examples, and validation usually delivers a much larger improvement.
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, runs with read-only credentials, and includes validation checks before results reach a user. It also helps to have the system decline questions outside its modeled scope rather than guess. Exposing raw, unverified text-to-SQL output as if it were a trusted number is where most real-world incidents come from, because wrong answers look just as confident as right ones.
How long does it take to build a reliable text-to-SQL feature?
Generation itself can be prototyped in days, but building the semantic layer — agreeing on metric definitions across teams, mapping join paths, documenting synonyms — is usually the longer effort, often measured in weeks per domain depending on how well documented the data already is. Scoping the first version to a narrow, well-understood domain with a test set of real questions 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 and security problems: it guesses at business meaning, may choose wrong join paths, and may access data users shouldn't see. A purpose-built text-to-SQL system adds schema retrieval, a semantic layer, permission enforcement, validation, and often human review specifically to make the generated queries trustworthy rather than just plausible.
How much does a text-to-SQL system cost to run?
Model costs per question are usually modest, since each query involves a prompt with selected schema context and a short generated response, plus occasional repair attempts. The larger costs are people's time: analysts and data engineers building and maintaining the semantic layer, writing verified examples, and reviewing flagged answers, plus warehouse compute for executing queries. Budget the project mainly around that ongoing data-modeling and maintenance work, not the LLM bill.
Conclusion
Text-to-SQL looks solved in demos and unfinished in production, and the reason is clear once you look closely: the hard part isn't writing SQL, it's knowing what the data means. On real enterprise schemas, an LLM pointed directly at raw tables often gets the answer wrong, and many of those wrong answers look perfectly reasonable.
The main insight is that context closes most of the gap. A semantic layer that defines entities, metrics, join paths, synonyms, time rules, and permissions turns the model's job from guessing into selecting verified building blocks, and retrieval, examples, validation, and visible queries add further safety. Scoping narrowly, measuring against a test set of real questions, and assigning an owner to the semantic layer keep accuracy from drifting.
The limits remain real. Complex analytical questions are still harder, organizational agreement on definitions is often the true bottleneck, coverage gaps can fail silently, and evaluation and access control need careful design.
A good next step is to collect the questions your team actually asks analysts and pick one domain to model well. If you'd like help designing the semantic layer, validation pipeline, and LLM integration, our LLM integration team can scope it with you.