Define metrics once in a semantic layer

Cerca de 32 minutos min de leitura, com 8 exemplos em 9 seções.

Edição 2026.11.15 Exemplos aprovados em 19 de nov de 2026 78 de 78 exemplos passam O que mudou

Traduzido com IA. Verificado por pessoas em inglês. Como fazemos os livros

Traduzido do livro em inglês, edição 2026.11.15. Leia o original

Esta parte ainda não foi traduzida, por isso está em 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.

Descreva cada tabela como um modelo semântico

Última alteração na edição 2026.11.12

Um modelo semântico é uma tabela descrita para a camada: qual tabela ele lê, as entidades que ela contém, as dimensões que oferece e a dimensão de tempo na qual seus números são contados. Uma entidade é uma chave com um tipo. Uma entidade primária é única em sua tabela, de modo que outras tabelas podem se juntar a ela com muitas linhas para uma. Uma entidade estrangeira é uma chave que aponta para a entidade primária de outra tabela, e se repete. Essa única palavra, primária ou estrangeira, é o que permite a uma camada juntar tabelas sem repetir linhas, por isso é a coisa mais importante que você declara. Toda a camada da loja está em examples/data/semantic/: um arquivo por modelo semântico, um metrics.yml e um project_configuration.yml, que nomeia a tabela de tempo (time spine), uma tabela com uma linha por dia.

Especificações mais antigas do MetricFlow têm um terceiro tipo de elemento, a medida (measure): uma agregação declarada no modelo semântico, à qual uma métrica então se refere. A documentação da dbt para sua especificação no dbt v1.12 e versões posteriores diz "As medidas estão obsoletas na nova especificação e são substituídas por métricas simples" (dbt Labs). O YAML da loja segue essa direção. Cada métrica simples nomeia ela mesma seu modelo semântico, sua agregação e sua expressão, no formato de documento autônomo que o próprio analisador do MetricFlow lê: um semantic_model: ou metric: por documento YAML, e tabelas nomeadas por node_relation. Aqui está o modelo de pedidos da loja, resumido, e a receita líquida, a métrica que o capítulo 1 definiu em 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'"

A dimensão de tempo do modelo de pedidos é order_date_local, o dia útil local do pedido, de modo que toda métrica nele conta os dias como a área financeira conta. A regra de quais pedidos contam, aquela que o SQL apenas com o esquema do capítulo 1 não captou, é o filter da métrica. metrics.yml também contém a descrição da receita líquida, palavra por palavra como o capítulo 1 a escreveu, e o lib/metrics.py do capítulo 1 lê sua tabela, agregação e condição a partir deste YAML, de modo que a definição existe em um único lugar.

A análise sintática não revela uma declaração errada, então o código carrega a camada e verifica suas definições contra o armazém de dados: cada expressão deve executar, e cada entidade primária deve ser única em sua tabela. Ele repete a verificação em uma cópia com um erro, na qual as linhas de pedido declaram order como sua entidade primária.

load-semantic-models.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Aprovado
"""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}")
Saídapython 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

A loja tem 5 modelos semânticos e 10 métricas, e a verificação encontra 0 problemas nelas. A cópia com erro falha na verificação: order_lines: primary entity 'order' repeats in 23,215 rows. Um pedido tem várias linhas, então order_id se repete na tabela de linhas, e uma camada que a considerasse única juntaria pedidos a linhas como se cada pedido tivesse uma única linha. Analisar o YAML não consegue encontrar isso, porque o arquivo é válido. Só os dados conseguem, por isso check() conta as duplicatas. Dois dos modelos semânticos, e a tabela de tempo, leem views em um terceiro schema do armazém de dados, semantic, que este capítulo acrescenta; a seção sobre calendário fiscal e a seção sobre produção explicam o motivo.

Regra prática: Declare um modelo semântico por tabela, marque uma entidade como primária somente onde ela for única, e verifique as declarações contra os dados sempre que o armazém de dados ou o YAML mudar. Escreva métricas simples sem medidas, indicando seu modelo e sua agregação, a menos que sua versão do MetricFlow ou seu projeto dbt ainda exija medidas.

Esta parte ainda não foi traduzida, por isso está em inglês.

Compile a metric request to SQL

Última alteração na edição 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 Aprovado
"""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}")
Saídapython 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 ainda não foi traduzida, por isso está em inglês.

Join through entities so sums can't fan out

Última alteração na edição 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 Aprovado
"""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}")
Saídapython 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 ainda não foi traduzida, por isso está em inglês.

Build ratio and derived metrics from simple ones

Última alteração na edição 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 Aprovado
"""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'}")
Saídapython 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 ainda não foi traduzida, por isso está em inglês.

Put the fiscal calendar in the time spine

