When Should AI Generate SQL? Only After Intent and Metrics Are Resolved

A query can execute cleanly, return a plausible number, and still be wrong. The failure is rarely in the SQL syntax. It happens when nobody settled the grain, the time window, or the definition of the metric before a model wrote the query. That is why, when you ask when should AI generate SQL, the honest answer is: last. Generation belongs after two earlier gates, intent resolution and metric resolution. Intent resolution means settling what the question actually asks, including entity, grain, scope, time window, filters, exclusions, and the expected shape of the output. Metric resolution means settling how the number is computed, including numerator, denominator, aggregation, and how nulls, duplicates, refunds, and late-arriving records are handled. Generating SQL before those gates close is the root cause of most wrong answers, not a tuning problem to fix with a better prompt. This article gives you a decision framework for which requests should reach generation at all.

What does it actually mean for AI to generate SQL in an analytics pipeline?

Generating SQL is one step in a longer pipeline, not the whole system. The original Spider benchmark defined text-to-SQL as complex, cross-domain semantic parsing where both the SQL queries and the database schemas differ between training and test, so generalization to unseen schemas is the point (Spider paper, 2018). That framing already separates understanding the question from writing the query.

Spider 2.0 pushed the task toward real enterprise workflows rather than single queries. Its databases often exceed 1,000 columns, span multiple dialects and systems including BigQuery, Snowflake, SQLite, DuckDB, Postgres, and ClickHouse, and require reading documentation, metadata, and project conventions (Spider 2.0 paper, 2024). A semantic layer, in this context, is a governed definition layer that stores metrics, dimensions, and relationships so a query engine can construct SQL from a specification rather than from a raw schema. Grain is the level of detail a row represents, such as one row per order versus one row per order line.

Once you see generation as the final stage, the two gates become concrete. Intent resolution closes first and settles what is being asked. Metric resolution closes second and settles how the number is defined. Only then does generation have a specification precise enough to produce a correct query.

How accurate is text-to-SQL today, really?

Accuracy collapses as schemas grow more realistic. An o1-preview-based agent framework solves only 21.3% of Spider 2.0 tasks, against 91.2% on Spider 1.0 and 73.0% on BIRD (Spider 2.0 benchmark results, 2024). DAIL-SQL with GPT-4o reaches 5.68% execution accuracy on Spider 2.0-lite and just 2.20% on Spider 2.0-snow, the same method that scores 86.6% on Spider 1.0 and 57.4% on BIRD.

The scale explains part of the drop. Spider 2.0 databases average 803.6 columns per database on the lite variant and 812.1 on the snow variant, with dialects split across BigQuery at 33.86%, Snowflake at 31.33%, SQLite at 21.36%, and DuckDB at 10.76% (Spider 2.0 dataset statistics, 2024). BIRD's most effective model at publication reached 40.08% execution accuracy against 92.96% for human experts (original BIRD paper, 2023), and the live leaderboard now shows a top test score of 82.95% with human performance still at 92.96% (live BIRD leaderboard, accessed March 2026).

The enterprise picture is harsher. BEAVER, built from private warehouses with 9,128 question-SQL pairs across 19 domains, shows leading agentic frameworks with GPT-5.2 reaching only 10.8%, while the best result, ReFoRCE with Claude-4.5-Sonnet, reaches 11.4% (BEAVER enterprise benchmark, 2025). These are different systems on different benchmarks, so the numbers are indicative rather than a ranking, and text-to-SQL governance should treat any vendor figure as a claim to validate in your own environment before it becomes a planning assumption. The same gap shows up in the limits of LLM-generated SQL.

Why can a valid SQL query still answer the wrong business question?

