Define metrics once in a semantic layer

Unos 27 minutos de lectura, con 8 ejemplos en 9 secciones.

Edición 2026.11.15 Ejemplos verificados el 19 de nov de 2026 78 de 78 ejemplos pasan Qué ha cambiado

Traducido con IA. Verificado por personas en inglés. Cómo hacemos los libros

Traducido del libro en inglés, edición 2026.11.15. Lee el original

Esta parte aún no está traducida, así que está en inglés.

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 about the camping and outdoor gear it sells. 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 the choices behind joins, grains and ratios 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.214.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 cada tabla como un modelo semántico

Último cambio en la edición 2026.11.12

Un modelo semántico es una tabla descrita para la capa: qué tabla lee, las entidades que contiene, las dimensiones que ofrece y la dimensión temporal sobre la que se cuentan sus cifras. Una entidad es una clave con un tipo. Una entidad primaria es única en su tabla, de modo que otras tablas pueden unirse a ella con relación de muchos a uno. Una entidad foránea es una clave que apunta a la entidad primaria de otra tabla, y se repite. Esa sola palabra, primaria o foránea, es lo que permite a una capa unir tablas sin repetir filas, así que es lo más importante que declaras. Toda la capa de la tienda vive en examples/data/semantic/: un archivo por modelo semántico, un metrics.yml y un project_configuration.yml, que nombra la escala temporal, una tabla con una fila por día.

Las especificaciones más antiguas de MetricFlow tienen un tercer tipo de elemento, la medida: una agregación declarada en el modelo semántico, a la que luego una métrica hace referencia. La documentación de dbt para su especificación en dbt v1.12 y posteriores dice «Measures are deprecated in the new spec and are replaced with simple metrics» (dbt Labs). El YAML de la tienda sigue esa dirección. Cada métrica simple nombra por sí misma su modelo semántico, su agregación y su expresión, en la forma de documento independiente que lee el propio analizador de MetricFlow: un semantic_model: o un metric: por documento YAML, y las tablas se nombran mediante node_relation. Aquí está el modelo de pedidos de la tienda, abreviado, y los ingresos netos, la métrica que el capítulo 1 definió en 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'"

La dimensión temporal del modelo de pedidos es order_date_local, el día hábil local del pedido, así que cada métrica sobre ella cuenta los días como lo hace el área de finanzas. La regla de ingresos netos sobre qué pedidos cuentan, la que el SQL solo de esquema del capítulo 1 pasó por alto, es el filter de la métrica. metrics.yml también contiene la descripción de los ingresos netos, palabra por palabra como la escribió el capítulo 1, y el lib/metrics.py del capítulo 1 lee su tabla, agregación y condición de este YAML, de modo que la definición existe en un solo lugar.

El análisis no revela una declaración incorrecta, así que el código carga la capa y comprueba sus definiciones contra el almacén de datos: cada expresión debe ejecutarse, y cada entidad primaria debe ser única en su tabla. Repite la comprobación sobre una copia con un error, en la que las líneas de pedido declaran order como su entidad primaria.

load-semantic-models.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Load the shop's semantic layer from YAML, list what each semantic model declares, and check it against the warehouse."""from dataclasses import replacefrom lib.semantic import Entity, SemanticLayer, loadfrom lib.warehouse import connectlayer = load()                   # reads examples/data/semantic/*.yml and validates the definitionsprint(f"semantic models: {len(layer.semantic_models)}")print(f"metrics: {len(layer.metrics)}")for model in layer.semantic_models.values():    entities = ", ".join(f"{e.name} ({e.type})" for e in model.entities.values())    print(f"{model.name}: {model.table}; entities: {entities}; dimensions: {', '.join(model.dimensions) or 'none'}")metric = layer.metrics["net_revenue"]print(f"net_revenue: {metric.agg} of {metric.expr} in {metric.semantic_model}, where {metric.filter}")con = connect()print(f"problems in the shop's definitions: {len(layer.check(con))}")# A layer with one mistake: the lines declare `order` as their primary entity, as if each order had one line.lines = layer.semantic_models["order_lines"]wrong = replace(lines, entities={"order": Entity("order", "primary", "order_id"),                                 "product": lines.entities["product"]})for problem in SemanticLayer({"order_lines": wrong}, {}, layer.time_spine).check(con):    print(f"problem: {problem}")
Salidapython load-semantic-models.py
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

