Define metrics once in a semantic layer

阅读约需 82分钟 分钟,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时,这些定义才是可移植的。在生产环境中,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 中,该类的文档字符串给维护者留下了一条备注,要"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

如果一个层把查询编译成表达式树,就像本书的编译器那样,它就能把这棵树写成另一个数据仓库所用的 SQL。sqlglot 30.31.0 自称是“转译器,而不是校验器”,并提醒说,有些查询无法在某些方言之间转译;除非将 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

商店的语义层能回答商店的黄金问题。在真实的数据仓库之上,风险存在于声明、日期和引擎之中,每一种风险都有需要留意的症状。

错误的实体类型会关闭扇出保护。 外键若被声明为主键,语义层就会把多行当作一行来连接,经过该连接的每个求和都会被放大。解析阶段发现不了这个问题。请像 check() 那样,统计数据中每个主实体的重复项,并在 CI 中和每次加载之后执行。

没有自身日期的事实表无法承载指标。 MetricFlow 要求每个简单指标所在的语义模型中都有一个时间维度。在 0.214.0 版本下,缺少时间维度时它会拒绝构建引擎,MetricFlow 一节用 InvalidManifestException 展示了这一点。商店的订单行没有日期,这是我们在编写这个语义层时发现的。因此数据仓库增设了 semantic.order_lines,其中记录每个订单所在的本地日。请在数据集市的每张事实表上都放置业务日期。

同一指标的两份定义会逐渐出现分歧。 若一个指标在语义层中定义过,又在仪表盘、笔记本或智能体的提示词中再定义一次,其中任何一处发生第一次修改后,两者就会不一致。第 1 章的指标正是出于这个原因,从 YAML 中读取其定义。请在语义层之外搜索每个指标所用的列,并在每次修改 YAML 之后运行黄金问题。

过滤器是模板,而模板就是代码。 指标的 filter 是一个 Jinja 模板,MetricFlow 的变更日志记录了为修复一个安全问题而对其渲染过程做了沙箱化(dbt Labs)。切勿把由模型或用户编写的过滤器字符串传给语义层。请像 lib/semantic.py 的 MetricRequest 那样发送结构化过滤器,把模板留给维护 YAML 的人。第 9 章在这一环节附加行级过滤器。

引擎的 API 会变动。 MetricFlow 的 Python 引擎是内部接口,版本为 0.x,某次发布可能重命名参数,或改变 SQL 的形态。请把所有调用集中在一个模块中,精确固定版本,并通过与你自己的编译器或黄金结果的对比,得知升级是否改变了某个数值。

格式也会变动。 dbt 针对 dbt v1.12 及之后版本的规范,把实体和维度移到列之下,并用简单指标取代度量(dbt Labs)。Apache Ossie,即原来的 Open Semantic Interchange,是另一种格式(Apache Ossie),MetricFlow 附带了针对它的转换器,其变更日志对此有记录。请选定一种格式,将其置于版本控制之下,并使用经过测试的工具进行转换,切勿手工转换。

工作准则: 像测试代码一样测试语义层:对照数据检查实体的唯一性,在 CI 中运行每个指标的 SQL,每次变更时都与 MetricFlow 和黄金问题进行比对,并且对任何不是维护语义层的人的来源,只接受结构化请求。

本章所做的决定

最近一次修改见于版次 2026.11.13
  • **店铺的指标集中在一处,**即 examples/data/semantic/*.yml,采用 MetricFlow 的独立 YAML 格式:五个语义模型、十个指标和一个时间脊柱。第 1 章的 net_revenue 就从这里读取其定义。
  • **简单指标不使用度量:**每个指标都指明所属的语义模型、聚合方式和表达式。这是 dbt 规范所采取的方向,MetricFlow 0.214.0 也按此读取。
  • **本书用自己的代码编译,**即 lib/semantic.py。它构建 sqlglot 表达式,只做多对一的连接,对比率的每个输入或多指标请求的每个输入分别聚合,并在遇到扇出时拒绝执行并给出原因。
  • **请求只携带名称和值,绝不携带 SQL,**过滤值在表达式树中成为字面量。
  • **财年日历就是时间脊柱的自定义粒度,**即 fiscal_year 和 fiscal_quarter,其标签使每个值都对应唯一的一个时期。
  • **数据仓库新增一个 semantic 模式,**包含三个视图,使每条事实都有自己的日期,每个财季都有名称。raw 和 analytics 模式以及所有已记录的提示均保持不变。
  • **仅用 MetricFlow、不借助 dbt,**通过一个模块检查这些定义,并且在本章比较的每个请求上都与本书的编译器结果一致。metricflow 包采用 Apache 2.0 许可,可离线运行,不依赖 dbt;dbt-metricflow 则是常见的生产环境路径。
  • **每个数据仓库对应转译一份定义,**在截断之前先把时间列转换为日期类型,转译后的 SQL 在对应仓库中实际运行之前,一律视为未经测试。
  • **比率是求和之比,**分子与分母各自使用自己的过滤条件和时间维度单独聚合:退款率是当期订单的退款额除以这些订单的总收入,其中总收入与退款额一样包含税款,本书的术语表和已验证查询使用同一定义。
  • **定义要对照数据检查,**而不只是解析:每个表达式都要运行,每个主实体都要唯一,因此错误声明的键会在它使求和虚增之前被发现。

本章,在本版次中

版次 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. 本章所做的决定

返回顶部全部章节