A semantic layer is a version-controlled set of business definitions (metrics, dimensions, the joins between tables, and the person who owns each one) that sits between a warehouse and anything that queries it. Without one, text-to-SQL leaves the model to guess what “revenue” or “active customer” means in a particular company, and the guess comes back as valid SQL with a plausible number. With one, the model picks from definitions the team already agreed on, and that choice is testable.

What a semantic layer is, in plain terms

A warehouse stores facts. It doesn’t store what a company means by “net revenue” or “churned.” That meaning lives in dashboards, spreadsheets, and the heads of a few analysts. A semantic layer writes it down once, as code.

Part What it pins down Example
Metrics The calculation, the filters, and the unit Net revenue = paid invoice amounts minus refunds, in USD
Dimensions The ways to slice a metric Region, plan, and signup month
Entities and joins Table grain, keys, and how tables connect One customer has many orders, and each order has one customer
Ownership Who decides what the number means Finance owns revenue, and product analytics owns weekly active users

It isn’t a new database. It’s metadata plus a query generator that turns “net revenue by region, last quarter” into the same SQL every time. Open-source implementations include dbt’s MetricFlow and Cube Core. The format matters less than the discipline: one definition per name and one owner per definition.

Why text-to-SQL gives confident wrong answers without one

A model sees table and column names, maybe a few sample rows, and has to infer business rules nobody wrote down. The costly failure is a query that runs and returns a plausible, wrong number, because nothing flags it.

Ambiguous business terms

“How many active customers did we have in September?” has at least three readings: customers with a paid invoice, users who logged in during the last 30 days, or accounts with an uncancelled subscription. All three fit the schema. The model picks one, and next week, with different wording, it might pick another.

Join fan-out

Asked for “revenue by product category,” the model joins orders to order_items to reach the category. If it then sums orders.order_total, an order with five line items contributes five times its total. That’s a fan-out join: one row joined to many rows, producing more output rows than input rows. The SQL is valid, and the number is inflated.

Filters nobody says out loud

In finance, “revenue” may mean completed orders only, net of refunds, excluding test and internal accounts. None of that is in the question, so a model that sums every row in orders counts pending orders, cancellations, and test purchases.

Time zones and calendars

With timestamps in UTC, a New York team’s “yesterday” ends in the early evening local time, and late sales land in the wrong day. PostgreSQL’s date_trunc accepts a time zone argument for exactly this, but the model has to know which zone the reporting uses. Fiscal quarters raise the same question.

Each of these is a missing definition, not a SQL skill gap.

What the benchmarks say about text-to-SQL accuracy

BIRD is a benchmark of 12,751 question-SQL pairs over 95 databases (Li et al., NeurIPS 2023). Questions come with “external knowledge evidence” where it’s needed, a short hint such as how a ratio is calculated.

In the paper’s results, GPT-4 reached 34.88% execution accuracy on the test set without the hints and 54.89% with them.

Data engineers and database students went from 72.37% to 92.96% with the same hints (Table 2), so even the humans missed more than a quarter of the questions without them.

Spider 2.0 is closer to enterprise work: 632 tasks on real databases that often have over 1,000 columns (Lei et al., ICLR 2025).

On those tasks, an agent built on o1-preview solved 21.3%, while GPT-4-based methods had reached 91.2% on the original Spider and 73.0% on BIRD.

The authors’ error analysis of a random sample shows what went wrong. Wrong tables or columns accounted for 27.6% of errors and join errors for 8.3%, partly because BigQuery databases often lack declared foreign keys and the model had to guess keys from column names (section 4).

Business rules written as prose are hard for a model to apply. Tasks that depended on external documents succeeded 11.54% of the time, against 26.64% without them.

A smaller study tested the idea directly. On an insurance schema with 43 questions, GPT-4 with zero-shot prompts averaged 16.7% execution accuracy on the raw SQL database and 54.2% when it queried a knowledge graph built from an ontology and mappings that defined the business concepts (Sequeda et al., 2023). One schema proves little alone, but the direction matches BIRD.

Public leaderboards have climbed since. On October 15, 2026, the top test entry on the BIRD leaderboard scored 82.95%, against 92.96% for humans, and all top entries used the benchmark’s oracle knowledge, the per-question hints.

The Spider 2.0 leaderboards listed 96.70% in the Snowflake setting and 76.23% in the Lite setting (547 tasks each), against the paper’s best results of 23.77% and 23.40% on those settings (Table 3). The original 632-task setting is no longer ranked.

That’s progress on fixed public question sets with prepared context. On a company’s own warehouse, nobody hands the model a hint per question. The semantic layer is where those hints get written, and an evaluation set gives that warehouse its own accuracy number.

How to build a semantic layer, step by step

1. An inventory of questions and metrics

The work starts from demand, not the schema. We recommend collecting 50 to 100 questions people asked last quarter in Slack threads, BI tickets, and recurring reports, then listing the metrics they need. We aim for 20 to 40 core metrics at first and add more when the evaluation set shows gaps.

2. Definitions with an owner

For each metric, the first job is to collect every existing version: dashboard SQL, finance spreadsheets, and an analyst’s saved query. Where they disagree, the owner decides. When two meanings are both legitimate, they get two names (active_customers for billing, weekly_active_users for product usage). We recommend making the owner someone who makes decisions with the number, usually in finance, sales operations, or product analytics, rather than on the data team.

3. Entities, grain, and joins