La tienda tiene 5 modelos semánticos y 10 métricas, y la comprobación encuentra 0 problemas en ellos. La copia errónea no la supera: order_lines: primary entity 'order' repeats in 23,215 rows. Un pedido tiene varias líneas, así que order_id se repite en la tabla de líneas, y una capa que lo considerara único uniría pedidos y líneas como si cada pedido tuviera una sola línea. Analizar el YAML no puede detectarlo, porque el archivo es válido. Solo los datos pueden hacerlo, por eso check() cuenta los duplicados. Dos de los modelos semánticos, y la escala temporal, leen vistas de un tercer esquema del almacén de datos, semantic, que este capítulo añade; la sección de calendario fiscal y la sección de producción explican por qué.

Regla práctica: Declara un modelo semántico por tabla, marca una entidad como primaria solo cuando sea única, y comprueba las declaraciones contra los datos cada vez que cambien el almacén o el YAML. Escribe métricas simples sin medidas, nombrando su modelo y su agregación, a menos que tu versión de MetricFlow o tu proyecto de dbt todavía requiera medidas.

Esta parte aún no está traducida, así que está en inglés.

Compile a metric request to SQL

Último cambio en la edición 2026.11.15

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:

Name What it reaches
order__region region in the model whose primary entity is order
order__customer__region region of the order's customer, two joins away
metric_time__month the metric's own time dimension, by month
metric_time__fiscal_quarter a fiscal quarter from the time spine, a custom grain (fiscal section)

Chapter 1's question is the first test: golden question gq-002 asks, "What was net revenue in each region in March 2026?" As a request, it is net_revenue grouped by order__region and filtered to March 2026. The code prints the request and its SQL, runs it, and checks the answer against the golden result. Then it sends a filter value holding SQL of its own, and asks for a dimension the layer doesn't define.

compile-net-revenue.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Compile one metric request to SQL with the book's semantic layer, run it, and check it against the golden answer. The printed `matches golden: yes` line is the fact the prose states."""from lib.golden import golden_result, load_golden, results_matchfrom lib.semantic import MetricRequest, SemanticError, loadfrom lib.warehouse import connectlayer, con = load(), connect()question = next(q for q in load_golden() if q.question_id == "gq-002")print(f"question: {question.question}")request = MetricRequest(metrics=("net_revenue",), group_by=("order__region",),                        filters={"metric_time__month": "2026-03"})  # the compiler turns this text month into a date literal, compared with a date castprint(f"request: {request}")print(layer.sql(request))answer = layer.query(con, request)print(answer.show())matches = results_match(answer.rows, golden_result(con, question))assert matches, "gq-002 must match its golden result"print(f"matches golden: {'yes' if matches else 'no'}")# A filter value becomes a literal in the expression tree, so text in it can't change the query.hostile = MetricRequest(("net_revenue",), filters={"order__region": "EMEA' OR '1'='1"})condition = layer.compile(hostile).args["where"].sql(dialect="duckdb")print(f"hostile filter compiles to: {condition}")print(f"rows for the hostile value: {layer.query(con, hostile).rows}")# A name the layer doesn't define is refused before any SQL exists, with what the metric can use.try:    layer.compile(MetricRequest(("net_revenue",), ("order__colour",)))except SemanticError as e:    print(f"refused: {e}")
Salidapython compile-net-revenue.py
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: yes
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 for gq-002. 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. A 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.

Esta parte aún no está traducida, así que está en inglés.

Join through entities so sums can't fan out

Último cambio en la edición 2026.11.15

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 layer refuses net_revenue by product__category. A hand-written join runs beside that refusal, since it returns a number without error, and the line-level metric is asked for instead.

