Text-to-SQL for Business Data: Why Accuracy Depends on Context, Not Just the Model
Why natural-language-to-SQL fails on real business data, and how schema curation, semantic layers, permissions and validation make it dependable.
Quick answer
Text-to-SQL lets people ask questions of business data in plain language while a language model writes the SQL. Modern models write syntactically valid SQL well. The hard part is business meaning: which table is authoritative, what counts as revenue, how quarters are defined, which records to exclude and what this user is allowed to see.
Accuracy therefore depends more on context than on the model. Curate the schema the model sees, describe tables and columns in business language, route governed metrics through a semantic layer, enforce permissions in the database layer, validate queries before running them and show users the definitions used.
Why text-to-SQL is harder on real business data
Demos use clean schemas with obvious names. Enterprise warehouses have hundreds of tables, legacy naming, several versions of the same entity, soft-deleted rows, test orders and business rules that exist only in report code. Research benchmarks reflect this: BIRD, which uses large real-world databases with dirty values and external knowledge, still shows a clear gap between the best automated systems and human experts (BIRD benchmark).
The failure mode is not a crash. It is a confident, plausible, wrong number. That is why text-to-SQL needs more than a capable model.
Where text-to-SQL goes wrong
| Error type | Example | Fix |
|---|---|---|
| Ambiguous term | 'Customers' = all accounts, or paying accounts? | Governed definitions and synonyms; ask when unclear |
| Wrong table | Uses orders_v1 instead of fct_orders | Expose only curated tables or views |
| Missing exclusions | Includes test orders and cancelled orders | Encode exclusions in views or metrics |
| Wrong join | Joins on email instead of customer ID; duplicates rows | Document join keys; use semantic models |
| Time conventions | Calendar quarter instead of fiscal quarter | Time dimension with fiscal calendar |
| Units and currency | Sums mixed currencies | Reporting currency columns; documented units |
| Permissions | Returns rows the user should not see | Row- and column-level security in the database |
| Silent fan-out | Aggregates after a one-to-many join | Validation for row-count anomalies; semantic layer |
Three architectures, from least to most governed
1. Raw schema. The model sees table definitions and writes SQL. Fast to prototype, risky for business answers. 2. Curated views with descriptions. The model sees a small set of documented views built for questions, with business descriptions, sample values and example queries. Much more reliable for exploration. 3. Semantic layer. The model selects metrics, dimensions and filters; the layer generates governed SQL. Most reliable for recurring business metrics, limited to what has been modelled.
Many teams combine 2 and 3: governed metrics through the semantic layer, exploratory questions through curated views, with the assistant telling users which mode it used.
| Raw schema | Curated views | Semantic layer | |
|---|---|---|---|
| Setup effort | Low | Medium | Higher |
| Consistency with official numbers | Low | Medium | High |
| Flexibility | High | Medium | Limited to modelled metrics |
| Best for | Prototypes, analysts | Exploration by business users | KPIs, reporting, agents |
Context that improves accuracy
Whatever the architecture, the model needs the right context for each question, not the whole schema. Useful context includes table and column descriptions in business language, relationships and join keys, sample and allowed values for categorical columns, business definitions and synonyms, the fiscal calendar, verified example questions with their correct SQL, and the user's permissions. Retrieve the relevant subset per question. Warehouse vendors' natural-language features are built on this idea; Snowflake's semantic views, for example, can hold verified queries and custom instructions alongside metrics and dimensions.
This is the same principle as a business context layer: meaning must be supplied, not inferred.
Question + user identity
│
▼
Classify: governed metric? ──yes──▶ semantic layer request
│ no (metric, dims, filters)
▼
Retrieve context: relevant views, descriptions,
values, definitions, verified examples
│
▼
Model drafts SQL (structured output)
│
▼
Validate: parse · allowed tables only · read-only ·
row/cost limits · dry run / EXPLAIN
│ ✓ ✗ → repair or ask user
▼
Run as the user (row/column security applies)
│
▼
Answer + SQL + definitions used + data as-of timeSecurity and permissions
Never rely on the prompt to enforce access. Run queries with credentials tied to the requesting user, or apply row- and column-level security in the warehouse so the same query returns only what that user may see. Use read-only roles on a replica or warehouse, never the transactional database. Allowlist schemas, block data-modifying statements at the parser and the role, cap rows and execution time, and log every query. Treat question text as untrusted input, since a crafted question can try to steer the generated SQL; see prompt injection prevention and AI agent access control.
Validation before and after the query
Before running: parse the SQL, check that it only references allowed objects, require a time filter on large tables and run a cost estimate. After running: check for empty results, suspicious row counts after joins and values outside expected ranges. When a check fails, let the model repair the query once, then ask the user to clarify rather than looping. Generating SQL into a structured output (query plus the metrics, tables and assumptions used) makes these checks easier.
Show your working
Users trust numbers they can check. Show the definition used ('net revenue: excludes tax, shipping and refunds; fiscal quarters'), the time range, the filters and the data as-of time, with the SQL available for analysts. When the assistant is unsure which definition is meant, it should ask. Recording this is also your answer provenance.
How to evaluate text-to-SQL for your business
- Collect 50 to 200 real questions from the people who will use it
- Have analysts write and verify the correct answer for each
- Compare results (execution accuracy), not just SQL text
- Include ambiguous questions; the right behaviour may be to ask
- Include permission tests: questions users should not be able to answer
- Re-run the set on every change to models, prompts, views or definitions
- Track accuracy by question type to see where curation is needed
Common mistakes
The biggest mistake is giving the model the full schema and judging success by whether queries run. Others: letting the assistant answer KPI questions with different logic from official reports, enforcing permissions in the prompt, skipping evaluation with verified answers and hiding the SQL and definitions from users.
Building natural-language access to business data?
ZSpace Labs builds AI analytics assistants on governed data, with permissions, validation and evaluation. See AI automation.
Conclusion
Text-to-SQL is no longer limited by SQL syntax. It is limited by business context. Curate what the model sees, route governed metrics through a semantic layer, enforce access in the database, validate before and after running, evaluate against verified answers and show users how each number was produced. Do that, and natural-language access to data becomes something people can rely on.
Common questions.
Text-to-SQL is the use of a language model to turn a natural-language question, such as 'What was revenue by region last quarter?', into a SQL query that runs against a database, so non-technical users can ask questions of data.