Define metrics once in a semantic layer

By the end of this chapter, you can write the shop's semantic models and metrics in MetricFlow's YAML spec, compile a metric request to SQL with the book's own compiler, show that its entity joins can't fan out, group by the fiscal calendar, and confirm that MetricFlow compiles the same definitions to the same results.

Chapter 1 answered one question from one defined metric, net revenue, written as a few lines of Python with its SQL built by string formatting. That was enough to show the shape the book keeps: the model chooses a governed definition, and code writes the SQL. It isn't enough for the questions the shop's teams ask. Finance wants refund rates by country in fiscal 2026. A category manager wants net revenue by product category, which lives on the order lines, not the orders. Marketing wants average order value by channel. Each of those needs a join, a grain or a ratio, and each is a place where chapter 2 found schema-only SQL going wrong: a join that repeats rows, a definition the schema doesn't hold, a year that doesn't start in January.

A semantic layer is where those choices are written down once. It describes each table by its entities (the keys it can be joined on), its dimensions (what you can group and filter by) and its metrics (the numbers people ask for, with their rules), and it compiles a request for a metric into SQL. The evidence that business meaning in a model's context helps is thin. The one benchmark we rely on, over one enterprise insurance schema, found that GPT-4 answered 16% of questions correctly over the SQL tables and 54% over a knowledge-graph layer that carried the business's meaning (Sequeda et al., 2023). The authors work for a knowledge-graph vendor, and the study covers one schema and one model, so read it as a sign that meaning helps, not as a multiplier you will see.

This chapter builds the shop's semantic layer in MetricFlow's YAML, compiles it with a small compiler of the book's own, lib/semantic.py, and checks it against MetricFlow 0.213.0, which reads the same files. MetricFlow is the semantic layer dbt Labs maintains, available under the Apache 2.0 licence since Oct 14, 2025, from version 0.209.0 on (dbt Labs; PyPI). The book uses its own compiler so that you can read every step from request to SQL, and so that the examples change only when we change them.

Describe each table as a semantic model

A semantic model is one table described for the layer: which table it reads, the entities it holds, the dimensions it offers and the time dimension its numbers are counted on. An entity is a key with a type. A primary entity is unique in its table, so other tables can join to it many rows to one. A foreign entity is a key that points at another table's primary entity, and it repeats. That one word, primary or foreign, is what lets a layer join tables without repeating rows, so it is the most important thing you declare. The shop's whole layer lives in examples/data/semantic/: one file per semantic model, one metrics.yml, and one project_configuration.yml, which names the time spine, a table with one row per day.

Older MetricFlow specs have a third kind of element, the measure: an aggregation declared on the semantic model, which a metric then names. dbt's documentation for its spec in dbt v1.12 and later says "Measures are deprecated in the new spec and are replaced with simple metrics" (dbt Labs). The shop's YAML follows that direction. Each simple metric names its semantic model, its aggregation and its expression itself, in the standalone document shape that MetricFlow's own parser reads: one semantic_model: or metric: per YAML document, and tables named by node_relation. Here is the shop's orders model, shortened, and net revenue, the metric chapter 1 defined in Python:

semantic_model:
  name: orders
  node_relation:
    schema_name: analytics
    alias: fct_orders
  defaults:
    agg_time_dimension: order_date
  entities:
    - name: order
      type: primary
      expr: order_id
    - name: customer
      type: foreign
      expr: customer_id
  dimensions:
    - name: order_date
      type: time
      expr: order_date_local
      type_params:
        time_granularity: day
    - name: region
      type: categorical
---
metric:
  name: net_revenue
  type: simple
  type_params:
    expr: net_revenue_eur
    metric_aggregation_params:
      semantic_model: orders
      agg: sum
  filter: "{{ Dimension('order__status') }} != 'cancelled'"

The orders model's time dimension is order_date_local, the order's local business day, so every metric on it counts days the way finance does. Net revenue's rule for which orders count, the one chapter 1's schema-only SQL missed, is the metric's filter. metrics.yml also holds net revenue's description, word for word as chapter 1 wrote it, and chapter 1's lib/metrics.py reads its table, aggregate and condition from this YAML, so the definition exists in one place.

The first example loads the layer, lists what each semantic model declares, and checks the definitions against the warehouse: every expression must run, and every primary entity must be unique in its table. Then it checks a copy with one mistake, in which the order lines declare order as their primary entity.

1"""Load the shop's semantic layer from YAML, list what each semantic model declares, and check it against the warehouse."""2from dataclasses import replace34from lib.semantic import Entity, SemanticLayer, load5from lib.warehouse import connect67layer = load()                   # reads examples/data/semantic/*.yml and validates the definitions8print(f"semantic models: {len(layer.semantic_models)}")9print(f"metrics: {len(layer.metrics)}")10for model in layer.semantic_models.values():11    entities = ", ".join(f"{e.name} ({e.type})" for e in model.entities.values())12    print(f"{model.name}: {model.table}; entities: {entities}; dimensions: {', '.join(model.dimensions) or 'none'}")1314metric = layer.metrics["net_revenue"]15print(f"net_revenue: {metric.agg} of {metric.expr} in {metric.semantic_model}, where {metric.filter}")1617con = connect()18print(f"problems in the shop's definitions: {len(layer.check(con))}")1920# A layer with one mistake: the lines declare `order` as their primary entity, as if each order had one line.21lines = layer.semantic_models["order_lines"]22wrong = replace(lines, entities={"order": Entity("order", "primary", "order_id"),23                                 "product": lines.entities["product"]})24for problem in SemanticLayer({"order_lines": wrong}, {}, layer.time_spine).check(con):25    print(f"problem: {problem}")

