TransformGENERALTOOL-SPECIFICORG-SPECIFIC

The Metrics Layer

Business logic copied into twenty dashboards produces twenty definitions of revenue, all defensible. A metric defined once, with an owner, is the only fix — and it does not fix everything.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Two teams present revenue for the same quarter and the numbers differ. Both queries are correct. What is actually broken?

Who needs this

An executive who will make a decision from one number and has no way to interrogate it. They cannot read the SQL, they cannot see which filters the BI tool applied, and they have no reason to suspect that "revenue" is ambiguous. When two numbers arrive, the thing that breaks is not the report — it is their trust in every report (Trusting Data).

What one row is

A metric has three parts and all three are the grain: an expression (what is summed), a grain (per what — order, customer, day, country), and a filter set (which rows count). Two metrics with the same name and any one of those three different are two different metrics (Grain: What Does One Row Represent?).

The obvious build

Define revenue in the dashboard. The BI tool has a calculated field, the analyst writes SUM(amount) with a couple of filters, and the tile shows a number. It takes ten minutes, it needs no engineering, and it is correct.

Why it breaks

A second dashboard needs revenue. Its author writes the same calculation, from memory, and includes shipping — because for their question shipping is revenue. Both are defensible; both are now published under the same word.

How it breaks with real data

A second dashboard needs revenue. Its author writes the same calculation, from memory, and includes shipping — because for their question shipping is revenue. Both are defensible; both are now published under the same word.
A third dashboard filters status = 'completed'; a fourth uses status <> 'cancelled'. The difference is every order whose status is null, which is a small number until the day an upstream bug makes it large (Nullability & Defaults).
Refunds are subtracted in some dashboards and not others, and the ones that subtract them do it as of the refund date while the others do it as of the order date. The two numbers diverge at every period boundary and agree in the middle (Late-Arriving Data).
The definition changes. Finance decides revenue is net of shipping. Twenty dashboards must be found and edited, which requires knowing which twenty, which nobody does (Impact Analysis).
An executive receives two numbers in one week and asks which is right. The honest answer is that both are, and that answer is worse than either number (Two Dashboards, Two Numbers).
Someone builds a machine learning feature from one of the definitions and a finance report from another, so a model is trained against a target the business does not measure (Feature Pipelines).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

The anti-pattern is precise: business logic copied into every consumer. It is not that people are careless — each copy is written by someone who understands their own question, and each is correct for that question. The failure is that they share a *name* and not a *definition* (Data Platform Anti-Patterns).
Every copy is a fork. Forks drift the moment anything changes — a new status, a new revenue line, a new market — and there is no merge, because there was never a shared origin to merge back into.
A metrics layer inverts the dependency. The metric is defined once, in version control, as an expression plus a grain plus a filter set plus an owner. Consumers reference it rather than re-deriving it, exactly as a model references its upstream rather than naming a table (dbt Concepts).
That reference is what makes the metric findable, testable, versioned and changeable. Changing a definition becomes an edit and a rebuild with a known blast radius, rather than an archaeological expedition through a BI tool (The Transformation DAG).
Two implementations exist and they trade differently. Pre-aggregated marts compute the metric into a table at a fixed grain — fast, cheap to query, inflexible about slicing. Semantic layers hold the definition as metadata and compile it into SQL at query time against the fact table — flexible, and every query pays the aggregation cost (Data Marts).
Neither implementation supplies the part that matters most. A metric with an expression and no owner is a definition nobody is accountable for, and the question "is this still what we mean" has no addressee (Data Ownership).

Two queries, one word, two numbers

Here are two definitions of revenue for the same quarter, written by two competent analysts against the same fact table. Neither contains a bug. Neither is careless. They differ because the two analysts were answering slightly different questions and used the same word for both.

The first is a finance view: recognised revenue, net of settled refunds, excluding shipping, attributed to the date the order was placed. The second is a growth view: gross booking value including shipping, attributed to the date payment settled, with partial orders counted in full. Every one of those choices is defensible in the context it was made in.

What makes this the domain's hardest failure is that there is no technical remedy. Every data test passes on both. Reconciliation against the source passes on both. Lineage is complete for both. The only artifact that could have prevented it is a definition that both analysts were required to reference, and no amount of pipeline engineering substitutes for it.

Two defensible definitions of "revenue"

