What a semantic layer actually contains

A semantic layer is usually described as a concept: a layer between the tables and the questions that keeps the vocabulary consistent. True, and no help at all when you sit down to build one. In practice it is three stored things.

The three parts

That is all. A metric and its dimensions are created in a single transaction so that nobody ever observes half a metric — a measure without its permitted groupings would be worse than none.

The rule that matters

The measure and the dimension are never SQL a model wrote. They are exactly the expressions an administrator stored, inserted verbatim. Everything else — catalog, schema, table, column aliases — goes through an allowlist and is quoted.

That is the difference between a semantic layer and a collection of text snippets. If the model may compose the expression, you do not have an authorization model, you have a hope. If it may only choose one, the worst case is the wrong metric — not the wrong query.

What the caller still decides freely

Exactly one thing: the filter. Whoever asks may add a condition — “north region only”, “this quarter only”. That has to be free, otherwise the layer is useless. Three things keep it in bounds:

• The filter runs under the delegated identity of the person asking. It can never see more than they could in the SQL worksheet — the same grants and the same row and column policies decide.
• It is bounded: length, no control characters, no statement separator. The single statement can neither be taken apart nor have a second one smuggled in.
• The row count per query has a hard ceiling. A metric is an analysis, not a data export.

The join type is guarded three times over: checked on creation, enforced by a database constraint, and checked again while the query is built, where an unexpected value raises an error instead of emitting a keyword. That looks excessive until you ask what a tampered-with store could otherwise produce.

Why this is more than a naming convention

The real gain shows in the case every company knows: two departments define revenue differently. One subtracts returns, the other does not. Both are right, both built it that way in their own workbook, and the meeting has two numbers on two slides.

Without a semantic layer the definition is copied into as many places as there are reports. A correction has to be carried into all of them, and nobody knows where all of them are. With one, the definition is a single row. Change it and you change the answer in chat, in the dashboard and in the BI tool at once — because all three read that row rather than three copies of it.

That is also the test question for any implementation you are offered: does the SQL for chat, dashboard and BI come out of the same building block? If not, you have three semantics that share a name.

What a semantic layer does not solve

It does not make numbers correct, it makes them consistent. If the stored definition is wrong, every answer is equally wrong — consistently so, which makes it harder to notice. Deciding what revenue means is not something software takes off your hands. It is a leadership task; software only makes it enforceable.

Nor does it replace data quality. A clean metric over a table with duplicate rows returns a clean wrong number.

How you notice one is missing

1. 1Two reports on the same subject disagree, and resolving it takes longer than producing them did.
2. 2Asked how a metric is calculated, people point to a person rather than to a place.
3. 3An AI assistant is given a tool that accepts SQL — and nobody can say which queries it will run tomorrow.
4. 4A definition changes and the task reads: “check where this is buried.”

The third point is the new one. The other three have been familiar from reporting for twenty years; they were tiresome and manageable. An agent allowed to write SQL turns them into a security matter — and then the layer everyone talks about stops being a question of style.