semantic models: 5
metrics: 10
customers: analytics.dim_customers; entities: customer (primary); dimensions: first_order_date, region, country
order_lines: semantic.order_lines; entities: order_line (primary), order (foreign), product (foreign); dimensions: order_date
orders: analytics.fct_orders; entities: order (primary), customer (foreign); dimensions: order_date, region, country, channel, status
products: analytics.dim_products; entities: product (primary); dimensions: product_name, category, subcategory
refunds: semantic.refunds; entities: refund (primary), order (foreign); dimensions: refund_date, order_date, reason
net_revenue: sum of net_revenue_eur in orders, where {{ Dimension('order__status') }} != 'cancelled'
problems in the shop's definitions: 0
problem: order_lines: primary entity 'order' repeats in 23,215 rows

The shop has 5 semantic models and 10 metrics, and the check finds 0 problems in them. The mistaken copy fails it: order_lines: primary entity 'order' repeats in 23,215 rows. An order has several lines, so order_id repeats in the lines table, and a layer that believed it unique would join orders to lines as if each order had one line. Parsing the YAML can't find this, because the file is valid. Only the data can, which is why check() counts duplicates. Two of the semantic models, and the time spine, read views in a third schema of the warehouse, semantic, which this chapter adds; the fiscal calendar section and the production section say why.

Working rule: Declare one semantic model per table, mark an entity primary only where it's unique, and check the declarations against the data whenever the warehouse or the YAML changes. Write simple metrics without measures, naming their model and aggregation, unless your MetricFlow version or your dbt project still requires measures.

Compile a metric request to SQL

With the shop's definitions loaded, the layer's job is to turn a request into SQL. A metric request names the metrics, the dimensions to group by and the filters, and optionally an order and a limit. It holds names, not SQL, so everything in it can be checked against the definitions before anything runs. The compiler then finds each dimension's column, adds the joins and the metric's own filter, and writes the aggregate.

The book's compiler builds the query as a sqlglot expression tree rather than as text. sqlglot is a Python library that parses, builds and translates SQL. Building a tree has two consequences. A filter value becomes a literal node in the tree, so text inside it can't change the query's structure. And the same tree can be written in another warehouse's dialect, which the dialect subsection uses.

Dimension names follow MetricFlow's convention, so a request reads the same in either tool:

The example answers chapter 1's question, golden question gq-002: "What was net revenue in each region in March 2026?" The example asks for net_revenue grouped by order__region and filtered to March 2026, prints the request and the SQL, runs it, and compares the answer with the golden result. Then it sends a filter value that holds SQL of its own, and a request for a dimension the layer doesn't define.

1"""Compile one metric request to SQL with the book's semantic layer, run it, and compare it with the golden answer."""2from lib.golden import golden_result, load_golden, results_match3from lib.semantic import MetricRequest, SemanticError, load4from lib.warehouse import connect56layer, con = load(), connect()7question = next(q for q in load_golden() if q.question_id == "gq-002")8print(f"question: {question.question}")910request = MetricRequest(metrics=("net_revenue",), group_by=("order__region",),11                        filters={"metric_time__month": "2026-03"})12print(f"request: {request}")13print(layer.sql(request))1415answer = layer.query(con, request)16print(answer.show())17print(f"matches golden: {results_match(answer.rows, golden_result(con, question))}")1819# A filter value becomes a literal in the expression tree, so text in it can't change the query.20hostile = MetricRequest(("net_revenue",), filters={"order__region": "EMEA' OR '1'='1"})21condition = layer.compile(hostile).args["where"].sql(dialect="duckdb")22print(f"hostile filter compiles to: {condition}")23print(f"rows for the hostile value: {layer.query(con, hostile).rows}")2425# A name the layer doesn't define is refused before any SQL exists, with what the metric can use.26try:27    layer.compile(MetricRequest(("net_revenue",), ("order__colour",)))28except SemanticError as e:29    print(f"refused: {e}")

question: What was net revenue in each region in March 2026?
request: MetricRequest(metrics=('net_revenue',), group_by=('order__region',), filters={'metric_time__month': '2026-03'}, order_by=(), limit=None)
SELECT
  orders.region AS order__region,
  SUM(orders.net_revenue_eur) AS net_revenue
FROM analytics.fct_orders AS orders
WHERE
  orders.status <> 'cancelled'
  AND CAST(DATE_TRUNC('MONTH', CAST(orders.order_date_local AS DATE)) AS DATE) = CAST('2026-03-01' AS DATE)
GROUP BY
  orders.region
ORDER BY
  order__region ASC
order__region  net_revenue
AMER           51,762.14
APAC           35,296.93
EMEA           63,023.76
matches golden: True
hostile filter compiles to: WHERE orders.status <> 'cancelled' AND orders.region = 'EMEA'' OR ''1''=''1'
rows for the hostile value: ((None,),)
refused: unknown dimension 'order__colour'; 'net_revenue' can use: metric_time__day, metric_time__week, metric_time__month, metric_time__quarter, metric_time__year, metric_time__fiscal_year, metric_time__fiscal_quarter, customer__country, customer__first_order_date, customer__region, order__channel, order__country, order__order_date, order__region, order__status

The SQL reads one table, analytics.fct_orders, applies the metric's filter on cancelled orders, truncates the local business day to its month and groups by region. It's what an analytics engineer would write by hand, and it matches the golden result: matches golden: True. The month is written as a date and compared with a date cast, so the comparison doesn't depend on how a warehouse compares text.

