Semantic Layers and Consistent Metric Definitions
Seven orders sit in a table. Ask three analysts for “March revenue” and, without any error on anyone’s part, you can get 600, 550, and 100. The first summed gross order amounts by the order timestamp in UTC. The second subtracted refunds. The third grouped by the date each order shipped. Every number is arithmetically right and each answers a different question, but all three will be labeled “revenue” on a dashboard, and the first time two of them appear in one meeting the discussion is about whose number is wrong rather than what the business did.
A semantic layer is the part of a data platform that exists to prevent that discussion. It holds metric definitions once, in a form that tools can query, so that a dashboard, a notebook, and an API all compute “March revenue” from the same rules. This post explains what those rules must say, since a layer that serves an underspecified definition only spreads the ambiguity faster. It covers metric contracts, how dimensions and aggregation interact, time windows, versioning, and consumer consistency tests. The data and numbers are invented for illustration, and every figure was computed by running the queries shown against a small in-memory SQLite database; none comes from a real system.
The post builds on Dimensional Modeling and Historical Restatement, from which it takes the assumption that the underlying tables have a declared grain, keyed dimensions, and a known policy for history and corrections. It also assumes the vocabulary of facts, dimensions, and joins from Data Modeling for Operational and Analytical Systems. The definition of “region” or “customer segment” that a metric groups by is the modeling question of the previous post; this post is about the metric on top of it.
One question, several honest answers
The data below is a hypothetical orders table. Timestamps are in UTC, amounts are in a single currency, and refunded is the amount returned to the customer.
| id | region | order_ts (UTC) | ship_date | gross | refunded |
|---|---|---|---|---|---|
| 1 | N | 2026-03-30 10:00 | 2026-03-31 | 100 | 0 |
| 2 | N | 2026-03-31 16:30 | 2026-04-02 | 200 | 50 |
| 3 | S | 2026-03-31 23:10 | 2026-04-01 | 300 | 0 |
| 4 | S | 2026-04-01 02:00 | 2026-04-03 | 50 | 50 |
| 5 | N | 2026-04-02 08:00 | 2026-04-02 | 150 | 0 |
| 6 | S | 2026-04-03 12:00 | 2026-04-05 | 400 | 0 |
| 7 | N | 2026-04-03 09:00 | 2026-04-03 | 50 | 0 |
Five plausible definitions of March revenue give these results.
| Definition | Orders counted | Result |
|---|---|---|
| Gross amount, grouped by order date in UTC | 1, 2, 3 | 600 |
| Gross minus refunds, grouped by order date in UTC | 1, 2, 3 | 550 |
| Gross amount, grouped by ship date | 1 | 100 |
| Gross amount, grouped by order date in a UTC+9 time zone | 1 | 100 |
| Gross minus refunds, grouped by order date in a UTC+9 time zone | 1 | 100 |
Two different mechanisms produce the spread. The first is what is measured: refunds are subtracted or not. The second is when an order belongs to March: orders 2 and 3 are in March in UTC but fall on 1 April at 01:30 and 08:10 in UTC+9, and by ship date only order 1 had shipped by the end of March. Neither mechanism is visible in the label “revenue.” A metric definition has to fix all of them, which the glossary entry on metric definition describes in terms of the counted unit, the grain of the rows read, inclusion rules, time treatment, and calculation. The rest of this post turns that checklist into something enforceable.
What a semantic layer is, and where it sits
A semantic layer is a place where business concepts and metrics are defined for reuse, from which queries are generated for each consuming tool. It sits between the modeled warehouse tables and the consumers. The tables hold facts and dimensions at declared grains; the layer holds named metrics, the dimensions each can be sliced by, and the rules for combining them; consumers ask for “net revenue by region by month” and receive a query that applies those rules.
Definitions can live in several places, and the choice matters because it decides how many copies of the logic exist.
| Where the definition lives | Copies of the logic | Consistency across tools | Typical weakness |
|---|---|---|---|
| Inside each dashboard or spreadsheet | One per artifact | Agree only by accident | A change requires finding every copy |
| A shared SQL view or table in the warehouse | One per view | Good for tools that read the view; weak for slicing by arbitrary dimensions | Each new grouping needs another view, or users re-aggregate and re-introduce errors |
| A modeling layer inside one BI tool | One per tool | Consistent within the tool | Other tools and APIs do not see it |
| A semantic layer that generates queries for many consumers | One definition, many generated queries | Consistent for consumers that go through it | Consumers can bypass it; the definition is only as good as its owner |
Real products share a structure even though vocabulary differs. As described in the dbt documentation on MetricFlow (read on 3 October 2026), semantic models are the entry points from tables and carry entities (the join keys that define traversal paths between models), dimensions (the ways to group or slice), and, in the current specification, simple metrics that reference a single column expression. Metrics combine those into named quantities, and the documentation lists five metric types: simple, ratio, cumulative, derived, and conversion. The same page states that MetricFlow generates the SQL and uses the declared types of entities to choose joins, with the stated aim of avoiding fan-out and chasm joins. Cube’s documentation, read on the same date, describes measures as aggregations over table columns, with types such as count, sum, average, and rolling windows, and dimensions as categorical or time attributes. Both products continue to change their specification syntax across versions, so treat the examples in this post as showing concepts and check the documentation for the version you run.
A semantic layer does not decide what the right definition is. It serves the denominator you declared, faithfully. It also does not make the inputs correct: declaring a grain and join keys makes the intended relationship explicit but does not enforce that keys are unique or that required matches exist, so data tests on the input tables remain necessary. The layer also cannot stop a consumer from querying the warehouse directly, so adoption has to be measured, a point returned to in the testing section.
Metric contracts
A metric contract is the full written promise about one metric: the information a consumer may rely on and the information the producer is accountable for keeping true. It is a specialized data contract for a number rather than a table. The five-row table of definitions above shows the minimum it must settle; a working contract has more fields.
| Field | What it fixes | Example for net revenue |
|---|---|---|
| Name and question | The business question the metric answers | “How much money did orders placed in the period bring in after refunds?” |
| Owner | An accountable role who decides disputes and approves changes | Finance analytics lead (a role, not a team name) |
| Input grain and counted unit | What one input row is, and what is added | One row per order; the amount added is gross - refunded |
| Population and filters | Which rows are in or out | Test-flagged orders and orders canceled before payment excluded |
| Time attribute and zone | Which timestamp assigns a row to a period, and in which time zone | Order timestamp, converted to the reporting zone |
| Aggregation rule | How the metric combines across dimensions | Additive across region and customer; summed over time within a period |
| Allowed dimensions | Which attributes it may be grouped by, and what history they use | Region and segment as they were at order time (as-was) |
| Edge-case policy | Zero denominators, nulls, negative values, unsupported states | Partial refunds are subtracted; null refund treated as zero |
| Freshness and coverage | How current and complete the underlying data is | Complete through the previous day’s cutoff; stated on every result |
| Version and status | Which definition this is and whether it is current or deprecated | 2.0.0, current |
| Test cases | Inputs with known expected outputs | The seven-order table and its expected values |
Each field exists because omitting it permits a specific disagreement. The population field settles whether test orders count. The time field settles the 600 versus 100 gap. The edge-case field settles what happens when a refund is null, which otherwise depends on whether a tool treats null as zero or drops the row. Writing the contract as structured text makes it inspectable. The following is an invented, product-neutral format for illustration; it is not the syntax of any particular tool.
metric: net_revenue
version: 2.0.0
status: current
owner_role: finance-analytics-lead
question: Money brought in by orders placed in the period, after refunds.
input:
table: orders
grain: one row per order
expression: gross - refunded
null_policy: {refunded: treat_as_zero}
population:
include: all orders
exclude: [test_orders, cancelled_before_payment]
time:
attribute: order_ts
zone: Asia/Seoul # the reporting zone; stored timestamps are UTC
aggregation:
across_dimensions: additive
across_time: additive # a period total is the sum of its days
dimensions:
allowed: [region, customer_segment, order_date]
history: as_was # attributes as they were at order time
freshness:
complete_through: previous_day_cutoff
tests:
- input: orders_fixture_7
query: {period: 2026-03, group_by: []}
expect: 100 # UTC+9 March contains only order 1The last line of the sketch deserves attention. The expected value is not a guess about “revenue”; it follows from stating the reporting zone and the refund treatment, which is the point of a contract. A person reading this file can disagree with a field, and the disagreement is then about one explicit rule. When the owner changes a field, the change is a new version, not an edit.
The contract also needs a measure of how its implementation is kept honest. A contract file is not enforcement: the generated queries, the tests described later, and the approval process are what make it binding. Where a metric definition file is kept under version control, require the approvals and deployment permissions that your process specifies, and record which definition version was actually deployed and when consumers began using it. Repository history alone does not identify the definition behind a number published last quarter.
Dimensions and aggregation
A metric is only meaningful together with the dimensions it will be grouped by, because the aggregation rule determines which groupings produce a valid number. A semantic layer exposes a metric to many dimensions, so the metric’s rule must hold across all the allowed ones or the layer must refuse the grouping.
Additive, semi-additive, and non-additive metrics
The Kimball Group’s classification, covered in the previous post, applies directly: additive measures can be summed across any dimension, semi-additive measures across some but not time, and non-additive measures such as ratios not at all. Its recommendation for non-additive facts is to sum their additive components and compute the ratio last. The same idea appears in the semantic layer as a metric type. In dbt’s specification a ratio metric is defined by a numerator metric and a denominator metric, so the engine can re-derive the ratio at whatever grain is requested instead of aggregating a precomputed rate. For semi-additive measures, the dbt specification for simple metrics includes a non_additive_dimension setting that names the dimension, such as a snapshot date, along which values must not be summed and chooses a minimum or maximum to pick the value in a window.
The consequence of getting this wrong is silent. The seven-order table gives an average order value by region of 125 for North (4 orders, gross 500) and 250 for South (3 orders, gross 750). Averaging the two regional values gives 187.5, while the actual average order value across all seven orders is 1250 divided by 7, which is about 178.57. Both calculations are valid arithmetic; only the second answers “what is the average order value.” The difference occurs because North has more orders than South, so equal weighting of the two region rows overstates the smaller group. The ratio contract must therefore say: store or compute the numerator (gross sum) and denominator (order count) separately, and divide at the requested grain.
-- Correct: aggregate the parts, divide last.
SELECT SUM(gross) * 1.0 / COUNT(*) AS aov FROM orders; -- 178.571...
-- Wrong for an overall figure: average of group averages.
SELECT AVG(aov) FROM (
SELECT region, SUM(gross) * 1.0 / COUNT(*) AS aov
FROM orders GROUP BY region
); -- 187.5The two queries run on the example table return 178.571… and 187.5 respectively. The comment values come from that execution. The first is the pooled rate; the second weights each region equally. A distinct count is harder still: it has no additive components, so a monthly distinct-customer count cannot be built from daily distinct counts, which overlap. The non-additive fact entry lists the options, which are carrying the identifiers, a mergeable sketch, or recomputing at the requested grain. Cube’s documentation notes that its approximate distinct count type, which uses HyperLogLog, is additive in the sense that it can be used in rollup pre-aggregations, unlike an exact distinct count; that is a property of a sketch with a stated approximation error, and the contract should say whether an approximate figure is acceptable for the metric’s use. A ratio metric built on distinct counts inherits the same limit.
Dimensions from other tables, and fan-out
Grouping a metric by a dimension requires a join, and a join that matches more than once multiplies the measure. A semantic layer reduces this risk by encoding the relationship type, one-to-many versus many-to-one, in the model, so that when a user groups order revenue by a support-case attribute the engine can refuse or aggregate each side first. The dbt FAQ states that MetricFlow captures the types of each identifier so as to avoid fan-out and chasm joins. This protection depends on the declared keys being true. A table declared to have one row per customer that in fact has two rows per customer defeats it, and the symptom is an inflated metric with no error. The grain declaration and the uniqueness test on the keys are therefore part of the metric’s dependency chain, not separate concerns.
A second hazard is dimensions that do not mean the same thing across the facts a metric combines. If “region” is a customer region in orders and a warehouse region in shipments, a metric that combines them grouped by region is incoherent however it is computed. The layer can only serve conformed dimensions consistently, which is why the previous post treated conforming as a prerequisite. Finally, the history rule of the dimension must be in the contract. Region as it was at order time and region as it is today give different answers for customers who moved, and a consumer asking for “revenue by region” has to be told which one the metric returns.
Time windows
Time is where most metric disagreements originate, because every question about a period hides several choices.
- Which timestamp assigns a row to a period. An order has an order time, a payment time, a ship time. The Kimball pattern of a role-playing date dimension, where one date dimension is joined under several role names, exists because all of these are legitimate. A metric must name one. In the example, revenue by order date for March is 600 gross while by ship date it is 100.
- Which time zone defines a day and a month. Orders 2 and 3 fall in March in UTC and in April in UTC+9, so March gross is 600 or 100 depending only on the zone, with the definition otherwise unchanged. The contract should name the reporting zone, and the layer should convert stored UTC timestamps before truncating to a day. Truncating first and converting afterward gives a different result.
- How a window includes its edges. “The last three days” is ambiguous. Counting calendar days 1 to 3 April inclusive gives orders 4, 5, 6 and 7 with gross 50 + 150 + 400 + 50 = 650. Excluding the start date, so the window is 2 and 3 April, gives 600. A four-day window from 31 March gives 1150. Define windows as half-open intervals with explicit start and end, or as a count of complete periods ending at a named date.
- Calendar windows versus rolling windows. A calendar month has a variable number of days, which makes month-over-month comparison of an additive total depend on month length. A rolling 28-day window always spans four weeks and removes day-of-week effects from the comparison but does not align with reporting months. The contract should say which one the metric uses.
- Cumulative and to-date metrics. A running total or a month-to-date value is a different metric from the period value. dbt’s cumulative metric type takes a window or a grain-to-date setting, and the documentation says to specify one or the other, not both. Cube’s rolling window measure takes either a fixed window or a to-date type with a named granularity; its documentation, read on the stated date, also notes that some rolling-window behaviors depend on engine version and data source, for example whether the date range can be omitted.
- Periods with no rows. A day with no orders has no row, so a time series grouped from the fact table skips it, and a chart connects the neighbors. A time spine, a table with one row per period, lets the layer produce a row for each period with a zero. dbt’s simple metric settings include joining to the time spine and filling nulls with a chosen value; whether an empty period should read zero or unknown is a contract decision, since zero asserts that nothing happened while unknown says nothing was observed.
Late data interacts with every one of these. If an order for 30 March arrives on 3 April, the March total changes after it was first published. The previous post covers how such corrections are applied to the model. For the metric, the contract needs a statement of how results are labeled while a window can still receive data: a complete-through marker on the result, or a rule that the most recent days are flagged provisional. Without that marker, the first week of a month reads as a decline that is really a delay. The glossary entries on late data and lookback and on the conversion window discuss the same boundary for rates: a cohort that has not yet had its full window to complete is unfinished, not a known failure, so it should wait or be reported separately.
Versioning metric definitions
A metric definition changes for legitimate reasons: a refund policy changes, a population is redefined, a bug in an expression is found. Versioning makes those changes traceable rather than preventing them. The questions to settle are what counts as a breaking change, how consumers learn of it, and whether history is recomputed.
The semantic versioning idea transfers when the public interface is declared, and for a metric that interface is more than a name and a type: it includes the grain, the population, the time rules, and the aggregation rule. Changing what is counted changes the meaning and, for a stable interface, is an incompatible change. The next table applies that to the example.
| Change | Effect on values | Suggested version step |
|---|---|---|
| Redefine revenue from gross to net of refunds | March UTC result moves from 600 to 550, about 8.3 percent lower, with no change to periods without refunds | Major: the meaning changed |
| Change the reporting zone from UTC to UTC+9 | March result moves from 550 to 100 on the example data | Major: period membership changed |
| Add an allowed dimension, such as channel | Existing groupings unchanged | Minor: new capability |
| Fix an expression bug so values match the stated definition | Published values may change | Patch for the label, but still a restatement of published numbers needing notice |
| Change the display label only | None for the same query | No value change; record anyway |
The fourth row is a common trap. A version label that says “patch” can mislead consumers into thinking published numbers did not move. Whether a correction is a patch under the declared interface and whether it requires a restatement notice are separate questions; answer both. The dbt documentation distinguishes the display label of a metric, which only affects how it is shown in downstream tools, from definitional parameters, which is the same distinction.
For major changes, the transition has four parts. First, run the old and new definitions in parallel for a period and publish the gap, so consumers see “revenue will read about 8 percent lower under version 2” before their targets are affected. Second, give each version an effective date and a deprecation date for the old one. Third, decide whether historical periods are recomputed under the new rule. Recomputing supports trend analysis on one basis but changes numbers that were reported; keeping the old version preserves what was reported but makes comparisons across the change need an explanation. The glossary entry on metric change management suggests recording the decision and the measured delta for each change. The treatment of corrected history, the subject of the previous post, applies here too: a definition change that moves figures people acted on is a restatement and carries the same notice obligations, described in delivery revision and restatement. Fourth, retire the old version on its date, so two live definitions do not become a permanent split.
A dated changelog for each metric is the artifact that answers “why did this number change” quickly. Each entry should record the version, the effective date, what changed, the measured delta on a named period, whether history was restated, and who approved it. Every published result should carry the metric version used, because the same query can legitimately return different values under different versions and a number without its version cannot be reproduced.
History can also change with no definition change at all. A dimension attribute used for grouping can be corrected, or source data can be reloaded or backfilled, and a recomputed past period then differs. A version number does not capture those causes, so the result also needs references to the data state it was computed from, or at least the load or snapshot time, which connects back to the recorded-time column in the previous post’s dimension.
Consumer consistency tests
A definition that nothing checks drifts. Tests turn the contract into something that fails loudly. They fall into groups that catch different faults, and a single group is not enough.
| Test group | What it compares | Fault it catches | What a pass does not show |
|---|---|---|---|
| Definition tests | The metric on a small fixture with known inputs against hand-computed expected values | Expression errors, wrong time zone, wrong null handling | That production data resembles the fixture |
| Invariant tests | Total equals the sum of its parts for additive metrics; a ratio equals its pooled components; results do not depend on which dimension is added to the grouping | Fan-out, double counting, mean-of-means errors | That the definition itself is the intended one |
| Consumer parity tests | The same metric, filter, and period fetched through each consumer path: the generated query, the BI tool’s export, the API | A consumer that reimplemented the logic or applies a different default | Consumers not included in the test |
| Source reconciliation | The metric against an independent total, such as a payment processor’s report, at an aligned cutoff | Missing or duplicated input data | Errors shared with the reference |
| Version regression | Old and new versions on a fixed period; the delta is reported and compared with the announced one | An unannounced change in values | Whether the new definition is better |
| Input data tests | Uniqueness and not-null on keys, relationships present, history intervals well formed | Violated assumptions that make generated joins wrong | Business correctness |
The parity test is the one specific to a shared definition, and it is also the one most often missing, because it requires access to the paths consumers use. The following sketch shows the idea with a toy generator. It is an illustration of the mechanism, not a production semantic layer: a contract becomes SQL, a hypothetical dashboard computes the same number its own way, and tests compare them. This code was run with Python 3.12 and SQLite 3.53.4 against the seven-order table in memory, and the output lines shown are from that run.
CONTRACT = {
"net_revenue": dict(num="gross - refunded", den=None, tz=0),
"aov_gross": dict(num="gross", den="1", tz=0),
}
def metric_sql(name, group=None, start=None, end=None):
m = CONTRACT[name]
day = f"date(order_ts,'{m['tz']:+d} hours')" # convert zone, then truncate
where = f"where {day} >= '{start}' and {day} < '{end}'" if start else ""
sel = (f"1.0*sum({m['num']})/sum({m['den']})" if m["den"]
else f"sum({m['num']})")
g = f"{group}, " if group else ""
gb = f"group by {group}" if group else ""
return f"select {g}{sel} from orders {where} {gb}"
# Consumer A uses the contract; consumer B is a hypothetical dashboard
# that sums gross amounts.
a = q(metric_sql("net_revenue", start="2026-03-01", end="2026-04-01"))
b = q("select sum(gross) from orders where date(order_ts) < '2026-04-01'")
print(a, b) # [(550,)] [(600,)] parity test fails
# Invariant 1: total equals the sum of parts for an additive metric.
tot = q(metric_sql("net_revenue"))[0][0]
parts = sum(v for _, v in q(metric_sql("net_revenue", group="region")))
print(tot, parts, tot == parts) # 1150 1150 True
# Invariant 2: a ratio must be pooled, not averaged across groups.
pooled = q(metric_sql("aov_gross"))[0][0]
by_reg = q(metric_sql("aov_gross", group="region"))
print(pooled, sum(v for _, v in by_reg) / len(by_reg)) # 178.571... 187.5The output shows three separate things. The first line, 550 against 600, is a parity failure: the dashboard and the contract give different answers for the same question, and the difference is exactly the 50 of refunds on order 2, so the test points at the cause as well as the symptom. The second line shows an invariant that holds for an additive metric: net revenue of 1150 equals the sum of the two regional values. That invariant should hold for every additive metric and every grouping; a failure means a join multiplied rows or the group filter dropped some. The third line shows why the invariant cannot be applied to a ratio. Summing or averaging the regional values would not reproduce the pooled value, so the test for a ratio is instead that its numerator and denominator, each additive, satisfy the first invariant, and that the displayed ratio equals their quotient. A test that applied the additive invariant to a ratio would fail on correct code, which is why the metric’s declared aggregation rule has to select the test.
Limits of the sketch should be stated plainly. It does not model joins, so it cannot demonstrate fan-out; it assumes a single table and one fixed time zone offset per metric rather than daylight saving rules; and it builds SQL by string concatenation, which is acceptable for a fixed contract but would need parameter handling and escaping if the filter values came from users. Real engines handle these concerns and add their own validations; the dbt documentation, for example, separates validations of the semantic configuration from data tests such as uniqueness and relationship assertions, which is the split between the first and last rows of the table above.
Several practical rules make the test suite useful. Run definition and invariant tests whenever a definition or its inputs change, and parity and reconciliation tests on a schedule that fits how fast definitions and consumers change. Keep fixtures small enough to compute by hand, since an expected value nobody can verify is a snapshot of the current behavior rather than a test of the definition. Treat a failing parity test as a ticket for the consumer’s owner, because the layer cannot fix a tool it does not control. Finally, measure how much reporting is served through the layer and how often parity fails; those figures show whether the definition is actually the single source, not merely declared to be.
Failure modes
| Failure | Symptom | Cause | Control |
|---|---|---|---|
| Same name, different numbers | Two dashboards disagree for the same period | Definitions differ in measure, population, or time rule | One contract per metric; parity tests across consumers |
| Averaged ratios | An overall rate disagrees with the pooled computation | A ratio aggregated as a stored column instead of from components | Ratio contracts declare numerator and denominator; invariant test |
| Inflated totals after adding a dimension | A metric grows when a new grouping is added | A join multiplied rows; declared keys were not actually unique | Grouping-independence invariant; key uniqueness tests |
| Month boundary shifts | Period totals change when a tool changes its default zone | The zone is implicit, or truncation happens before conversion | Reporting zone named in the contract; convert before truncating |
| False decline in recent days | The latest period looks low and later recovers | Late data not yet arrived; no completeness marker | Complete-through marker; provisional flag |
| Silent meaning change | Historical comparison breaks without a data fix | A definition was edited without a version and announcement | Version every change; record delta; require approval |
| Bypassed layer | A spreadsheet or notebook computes its own version | Consumers query tables directly | Track adoption; provide a supported export; parity tests where feasible |
| Wrong definition served consistently | All tools agree and the business still disputes the number | The layer enforces consistency, not correctness | A named owner and a decision process for the definition |
The last row is the boundary of what this approach delivers. A semantic layer with consistent contracts, aggregation rules, time rules, versions, and tests guarantees that tools agree with the definition as written. It does not guarantee that the definition answers the question the business meant to ask. That judgment remains with the metric’s owner, which is why the contract names a role and a change process before it names an expression.
References
- dbt Documentation, About MetricFlow (read on 3 October 2026)
- dbt Documentation, Creating metrics (read on 3 October 2026)
- Cube Documentation, Data modeling overview (read on 3 October 2026)
- Cube Documentation, Measures reference (read on 3 October 2026)
- Kimball Group, Additive, Semi-Additive, and Non-Additive Facts
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.