Define metrics once in a semantic layer

閱讀約需 83 分鐘 分鐘,9 節 中共有 8 個範例。

版次 2026.11.15 範例於 2026年11 月 19 通過測試 78 個範例中有 78 個通過 變更內容

由 AI 翻譯。經人工以英文驗證。 我們如何製作書籍

譯自英文書,版次 2026.11.15。 閱讀原文

此部分尚未翻譯,因此以英文呈現。

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.

將每個資料表描述為一個語意模型

最近一次變動於第 2026.11.12 版

語意模型是為語意層描述的單一資料表:它讀取哪個資料表、包含哪些實體、提供哪些維度,以及其數值以哪個時間維度計算。實體是帶有類型的鍵。主要實體(primary entity)在其資料表中是唯一的,因此其他資料表可以用多對一的方式與它連接。外部實體(foreign entity)是指向另一個資料表主要實體的鍵,它會重複出現。就是這「主要」或「外部」一個詞,讓語意層能在不重複資料列的情況下連接資料表,因此它是你所宣告的內容中最重要的一項。商店的整個語意層都放在 examples/data/semantic/:每個語意模型一個檔案、一個 metrics.yml,以及一個 project_configuration.yml,後者指定時間骨幹(time spine),也就是每天一列的資料表。

較舊的 MetricFlow 規格還有第三種元素,即度量(measure):宣告在語意模型上的聚合,再由指標引用其名稱。dbt 針對 dbt v1.12 及之後版本的規格文件寫道:「Measures are deprecated in the new spec and are replaced with simple metrics」(dbt Labs)。商店的 YAML 依循這個方向。每個簡單指標自行指定其語意模型、聚合與運算式,採用 MetricFlow 自身的剖析器所讀取的獨立文件形式:每份 YAML 文件有一個 semantic_model: 或 metric:,資料表則由 node_relation 指定。以下是商店的訂單模型(已縮短),以及淨營收,也就是第 1 章以 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'"

訂單模型的時間維度是 order_date_local,即訂單的當地營業日,因此建立在它之上的每個指標,都以財務部門的方式計算日期。淨營收中決定哪些訂單要計入的規則,也就是第 1 章僅依結構描述撰寫的 SQL 所遺漏的部分,就是該指標的 filter。metrics.yml 也包含淨營收的描述,與第 1 章所寫的一字不差,而第 1 章的 lib/metrics.py 就從這份 YAML 讀取其資料表、聚合與條件,因此這個定義只存在於一處。

剖析無法揭露錯誤的宣告,所以程式碼會載入語意層,並對照資料倉儲檢查其定義:每個運算式都必須能執行,每個主要實體在其資料表中都必須是唯一的。它也會對一份含有一處錯誤的副本重複這項檢查,在該副本中,訂單明細將 order 宣告為其主要實體。

load-semantic-models.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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}")
輸出python 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

商店有 5 個語意模型與 10 個指標,檢查在其中找到 0 個問題。含錯誤的副本則未通過檢查:order_lines: primary entity 'order' repeats in 23,215 rows。一筆訂單有多筆明細,因此 order_id 在明細資料表中會重複,而相信它是唯一的語意層,會把訂單與明細連接起來,彷彿每筆訂單只有一筆明細。剖析 YAML 無法發現這一點,因為該檔案是有效的。只有資料能發現,這就是 check() 要計算重複數的原因。有兩個語意模型與時間骨幹,讀取的是資料倉儲第三個結構描述 semantic 中的檢視表,本章會新增這個結構描述;原因見會計年度日曆一節與正式環境一節。

實用原則: 每個資料表宣告一個語意模型,只在實體唯一時才將其標為主要實體,並且在資料倉儲或 YAML 變更時,對照資料檢查這些宣告。撰寫簡單指標時不使用度量,直接指定其模型與聚合,除非你的 MetricFlow 版本或 dbt 專案仍要求使用度量。

此部分尚未翻譯,因此以英文呈現。

Compile a metric request to SQL

最近一次變動於第 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 通過
"""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}")
輸出python 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.

此部分尚未翻譯,因此以英文呈現。

Join through entities so sums can't fan out

最近一次變動於第 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 通過
"""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}")
輸出python 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.

