Cloud Parts

Semantic layers

One shared, code-defined model of metrics, dimensions and joins that BI tools, apps and AI agents query by name, which the layer compiles to SQL against the warehouse and often caches.

2 technologies on this page ↓
Live model
What you’re watchingA BI tool, an app and an AI agent all ask the semantic layer for the same metric by name. The layer answers most requests from rollups it refreshed ahead of time; the rest it compiles to SQL and sends to the warehouse, which takes far longer to reply.

How it works

Without one, every dashboard, notebook and app writes its own SQL for “revenue”, and they drift apart: one subtracts refunds, another doesn’t. A semantic layer moves those definitions into one model, usually YAML or a small language in git. The model names entities (orders, customers), the joins between them, dimensions to group and filter by (country, month, status) and measures or metrics with their aggregation (net_revenue = SUM(amount) - SUM(refunded)). Consumers ask for metrics by dimensions through the layer’s API: a SQL interface that looks like a database, REST or GraphQL for apps, or an MCP server for AI agents. The layer checks the request against the model, applies access rules such as row-level filters for the caller, works out the joins, and generates SQL in the warehouse’s own dialect. Many layers also cache: results in memory, or rollup tables refreshed ahead of time and used whenever a query matches them, so busy dashboards don’t rescan raw tables. The warehouse still does the heavy work; the layer decides what to ask it.

Think of it as

A restaurant chain’s recipe book. Every branch, the delivery app and the catering team order “house curry” by name, and the kitchen cooks it the same way each time. Nobody improvises their own version, and when the recipe changes, it changes everywhere at once.

Reach for it when

  • Several tools (BI, spreadsheets, embedded dashboards, AI agents) report the same metrics and the numbers disagree
  • Customer-facing analytics, where an app needs a governed API with per-tenant access rules instead of raw SQL
  • AI agents should answer questions from defined metrics instead of guessing at raw tables
  • Dashboards repeatedly hit the warehouse with the same aggregations and you want a cache in front of it

Think twice when

  • One team, one BI tool and a handful of reports: the BI tool’s own model or a few dbt models is enough
  • Exploratory analysis over new, unmodelled data, where analysts need raw SQL more than governed metrics
  • You can’t staff the model: an outdated semantic layer is a second source of wrong numbers

A real example

A SaaS company reports “active customers” and “net revenue” in its BI tool, in a customer-facing usage dashboard and through an AI chat assistant. Before, each had its own SQL, and the board deck and the app disagreed by 4% because one forgot refunds. With a semantic layer, the definition lives in one YAML file reviewed in pull requests. The BI tool connects to its SQL endpoint, the app calls its REST API with a token that limits each customer to their own rows, and the assistant uses its MCP server. A monthly rollup of revenue by plan and country answers 90% of dashboard queries in milliseconds without touching the warehouse.

Technologies

Specific products, and what each does differently.

What each provider calls it

AWS
Amazon Quick Sight Topics
Google Cloud
Looker (LookML)
Azure
Power BI semantic models
Open source & others
Cube, dbt Semantic Layer (MetricFlow, Apache 2.0), Snowflake semantic views, Apache Ossie (exchange format)

Where the model lives#

Most semantic layers do the same job; they differ in who owns the model and who can query it.

Inside a BI tool

Looker, Power BI

LookML models in a Git-backed Looker project describe dimensions, measures and joins, and Looker’s SQL generator turns each Explore query into SQL. Power BI semantic models are, in effect, Analysis Services tabular models. Both are built for their own tool first.

Standalone and headless

Cube

Cube Core keeps cubes and views in YAML or JavaScript and exposes them through a Postgres-compatible SQL API, REST and GraphQL, with pre-aggregations as its cache. It has no UI of its own, so any BI tool, app or agent can sit on top.

Next to the transformations

dbt

The dbt Semantic Layer defines metrics in the dbt project, on top of the models that build the tables. MetricFlow compiles requests into a query plan and then into SQL for the target engine; it moved to the Apache 2.0 licence in 2025. The hosted Semantic Layer needs a dbt Starter or Enterprise-tier account.

Inside the warehouse

Snowflake

Snowflake semantic views are schema-level objects created with CREATE SEMANTIC VIEW. You query them with SELECT, share them through listings and give them to Cortex Agents, but only inside Snowflake.

In code · python

import sqlite3

