Define metrics once in a semantic layer
읽는 데 약 56분분 걸린다, 9절에 8개 예제이(가) 있음.
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.
각 테이블을 시맨틱 모델로 기술하기
시맨틱 모델은 계층을 위해 기술한 하나의 테이블이다. 어떤 테이블을 읽는지, 그 테이블이 담은 엔터티, 제공하는 디멘션, 수치를 집계하는 기준이 되는 시간 디멘션을 적는다. 엔터티는 유형을 가진 키이다. 기본 엔터티는 자기 테이블에서 유일하므로, 다른 테이블이 여러 행 대 한 행으로 조인할 수 있다. 외래 엔터티는 다른 테이블의 기본 엔터티를 가리키는 키이며, 값이 반복된다. 기본인지 외래인지를 밝히는 이 한 단어가 계층이 행을 중복시키지 않고 테이블을 조인하게 해 주므로, 선언할 내용 중 가장 중요하다. 이 가게의 계층 전체는 examples/data/semantic/에 있다. 시맨틱 모델마다 파일 하나, metrics.yml 하나, 그리고 하루에 한 행씩 있는 테이블인 타임 스파인을 지정하는 project_configuration.yml 하나로 구성된다.
이전 MetricFlow 명세에는 세 번째 종류의 요소인 메저(measure)가 있다. 메저는 시맨틱 모델에 선언하는 집계이며, 메트릭이 이를 이름으로 참조한다. dbt v1.12 이상의 명세에 대한 dbt 문서는 "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를 자신의 기본 엔터티로 선언한다.
1"""Load the shop's semantic layer from YAML, list what each semantic model declares, and check it against the warehouse."""2from dataclasses import replace34from lib.semantic import Entity, SemanticLayer, load5from lib.warehouse import connect67layer = load() # reads examples/data/semantic/*.yml and validates the definitions8print(f"semantic models: {len(layer.semantic_models)}")9print(f"metrics: {len(layer.metrics)}")10for model in layer.semantic_models.values():11 entities = ", ".join(f"{e.name} ({e.type})" for e in model.entities.values())12 print(f"{model.name}: {model.table}; entities: {entities}; dimensions: {', '.join(model.dimensions) or 'none'}")1314metric = layer.metrics["net_revenue"]15print(f"net_revenue: {metric.agg} of {metric.expr} in {metric.semantic_model}, where {metric.filter}")1617con = connect()18print(f"problems in the shop's definitions: {len(layer.check(con))}")1920# A layer with one mistake: the lines declare `order` as their primary entity, as if each order had one line.21lines = layer.semantic_models["order_lines"]22wrong = replace(lines, entities={"order": Entity("order", "primary", "order_id"),23 "product": lines.entities["product"]})24for problem in SemanticLayer({"order_lines": wrong}, {}, layer.time_spine).check(con):25 print(f"problem: {problem}")
python load-semantic-models.pysemantic 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
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.
1"""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."""2from lib.golden import golden_result, load_golden, results_match3from lib.semantic import MetricRequest, SemanticError, load4from lib.warehouse import connect56layer, con = load(), connect()7question = next(q for q in load_golden() if q.question_id == "gq-002")8print(f"question: {question.question}")910request = MetricRequest(metrics=("net_revenue",), group_by=("order__region",),11 filters={"metric_time__month": "2026-03"}) # the compiler turns this text month into a date literal, compared with a date cast12print(f"request: {request}")13print(layer.sql(request))1415answer = layer.query(con, request)16print(answer.show())17matches = results_match(answer.rows, golden_result(con, question))18assert matches, "gq-002 must match its golden result"19print(f"matches golden: {'yes' if matches else 'no'}")2021# A filter value becomes a literal in the expression tree, so text in it can't change the query.22hostile = MetricRequest(("net_revenue",), filters={"order__region": "EMEA' OR '1'='1"})23condition = layer.compile(hostile).args["where"].sql(dialect="duckdb")24print(f"hostile filter compiles to: {condition}")25print(f"rows for the hostile value: {layer.query(con, hostile).rows}")2627# A name the layer doesn't define is refused before any SQL exists, with what the metric can use.28try:29 layer.compile(MetricRequest(("net_revenue",), ("order__colour",)))30except SemanticError as e:31 print(f"refused: {e}")
python compile-net-revenue.pyquestion: 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
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.
1"""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."""2from lib.semantic import FanOutError, MetricRequest, load3from lib.warehouse import connect45layer, con = load(), connect()6in_2025 = {"metric_time__year": 2025}78try:9 layer.sql(MetricRequest(("net_revenue",), ("product__category",), in_2025))10except FanOutError as e:11 print(f"refused: {e}")1213# What a hand-written join does with the same question: each order's revenue repeats once per line.14(joined,), = con.execute("""15 SELECT sum(o.net_revenue_eur) FROM analytics.fct_orders AS o16 JOIN analytics.fct_order_lines AS l USING (order_id)17 WHERE o.status <> 'cancelled' AND year(o.order_date_local) = 2025""").fetchall()18(total,), = layer.query(con, MetricRequest(("net_revenue",), filters=in_2025)).rows19print(f"net revenue, 2025: {total:,.2f}")20print(f"summed over the joined lines: {joined:,.2f}")21print(f"inflated by: {joined / total - 1:.0%}")2223request = MetricRequest(("line_net_revenue",), ("product__category",), in_2025, order_by=("-line_net_revenue",)) # a leading minus sorts descending, so the largest category prints first24print(layer.sql(request))25answer = layer.query(con, request)26print(answer.show())27by_category = sum(row[1] for row in answer.rows)28print(f"categories add up to: {by_category:,.2f}")29print(f"difference from net revenue: {abs(by_category - total):,.2f}")
python entity-join-no-fanout.pyrefused: '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
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.
1"""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."""2from lib.golden import golden_result, load_golden, results_match3from lib.semantic import MetricRequest, load4from lib.warehouse import connect56layer, con = load(), connect()7fiscal_2026 = {"metric_time__fiscal_year": 2026} # the fiscal year runs February to January, named for the year it ends in89request = MetricRequest(("refund_rate",), ("order__region",), fiscal_2026)10print(layer.sql(request))11for region, rate in layer.query(con, request).rows:12 print(f"refund rate, {region}: {rate:.2%}")1314# The tempting shortcut: each order's refunds over its gross revenue, averaged per region.15for region, rate in con.execute("""16 SELECT o.region, avg(coalesce(r.refunded, 0) / o.gross_revenue_eur)17 FROM analytics.fct_orders AS o18 JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local19 LEFT JOIN (SELECT order_id, sum(amount_eur) AS refunded FROM analytics.fct_refunds GROUP BY order_id) AS r20 USING (order_id)21 WHERE o.status <> 'cancelled' AND d.fiscal_year = 202622 GROUP BY o.region ORDER BY o.region""").fetchall():23 print(f"average of per-order rates, {region}: {rate:.2%}")2425golden = {q.question_id: q for q in load_golden()}26top = layer.query(con, MetricRequest(("refund_rate",), ("order__country",), fiscal_2026, ("-refund_rate",), 1)) # a leading minus sorts descending; the limit keeps the top row27print(f"highest refund rate: {top.rows[0][0]}")28matches = results_match([top.rows[0][:1]], golden_result(con, golden['gq-028']))29assert matches, "gq-028 must match its golden result"30print(f"gq-028 matches golden: {'yes' if matches else 'no'}")3132average = layer.query(con, MetricRequest(("average_order_value",), ("order__region",), {"metric_time__month": "2026-03"}))33print(average.show())34matches = results_match(average.rows, golden_result(con, golden['gq-009']))35assert matches, "gq-009 must match its golden result"36print(f"gq-009 matches golden: {'yes' if matches else 'no'}")
python refund-rate-metric.pyWITH 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
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.
1"""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."""2from datetime import date34from lib.golden import golden_result, load_golden, results_match5from lib.semantic import MetricRequest, load6from lib.warehouse import connect78layer, con = load(), connect()910fiscal = MetricRequest(("net_revenue",), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026})11print(layer.sql(fiscal))12fiscal_answer = layer.query(con, fiscal)13print(fiscal_answer.show())1415calendar = MetricRequest(("net_revenue",), ("metric_time__quarter",), {"metric_time__year": 2025}) # standard grain: truncates the date to the calendar quarter16calendar_answer = layer.query(con, calendar)17print(calendar_answer.show())18print(f"fiscal 2026-Q1, February to April 2025: {dict(fiscal_answer.rows)["2026-Q1"]:,.2f}")19print(f"calendar 2025 Q1, January to March 2025: {dict(calendar_answer.rows)[date(2025, 1, 1)]:,.2f}")2021golden = {q.question: q for q in load_golden()}22for text, request in [23 ("What was net revenue in the first quarter of fiscal year 2026?",24 MetricRequest(("net_revenue",), filters={"metric_time__fiscal_quarter": "2026-Q1"})),25 ("What was net revenue in each region in the first quarter of fiscal year 2027?",26 MetricRequest(("net_revenue",), ("order__region",), {"metric_time__fiscal_quarter": "2027-Q1"})),27]:28 question = golden[text]29 answer = layer.query(con, request)30 matches = results_match(answer.rows, golden_result(con, question))31 assert matches, f"{question.question_id} must match its golden result"32 print(f"{question.question_id} matches golden: {'yes' if matches else 'no'}")
python fiscal-quarter-grain.pySELECT
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와 대조하기
상점의 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 휠(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개의 요청을 검사한다. 단순 지표, 비율 지표, 파생 지표, 두 단계 조인, 회계 기간 단위, 한 테이블의 두 시간 차원이 포함된다. 각 쌍은 상점의 웨어하우스에서 실행되고 그 행이 비교된다. 이 실행은 1장의 질문에 대한 MetricFlow의 SQL과, 조인 절의 요청에 대한 MetricFlow의 거부도 출력한다.
1"""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."""2from lib.golden import results_match3from lib.metricflow_bridge import as_dates, engine, explain_sql4from lib.semantic import MetricRequest as Request, load5from lib.warehouse import connect67con = connect()8layer, mf = load(), engine(con) # both read examples/data/semantic/*.yml9requests = [10 Request(("net_revenue",), ("order__region",), {"metric_time__month": "2026-03"}),11 Request(("net_revenue", "orders"), ("metric_time__fiscal_quarter",), {"metric_time__fiscal_year": 2026}),12 Request(("line_net_revenue", "units_sold"), ("product__category",), {"metric_time__year": 2025}),13 Request(("units_sold",), ("order__customer__region",), {"metric_time__year": 2025}),14 Request(("refund_rate",), ("order__country",), {"metric_time__fiscal_year": 2026}),15 Request(("average_order_value",), ("order__channel",), {"metric_time__month": "2026-03"}),16 Request(("refunds", "order_refunds"), ("metric_time__month",), {"metric_time__year": 2025}),17 Request(("new_customers",), ("customer__region",), {"metric_time__year": 2025}),18]192021agree = 022for request in requests:23 ours = con.execute(layer.sql(request)).fetchall()24 theirs = as_dates(con.execute(explain_sql(mf, request)).fetchall())25 same = results_match(ours, theirs, tolerance=1e-9) # a tolerance for float rounding differences between the two engines26 agree += same27 print(f"{', '.join(request.metrics)} by {', '.join(request.group_by)}: {len(ours)} rows, agree: {same}")28month = requests[6] # the one request with a month grain, so its column types show MetricFlow's timestamp and the book's date29print(f"MetricFlow's type for metric_time__month: {type(con.execute(explain_sql(mf, month)).fetchone()[0]).__name__}")30print(f"the book's type for metric_time__month: {type(con.execute(layer.sql(month)).fetchone()[0]).__name__}")31print(f"requests compared: {len(requests)}")32print(f"requests that agree: {agree}")3334print(explain_sql(mf, requests[0])) # chapter 1's question: net revenue by region in March 202635try:36 explain_sql(mf, Request(("net_revenue",), ("product__category",), {"metric_time__year": 2025}))37except Exception as e:38 print(f"MetricFlow on net_revenue by product__category: {type(e).__name__}")39 lines = [line.strip() for line in str(e).splitlines()]40 cause = lines.index("* No valid join paths exist from the measure to the group-by-item.")41 # MetricFlow puts each cause on one line and its explanation on the next, so the line after the cause is printed too.42 print(f"one cause MetricFlow lists: {lines[cause][2:]} {lines[cause + 1]}") # [2:] drops the bullet's "* "
python metricflow-agrees.pynet_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을 가리키게 하고, 모델의 시간 차원을 제거한다.
1"""Show that MetricFlow won't build its engine when a fact's semantic model has no date of its own."""2import shutil3import tempfile4from pathlib import Path56import yaml78from lib.metricflow_bridge import engine9from lib.semantic import SEMANTIC_DIR10from lib.warehouse import connect1112con = connect()13print(f"MetricFlow with the shop's YAML: {type(engine(con)).__name__}")1415# A copy in which order_lines reads analytics.fct_order_lines, which has no order day, and declares no date.16with tempfile.TemporaryDirectory() as tmp:17 folder = Path(tmp) / "semantic"18 shutil.copytree(SEMANTIC_DIR, folder)19 path = folder / "order_lines.yml"20 document = yaml.safe_load(path.read_text())21 lines_model = document["semantic_model"]22 lines_model["node_relation"] = {"schema_name": "analytics", "alias": "fct_order_lines"}23 del lines_model["defaults"], lines_model["dimensions"]24 path.write_text(yaml.safe_dump(document, sort_keys=False))25 try:26 engine(con, folder)27 print("MetricFlow without a date on order_lines: built")28 except Exception as e:29 print(f"MetricFlow without a date on order_lines: {type(e).__name__}")30 print(f"its message begins: {str(e).splitlines()[0]}")
python metricflow-needs-a-date.pyMetricFlow 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는 어느 릴리스에서든 바뀔 수 있기 때문이다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
One definition, any warehouse's dialect
A layer that compiles to an expression tree, as the book's compiler does, can write that tree for another warehouse. sqlglot 30.31.0 calls itself "a transpiler, not a validator", warns that some queries can't be translated between some dialects, and by default makes a best-effort translation unless unsupported_level is set to raise (PyPI). Each dialect's refund rate by calendar quarter differs in two lines, the quarter and the division, so the example prints those two for each dialect. It also prints BigQuery's quarter as sqlglot writes it from an uncast column.
1"""Write one compiled metric request in six warehouses' dialects, and show what transpiling doesn't check. The printed quarter and ratio lines per dialect, and the `bigquery without the cast` line, are the facts the prose states."""2import sqlglot3from sqlglot import ErrorLevel45from lib.semantic import MetricRequest, load67request = MetricRequest(("refund_rate",), ("metric_time__quarter",), {"metric_time__year": 2025})8compiled = load().compile(request)910for dialect in ("duckdb", "snowflake", "bigquery", "databricks", "postgres", "tsql"):11 sql = compiled.sql(dialect=dialect, pretty=True, unsupported_level=ErrorLevel.RAISE)12 lines = [line.strip().rstrip(",") for line in sql.splitlines()]13 # the quarter line is the one that truncates the date; the ratio line is the one that divides, guarded by NULLIF14 print(f"{dialect} quarter: {next(line for line in lines if 'TRUNC' in line)}")15 print(f"{dialect} ratio: {next(line for line in lines if 'NULLIF' in line)}")1617# The compiler casts the column to DATE before truncating. Without the cast, BigQuery gets TIMESTAMP_TRUNC.18uncast = sqlglot.transpile("SELECT DATE_TRUNC('quarter', order_date_local)", read="duckdb", write="bigquery")[0]19print(f"bigquery without the cast: {uncast}")2021# Transpiling translates syntax; it doesn't check that the target warehouse has the function.22tsql = sqlglot.transpile("SELECT * FROM customers WHERE country ILIKE 'g%'", read="duckdb", write="tsql",23 unsupported_level=ErrorLevel.RAISE)[0]24print(f"ILIKE in T-SQL: {tsql}")
python transpile-metric-sql.pyduckdb quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
duckdb ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
snowflake quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
snowflake ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
bigquery quarter: CAST(DATE_TRUNC(CAST(refunds.order_date_local AS DATE), QUARTER) AS DATE) AS metric_time__quarter
bigquery ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
databricks quarter: CAST(TRUNC(CAST(refunds.order_date_local AS DATE), 'QUARTER') AS DATE) AS metric_time__quarter
databricks ratio: order_refunds.order_refunds / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
postgres quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
postgres ratio: CAST(order_refunds.order_refunds AS DOUBLE PRECISION) / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
tsql quarter: CAST(DATE_TRUNC('QUARTER', CAST(refunds.order_date_local AS DATE)) AS DATE) AS metric_time__quarter
tsql ratio: CAST(order_refunds.order_refunds AS FLOAT) / NULLIF(gross_revenue.gross_revenue, 0) AS refund_rate
bigquery without the cast: SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER)
ILIKE in T-SQL: SELECT * FROM customers WHERE country ILIKE 'g%'
The compiler casts the time column to a date before it truncates, and that cast decides BigQuery's line. Without it, sqlglot writes SELECT TIMESTAMP_TRUNC(order_date_local, QUARTER), and BigQuery documents TIMESTAMP_TRUNC's argument as a timestamp or a datetime, not a date (Google Cloud). With it, BigQuery gets DATE_TRUNC, which takes a date and a QUARTER granularity (Google Cloud), and Databricks gets TRUNC. The division gains a cast to a floating-point type for Postgres and T-SQL, so a ratio of integers stays fractional there; DuckDB's division operator is already float division (DuckDB).
Two limits show in those lines. The same cast changes T-SQL: its quarter line reads DATE_TRUNC, while Microsoft documents T-SQL's function as DATETRUNC, with no underscore (Microsoft). The fix for one dialect may have broken another, and sqlglot raised nothing, though the example sets unsupported_level=ErrorLevel.RAISE for every dialect. And the last line, ILIKE, passes through to T-SQL unchanged even with unsupported_level=ErrorLevel.RAISE, though T-SQL's pattern match is LIKE, with no ILIKE (Microsoft). We couldn't run BigQuery or SQL Server here. A query that transpiled is a query you still have to run on the target warehouse.
Working rule: Keep one definition and transpile it, but treat each dialect's SQL as untested until it runs on that warehouse against a known answer. Cast dates and divisions explicitly in the compiler, so the translation has less to guess.
운영 환경에서 무엇이 잘못되는가
이 가게의 계층은 가게의 핵심 질문(golden question)에는 답한다. 그러나 실제 웨어하우스에서는 위험이 선언, 날짜, 엔진에 있으며, 각각에는 주의해서 살펴야 할 증상이 있다.
잘못된 엔터티 유형은 팬아웃 방지를 끈다. 외래 키를 기본 키로 선언하면 계층은 여러 행을 하나인 것처럼 조인하고, 그 조인을 거치는 모든 합계가 부풀려진다. 구문 분석으로는 이를 잡아내지 못한다. 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 v1.12 이후의 dbt 명세는 엔터티와 차원을 열 아래로 옮기고 측정값(measure)을 단순 지표로 대체한다(dbt Labs). 예전 이름이 Open Semantic Interchange였던 Apache Ossie는 또 다른 형식이며(Apache Ossie), MetricFlow는 변경 기록에 적힌 대로 이를 위한 변환기를 제공한다. 형식 하나를 골라 버전 관리에 두고, 변환은 테스트한 도구로 하고 손으로는 하지 않아야 한다.
작업 규칙: 시맨틱 계층을 코드처럼 테스트한다. 엔터티 유일성을 데이터와 대조해 확인하고, 모든 지표의 SQL을 CI에서 실행하며, 변경할 때마다 MetricFlow 및 핵심 질문과 비교하고, 계층을 유지 관리하는 사람이 아닌 모든 주체에게서는 구조화된 요청만 받는다.
이 장에서 내린 결정
- 상점의 지표는 한곳에 둔다. 그곳은
examples/data/semantic/*.yml이며, MetricFlow의 독립형 YAML로 작성한다. 시맨틱 모델 다섯 개, 지표 열 개, 시간 스파인 하나로 구성된다. 1장의net_revenue는 정의를 이곳에서 읽는다. - 단순 지표에는 measure를 쓰지 않는다. 각 지표는 자신의 시맨틱 모델, 집계, 표현식을 명시한다. 이는 dbt 사양이 택한 방향이며, MetricFlow 0.214.0이 이를 읽는다.
- 이 책은 자체 코드로 컴파일한다. 그 코드는
lib/semantic.py이며, sqlglot 표현식을 만들고, 다대일 조인만 허용하며, 비율이나 다중 지표 요청의 각 입력을 따로 집계하고, 팬아웃은 이유를 밝혀 거부한다. - 요청에는 이름과 값만 담고 SQL은 담지 않는다. 필터 값은 표현식 트리에서 리터럴이 된다.
- 회계 달력은 시간 스파인의 사용자 지정 그레인이며,
fiscal_year와fiscal_quarter이 그것이다. 각 값이 하나의 기간을 가리키도록 레이블을 붙인다. - 웨어하우스에 뷰 세 개로 이루어진
semantic스키마가 추가된다. 그래서 모든 팩트는 자기만의 날짜를 가지고, 모든 회계 분기는 이름을 가진다.raw스키마와analytics스키마, 그리고 기록된 모든 프롬프트는 바뀌지 않는다. - dbt 없이 MetricFlow만으로 정의를 검사한다. 검사는 하나의 모듈을 통해 이루어지며, 이 장이 비교하는 모든 요청에서 책의 컴파일러와 결과가 일치한다.
metricflow패키지는 Apache 2.0 라이선스이고 dbt에 의존하지 않고 오프라인으로 실행된다. 실무에서 흔히 쓰는 경로는dbt-metricflow이다. - 하나의 정의를 웨어하우스별로 트랜스파일한다. 이때 시간 컬럼은 절단하기 전에 날짜로 캐스팅하며, 트랜스파일된 SQL은 해당 웨어하우스에서 실행되기 전까지 검증되지 않은 것으로 취급한다.
- 비율은 합계의 비율이다. 분자와 분모는 각각 자신의 필터와 시간 차원으로 따로 집계한다. 환불률은 해당 기간 주문의 환불액을 그 주문의 총매출로 나눈 값이며, 환불과 마찬가지로 총매출에도 세금이 포함된다. 이 책의 용어집과 검증된 쿼리도 같은 정의를 쓴다.
- 정의는 파싱만 하지 않고 데이터와 대조해 검사한다. 모든 표현식을 실행하고 모든 기본 엔터티가 유일한지 확인하므로, 잘못 선언된 키는 합계를 부풀리기 전에 발견된다.
이 판의 이 장
2026.11.15판예제 통과 2026년 11월 19이 장에서 8개 예제개 중 8개가 통과한다
영어판 2026.11.15판을 번역한 것이다.