Book a 30-min call
← cd ../blogs
$ cat posts/semantic-layer-not-better-model-nl-to-sql.mdx

The problem with enterprise NL-to-SQL is specification, not model capability

July 23, 2026 · ImmovableTech Team

  • Data Engineering
  • Production AI
  • Evaluation

The gap is in the specification, not the model

Every natural-language-to-SQL deployment we have watched lose its users lost them the same way. The model produced syntactically perfect SQL that answered a different question from the one that was asked. Not an error, not a timeout, not a malformed query — a number. A plausible one, with the right order of magnitude and a sensible trend. It went into a weekly report and stayed there until someone noticed it did not tie out to finance, and after that nobody trusted anything the system said.

That is worse than a failure, because a failure is visible. This is a system that is wrong quietly, at a rate you cannot measure, in a direction nobody can see.

The usual diagnosis is that the model is not good enough yet, and the usual prescription is to wait for the next one. Both are wrong. Frontier models write good SQL now. What they do not have is your organisation’s definition of revenue, and no amount of pre-training will give them one.

What the benchmarks stopped measuring

Spider 1.0 is effectively solved and it stopped being informative years ago. The Spider 2.0 team built a successor precisely to measure the thing that broke: 632 real enterprise workflow problems over warehouses with more than a thousand columns each, spread across BigQuery, Snowflake and local engines, where gold queries routinely run past 100 lines with CTEs and window functions. On the benchmark authors’ own numbers, GPT-4o solves 86.6% of Spider 1.0 and 10.1% of Spider 2.0. o1-preview solves 17.1%. The model did not get worse between those two rows. The task got honest.

The collapse of prompting pipelines on that benchmark is the part worth sitting with. On the 547-task Spider 2.0-Snow split, DAIL-SQL with GPT-4o scores 2.20%. Spider-Agent with o1-preview reaches 23.58% and ReFoRCE 31.26%. These are not bad methods — they were competitive when schemas were small and questions mapped cleanly onto single queries. They fall over when the schema is large, the column names are opaque and the question has four defensible readings.

We should be plain that this implicates our own work. The NL-to-SQL server on our restaurant intelligence platform uses a DAIL-SQL-style approach, and it does work there, because the question space is narrow and we curated it. The general lesson does not transfer, and we would not build the same thing against a thousand-column warehouse today.

Constraining the output space beats improving the model

The most reliable accuracy lever we have found is not choosing a better model, it is giving the model a smaller space to be wrong in. We ran a small version of this experiment on the same restaurant platform and wrote it up in our MCP production notes: the first version of the tool took a free-form SQL string and produced valid SQL about 60% of the time, and restructuring the same tool to take structured parameters — table, columns, filters, group-by — took it to 94%. The model’s reasoning did not change. Its opportunity to invent a column name did.

A semantic layer is that move applied one level up, and the published results are larger than we expected. A paper from June 2026 describes an NL2SQL agent that never sees the raw schema at all. It queries a curated semantic layer through a compact intermediate representation, a deterministic compiler turns that into dialect-correct SQL, and the agent composes its final query only from identifiers lifted out of compiler output. On Spider 2.0-Snow it answers 515 of 547 tasks correctly — 94.15% execution accuracy, third on the official leaderboard, against 2.20% for DAIL-SQL on the same 547 tasks.

Read that comparison carefully, because the authors do. The semantic layer encodes per-database domain knowledge that the zero-shot baselines never receive, so the number measures the system, not the model. Their own per-backend table shows the mechanism: accuracy exceeds 94% on every database class except native Snowflake, where it is 55.6% on 18 instances, and they attribute the gap to semantic-layer maturity rather than to the agent. They also name the risk directly — curating descriptions against an evaluation set drifts towards encoding expected answers, which raises benchmark accuracy while degrading performance on questions you have not seen.

dbt Labs measured the same effect from the opposite end, on a much smaller and more controlled setup. In their April 2026 rerun of an 11-question insurance benchmark over 15 tables, each question run 20 times, text-to-SQL on a normalised schema went from 32.7% with a 2023-era model to 64.5% with current ones. The semantic-layer path went from 60.5% to 72.7%. Then they asked a model to add the minimum modelling needed to cover the questions MetricFlow could not express. It wrote three dbt models. Accuracy through the semantic layer reached 98.2% and 100% depending on the model, and raw text-to-SQL over the same new tables improved to between 84.1% and 90%.

Three models bought more accuracy than any model upgrade in the study. And two frontier models with different architectures scored identically at 64.5% on the raw schema, which tells you where the remaining error lives. Two caveats the post states plainly and we will repeat: eleven questions is a small sample, and they loaded the entire schema as context, which is not available to you at real warehouse scale.

What a semantic layer actually has to contain

Calling a folder of SQL views a semantic layer does not make agent answers correct. Five things have to be present, and the fifth is the one teams skip.

