What problem does the Semantic Layer actually solve?

Without it, 'revenue' gets redefined in every BI tool and every analyst's ad-hoc query — one dashboard sums completed orders, another includes returns, a third uses a different date column. The Semantic Layer defines a metric once, in the dbt project, and every consumer (mf, a BI tool, a notebook) queries the same governed definition instead of retyping the SQL.

Do I need a data warehouse to try MetricFlow?

No. Like the rest of this track, dbt-duckdb runs the whole semantic layer locally against a file — mf compiles your metric definitions into SQL and sends it to whatever adapter your profile points at, DuckDB included.

What is a time spine, and why does dbt demand one?

A time spine is a model that is just a calendar — one row per day, no gaps. MetricFlow uses it to fill in dates a metric has zero activity on and to support relative-date queries (last 7 days, month-to-date). It is not optional: a project with metrics but no time spine model fails to parse at all, before you write a single query.

What is the difference between a metric and a measure?

A measure is a raw aggregation tied to one semantic model — `sum(amount)` on the orders model. A metric is what you actually query — it wraps one measure (a `simple` metric) or combines several (a `derived` metric, like `revenue / orders_placed`). You define measures once per semantic model and metrics on top, so `average_order_value` never needs its own SQL.

The Semantic Layer: Defining Metrics Once with MetricFlow

Every dashboard tool eventually reinvents “revenue.” One report sums completed orders, another forgets to exclude returns, a third joins on the wrong date column. The dbt Semantic Layer, queried through the mf CLI, defines a metric once against a model and lets every consumer ask for the same governed number.

The mart underneath

This builds on the same bookshop project as the rest of this track. A fct_orders mart joins completed orders to customers:

-- models/marts/fct_orders.sql
select
    o.order_id,
    o.customer_id,
    c.country,
    o.ordered_at,
    o.amount
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c using (customer_id)

The gotcha before you can query anything

Define a semantic model and metrics, then just try running the project:

# models/semantic/orders_semantic_model.yml
semantic_models:
  - name: orders
    model: ref('fct_orders')
    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: country
        type: categorical
    measures:
      - name: order_amount
        agg: sum
        expr: amount
      - name: order_count
        agg: count
        expr: order_id

metrics:
  - name: revenue
    description: "Total completed order revenue."
    type: simple
    type_params:
      measure: order_amount

dbt run

