Lab 07 — The Semantic Layer & Metrics

The goal

So far the platform can store facts. Now it needs to mean something. A "metric" is a number with a fixed business definition — Gross Merchandise Value, take-rate revenue, utilization. The job of the semantic layer is to capture each definition once and hand the same answer to everyone who asks.

This realizes chapter 08 — Serving & the Semantic Layer of the Systems Design reference, at laptop scale. The principle is identical whether your warehouse serves five people or five thousand.

The five-definitions problem

Here's the failure mode the semantic layer exists to prevent. Imagine mini-GridDP has no shared metric definitions. The CEO asks five people for "monthly revenue," and each one writes their own SQL against fct_rentals:

Five numbers. They disagree, sometimes by 5×. Now every meeting starts with "whose number is right?" instead of "what should we do?" — and once people stop trusting the dashboard, they go back to spreadsheets and the platform's whole value evaporates.

This is the same lesson as systems-design chapter 08: serving isn't about making data available, it's about making one agreed definition available. Trust is the product.

The metric catalog

Before writing a line of code, write the catalog. For each metric: a precise English definition and the gold fact it's computed from. This table is the spec — the SQL that follows just encodes it.

Implementing one source of truth

There are two ways to build a semantic layer in dbt. The fancy way is dbt's MetricFlow / semantic models — you declare metrics in YAML and dbt generates the SQL on demand. It's powerful, but it needs dbt Cloud or the MetricFlow CLI and adds moving parts. We'll use the pragmatic way that's fully local and just as principled: metric models. Each metric is a small, heavily documented dbt model in models/metrics/ that every consumer queries. The definition lives in the SQL; nobody re-derives it.

Create the folder and tell dbt to materialize these as views (cheap, always fresh):

cd mini-griddp/dbt
mkdir -p models/metrics

First, the headline metrics — GMV and take-rate revenue — at a daily grain, so any consumer can roll them up to month, quarter, or year:

-- METRIC: GMV and take-rate revenue, per day.
-- Definition (the contract — do NOT redefine elsewhere):
--   gmv     = sum(rental_amount)               over completed rentals
--   revenue = sum(rental_amount * take_rate)   over completed rentals
-- Grain: one row per rental_date. Roll up by summing.
{{ config(materialized='view') }}

select
    d.date_day                              as rental_date,
    d.year_month,
    sum(r.rental_amount)                    as gmv,
    sum(r.rental_amount * r.take_rate)      as revenue
from {{ ref('fct_rentals') }} r
join {{ ref('dim_date') }} d on r.date_key = d.date_key
where r.status = 'completed'
group by 1, 2

Next, ARPU — note it reuses the revenue definition by referencing the same filtered facts, so it can never disagree with metric_revenue:

-- METRIC: ARPU — average take-rate revenue per active customer, per month.
-- Definition:
--   arpu = sum(rental_amount * take_rate) / count(distinct customer_key)
--          over completed rentals, per year_month.
{{ config(materialized='view') }}

select
    d.year_month,
    count(distinct r.customer_key)                      as active_customers,
    sum(r.rental_amount * r.take_rate)                  as revenue,
    sum(r.rental_amount * r.take_rate)
        / nullif(count(distinct r.customer_key), 0)     as arpu
from {{ ref('fct_rentals') }} r
join {{ ref('dim_date') }} d on r.date_key = d.date_key
where r.status = 'completed'
group by 1

And revenue per GPU model, the same contract sliced by a dimension:

-- METRIC: take-rate revenue by GPU model.
-- Same revenue definition as metric_revenue, grouped by gpu_model.
{{ config(materialized='view') }}

select
    g.gpu_model,
    sum(r.rental_amount * r.take_rate)      as revenue,
    count(*)                                as completed_rentals
from {{ ref('fct_rentals') }} r
join {{ ref('dim_gpu') }} g on r.gpu_key = g.gpu_key
where r.status = 'completed'
group by 1
order by revenue desc

Finally, document the catalog so it shows up in dbt docs and anyone browsing knows these are the blessed definitions:

version: 2

models:
  - name: metric_revenue
    description: >
      SOURCE OF TRUTH for GMV and take-rate revenue, per day.
      gmv = sum(rental_amount); revenue = sum(rental_amount * take_rate);
      both over completed rentals only. Roll up by summing across rental_date.
    columns:
      - name: revenue
        description: Take-rate revenue (what the marketplace keeps).
        tests: [not_null]
      - name: gmv
        description: Gross Merchandise Value (total customer spend).
        tests: [not_null]

  - name: metric_arpu
    description: Average take-rate revenue per active customer, per month.

  - name: metric_revenue_by_model
    description: Take-rate revenue sliced by GPU model. Same revenue definition.

Two consumers, one number

Here's the whole payoff. Two different "consumers" ask for June revenue. Neither one knows or cares how revenue is defined — they just read the metric model. Watch them land on the same number.

Consumer 1 — the BI dashboard wants monthly revenue, so it rolls the daily metric up to a month:

-- "June take-rate revenue" as the dashboard would ask it
select sum(revenue) as june_revenue
from metric_revenue
where year_month = '2026-06';

Consumer 2 — an analyst (or AI assistant) comes in through ARPU but only wants the revenue piece. Because ARPU is built on the same filtered facts, its revenue column must match:

-- Different consumer, different entry point, SAME definition underneath
select revenue as june_revenue
from metric_arpu
where year_month = '2026-06';

Build & query

Build the new metric models, then run both consumer queries and compare:

cd mini-griddp/dbt
dbt run --select metrics          # build the 4 metric views
dbt test --select metrics         # the not_null contracts pass

# now query as the two consumers (DuckDB CLI)
duckdb ../warehouse.duckdb "
  select 'dashboard' as consumer, sum(revenue) as june_revenue
  from metric_revenue where year_month = '2026-06'
  union all
  select 'ad-hoc/AI', revenue
  from metric_arpu where year_month = '2026-06';
"

For a sanity sweep, peek at the model breakdown too — it should tell a coherent business story:

duckdb ../warehouse.duckdb "select * from metric_revenue_by_model;"

Commit Lab 07

The semantic layer is now part of the repo's story — commit it.

cd mini-griddp
git add dbt/models/metrics
git commit -m "Lab 07: semantic layer — metric models (GMV, revenue, ARPU, by-model)"

✓ Check yourself

• Can you explain the "five definitions" problem and why it destroys trust in a dashboard?
• Why does a metric live in one model instead of being written into each consumer's query?
• Why must the two consumer queries above return the identical number — and what does it mean if they don't?
• What does each metric definition pin down besides the arithmetic (hint: filter and grain)?

-- METRIC: average duration of a completed rental, per month.
-- Definition: avg(rental_hours) over completed rentals, per year_month.
{{ config(materialized='view') }}

select
    d.year_month,
    avg(r.rental_hours)     as avg_rental_hours,
    count(*)                as completed_rentals
from {{ ref('fct_rentals') }} r
join {{ ref('dim_date') }} d on r.date_key = d.date_key
where r.status = 'completed'
group by 1
order by d.year_month;

cd mini-griddp/dbt
dbt run --select metric_avg_rental_hours
duckdb ../warehouse.duckdb "select * from metric_avg_rental_hours;"

Next

Your metrics are defined once and proven consistent. Now let's put them in front of a human — a real, clickable dashboard wired straight to the semantic layer. → Lab 08 — Serve It — BI Dashboard