Because the query can be syntactically correct and still target the wrong schema elements. Spider 2.0's own error analysis of 300 failed generations found wrong schema linking at 27.6%, split into column linking at 16.6% and table linking at 10.1%, intricate query planning at 17.7%, and JOIN errors at 8.3% (error analysis of 300 failed generations, 2024). A survey compiling text-to-SQL error studies independently reports schema-linking errors at 29% to 49% of failures, the largest category, with join-related errors at 21% to 26% (survey compiling text-to-SQL error studies, 2025).

The paper's own conclusion is that the central difficulty is not SQL syntax but finding the right schema elements, grounding them in the correct dialect and business context, and planning a multi-step computation (Spider 2.0 paper, 2024). Consider an illustrative inference: a request for "revenue by region last quarter" can be written as a clean join and sum, yet return a different total depending on whether the grain is order or order line, whether refunds are netted, and whether the quarter is calendar or fiscal. The SQL is valid. The answer is wrong.

This is why a validation gate matters. Databricks states plainly that "successful execution is not correctness" and recommends checking that referenced objects and columns are on an approved list, that joins and grouping match the metric definition, and that results fall within expected ranges (Databricks validation guidance, accessed March 2026). The same failure mechanism is documented in more depth in the limits of LLM-generated SQL.

What is the intent gate, and how is intent resolved before generation?

The intent gate is the step that settles what the question means before any SQL is written. "Disambiguate First, Parse Later" generates all semantically distinct natural-language interpretations before any SQL is produced, then maps each unambiguous interpretation to a formal expression, explicitly separating misunderstanding the user's meaning from incorrectly expressing a known meaning in SQL (Disambiguate First, Parse Later, Findings of ACL 2025).

AmbiSQL supplies a working taxonomy for that ambiguity. It separates database-sourced ambiguity, covering AmbiSchema, AmbiValue, and AmbiIntent, from LLM-sourced ambiguity, covering AmbiSource, AmbiContext, AmbiFallacy, and AmbiRef, detects it with an LLM using in-context learning against the schema, and resolves it through targeted multiple-choice clarification before query generation (AmbiSQL, 2025).

The evaluation side has moved in the same direction. PRACTIQ was built specifically around 1,802 ambiguous and unanswerable questions alongside 1,034 answerable queries, structured as four-turn conversations with a clarification request between the question and the clarified SQL (PRACTIQ, NAACL 2025). Bird-Interact adds a user simulator that answers clarification questions, with 600 tasks and up to 11,796 dynamic interactions in its full set (Bird-Interact, 2025). Intent resolution is the production practice these benchmarks are measuring.

What is the metric contract gate, and what does a semantic layer guarantee?

The metric contract gate is the step that settles how a number is computed before any SQL is written: numerator, denominator, aggregation, and the treatment of nulls, duplicates, refunds, and late-arriving rows. A semantic layer is the artifact that holds those decisions. In dbt's MetricFlow, metrics, dimensions, and entities live in version-controlled semantic models, and MetricFlow generates SQL deterministically from that specification at query time; joins come from declared entities, and it avoids fan-out and chasm joins based on entity types (MetricFlow, the engine behind the dbt Semantic Layer, accessed March 2026). The server builds a semantic graph, finds that a requested dimension sits in another semantic model, and generates the SQL including the necessary join (how the dbt Semantic Layer works, accessed March 2026).

Snowflake stores the same concepts as a schema-level object holding logical tables, relationships, facts, metrics, and dimensions, which provides consistent business definitions across enterprise applications; Cortex Analyst reads it and executes generated SQL under Snowflake RBAC (Snowflake semantic views, accessed March 2026). Databricks separates measure definitions from the fields used to group and filter, so a metric is defined once and the engine compiles the computation at runtime, where standard views lock aggregations at creation time (defining a metric once in a metric view, accessed March 2026).

