In short. A semantic layer is one agreed definition of your numbers that every tool reads from. Large teams with many tools need a product for it. Most small teams already know the language that does the job: SQL.
What it is
Ask three people for last month's revenue and you can get three answers: one counts returns, one counts in local currency, one forgot the intercompany invoices. Each figure was correct according to its own definition.
A semantic layer sits between the data and everything that reads it. It holds the definitions in one place:
- measures: what "revenue", "margin" or "active customer" means;
- joins: how orders, customers and products relate;
- rules: currency, time grain, who may see which rows.
Every consumer goes through it: a BI tool, a spreadsheet, an app, an AI agent. They all get the same answer because they all ask the same definition.
Do you really need one?
Yes, when the same numbers are read in many places. Several BI tools across teams, a product that embeds analytics for its customers, AI agents that query data on their own. Without a shared layer, each of them redefines revenue, and the definitions drift apart.
Probably not as a separate product when one team reports from one BI tool. A Power BI semantic model is a semantic layer: measures in DAX, relationships, row-level security. It just mostly serves Microsoft's own front ends.
The real question is not "do we have a semantic layer" but "is there exactly one place where revenue is defined?"
The main players
| Option | What it is | Best fit | Watch out for |
|---|---|---|---|
| Power BI semantic model | DAX measures and relationships inside Power BI and Fabric | Microsoft shops | Other tools reach it through the XMLA endpoint, which needs Premium, PPU or Fabric capacity |
| Looker (LookML) | A modelling language that sits at the core of Looker | Teams standardised on Looker | Tied to Looker's licence and way of working |
| dbt Semantic Layer | Metrics in YAML next to dbt models, compiled to SQL by MetricFlow | Teams that already run dbt | MetricFlow is open source; the hosted APIs other tools connect to are on the paid dbt platform |
| Cube | A headless layer with SQL, REST and GraphQL APIs, caching and access rules | Apps, embedded analytics, AI agents | One more service to deploy and run |
| AtScale | An enterprise layer that serves Excel, Power BI and Tableau as if it were a cube | Large enterprises with many BI tools | Commercial, enterprise pricing |
| Warehouse-native | Snowflake semantic views, Databricks metric views | Teams committed to one warehouse | Tied to that warehouse |
The vendors are also working on a shared format. Snowflake, dbt Labs, Salesforce and others back the Open Semantic Interchange, so definitions can move between tools. It is early days.
How they differ
- Where the definition lives: inside a BI tool (Power BI, Looker), next to the transformations (dbt), in its own service (Cube, AtScale), or in the warehouse (Snowflake, Databricks).
- Who can read it: one front end, or any tool through an API.
- What else it does: caching and pre-aggregation (Cube, AtScale), access control, lineage.
- What you have to learn: DAX, LookML, dbt YAML, Cube's data model, or a vendor's UI. That is a new language for every option on the list.
I run Cube for the online-sales showcase on this site, and wrote up that stack. It is a good tool. It is also more than most small teams need on day one.
A simpler version: plain SQL, in git, written with an AI agent
If your team knows SQL, the definitions can be SQL. No new modelling language, no new service.
- One query per business object. Revenue, orders, the P&L lines: each is a SQL script with one owner and a readable layout.
- The query lives in the database, and Power BI reads it from there. A small table holds the scripts; Power Query fetches the text and runs it on every refresh. I described this in a Postgres + Power BI workflow. Nothing is copied into the report, so the report can't drift from the definition.
- The same scripts are in git. Every change is a diff with an author and a date. You can read it, reverse it, or ask why it happened.
- An AI coding agent does most of the typing. It writes and edits the SQL in your IDE, in your house style, and you review the diff. With read-only access to the database it can check its own work against real rows. That took one query from five minutes to 15 seconds in this post.
- Other front ends read the same table. The Excel view's DAX can sit in the row next to the SQL, so Power BI and Excel share one default view.
What you get: one definition of every number, in a language the whole team reads, in files you can open by hand when the agent isn't around. Nobody needs to learn a semantic-layer product to change a measure.
What you don't get: an API for apps, a query cache, or row-level rules beyond what database roles give you. You also need one rule: definitions change in git, never in the report. When you outgrow that, the SQL you wrote moves into dbt or Cube almost as it is, so none of it is wasted.
Where to start
Pick the three numbers people argue about most. Write each as one SQL query, commit it, and point the report at it. If nobody argues about those numbers next month, you have a semantic layer.