So you want an agentic semantic layer?

My least favorite part of data engineering has always been the cat-herding exercise that is getting a bunch of humans to agree on the definition of a Monthly Active User. I am absolutely delighted that the robots are taking over this part of the data stack. I don't know if Claude is much better at telling a C-suite executive or Director that the business has conflicting definitions of Sales-driven revenue, but it certainly lands much more effectively than any way I have ever tried to communicate it.

For the robots to take that over, the definitions have to be written down somewhere a robot can read them. Every place the industry has tried to write them down has failed the same way. Most companies I have worked with have some version of a failed data catalog implementation. The story is typically a vendor has convinced some executive they need to buy a data catalog tool or a semantic layer implementation using big enterprise concepts like "Master Data Management" and "Governance". In the best case, some poor team gets tasked with the toil of keeping the catalog up to date with the codebase. In the typical case, the tools become stale and abandoned and become false knowledge traps for anyone unlucky enough to stumble across them. Modern SQL execution frameworks (DBT and SQLMesh), provide places for you to write down metadata like column definitions and model descriptions. That is the one place a definition survives, because it changes in the same pull request as the SQL it describes. Any tool that requires manual upkeep of metadata outside your codebase is dead on arrival. So we wanted to know: how far can you get with a semantic layer built from nothing but the metadata that already lives in your SQLMesh project?

Main takeaways

• A model given a generic question will usually find the answer, but it costs more tokens With nothing but table names, Haiku got 73% at 14 tables and 73% at 1,000. It defused most trap evaluation questions by inspecting the data. What scaled was the cost of the context, not the failure rate.
• Small additions to table metadata can have massive improvements in agent accuracy. Decisions are not in the data: what an id column is meant to identify, whether customer zero counts, which business the asker means. A one-line description of what an id column means took Haiku from 80% to 96% on trap questions and put it level with Opus at a thirtieth of the cost.
• Keep your definitions next to the code and write audits or tests to validate them. At 100 and 1,000 tables, retrieving the few relevant tables' compiled definitions and pushing them into the prompt matched the full dump and the search tools on accuracy at a fraction of the cost. Definitions buried in SQL were not read as rules; descriptions were.
• Surfacing your context to your agents via MCP tools is a counter-intuitive trap. Prompt caching for OpenAI and Anthropic means one large system prompt of a subset of tables uses fewer tokens than a tool which fetches only the tables relevant to the question.
• Fabrication errors are the main gotcha. You have a couple of options, none of them are great. Does a model abstain when users ask questions about metrics or data that don't exist? We measured this as abstention. Explicit rules are required to prevent fabrication. Models still fabricated answers even in cases where explicit rules were declared.

How we tested it

We started with the SQLMesh example project, a sushi restaurant: orders, order items, items with daily prices, customers, waiters, a marketing status table that keeps history, and some derived revenue tables. Fourteen tables. Later we buried those same tables inside a generated company of 100 tables, then 1,000, with finance, inventory, HR, marketing, support and facilities around them. The restaurant data never changed, so the same 24 questions had the same answers at every size.

The questions are phrased the way a finance or ops person would ask them. "How much revenue did we do last week?" "How many customers do we have?" "Which dish was our best seller?" Some have traps, like item prices that change daily so you have to join on the date. Some depend on a definition the project wrote down. Four have no answer in the data.

The agent got one tool, run a read-only SQL query, and answered each question five times under different amounts of context. Schema only: table and column names. Compiled context: everything SQLMesh produces when it compiles the project, which is each table's keys, descriptions, the SQL that builds it, and where its columns come from. And that same context with three one-line descriptions added to the project. We ran Claude Haiku on everything and Claude Opus on the schema-only baseline, because the question I care about is whether good context lets you run the cheap model.

What we found

All runs: Claude Haiku unless noted, 24 questions, 5 repetitions per cell, read-only SQL tool. Cost is per correct answer at list price with the prompt cached, which is what a steady workload pays; the blind re-test row is as billed.

14 tables

100 tables

1,000 tables

Fabrication, "which waiters are also customers?" (no such relationship)

What to do with the fabrication errors

This is the problem that we have all heard of with LLM hallucination. The models are trained to be helpful and answer questions so thats what they do, even when the answer to the question isn't possible or the question is not logically possible, i.e. which waiters are also customers? In the words of Anthropic, "we don't have a robust solution yet". What are your options?

1. You don't allow text-to-sql at all, and you create a skill that returns metrics from a certified semantic layer. The obvious tradeoff is you lose the ability for customers to self-serve questions that don't have an approved entry in the certified semantic layer.

2. You allow text-to-sql, but you force the first pass through the certified semantic layer.

Reference Architecture with SQLMesh

flowchart LR
    P["Your SQLMesh project<br/>models, descriptions,<br/>audits, metrics"] -.->|"compiled on<br/>every deploy"| L["Semantic layer<br/>definitions, rules,<br/>search index"]
    C["Any AI client<br/>Claude, Cursor,<br/>a chat app"] -->|"question +<br/>who is asking"| M["MCP server<br/>auth, read-only SQL"]
    M -->|"tool call"| L
    M -->|"read-only role"| W[("Warehouse")]
    class P,L deploy
    class C,M,W ask

The client needs nothing but the MCP connector. The semantic layer is compiled from the project on every deploy; the MCP server reads it through a tool call and returns the relevant definitions to the model before it writes any SQL. The model never holds credentials.

dagctl does this for a SQLMesh project by default: deploy the project and it is exposed as an MCP-based semantic layer.