What the gate guarantees is definitional consistency, not correct answers. Databricks frames the outcome as resolving metrics from a single source of truth rather than hallucinating them (Genie resolving metrics from a single source of truth, 2026-02-04), and Cube describes agents querying the governed semantic layer instead of the raw schema, with access policies applied before a query reaches the warehouse (agents querying the governed semantic layer, accessed March 2026). No source measures an execution-accuracy delta from adding a semantic layer to any benchmark, so the guarantee is consistency and traceability, and the accuracy gain stays unquantified. That distinction matters most when a semantic layer for AI analytics is sold as an accuracy fix; the layer is covered further in the enterprise semantic layer explained, and the resolution step in text-to-metric metric resolution.

Which requests should never reach SQL generation at all?

Requests that fail either gate should be routed away from generation, not sent through it with a better prompt. Routing is where text-to-SQL governance becomes concrete: the decision of when should AI generate SQL is really a decision about which handler a request belongs to.

Verification is not a formality on that last row. A 2026 VLDB study found that combining Round-Trip Critique with Synthetic Execution Consistency flagged 64% of generation errors from a state-of-the-art generator, outperforming standard error detectors. The same research recommends isolating the verifier from the generator to break confirmation bias, and treating low-confidence cases as explicit abstentions rather than silent failures. Databricks makes the complementary point that "successful execution is not correctness," and recommends a validation gate that checks referenced objects against an approved list and confirms joins and grain against the metric definition before results reach a reader.

What controls decide how much SQL generation a platform can safely absorb?

Four controls cap generation volume and blast radius. The first is a validation gate that checks approved objects and columns, join and grouping match against the metric definition, and result ranges, as Databricks recommends (validating joins and grain against the approved metric definition, accessed March 2026). The second is an isolated verifier: the generate-then-verify paradigm is endorsed as an effective guardrail, with the verifier instance kept entirely separate from the generator to break confirmation bias, and low-confidence cases turned into explicit abstentions (a verifier that flagged 64% of generation errors, PVLDB vol. 19, 2026). That study's combined Round-Trip Critique and Synthetic Execution Consistency flagged 64% of errors from a state-of-the-art generator, which is a detection rate, not a reduction in wrong answers.

The third control is schema-scoped permissions, so generated queries can only reach approved schemas and objects. Snowflake executes generated SQL under RBAC (Cortex Analyst grounding generation in semantic views, accessed March 2026), and Cube applies access policies before a query reaches the warehouse for every person and every agent (Cube semantic layer, accessed March 2026). The fourth is selective generation, where coverage and selective accuracy are reported separately so a low-confidence request abstains instead of failing silently (VLDB verification paper, 2026).

The objection that gates slow self-service down is worth answering directly. The gates move work from debugging wrong numbers to settling definitions once, and a governed definition is reusable across every later request. Cross-vendor work on the Open Semantic Interchange, whose stated goal is solving inconsistent metric definitions and whose working group includes AWS, Collibra, DataHub, Domo, Firebolt, Informatica, Instacart, JPMC, Preset, Starburst and Strategy, treats shared definitions as the direction of the field rather than a per-team cost (the Open Semantic Interchange specification, 2025).

Conclusion

The evidence points one way: generation is the last step, not the first. Accuracy falls from 86.6% on Spider 1.0 to 5.68% on Spider 2.0-lite for the same model (Spider 2.0 results table, 2024), and enterprise warehouses push it lower still, with leading agentic frameworks reaching 10.8% on BEAVER (BEAVER enterprise benchmark, 2025). The dominant failure is not syntax but schema linking, at 29% to 49% in a survey of prior error studies (survey compiling text-to-SQL error studies, 2025). Both failures are definitional, which is why the two gates come first.

Apply the framework to your own request queue. For each request, ask whether the intent is unambiguous and whether the metric has a governed definition. If both are settled, reuse the governed artifact where one exists and generate only where it does not. If either is unsettled, resolve the definition before generation. Then cap volume with the four controls: a validation gate, an isolated verifier, schema-scoped permissions, and selective generation with explicit abstention.

The next step is to classify a week of requests against the routing table and see how many were reaching generation without a settled definition. That count is the size of the problem, and it is the number worth fixing first.

