Data Warehousing
Intermediate 13 min read Data Warehousing

The Semantic Layer

One definition of 'revenue', everywhere

A semantic layer defines metrics and dimensions once — names, definitions, allowed filters, access — and lets every dashboard consume them consistently instead of re-implementing SQL per chart.

semantic layermetricsdefinitionsconsistencyaccess
Prerequisites: Facts and Measures
Before we start

What you will learn

  • Distinguish modeling layer from semantic/metric layer
  • Define a metric (name + SQL + grain) once
  • Handle metric consistency and access in one place
Introduction

What is this?

A semantic layer sits between raw/modeled data and consumers, exposing curated metrics with canonical names, definitions, allowed granularities, and governed access. Dashboards query definitions rather than re-authoring GROUP BYs, so 'Revenue' means the same thing in every chart.

Motivation

Why do you need it?

  • Two BI dashboards defining 'Revenue' differently is the source of a thousand meetings.
  • It converts modeling work into governed value: definitions, not just tables.
  • Metrics change together and roll out to every consumer without dashboard rewrites.
Think about it

Mental model

Imagine

A shared menu at a restaurant chain

Think of

Semantic layer

Every franchise reads the same 'House Salad' definition and price — the kitchen (model) is consistent. Without the shared menu, each branch writes its own version and nobody agrees on what they ordered.

Mechanics

How it works

  • Dimensions and measures are declared with canonical names, types, and SQL expressions.
  • Metrics reference measures with a defined grain and aggregation (revenue = SUM(amount) where gross_amount).
  • Relationship maps link metric inputs so the engine can generate joins and group-bys.
  • Access and row/column security hang off the layer, not off individual dashboards.
  • Consumer tools translate metric calls into hidden SQL against the warehouse.

Where the semantic layer plugs in.

Modeled facts & dims
Semantic layer
BI tool
Dashboards
Hands-on

Examples

Defining the metric, not the query

Defining the metric, not the query
# (conceptual) semantic-layer definition
metric Revenue {
  description: "Gross sales revenue from completed orders";
  type: MEASURE;
  sql: "COALESCE(SUM(${gross_amount}), 0)";
  grain: order_line;
  filters: [ "status = 'completed'" ];
  exposed: true;
}
 
# A dashboard consumer then asks for `Revenue` by month + region
# and the layer composes the SQL — consistency guaranteed.

Definition once, governance everywhere — the dashboard never re-derives Revenue differently.

Real world

In production

  • A finance team exposes P&L metrics from the semantic layer; sales and support charts consume the same 'Bookings' definition.
  • Row-level security in the layer keeps each manager seeing only their region without per-dashboard joins.
  • A metric change (new definition of Active User) rolls out instantly to every dashboard.
Guidelines

Best practices

  • Name metrics precisely and resolve disputes in the layer, not in SQL copies.
  • Keep the layer thin over the modeled core; cheesy duplication recurs otherwise.
  • Expose grain and filters with each metric so consumers know what they are summing.
  • Govern access and version metrics with the same rigor as the schema.
Watch out

Common mistakes

  • Re-defining metrics inside each dashboard instead of using the layer, recreating inconsistency.
  • Over-layering: pushing every ad-hoc column into the semantic model and bloating it.
  • Forgetting row/security: the layer opens 'some SQL' access without the guardrails you documented.
When things break

Debugging tips

  • When two charts disagree, resolve each to its semantic definition and the filters applied.
  • A metric that returns NULL in one dashboard often has a filter or grain mismatch in the layer definition.
  • Verify join cardinality at the layer; repeated facts behind a metric double counts.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

A semantic layer lets dashboards read metric names instead of hand-written GROUP BYs.

2

Marketing defines 'Revenue' as gross before refunds; finance defines it net of refunds. Where is the fix?

Go deeper

Official documentation

dbt semantic layer
docs
Cube semantic layer docs
docs
Looker model: dimension & measure
docs