entity-join-no-fanout.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Ask for net revenue by product category: the layer refuses the fan-out join and points to a line-level metric. The printed `inflated by` and `difference from net revenue` lines are the facts the prose states."""from lib.semantic import FanOutError, MetricRequest, loadfrom lib.warehouse import connectlayer, con = load(), connect()in_2025 = {"metric_time__year": 2025}try:    layer.sql(MetricRequest(("net_revenue",), ("product__category",), in_2025))except FanOutError as e:    print(f"refused: {e}")# What a hand-written join does with the same question: each order's revenue repeats once per line.(joined,), = con.execute("""    SELECT sum(o.net_revenue_eur) FROM analytics.fct_orders AS o    JOIN analytics.fct_order_lines AS l USING (order_id)    WHERE o.status <> 'cancelled' AND year(o.order_date_local) = 2025""").fetchall()(total,), = layer.query(con, MetricRequest(("net_revenue",), filters=in_2025)).rowsprint(f"net revenue, 2025: {total:,.2f}")print(f"summed over the joined lines: {joined:,.2f}")print(f"inflated by: {joined / total - 1:.0%}")request = MetricRequest(("line_net_revenue",), ("product__category",), in_2025, order_by=("-line_net_revenue",))  # a leading minus sorts descending, so the largest category prints firstprint(layer.sql(request))answer = layer.query(con, request)print(answer.show())by_category = sum(row[1] for row in answer.rows)print(f"categories add up to: {by_category:,.2f}")print(f"difference from net revenue: {abs(by_category - total):,.2f}")
Salidapython entity-join-no-fanout.py
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 net revenue's 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.

Esta parte aún no está traducida, así que está en inglés.

Build ratio and derived metrics from simple ones

Último cambio en la edición 2026.11.15

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 metrics 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 tempting version is why this example matters: averaging each order's refund rate per region looks right and isn't. Beside it sits the layer's rate for fiscal 2026, which the example compiles.

refund-rate-metric.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""A ratio metric divides two separately aggregated sums, and averaging per-order ratios gives a different number. The printed `matches golden: yes` lines are the facts the prose states."""from lib.golden import golden_result, load_golden, results_matchfrom lib.semantic import MetricRequest, loadfrom lib.warehouse import connectlayer, con = load(), connect()fiscal_2026 = {"metric_time__fiscal_year": 2026}  # the fiscal year runs February to January, named for the year it ends inrequest = MetricRequest(("refund_rate",), ("order__region",), fiscal_2026)print(layer.sql(request))for region, rate in layer.query(con, request).rows:    print(f"refund rate, {region}: {rate:.2%}")# The tempting shortcut: each order's refunds over its gross revenue, averaged per region.for region, rate in con.execute("""    SELECT o.region, avg(coalesce(r.refunded, 0) / o.gross_revenue_eur)    FROM analytics.fct_orders AS o    JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local    LEFT JOIN (SELECT order_id, sum(amount_eur) AS refunded FROM analytics.fct_refunds GROUP BY order_id) AS r        USING (order_id)    WHERE o.status <> 'cancelled' AND d.fiscal_year = 2026    GROUP BY o.region ORDER BY o.region""").fetchall():    print(f"average of per-order rates, {region}: {rate:.2%}")golden = {q.question_id: q for q in load_golden()}top = layer.query(con, MetricRequest(("refund_rate",), ("order__country",), fiscal_2026, ("-refund_rate",), 1))  # a leading minus sorts descending; the limit keeps the top rowprint(f"highest refund rate: {top.rows[0][0]}")matches = results_match([top.rows[0][:1]], golden_result(con, golden['gq-028']))assert matches, "gq-028 must match its golden result"print(f"gq-028 matches golden: {'yes' if matches else 'no'}")average = layer.query(con, MetricRequest(("average_order_value",), ("order__region",), {"metric_time__month": "2026-03"}))print(average.show())matches = results_match(average.rows, golden_result(con, golden['gq-009']))assert matches, "gq-009 must match its golden result"print(f"gq-009 matches golden: {'yes' if matches else 'no'}")
Salidapython refund-rate-metric.py
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: yes
order__region  average_order_value
AMER           300.94
APAC           291.71
EMEA           272.83
gq-009 matches golden: yes

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 belongs to NL, the answer golden question gq-028 expects. 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. Grouped by region for March 2026, it gives the averages golden question gq-009 expects.

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.

Esta parte aún no está traducida, así que está en inglés.

Put the fiscal calendar in the time spine

Último cambio en la edición 2026.11.15

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.

Grouping net revenue by fiscal and calendar quarter shows how far apart the two calendars sit when a year starts in February. Two golden questions about first fiscal quarters close the check.