Última alteração na edição 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 Aprovado
"""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'}")
Saídapython 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.

Confira as definições no MetricFlow

Última alteração na edição 2026.11.12

O YAML da loja só é portável se uma ferramenta de produção o ler da mesma forma que o compilador do livro. Em produção, o MetricFlow normalmente roda dentro de um projeto dbt, pelo pacote dbt-metricflow e sua linha de comando. A descrição desse pacote diz que ele executa comandos sobre a camada semântica do MetricFlow com o dbt, e ele exige dbt-core (PyPI). O livro verifica suas definições somente com o MetricFlow, pelo pacote metricflow, que é licenciado sob Apache 2.0 e não tem dependência do dbt; assim, a verificação roda offline em segundos. O MetricFlow analisa e valida os mesmos arquivos, e o método explain() do motor retorna o SQL que ele executaria. Seu motor em Python, MetricFlowEngine, é uma API interna em versão 0.x: a docstring da classe, em metricflow/engine/metricflow_engine.py do wheel 0.213.0 (PyPI), deixa a seguinte nota a seus mantenedores: "fornecer uma camada de API mais estável, em vez de presumir que esta classe é estável". O livro mantém tudo o que a toca em lib/metricflow_bridge.py, com um pequeno adaptador que permite ao MetricFlow executar SQL no DuckDB.

Os dois compiladores devem retornar as mesmas linhas, então o exemplo compara 8 consultas: métricas simples, de razão e derivadas, um join de dois saltos, as granularidades fiscais e duas dimensões de tempo em uma mesma tabela. Cada par roda no data warehouse da loja e suas linhas são comparadas. A execução também imprime o SQL do MetricFlow para a pergunta do capítulo 1 e a recusa dele diante do pedido da seção sobre 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 Aprovado
"""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 "* "
Saídapython 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 as 8 consultas retornam as mesmas linhas. A comparação admite uma única diferença, de tipo: o MetricFlow retorna um mês como datetime, um timestamp à meia-noite, enquanto o compilador do livro o converte para date; por isso, o exemplo transforma os timestamps em datas antes de comparar.

O SQL do MetricFlow é mais longo, com um comentário que nomeia cada etapa, e chega aos mesmos totais regionais do compilador do livro. Ele também recusa a receita líquida por categoria de produto em 2025, com um InvalidQueryException. Entre os problemas comuns que a mensagem lista está o que a seção sobre joins encontrou: "No valid join paths exist from the measure to the group-by-item. (fan-out join support is pending)."

O MetricFlow também exige mais do YAML do que o compilador do livro: todo modelo semântico em que uma métrica simples se apoia precisa de uma dimensão de tempo própria. O código a seguir constrói o motor duas vezes. A cópia aponta order_lines para analytics.fct_order_lines, a tabela analítica, que não tem dia do pedido, e remove a dimensão de tempo do 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 Aprovado
"""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]}")
Saídapython 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

O YAML da loja constrói MetricFlowEngine. A cópia não: com InvalidManifestException, o MetricFlow levanta um erro cuja mensagem começa com "Invalid aggregation time dimension configuration". Por isso o modelo order_lines da loja lê a view semantic.order_lines, que acrescenta o dia local de cada pedido.

Regra prática: Compare sua camada com o MetricFlow, ou com a ferramenta que a atende em produção, em cada formato de consulta de que você depende, e execute a comparação sempre que qualquer um dos dois lados mudar. Fixe a versão do MetricFlow com exatidão e mantenha o motor dele atrás de um único módulo, pois a API em Python pode mudar em qualquer versão.

Uma definição, o dialeto de qualquer data warehouse

Última alteração na edição 2026.11.12

Uma camada que compila para uma árvore de expressões, como o compilador do livro faz, pode escrever essa árvore para outro data warehouse. O sqlglot 30.31.0 se descreve como "um transpilador, não um validador", avisa que algumas consultas não podem ser traduzidas entre certos dialetos e, por padrão, faz uma tradução de melhor esforço, a menos que unsupported_level seja definido para lançar erro (PyPI). A taxa de reembolso de cada dialeto por trimestre do calendário difere em duas linhas, o trimestre e a divisão, então o exemplo imprime essas duas para cada dialeto. Ele também imprime o trimestre do BigQuery como o sqlglot o escreve a partir de uma coluna sem cast.

transpile-metric-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 Aprovado
"""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}")
Saídapython 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%'