The hostile value doesn't change the query. It compiles to WHERE orders.status <> 'cancelled' AND orders.region = 'EMEA'' OR ''1''=''1': one string literal, its quotes doubled, compared with orders.region. No region has that name, so the query returns one row with an empty total. Chapter 1's metric passed values as query parameters for the same reason; a tree of literals does the same job and still lets the query be translated to another dialect.

What a request can't name matters as much. A request for a metric or dimension that isn't defined fails before any SQL exists. The example's request for order__colour ends in a SemanticError that names the unknown dimension and lists every dimension net revenue can use, from metric_time__day to order__status. That error is what an agent sees when it asks for something the layer doesn't hold, and chapter 6 builds on it.

Working rule: Let the agent, and every other client, send metric requests, never SQL fragments. Resolve every name in a request against the definitions, build the query as a tree with values as literals, and show the SQL with every answer so a person can read what ran. Accept a raw SQL filter only from the people who maintain the layer, never from a model.

Join through entities so sums can't fan out

The compiled request read one table. Many questions need two. A fan-out happens when a join matches each row of one table to several rows of another, so a sum over the joined rows counts each value of the first table several times. A semantic layer prevents it with the entity types: it joins from a metric's table only to a table where the entity is primary, many rows to one, so each row of the metric's table matches at most one row. MetricFlow's documentation lists a join from a primary to a foreign entity as "Fan-out (Not allowed)" and allows at most two hops (dbt Labs). The book's compiler applies the same two rules.

The shop's case is a category manager's question: net revenue by product category in 2025. Net revenue is summed per order, in analytics.fct_orders, and a product's category is reached through the order lines, several per order. The example asks the layer for net_revenue by product__category, runs the hand-written join a model would write, and then asks for the line-level metric instead.

1"""Ask for net revenue by product category: the layer refuses the fan-out join and points to a line-level metric."""2from lib.semantic import FanOutError, MetricRequest, load3from lib.warehouse import connect45layer, con = load(), connect()6in_2025 = {"metric_time__year": 2025}78try:9    layer.sql(MetricRequest(("net_revenue",), ("product__category",), in_2025))10except FanOutError as e:11    print(f"refused: {e}")1213# What a hand-written join does with the same question: each order's revenue repeats once per line.14(joined,), = con.execute("""15    SELECT sum(o.net_revenue_eur) FROM analytics.fct_orders AS o16    JOIN analytics.fct_order_lines AS l USING (order_id)17    WHERE o.status <> 'cancelled' AND year(o.order_date_local) = 2025""").fetchall()18(total,), = layer.query(con, MetricRequest(("net_revenue",), filters=in_2025)).rows19print(f"net revenue, 2025: {total:,.2f}")20print(f"summed over the joined lines: {joined:,.2f}")21print(f"inflated by: {joined / total - 1:.0%}")2223request = MetricRequest(("line_net_revenue",), ("product__category",), in_2025, order_by=("-line_net_revenue",))24print(layer.sql(request))25answer = layer.query(con, request)26print(answer.show())27by_category = sum(row[1] for row in answer.rows)28print(f"categories add up to: {by_category:,.2f}")29print(f"difference from net revenue: {abs(by_category - total):,.2f}")

refused: 'net_revenue' is summed per order, but 'product' is reached through 'order_lines', which has many rows per order; each order would count once per row. Use a metric defined on 'order_lines': line_net_revenue, units_sold
net revenue, 2025: 1,651,527.86
summed over the joined lines: 5,573,978.43
inflated by: 238%
SELECT
  products.category AS product__category,
  SUM(order_lines.net_amount_eur) AS line_net_revenue
FROM semantic.order_lines AS order_lines
LEFT JOIN analytics.dim_products AS products
  ON order_lines.product_id = products.product_id
LEFT JOIN analytics.fct_orders AS orders
  ON order_lines.order_id = orders.order_id
WHERE
  orders.status <> 'cancelled'
  AND CAST(DATE_TRUNC('YEAR', CAST(order_lines.order_date_local AS DATE)) AS DATE) = CAST('2025-01-01' AS DATE)
GROUP BY
  products.category
ORDER BY
  line_net_revenue DESC
product__category  line_net_revenue
Sleeping bags      279,693.27
Backpacks          246,002.99
Tents              230,502.32
Footwear           216,840.39
Navigation         212,322.26
Cooking            202,007.56
Clothing           178,584.24
Lighting           85,573.93
categories add up to: 1,651,526.96
difference from net revenue: 0.90

The layer refuses the request and says why: 'net_revenue' is summed per order, but 'product' is reached through 'order_lines', which has many rows per order; each order would count once per row. Use a metric defined on 'order_lines': line_net_revenue, units_sold. The hand-written join shows what the refusal prevents. Net revenue in 2025 is 1,651,527.86 euros. Summed over the orders joined to their lines, the same revenue comes to 5,573,978.43, inflated by 238%, because each order counts once for each of its lines. Nothing in that query fails, and nothing in its result says it's wrong.

line_net_revenue is net revenue counted on the lines themselves, in the order_lines semantic model, with the same rule on cancelled orders. Its SQL joins the lines to products and to orders, both many rows to one, so no line repeats. The categories add up to 1,651,526.96 euros, 0.90 away from net revenue. That gap is rounding, not fan-out: the warehouse rounds each line's amount and each order's total to the cent separately, and over a year of lines the cents add up.