1-- Finance: recognised, net of settled refunds, excluding shipping,
2-- attributed to order date.
3SELECT
4  DATE_TRUNC('quarter', o.order_ts)                       AS period,
5  SUM(o.net_goods_amount_eur - COALESCE(r.refunded_eur, 0)) AS revenue
6FROM fct_orders o
7LEFT JOIN fct_refunds_by_order r
8       ON r.order_id = o.order_id
9WHERE o.status IN ('completed', 'shipped')
10GROUP BY 1;
11 
12 
13-- Growth: gross booking value including shipping, attributed to
14-- settlement date, partial orders counted in full.
15SELECT
16  DATE_TRUNC('quarter', p.settled_at)                     AS period,
17  SUM(o.gross_amount_eur + o.shipping_amount_eur)         AS revenue
18FROM fct_orders o
19JOIN fct_payments p
20  ON p.order_id = o.order_id
21WHERE o.status <> 'cancelled'
22GROUP BY 1;

Four independent differences — measure, refund handling, status filter, attribution date — and each one alone would produce a divergence. The two queries agree on nothing except the word in the column alias, and it is the alias that reaches the executive.

Define it once, and make consumers reference it

The fix is structural and unglamorous: the definition moves out of the consumers and into one place, in version control, with an owner, and consumers reference it by name. It is exactly the move that ref() made for models — the value comes from replacing a copied expression with a resolvable reference (dbt Concepts).

A definition is not just an expression. It needs the grain it is valid at, the filters that constitute it, the date it is attributed by, the dimensions it may legitimately be sliced by, and — the part most often omitted — a person accountable for whether it is still what the business means.

The last field in the definition below is the one that makes it an asset rather than a query. An unowned definition ossifies: it stays technically correct and slowly stops describing the business, and nobody is responsible for noticing.

Where the business logic lives

In every consumer

Each dashboard, extract and notebook computes revenue itself, from the fact table, with its own filters and its own attribution date. The definitions were correct when written and there is no mechanism that keeps them equal.

Once, referenced by name, with an owner

One versioned definition — expression, grain, filters, attribution, valid dimensions, owner — that consumers reference rather than re-derive, either compiled to SQL at query time or materialised into a mart.

A copied expression has no shared origin, so there is no operation that can bring the copies back into agreement when one of them changes — every copy is an independent fork of business logic, drifting on its own schedule as statuses are added and definitions are revised. Referencing by name converts a change from a search problem across an unknown set of consumers into an edit with a computable blast radius, and it makes the definition reviewable, testable and versioned in the same way a model is. What it does not do is decide what revenue means; it only guarantees that everyone is wrong or right together (Impact Analysis).

A metric definition, versioned alongside the models

1metrics:
2  - name: net_revenue
3    label: Net Revenue
4    description: >
5      Goods value of completed and shipped orders, excluding shipping and tax,
6      net of refunds that have settled. Attributed to the date the order was
7      placed, not the date payment settled. Never final: a refund settling in a
8      later period reduces the original period retrospectively.
9    owner: finance-analytics
10    model: ref('fct_orders')
11    calculation: net_goods_amount_eur - settled_refund_amount_eur
12    aggregation: sum
13    grain: order
14    time_attribution: order_ts
15    filters:
16      - "status in ('completed', 'shipped')"
17    valid_dimensions: [country, channel, product_category, customer_segment]
18    version: 3
19    effective_from: '2026-01-01'
20    supersedes:
21      version: 2
22      changed: Shipping revenue removed from the measure.
23    known_gaps:
24      - Chargebacks are not deducted; they are reported separately.
25      - Marketplace orders were out of scope before 2025-07.

known_gaps and supersedes do most of the work here. The first tells a consumer what the number does not include before they ask; the second is why a chart's history changed and what it changed from.

What a metrics layer does not fix

ORG-SPECIFICEvery row here except the first is organisational rather than technical — deadlines beating process, ownership lapsing, consumers outside the intended scope. A metrics layer in a company with no agreement about what revenue means records the disagreement precisely; it does not settle it.

It is worth being honest about the limits, because a metrics layer is often sold as a solution to trust and delivers something narrower. It makes definitions consistent, findable and versioned. It does not make them correct, it does not stop people going around it, and it does not create the ownership that is the actual scarce resource.

