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, then tell dbt two things in dbt_project.yml: materialize these models as views (cheap, always fresh) in a metrics schema, and hold the take rate as a project variable. That variable is the one place the number 0.20 lives; every metric reads it with var('take_rate'):

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

vars:
  take_rate: 0.20   # the marketplace's cut of every rental; the ONE place this number lives

models:
  mini_griddp:
    staging:
      +schema: silver
      +materialized: view
    marts:
      +schema: gold
      +materialized: table
    metrics:
      +schema: metrics
      +materialized: view

A word on vocabulary first. fct_rentals.revenue is what the customer was billed: hours × the price in force when the rental started. To the marketplace that is GMV; its own revenue is the take. Lab 03 named the column from the GPU owner's point of view, and settling that argument once, here, is exactly the job of this layer. Now 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(fct_rentals.revenue)   what customers were billed (hours x price-at-the-time)
--   revenue = gmv * take_rate            what the marketplace keeps; take_rate is a dbt var
-- Grain: one row per rental_date. Roll up by summing. No rounding here: round where you display.
{{ config(materialized='view') }}

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

Next, ARPU — note it reuses the revenue definition by applying the same expression and the same variable to the same facts, so it can never disagree with metric_revenue:

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

select
    d.year_month,
    count(distinct r.customer_id)                         as active_customers,
    sum(r.revenue) * {{ var('take_rate') }}               as revenue,
    round(sum(r.revenue) * {{ var('take_rate') }}
          / nullif(count(distinct r.customer_id), 0), 2)  as arpu
from {{ ref('fct_rentals') }} r
join {{ ref('dim_date') }} d on r.date_key = d.date_key
group by 1

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 dim_gpu.model.
{{ config(materialized='view') }}

select
    g.model,
    sum(r.revenue) * {{ var('take_rate') }}    as revenue,
    count(*)                                   as completed_rentals
from {{ ref('fct_rentals') }} r
join {{ ref('dim_gpu') }} g on r.gpu_id = g.gpu_id
group by 1
order by revenue desc

And utilization, the ops lead's number, from the Lab 04 rollup. The hourly fact carries sample_count for exactly this reason: an average of hourly averages is only honest when each hour is weighted by how many samples it holds.

-- METRIC: GPU utilization %, per model.
-- Definition: the mean util_pct over every telemetry sample for that model,
--   = sum(avg_util_pct * sample_count) / sum(sample_count) over fct_telemetry_hourly.
{{ config(materialized='view') }}

select
    g.model,
    round(sum(t.avg_util_pct * t.sample_count)
          / nullif(sum(t.sample_count), 0), 1)    as utilization_pct,
    cast(sum(t.sample_count) as bigint)           as samples
from {{ ref('fct_telemetry_hourly') }} t
join {{ ref('dim_gpu') }} g on t.gpu_id = g.gpu_id
group by 1

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(fct_rentals.revenue); revenue = gmv * var('take_rate').
      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 (what customers were billed).
        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.

  - name: metric_utilization
    description: Sample-weighted mean GPU utilization, per model, from the hourly telemetry rollup.

Two consumers, one number

Here's the whole payoff. Two different "consumers" ask for last month's revenue. Neither one knows or cares how revenue is defined — they just read the metric model. Watch them land on the same number. (The generator dates rentals relative to today, so "last month" is computed rather than typed.)

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

-- "last month's take-rate revenue" as the dashboard would ask it
select sum(revenue) as last_month_revenue
from metrics.metric_revenue
where year_month = strftime(current_date - interval 1 month, '%Y-%m');

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

-- Different consumer, different entry point, SAME definition underneath
select revenue as last_month_revenue
from metrics.metric_arpu
where year_month = strftime(current_date - interval 1 month, '%Y-%m');

Build & query

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

cd mini-griddp/dbt
uv run dbt run --select metrics          # build the 4 metric views
uv run 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 last_month_revenue
  from metrics.metric_revenue
  where year_month = strftime(current_date - interval 1 month, '%Y-%m')
  union all
  select 'ad-hoc/AI', revenue
  from metrics.metric_arpu
  where year_month = strftime(current_date - interval 1 month, '%Y-%m');
"

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

duckdb ../warehouse.duckdb "select * from metrics.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 dbt/dbt_project.yml
git commit -m "Lab 07: semantic layer — metric models (GMV, revenue, ARPU, by-model, utilization)"

