Chapter 20 · Semantic Layers, Metrics, Dimensions, Measures, and BI Contracts
Central Metric Definitions, Measures vs Metrics, Filters, Time Windows, Currency/Unit Semantics, and Ownership
Define measures and metrics centrally with explicit filters, date windows, units, currency, owners, zero-denominator behavior, and executable golden results rather than dashboard-local assumptions.
Chapter 20 begins from the Chapter 19 post-refresh warehouse
truth at source sequence 207:
10 current paid lines, 8 orders, 12 units, 820 USD gross
revenue, 495 USD cost, and 325 USD gross profit. The atomic fact grain remains
one current paid order line. This chapter does
not rewrite facts, SCD history, bridge allocations, physical
layout, or the acceleration layer. It adds a governed semantic
contract above those structures so consumers use the same metric
definitions regardless of whether a query is served from atomic
facts or an eligible accelerator.
Runtime: Python standard library plus SQLite;
generation evidence used Python 3.13.5 and SQLite 3.46.1.
Environment: local/in-process and synthetic,
with no paid service. Source lineage: AtlasMart
ERP orders/order lines plus governed warehouse facts and
dimensions. Fact grain: one current sales order
line. Time: governed metric windows use
inclusive warehouse order_date boundaries in UTC;
the lab does not infer user-local calendar dates.
Currency: the governed revenue metric accepts
USD rows only; mixed currencies are blocked until an explicit FX
policy exists. Security: synthetic geography
entitlements; row-level filtering is applied before aggregation.
Acceleration: the Chapter 19 daily/product
aggregate is a physical implementation option and must reconcile
to semantic truth. Non-guarantee: the tiny
compiler demonstrates contract mechanics, not a production BI
semantic engine or universal SQL dialect.
Learning outcomes
Write complete metric specifications including aggregation, filters, time, currency/units, ownership, and edge behavior.
Distinguish a raw/additive measure from a business metric evaluated in context.
Reject mixed-currency aggregation without an explicit conversion policy.
Test active-customer and retention boundaries, including a zero prior population.
1. Start with the decision, not the arithmetic
Finance asks “how much paid gross revenue did we book?” Growth asks “how many distinct customers bought during this interval?” Retention asks “what fraction of last period’s active customers returned?” The formulas look small, but each depends on a source event, population, time window, and unit. A central definition is useful only if it records all of those choices.
Calling amount_cents a metric is incomplete: it is
a row-level measure stored at the order-line grain.
SUM(amount_cents) is an aggregation, but it still
becomes a business metric only after status, currency, date
semantics, allowed grouping dimensions, ownership, and version
are fixed.
2. Required fields in a metric specification
| Field | AtlasMart example | Failure if omitted |
|---|---|---|
| Name/version | gross_revenue_usd.v1 |
Meaning changes in place |
| Population/filter | status='paid' |
Pending/cancelled events leak in |
| Measure/aggregation | SUM(amount_cents) |
Column is mistaken for a final metric |
| Time dimension/window |
order_date, inclusive closed range, UTC
|
Boundary events move or disappear |
| Unit/currency | USD cents → USD display | Incomparable values are added |
| Owner | finance-analytics / growth-analytics | No authority for changes/incidents |
| Zero/null behavior | retention denominator 0 → NULL | Undefined state becomes misleading 0% |
| Security inheritance | apply entitlement before aggregation | Restricted rows leak through aggregate |
3. Time windows are semantics, not UI defaults
The golden active-customer window is 2026-09-20 through 2026-09-22, inclusive, in the warehouse’s UTC date semantics. It contains six paid lines but only four distinct customers. The prior retention window is Sep 18–19 with two active customers; the current window has four; one customer appears in both; therefore retention is 1/2 = 50%.
WITH prior AS ( SELECT DISTINCT customer_id FROM fact_sales WHERE status='paid' AND order_date BETWEEN :prior_start AND :prior_end), current AS ( SELECT DISTINCT customer_id FROM fact_sales WHERE status='paid' AND order_date BETWEEN :current_start AND :current_end), counts AS ( SELECT (SELECT COUNT(*) FROM prior) AS prior_n, (SELECT COUNT(*) FROM current) AS current_n, (SELECT COUNT(*) FROM prior p JOIN current c USING(customer_id)) AS retained_n)SELECT prior_n,current_n,retained_n, CASE WHEN prior_n=0 THEN NULL ELSE retained_n*1.0/prior_n END AS retention_rateFROM counts;Dividing the same one retained customer by the four current-period active customers yields 25%. That query is mathematically valid but answers a different question. The denominator belongs in the metric specification.
4. Currency and units: reject ambiguity before conversion
All accepted current sales rows are USD cents. The lab copies them into a temporary probe and adds one EUR row. The gate sees two currencies and returns BLOCK. This is deliberate: a semantic layer must not invent an exchange rate or treat 1,000 EUR cents as 1,000 USD cents.
SELECT COUNT(DISTINCT currency_code) AS currenciesFROM currency_probeWHERE status='paid';-- if currencies > 1 and no governed FX policy exists: BLOCKA future FX-enabled metric would need an authoritative rate source, rate type (spot/close/average), effective time, source/reporting currency, rounding, missing-rate policy, restatement behavior, and version. Those are business/accounting rules, not merely SQL casts.
5. Measures, metrics, and derived metrics
Stored measure: amount_cents at
one order line. Aggregated measure:
SUM(amount_cents) in a query group.
Metric: gross_revenue_usd.v1 binds
that sum to paid USD rows and UTC order-date context.
Derived metric: retention computes a ratio of
two governed customer sets. Distinct counts and ratios are
non-additive: precomputing them requires compatible set/state
semantics, not blind summation.
6. Ownership is part of correctness
Finance Analytics owns gross revenue because it decides whether future returns, taxes, discounts, or restatements should alter the definition. Growth Analytics owns active-customer and retention semantics. Engineering implements and tests the contract but should not silently choose the business denominator. A change request therefore has an accountable reviewer and consumer migration plan.
Ownership also covers incidents: who responds when the source is late, when an accelerator is stale, when a consumer requests an unsupported currency, or when a metric version is deprecated.
7. Rerun and test behavior
Metric evaluation is deterministic for the same certified warehouse state, parameters, security principal, and metric version. The lab writes deterministic specification hashes; re-running setup recreates the same hashes and golden results. If a spec changes, the hash changes and compatibility must be revalidated. This is not distributed exactly-once execution; it is reproducible semantic evaluation over a fixed fixture.
gross_revenue_usd.v1 6be77e930e087257e5756fc2c9ccddd4293fed6a457d3a8c72badef65dad5e8aactive_customers.v1 1fb6a9bb4c820d321fe786b0809124fa279182c0f6a28e81db1194f37ada1eb0period_retention.v1 66e7840903abf5f871d39f72c35935d3d98cbadb8496480342f781777da3a9628. Production judgment and bridge
Do not make every ad-hoc calculation a centrally certified metric. Central governance is justified when a definition is shared, decision-relevant, security-sensitive, audited, or expensive to duplicate incorrectly. Keep experimentation possible in sandboxes, but require promotion before a number is labeled certified.
Lesson 3 adds semantic relationships, drill paths, and security. These determine which dimensions a metric can safely use and which rows a principal may see before aggregation.
Knowledge check
Check your understanding
-
Why is
SUM(amount_cents)still not a complete revenue definition? - Why is the retention denominator part of the contract?
- What should happen when a mixed currency appears without FX policy?
- Why is owner metadata operationally useful?
- When should a calculation remain exploratory rather than certified?
Review the answers
1. It omits population, time, currency, version, owner, security, and edge behavior.
2. Changing it changes the business question even if the numerator is unchanged.
3. Block/reject the ambiguous aggregation until governed conversion semantics exist.
4. It identifies the authority for definition changes and incident response.
5. When it is provisional, local to analysis, or not yet reviewed/tested for shared decision use.
Authoritative references
- Kimball Group — Conformed DimensionsBackground for shared dimensional meaning across analytical consumers and processes.
- Kimball Group — Fact TablesGrain-first fact-table guidance underlying safe metric aggregation.
- dbt — MetricFlow overviewCurrent official example of a semantic metric engine. It is a non-prerequisite product reference, not the course's semantic source of truth.
- dbt — Semantic LayerCurrent official example of centrally defined metrics consumed across tools; exact capabilities and syntax are product-specific.
- PostgreSQL — Row Security PoliciesOfficial engine-specific reference for row-level security. The mandatory local lab simulates entitlement filtering with joins instead of claiming SQLite has equivalent native RLS.
- SQLite — SELECTOfficial semantics for the deterministic local SQL examples.
- Python — sqlite3Standard-library interface used by the no-cost local lab.
9. Lab cleanup/reset
The mandatory examples use only the deterministic local Chapter
20 fixture. Delete atlasmart_ch20_lab (or your
configured local output directory) and rerun
python ch20_lab.py to recreate a clean state. The
script recreates the SQLite database and metric/report files; it
does not create cloud resources or modify the academy
repository.