Every table in the layer gets a recorded grain (one row per order, per order line, or per day), a primary key, and its relationships (one-to-one, many-to-one, or one-to-many). Declared relationships let the query generator refuse a join that would fan out.

4. Version control

Definitions live in Git and change through pull requests the metric’s owner reviews. When finance changes how refunds are netted, the change is on record and every downstream answer follows.

5. Tests

Data tests check that keys are unique at the declared grain. Reconciliation tests check that a closed month matches the number finance signed off. Contract tests check that every metric has an owner, a description, and a time column. All three run in CI on every change.

Here’s one definition, in a tool-neutral format (MetricFlow, Cube, and BI tools each have their own syntax):

# metrics/active_customers.yml
name: active_customers
description: >
  Distinct customers with at least one paid invoice in the period.
  Excludes internal accounts and test accounts.
owner: Head of Finance Operations
version: 3
expression: count(distinct invoices.customer_id)
source: analytics.invoices            # grain: one row per invoice
filters:
  - invoices.status = 'paid'
  - customers.is_internal = false
  - customers.is_test = false
time:
  column: invoices.paid_at            # timestamptz, stored in UTC
  reporting_timezone: America/New_York
  grains: [day, week, month, quarter]
joins:
  - to: analytics.customers           # grain: one row per customer
    on: invoices.customer_id = customers.id
    relationship: many_to_one         # cannot fan out
dimensions: [customers.region, customers.plan, customers.signup_month]
synonyms: ["paying customers", "active accounts"]
not_to_be_confused_with: [weekly_active_users]   # owned by product analytics
tests:
  - reconcile_with: finance_close.active_customers
    periods: last_3_closed_months

And the SQL a generator would compile from it for “active customers by month”:

SELECT date_trunc('month', i.paid_at, 'America/New_York') AS month,
       count(DISTINCT i.customer_id)                      AS active_customers
FROM analytics.invoices AS i
JOIN analytics.customers AS c ON c.id = i.customer_id
WHERE i.status = 'paid'
  AND NOT c.is_internal
  AND NOT c.is_test
GROUP BY 1
ORDER BY 1;

How the AI analyst uses the semantic layer

With definitions in place, the model chooses business logic instead of writing it. Our flow:

  1. Question to definitions. The analyst matches the question to metrics, dimensions, and synonyms in the layer, not to raw tables. Questions that fall outside the defined metrics are flagged for a person instead of guessed. We also recommend flagging questions where two metrics match equally well.
  2. SQL against the definitions. The model either writes SQL from the definition’s expression, filters, and join paths, or calls the layer’s query interface to compile it. The second option leaves less room for error.
  3. Validation before running. Every query is checked before it runs: no writes, a row limit, and a timeout (statement_timeout in PostgreSQL). We also recommend parsing the SQL, allowing a single read statement, and checking that every table and column belongs to the layer.
  4. A read-only role. In PostgreSQL terms, that’s USAGE on the layer’s schemas and SELECT on their tables and views, and nothing else (GRANT). OWASP’s LLM06:2025 Excessive Agency lists the opposite setup as a risk: a read-only feature connecting with an identity that also has UPDATE, INSERT, and DELETE permissions. Broad roles such as PostgreSQL’s pg_read_all_data are a poor fit because they read every table. That role does respect row-level security policies, which is how rows get limited per user or team, as long as the analyst’s role isn’t a superuser or the table owner. A read-only transaction default works as a second guard, not the boundary, because a session can change it.
  5. Answers that show the work. Every answer comes with the query behind it and a chart, so a person can check the logic. We recommend also showing the definitions, their versions, and the time zone used, so a wrong answer points at the definition to fix.

An evaluation set built from real questions

The evaluation set is where accuracy gets measured. Each entry records:

  • The question as someone phrased it, shorthand included.
  • The correct answer for a closed period, so it doesn’t drift.
  • The reference SQL and the metrics it uses.
  • Who confirmed the answer, usually the metric owner.

Scoring compares query results, not SQL text, because two different queries can both be right.

Strong evaluation sets also include questions the analyst shouldn’t answer (outside the layer, missing data, or ambiguous on purpose). For those, a clarifying question or a handoff to a person counts as the correct response.

We aim for 100 or more questions before launch, then re-run the set after every change to a definition, the prompt, or the model version. We recommend tracking accuracy per metric, because an overall average can hide a metric that’s wrong every time. More on the method in our guide to LLM evaluation for product teams.

Where to start

The first steps need no new software:

  1. First week: 50 real questions from the last quarter, with a note of who asked each.
  2. Second week: the 10 metrics those questions need most, with an owner settling conflicting definitions.
  3. Then: those 10 definitions as files in a repository, with reconciliation tests against last month’s signed-off numbers.
  4. Before any model touches it: the 50 questions turned into an evaluation set with confirmed answers.
  5. Only then: a model on a read-only role, a run of the evaluation set, and a close read of every wrong answer, each of which points to a definition, synonym, join, or prompt to fix.

Checklist before trusting an answer:

  • Every metric has a description, an owner, and a version.
  • Every table has a declared grain and key, and every join has a declared relationship.
  • Time zones, fiscal calendars, and standard filters live in the definitions, not the prompt.
  • Definitions are in version control, owner-reviewed, and reconciled with signed-off numbers.
  • The analyst uses a read-only role with a timeout and row limit, shows its SQL, and is re-scored on the evaluation set after every change.

For teams that want this built on their own warehouse, our AI data analyst service covers the semantic layer, text-to-SQL with validation, read-only access, and an evaluation set made from the team’s real questions, in 6 to 8 weeks.