Semantic Layer as a Single Source of Metric Truth

The problem

Three teams report revenue.

Finance reports one figure, sales reports another, and the operations dashboard reports a third.

Each is defensible. Each was built by a competent person who understood the question they were asked.

None of them reconcile, and no two use the same definition of when revenue is recognised, whether cancellations are netted, or how partial refunds are treated.

The first time this surfaces in a meeting it costs twenty minutes.

The second time it costs trust, and trust is the asset the whole reporting layer runs on.

Once an executive has been given two different numbers for the same thing, every subsequent number carries an implicit question mark, and people quietly revert to maintaining their own spreadsheets.

At that point the platform investment has stopped paying for itself, while continuing to cost money.

The reason this is hard to fix is that it presents as a tooling problem and is not one.

The definitions live in the SQL behind individual dashboards — dozens of them, each with a slightly different WHERE clause that made sense at the time.

Changing dashboard vendors migrates the problem to a new vendor.

The definitions have to move somewhere they can be governed, and that somewhere is not the BI tool.

When to use this

• More than one BI tool, or more than one team, is building reports against the same warehouse.
• Core metrics appear in multiple places and must agree.
• Metric definitions have real complexity — fiscal calendars, currency conversion, revenue recognition rules or multi-level allocations.
• Definitions need version control and an audit trail because someone will eventually ask when and why a number changed.
• Downstream consumers extend beyond dashboards — embedded reporting, an API or agents querying metrics programmatically.

When not to use this

One small team, one BI tool, and a handful of agreed metrics

The semantic layer adds a build step, a deployment, and a new abstraction for analysts to learn.

Below a certain scale, a well-documented set of warehouse models can do the same job with less machinery.

The underlying models are not trustworthy yet

A semantic layer over badly modelled tables produces consistently wrong answers instead of inconsistently wrong ones.

That is a genuine improvement in diagnosability and no improvement at all in correctness.

Fix the modelling layer first.

Nobody with business authority will arbitrate definitions

This is the failure that kills these projects.

The semantic layer is a place to record decisions, not a mechanism for making them.

Without someone empowered to rule on what revenue means, the project becomes an engineering team mediating a political dispute, and it will stall.

Secure that owner before starting.

The requirement is ad-hoc exploratory analysis

When the shape of the data is not yet understood, analysts need SQL and direct warehouse access.

Constraining exploration through a governed metric layer is the wrong tool applied to the wrong stage.

Architecture

Metric definitions live in version-controlled configuration next to the transformation code that produces the underlying tables.

A semantic model declares the entities in a table, the dimensions available on it, and the measures that can be aggregated.

Metrics are then defined in terms of those measures — simple aggregations, ratios, cumulative windows and derived metrics composed from other metrics.

At query time a consumer asks for a metric sliced by dimensions, and the layer compiles that request into SQL.

It selects the correct tables, performs the joins, applies the filters that are part of the metric definition, and aggregates at the requested grain.

The consumer never writes the join or the filter, which is exactly why the definition cannot drift.

The operational benefit that makes this worth the effort is what happens when a definition changes.

Revenue recognition rules change. A new product line needs different treatment. A fiscal calendar shifts.

In the old arrangement, someone has to find every dashboard that touched revenue and hope they found them all.

Here, one definition changes, goes through code review, and every consumer reflects it on the next query.

Components

Tradeoffs

Implementation notes

Start with fewer than ten metrics

The instinct is to model everything the organisation currently reports.

That produces a six-month project that delivers nothing until the end and is cancelled at month four.

Take the metrics that appear in the most places and cause the most arguments, get those governed and adopted, and expand from there.

Expect the definitional work to dominate

Getting finance and sales to agree on one revenue definition is the deliverable.

Encoding it in YAML afterwards is straightforward.

Budget calendar time for the disagreement, hold sessions with real numbers on screen, and document what was decided and why.

Document the reasoning next to the definition

Do not record only the logic.

A future analyst looking at a filter that excludes a particular transaction type needs to know that it was a deliberate decision made for a specific reason.

Otherwise they may remove it as an apparent bug.

Migrate consumers deliberately

Do not leave the old dashboards running indefinitely alongside the governed layer.

A semantic layer that serves four dashboards while thirty others still carry their own definitions has added another source of truth rather than consolidating one.

The inconsistency is now harder to explain because some numbers are governed and some are not.

Test metrics like code

Assert known values for known periods.

A fixed month’s revenue should return a fixed number, and those assertions should run in CI.

Metric logic is business logic, and it regresses the same way.

Watch query cost after adoption

Compiling SQL per query means a dashboard that previously hit a pre-aggregated table may now scan considerably more data.

Identify common query shapes and cache or pre-materialise those specifically rather than discovering the problem through the warehouse bill.

References

• dbt Semantic Layer documentation — the reference implementation of this pattern, covering deployment, consumption, exports and caching.
  dbt Documentation
• Build your metrics with MetricFlow — the specification for semantic models and metric types, including ratio, cumulative and derived metrics.
  dbt MetricFlow
• About MetricFlow — explains how metric requests are compiled into warehouse SQL, which is the mechanism worth understanding before adopting the approach.
  MetricFlow Documentation
• dbt Semantic Layer in the data ecosystem — independent context on how the category developed, how it compares with LookML and Cube, and where the rough edges are.
  TextQL
• dbt Semantic Layer at scale — covers governance and change management, which is the part that determines whether the pattern survives its first year.
  B-EYE