When a request needs numbers from two tables, a layer doesn't join the tables first. It aggregates each metric in its own table, then joins the aggregated results on the dimensions they share; MetricFlow uses full outer joins for that step (dbt Labs), and so does the book's compiler. Each sum is complete before any join, so no join can repeat its rows.

The protection holds only as far as the declarations are true. Declare the lines' order entity primary, as the loading example did on purpose, and the layer would join orders to lines without complaint.

Working rule: Declare entities so that every join the layer makes is many rows to one, and let the layer refuse the rest. When a question needs a number on a finer table, such as revenue by product, define the metric on that table rather than reaching it through a join. Check that primary entities are unique in the data, because a wrong declaration turns the protection off.

Build ratio and derived metrics from simple ones

Simple metrics, like the line-level revenue, aggregate one column of one table. Many of the numbers people ask for are built from two: a refund rate, an average order value. A ratio metric divides one metric by another, and a derived metric combines several with an expression. What matters is the order of operations: a ratio is the sum of the numerator over the sum of the denominator, per group, never the average of row-by-row ratios. MetricFlow computes the numerator and the denominator in separate subqueries, joins them on their common dimensions and then divides (dbt Labs).

The shop's finance team defines the refund rate in golden question gq-028: refunded euros over gross revenue of the same orders, those placed in the period that weren't cancelled. Two choices hide in that sentence. The refunds belong to the order's day, not the day the money went back, so the numerator is order_refunds, a metric counted on the order's local day; refunds, counted on the day of the refund, answers a different question. And the denominator is gross revenue, not net revenue. A refund pays back what the customer was charged, tax included, and gross revenue includes tax while net revenue leaves it out, so only gross revenue measures both sides in the same euros; over net revenue, a fully refunded order would have a refund rate above 100%. The book uses this one definition everywhere, in the glossary and the verified queries as well. The numerator carries the rule on cancelled orders as an input filter, and gross_revenue carries it in its own definition, so each side applies it.

The example compiles the refund rate by region for fiscal 2026 and prints its SQL. Then it computes the tempting version, each order's refunds over its gross revenue averaged per region, and checks two golden questions: the country with the highest refund rate, and average order value by region in March 2026.

1"""A ratio metric divides two separately aggregated sums; averaging per-order ratios gives a different number."""2from lib.golden import golden_result, load_golden, results_match3from lib.semantic import MetricRequest, load4from lib.warehouse import connect56layer, con = load(), connect()7fiscal_2026 = {"metric_time__fiscal_year": 2026}89request = MetricRequest(("refund_rate",), ("order__region",), fiscal_2026)10print(layer.sql(request))11for region, rate in layer.query(con, request).rows:12    print(f"refund rate, {region}: {rate:.2%}")1314# The tempting shortcut: each order's refunds over its gross revenue, averaged per region.15for region, rate in con.execute("""16    SELECT o.region, avg(coalesce(r.refunded, 0) / o.gross_revenue_eur)17    FROM analytics.fct_orders AS o18    JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local19    LEFT JOIN (SELECT order_id, sum(amount_eur) AS refunded FROM analytics.fct_refunds GROUP BY order_id) AS r20        USING (order_id)21    WHERE o.status <> 'cancelled' AND d.fiscal_year = 202622    GROUP BY o.region ORDER BY o.region""").fetchall():23    print(f"average of per-order rates, {region}: {rate:.2%}")2425golden = {q.question_id: q for q in load_golden()}26top = layer.query(con, MetricRequest(("refund_rate",), ("order__country",), fiscal_2026, ("-refund_rate",), 1))27print(f"highest refund rate: {top.rows[0][0]}")28print(f"gq-028 matches golden: {results_match([top.rows[0][:1]], golden_result(con, golden['gq-028']))}")2930average = layer.query(con, MetricRequest(("average_order_value",), ("order__region",), {"metric_time__month": "2026-03"}))31print(average.show())32print(f"gq-009 matches golden: {results_match(average.rows, golden_result(con, golden['gq-009']))}")

WITH order_refunds AS (
  SELECT
    orders.region AS order__region,
    SUM(refunds.amount_eur) AS order_refunds
  FROM semantic.refunds AS refunds
  LEFT JOIN analytics.fct_orders AS orders
    ON refunds.order_id = orders.order_id
  LEFT JOIN semantic.calendar AS calendar
    ON calendar.date_day = refunds.order_date_local
  WHERE
    orders.status <> 'cancelled' AND calendar.fiscal_year = 2026
  GROUP BY
    orders.region
), gross_revenue AS (
  SELECT
    orders.region AS order__region,
    SUM(orders.gross_revenue_eur) AS gross_revenue
  FROM analytics.fct_orders AS orders
  LEFT JOIN semantic.calendar AS calendar
    ON calendar.date_day = orders.order_date_local
  WHERE
    orders.status <> 'cancelled' AND calendar.fiscal_year = 2026
  GROUP BY
    orders.region
)
SELECT
  COALESCE(order_refunds.order__region, gross_revenue.order__region) AS order__region,
  order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
FROM order_refunds
FULL OUTER JOIN gross_revenue
  ON gross_revenue.order__region = order_refunds.order__region
ORDER BY
  order__region ASC
refund rate, AMER: 7.11%
refund rate, APAC: 6.02%
refund rate, EMEA: 8.45%
average of per-order rates, AMER: 7.77%
average of per-order rates, APAC: 6.78%
average of per-order rates, EMEA: 8.38%
highest refund rate: NL
gq-028 matches golden: True
order__region  average_order_value
AMER           300.94
APAC           291.71
EMEA           272.83
gq-009 matches golden: True

