Why AI analytics needs a semantic layer

Ask a large language model to "show monthly revenue by plan" and it will usually return syntactically perfect SQL within seconds. The problem is everything the SQL silently decides: whether revenue means invoiced or collected, whether refunds are netted, whether a plan change mid-month counts twice, whether test accounts are excluded. The model makes those decisions by reading column names. Your finance team made them in a meeting two years ago. The two rarely agree.

This is the gap a semantic layer closes. I lead data at Kimo, and this post explains what the research says about AI-generated SQL, why business definitions — not SQL skill — are the bottleneck, and how a governed metrics layer changes the failure mode from "plausible and wrong" to "correct or explicitly unsure".

What is a semantic layer?

The idea predates AI. dbt describes the goal of its Semantic Layer as moving metric definitions out of the BI layer and into the modeling layer, so that "different business units are working from the same metric definitions, regardless of their tool of choice".⁵Source 5 · dbt Labs Documentationdbt Semantic Layerdocs.getdbt.com What is new is that AI assistants have become one more tool — and the one most likely to improvise. See our glossary entry on the semantic layer and the walkthrough Metrics layers, explained with one MRR definition for the basics.

How accurate is text-to-SQL on real business data?

Academic benchmarks give a useful, if imperfect, answer. The original Spider benchmark (2018) introduced cross-database text-to-SQL with 10,181 questions over 200 databases; at the time, the best model reached 12.4% exact-match accuracy on unseen databases.¹Source 1 · Yu et al., EMNLP 2018 (arXiv:1809.08887), 2018Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Taskarxiv.org Models improved fast — so the community built harder tests.

BIRD (2023) used larger, messier databases with real values; its best ChatGPT setup (with chain-of-thought prompting and the benchmark’s knowledge evidence) reached 40.08% execution accuracy, against 92.96% for human annotators.²Source 2 · Li et al., NeurIPS 2023 (arXiv:2305.03111), 2023Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs (BIRD)arxiv.org Spider 2.0 (2024) went further, with enterprise-scale schemas, multiple SQL dialects and workflows closer to real data work. Its authors report that an agent framework built on o1-preview solved only 21.3% of tasks, compared with 91.2% on Spider 1.0 and 73.0% on BIRD.³Source 3 · Lei et al., ICLR 2025 (arXiv:2411.07763), 2024Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflowsarxiv.org

The pattern is consistent: the closer the benchmark gets to a real company’s warehouse — hundreds of tables, ambiguous column names, domain conventions — the worse raw text-to-SQL performs. Your schema looks a lot more like Spider 2.0 than Spider 1.0.

Why is the problem definitions, not syntax?

The most telling result in the BIRD paper is not the headline number. The authors also gave models external knowledge evidence — short notes such as how a value is encoded or how a domain term maps to columns — and measured the difference. GPT-4’s execution accuracy on the test set rose from 34.88% without evidence to 54.89% with it; ChatGPT went from 26.77% to 39.30%.²Source 2 · Li et al., NeurIPS 2023 (arXiv:2305.03111), 2023Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs (BIRD)arxiv.org The categories of knowledge they supplied read like a semantic layer’s job description: numeric reasoning, domain knowledge, synonyms and value illustrations.

Independent work on enterprise data points the same way. Sequeda, Allemang and Jacob built a benchmark on an insurance-domain SQL schema and found that GPT-4 with zero-shot prompts directly on the SQL database answered 16% of questions accurately; posing the same questions over a knowledge graph representation of the business raised that to 54%.⁴Source 4 · Sequeda, Allemang & Jacob (arXiv:2311.07509), 2023A Benchmark to Understand the Role of Knowledge Graphs on Large Language Model’s Accuracy for Question Answering on Enterprise SQL Databasesarxiv.org Different technique, same lesson: the model needs your semantics, not just your schema.

What goes wrong without one: a concrete example

Here are two queries an assistant might reasonably write for "how many active customers did we have last month?" Both run. Both look right.

1234567891011121314-- A: anyone with a subscription row in the month
SELECT count(DISTINCT account_id)
FROM subscriptions
WHERE started_at < date '2026-10-01'
  AND (ended_at IS NULL OR ended_at >= date '2026-09-01');

-- B: paying, non-test accounts with MRR > 0 at month end
SELECT count(DISTINCT s.account_id)
FROM subscriptions s
JOIN accounts a ON a.id = s.account_id
WHERE a.is_test = false
  AND s.mrr_cents > 0
  AND s.started_at <= date '2026-09-30'
  AND (s.ended_at IS NULL OR s.ended_at > date '2026-09-30');

Query A counts trials, test accounts and anyone who churned on the 2nd. Query B matches what finance reports to the board. The gap between the two can be large, especially at a young company with many trials — and nobody notices until an investor compares the AI answer with the deck. A semantic layer removes the choice: "active customer" is defined once, and the AI may only use that definition.

1234567891011measures:
  - name: active_customers
    description: Paying, non-test accounts with MRR > 0 at period end.
    type: count_distinct
    expr: account_id
    filters:
      - accounts.is_test = false
      - subscriptions.mrr_cents > 0
    time: period_end
    synonyms: [active accounts, paying customers, logos]
    owner: finance

How does a semantic layer make AI answers trustworthy?

1. 1
   Step 1:

   Resolve terms, then query

   The assistant first maps each phrase in the question to a defined measure, dimension or filter. "Paying customers" resolves to active_customers through its synonyms.

2. 2
   Step 2:

   Generate a small, structured request

   Instead of free-form SQL, the model emits something like active_customers by plan, last month. That is far easier to get right and to validate.

3. 3
   Step 3:

   Compile deterministically

   The semantic layer — not the model — turns the request into SQL, applying joins, filters and row-level permissions the same way every time.

4. 4
   Step 4:

   Show the trace

   Every answer lists the measures used, filters applied, generated SQL and source freshness, so a human can audit it in seconds.

5. 5
   Step 5:

   Refuse when undefined

   If a term has no definition, the assistant says so and asks — rather than inventing one.

This is how Ask Kimo works: it answers only through the measures in your data models, and it tells you when a question needs a definition that does not exist yet. You can see the models it uses in the Models page and try questions in Ask.

A checklist for AI-ready metrics

If you are starting from scratch, the definitions in SaaS metrics, defined once and the SaaS metrics template are a practical first layer: ARR, net revenue retention, CAC payback and the rest, ready to map onto your tables.

Beyond accuracy: consistency is the real product

Even a perfectly accurate assistant would be a problem if it computed revenue differently from your dashboard. The value of a semantic layer is that the board deck, the marketing dashboard, the CSV export and the AI answer all agree, because they all compile from the same definition. When finance changes how refunds are treated, it changes in one place, and every surface updates — exactly the property dbt highlights when a metric definition is "refreshed everywhere it’s invoked".⁵Source 5 · dbt Labs Documentationdbt Semantic Layerdocs.getdbt.com

That consistency also holds across where data lives: with Kimo Bridge, the same measures compile to SQL that runs live on your own database, so governance does not depend on copying data into anyone’s cloud.