此部分尚未翻譯,因此以英文呈現。

Build ratio and derived metrics from simple ones

最近一次變動於第 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 通過
"""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'}")
輸出python 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.

此部分尚未翻譯,因此以英文呈現。

Put the fiscal calendar in the time spine

最近一次變動於第 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 通過
"""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'}")
輸出python 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.

以 MetricFlow 檢驗定義

最近一次變動於第 2026.11.12 版

只有在正式環境的工具讀取 YAML 的方式與本書的編譯器相同時,商店的 YAML才具備可攜性。在正式環境中,MetricFlow 通常在 dbt 專案內執行,透過 dbt-metricflow 套件及其命令列使用。該套件的摘要說明它是用 dbt 對 MetricFlow 的語意層執行指令,並且需要 dbt-core(PyPI)。本書只用 MetricFlow 本身,也就是 metricflow 套件來檢驗定義。它採用 Apache 2.0 授權,沒有 dbt 相依性,因此檢驗可以離線在數秒內完成。MetricFlow 會解析並驗證相同的檔案,其引擎的 explain() 會回傳它將執行的 SQL。它的 Python 引擎 MetricFlowEngine 是版本為 0.x 的內部 API:在 0.213.0 wheel(PyPI)的 metricflow/engine/metricflow_engine.py 中,該類別的 docstring 留給維護者一則註記,寫著「provide a more stable API layer instead of assuming this class is stable」。本書將所有與它相關的程式都放在 lib/metricflow_bridge.py,並搭配一個小型轉接器,讓 MetricFlow 能在 DuckDB 上執行 SQL。

兩個編譯器應該回傳相同的資料列,因此本範例檢查 8 個請求:簡單指標、比率指標與衍生指標、兩段式聯結、會計年度粒度,以及同一個資料表上的兩個時間維度。每一對請求都在商店的資料倉儲上執行,並比較其資料列。這次執行也會印出 MetricFlow 針對第 1 章的問題所產生的 SQL,以及它對聯結一節的請求所給出的拒絕訊息。

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 通過
"""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 "* "
輸出python 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).

所有 8 個請求都回傳相同的資料列。比較時容許一項差異,也就是型別:MetricFlow 將月份回傳為 datetime,即午夜時間的時間戳記,而本書的編譯器則將它轉型為 date,因此範例在比較之前會先把時間戳記轉成日期。

MetricFlow 的 SQL 較長,每個步驟都有註解說明,並得到與本書編譯器相同的各區域總計。它同樣拒絕 2025 年依產品類別計算的淨營收,並回報 InvalidQueryException。在其訊息列出的常見問題中,有一項正是聯結一節所遇到的問題:「No valid join paths exist from the measure to the group-by-item. (fan-out join support is pending).」

MetricFlow 對 YAML 的要求也比本書的編譯器更多:每個被簡單指標依賴的語意模型,都需要有自己的時間維度。下列程式碼建立了兩次引擎。副本將 order_lines 指向 analytics.fct_order_lines(這個分析資料表沒有訂單日期),並移除該模型的時間維度。

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 通過
"""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]}")
輸出python 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

商店的 YAML 可以建出 MetricFlowEngine,副本則不行:MetricFlow 會拋出 InvalidManifestException,其訊息以「Invalid aggregation time dimension configuration」開頭。這就是商店的 order_lines 模型讀取檢視 semantic.order_lines 的原因,該檢視會加上每筆訂單的當地日期。

實用原則: 對於你所依賴的每一種請求形式,都要拿你的語意層與 MetricFlow(或在正式環境中提供該服務的任何工具)比較,並且在任何一方有變動時重新執行比較。請精確固定 MetricFlow 的版本,並將其引擎限制在單一模組之內,因為它的 Python API 可能在任何一個版本中改變。

一份定義,適用任何資料倉儲的方言

最近一次變動於第 2026.11.12 版

如果某一層會編譯成運算式樹,就像本書的編譯器那樣,它就能把這棵樹寫成另一種資料倉儲所用的語法。sqlglot 30.31.0 自稱是「轉譯器(transpiler),而非驗證器(validator)」,並警告某些查詢無法在某些方言之間轉譯;除非將 unsupported_level 設為拋出例外,否則預設會盡力轉譯(PyPI)。各方言中「依日曆季度計算的退款率」只有兩行不同,也就是季度與除法,所以這個範例會為每種方言印出這兩行。範例也會印出 sqlglot 從未轉型的欄位寫出的 BigQuery 季度。