The SQL has one part per input. order_refunds sums refunds joined to their orders, filtered and dated by the order, and gross_revenue sums the orders. The outer query joins the two on region and divides, with NULLIF so that a region with no revenue gets no rate instead of an error. EMEA's refund rate is 8.45%, AMER's 7.11% and APAC's 6.02%.

The average of per-order rates gives other numbers: 7.77% for AMER against the layer's 7.11%, and 8.38% for EMEA against 8.45%. It weights a small order's refund as heavily as a large one's, so it answers what share of a typical order is refunded, which nobody asked. Here it moves AMER and APAC up and EMEA down, so the regions look closer together than the money says they are.

The highest refund rate by country is NL, which matches the golden answer: True. Average order value is a derived metric, net_revenue / kept_orders, where kept_orders is the orders metric with the rule on cancelled orders applied as an input filter. The shop counts orders with cancelled ones included, but divides revenue by orders without them, and the derived metric keeps both definitions. It matches golden question gq-009: True.

Working rule: Define every rate as a ratio of two metrics, so each side is aggregated on its own and divided per group. Write down which time dimension each side is counted on, and make sure each side applies its filter, in its own definition or as an input filter, rather than relying on readers to know. Use a derived metric when the expression is more than one division, and keep its inputs simple metrics you can query on their own.

Put the fiscal calendar in the time spine

Every metric in the shop's layer is counted on a time dimension, and most questions name a period. A layer handles standard grains, such as month, quarter and year, by truncating the date. Fiscal periods can't be computed that way, because they start where the business says they do. MetricFlow handles both through the time spine, a table with one row per day that every time-based metric needs, at least at a daily grain. A custom granularity, such as fiscal_year, is a column of the time spine that names the period each day belongs to (dbt Labs).

The shop's fiscal year starts on February 1 and is named for the year it ends in, so fiscal 2026 runs from February 1, 2025 to January 31, 2026. The warehouse's analytics.dim_date holds fiscal_year and fiscal_quarter, but its quarter is a number from 1 to 4, which repeats every year. A custom grain must name one period, so the view semantic.calendar labels each quarter with its year, as in 2026-Q1, and the project configuration declares fiscal_year and fiscal_quarter as custom grains of that view. A request then groups by metric_time__fiscal_quarter like any other grain.

The example groups net revenue by fiscal quarter in fiscal 2026 and prints the SQL, groups it by calendar quarter in 2025 to show the difference, and checks two golden questions about first fiscal quarters.

1"""Group net revenue by fiscal quarter, a custom grain from the time spine, and by calendar quarter, a standard one."""2from datetime import date34from lib.golden import golden_result, load_golden, results_match5from lib.semantic import MetricRequest, load6from lib.warehouse import connect78layer, con = load(), connect()910fiscal = MetricRequest(("net_revenue",), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026})11print(layer.sql(fiscal))12fiscal_answer = layer.query(con, fiscal)13print(fiscal_answer.show())1415calendar = MetricRequest(("net_revenue",), ("metric_time__quarter",), {"metric_time__year": 2025})16calendar_answer = layer.query(con, calendar)17print(calendar_answer.show())18print(f"fiscal 2026-Q1, February to April 2025: {dict(fiscal_answer.rows)["2026-Q1"]:,.2f}")19print(f"calendar 2025 Q1, January to March 2025: {dict(calendar_answer.rows)[date(2025, 1, 1)]:,.2f}")2021golden = {q.question: q for q in load_golden()}22for text, request in [23    ("What was net revenue in the first quarter of fiscal year 2026?",24     MetricRequest(("net_revenue",), filters={"metric_time__fiscal_quarter": "2026-Q1"})),25    ("What was net revenue in each region in the first quarter of fiscal year 2027?",26     MetricRequest(("net_revenue",), ("order__region",), {"metric_time__fiscal_quarter": "2027-Q1"})),27]:28    question = golden[text]29    answer = layer.query(con, request)30    print(f"{question.question_id} matches golden: {results_match(answer.rows, golden_result(con, question))}")

SELECT
  calendar.fiscal_quarter AS metric_time__fiscal_quarter,
  SUM(orders.net_revenue_eur) AS net_revenue
FROM analytics.fct_orders AS orders
LEFT JOIN semantic.calendar AS calendar
  ON calendar.date_day = orders.order_date_local
WHERE
  orders.status <> 'cancelled' AND calendar.fiscal_year = 2026
GROUP BY
  calendar.fiscal_quarter
ORDER BY
  metric_time__fiscal_quarter ASC
metric_time__fiscal_quarter  net_revenue
2026-Q1                      324,929.25
2026-Q2                      467,081.90
2026-Q3                      420,686.84
2026-Q4                      452,065.73
metric_time__quarter  net_revenue
2025-01-01            302,935.85
2025-04-01            415,495.84
2025-07-01            457,906.36
2025-10-01            475,189.81
fiscal 2026-Q1, February to April 2025: 324,929.25
calendar 2025 Q1, January to March 2025: 302,935.85
gq-010 matches golden: True
gq-026 matches golden: True

The fiscal request joins the orders to semantic.calendar on the local business day and groups by the calendar's quarter label. The calendar quarters of 2025 start on January 1, April 1, July 1 and October 1, while fiscal 2026's first quarter runs from February to April 2025, so no quarter in one grouping equals a quarter in the other. Fiscal 2026's first quarter, February to April 2025, brought 324,929.25 euros of net revenue; the calendar's first quarter of 2025, January to March, brought 302,935.85. Both golden questions match: net revenue in the first quarter of fiscal 2026, gq-010 (True), and by region in the first quarter of fiscal 2027, gq-026 (True).