✓ Check yourself

Answer from memory before opening each one.

1. An analyst reports last month's revenue as $9,600 from sum(revenue) on fct_rentals; Finance reports $1,920. Both used the same fact table and neither made an arithmetic slip. What happened?

   Show answer

   They measured different things under one name. sum(fct_rentals.revenue) is GMV — what customers were billed. Revenue is what the marketplace keeps, GMV × take_rate, here a 20% take. With no shared definition both get called "revenue", and the meeting is spent on whose number is right. See The five-definitions problem and the catalog.

2. metric_revenue is daily so that consumers can roll it up by summing. A consumer wants quarterly ARPU and sums — or averages — the three monthly arpu rows from metric_arpu. Why is the result wrong, and how should the quarterly number be produced?

   Show answer

   ARPU is a ratio over count(distinct customer_id), and distinct counts do not add: a customer active in all three months is one customer for the quarter but is counted in each monthly row. Neither the sum nor the average equals quarter revenue ÷ quarter customers. Additive measures such as gmv and revenue roll up; ratios and distinct counts have to be recomputed from the facts at the new grain — as a metric model with the same expression. See Definitions carry their grain and metric_arpu.

3. The two-consumer query prints 1923.310 for dashboard and 1980.500 for ad-hoc/AI. What does that tell you, and what do you compare first?

   Show answer

   The layer has a leak: metric_revenue and metric_arpu are computing revenue two different ways. Compare the things the contract fixes — the expression sum(revenue) * var('take_rate') (a hard-coded rate in one model is the classic drift), any rounding inside a definition, then the dim_date join. One of them has drifted. See Two consumers, one number.

4. Finance changes the contract twice: first the take rate moves to 18%, then revenue must exclude rentals shorter than one hour. For each change, which files have to change, which do not, and what does that say about the design?

   Show answer

   No consumer changes in either case — the dashboard and the ad-hoc query read the metric models, and that is the win. The rate change is one line in dbt_project.yml, because every model reads var('take_rate'). The filter change is worse: all three revenue models must change, because each one re-types the base selection; miss one and the two-consumer check is what catches you. Pulling the revenue-bearing rows into one shared model that the three ref would make that a single edit too. See Implementing one source of truth.

5. Six months on there are fifteen metrics, and people want each of them by GPU model, by customer and by month, in any combination. Stay with metric models or move to MetricFlow — what decides it?

   Show answer

   With metric models every metric × slice is another hand-written view (as metric_revenue_by_model is), each repeating the definition — fine for a handful, combinatorial beyond that. MetricFlow declares a metric once and generates the SQL for whichever dimensions are requested, at the cost of extra tooling (dbt Cloud or the MetricFlow CLI). Few metrics, few slices, local-only: models win. Many combinations and consumers: MetricFlow. The principle — one governed definition — is the same either way. See the two ways to build it.

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

select
    d.year_month,
    round(avg(r.hours), 1)  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
group by 1
order by d.year_month

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

Takeaway

• A metric is a contract, not a query; if five people write their own revenue SQL you get five numbers and a meeting about whose is right.
• Write the catalog first, with a precise definition, the gold fact it comes from and its grain, and let the SQL only encode it.
• Metrics here are dbt views in a metrics schema, and the take rate (0.20) is a project variable set in exactly one place.
• fct_rentals.revenue is what customers were billed, which is GMV, and the marketplace's own revenue is the take, so name them apart.
• Utilization must weight each hourly average by its sample count, because an average of hourly averages is only honest when each hour carries its weight.
• ARPU applies the same revenue expression to the same facts, so two consumers must return the same last-month revenue, and a mismatch means a leak in the layer.

The metrics are defined once and proven consistent, and the next lab puts them in front of a person on a dashboard. → Lab 08 — Serve It: BI Dashboard

Further reading

• dbt: Project variables — dbt Labs docs, accessed 2026. Defining the take rate once with var().
• dbt: About MetricFlow — dbt Labs docs, accessed 2026. The declarative alternative to the hand-built metric views.
• dbt: Documentation — dbt Labs docs, accessed 2026. Publishing descriptions so the blessed definitions show in dbt docs.
• Dimensional Modeling Techniques — Kimball Group. Additive and non-additive measures, which decide how utilization is computed.