# The semantic layer: metrics, dimensions and joins, defined once
MODEL = {
    "table": "orders o",
    "joins": {"customers": "JOIN customers c ON c.id = o.customer_id"},
    "metrics": {"revenue": "SUM(o.amount)", "orders": "COUNT(*)",
                "net_revenue": "SUM(o.amount) - SUM(o.refunded)"},
    "dimensions": {"country": ("c.country", "customers"), "status": ("o.status", None)},
}

def compile_query(metrics: list[str], by: list[str]) -> str:
    dims = [MODEL["dimensions"][d] for d in by]
    joins = sorted({MODEL["joins"][j] for _, j in dims if j})
    cols = [f"{expr} AS {d}" for d, (expr, _) in zip(by, dims)]
    cols += [f"{MODEL['metrics'][m]} AS {m}" for m in metrics]
    sql = f"SELECT {', '.join(cols)} FROM {MODEL['table']} {' '.join(joins)}"
    return sql + (f" GROUP BY {', '.join(e for e, _ in dims)}" if dims else "")

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE customers (id INT, country TEXT);
  CREATE TABLE orders (id INT, customer_id INT, amount REAL, refunded REAL, status TEXT);
  INSERT INTO customers VALUES (1, 'DE'), (2, 'FR');
  INSERT INTO orders VALUES (1, 1, 100, 0, 'paid'), (2, 1, 50, 50, 'refunded'), (3, 2, 80, 0, 'paid');
""")

# A BI tool, an app and an AI agent all ask by name; nobody rewrites the SQL
sql = compile_query(["net_revenue"], by=["country"])
print(sql)
# SELECT c.country AS country, SUM(o.amount) - SUM(o.refunded) AS net_revenue FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.country
print(db.execute(sql).fetchall())   # [('DE', 100.0), ('FR', 80.0)]

Often confused with

Real-time analytics databasesData warehousesETL & ELT pipelinesStatic read models
Where it came from1991–2025

The idea dates to the start of business intelligence. In November 1991 Business Objects filed a US patent, granted in 1996, for a system that let end users query relational databases “without knowing the relational structure” or SQL: users worked with familiar business terms, called business objects, and the software wrote the queries. Rivals such as Cognos paid licence fees for it, and for decades each BI tool kept its own model that only it could use.

Looker made the model code. LookML describes dimensions, measures and joins in files kept in Git, and Looker generates SQL from it for every query; Google completed its acquisition of Looker in February 2020. In the late 2010s and early 2020s, standalone products split the model from the dashboard entirely, under names like “metrics layer”, “headless BI” or “universal semantic layer”. Cube.js, released as open source in 2019 from the engine behind the Statsbot BI app, served one model to any front end. dbt Labs said in 2021 that it would build its own semantic layer, and in 2023 bought Transform, whose MetricFlow engine compiles metric definitions into SQL.

Large language models raised the stakes: an agent writing SQL against raw tables invents its own definitions. In September 2025 Snowflake, RelationalAI and other partners announced the Open Semantic Interchange, an open-source effort to define a common, vendor-agnostic specification for semantic models. That autumn dbt Labs moved MetricFlow to the Apache 2.0 licence, aligned with the same goals and built with partners including Snowflake and Salesforce. The format is now an Apache Incubator project, Apache Ossie, and Cube converts models to and from it.

Why it’s built this way

Business terms in front of SQL
The 1991 patent’s premise: people who know the business shouldn’t need to know table structures or SQL to ask a question, so the system maps their vocabulary onto the database.
Models as code in version control
LookML, Cube and dbt all keep definitions in files, so metric changes get review, history, tests and CI like application code, and AI tools can edit them through the same pull requests.
Compile, don’t copy
The layer generates SQL for the warehouse’s own dialect instead of storing another copy of the data, so the warehouse stays the single store and the model stays small.
Headless, with many APIs
A model locked inside one BI tool can’t serve apps, spreadsheets or agents. Standalone layers expose SQL, REST, GraphQL and MCP so every consumer gets the same numbers.

Timeline

  1. 1991

    Business Objects files its patent for querying databases through business objects (granted 1996)

  2. 2019

    Cube.js released as open source

  3. 2020

    Google completes its acquisition of Looker

  4. 2023

    dbt Labs acquires Transform and its MetricFlow engine

  5. 2025

    Open Semantic Interchange announced in September; MetricFlow moves to Apache 2.0

Sources11

Improve this page on GitHub