fiscal-quarter-grain.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Group net revenue by fiscal quarter, a custom grain from the time spine, and by calendar quarter, a standard one. The printed `matches golden: yes` lines are the facts the prose states."""from datetime import datefrom lib.golden import golden_result, load_golden, results_matchfrom lib.semantic import MetricRequest, loadfrom lib.warehouse import connectlayer, con = load(), connect()fiscal = MetricRequest(("net_revenue",), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026})print(layer.sql(fiscal))fiscal_answer = layer.query(con, fiscal)print(fiscal_answer.show())calendar = MetricRequest(("net_revenue",), ("metric_time__quarter",), {"metric_time__year": 2025})  # standard grain: truncates the date to the calendar quartercalendar_answer = layer.query(con, calendar)print(calendar_answer.show())print(f"fiscal 2026-Q1, February to April 2025: {dict(fiscal_answer.rows)["2026-Q1"]:,.2f}")print(f"calendar 2025 Q1, January to March 2025: {dict(calendar_answer.rows)[date(2025, 1, 1)]:,.2f}")golden = {q.question: q for q in load_golden()}for text, request in [    ("What was net revenue in the first quarter of fiscal year 2026?",     MetricRequest(("net_revenue",), filters={"metric_time__fiscal_quarter": "2026-Q1"})),    ("What was net revenue in each region in the first quarter of fiscal year 2027?",     MetricRequest(("net_revenue",), ("order__region",), {"metric_time__fiscal_quarter": "2027-Q1"})),]:    question = golden[text]    answer = layer.query(con, request)    matches = results_match(answer.rows, golden_result(con, question))    assert matches, f"{question.question_id} must match its golden result"    print(f"{question.question_id} matches golden: {'yes' if matches else 'no'}")
Salidapython fiscal-quarter-grain.py
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: yes
gq-026 matches golden: yes

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 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, and by region in the first quarter of fiscal 2027, gq-026.

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 comparing fiscal Q1 with 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.

Verifica las definiciones con MetricFlow

Último cambio en la edición 2026.11.12

El YAML de la tienda es portable solo si una herramienta de producción lo lee del mismo modo que el compilador del libro. En producción, MetricFlow suele ejecutarse dentro de un proyecto dbt, mediante el paquete dbt-metricflow y su línea de comandos. El resumen de ese paquete lo describe como la ejecución de comandos contra la capa semántica de MetricFlow con dbt, y requiere dbt-core (PyPI). El libro verifica sus definiciones únicamente con MetricFlow, el paquete metricflow, que tiene licencia Apache 2.0 y no depende de dbt, de modo que la comprobación se ejecuta sin conexión en segundos. MetricFlow analiza y valida los mismos archivos, y el explain() de su motor devuelve el SQL que ejecutaría. Su motor de Python, MetricFlowEngine, es una API interna en una versión 0.x: el docstring de la clase, en metricflow/engine/metricflow_engine.py de la rueda 0.213.0 (PyPI), deja a sus mantenedores una nota para "proporcionar una capa de API más estable en lugar de asumir que esta clase es estable". El libro mantiene todo lo que la toca en lib/metricflow_bridge.py, con un pequeño adaptador que permite a MetricFlow ejecutar SQL sobre DuckDB.

Los dos compiladores deberían devolver las mismas filas, así que el ejemplo comprueba 8 solicitudes: métricas simples, de razón y derivadas, un join de dos saltos, los granos fiscales y dos dimensiones temporales sobre una misma tabla. Cada par se ejecuta en el almacén de la tienda y se comparan sus filas. La ejecución también imprime el SQL de MetricFlow para la pregunta del capítulo 1, y su rechazo de la solicitud de la sección de joins.

metricflow-agrees.py Python 3.12.15 · duckdb 2.0.0 · metricflow 0.214.0 · pydantic 2.14.3 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Compile the same requests with MetricFlow and with the book's compiler, run both, and compare the results. The printed `requests that agree` line is the fact the prose states."""from lib.golden import results_matchfrom lib.metricflow_bridge import as_dates, engine, explain_sqlfrom lib.semantic import MetricRequest as Request, loadfrom lib.warehouse import connectcon = connect()layer, mf = load(), engine(con)   # both read examples/data/semantic/*.ymlrequests = [    Request(("net_revenue",), ("order__region",), {"metric_time__month": "2026-03"}),    Request(("net_revenue", "orders"), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026}),    Request(("line_net_revenue", "units_sold"), ("product__category",), {"metric_time__year": 2025}),    Request(("units_sold",), ("order__customer__region",), {"metric_time__year": 2025}),    Request(("refund_rate",), ("order__country",), {"metric_time__fiscal_year": 2026}),    Request(("average_order_value",), ("order__channel",), {"metric_time__month": "2026-03"}),    Request(("refunds", "order_refunds"), ("metric_time__month",), {"metric_time__year": 2025}),    Request(("new_customers",), ("customer__region",), {"metric_time__year": 2025}),]agree = 0for request in requests:    ours = con.execute(layer.sql(request)).fetchall()    theirs = as_dates(con.execute(explain_sql(mf, request)).fetchall())    same = results_match(ours, theirs, tolerance=1e-9)  # a tolerance for float rounding differences between the two engines    agree += same    print(f"{', '.join(request.metrics)} by {', '.join(request.group_by)}: {len(ours)} rows, agree: {same}")month = requests[6]  # the one request with a month grain, so its column types show MetricFlow's timestamp and the book's dateprint(f"MetricFlow's type for metric_time__month: {type(con.execute(explain_sql(mf, month)).fetchone()[0]).__name__}")print(f"the book's type for metric_time__month: {type(con.execute(layer.sql(month)).fetchone()[0]).__name__}")print(f"requests compared: {len(requests)}")print(f"requests that agree: {agree}")print(explain_sql(mf, requests[0]))  # chapter 1's question: net revenue by region in March 2026try:    explain_sql(mf, Request(("net_revenue",), ("product__category",), {"metric_time__year": 2025}))except Exception as e:    print(f"MetricFlow on net_revenue by product__category: {type(e).__name__}")    lines = [line.strip() for line in str(e).splitlines()]    cause = lines.index("* No valid join paths exist from the measure to the group-by-item.")    # MetricFlow puts each cause on one line and its explanation on the next, so the line after the cause is printed too.    print(f"one cause MetricFlow lists: {lines[cause][2:]} {lines[cause + 1]}")  # [2:] drops the bullet's "* "
Salidapython metricflow-agrees.py
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).