FAQs

What does "intent resolution" mean in text-to-SQL?

Intent resolution is the step that settles what a question actually asks before any SQL is written: the entity, grain, scope, time window, filters, exclusions, and expected output shape. Research on this step generates all semantically distinct interpretations first and maps each unambiguous one to a formal expression, separating misunderstanding the user's meaning from incorrectly expressing a known meaning (Disambiguate First, Parse Later, Findings of ACL 2025).

What is a metric contract, and why does it come before SQL generation?

A metric contract is the settled definition of how a number is computed: numerator, denominator, aggregation, and how nulls, duplicates, refunds, and late-arriving records are handled. It comes before generation because the same valid SQL can return different totals depending on grain and netting rules. Databricks separates measure definitions from the dimensions used to group and filter, so a metric is defined once and the engine generates the computation at runtime (defining a metric once in a metric view, accessed March 2026).

How accurate is AI-generated SQL on enterprise schemas?

Much lower than benchmark headlines suggest. An o1-preview agent framework solves 21.3% of Spider 2.0 tasks, and DAIL-SQL with GPT-4o reaches 5.68% on Spider 2.0-lite and 2.20% on Spider 2.0-snow (Spider 2.0 benchmark results, 2024). On private warehouses, leading agentic frameworks with GPT-5.2 reach only 10.8% (BEAVER enterprise benchmark, 2025). Validate any vendor figure in your own environment.

Why does a SQL query that runs successfully still return the wrong answer?

Because execution success is not correctness. A query can reference the wrong column, join at the wrong grain, or omit a filter, and still run cleanly. Spider 2.0's analysis of 300 failed generations found wrong schema linking at 27.6% and intricate query planning at 17.7% (error analysis of 300 failed generations, 2024). Databricks recommends a validation gate that checks approved objects, join and grouping match, and result ranges (Databricks validation guidance, accessed March 2026).

Which questions should never be sent to SQL generation?

Ambiguous questions that map to multiple valid SQLs, and unanswerable questions where no valid SQL exists given the schema. PRACTIQ was built around 1,802 such questions across four ambiguous and four unanswerable categories (PRACTIQ, NAACL 2025). Route these to clarification or to an explicit decline with a reason, rather than to generation.

Does a semantic layer guarantee correct answers?

No. A semantic layer guarantees consistent, traceable definitions, not correct results. dbt's MetricFlow generates SQL deterministically from the semantic specification and avoids fan-out and chasm joins (MetricFlow, the engine behind the dbt Semantic Layer, accessed March 2026), and Snowflake semantic views provide consistent business definitions across applications (Snowflake semantic views, accessed March 2026). No source reports a measured accuracy delta from adding one, so treat the gain as consistency, not a proven accuracy lift.

Do the gates slow self-service down?

They move effort from debugging wrong numbers to settling definitions once. A governed definition is reusable across every later request, and the verification research recommends reporting coverage and selective accuracy separately so low-confidence requests abstain rather than fail silently (VLDB verification paper, 2026). Cross-vendor work on shared metric definitions treats this as the direction of the field (the Open Semantic Interchange specification, 2025).

Does text-to-SQL work fine on a small schema?

Small, clean schemas are where benchmark scores are highest, and they are not representative of enterprise environments. Spider 1.0 databases average around 27 columns, while Spider 2.0 databases average over 800 (Spider 2.0 dataset statistics, 2024). The same model that scores 86.6% on Spider 1.0 scores 5.68% on Spider 2.0-lite, so a small-schema pilot will overstate what production will deliver.

About the publisher: Singdata is a Singapore-headquartered company that builds data agents on top of its AI-native data infrastructure and sells Singdata Lakehouse, a platform for enterprise data engineering and analytics. It has a commercial interest in AI-driven data tooling, so weigh its perspective on this topic accordingly. The guidance above stands on its own and does not depend on Singdata's products.