transpile-metric-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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}")
輸出python 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%'

編譯器在截斷之前,會先把時間欄位轉型為日期,而這個轉型決定了 BigQuery 那一行的寫法。沒有轉型時,sqlglot 寫出 SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER),但 BigQuery 的文件指出 TIMESTAMP_TRUNC 的引數是 timestamp 或 datetime,而不是 date(Google Cloud)。有轉型時,BigQuery 得到 DATE_TRUNC,它接受日期與 QUARTER 粒度(Google Cloud),而 Databricks 得到 TRUNC。對 Postgres 與 T-SQL,除法會多一個轉成浮點型別的轉型,因此整數相除的比值在那裡仍保有小數;DuckDB 的除法運算子本來就是浮點除法(DuckDB)。

這些行顯示出兩項限制。同樣的轉型改變了 T-SQL:它的季度那一行是 DATE_TRUNC,但 Microsoft 文件記載 T-SQL 的函式是 DATETRUNC,中間沒有底線(Microsoft)。為某一種方言所做的修正,可能弄壞了另一種方言,而 sqlglot 沒有拋出任何錯誤,儘管範例已對每種方言設定 unsupported_level=ErrorLevel.RAISE。最後一行的 ILIKE,即使設定了 unsupported_level=ErrorLevel.RAISE,仍原封不動地傳給 T-SQL,但 T-SQL 的模式比對是 LIKE,沒有 ILIKE(Microsoft)。我們無法在這裡執行 BigQuery 或 SQL Server。轉譯成功的查詢,仍然必須在目標資料倉儲上執行過才算數。

實用原則: 只維護一份定義並加以轉譯,但在每種方言的 SQL 於該資料倉儲上對照已知答案執行成功之前,都視為未經測試。請在編譯器中明確轉型日期與除法,讓轉譯時需要猜測的地方更少。

正式環境中會出什麼問題

最近一次變動於第 2026.11.13 版

商店的語意層能回答商店的黃金問題。放到真實的資料倉儲上,風險出在宣告、日期和引擎這三處,每一處都有可供留意的徵兆。

**錯誤的實體類型會關閉扇出(fan-out)保護。**外鍵若被宣告為主鍵,語意層就會把多列當成一列來進行 join,經過該 join 的每個加總都會膨脹。解析階段不會發現這個問題。請像 check() 那樣,計算資料中每個主要實體的重複筆數,並在 CI 中以及每次載入之後進行。

**沒有自己日期的事實表無法承載指標。**MetricFlow 要求每個簡單指標所在的語意模型中都要有一個時間維度,在 0.214.0 版本下,沒有時間維度就拒絕建立引擎,如 MetricFlow 一節以 InvalidManifestException 所示。商店的訂單明細沒有日期,這是我們在撰寫這個語意層時發現的。因此資料倉儲新增了 semantic.order_lines,記錄每筆訂單的當地日期。請在資料集市(marts)的每張事實表上都放入業務日期。

**同一個指標的兩份定義會逐漸分歧。**若指標在語意層中定義一次,又在儀表板、筆記本或代理程式的提示詞中再定義一次,只要其中一處第一次變更,兩者就會不一致。第 1 章的指標正是因為這個緣故,才從 YAML 讀取它的定義。請在語意層之外搜尋每個指標用到的欄位,並在每次變更 YAML 之後執行黃金問題。

**篩選條件是範本,而範本就是程式碼。**指標的 filter 是一個 Jinja 範本,MetricFlow 的變更紀錄記載了為修正一個安全性問題,而對範本的渲染加上沙箱(dbt Labs)。切勿把由模型或使用者撰寫的篩選字串傳給語意層。請傳送結構化的篩選條件,就像 lib/semantic.py 的 MetricRequest 所做的那樣,並把範本留給維護 YAML 的人。第 9 章會在這個位置加上列層級篩選。

