The semantic layer

Why do two dashboards built from the same warehouse disagree about revenue?

Two dashboards. One warehouse. Both say "Revenue, last quarter". One says 4.21 million, the other 4.06 million. Nobody has made an error, nobody is using stale data, and both queries can be shown to be correct against the tables they read.

This is the most common failure in analytics, and the first reaction is almost always the wrong one: to go looking for the bug. There is no bug. So what is actually different between the two, if the data is identical and the code is correct?

Write out what a revenue query has to decide. It must choose whether to count an order at the moment it was placed or the moment it was paid. It must decide whether refunds are subtracted, and whether they are subtracted from the quarter they were issued in or the quarter of the original sale. It must decide about tax, about shipping charged to the customer, about discounts, about test orders from the internal QA account, about orders from a subsidiary that reports separately, and about how a foreign currency was converted — at the day's rate or the quarter's average.

Now count the possible answers. Even with a dozen binary choices, there are thousands of internally consistent things "revenue last quarter" could mean. Both dashboards picked one. They picked differently.

Notice where the divergence actually lives. Not in the data, and not in the SQL as a matter of correctness — in the definition, which was never written down anywhere and so was re-decided, independently, by each person who needed it. And ask a sharper question: when the first analyst wrote their query, where were they supposed to look up the agreed answer? There was nowhere. The definition had no home, so it was reconstructed from scratch each time.

That reframes the problem. This is not a data quality issue and not an analyst competence issue. It is a coordination problem: many people need the same non-obvious decision, and there is no artefact that carries it.

So consider the obvious fix — write it down in a document. Does it work? Partially, and it is worth being clear about why only partially. A document is not consulted at the moment of writing SQL, it cannot be tested, and it drifts silently from the queries people actually run. Within a year you have a wiki page that describes a definition nobody uses, and you have not eliminated the divergence, only added a fourth opinion.

What would fix it properly? The definition must sit somewhere that queries cannot bypass. If revenue exists as a defined, versioned object — with its measure, its grain, its filters, its time basis, its currency rule — and every dashboard requests that object rather than writing its own arithmetic, then there is exactly one place the answer can come from. Change it once and every consumer changes together. Disagreement stops being possible rather than merely discouraged.

That is what a semantic layer is: the place where business definitions live, between the physical tables and the tools people use. Its ancestor is the business-object layer of tools like BusinessObjects in the 1990s; its familiar modern form is Looker's LookML, and more recently standalone metric layers such as Cube and dbt's MetricFlow that aim to serve one definition to many front-end tools.

There is a second thing it buys, less discussed but real. A defined metric knows which dimensions it may legitimately be sliced by. Revenue defined at order grain cannot honestly be broken down by individual product line if an order contains several products — and a layer that knows the grain can refuse that request instead of returning a plausible wrong number.

The most important correction is that one. A semantic layer resolves disagreement, not doubt. It cannot tell you whether refunds should net against the original quarter; it can only guarantee that once someone with authority decides, everyone gets the same answer. Installing the technology without settling the definitions produces a well-engineered layer that serves an arbitrary choice with great consistency.

A related misconception is that a single definition per metric is always the goal. Finance's revenue, recognised under an accounting standard, genuinely is a different quantity from the sales team's bookings, and forcing them into one number destroys information. The layer's real job is to make each definition explicit, named distinctly, and separately governed — not to collapse them. Two numbers with two names is a healthy state; two numbers with one name is the disease.

Two practical notes. A semantic layer only helps consumers who go through it — the moment one team connects a spreadsheet directly to the warehouse, that team is outside the guarantee. And defining metrics centrally does not mean defining them by one team: the durable arrangements let the domain that owns the data own its metric definitions, with the layer providing the mechanism rather than the authority.

• How metric definitions are tested, and what a regression test for a business definition even looks like.
• Why serving one definition to many BI tools is harder than it sounds, and what a query interface must expose.
• The governance question underneath it: who is allowed to change what revenue means, and how consumers learn that it changed.

Every term the collection defines is gathered in the glossary.