What Is a Semantic Layer? Define Metrics Once, Use Them Everywhere

Two dashboards show different revenue for the same month. Finance says one number, sales says another, and a meeting that should have been about decisions turns into a debate about whose query is right. The data is not wrong; the definitions are inconsistent. A semantic layer fixes this by defining business metrics once, in one place, and making every tool use those definitions.

What a semantic layer contains

• Metrics: calculations with business meaning, such as net revenue, active customers or 30-day readmission rate, including their exact rules (is a refund negative revenue or excluded?).
• Dimensions: the ways metrics can be sliced, such as region, product, month or customer segment, with their hierarchies.
• Relationships: how facts and dimensions join, so users never write joins themselves and can't accidentally double-count.
• Access rules: who may see which rows or columns, applied the same way in every tool.
• Descriptions: plain-language definitions that people, and AI assistants, can read.

Why it matters now

Classic BI tools always had some semantic layer (a Business Objects universe, an OLAP cube, a Power BI dataset), but it was locked inside one tool. Modern stacks have many consumers: several BI tools, notebooks, spreadsheets, applications, and now AI assistants. Defining "net revenue" separately in each one guarantees drift. A shared, tool-independent semantic layer keeps them aligned. It is also the most reliable way to let an AI analytics assistant answer questions, because the model picks from governed metrics instead of inventing SQL over raw tables.

A minimal semantic layer in 40 lines of Python

Real products do much more, but the core idea fits in a small example. Metrics and dimensions are declared once; a tiny compiler turns a request such as "net revenue by region" into SQL. Any tool calling compile_query gets the same definition.

import duckdb

con = duckdb.connect()
con.execute("""
CREATE TABLE fct_orders AS SELECT * FROM (VALUES
  (1, DATE '2026-07-03', 'EMEA', 'online',  120.0, 20.0),
  (2, DATE '2026-07-15', 'AMER', 'store',    80.0,  0.0),
  (3, DATE '2026-08-02', 'EMEA', 'store',   200.0, 30.0),
  (4, DATE '2026-08-20', 'AMER', 'online',   50.0,  5.0),
  (5, DATE '2026-08-28', 'APAC', 'online',   90.0,  0.0)
) t(order_id, order_date, region, channel, gross_amount, discount)
""")

# The semantic layer: metrics and dimensions are defined ONCE, in one place.
SEMANTIC_MODEL = {
    "table": "fct_orders",
    "dimensions": {
        "region": "region",
        "channel": "channel",
        "month": "date_trunc('month', order_date)",
    },
    "metrics": {
        "net_revenue": "SUM(gross_amount - discount)",
        "orders": "COUNT(DISTINCT order_id)",
        "avg_order_value": "SUM(gross_amount - discount) / COUNT(DISTINCT order_id)",
    },
}

def compile_query(metrics, dimensions, model=SEMANTIC_MODEL):
    """Turn a request like (['net_revenue'], ['region']) into SQL."""
    dims = [f"{model['dimensions'][d]} AS {d}" for d in dimensions]
    mets = [f"{model['metrics'][m]} AS {m}" for m in metrics]
    group = ", ".join(str(i + 1) for i in range(len(dims)))
    sql = f"SELECT {', '.join(dims + mets)} FROM {model['table']}"
    return sql + (f" GROUP BY {group} ORDER BY {group}" if dims else "")

# Two different "tools" asking questions -- both get the same definition of net revenue
print(con.sql(compile_query(["net_revenue", "orders"], ["region"])).df())
print(con.sql(compile_query(["net_revenue", "avg_order_value"], ["month"])).df())
print(compile_query(["net_revenue"], ["channel"]))

  region  net_revenue  orders
0   AMER        125.0       2
1   APAC         90.0       1
2   EMEA        270.0       2
       month  net_revenue  avg_order_value
0 2026-07-01        180.0        90.000000
1 2026-08-01        305.0       101.666667
SELECT channel AS channel, SUM(gross_amount - discount) AS net_revenue FROM fct_orders GROUP BY 1 ORDER BY 1

If finance later decides that net revenue should also subtract tax, you change one line in SEMANTIC_MODEL and every dashboard, notebook and assistant picks it up. (Run it with pip install duckdb pandas.)

Semantic layer options

Option	How it works	Good fit
dbt Semantic Layer (MetricFlow)	Metrics defined in YAML next to dbt models; queried through APIs and integrations	Teams already using dbt
Cube	Open-source semantic layer with SQL and REST/GraphQL APIs, caching and access control	Embedded analytics and multiple consuming apps
LookML (Looker)	Modelling language inside Looker	Organisations standardised on Looker
Power BI semantic models	Tabular models with DAX measures	Microsoft-centric organisations
Warehouse-native options	Semantic views and metric definitions inside the data platform itself	Keeping definitions next to the data

How to introduce one

1. Start with the ten metrics that cause arguments. Revenue, customers, churn, margin: write down the agreed definition with the business owner before writing any code.
2. Model on clean marts, not raw tables. The semantic layer sits on top of tested fact and dimension tables (see ETL vs ELT for how those get built).
3. Version-control the definitions and review changes like code; a metric change affects every report.
4. Point one high-visibility dashboard at it first, then migrate others as they are touched.
5. Add descriptions for every metric. They document the business logic and make the model usable by AI tools.

Summary

A semantic layer is a single, governed dictionary of business metrics that every tool shares. It ends the "which number is right" debate, simplifies self-service, and is the foundation for trustworthy AI-assisted analytics. It is layer five in the modern data engineering architecture.