Metric definitions come first: the aggregation, the filters baked into it and the grain it is valid at, expressed once. Then an entity and join graph, declared rather than rediscovered per query, so that the fan-out that double-counts revenue across a one-to-many join is impossible to express rather than merely discouraged. Then allowed dimensions per metric, because most metrics are only meaningful along some of the axes in your warehouse and an agent given the full cross-product will happily group churn rate by invoice line. Then time-grain rules, which are where fiscal calendars stop matching calendar quarters and where a metric that is valid daily silently becomes nonsense when summed to a month.

The shape is roughly this, and the specific syntax varies by tool:

metric:
  name: net_revenue
  agg: sum
  expr: order_total - refunds - credits
  agg_time_dimension: recognised_at
  granularity: day
  allowed_dimensions: [region, product_line, fiscal_quarter]
  owner: finance-systems
  policy: role_in(['analyst', 'finance'])

The fifth thing is tests, and a metrics layer with no tests is just a slower prompt. Definitions rot the moment an upstream column is renamed, and a semantic layer that compiles but no longer matches the physical tables produces exactly the failure mode it was built to prevent. dbt’s MetricFlow runs three validation layers — YAML parsing, semantic graph constraints such as every measure having a valid time dimension, and data-platform validation that executes the generated SQL against real tables — and it belongs in CI, not in someone’s terminal:

dbt parse && dbt sl validate

The tooling to do this is not exotic. dbt’s Semantic Layer defines metrics as code next to the models; Cube Core is an Apache-2.0 semantic layer that serves the same definitions over SQL, REST, GraphQL and an MCP endpoint with row-level security applied before the query runs; Malloy’s Publisher is an MIT-licensed model server exposing governed models over REST and MCP; and both Snowflake and Databricks now ship warehouse-native semantic objects aimed at their own AI features. The choice between them is mostly about how far your agents reach beyond one platform. It is not the hard part.

Evaluate on agreed numbers, not on reference SQL

Build your eval set out of questions with agreed correct answers, not questions with reference queries. There are many correct queries for a business question and exactly one correct number, and grading SQL similarity measures the wrong object.

There is also a stronger reason not to trust reference SQL, published in VLDB volume 19 in January 2026. An expert audit found annotation errors in 263 of 498 BIRD Mini-Dev examples — 52.8% — and in 76 of the 121 Spider 2.0-Snow problems with public ground truth, 62.8%. The error categories are the same ones that break enterprise deployments: the query not matching the question’s intent, the query not matching the data, missing domain knowledge and questions with more than one reading.

Re-running all 16 open-source agents from the BIRD leaderboard against a corrected subset moved execution accuracy by −7% to +31% in relative terms and moved rankings by up to nine positions in either direction. The authors’ sharpest result is a correlation: rankings on the uncorrected subset track the full development set closely (Spearman’s ρ = 0.85), while rankings on the corrected subset barely correlate with it at all (ρ = 0.32). The leaderboard is measuring agreement with the annotator, not correctness.

If benchmarks with funded annotation teams are wrong half the time, an eval set your team writes in an afternoon from reference SQL will be worse. The version that survives contact with production is fifty to two hundred questions with numbers that finance, ops and the analytics team have all signed off on, versioned in the repository, regenerated on every definition change.

The cost nobody puts in the proposal

Building the semantic layer is the hard, political, unglamorous work of getting an organisation to agree on what its words mean, and no tool removes that. Deciding whether customers includes churned accounts, whether a trial counts as active, which of three definitions of active user survives and who owns the answer — those are meetings, not migrations. The engineering afterwards is comparatively trivial.

This is the actual reason raw text-to-SQL keeps getting chosen. It lets you ship something without having the argument. Every ambiguity that would have surfaced in a definitions review instead gets resolved silently, per query, by a model guessing from column names. That is not a shortcut around the specification problem. It is the specification problem, deferred until it reaches a board deck.

What we’d do differently

We would build the eval set out of numbers before writing a line of agent code. Our first evaluation harness on an NL-to-SQL project graded generated SQL against reference SQL we had written ourselves, which meant a query could pass our grader and still answer the wrong question — and did, on anything involving a revenue-shaped column, of which that warehouse had four. We only found it because a manager queried the same figure two ways and got two answers. Reference SQL encodes one analyst’s reading of an ambiguous question, and grading against it launders that reading into a green test.

We would also stop treating the semantic layer as a follow-up phase. On more than one engagement we scoped modelling as something to do after the agent worked, on the theory that we would learn what to model from real questions. What actually happened is that the agent shipped, produced answers people acted on, and the modelling work then had to be done retroactively against a system users had already stopped believing. Trust is much cheaper to keep than to rebuild.

The last one is smaller and we still get it wrong. Semantic layers need owners with names attached, per metric, in the file. Every definition we left unowned drifted within two quarters.

References

We build governed semantic layers behind NL-to-SQL systems as part of our Data Engineering practice. Talk to us if your teams cannot agree on what “active user” means and your dashboards show it.