Two limits apply. MetricFlow "doesn't perform any timezone manipulation" (dbt Labs), so the local business day has to exist in the warehouse before the layer sees it. The shop's marts build order_date_local from each region's time zone, and the views add the same for refunds. And the same documentation marks custom calendars as a preview feature, without offsets or period-over-period comparisons at custom grains, so "fiscal Q1 against the same quarter last year" takes two requests, not one.

Working rule: Put every fiscal and business calendar in the time spine as custom grains whose values each name one period, and let requests group by them. Build local business days in the marts, before the layer. When you need a comparison across fiscal periods, run one request per period and compare the results in code, until your layer supports offsets at custom grains.

Check the definitions against MetricFlow

The shop's YAML is portable only if a production tool reads it the way the book's compiler does. In production, MetricFlow usually runs inside a dbt project, through the dbt-metricflow package and its command line. That package's summary reads "Execute commands against the MetricFlow semantic layer with dbt", and it requires dbt-core (PyPI). The book checks its definitions with MetricFlow alone, the metricflow package, which is Apache 2.0 and has no dbt dependency, so the check runs offline in seconds without installing dbt and its dependencies. MetricFlow parses and validates the same files, and its engine's explain() returns the SQL it would run. Its Python engine, MetricFlowEngine, is internal API at a 0.x version: the class's docstring, in metricflow/engine/metricflow_engine.py of the 0.213.0 wheel (PyPI), leaves its maintainers a note to "provide a more stable API layer instead of assuming this class is stable". The book keeps everything that touches it in lib/metricflow_bridge.py, with a small adapter that lets MetricFlow run SQL on DuckDB.

The example compiles 8 requests both ways, covering simple, ratio and derived metrics, a two-hop join, the fiscal grains and two time dimensions on one table, runs both statements on the shop's warehouse and compares the rows. It prints MetricFlow's SQL for chapter 1's question, then sends MetricFlow the request the join section refused.

1"""Compile the same requests with MetricFlow and with the book's compiler, run both, and compare the results."""2from lib.golden import results_match3from lib.metricflow_bridge import as_dates, engine, explain_sql4from lib.semantic import MetricRequest as Request, load5from lib.warehouse import connect67con = connect()8layer, mf = load(), engine(con)   # both read examples/data/semantic/*.yml9requests = [10    Request(("net_revenue",), ("order__region",), {"metric_time__month": "2026-03"}),11    Request(("net_revenue", "orders"), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026}),12    Request(("line_net_revenue", "units_sold"), ("product__category",), {"metric_time__year": 2025}),13    Request(("units_sold",), ("order__customer__region",), {"metric_time__year": 2025}),14    Request(("refund_rate",), ("order__country",), {"metric_time__fiscal_year": 2026}),15    Request(("average_order_value",), ("order__channel",), {"metric_time__month": "2026-03"}),16    Request(("refunds", "order_refunds"), ("metric_time__month",), {"metric_time__year": 2025}),17    Request(("new_customers",), ("customer__region",), {"metric_time__year": 2025}),18]192021agree = 022for request in requests:23    ours = con.execute(layer.sql(request)).fetchall()24    theirs = as_dates(con.execute(explain_sql(mf, request)).fetchall())25    same = results_match(ours, theirs, tolerance=1e-9)26    agree += same27    print(f"{', '.join(request.metrics)} by {', '.join(request.group_by)}: {len(ours)} rows, agree: {same}")28month = requests[6]29print(f"MetricFlow's type for metric_time__month: {type(con.execute(explain_sql(mf, month)).fetchone()[0]).__name__}")30print(f"the book's type for metric_time__month: {type(con.execute(layer.sql(month)).fetchone()[0]).__name__}")31print(f"requests compared: {len(requests)}")32print(f"requests that agree: {agree}")3334print(explain_sql(mf, requests[0]))35try:36    explain_sql(mf, Request(("net_revenue",), ("product__category",), {"metric_time__year": 2025}))37except Exception as e:38    print(f"MetricFlow on net_revenue by product__category: {type(e).__name__}")39    lines = [line.strip() for line in str(e).splitlines()]40    cause = lines.index("* No valid join paths exist from the measure to the group-by-item.")41    print(f"one cause MetricFlow lists: {lines[cause][2:]} {lines[cause + 1]}")

net_revenue by order__region: 3 rows, agree: True
net_revenue, orders by metric_time__fiscal_quarter: 4 rows, agree: True
line_net_revenue, units_sold by product__category: 8 rows, agree: True
units_sold by order__customer__region: 3 rows, agree: True
refund_rate by order__country: 10 rows, agree: True
average_order_value by order__channel: 4 rows, agree: True
refunds, order_refunds by metric_time__month: 12 rows, agree: True
new_customers by customer__region: 3 rows, agree: True
MetricFlow's type for metric_time__month: datetime
the book's type for metric_time__month: date
requests compared: 8
requests that agree: 8
-- Constrain Output with WHERE
-- Select: ['__net_revenue', 'order__region']
-- Aggregate Inputs for Simple Metrics
-- Compute Metrics via Expressions
-- Order By ['order__region']
-- Write to DataTable
SELECT
  order__region
  , SUM(net_revenue) AS net_revenue
FROM (
  -- Read Elements From Semantic Model 'orders'
  -- Metric Time Dimension 'order_date'
  -- Select: ['__net_revenue', 'order__region', 'order__status', 'metric_time__month']
  SELECT
    DATE_TRUNC('month', order_date_local) AS metric_time__month
    , region AS order__region
    , status AS order__status
    , net_revenue_eur AS net_revenue
  FROM analytics.fct_orders orders_src_10000
) subq_2
WHERE (
  order__status != 'cancelled'
) AND (
  metric_time__month = '2026-03-01'
)
GROUP BY
  order__region