**引擎的 API 會變動。**MetricFlow 的 Python 引擎屬於內部介面,版本為 0.x,新版本可能更改引數名稱,或改變 SQL 的形式。請把所有呼叫集中在同一個模組,精確固定版本,並讓與你自己的編譯器或黃金結果的比對,告訴你升級是否改變了某個數字。

**格式也會變動。**dbt 針對 dbt v1.12 及之後版本的規格,把實體與維度移到欄位之下,並以簡單指標取代度量(measures)(dbt Labs)。Apache Ossie,即先前的 Open Semantic Interchange,是另一種格式(Apache Ossie),MetricFlow 也提供針對它的轉換器,如其變更紀錄所載。請選定一種格式,納入版本控制,並使用經過測試的工具轉換,切勿手動轉換。

**實用原則:**像測試程式碼一樣測試語意層:對照資料檢查實體的唯一性,在 CI 中執行每個指標的 SQL,每次變更都與 MetricFlow 及黃金問題比對,並且對於任何不是維護語意層的人所送來的請求,只接受結構化的請求。

本章所做的決定

最近一次變動於第 2026.11.13 版
  • **商店的指標集中在同一處,**也就是 examples/data/semantic/*.yml,使用 MetricFlow 的獨立 YAML 格式:五個語意模型、十個指標和一個時間骨幹(time spine)。第 1 章的 net_revenue 便是從這裡讀取它的定義。
  • **簡單指標不使用度量(measure):**每個指標都指明其語意模型、聚合方式和運算式,這是 dbt 規格採取的方向,MetricFlow 0.214.0 也能讀取這種寫法。
  • **本書以自己的程式碼進行編譯,**即 lib/semantic.py。它會建構 sqlglot 運算式,只在多對一的方向上進行連接,對比率的每個輸入項或多指標請求中的每個指標各自聚合,並在遇到扇出(fan-out)時拒絕執行並說明原因。
  • **請求只帶名稱與值,絕不帶 SQL,**而篩選值會成為運算式樹中的字面值。
  • **會計日曆是時間骨幹的自訂粒度,**即 fiscal_year 與 fiscal_quarter,其標籤的設計使每個值都只對應一個期間。
  • **資料倉儲新增一個 semantic 結構描述(schema),**內含三個檢視表,使每筆事實都有自己的日期,每個會計季度也都有名稱。raw 與 analytics 這兩個結構描述,以及每一則已記錄的提示詞,都維持不變。
  • **單靠 MetricFlow,不需 dbt,即可檢查這些定義,**只透過一個模組進行,而且在本章比較的每一項請求上,都與本書的編譯器結果一致。metricflow 套件採用 Apache 2.0 授權,可離線執行,也不依賴 dbt;dbt-metricflow 則是常見的正式環境做法。
  • **每個資料倉儲各自轉譯同一份定義,**轉譯時會在截斷之前先把時間欄位轉型為日期,而轉譯後的 SQL 在該倉儲上實際執行之前,一律視為尚未測試。
  • **比率是總和之比,**分子與分母各自以自己的篩選條件和時間維度聚合:退款率是該期間訂單的退款額除以這些訂單的總營收,其中總營收和退款額一樣都含稅,本書的詞彙表與已驗證查詢也採用相同的定義。
  • **定義要以資料來檢查,**而不只是剖析:每個運算式都會執行,每個主要實體(primary entity)都必須是唯一的,因此宣告錯誤的鍵會在膨脹總和之前就被發現。

本章,於本版次中

版次 2026.11.15範例於 2026年11 月 19 通過測試本章 8 個範例 個範例中有 8 個通過

譯自英文書,版次 2026.11.15。

引用本節

格式

各種格式皆引用英文原書,本譯本即依循該原書。

每種格式都會標示本版。其連結會開啟目前版次,節標題會說明該節內文最後一次變動的時間。存取日期與擷取日期即為你開啟此頁的當日。

本頁內容

第 5 章,共 11 章: Define metrics once in a semantic layer

  1. 將每個資料表描述為一個語意模型
  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. 以 MetricFlow 檢驗定義
  7. 一份定義,適用任何資料倉儲的方言
  8. 正式環境中會出什麼問題
  9. 本章所做的決定

返回頂端所有章節