The failure that remains is the one this lesson opened with, in a subtler form. Two numbers reaching an executive is visible and gets fixed. One number reaching an executive under a definition that stopped matching the business two years ago is invisible, and a metrics layer makes it *more* convincing, because now it is the official number.

That is why the owner field matters more than the tooling. The technical layer guarantees that everybody computes the same thing. Only a person, periodically asking whether that thing is still what the business means, guarantees it is the right thing (Who Owns Data Quality).

Metric failures that survive a metrics layer

Trigger	Symptom	Cause	Response
A new order status is introduced upstream.	The metric drops by a few percent over a weekend, with no deploy and no failed test.	The filter set enumerates statuses and does not know about the new one. The definition is unchanged and now means something different (Semantic Changes).	An accepted-values test on the status column, in the model, blocking the build. A new value becomes a build failure and a conversation rather than a silent move.
An urgent question needs revenue sliced by a dimension the metric does not allow.	A dashboard appears that queries the fact table directly, computing its own version.	The layer was slower than the deadline. This is the normal way metrics layers erode, and it is a process failure rather than a technical one.	Make definition changes fast and make direct fact-table access a granted exception. A layer that is slower than going around it will be gone around (Data Access Control).
Finance changes the definition — shipping is no longer revenue.	Every historical chart changes overnight and no longer matches printed reports.	The definition was edited in place rather than versioned, so there is no way to reproduce what was reported under the previous rule.	Version definitions with effective dates, publish both series across an overlap, and mark the change on every chart's time axis (Planning a Backfill).
The metric's owner leaves the company.	Nothing, for a long time. Then a definition that no longer matches the business is discovered during an audit.	Ownership was recorded as a name rather than as a standing obligation with a review cadence.	Reassign explicitly and review definitions on a schedule. A metric nobody has confirmed in two years should be flagged as such to its consumers (Data Ownership).
A machine learning feature is built from a slightly different revenue definition.	The model optimises a target the business does not report, and its offline metrics look fine.	Feature pipelines consumed the fact table directly rather than the defined metric, because the metrics layer served BI and nothing else (Feature Pipelines).	Treat every consumer as a consumer — dashboards, extracts, reverse-ETL, training sets. A metrics layer that only covers the BI tool covers the consumers that complain, not the ones that matter most.
Everyone references the metric and it is wrong.	Perfect consistency across every consumer, every test green, and a number that does not match what finance reports.	Consistency was mistaken for correctness. Nothing in the layer compares the metric against the authoritative system.	Reconcile against the authoritative source for closed periods, on a schedule, and treat divergence as an incident (Reconciliation).

How to build it

Most important first.

Write the definition down in prose before writing it in SQL: what is included, what is excluded, at what grain, as of when, and who decides. The prose is the thing people disagree about; the SQL is a transcription.
Give every metric a named owner — a person or a team who is accountable for the definition, not for the pipeline. The owner is what turns a metric from a query into an asset (Data Ownership).
Define it once, in version control, alongside the models. A definition in a BI tool is a definition outside review, outside lineage and outside testing (Dataset Documentation).
Make the filter set explicit and tested. Most metric disagreements are filter disagreements, and an accepted-values test on the status column is what turns a future disagreement into a build failure (Data Tests).
Version the definition. When revenue changes from gross to net, that is a new version with a start date, not a silent edit — otherwise every historical number changes overnight and nobody can reproduce last quarter's report (Planning a Backfill).
Publish the definition next to the number — a dashboard tile that links to what its metric means and who owns it prevents more disagreements than any governance process — and forbid redefinition technically where you can, by granting BI tools access to marts and defined metrics rather than to raw facts they can re-aggregate however they like (Data Access Control).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A metrics layer guarantees that everyone referencing a metric gets the same expression, grain and filters. That is a real and narrow guarantee, and it is the one being bought.
It guarantees the definition is discoverable and attributable — you can find what it means and who owns it, which is often more valuable than the consistency itself.
It guarantees nothing about the definition being right. A single, consistent, wrong definition is exactly as wrong as before and considerably more convincing (The Pipeline Succeeded. The Data Is Wrong.).
It guarantees nothing about anyone using it. A metrics layer alongside unrestricted access to fact tables is a suggestion, and suggestions lose to deadlines (Data Access Control).
It guarantees nothing about consistency over time unless definitions are versioned. An unversioned edit silently changes every historical number the metric ever produced.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The strongest check is a reconciliation against the authoritative system: compare the metric for a closed period against what finance or the source system reports. It is the only check that tests meaning rather than structure (Reconciliation).
Add a consistency check across consumers: the metric computed from the metrics layer versus the same metric as any dashboard computes it. A divergence is a consumer that went around the layer.
Add an accepted-values test on every column the filter set references, so a new category fails the build rather than moving the number silently.
What all of these miss is the definition drifting away from what the business currently means. Nothing technical detects that. It is caught by a person who owns the metric asking, periodically, whether it is still right (Who Owns Data Quality).

