Agentic text-to-SQL: generation was never the hard part
Pull-quote: “A query that runs, returns rows, and answers a question nobody asked never raises an error.”
Agentic text-to-SQL is decided at four points, and none of them is generation: how the question binds to your model, which slice of the catalog the generator sees, what the platform lets the query touch, and what must be true before the result leaves. Models write valid SQL reliably now. Systems still return wrong numbers, almost always for reasons upstream or downstream of the SQL.
This post is for the engineer whose prototype demos well and cannot be trusted with a real question. It assumes you know SQL and have a warehouse with permissions on it. The term is worth pinning down: text-to-SQL is a translation step, while agentic text-to-SQL is a loop that plans, generates, executes, inspects what came back, and decides whether to answer, retry, or decline. The extra steps are where correctness gets established.
What you will be able to do
- Separate a generation failure from a binding failure, and stop tuning prompts to fix modelling defects.
- Run three binding checks before any SQL is written, and act correctly when a binding is not unique.
- Assemble schema grounding that fits a prompt when the warehouse has thousands of columns.
- Push entitlement and execution limits into the platform, where a persuasive prompt cannot move them.
- Verify a result with four checks that do not depend on the model that wrote the query.
- Report intent resolution, execution accuracy, and abstention correctness as three separate numbers.
Why is SQL generation no longer the bottleneck?
Because when the schema is described and the question is unambiguous, current systems generate correct SQL most of the time, and the remaining errors trace back to the description and the question rather than to the syntax.
Two benchmarks show the shape of it. BIRD holds 12,751 question and SQL pairs across 95 databases totalling 33.4 GB, built on messy real-world values, and publishes a human baseline from data engineers and database students at 92.96% execution accuracy. Spider 2.0 moved the target to enterprise workflows: 632 problems on real warehouses that often carry more than 1,000 columns, where a reference query can exceed 100 lines. At launch, GPT-4o solved 10.1% of Spider 2.0 against 86.6% on Spider 1.0.
Leading entries on the Spider 2.0-Snow split now score above 90, and its authors describe that split as including well-prepared database metadata and documentation. The number moved when the context improved, which is the argument for treating this as an architecture problem. Both benchmarks share one limit: execution accuracy compares result sets and says nothing about whether the question was the right question, which is exactly the failure your users report.
What goes wrong when the generated SQL is valid?
The query answers a different question from the one asked, and nothing objects because both queries are legal SQL against the same tables.
| Failure | What the query did | What the asker meant |
|---|---|---|
| Wrong grain | Summed a line-level amount across a join to the header | One amount per order |
| Missing default filter | Counted every row in the table | Excluded cancelled, test, and internal rows |
| Wrong entity | Joined the customer dimension in one mart | The conformed customer used for reporting |
| Wrong time semantics | Filtered on an ingestion timestamp in UTC | The fiscal period in the reporting calendar |
Grain is the most common and the least visible: join an order header to its lines, sum a header-level order_total, and every order with three lines counts three times. None of the four raises an exception, and each returns a number that fits a dashboard.
How does an agent resolve an ambiguous question before writing SQL?
It enumerates the readings the question allows against the model in scope, and when more than one survives, it asks or declines instead of choosing. Run three binding checks before generation:
- Entity binding. Every noun in the question maps to exactly one table or view in scope. “Customer” mapping to three tables is not a prompting problem, it is an unresolved conformance decision.
- Measure binding. Every quantity maps to one governed definition. If revenue is defined one way in a metric view and another in a legacy report, the question is ambiguous no matter how clearly it was phrased.
- Period binding. Every time expression maps to one calendar and one timestamp column. “Last quarter” needs a fiscal calendar and a choice between event time and ingestion time.
When a binding is not unique, two responses are acceptable and one is not. You can ask, presenting the surviving readings rather than a vague request for clarification: “Order-line revenue including cancelled orders, or booked revenue excluding them?” Or you can apply a declared default and state it on the answer. Choosing silently is what produces the incidents.
Autonomous runs need the second path, because there is nobody to ask. Declare a default per ambiguity class, record which one fired, and treat a run that fired three defaults as a candidate for review. Structured output from an LLM covers making that payload something the calling code can branch on.
What does schema grounding actually require?
A small, described, permission-filtered slice of the catalog plus worked examples, not an information_schema dump in the prompt.
| Grounding input | The failure it removes |
|---|---|
| A curated table set per subject area | Choosing between near-duplicate tables becomes a coin flip |
| Column comments on every exposed field | Correct SQL against a column whose name carries no meaning |
| Declared primary and foreign keys | The generator invents a join path |
| Governed measures as functions or metric views | Three consumers produce three revenue numbers |
| Question and query example pairs | Wrong grain, wrong filters, wrong join order |
| Catalog retrieval instead of full schema injection | The prompt cannot fit a 3,000-column warehouse |
Example pairs earn their place ahead of prose, because a worked query encodes the join path, the filter conventions, and the grain more precisely than a paragraph describing the same rules. Production systems assemble this as a pipeline rather than one call: Databricks Genie Agents are documented as a compound system that classifies the question, selects the dataset, identifies the metrics and dimensions, then generates and evaluates SQL. A Genie Agent supports up to 100 instructions, which is a useful forcing function, since running out of slots means the fix is the model rather than more prose about it. Genie and answers you can defend works through that curation.
Where should governance live, in the agent or in the platform?
In the platform, at query time, because a prompt is not an access control mechanism and any rule an agent can be argued out of was never a control.
Three things belong to the platform. Identity is the first: the query executes as the asking user, so entitlement is decided before the agent exists. Row and column controls are the second. In Unity Catalog, a row filter is a SQL function evaluated per row at query time, and rows for which it returns FALSE never reach the result. A column mask is a function applied to one column’s value. Both attach with ALTER TABLE:
ALTER TABLE sales.orders SET ROW FILTER governance.region_filter ON (region);
ALTER TABLE sales.orders ALTER COLUMN customer_email SET MASK governance.mask_email;Databricks now recommends attribute-based access control over per-table filters when a rule has to hold across many tables, because a policy attaches at the catalog or schema level and applies automatically to anything carrying the governed tag. That matters for an agent, which will happily query a table added last week that never got its filter.
The third is the execution envelope, about blast radius rather than entitlement: read-only sessions, statement timeouts, row caps, and a ceiling on bytes scanned. In Postgres:
BEGIN;
SET TRANSACTION READ ONLY;
SET LOCAL statement_timeout = '30s';
-- the generated query runs here, as the asking user
COMMIT;One consequence catches teams building caches. Filters and masks apply per user, so two people asking the same question can correctly get different results, and an answer cache keyed on question text alone is a data leak with good latency.
How do you verify an answer before returning it?
Run checks that do not depend on the model that wrote the query, then attach what they found to the answer. Four of them, cheapest first:
- Static inspection. Before execution, confirm the query touches only tables in scope, carries a date predicate on any large fact table, and has no unexpected cross join.
EXPLAINgives an estimated cost to compare against a budget, so a runaway query is rejected rather than abandoned halfway through. - Shape check. Assert the result matches the shape the question implies. Monthly revenue by region has a predictable row count, and 4,000 rows means a grain error before anyone reads a value.
- Reconciliation. Compute the same measure a second way and compare. A governed metric evaluated at a different grain is the cheapest independent check available, and disagreement is a hard stop.
- Reference comparison. For questions on your benchmark set, compare against the known-correct answer and fail loudly on drift.
The answer that leaves the system carries the generated SQL, the tables it read, the filters and defaults applied, and which checks ran. That record is what makes the number defensible three weeks later.
What changes when an agent reads the result instead of a person?
The implicit sanity check disappears, so it has to be written down as a contract on the result.
A person who sees total revenue of 4.1 billion for a regional distributor stops and asks a question. An agent passes the figure on, and the next step drafts a summary, raises an alert, and files a ticket. Each step treats its input as ground truth, so one grain error becomes four artefacts and the one that reaches a human arrives with the original query three hops out of sight. This is the hallucinated success class from the agent failure taxonomy, arriving through the data layer instead of a tool call.
Three elements make the contract enforceable. The result is typed, carrying units, currency, grain, and period, so a later step cannot add monthly figures to quarterly ones. It carries coverage, meaning what share of the requested scope the data supported, because “no rows” and “no matching rows” differ. And it has an abstention branch the caller must handle, so declining is a first-class outcome rather than an empty result set that reads as zero.
How do you measure a text-to-SQL system people rely on?
Measure intent, execution, and abstention as three separate numbers, because one accuracy figure hides which is failing and therefore what to fix.
| Metric | The question it answers | How it is scored |
|---|---|---|
| Intent resolution | Did it bind the right entities, measures, and periods | Against a labelled reading per question |
| Execution accuracy | Does the result set match the reference | Set comparison against reference SQL |
| Abstention correctness | Did it decline what the data cannot answer | On a held-out set of unanswerable questions |
Re-run all three after every schema, instruction, or model change: what matters is whether last month’s correct answers are still correct. Build the suite from questions people actually asked, and include the traps: a double count across grains, a measure with two definitions, a period straddling a fiscal boundary, and a handful the system should refuse. Abstention is the metric teams skip and the one that predicts whether the system survives an executive. Grading the run rather than the reply covers scoring a trajectory rather than a string.
Where this goes wrong
Instructions grow to compensate for the model. The symptom is an instruction block that keeps expanding while accuracy stays flat. The cause is prose patching structure: ambiguous names, undeclared grain, duplicate entities. Fix the schema or expose a view with clear names, then delete the instructions covering for it.
Governance is implemented in the prompt. The symptom is a review that finds a rule in the system prompt and nowhere else, because the model is being treated as an enforcement point. Move entitlement to filters, masks, or policies, and re-test with a low-privilege account.
Verification runs only in the demo. The symptom is checks living in a notebook rather than on the serving path, because verification adds latency and gets deferred. Attach static inspection and the shape check to every request, and reserve reconciliation for material questions.
Caching ignores identity. The symptom is a user seeing rows they lack permission for, with no audit trail. The cause is a cache keyed on question text while the platform enforces per-user filters. Key on identity plus question, or do not cache.
Common questions
Does a stronger model fix a wrong text-to-SQL answer?
Sometimes, and rarely for the failures that matter most. It helps with complex joins and unusual dialect features, and does nothing about a question with two valid readings, a measure with two definitions, or an undeclared grain, because none of those are visible in the prompt. Fix the grounding first, then measure whether model choice still moves the number.
Do I need a semantic layer before this will work?
You need one governed definition per measure, which is the part of a semantic layer that matters here. Without it, two correct queries produce two different revenue figures and neither is wrong. A metric view or a catalog function gives the generator something to call instead of something to re-derive. Ontology governance and the semantic layer covers how those definitions get owned and changed.
How should the system behave when it cannot answer?
It should say so, name what was missing, and return the partial work. A refusal that identifies the ambiguous term or the absent table beats a confident number, and unanswered questions become your curation backlog. Score abstention deliberately, or the system learns to always produce something.
Is it safe to let an agent write to the warehouse?
Not on the path that answers questions. Keep the analytical session read-only and route writes through a separate tool with its own schema, credential, and approval gate. A prompt injection reaching the question path then cannot become a DELETE, and the audit story stays simple.
Next steps
Take the ten questions your users ask most often and answer them by hand in SQL. Those queries become your grounding examples and the first version of your benchmark set, and writing them usually surfaces two or three binding ambiguities you would otherwise meet in front of an audience.
Then read knowledge graphs as the memory layer agents need, because join paths are relationships an agent traverses deliberately, and AI systems fail quietly at the retrieval layer, the same silent-failure problem for unstructured sources. More engineering writing sits in the Signals index.
Zorost builds these on client lakehouses as a trusted Databricks partner, where the benchmark set and reconciliation query are written before the first prompt.