O compilador faz cast da coluna de tempo para data antes de truncá-la, e esse cast define a linha do BigQuery. Sem ele, o sqlglot escreve SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER), e o BigQuery documenta que o argumento de TIMESTAMP_TRUNC é um timestamp ou um datetime, não uma data (Google Cloud). Com ele, o BigQuery recebe DATE_TRUNC, que aceita uma data e uma granularidade QUARTER (Google Cloud), e o Databricks recebe TRUNC. A divisão ganha um cast para um tipo de ponto flutuante no Postgres e no T-SQL, para que uma razão entre inteiros continue fracionária nesses dialetos; o operador de divisão do DuckDB já realiza divisão em ponto flutuante (DuckDB).

Dois limites aparecem nessas linhas. O mesmo cast altera o T-SQL: sua linha de trimestre lê DATE_TRUNC, enquanto a Microsoft documenta a função do T-SQL como DATETRUNC, sem sublinhado (Microsoft). A correção para um dialeto pode ter quebrado outro, e o sqlglot não lançou nenhum erro, embora o exemplo defina unsupported_level=ErrorLevel.RAISE para todos os dialetos. Por fim, a última linha, ILIKE, passa para o T-SQL sem alterações mesmo com unsupported_level=ErrorLevel.RAISE, embora o padrão de correspondência do T-SQL seja LIKE, sem ILIKE (Microsoft). Não conseguimos executar o BigQuery nem o SQL Server aqui. Uma consulta que foi transpilada é uma consulta que você ainda precisa executar no data warehouse de destino.

Regra prática: Mantenha uma única definição e transpile-a, mas trate o SQL de cada dialeto como não testado até que ele seja executado nesse data warehouse contra um resultado conhecido. Faça casts de datas e divisões explicitamente no compilador, para que a tradução tenha menos o que adivinhar.

Esta parte ainda não foi traduzida, por isso está em inglês.

What goes wrong in production

Última alteração na edição 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.

Decisões tomadas neste capítulo

Última alteração na edição 2026.11.13
  • As métricas da loja ficam em um só lugar, examples/data/semantic/*.yml, no YAML independente do MetricFlow: cinco modelos semânticos, dez métricas e uma time spine. A net_revenue do capítulo 1 lê sua definição a partir daí.
  • As métricas simples não usam measures: cada uma informa seu modelo semântico, sua agregação e sua expressão, na direção que a especificação do dbt adota, e o MetricFlow 0.214.0 as lê.
  • O livro compila com o próprio código, lib/semantic.py, que constrói expressões do sqlglot, faz joins apenas de muitas linhas para uma, agrega cada entrada de uma razão ou de uma requisição com múltiplas métricas separadamente, e recusa um fan-out informando o motivo.
  • As requisições carregam nomes e valores, nunca SQL, e os valores de filtro se tornam literais na árvore de expressões.
  • O calendário fiscal são os grãos personalizados da time spine, fiscal_year e fiscal_quarter, com rótulos para que cada valor identifique um único período.
  • O data warehouse ganha um schema semantic com três views, para que cada fato tenha sua própria data e cada trimestre fiscal tenha um nome. Os schemas raw e analytics, assim como cada prompt registrado, permanecem inalterados.
  • Somente o MetricFlow, sem o dbt, verifica as definições, por meio de um único módulo, e concorda com o compilador do livro em cada requisição que o capítulo compara. O pacote metricflow é Apache 2.0 e roda offline, sem dependência do dbt; o dbt-metricflow é a rota usual em produção.
  • Cada definição é transpilada uma vez por data warehouse, com a coluna de tempo convertida para date antes do truncamento, e o SQL transpilado é tratado como não testado até ser executado nesse warehouse.
  • Taxas são razões entre somas, com cada lado agregado separadamente, com seu próprio filtro e sua própria dimensão de tempo: a taxa de reembolso é o total de reembolsos dos pedidos do período dividido pela receita bruta desses pedidos, que inclui impostos, assim como os reembolsos incluem; o glossário e as consultas verificadas do livro usam a mesma definição.
  • As definições são verificadas contra os dados, e não apenas analisadas sintaticamente: cada expressão é executada e cada entidade primária é única, de modo que uma chave declarada incorretamente seja detectada antes de inflar uma soma.

Este capítulo, nesta edição

Edição 2026.11.15Exemplos aprovados em 19 de nov de 20268 de 8 exemplos exemplos deste capítulo passam

Traduzido do livro em inglês, edição 2026.11.15.

Citar esta seção

Formato

Cada formato cita o livro em inglês, que esta tradução acompanha.

Todos os formatos citam esta edição. Seu link abre a edição atual, em que o título de cada seção indica quando seu texto foi alterado pela última vez. As datas de acesso e de recuperação são o dia em que você abre esta página.

Nesta página

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

  1. Descreva cada tabela como um 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. Confira as definições no MetricFlow
  7. Uma definição, o dialeto de qualquer data warehouse
  8. What goes wrong in production
  9. Decisões tomadas neste capítulo

Voltar ao topoTodos os capítulos