Parsing Error
  The semantic layer requires a time spine model with granularity DAY or smaller in the
  project, but none was found. Guidance on creating this model can be found on our docs
  site (https://docs.getdbt.com/docs/build/metricflow-time-spine).

The whole project refuses to parse — not just the semantic model, everything, including plain SQL models with no relation to metrics at all. MetricFlow needs a calendar table to fill gaps in time-series queries and resolve relative dates, and it checks for one before it lets dbt do anything.

-- models/marts/metricflow_time_spine.sql
select cast(date_day as date) as date_day
from (
    select unnest(generate_series(
        cast('2020-01-01' as date),
        cast('2030-01-01' as date),
        interval 1 day
    )) as date_day
)

# models/marts/metricflow_time_spine.yml
models:
  - name: metricflow_time_spine
    time_spine:
      standard_granularity_column: date_day
    columns:
      - name: date_day
        granularity: day

dbt seed
dbt run

Finished running 2 table models, 2 view models in 0 hours 0 minutes and 3.07 seconds (3.07s).

Completed successfully

Done. PASS=4 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=4

Querying the metric

mf query --metrics revenue

Success 🦄 - query completed after 0.10 seconds
  revenue
---------
   184.73

184.73 is the sum of amount across every completed order in the seed data — the same number a SELECT SUM(amount) FROM fct_orders would give you, except no one had to write that SQL. Group it by month, and add a second metric in the same query:

mf query --metrics revenue,orders_placed --group-by metric_time__month

Success 🦄 - query completed after 0.35 seconds
metric_time__month      revenue    orders_placed
--------------------  ---------  ---------------
2026-02-01T00:00:00      118.49                4
2026-01-01T00:00:00       66.24                4

metric_time__month is not a column in fct_orders — MetricFlow derives it from the agg_time_dimension: ordered_at declared on the semantic model, at whatever granularity the query asks for.

A metric built from other metrics

  - name: average_order_value
    description: "Revenue divided by order count."
    type: derived
    type_params:
      expr: revenue / orders_placed
      metrics:
        - name: revenue
        - name: orders_placed

mf query --metrics average_order_value --group-by metric_time__month

Success 🦄 - query completed after 0.23 seconds
metric_time__month      average_order_value
--------------------  ---------------------
2026-01-01T00:00:00                 16.56
2026-02-01T00:00:00                 29.6225

No new SQL, no new measure — average_order_value is defined entirely in terms of the two metrics already queried above. Change how revenue is calculated and every derived metric built on it updates automatically.

The second gotcha: dimension names are not bare column names

mf query --metrics revenue --group-by country

Unable to resolve elements ...
  Nearby matches:
    [
      'order_id__country',
      'customer_id',
      'order_id__customer_id',
      'order_id',
      'metric_time__day',
      'order_id__ordered_at__day',
    ]

country alone does not resolve — dimensions are addressed through the entity that owns them, order_id__country, even though order_id is the primary key of the very semantic model country is declared on. The error’s “Nearby matches” list is doing real work here; read it before guessing.

mf query --metrics revenue --group-by order_id__country

Success 🦄 - query completed after 1.00 seconds
order_id__country      revenue
-------------------  ---------
                         19.99
GB                       75.64
NL                       22.1
US                       67

The blank first row is order 1006, whose customer_id is 9 — a customer that does not exist in raw_customers. The left join in fct_orders kept the order and produced a null country instead of dropping it silently. A governed metric does not fix a data problem; it makes the problem visible at the exact place someone is trying to trust a number.

Practice

1. Add an orders_placed metric grouped by order_id__country and compare its shape to revenue's.

mf query --metrics orders_placed --group-by order_id__country

Success 🦄 - query completed after 0.20 seconds
order_id__country      orders_placed
-------------------  ---------------
NL                                  1
                                    1
US                                  2
GB                                  4

Same blank-country row as revenue — it is the same orphaned order, not a metric-specific issue. Any metric built on orders inherits every data-quality gap already in the mart.

2. Filter the query to a single country using --where.

mf query --metrics revenue --where "order_id__country = 'GB'"

ERROR: Runtime Error
  Binder Error: Referenced column "order_id__country" not found in FROM clause!
  Candidate bindings: "revenue"

A third gotcha: --where needs the dimension wrapped in MetricFlow’s own Jinja-style templating, not a bare column reference — that is how it distinguishes a dimension from an arbitrary SQL expression before compiling the filter into the query.

mf query --metrics revenue --where "{{ Dimension('order_id__country') }} = 'GB'"

Success 🦄 - query completed after 0.13 seconds
  revenue
---------
    75.64

3. List every metric the project currently defines.

mf list metrics

🌱 We've found 3 metrics.
The list below shows metrics in the format of "metric_name: list of available dimensions"
• average_order_value: metric_time, order_id__country, order_id__ordered_at
• orders_placed: metric_time, order_id__country, order_id__ordered_at
• revenue: metric_time, order_id__country, order_id__ordered_at

mf list metrics is the fastest way to discover what is already governed before defining a fourth near-duplicate of “revenue” from scratch — and it shows every dimension each metric can be sliced by, without opening a single YAML file.

4. Ask for a narrower time window with --start-time.

mf query --metrics revenue --group-by metric_time__month --start-time 2026-02-01

Success 🦄 - query completed after 0.27 seconds
metric_time__month      revenue
--------------------  ---------
2026-02-01T00:00:00      118.49

January’s row disappears entirely rather than showing as zero — this is exactly what the time spine exists to make correct, filling and bounding the date range instead of leaving it to whatever rows happen to exist in fct_orders.