Todas las 8 solicitudes devuelven las mismas filas. La comparación admite una diferencia, de tipo: MetricFlow devuelve un mes como un datetime, una marca de tiempo a medianoche, mientras que el compilador del libro lo convierte en un date, por lo que el ejemplo convierte las marcas de tiempo en fechas antes de comparar.

El SQL de MetricFlow es más largo, con un comentario que nombra cada paso, y llega a los mismos totales regionales que el compilador del libro. También rechaza los ingresos netos por categoría de producto en 2025, con un InvalidQueryException. Entre los problemas comunes que enumera su mensaje está el que encontró la sección de uniones: "No valid join paths exist from the measure to the group-by-item. (fan-out join support is pending)."

MetricFlow también exige más al YAML que el compilador del libro: cada modelo semántico en el que una métrica simple se apoya necesita una dimensión temporal propia. El siguiente código construye el motor dos veces. La copia apunta order_lines a analytics.fct_order_lines, la tabla analítica, que no tiene día de pedido, y elimina la dimensión temporal del modelo.

metricflow-needs-a-date.py Python 3.12.15 · duckdb 2.0.0 · metricflow 0.214.0 · pydantic 2.14.3 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Show that MetricFlow won't build its engine when a fact's semantic model has no date of its own."""import shutilimport tempfilefrom pathlib import Pathimport yamlfrom lib.metricflow_bridge import enginefrom lib.semantic import SEMANTIC_DIRfrom lib.warehouse import connectcon = connect()print(f"MetricFlow with the shop's YAML: {type(engine(con)).__name__}")# A copy in which order_lines reads analytics.fct_order_lines, which has no order day, and declares no date.with tempfile.TemporaryDirectory() as tmp:    folder = Path(tmp) / "semantic"    shutil.copytree(SEMANTIC_DIR, folder)    path = folder / "order_lines.yml"    document = yaml.safe_load(path.read_text())    lines_model = document["semantic_model"]    lines_model["node_relation"] = {"schema_name": "analytics", "alias": "fct_order_lines"}    del lines_model["defaults"], lines_model["dimensions"]    path.write_text(yaml.safe_dump(document, sort_keys=False))    try:        engine(con, folder)        print("MetricFlow without a date on order_lines: built")    except Exception as e:        print(f"MetricFlow without a date on order_lines: {type(e).__name__}")        print(f"its message begins: {str(e).splitlines()[0]}")
Salidapython metricflow-needs-a-date.py
MetricFlow with the shop's YAML: MetricFlowEngine
MetricFlow without a date on order_lines: InvalidManifestException
its message begins: Invalid aggregation time dimension configuration

El YAML de la tienda construye un MetricFlowEngine. La copia no: MetricFlow lanza un InvalidManifestException cuyo mensaje comienza "Invalid aggregation time dimension configuration". Por eso el modelo order_lines de la tienda lee la vista semantic.order_lines, que añade el día local de cada pedido.

Regla práctica: Compara tu capa con MetricFlow, o con la herramienta que la sirva en producción, en cada forma de solicitud en la que confíes, y ejecuta la comparación cada vez que cambie cualquiera de los dos lados. Fija la versión de MetricFlow exactamente y mantén su motor detrás de un único módulo, porque su API de Python puede cambiar en cualquier versión.

Una definición, el dialecto de cualquier almacén

Último cambio en la edición 2026.11.12

Una capa que compila a un árbol de expresiones, como el compilador del libro, puede escribir ese árbol para otro almacén de datos. sqlglot 30.31.0 se describe a sí mismo como "un transpilador, no un validador", advierte de que algunas consultas no se pueden traducir entre algunos dialectos y, por defecto, hace una traducción de mejor esfuerzo a menos que unsupported_level esté configurado para lanzar un error (PyPI). La tasa de devoluciones de cada dialecto por trimestre del calendario difiere en dos líneas, el trimestre y la división, así que el ejemplo imprime esas dos para cada dialecto. También imprime el trimestre de BigQuery tal como lo escribe sqlglot a partir de una columna sin conversión.

transpile-metric-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Superado
"""Write one compiled metric request in six warehouses' dialects, and show what transpiling doesn't check. The printed quarter and ratio lines per dialect, and the `bigquery without the cast` line, are the facts the prose states."""import sqlglotfrom sqlglot import ErrorLevelfrom lib.semantic import MetricRequest, loadrequest = MetricRequest(("refund_rate",), ("metric_time__quarter",), {"metric_time__year": 2025})compiled = load().compile(request)for dialect in ("duckdb", "snowflake", "bigquery", "databricks", "postgres", "tsql"):    sql = compiled.sql(dialect=dialect, pretty=True, unsupported_level=ErrorLevel.RAISE)    lines = [line.strip().rstrip(",") for line in sql.splitlines()]    # the quarter line is the one that truncates the date; the ratio line is the one that divides, guarded by NULLIF    print(f"{dialect} quarter: {next(line for line in lines if 'TRUNC' in line)}")    print(f"{dialect} ratio: {next(line for line in lines if 'NULLIF' in line)}")# The compiler casts the column to DATE before truncating. Without the cast, BigQuery gets TIMESTAMP_TRUNC.uncast = sqlglot.transpile("SELECT DATE_TRUNC('quarter', order_date_local)", read="duckdb", write="bigquery")[0]print(f"bigquery without the cast: {uncast}")# Transpiling translates syntax; it doesn't check that the target warehouse has the function.tsql = sqlglot.transpile("SELECT * FROM customers WHERE country ILIKE 'g%'", read="duckdb", write="tsql",                         unsupported_level=ErrorLevel.RAISE)[0]print(f"ILIKE in T-SQL: {tsql}")
Salidapython transpile-metric-sql.py
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%'

El compilador convierte la columna de tiempo a fecha antes de truncarla, y esa conversión determina la línea de BigQuery. Sin ella, sqlglot escribe SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER), y BigQuery documenta que el argumento de TIMESTAMP_TRUNC es una marca de tiempo o un datetime, no una fecha (Google Cloud). Con ella, BigQuery obtiene DATE_TRUNC, que recibe una fecha y una granularidad QUARTER (Google Cloud), y Databricks obtiene TRUNC. La división incorpora una conversión a un tipo de punto flotante para Postgres y T-SQL, de modo que un cociente de enteros se mantiene fraccionario en esos motores; el operador de división de DuckDB ya realiza división en punto flotante (DuckDB).

Dos límites se aprecian en esas líneas. La misma conversión afecta a T-SQL: su línea de trimestre dice DATE_TRUNC, mientras que Microsoft documenta la función de T-SQL como DATETRUNC, sin guion bajo (Microsoft). La corrección para un dialecto puede haber roto otro, y sqlglot no lanzó ningún error, aunque el ejemplo establece unsupported_level=ErrorLevel.RAISE para todos los dialectos. Y la última línea, ILIKE, pasa a T-SQL sin cambios incluso con unsupported_level=ErrorLevel.RAISE, aunque la coincidencia de patrones de T-SQL es LIKE, sin ILIKE (Microsoft). No pudimos ejecutar BigQuery ni SQL Server aquí. Una consulta que se transpiló es una consulta que aún tienes que ejecutar en el almacén de destino.

Regla práctica: Mantén una sola definición y transpílala, pero considera el SQL de cada dialecto como no probado hasta que se ejecute en ese almacén contra un resultado conocido. Convierte explícitamente las fechas y las divisiones en el compilador, de modo que la traducción tenga menos que adivinar.

Esta parte aún no está traducida, así que está en inglés.

What goes wrong in production

Último cambio en la edición 2026.11.13

The shop's layer answers the shop's golden questions. Over a real warehouse, the risks sit in the declarations, the dates and the engine, and each has a symptom to watch.

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.214.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). Apache Ossie, the former Open Semantic Interchange, is another format (Apache Ossie), and MetricFlow ships converters for it, as its changelog records. Pick one format, keep it in version control, and convert with a tool you test, never by hand.

Working rule: Test the semantic layer like code: 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.

Decisiones que tomó este capítulo

Último cambio en la edición 2026.11.13
  • Las métricas de la tienda viven en un solo lugar, examples/data/semantic/*.yml, en el YAML independiente de MetricFlow: cinco modelos semánticos, diez métricas y una escala temporal. El net_revenue del capítulo 1 lee su definición desde ahí.
  • Las métricas simples no tienen medidas: cada una indica su modelo semántico, su agregación y su expresión, con la dirección que sigue la especificación de dbt, y MetricFlow 0.214.0 las lee.
  • El libro compila con su propio código, lib/semantic.py, que construye expresiones de sqlglot, une únicamente varias filas con una sola (de muchos a uno), agrega por separado cada entrada de un ratio o de una solicitud de varias métricas, y rechaza una expansión de filas indicando el motivo.
  • Las solicitudes llevan nombres y valores, nunca SQL, y los valores de los filtros se convierten en literales dentro del árbol de expresiones.
  • El calendario fiscal son los granos personalizados de la escala temporal, fiscal_year y fiscal_quarter, etiquetados para que cada valor nombre un único periodo.
  • El almacén gana un esquema semantic con tres vistas, de modo que cada hecho tiene su propia fecha y cada trimestre fiscal tiene un nombre. Los esquemas raw y analytics, y cada prompt registrado, no cambian.
  • MetricFlow, por sí solo y sin dbt, verifica las definiciones, mediante un único módulo, y coincide con el compilador del libro en cada solicitud que compara el capítulo. El paquete metricflow tiene licencia Apache 2.0 y funciona sin conexión y sin dependencia de dbt; dbt-metricflow es la vía habitual en producción.
  • Cada definición se transpila una vez por almacén, con la columna temporal convertida a fecha antes del truncamiento, y el SQL transpilado se considera no probado hasta que se ejecute en ese almacén.
  • Las tasas son razones de sumas, cada lado agregado por separado con su propio filtro y su propia dimensión temporal: la tasa de devoluciones es el cociente entre las devoluciones de los pedidos del periodo sobre sus ingresos brutos, que incluyen el impuesto como lo hacen las devoluciones, y el glosario y las consultas verificadas del libro usan la misma definición.
  • Las definiciones se comprueban contra los datos, no solo se analizan sintácticamente: cada expresión se ejecuta y cada entidad primaria es única, de modo que una clave mal declarada se detecta antes de que infle una suma.

Este capítulo, en esta edición

Edición 2026.11.15Ejemplos verificados el 19 de nov de 2026Superan la prueba 8 de 8 ejemplos ejemplos de este capítulo

Traducido del libro en inglés, edición 2026.11.15.

Cita esta sección

Formato

Cada formato cita el libro en inglés, que esta traducción sigue.

Cada formato nombra esta edición. Su enlace abre la edición actual, donde el encabezado de cada sección indica cuándo cambió su texto por última vez. Las fechas de acceso y de recuperación son el día en que lo abres.

En esta página

Capítulo 5 de 11: Define metrics once in a semantic layer

  1. Describe cada tabla como un modelo semántico
  2. Compile a metric request to SQL
  3. Join through entities so sums can't fan out
  4. Build ratio and derived metrics from simple ones
  5. Put the fiscal calendar in the time spine
  6. Verifica las definiciones con MetricFlow
  7. Una definición, el dialecto de cualquier almacén
  8. What goes wrong in production
  9. Decisiones que tomó este capítulo

Volver arribaTodos los capítulos