What is a semantic layer in an analytics stack, and what does it define once?

## The problem it solves Without a semantic layer, the logic that turns rows into numbers lives in whatever queried the table last: a dashboard's custom SQL, a spreadsheet formula, a notebook cell, a BI tool's calculated field. Each of those is a private re-implementation of a business definition, written by whoever was on the ticket, and none of them are reviewed. The result is the familiar meeting where two dashboards show different revenue and nobody can say which is right — not because the data is wrong, but because the *definition* was written four times. A **semantic layer** moves that logic out of the consumers and into one declared, version-controlled place. ## What it declares **Measures.** A measure is a named metric plus the expression, the aggregation and the filters that produce it. `net_revenue` might be `SUM(gross_amount - discount_amount - refund_amount)` over the order-line fact, excluding cancelled orders and internal test accounts. All three parts matter — a metric with the right expression and the wrong filter is still a wrong number. **Dimensions.** The attributes a measure may be sliced by, along with their labels, groupings and time grains. This is where you say that `order_month` means the calendar month of the *order* date, not the ship date, and that `region` comes from the customer dimension rather than the shipping address. **Join paths and grain.** The layer knows which entities join to which, on which key, and — critically — the grain of each. That is what stops a measure being summed across a join that fans rows out. A layer that understands grain will aggregate the fact first and then join, rather than joining and then summing a duplicated column. Many layers also carry descriptions, ownership, and access rules, which is what makes them a governance surface and not just a query convenience. ## How it is consumed A consumer sends a request in terms of the model — metrics, dimensions, filters, a grain — and the layer compiles it into SQL against the physical tables and returns rows. The consumer never names a table or writes an aggregation. Practically this means: - **Changing a definition changes every report.** When the controller decides refunds now net against revenue, you edit one definition and every dashboard, notebook and extract picks it up on its next query. That is the whole point, and it is also the reason definition changes need a review process rather than a quick edit. - **New questions do not need new SQL.** Any legal combination of the declared measures and dimensions is answerable, so an analyst asking a new slice is not a modelling ticket. - **The definition is readable.** "How is net revenue calculated?" is answered by opening one file, not by reverse-engineering a dashboard. ## Where it sits Modelled core → marts → semantic layer → tools. The layer governs *reading*; the marts govern *what exists and who owns it*. Pointing a semantic layer at a chaotic, unconformed pile of tables gives you consistently wrong numbers rather than inconsistently wrong ones — the underlying model still has to be right. A typical definition, expressed in the vendor-neutral shape most of these tools share: ```yaml metric: name: net_revenue source: fct_order_line grain: order_line aggregation: sum expression: gross_amount - discount_amount - refund_amount filters: ["order_status <> 'cancelled'", "is_internal_test = false"] ``` The specific modelling language differs by product, and that syntax belongs to whichever tool you are using — the portable idea is the three declarations: measure, dimension, join path. ## What it costs It is another component to operate, and it constrains analysts: a question the model does not express requires a change to the model, which is a review, which is slower than opening a SQL editor. That friction is the price of consistency, and if it gets too high people bypass the layer entirely and you are back where you started. The usual mitigations are a fast path for adding measures, and an explicit escape hatch to raw SQL for genuinely exploratory work, with the understanding that nothing built on the escape hatch is a certified number. ## In an interview Say what it defines (measures, dimensions, joins), say that consumers request metrics rather than write SQL, and say that a definition change propagates. Then show you know the limits: it does not fix an inconsistent underlying model, and it introduces a governance bottleneck you have to keep fast.