Text-to-SQL Evaluation That Catches Silent Wrong Answers

Your copilot cleared Spider-class benchmarks at 89% execution accuracy, the demo landed, and three weeks into the pilot a finance lead asked for EMEA net revenue excluding intercompany transfers. The model wrote SQL, the warehouse returned rows in under a second, and a quietly wrong number went into a board deck. No syntax error, no empty result, no warning. That gap between a leaderboard score and a number you can defend in design review is what serious text-to-SQL evaluation exists to close, and most teams shipping NL2SQL copilots have not built it yet.

The gap is structural, and it is now quantified. ESQ-Bench, an enterprise oracle benchmark for NL2SQL, loaded identical seed data onto Oracle, PostgreSQL, MySQL, and SQL Server and found that models reporting above 89% execution accuracy on Spider and BIRD degrade sharply as enterprise schema complexity rises. The dominant failure at harder tiers was queries that executed cleanly and answered a subtly different question. This article maps that failure class, gives you an oracle-based evaluation design that catches it on your own warehouse, and sets a severity-weighted acceptance bar you can actually defend.

What 89% Execution Accuracy Actually Measures

Execution accuracy has a narrow, precise definition. You run the model's SQL and a human-approved gold query against the benchmark's database, compare result sets, and score a match. Spider's evaluation setup works this way, and BIRD follows the same pattern on larger databases. The metric certifies exactly one construct: given a small academic schema and a modest set of test rows, the generated SQL produced the same answer as one fixed gold query.

That construct excludes almost everything an enterprise rollout depends on.

• It does not exercise your dialect. Benchmark schemas are SQLite-flavored, with SQLite functions and SQLite semantics.
• It does not exercise your schema. Dozens of cleanly named tables behave differently from hundreds of tables, layered views, and columns named under three different conventions.
• It does not encode your metric definitions. A gold query in Spider knows nothing about what your finance team counts as net revenue.
• It cannot detect silent divergence, because the comparison stops at "result sets match on this data."

There is a second, quieter problem. Gold queries are brittle oracles. A candidate can coincide with gold on the benchmark's small dataset while diverging on edge cases, scoring a false pass. A correct alternative formulation can miss on that same data, scoring a false fail. Later evaluation work attacks this with larger test suites, which helps inside the benchmark and does nothing for your warehouse.

Why Spider and BIRD Scores Do Not Transfer to Your Warehouse

Three gaps separate a leaderboard number from enterprise correctness, and each maps to a concrete failure you will hit in production.

Gap 1, dialect anchoring. The Spider and BIRD benchmarks run on SQLite-style schemas, so they under-exercise the function signatures and semantic conventions of the engines you actually run. Research on benchmark transfer argues this SQLite anchoring is a real limitation, not a nitpick: models learn date functions, cast behavior, and NULL conventions from the dialect the benchmark speaks, then import those habits where they do not hold. SQL dialect differences between Postgres, Snowflake, and BigQuery are not cosmetic. The resulting Snowflake, BigQuery, and Postgres dialect errors often execute rather than throw.

Gap 2, schema complexity. Academic schemas top out at dozens of tables. ESQ-Bench built six populated enterprise schemas with 465 tables and 164,682 rows, deployed the same seed data to four engines, and ran 550 gold-validated question-query pairs across three complexity tiers. GPT-4o with schema-linked prompting fell from 79.8% to 60.3% to 57.2% execution match across those tiers, with exact match below 7% throughout. Claude Sonnet 4.6 fared better at 87.4%, 74.9%, and 68.7%, and still lost roughly 19 points from easiest to hardest tier. An open-weight Llama 3.2 reached 13.3% bank-wide. The ESQ-Bench paper reports all of these figures. Enterprise text-to-SQL degrades with schema complexity, monotonically, on every model tested. Notably, Snowflake and BigQuery are not even in ESQ-Bench's engine list, so if your stack lives there, the measured degradation is evidence of a pattern rather than a covered case.

Gap 3, the oracle is theirs. Even a perfect Spider score certifies agreement with gold queries written for those schemas. Enterprise correctness is defined by your conventions, your calendar tables, your intercompany flags. A rollout justified by a public score is calibrated to a different world, which is a large part of why text-to-SQL fails in production.

Silent Semantic Divergence, the Failure That Never Throws

Sort copilot failures by the signal they give you.