ORDER BY order__region
MetricFlow on net_revenue by product__category: InvalidQueryException
one cause MetricFlow lists: No valid join paths exist from the measure to the group-by-item. (fan-out join support is pending).

All 8 requests return the same rows. The one difference the comparison has to allow is a type: MetricFlow returns a month as a datetime, a timestamp at midnight, while the book's compiler casts it to a date, so the example turns timestamps into dates before it compares. MetricFlow's SQL is longer, with a comment naming each step, and it reaches the same three regional totals. It refuses net revenue by product category in 2025 too, with an InvalidQueryException. Among the common issues its message lists is the one the join section met: "No valid join paths exist from the measure to the group-by-item. (fan-out join support is pending)."

MetricFlow also asks more of the YAML than the book's compiler does: every semantic model that a simple metric counts on needs a time dimension of its own. The next example builds MetricFlow's engine from the shop's YAML, then from a copy that points order_lines at analytics.fct_order_lines, the analytics table, which has no order day, and drops the model's time dimension.

1"""Show that MetricFlow won't build its engine when a fact's semantic model has no date of its own."""2import shutil3import tempfile4from pathlib import Path56import yaml78from lib.metricflow_bridge import engine9from lib.semantic import SEMANTIC_DIR10from lib.warehouse import connect1112con = connect()13print(f"MetricFlow with the shop's YAML: {type(engine(con)).__name__}")1415# A copy in which order_lines reads analytics.fct_order_lines, which has no order day, and declares no date.16with tempfile.TemporaryDirectory() as tmp:17    folder = Path(tmp) / "semantic"18    shutil.copytree(SEMANTIC_DIR, folder)19    path = folder / "order_lines.yml"20    document = yaml.safe_load(path.read_text())21    lines_model = document["semantic_model"]22    lines_model["node_relation"] = {"schema_name": "analytics", "alias": "fct_order_lines"}23    del lines_model["defaults"], lines_model["dimensions"]24    path.write_text(yaml.safe_dump(document, sort_keys=False))25    try:26        engine(con, folder)27        print("MetricFlow without a date on order_lines: built")28    except Exception as e:29        print(f"MetricFlow without a date on order_lines: {type(e).__name__}")30        print(f"its message begins: {str(e).splitlines()[0]}")

MetricFlow with the shop's YAML: MetricFlowEngine
MetricFlow without a date on order_lines: InvalidManifestException
its message begins: Invalid aggregation time dimension configuration

The shop's YAML builds a MetricFlowEngine. The copy doesn't: MetricFlow raises an InvalidManifestException whose message begins "Invalid aggregation time dimension configuration". That is why the shop's order_lines model reads the view semantic.order_lines, which adds each order's local day.

Working rule: Compare your layer with MetricFlow, or whichever tool serves it in production, on every request shape you rely on, and run the comparison whenever either side changes. Pin MetricFlow's version exactly and keep its engine behind one module, because its Python API can change in any release.

One definition, any warehouse's dialect

A layer that compiles to an expression tree, as the book's compiler does, can write that tree for another warehouse. sqlglot 30.29.0 calls itself "a transpiler, not a validator", warns that some queries can't be translated between some dialects, and by default makes a best-effort translation unless unsupported_level is set to raise (PyPI). The example writes the refund rate by calendar quarter in six dialects and prints the two lines that differ: the quarter and the division. It also prints BigQuery's quarter as sqlglot writes it from an uncast column.

1"""Write one compiled metric request in six warehouses' dialects, and show what transpiling doesn't check."""2import sqlglot3from sqlglot import ErrorLevel45from lib.semantic import MetricRequest, load67request = MetricRequest(("refund_rate",), ("metric_time__quarter",), {"metric_time__year": 2025})8compiled = load().compile(request)910for dialect in ("duckdb", "snowflake", "bigquery", "databricks", "postgres", "tsql"):11    sql = compiled.sql(dialect=dialect, pretty=True, unsupported_level=ErrorLevel.RAISE)12    lines = [line.strip().rstrip(",") for line in sql.splitlines()]13    print(f"{dialect} quarter: {next(line for line in lines if 'TRUNC' in line)}")14    print(f"{dialect} ratio: {next(line for line in lines if 'NULLIF' in line)}")1516# The compiler casts the column to DATE before truncating. Without the cast, BigQuery gets TIMESTAMP_TRUNC.17uncast = sqlglot.transpile("SELECT DATE_TRUNC('quarter', order_date_local)", read="duckdb", write="bigquery")[0]18print(f"bigquery without the cast: {uncast}")1920# Transpiling translates syntax; it doesn't check that the target warehouse has the function.21tsql = sqlglot.transpile("SELECT * FROM customers WHERE country ILIKE 'g%'", read="duckdb", write="tsql",22                         unsupported_level=ErrorLevel.RAISE)[0]23print(f"ILIKE in T-SQL: {tsql}")

duckdb quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
duckdb ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
snowflake quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
snowflake ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
bigquery quarter: CAST(DATE_TRUNC(CAST(refunds.order_date_local AS DATE), QUARTER) AS DATE) AS metric_time__quarter
bigquery ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
databricks quarter: CAST(TRUNC(CAST(refunds.order_date_local AS DATE), 'QUARTER') AS DATE) AS metric_time__quarter
databricks ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
postgres quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
postgres ratio: CAST(order_refunds.order_refunds AS DOUBLE PRECISION) / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
tsql quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
tsql ratio: CAST(order_refunds.order_refunds AS FLOAT) / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
bigquery without the cast: SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER)
ILIKE in T-SQL: SELECT * FROM customers WHERE country ILIKE 'g%'

