Semantic Models: the Text-to-SQL Accuracy Lever
The governed metric contract is the single biggest accuracy lever on AI-written SQL, and reading one and implementing it exactly is the data engineer's half of the job.
The model is not the lever
The obvious way to make an AI write better SQL is to buy a better model. The measurements say otherwise, and the gap is not subtle.
- dbt Labs published a text-to-SQL benchmark in April 2026. Grounding generation in a semantic layer lifted Claude Sonnet 4.6 from 90.0% to 98.2% and GPT-5.3-Codex from 84.1% to 100%. Raw text-to-SQL over a bare schema, meanwhile, improved from only 32.7% in 2023 to 64.5% in 2026 across three years of model releases.
- Spider 2.0 (ICLR 2025) ran an o1-preview agent against two suites: 91.2% on academic benchmarks, 21.3% on enterprise-realistic workflows. Same model, two benchmark suites. The difference was the messiness and ungoverned meaning of a real warehouse.
- Snowflake reported Cortex Analyst moving from 57% to 78% on BIRD-SQL once a semantic model was attached.
Three independent teams, one conclusion: the context you hand the generator moves accuracy far more than the generator does. That context is an artifact, somebody authors it, and on most teams that somebody is a data engineer.
What a semantic model actually contains
A semantic model (dbt calls it a semantic layer, Snowflake a semantic model, Looker a LookML model) is a contract that names the business meaning of your tables. It is authored as a versioned text spec, YAML in dbt and in the OSI standard, LookML in Looker, then compiled and served to both dashboards and generators.
| contract element | what it pins down | the drift it prevents |
|---|---|---|
| logical table | which physical table a metric reads | one team on orders, another on orders_v2 |
| grain | one row per what, in which time bucket | daily rows summed into a monthly chart twice |
| metric definition | the exact expression, gross minus refunds | revenue meaning gross in one deck and net in the next |
| required filter | the predicate every implementation applies | cancelled and pending orders counted as sales |
| join path | the one approved way to reach another table | a fan-out join that multiplies the measure |
| verified query | a known-good answer to a known question | the generator inventing a shape nobody checked |
In January 2026 the Open Semantic Interchange (OSI) v1.0 specification landed, led by Snowflake with dbt Labs, Databricks, and Microsoft among the contributors, so that one metric definition can be shared across vendors instead of re-authored per tool. That is the direction of travel: the definition is becoming a portable asset, not a setting inside a BI tool.
Metric drift is the failure it kills
Omni's April 2026 analysis found 81.2% of text-to-SQL failures are schema or semantic, not syntax. The headline symptom has a name: metric drift, which is the same question producing different revenue numbers depending on who asked and which tool answered.
Drift is not an AI problem. It predates AI by decades. Finance defines revenue as complete orders net of refunds; the growth dashboard sums everything with a dollar sign on it including pending carts; a Monday morning is then spent reconciling two numbers that were never the same metric. What AI changed is the blast radius, because a generator will happily produce forty variants of that query per day instead of one per quarter.
The contract fixes it by making the definition the thing you implement, not the thing you infer. When net_revenue says grain monthly, definition SUM(gross_usd - refund_usd), required filter status = 'complete', there is exactly one correct query, and both the human and the generator are graded against it.
Reading the contract, then implementing it
Below, the contract is a queryable table. That is not a teaching shortcut: a compiled semantic model IS metadata, and the platforms that serve one expose it exactly this way so tools can read it. Your job in the exercises is the data engineer's half of the loop, which is to turn a contract row into the query it describes. In SQLite the month grain comes from strftime('%Y-%m', order_dt), and the required filter goes in the WHERE clause where it belongs.
Common mistake: implementing the definition but skipping the required filter. SUM(gross_usd - refund_usd) over every row is a real number, it is just not net_revenue. A metric is the expression AND its filter AND its grain, and dropping any one of the three produces a query that runs, returns plausible dollars, and is wrong.
Interview nuance: if you are asked how you would make an AI assistant reliable on your warehouse, the answer that lands is "ground it in a semantic layer, because the benchmarks say context beats model choice," followed by naming what the layer holds: metrics with exact filters, predefined join paths, verified queries. Only about 5% of teams have a semantic model while 89% report modeling pain, so being the junior who can author and implement metric contracts is unusually visible.
On a real platform this differs. Here the contract is four rows in a SQLite table you read by eye. In production it is YAML in a dbt project compiled by MetricFlow, or a Cortex semantic model file, version-controlled beside the transformations and served through an API that dashboards and generators both call. The reasoning is identical: find the metric row, read its grain, definition, and required filter, and implement exactly that.
CREATE TABLE orders (
order_id TEXT,
customer_id TEXT,
order_dt TEXT, -- ISO timestamp; the month grain comes from strftime over this
status TEXT, -- complete | cancelled | pending
gross_usd REAL,
refund_usd REAL -- refunded back to the customer; 0.00 when nothing was returned
);
INSERT INTO orders (order_id, customer_id, order_dt, status, gross_usd, refund_usd) VALUES
('o-1001', 'c-101', '2026-01-05 09:14:00', 'complete', 1200.00, 0.00),
('o-1002', 'c-102', '2026-01-11 16:02:00', 'complete', 840.50, 150.50),
('o-1003', 'c-101', '2026-01-19 11:38:00', 'cancelled', 500.00, 500.00),
('o-1004', 'c-103', '2026-01-27 08:55:00', 'pending', 640.25, 0.00),
('o-1005', 'c-102', '2026-02-03 13:21:00', 'complete', 950.00, 0.00),
('o-1006', 'c-104', '2026-02-09 10:07:00', 'complete', 1500.00, 300.00),
('o-1007', 'c-103', '2026-02-17 15:44:00', 'complete', 700.00, 75.00),
('o-1008', 'c-105', '2026-02-24 09:30:00', 'cancelled', 1100.00, 1100.00),
('o-1015', 'c-105', '2026-02-27 10:05:00', 'cancelled', 400.00, 400.00),
('o-1009', 'c-101', '2026-03-04 12:12:00', 'complete', 2000.00, 0.00),
('o-1010', 'c-105', '2026-03-12 17:49:00', 'complete', 460.00, 60.00),
('o-1011', 'c-102', '2026-03-20 07:26:00', 'pending', 880.00, 0.00),
('o-1012', 'c-101', '2026-03-28 14:03:00', 'complete', 1300.00, 0.00),
('o-1013', 'c-103', '2026-04-06 11:11:00', 'complete', 900.00, 0.00),
('o-1014', 'c-106', '2026-04-15 18:37:00', 'cancelled', 300.00, 180.00);
CREATE TABLE semantic_metrics (
metric_name TEXT,
grain TEXT, -- the time grain the metric is defined at
definition TEXT, -- the exact expression the contract compiles to
required_filter TEXT -- the predicate every implementation must apply
);
INSERT INTO semantic_metrics (metric_name, grain, definition, required_filter) VALUES
('net_revenue', 'monthly', 'SUM(gross_usd - refund_usd)', 'status = ''complete'''),
('gross_revenue', 'monthly', 'SUM(gross_usd)', 'status = ''complete'''),
('active_customers', 'monthly', 'COUNT(DISTINCT customer_id)', 'status = ''complete'''),
('refund_exposure', 'monthly', 'SUM(refund_usd)', 'status = ''cancelled''');-- The semantic contract itself, as the platform stores it: one row per governed metric.
SELECT metric_name, grain, definition, required_filter
FROM semantic_metrics
ORDER BY metric_name;Apply
Your turn
The task this lesson builds to.
Write a query that returns monthly net_revenue exactly as the semantic contract defines it, as (month, net_revenue), earliest month first, over orders(order_id, customer_id, order_dt, status, gross_usd, refund_usd).
The contract row for net_revenue gives grain monthly, definition SUM(gross_usd - refund_usd), and required filter status = 'complete'. Build the month with strftime('%Y-%m', order_dt), alias the columns exactly month and net_revenue, and round the revenue to 2 decimals.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 2 bonus drills.
Write a query that returns monthly active_customers exactly as the semantic contract defines it, as (month, active_customers), earliest month first, over the same orders table.
The contract row for active_customers gives grain monthly, definition COUNT(DISTINCT customer_id), and required filter status = 'complete'. Alias the columns exactly month and active_customers.
3 hints and 1 automated check are waiting in the workspace.