Building a Semantic Layer, Hands-On

In the earlier piece on the semantic layer, I made the case: define each business metric once, and let every dashboard, tool, and AI agent read from it. This is the hands-on companion. We build one.

What we’re building: a metrics layer for a single metric — revenue — that your BI tool, your dbt project, and an AI agent can all consume without disagreeing. By the end, “revenue” means one thing everywhere, it’s tested, and it’s served through an API.

Step 1: Define the metric with a human, not in YAML

The unsexy step that decides everything. Before touching code, get the business owner in a room (or a doc) and nail down:

Name: revenue
Definition: gross revenue, refunds excluded, recognized on order date
Grain: one row per day per region
Owner: who signs off when the number looks wrong

Write this down where everyone can see it. Most metric disagreements I’ve seen weren’t technical — two teams with two definitions, both convinced theirs was “revenue.” The semantic layer doesn’t fix disagreement. It forces the conversation early, once, instead of late, forever.

Step 2: Model it

Here’s the shape of a semantic model in dbt’s MetricFlow format (adapt to your dbt version — the concepts transfer to any tool):

semantic_models:
  - name: orders
    description: Order-level facts, one row per order line
    defaults:
      agg_time_dimension: ordered_at
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: ordered_at
        type: TIME
        type_params:
          time_granularity: day
      - name: region
        type: CATEGORICAL
    measures:
      - name: order_total
        agg: SUM
        expr: line_total

metrics:
  - name: revenue
    description: Gross revenue, refunds excluded, recognized on order date
    type: SIMPLE
    type_params:
      measure: order_total

Three things to notice. First, the measure (order_total) is the raw aggregation; the metric (revenue) is the business concept layered on top with its definition. Second, dimensions like region and ordered_at are what let every consumer slice the same metric the same way. Third, the description isn’t decoration — it’s the contract. When an AI agent reads this metric, the description is what keeps it from inventing its own interpretation.

No dbt? A SQL view gets you 80% of the way:

CREATE OR REPLACE VIEW marts.revenue_daily AS
SELECT
  DATE_TRUNC('day', ordered_at) AS day,
  region,
  SUM(line_total) AS revenue
FROM orders
WHERE order_status != 'refunded'
GROUP BY 1, 2;

Same idea: one definition, one place, everyone reads from it.

Step 3: Test it like production code

Metrics without tests are rumors. At minimum:

Sanity bounds: revenue is never negative; day-over-day change beyond some threshold pages someone.
Grain checks: no duplicate day/region rows; no future dates.
Reconciliation: the semantic layer’s revenue matches the finance system’s revenue within tolerance, on a schedule.

In dbt, these are data tests on the underlying models plus a scheduled reconciliation query. The point isn’t the tooling — it’s the discipline. A metric nobody tests is a metric nobody trusts, and an untrusted metric gets forked: someone builds their own version in a spreadsheet, and you’re back to three definitions of revenue.

Step 4: Serve it to everyone — including agents

This is the step most teams skip, and it’s the whole point.

Your BI tool connects to the semantic layer natively (most modern BI tools speak dbt’s Semantic Layer or query the views directly). But the second consumer — the AI agent — needs the same metrics through an API, not a dashboard. The agent should ask for “revenue by region for Q3” by metric name and get back a governed number, not write its own SQL against raw tables and reinvent the definition.

That’s the real payoff: the agent stops hallucinating metrics because it stops computing them. It reads them.

Step 5: Govern changes

Metrics change. Tax rules change, refund policies change, someone acquires a company. The semantic layer needs change control like any production system:

Versioning: metric definitions are versioned; breaking changes get a migration path, not a silent edit.
Ownership: every metric has an owner who approves changes. No owner, no metric.
Deprecation: old definitions get sunset with notice, not deleted on a Friday.

Humans grumble at schema changes and adapt. Agents break silently. Versioning isn’t bureaucracy — it’s the difference between a controlled rollout and a fleet of agents confidently reporting last quarter’s definition.

Five mistakes I see everywhere

1. Defining metrics in the BI tool. Now your definition lives in one vendor’s dialect, invisible to everyone else — including your agents. The semantic layer belongs underneath the BI tool, not inside it.

2. No owner per metric. A metric nobody owns is a metric nobody fixes. Assign ownership on day one.

3. Grain ambiguity. Is revenue recognized on order date or ship date? If the model doesn’t say, every consumer guesses — differently.

4. Skipping tests. “Trust me” is not a data quality strategy. Test the metric, reconcile it against finance, alert on drift.

5. Letting teams fork definitions. The moment someone builds “revenue_v2_final_FINAL” in a spreadsheet, you’ve lost. Make the governed path the easy path: good docs, fast queries, one API.

The payoff

A working semantic layer is unglamorous. No demo has ever wowed a room with “consistent metric definitions.” But it’s the foundation everything else stands on: dashboards people trust, agents that don’t invent numbers, and executives who stop arguing about whose report is right.

Build it once, define it once, test it, serve it to everyone. The iceberg below the waterline holds up the tip.

---

— Jugal