The compiler casts the time column to a date before it truncates, and that cast decides BigQuery's line. Without it, sqlglot writes SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER), and BigQuery documents TIMESTAMP_TRUNC's argument as a timestamp or a datetime, not a date (Google Cloud). With it, BigQuery gets DATE_TRUNC, which takes a date and a QUARTER granularity (Google Cloud), and Databricks gets TRUNC. The division gains a cast to a floating-point type for Postgres and T-SQL, so a ratio of integers stays fractional there; DuckDB's division operator is already float division (DuckDB).

The output shows two limits. The same cast changes T-SQL: its quarter line reads DATE_TRUNC, while Microsoft documents T-SQL's function as DATETRUNC, with no underscore (Microsoft). The fix for one dialect may have broken another, and sqlglot raised nothing, though the example sets unsupported_level=ErrorLevel.RAISE for every dialect. And the last line, ILIKE, passes through to T-SQL unchanged even with unsupported_level=ErrorLevel.RAISE, though T-SQL's pattern match is LIKE, with no ILIKE (Microsoft). We couldn't run BigQuery or SQL Server here. A query that transpiled is a query you still have to run on the target warehouse.

Working rule: Keep one definition and transpile it, but treat each dialect's SQL as untested until it runs on that warehouse against a known answer. Cast dates and divisions explicitly in the compiler, so the translation has less to guess.

What goes wrong in production

The shop's layer answers the shop's golden questions. A layer over a real warehouse fails in these ways, and each has a way to notice.

A wrong entity type turns off the fan-out protection. A foreign key declared primary makes the layer join many rows as if they were one, and every sum through that join inflates. Parsing won't catch it. Count duplicates of every primary entity in the data, as check() does, in CI and after every load.

A fact without its own date can't carry a metric. MetricFlow wants a time dimension in the same semantic model as each simple metric, and at 0.213.0 it refuses to build its engine without one, as the MetricFlow section shows with an InvalidManifestException. The shop's order lines had no date, which we found while writing this layer. That is why the warehouse adds semantic.order_lines with each order's local day. Put the business date on every fact table in the marts.

Two definitions of one metric drift apart. A metric defined in the layer and again in a dashboard, a notebook or an agent's prompt disagrees after the first change to one of them. Chapter 1's metric reads its definition from the YAML for that reason. Search for each metric's columns outside the layer, and run the golden questions after every change to the YAML.

Filters are templates, and templates are code. A metric's filter is a Jinja template, and MetricFlow's changelog records sandboxing their rendering to fix a security issue (dbt Labs). Never pass a filter string written by a model or a user to a layer. Send structured filters, as lib/semantic.py's MetricRequest does, and keep templates for the people who maintain the YAML. Chapter 9 attaches row filters at this point.

The engine's API moves. MetricFlow's Python engine is internal, at a 0.x version, and a release can rename an argument or change the SQL's shape. Keep every call in one module, pin the version exactly, and let the comparison with your own compiler, or with golden results, tell you when an upgrade changed a number.

Formats move too. dbt's spec for dbt v1.12 and later moves entities and dimensions under columns and replaces measures with simple metrics (dbt Labs), and Apache Ossie, the former Open Semantic Interchange, is another format that MetricFlow ships converters for (Apache Ossie; dbt Labs). Pick one format, keep it in version control, and convert with a tool you test, never by hand.

Working rule: Treat the semantic layer as code with tests: check entity uniqueness against the data, run every metric's SQL in CI, compare against MetricFlow and the golden questions on every change, and accept only structured requests from anything that isn't a person who maintains the layer.

Decisions this chapter made

• The shop's metrics live in one place, examples/data/semantic/*.yml, in MetricFlow's standalone YAML: five semantic models, ten metrics and one time spine. Chapter 1's net_revenue reads its definition from there.
• Simple metrics are measure-free: each names its semantic model, aggregation and expression, the direction dbt's spec takes, and MetricFlow 0.213.0 reads them.
• The book compiles with its own code, lib/semantic.py, which builds sqlglot expressions, joins only many rows to one, aggregates each input of a ratio or a multi-metric request on its own, and refuses a fan-out with a reason.
• Requests carry names and values, never SQL, and filter values become literals in the expression tree.
• The fiscal calendar is the time spine's custom grains, fiscal_year and fiscal_quarter, labelled so each value names one period.
• The warehouse gains a semantic schema of three views, so every fact has its own date and every fiscal quarter has a name. The raw and analytics schemas, and every recorded prompt, are unchanged.
• MetricFlow alone, without dbt, checks the definitions, through one module, and agrees with the book's compiler on every request the chapter compares. The metricflow package is Apache 2.0 and runs offline with no dbt dependency; dbt-metricflow is the usual production route.
• One definition is transpiled per warehouse, with the time column cast to a date before truncation, and the transpiled SQL is treated as untested until it runs there.
• Rates are ratios of sums, each side aggregated on its own with its own filter and time dimension: the refund rate is refunds on the period's orders over their gross revenue, which includes tax as refunds do, and the book's glossary and verified queries use the same definition.
• Definitions are checked against the data, not only parsed: every expression runs and every primary entity is unique, so a mis-declared key is caught before it inflates a sum.