Text-to-SQL Is Not Solved; It’s Specified

Spider 1.0 execution accuracy vs. Spider 2.0 at launch (late 2024). Nothing about the models got worse — the benchmark stopped hiding the task.

• Databases average ~27 columns across 5 tables.
• Clean names like student.age.
• One obvious join path between any two tables.
• Leaderboard became a fight over decimal points.
• Spider 2.0: BigQuery datasets, Snowflake deployments, real dbt projects.

• Scale of schema — 1,000–3,000+ columns.
• Dialects — BigQuery, Snowflake, DuckDB, ClickHouse.
• Multi-step workflows — explore, sample, then write.
• Business logic absent from the schema — the killer.
• First three are engineering. The fourth is a missing specification.

• A schema dump alone can exceed 100K tokens.
• “Put the schema in the prompt” becomes a retrieval problem.
• The model must find the right three tables first.
• Collisions: amount, amount_usd, amt_net, total_amount_v2.
• Lexical retrieval turns actively misleading.

• “Active customer” = purchase in trailing 90 days…
• …excluding refunds and test accounts. No DDL says that.
• Meaning lives in dbt models, BI definitions, Slack, two analysts’ heads.
• A fluent guess is worse than an error — it survives review.
• No model scale fills in a definition never written down.

• 12,751 questions over 95 databases totaling 33 GB.
• Deliberately “dirty” values.
• External knowledge field: “revenue means price * quantity”.
• First benchmark to admit the question underdetermines the SQL.

• Audits: a substantial fraction of gold queries wrong or ambiguous.
• Hired experts couldn’t reliably produce “the correct SQL” either.
• Inter-annotator agreement on the right SQL: far below 1.0.
• An 80%-accurate model is graded against a noisy key.
• Underspecified tasks have no ceiling to converge to.

Accuracy of state-of-the-art text-to-SQL on MIT’s own warehouse, with realistic questions from its actual users. Not adversarial — merely ordinary.

• Assistant over a 2,400-column SAP-derived warehouse.
• “What was Q3 churn?” — three different numbers.
• All valid SQL; four churn-adjacent columns, three analyst generations.
• Post-mortem: the company had no agreed definition of churn.
• The model faithfully reproduced the org chart’s disagreement.

• Only hallucinated column is a “model is dumb” failure.
• And it is the most fixable.
• The other three are failures of specification and contract.
• The schema tells you what is storable.
• Nothing machine-readable tells you what is meant. Thursday we fix that.

• A relational schema is a contract about structure.
• Enough for fifty years — humans carried definitions in their heads.
• Agents carry nothing.
• The meaning contract must be written, machine-readable, queryable.
• That artifact has a name: the semantic layer.

• Metrics — named, versioned computations, filters baked in.
• Dimensions — the legal slices, each bound to one column.
• Grain — declared row meaning: per order, per order line.
• Governed joins — explicit path graph with cardinalities.
• Grain makes fan-out double-counting detectable before execution.

# semantic_layer/metrics/net_revenue.yml
metric: net_revenue
model: fct_order_lines        # grain: one row per order line
expr: sum(amount_usd) - sum(refund_amount_usd)
filters:
  - field: account_type
    operator: not_in
    values: [internal, test]
dimensions: [fiscal_month, region, plan_tier]
joins:
  - { to: dim_customers, type: many_to_one, on: account_id }
owner: finance-analytics      # a human team, not a model

• A queryable contract, not documentation.
• The agent never reads a wiki and freestyles SQL.
• Illegal requests are rejected at compile time.
• A compile error costs a retry.
• A plausible wrong number costs trust — the actual product.

Anthropic’s self-service analytics: raw schema access vs. curated “skills” encoding metric definitions, join guidance, and pitfalls. Same model, 4.5× the accuracy — the expensive ingredient was analyst time, not GPUs.

Cube’s paired benchmark: one ~4 KB document of metric and join definitions moved accuracy +17 to +23 percentage points — while a schema dump alone can be 100K tokens of mostly noise.

• dbt’s production argument: route agent queries through the layer.
• More correct answers on answerable questions.
• Unanswerable ones become loud compile failures…
• …instead of silent wrong numbers.
• Fewer confident wrong answers is also accuracy.

• Attempt resolution top-down; fall through deliberately.
• “Resolved via semantic layer” ≠ “inferred from a 2024 corpus query.”
• The query corpus is the warehouse’s folklore, not its law.
• Free-text context: richest in meaning, weakest in guarantees.
• Surfacing provenance is what lets humans calibrate trust.

• The 2025 shortcut: have the model generate the layer.
• Its inputs are exactly the untrusted layers of the hierarchy.
• It recovers all four churn definitions, enshrines the most common.
• You haven’t removed the ambiguity — you’ve laundered it.
• Deciding is not a prediction task.

• Models: mine corpora, diff metric variants, flag undeclared grain.
• That’s drafting. The commit turns a draft into a contract.
• It needs an owner who can be wrong in a way that matters.
• owner: finance-analytics — the entire governance model in seventeen characters.

• “How many active customers last month?” — write four defensible SQL readings. Which failure bin does each silent choice land in?
• Which taxonomy bins can a 4 KB semantic layer close on the 400-column warehouse — and which survive?
• A bootstrapped layer can be internally consistent yet uniformly wrong. How would you measure that, separately from execution accuracy?

• Spider 2.0 — Lei et al., ICLR 2025. The 86→17 cliff, quantified; focus on the error analysis.
• BIRD — Li et al., NeurIPS 2023. The “external knowledge” design decision; be skeptical of the gold annotations.
• How Anthropic Enables Self-Service Data Analytics with Claude — engineering blog, June 2026. The 21→95 result; a semantic layer in everything but name.