• Loud failures are syntax errors, unknown columns, permission denials. The engine rejects the query in milliseconds, the user sees an error, and trust erodes honestly.
• Distorted failures return something visibly broken, like an empty table or a nonsense magnitude, and get caught by suspicion.
• Silent semantic divergence executes without error, returns plausible rows in the right shape, and answers a different question than the one asked.

Silent divergence is the worst class because of verification asymmetry. The user asked in English precisely because they cannot audit SQL. The only artifact they see is a plausible table with confident formatting, and plausible wrong numbers propagate at dashboard speed. "Revenue by quarter" answered with booked-date revenue instead of recognized-date revenue has the right shape, the right order of magnitude, and the wrong basis for every decision made from it.

This is why ESQ-Bench treats silent divergence as a first-class metric alongside exact match, execution match, and execution rate. Its failure analysis found that wrong-result semantics dominate at the higher complexity tiers, precisely the tiers that resemble a real warehouse. If your eval cannot tell ran-and-matched from ran-and-correct, it measures execution, not correctness.

Four Ways Generated SQL Answers the Wrong Question

A taxonomy with one example per mode covers most of the silent wrong SQL results from AI copilots we have seen on warehouse stacks.

1. Dialect function drift

Snowflake's DATEDIFF('day', a, b) computes b minus a, with the part first. BigQuery's DATE_DIFF(a, b, DAY) computes a minus b, with the part last. Both engines accept queries built with the other's habits, which is exactly what makes this dangerous.

-- intended, Snowflake: days from signup to cancellation
SELECT AVG(DATEDIFF('day', signed_up_at, cancelled_at))
FROM subscriptions;

-- ported to BigQuery with the argument order carried over
SELECT AVG(DATE_DIFF(DATE(signed_up_at), DATE(cancelled_at), DAY))
FROM subscriptions;

The second query is valid BigQuery that returns negated durations, and every average built on it flips sign. DATE_TRUNC carries the same disease, with its argument order reversed between BigQuery and Postgres or Snowflake.

2. NULL filtering and implicit casts

SELECT SUM(net_amount) FROM orders WHERE promo_code != 'NONE';

Three-valued logic drops every row where promo_code is NULL, in every engine, without warning. If a large share of orders carry NULL codes, the total quietly understates revenue. Cast semantics compound this: Postgres comparisons of mismatched column types tend to fail loudly, MySQL coerces silently, and BigQuery's coercion rules follow their own documented behavior. The same predicate can be loud on one engine and silent on another.

3. Temporal boundary logic

WHERE event_date BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE

BETWEEN is inclusive on both ends. A daily job using it against a metric defined on a half-open window (>= start AND < end) double-counts the boundary day. Add timezone defaults and week-start conventions, both of which vary by engine and configuration, and you get plausible rows that are wrong by one day or one whole weekly bucket.

4. Row-limit truncation

SELECT region, SUM(net_amount) AS revenue
FROM fct_orders
WHERE order_date >= '2026-01-01'
GROUP BY region
ORDER BY revenue DESC
LIMIT 10;

Asked for revenue across all regions, the model returned the top 10 of 17. The slice looks complete, the sums look plausible, and the missing seven regions are invisible. The nastier variant comes from the copilot's own safety guard appending LIMIT 100 to whatever the model writes, truncating a result whose aggregation already happened at the wrong stage. Treat injected limits as first-class suspects whenever an answer is plausible and possibly partial.

Your Text-to-SQL Evaluation Starts With Tier 1 Oracles

The highest-trust component of the harness is deterministic: question and gold-SQL pairs mined from your own warehouse, asserted in CI, with no judge involved. To evaluate text-to-SQL on your own database schema, build it in five steps.

1. Mine query logs. Pull 30 to 90 days from Snowflake's QUERY_HISTORY, BigQuery's INFORMATION_SCHEMA.JOBS_BY_PROJECT, or Postgres pg_stat_statements. These are the questions your business actually asks.
2. Canonicalize and dedupe. Strip literals, normalize whitespace and aliases, collapse duplicates, then rank by frequency times business criticality. Hundreds of raw queries commonly reduce to a few dozen canonical patterns.
3. Write the question. For each surviving query, write the English question it truly answers, metric conventions included. This is where "excluding intercompany" becomes an explicit assertion instead of a hope.
4. Enforce coverage rules. Every fact table, every dialect-sensitive function family from the taxonomy above, and every recurring join pattern gets at least one case. Coverage is a rule, not an aspiration.
5. Freeze the oracle. Run gold SQL against a pinned data snapshot, store expected results or hashes with the data version, and re-derive them on every snapshot refresh so the oracle tracks the warehouse.