Freshness

Pre-aggregated metrics are as fresh as their last build and cheap to read. Compiled semantic-layer metrics are as fresh as the fact table underneath and pay the aggregation on every query. The choice is a freshness-versus-query-cost trade with no default answer (Cost vs Freshness).
Metrics with late-settling inputs — anything net of refunds, chargebacks or corrections — are never final. Publishing them with an as-of date is honest; publishing them as settled produces a disagreement with finance at quarter end.
The freshest metric is not the most useful one. A number that changes while people are discussing it is worse than a slightly older number that is stable for the length of the meeting.

When the schema or meaning changes

A definition change is the highest-blast-radius change in a data platform, because it changes every historical number the metric ever produced. It needs versioning, an announcement and a stated effective date (Semantic Changes).
A new business event — a subscription product, a marketplace, a new market — usually means the existing definition is now incomplete rather than wrong. Incomplete definitions are harder to notice than wrong ones.
A new value in a filtered column changes the metric without changing the definition. This is the single most common way a metric moves for a reason nobody can find (Breaking Schema Changes).
Deprecating a metric requires knowing every consumer, which is the reason to make references explicit in the first place (Impact Analysis).

How to re-run this safely

A wrong definition is fixed by editing one place and rebuilding the descendant set — but every previously published number changes, and that is a communication problem before it is a technical one (Backfills).
Version definitions so a historical report can be reproduced under the definition that was in force when it was produced. Without that, "what did we report in March" is unanswerable.
When a definition changes, publish both the old and new series for an overlapping period. A step change in a chart with no explanation destroys more trust than the original error did (Trusting Data).

What can go wrong

Failure modes

The same metric name computed differently in several dashboards, each defensible, producing numbers that disagree by an amount too small to be obviously wrong and too large to ignore.
A metric defined once and then copied anyway, because the layer was inconvenient for one urgent question and the copy outlived the urgency.
A definition edited in place, silently changing every historical number, discovered when a chart's history no longer matches a printed report.
A metric with an owner who has left, so the question "is this still what we mean" has no addressee and the definition ossifies.
The mitigation failing: a metrics layer that everyone references *and* a BI tool with direct access to the fact table, so the layer describes what people should do and the fact table describes what they do (Data Engineering Anti-Patterns).

Misreads

"A metrics layer makes the numbers correct." It makes them consistent. A single wrong definition applied everywhere is more dangerous than several inconsistent ones, because inconsistency is at least visible (The Pipeline Succeeded. The Data Is Wrong.).
"The disagreement is a data quality problem." Both queries ran on the same correct data. It is a definition problem, and looking for a bug in the pipeline is the most common way weeks get spent on it (Two Dashboards, Two Numbers).
"We will document the definitions." Documentation that consumers are not forced to reference is documentation that drifts from what they compute. The reference has to be technical, not editorial (Dataset Documentation).
"The BI tool is the right place for business logic." It is the layer with no tests, no lineage, no review and no version history, sitting downstream of every check the platform performs (Who Actually Consumes This Data).
"One number per metric, forever." Some metrics legitimately have several variants — gross and net, booked and recognised. The requirement is that each has a distinct name and a definition, not that only one may exist.

Privacy, retention and access

A defined metric with an owner is the unit that regulatory and financial reporting can actually be built on, because it is the only artifact that says what a number means and who is accountable for it (Data Governance).
Access control belongs on the metric as much as on the table. A metric computed over a restricted population is a disclosure risk even when every underlying row is protected (Row and Column Security).
Versioned definitions with effective dates are what makes a historical report defensible under audit. An unversioned definition means no report older than the last edit can be reproduced (Data Retention).

