Building a Semantic Layer for Your Business

How to build a semantic layer AI and BI both trust: entities, joins, metric rules, where to store definitions, and a five-step rollout next to dbt.

A semantic layer that understands your business is the translation between how people talk and how your warehouse stores data: it maps business terms like "active customer," "net revenue," and "qualified lead" to specific tables, joins, and filters, so every query built on it returns the same number. Build it by writing down the ten metrics your leadership actually argues about, encoding each as reviewable code next to your dbt models, and letting real usage tell you what to add next.

The mistake most teams make is trying to model the entire warehouse before anyone uses it. A semantic layer earns its keep through coverage of the questions people ask, not coverage of the schema.

What a Semantic Layer Actually Contains

Entities and Their Grain

Start with the nouns: customer, account, order, subscription, session, ticket. For each, define the grain precisely — is a "customer" a billing account or a logged-in user? Ambiguity here is the root cause of most metric disputes downstream.

Joins and Relationships

Encode how entities connect, including the awkward parts: the subscription table that needs a valid-to filter, the account hierarchy where subsidiaries roll up to a parent, the events table that double-counts unless you deduplicate on a session key. This is knowledge that normally lives in one senior analyst's head.

Metrics and Their Business Rules

A metric is not just an aggregation. "Monthly recurring revenue" carries decisions about trials, discounts, annual contracts amortized monthly, and whether internal test accounts are excluded. Write those decisions down alongside the SQL, in plain language, so a non-analyst can audit them.

Filters, Exceptions, and Known Traps

Every warehouse has them: the migration cutover date before which the data means something different, the region code that changed in 2024, the QA orders that must be excluded. If these are not captured, every new analyst and every AI session rediscovers them the hard way.

Where the Semantic Layer Should Live

This is the decision with the longest half-life. Three common options:

Approach	Where logic lives	Strengths	Trade-offs
BI-tool modeling layer	Inside a single BI platform	Fast to start; tightly integrated with that tool's reports	Logic is usable only by that tool; migrating means rebuilding
Dedicated metrics layer	Standalone service with its own spec	Tool-agnostic; strong for consistent metric APIs	Another system to run and keep in sync with transformations
Files in your repo	SQL, Markdown, and Python next to dbt models	Code review, version history, portable to any agent or tool	Requires a review habit; less point-and-click authoring

Querio takes the third approach. Context is stored as plain SQL, Markdown, and Python files synced to GitHub in the same repo as your dbt project. The practical benefit is that a change to a dbt model and the change to the definition that depends on it can arrive in the same pull request — which is the only reliable defense against the sync treadmill, where dbt evolves faster than the semantic layer that describes it.

The second benefit is exit: because the definitions are ordinary files, they keep working with Claude Code or any other agent even without Querio. Context lock-in is the real vendor risk in AI analytics, and it is worth checking early which side of that line a platform sits on. See metrics layer vs. semantic layer for how the terms differ.

How to Build One in Five Steps

Step 1: Harvest the Real Questions

Pull the last quarter of ad-hoc requests from Slack and tickets. Cluster them. You will typically find that 70% of requests hit fewer than fifteen underlying concepts. Those concepts are your first release scope.

Step 2: Agree on Definitions Before Writing SQL

Get the finance lead, the revenue lead, and the data lead in one room for an hour per metric family. Write the sentence first — "Active customer: an account with at least one billable event in the trailing 30 days, excluding internal and QA accounts" — then encode it. Definitional disagreements found here are cheap; found in a board meeting they are expensive.

Step 3: Model the Entities and Joins

Build conformed dimensions (customer, date, product, region) and the fact tables that reference them. Keep the semantic layer thin: it should describe the warehouse, not become a second transformation pipeline. If a definition requires heavy reshaping, that logic probably belongs in dbt.

Step 4: Attach Access Rules

Row-level and column-level rules belong with the model, not with each dashboard. Decide early which fields are restricted (salary, PHI, customer PII) and make the restriction inherit everywhere, including Slack and any agent connection. Querio applies role-based access control and uses OAuth over MCP, so agent queries inherit each user's data permissions rather than running as a shared service account.

Step 5: Let Usage Drive Expansion

Ship the first fifteen definitions, then watch what people ask that the layer cannot answer. In Querio, the agent proposes additions from real usage — "save this as a reusable definition?" — and only logged-in users approve and commit them. Column-level lineage learned from actual queries shows which fields matter and which are decorative.

Keeping It Alive

A semantic layer decays faster than most documentation because the warehouse underneath it keeps moving. Three habits keep it honest:

Change review in the same PR. If a dbt model changes a column that a definition depends on, both changes should be visible to the same reviewer.
Trust labels. Mark definitions and dashboards as trusted, experimental, or team-specific. Analysts need somewhere to explore without implying certification.
Quarterly pruning. Definitions nobody has queried in six months are candidates for deletion. A smaller layer that is entirely correct beats a large one nobody trusts.

The test of a semantic layer is not how complete it looks. It is whether two people asking the same question in two different tools get the same number.

Why This Matters More With AI in the Loop

An AI analytics agent without a semantic layer starts every session at zero. It rediscovers the join path, guesses at the churn definition, and misses the test-account filter — burning tokens and creating a fresh chance to be confidently wrong. With a semantic layer, the agent arrives with context, and the same definition serves the notebook, the dashboard, the Slack question, and the MCP call from inside Claude.

This is also what makes AI answers auditable. In Querio, every answer is produced as real SQL and Python in a reactive notebook, so you can open any number and see which definition it used. If the data to answer a question is not there, the honest answer is "I don't have this" rather than an invented figure. More on that in how governed context prevents wrong answers.

Getting Started Without a Six-Month Project

A realistic first pass for a five-person data team: one week to harvest questions, two weeks of definition workshops and encoding, one week to wire access rules and publish. That gets you the metrics leadership uses, governed and reusable. Everything after that is incremental and usage-driven. Querio's plans and onboarding are listed on the pricing page, and the MCP tier is free to start if you want to test the context-plus-agent workflow before committing.

FAQs

Do we need a semantic layer if we already have dbt?

Usually yes, but a thin one. dbt gives you clean, tested tables; the semantic layer adds business meaning on top — which metric to use, how entities join, what the filters mean in English. Keeping both in the same repo prevents them from drifting apart.

How many metrics should the first version cover?

Ten to twenty. Choose the ones in leadership reporting plus any metric two teams have already disagreed about. Broad, shallow coverage produces a layer nobody trusts.

Who owns the semantic layer?

The data lead owns the framework and core financial metrics; domain owners in marketing, sales, and operations own their own definitions. Approval should be a named human with a code review, not a committee meeting.

What happens to our definitions if we change vendors?

That depends entirely on where they are stored. Definitions kept as files in your own repository move with you and remain usable by any agent or tool. Definitions kept inside a vendor's proprietary modeling layer generally have to be rebuilt — which is why storage format is worth deciding before feature comparisons.

Can an AI agent build the semantic layer for us?

It can propose most of it. An agent that watches real queries can suggest joins, spot repeated filters, and draft definitions. What it should not do is approve them. Keep humans on the commit.