One worked case, trimmed:

-- question: EMEA net revenue last quarter, excluding intercompany transfers
SELECT SUM(o.net_amount)
FROM fct_orders o
JOIN dim_entity e ON o.entity_id = e.entity_id
WHERE o.booking_status = 'confirmed'
  AND o.record_type <> 'IC'
  AND e.region = 'EMEA'
  AND o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE - 90);
-- expected (snapshot v37, illustrative): one row, 41238004.55

A candidate passes only if it reproduces that number on that snapshot. A public NL2SQL benchmark cannot measure these classes, because the schema, the dialect, and the metric definitions are yours.

Tiers 2 and 3, Equivalence Checks and Calibrated Judges

Tier 1 cannot cover everything. Real user questions drift beyond your mined set, and many have no gold query. The remaining tiers trade trust for coverage, in a known order.

Tier 2, execution equivalence

Where gold SQL exists, compare executions rather than strings, and beware coincidental matches. A candidate can agree with gold on today's data while diverging on edge cases. The test-suite execution evaluation approach attacks this by running both queries against multiple perturbed copies of the database, so queries that merely coincide on one dataset stop passing. Deciding semantic equivalence for SQL pairs without gold queries is an open research problem, and semantic equivalence scoring frameworks from recent NL2SQL work give you a place to borrow: compare candidate and reference on filter set, grain, time window, and metric formula, rather than trusting surface similarity.

Tier 3, the calibrated judge

An LLM judge with a structured rubric (same filters, same window, same grain, same metric formula, given the schema) scales cheapest and is weakest alone. Before trusting one, hand-label 100 to 200 pairs from your own suite, measure agreement against your labels, and iterate the rubric until agreement is high and stable. The judge calibration research is blunt on this point: calibration effort, not model size, decides whether judge verdicts are worth anything. The trust order is fixed. Deterministic oracle first, execution equivalence second, judge last, and only after it earns its calibration numbers.

Severity Weighting and an Honest Acceptance Bar

Not all wrong answers cost the same, so a single text-to-SQL accuracy number cannot be your gate. Score every failure by business impact and report the rates separately.

Severity	Definition	Example	Pre-launch gate
S1, critical	A number someone acts on is wrong	Quarterly revenue understated	Zero on Tier 1 suite
S2, major	Right metric, wrong scope or window	EMEA total includes LATAM entities	Under 1%, trending down
S3, cosmetic	Format, ordering, labeling	Cents displayed as dollars	Tracked, no gate

An aggregate claim like "92% accurate" conceals which 8% failed and whether any of it was S1. The report you can defend in design review reads like this, with numbers illustrative until your own suite produces them: Tier 1, zero S1 in 140 cases and three S2; Tier 2, silent divergence at 2.1%; judge agreement at 0.87 against 150 hand-labeled pairs. That report names the residual risk. A headline number hides it.

Run the Eval Continuously, Not Once

A text-to-SQL evaluation you run once is a screenshot of a moving system. Keep it alive in three places.

• CI gating. Prompt changes, model swaps, and schema migrations each trigger a full Tier 1 run. A renamed column should fail oracles loudly before it ships, not after.
• Snapshot discipline. Re-derive expected results on a fixed cadence, and tag failures as data drift or code drift so a stale oracle never masks a real regression.
• Post-launch sampling. Sample real user questions weekly, run them through Tiers 2 and 3, have a human confirm flagged divergences, and promote confirmed cases into Tier 1. The suite compounds, and last quarter's silent failure becomes next sprint's deterministic test.

When a vendor arrives with a benchmark claim, the interrogation is short.

• Which engine and dialect was it run on?
• Whose schemas, and how many tables?
• What served as the oracle?
• Was silent divergence measured as its own metric?
• Was any judge calibrated against hand-labeled data?
• How much data did the execution comparisons actually run on?

The 89% headline is a real number about a different world, one with small academic schemas and a fixed gold query per question. Your users live in this one. The EMEA question from the opening would have been caught by a single Tier 1 case encoding "excluding intercompany" as an assertion. Build that case before the board deck, not after.