Operating it

How you see it in production

Which queries hit defined metrics and which hit fact tables directly, from warehouse query logs. That ratio is the honest measure of whether the layer is real (The Data Catalog).
The metric value over time with definition-version changes marked on the same axis. A step change with no marker is an unrecorded redefinition (Data Observability).
Reconciliation divergence against the authoritative system, per period, tracked rather than checked once (Reconciliation).
Metrics with no owner, and metrics whose owner has not confirmed the definition in a long time. Both are quiet liabilities (Data Ownership).

What changes at 10x and 100x

At four dashboards, copied definitions are manageable and a metrics layer is overhead. The threshold is not a number of dashboards but the first time two of them disagree in public.
At a hundred consumers, the metric definition is the platform's most valuable artifact and its most contested one, and ownership becomes an organisational question rather than a technical one (Who Owns Data Quality).
Metric count scales worse than consumer count. Two hundred defined metrics with no ownership and no review is a different swamp from twenty dashboards with copied logic, not an improvement on it (Data Discovery).

What drives cost here

Pre-aggregation moves cost from query time to build time and is worth it exactly when the read-to-build ratio is high — which, for an executive dashboard, it always is (Scan Cost).
A semantic layer compiling metrics at query time costs an aggregation per query, and the cost scales with consumer count rather than with data volume (What Actually Drives Data Platform Cost).
The largest cost is not compute at all. It is the meeting where two teams reconcile two numbers, repeated quarterly, and the decisions delayed while it happens.

What this approach costs

Centralising definitions buys consistency and costs speed. An analyst who needs a slightly different cut now waits for a definition change instead of writing a query, and if that wait is long they will go around the layer — which is how the layer dies.
Pre-aggregated marts are fast and rigid; compiled semantic layers are flexible and pay per query. Choosing one platform-wide is usually wrong: high-traffic executive metrics want pre-aggregation, exploratory ones want flexibility (Data Marts).
Versioning definitions makes history reproducible and makes every change heavier. Teams that skip it get fast changes and unreproducible history, and only discover the cost during an audit.
An owner for every metric is the hardest part to sustain, because it is a standing obligation with no deadline. It is also the part without which the rest is a naming convention.

Metric definition lab

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Metric definition lab

Three switches, eight numbers, one word. This is why two dashboards disagree and neither is broken.

"Revenue"

140sim

Gross

200sim

Refunds netted

60sim

Distinct definitions here

8sim

Decision	This dashboard	Why anyone would choose the other
Refunds	netted	Finance nets them because that is what was earned. Sales reports gross because that is what was sold, and the refund was somebody else's problem.
Channels	all	A web team's dashboard was built before the app existed and nobody revisited the filter. This is the single most common cause of two numbers that "should" match.
Period	all days	A period boundary is also a timezone decision, and a report built in one office ends a day seven hours before a report built in another.

None of these switches is wrong. The failure is that they are switches at all — set independently in each dashboard, invisible to whoever reads the number, and impossible to diff. That is the argument for a metrics layer: not that it computes better, but that it makes the definition a single artefact somebody owns.

SIMULATEDComputed from the same four-order dataset. The magnitudes are trivially small on purpose — the finding is that the switches change the answer at all, not how much.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe failure — one name, several definitions, all defensible — is universal and predates every current tool. What varies is where the definition is allowed to live, and the only structural answer is: somewhere consumers must reference rather than re-derive.
TOOL-SPECIFICSemantic layers and metric definition formats differ substantially in what they can express — some handle only additive measures at fixed grains, others support ratios, windows and non-additive aggregations. A definition that is expressible in one may need to be a pre-aggregated table in another.
ORG-SPECIFICMetric ownership is an organisational commitment, not a configuration. In a company where nobody is accountable for what revenue means, a metrics layer records the disagreement in version control rather than resolving it — which is progress, but not the progress it is usually sold as.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databaseaggregation-and-grouping window-functions joins

Backendcontract-tests backend-api-versioning

Architecturecqrs

API Designwhat-is-an-api-contract versioning deprecation

Agenticevals-overview eval-metrics

Domains that do not exist yet

— DevOps / Production Engineering owns the change-management side of a definition edit: review, staged rollout, and the announcement that has to accompany a change which silently rewrites every historical number.