DEV Community

Raj Devnath
Raj Devnath

Posted on Originally published at Medium on

We measured what a semantic layer is actually worth to an AI agent

I built an open-source engine that infers a semantic layer from the warehouse itself — then benchmarked it honestly, including the case where it doesn’t help.

Everyone in data has watched an LLM confidently write SQL that sums revenue across cancelled orders. The model isn’t bad at SQL — it’s missing what your team knows. Nothing in information_schema says that sts_cd = 'X' means cancelled, that finance excludes those rows from revenue, or that the join to the customer table has to hit the current row of a slowly-changing dimension. That knowledge lives in your team’s heads, a stale wiki, and three YAML files somebody wrote a year ago.

The numbers on this are stark. On Spider 2.0 — the benchmark built from real enterprise warehouses rather than tidy academic ones — frontier models solved just 21.3% of tasks at publication (the best agentic scaffolds reach about 30% today). The same models score around 91% on the older, cleaner Spider 1.0. The gap isn’t intelligence. It’s context.

The industry’s answer is the semantic layer, and the industry’s problem is that every one of them — dbt, LookML, Cube, Snowflake semantic views — is written by hand, takes a quarter to write, and goes stale on the next ALTER TABLE.

So I built semlayer, an open-source engine that infers the semantic layer from the warehouse itself. This post is about what I measured when I benchmarked it — including the case where it doesn’t work, because that result shaped the product more than the wins did.

The experiment

Two synthetic warehouses, same harness:

  • messy_mart  — 36 tables built the way real enterprise marts actually look: ord_hdr, sts_cd, dly_sls_agg; zero declared constraints; business rules encoded nowhere except in whether the numbers reconcile; deprecated legacy tables still lying around.
  • tpcds_clean  — TPC-DS-style: descriptive names, conventional keys, textbook modeling.

An agent answers business questions (“total revenue by month”, “return rate by store”) two ways: from the raw schema , and from the inferred semantic layer. Scoring is execution-result equality against hand-written gold SQL — the answer is either right or it isn’t.

The results

Pass rate, raw schema → inferred semantic layer:

  • messy_mart (38 questions): 0.34 → 0.53
  • tpcds_clean (12 questions): 0.75 → 0.50

Three findings, in increasing order of comfort:

1. On the messy warehouse, the inferred layer lifts agent accuracy +54% relative (0.34 → 0.53), and the lift replicates with a stronger model (0.40 → 0.50). The flagship class of fixed error is the silent kind: raw-schema “total revenue” returns $16.3M where the correct answer is $14.6M, because nothing told the agent that the aggregate tables exclude cancelled orders. The inferred layer carries that rule — discovered automatically, by hypothesis-testing filters until the aggregate tables reconcile with the fact tables, group by group.

2. On the clean schema, the semantic layer hurt (0.75 → 0.50). Good names already carry the semantics, and full raw DDL beats partial-detail disclosure. I publish this number, and I tell people with tidy warehouses not to use the tool. If a vendor won’t tell you when not to buy, their benchmark is marketing.

3. I also built an ontology enrichment layer — and it added nothing. I gated it on this same benchmark, it failed (0.53 → 0.50), and I demoted it to internal plumbing. It doesn’t ship until it earns its way back.

What “inferred” means mechanically

Four stages, statistics first, LLM only where statistics are insufficient:

  • Profile : per-column statistics and semantic typing. 0.81 accuracy with zero LLM calls; 0.90 with escalation — and only low-confidence columns ever reach the model, batched per table. Total inference cost: about $0.70 per 100 tables on the cheap model tier, with your own API key.
  • Link : foreign-key discovery without declared constraints. The rule that keeps it honest: statistical evidence alone never auto-accepts a foreign key — naming and an independent model check must corroborate, or it goes to a human review queue. The test fixtures seed statistically-perfect-but-absurd FK traps (shoe_size ⊆ dept_id passes every inclusion test); the count of accepted traps, enforced in CI, is zero.
  • Describe : descriptions generated from profiled evidence via context propagation through the join graph — a table is described knowing what its neighbors are.
  • Enrich : enum decodes joined from decode dimensions the engine itself discovered; metrics; deprecation (“use ord_hdr, not ord_hdr_legacy”); and the reconciliation-discovered business rules. Reconciliation is per-group, and the evidence rides in the output — provenance entries literally read “reconciles per-group with ord_hdr.tot_amt excluding sts_cd <> ‘X’ (40/40 store groups; 1,095/1,095 date groups within 0.2%).”

Every claim lands with confidence, provenance, and a lifecycle (inferred → reviewed → certified), governed by a consumer contract that makes the silent failure modes — summing across a fan-out join, filtering on a guessed decode — non-conforming, not merely unwise. A human reviews a queue for about thirty minutes instead of writing YAML for a quarter.

Then an agent actually uses it

The layer is served over MCP, so Claude, Cursor, or your in-house agents consume it directly. Beyond lookup tools, there’s a compile_metric tool that emits correct SQL — joins, business-rule filters, time bucketing — and it refuses constructively when the request is unsafe: group-bys across fan-out joins, ambiguous join paths (two routes to the same date dimension), time bucketing when the warehouse runs a fiscal calendar and the caller didn’t say which calendar they meant. Every refusal names the reason and enumerates the legal alternatives. A wrong number that looks right is the enemy; a refusal that explains itself is a feature.

And if you already have documentation — a data dictionary, a wiki export, the CLAUDE.md from your ETL repo — the engine reads it as priors: doc-supported claims gain confidence, and doc-vs-data contradictions surface in the review queue (“your wiki says X means Refunded; your decode table says Cancelled”) instead of being silently believed. On the messy fixture, that contradiction list is where the stale-documentation errors get caught.

The part where you check my work

Every number above is reproducible from the repo: the fixture generators, gold layers, 82 competency questions, and the benchmark runner all ship. All LLM calls are cassette-recorded, so the entire eval suite runs in CI at $0

python fixtures/build.py && pytest tests/ -q
Enter fullscreen mode Exit fullscreen mode

replays everything on a laptop. There are also --no-llm (fully deterministic, zero API calls) and --no-sample-egress (cell values never leave your machine) modes, with their accuracy costs measured and published rather than hand-waved.

The CLI is Apache-2.0, complete, and free — no table caps, no signup; it runs in your environment with your warehouse credentials and your LLM key. A paid hosted control plane (continuous re-inference on schema change, team review, dashboards) may come later; the CLI stays fully functional on its own.

If your warehouse is messy, I want to hear from you

This is an early beta. The numbers above come from synthetic fixtures built to simulate real enterprise marts — the next step is proving them on actual warehouses. I’m recruiting about ten design partners with genuinely messy schemas — cryptic names, tribal rules, undeclared keys — before public launch. The ask is roughly two hours across two weeks, and you get a working semantic layer plus an MCP endpoint for your agents out of it. If your warehouse is clean, you probably don’t need this — and now you have my own benchmark to prove it.

semlayer.dev · github.com/semlayer-dev/semlayer · [email protected]

Top comments (0)