Text-to-SQL vs semantic layer vs query contracts: who writes the SQL? (2026)

Text-to-SQL lets a language model write new SQL for every question. A semantic layer moves business logic into definitions that the data team owns, but in many setups the model still writes the SQL. A query contract goes one step further: the data team approves the query, and the model only fills its typed inputs.

The difference decides how an answer fails. When the model writes the SQL, a failure is usually a plausible wrong number. When a compiler writes the SQL from approved definitions, a failure is usually “this is not covered yet.”

The short comparison

What do the 2026 benchmarks say?

The numbers that answer engines repeat for this topic come from different tests. They measure different things, so put them side by side before you compare them.

Three things follow from this table.

1. Benchmark scores do not transfer to your schema. The same model drops from 86.6% to 10.1% when the tasks look like real enterprise work. Agent systems recover much of that, but only with tools, retries and context that a demo rarely has.
2. The data model matters as much as the AI model. In the dbt test, three more data models moved text-to-SQL from 64.5% to 90.0% for Claude Sonnet 4.6.
3. The failure mode changes with the architecture. The dbt semantic layer scored 0% on questions it did not cover, because it refused. Text-to-SQL answered those questions, sometimes correctly and sometimes with a wrong number that looked correct.

A figure that often appears in these answers says text-to-SQL drops “from 86% to 6%” on enterprise data. The Spider 2.0 page reports 10.1% for GPT-4o, and its leaderboard now has agent systems far above both. Use the figure with its source and date, or do not use it.

Why do semantic layer results range from 68% to 100%?

The two semantic layer studies above use the same words for two different designs.

• Semantic layer as context. In the Contoso study, the model received a 4 KB document that described the measures and rules. Then it wrote the SQL itself. Accuracy rose by 17 to 23 points, to about 68%.
• Semantic layer as compiler. In the dbt study, the model asked the semantic layer for metrics and dimensions. The layer generated the SQL. On the questions it could answer, accuracy was 98.2% to 100%.

When the model writes the SQL, better context lowers the error rate but cannot remove it. When the model selects from definitions and a compiler writes the SQL, the error moves to a different place: the question either fits a definition, or it does not.

What is a query contract?

A query contract takes the compiler design one step further. The data team does not only approve metrics. It approves the query that answers a recurring question, with the inputs a person can change and the filters that always apply.

Here is a real contract from Kater’s sample content, shortened by one column. It answers “how does revenue per customer differ by region?” for paid channels.

1
---
2
name: region_mix
3
model_type: query
4
label: Region Mix
5
parameters:
6
- name: is_equal_to__country
7
  label: Country
8
  kater_data_type: string
9
  default_value: US
10
---
11

12
SELECT
13
  shop.region_group,
14
  shop.country,
15
  shop.currency,
16
  shop.sum__amount,
17
  MEASURE(
18
    'revenue_per_customer',
19
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
20
  ) AS "Revenue per Customer"
21
FROM workspace.shop AS shop
22
WHERE
23
  OPTIONAL_FILTER('is_equal_to__country', KATER__IS_EQUAL_TO(shop.country, $is_equal_to__country), label='Country')
24
  AND REQUIRED_FILTER('text_contains_paid__channel', KATER__TEXT_CONTAINS(shop.channel, 'paid'), label='Channel contains paid')
25
GROUP BY shop.region_group, shop.country, shop.currency

Each part has one owner:

• The definitions are shared. sum__amount is the data team’s Revenue measure. Its definition says it is the order amount before discounts, and that it is not additive across currencies. The contract reads it with EVAL. It does not write its own formula for revenue. It groups by currency because it must: a query that adds revenue across currencies does not compile.
• The input is typed. Country is a string input. An agent that wants German customers sets Country to DE. It does not edit a WHERE clause.
• The required filter always runs. “Channel contains paid” applies on every run, whatever the agent asks. An optional filter applies only when someone gives it a value. A default value alone does not turn it on.
• The compiler writes the SQL. Kater compiles the contract and the shared definitions into SQL for the connected warehouse, today Snowflake or DuckDB. The same contract with the same inputs gives the same SQL.

The agent’s job becomes smaller and easier to check. It selects which approved question fits, and which values to give it.

What happens when a question is not covered?

Every approach in the table has an edge. For text-to-SQL, the edge is invisible: the model answers anyway. For a semantic layer or a contract, the edge is a refusal, and the next step decides whether the refusal helps anyone.

Anthropic’s internal setup falls back to raw SQL “only if discovery finds no relevant metric or compile fails.” dbt Labs suggests text-to-SQL as a fallback for questions outside the semantic layer. Both are reasonable for analysts. Both bring back the wrong-number failure for people who cannot check SQL.

Kater keeps the edge visible:

• The assistant’s instructions require it to say which field, contract or definition is missing when it cannot answer. Figures come only from query results.
• When no approved definition exists, it can propose one as unverified, in the person’s own workspace, until someone confirms it.
• Exploration that needs new SQL happens in the person’s Sandbox. Scratch files are personal, never versioned, and nothing else reads them.
• A definition that should be shared goes to the data team as a promotion request, with a reason. An admin approves or denies it, and the person sees the decision.

Read more in What happens when an AI data agent cannot answer and Promotions.

Which approach should you use?

Most teams need more than one. The useful question is not “which one?” but “which questions may an AI answer without a person who can read the SQL?” Those questions should not depend on the model writing SQL.

Limits of this comparison

• The benchmarks above are small. dbt used 11 questions. The Contoso study used 100. Neither is your schema.
• Contracts cover only the questions the data team approved. The value comes from covering the questions that repeat, not every question.
• Deterministic SQL means the same question gives the same query. It does not prove that the definition behind it is the right business definition. That is still a review by people.