Semantic Layer¶

Scope¶

The layer between modelled tables and the people (and tools, and agents) that ask questions of them: what a semantic layer is, the failure it exists to prevent, and how to decide whether you need one. Covers metric definitions as governed, versioned artefacts rather than expressions retyped into every dashboard; the division of labour between transformation logic in the warehouse and aggregation logic in the semantic layer, and why non-additive measures are the structural reason the layer exists at all; single-tool semantic models (Power BI semantic models, LookML) versus tool-independent headless layers (dbt Semantic Layer, Cube, AtScale, and the warehouse-native entrants — Snowflake semantic views, Databricks metric views); where row- and column-level security should actually be enforced, and why a model-enforced filter is not a security boundary when another path to the same bytes exists; metric versioning, deprecation, and how a consumer finds out a definition changed; caching, aggregate awareness, and query-cost implications; and the conditions under which a separate semantic layer is over-engineering.

For the physical models the semantic layer sits on top of, see general/data-modelling.md. For the BI tools that consume it, see general/business-intelligence.md. For catalog, certification, lineage, and the enforcement matrix across engines, see general/data-governance.md. For the medallion layering that produces the gold tables, see patterns/lakehouse-medallion.md. For platform selection, see patterns/data-platform-selection.md.

Checklist¶

The Metric Definition Problem¶

[Critical] Is there a single, named, version-controlled artefact that defines each business-critical metric — and can anyone point at it? The canonical failure mode of an ungoverned consumption layer is that every dashboard defines "revenue" or "active customer" slightly differently and nobody can tell which is right. This is not a discipline problem and it is not fixed by telling analysts to be careful. It recurs for structural reasons that are worth naming, because each one implies a different countermeasure:
The definition lives in a query, not in an object. SQL expresses the definition and then throws it away. The next consumer starts from the table, not from the definition, because the definition was never a thing they could reference.
Every variant is individually defensible. Gross versus net of returns, booked versus recognised, including or excluding intercompany, trailing-twelve versus fiscal-year-to-date — none of these is wrong. They are different questions that share a word. Disagreement between two correct answers is much harder to detect than one wrong answer.
The number carries no provenance. A figure rendered in a tile shows no definition, no owner, and no version. Two dashboards showing different revenue look identical in every respect except the digits.
Rewriting is cheaper than discovering. Typing sum(amount) takes seconds. Finding out whether someone already defined revenue, whether their version answers your question, and whether you are allowed to use it takes an afternoon. Consumers behave rationally and the estate forks.
Accountability attaches to the dashboard, not to the definition. Someone owns the report. Nobody owns "what revenue means", so nobody is on the hook when two reports disagree.
[Critical] Does the arrangement make reuse genuinely cheaper than redefinition, rather than merely making reuse possible? A semantic layer that requires a consumer to know it exists, find the metric, and learn a new query interface will lose to SELECT every time. The three things that actually change the economics are: the metric being discoverable from the tool the consumer is already in; the metric being queryable through the interface they already use (a SQL endpoint, a native BI connector); and the authoritative version being visually distinguishable from someone's fork at the moment of choosing. That last one is what certification and endorsement tiers are for — see general/data-governance.md, which covers the Promoted / Certified / Master data model and the point that if anyone can certify, certification asserts nothing.
[Critical] Is the metric layer's authority backed by a closed consumption path, or is it advisory? If analysts retain direct SQL access to the same tables — and in most estates they should — then the semantic layer governs the reports that go through it and nothing else. That is a legitimate design, but it must be stated: the layer is then a consistency and productivity mechanism, not a control. Treating an advisory layer as if it were a control is how organisations end up believing their numbers are governed when only some of them are.
[Critical] Is a metric owner named per metric or per metric domain, distinct from the owner of any report that uses it? Ownership of a definition is a different job from ownership of a dashboard, and conflating them means the definition changes whenever the dashboard's owner needs it to. general/data-governance.md covers the owner/steward split in general; the specific point here is that metric definitions need the same treatment as datasets, and are routinely left out of the governance scope because they look like code rather than data.
[Recommended] Are metric definitions expressed against a conformed dimensional model rather than against raw or staging tables? A semantic layer built on unconformed sources inherits every conformance failure underneath it and adds a layer of apparent agreement over genuine disagreement, which is worse than visible inconsistency. Two "customer" dimensions with different keys do not become conformed by being referenced from the same metric file. See general/data-modelling.md on conformed dimensions and the bus matrix.
[Recommended] Is there a written distinction between metrics (governed, owned, certified, few) and ad-hoc measures (analyst-authored, uncertified, many)? Trying to govern every calculation produces a review queue nobody staffs and drives authors around the layer. Governing twenty metrics that appear in board packs and regulatory returns, and explicitly not governing the rest, is achievable and is where the value is.
[Optional] Are metric definitions expressed in a form that a non-engineer can read and dispute? The definition's job is partly social: it is the artefact a finance controller and a data engineer argue over until they agree. A definition only a query planner can read cannot perform that function.

Placement: Warehouse Modelling Versus Semantic Layer¶

[Critical] Is the boundary between "logic in the warehouse" and "logic in the semantic layer" decided by a stated rule rather than by whoever implements each metric first? The rule that holds up: anything requiring a join, a row-level transformation, or entity resolution belongs in the warehouse; anything that is an aggregation rule belongs in the semantic layer, because it can only be evaluated once the user's grouping is known. Logic placed on the wrong side is not merely inelegant — logic that must run after grouping cannot be materialised into a table at all, and logic that resolves entities cannot be correctly re-derived per query.
[Critical] Is it understood that non-additive measures are the structural reason a semantic layer exists? A ratio, a rate, a percentage, or an average must be computed after aggregation: the numerator and denominator are summed over whatever slice the user chose, and only then divided. Materialise it as a column and every subsequent aggregation averages an average. general/data-modelling.md states the modelling half of this (store the numerator and denominator as separate additive facts, never the ratio); the semantic layer is where the second half lives — the rule that says how to combine them at query time. An estate with no semantic layer has nowhere to put that rule except in each consumer's query, which is exactly the redefinition problem restated.
[Critical] Are semi-additive measures given an explicit time-aggregation rule in the layer? Balances, inventory levels, and headcount sum across every dimension except time, where they must be point-selected or averaged. A BI tool's default is to sum. The default is wrong, silently, and only for the time dimension — which is the dimension every dashboard slices by.
[Recommended] Does the semantic layer sit on gold-layer conformed tables rather than reaching back into silver or bronze? The medallion boundary exists so that consumers get curated, quality-gated, business-rule-applied data (see patterns/lakehouse-medallion.md). A semantic layer that shortcuts to silver reimplements business rules that already exist upstream, and the two implementations diverge. Where the layer genuinely needs something silver has and gold does not, the fix is to add it to gold.
[Recommended] Is the semantic layer kept out of the business of fixing data-quality problems? Coalescing nulls, patching bad joins, and hard-coding exceptions inside metric definitions hides defects from the quality gates that exist upstream and makes the metric file the only place the workaround is recorded. Push the fix upstream and let the quality check fail visibly if it cannot be pushed.
[Recommended] Where the platform offers a warehouse-native semantic construct, has it been evaluated before a separate service is introduced? Snowflake documents semantic views as schema-level objects carrying dimensions, facts, and metrics — addressing what its documentation describes as the mismatch where "a critical business concept like gross revenue" is stored in a column named something like amt_ttl_pre_dsc. Databricks documents metric views as "the core implementation of Unity Catalog semantics in Unity Catalog", separating "measure definitions from the fields (also called dimensions) used to group, filter, and aggregate them", queryable from "SQL editors, notebooks, dashboards, Genie Agents, and alerts" as well as "Power BI, Tableau, Sigma, and other external BI tools". These are newer and narrower than the dedicated products, and they bind you to the platform — but they inherit the platform's security model rather than introducing a second one, which is a material advantage for the enforcement question below.
[Optional] Is there a rule about where filters and defaults live? A default date range or a default "exclude test accounts" filter placed in the semantic layer applies everywhere and is invisible in the consumer's query; placed in each report it is visible and inconsistent. Both are defensible; undocumented defaults in the layer are the version that produces "why does my SQL not match the dashboard".

Single-Tool Semantic Models Versus Headless Layers¶

[Critical] Has the single-tool versus headless choice been made deliberately, with the honest question being how many distinct consumption surfaces exist rather than which product is better? One BI tool means the BI tool's own model is the cheapest correct answer. Notebooks, applications, embedded analytics, a second BI tool, spreadsheet users, or agents querying metrics all argue for a tool-independent layer — because each is a surface that would otherwise redefine the metrics itself.

Criterion	Single-tool model (Power BI semantic model, LookML)	Headless layer (dbt Semantic Layer, Cube, AtScale)
Reuse outside the tool	Via that tool's endpoints only, with its licensing attached	Native — that is the product
Authoring skills	The tool's language (DAX, LookML) — widely available	A second modelling language on top of the warehouse model
Operational surface	None beyond the BI platform you already run	A service to deploy, monitor, scale, and secure
Query latency	Direct — no extra hop	Extra hop; caching mitigates but adds its own state
Security enforcement	The tool's model-level controls	The layer's controls, which the warehouse does not know about
Lock-in	Definitions are expressed in the vendor's language	Portable in principle; in practice bounded by the layer's own dialect
Failure mode	Every other consumer redefines the metrics	Two semantic layers, because the BI tool still lets authors build models

[Critical] If a headless layer is adopted, is the BI tool's own modelling capability actively constrained? This is the single most common way a headless deployment fails to deliver. Power BI authors can still build an Import model with their own DAX measures; Tableau authors can still write calculated fields; Looker explores can still carry view-level logic. Nothing about installing a headless layer stops them. Without a policy — and ideally a technical control on which connections authors may create — the estate now has two competing definition stores plus the operational cost of the new service, which is strictly worse than either alternative alone.
[Critical] Is the licensing and access implication of the chosen consumption interface understood before the architecture is fixed? A single-tool model reachable "from anywhere" is usually reachable through an endpoint that carries a licence requirement, a capacity requirement, or both. For Power BI specifically, the capacity and per-user licensing model is the dominant cost driver and is covered in general/business-intelligence.md; the Fabric platform detail sits in providers/azure/fabric.md.
[Recommended] For a headless layer, does the query interface match what the consumers actually speak? dbt's Semantic Layer exposes GraphQL, JDBC, and ADBC APIs and generates SQL against the platform rather than storing data — its documentation describes it doing "the heavy lifting to find where the queried data exists in your data platform and generates the SQL to make the request (including performing joins)". Cube is a code-first semantic layer with access policies and a pre-aggregation cache. AtScale exposes DAX and MDX endpoints so Power BI and Excel connect live to the layer rather than importing. The differences are less about modelling expressiveness than about whether your existing tools can connect without a rewrite.
[Recommended] Is the layer's own dialect and portability assessed honestly rather than accepted as "open"? A metric expressed in MetricFlow YAML, a Cube data model, and a LookML measure are three different languages. Portability between them is a migration, not a config change. The realistic claim for a headless layer is reuse across consumers, not escape from the layer — evaluate it on the first and do not pay a premium for the second. The Open Semantic Interchange initiative (announced September 2025 by a coalition including Snowflake, Salesforce, dbt Labs, and Cube) is an attempt to standardise the interchange format; treat it as a direction of travel rather than as delivered portability until the specification has shipping implementations you have tested.
[Recommended] Where the organisation is standardising on one BI vendor anyway, has the cost of the headless layer been compared against just using that vendor's model properly? A well-maintained Power BI semantic model with certified endorsement, RLS roles, and a small governed measure set solves the redefinition problem for a Power-BI-only estate. Adding a headless layer to that estate buys tool-independence you are not using.
[Optional] Has the AI/agent consumption path been considered as a consumer rather than as a separate initiative? Natural-language querying against raw tables reproduces the redefinition problem at machine speed and with less accountability — the agent invents a definition per question. A governed metric layer is the same fix for the same problem, which is why the vendors have converged on it; it is not a reason to buy one you would not otherwise need.

Security: Where Row- and Column-Level Policy Is Actually Enforced¶

[Critical] Is it understood that a semantic-layer security filter is a query rewrite, and that it therefore binds exactly the queries that layer generates and nothing else? This is the whole argument, and it is worth reasoning through rather than asserting. Row-level security in a semantic model works by adding a predicate to the generated query — Superset documents that the filter "clause field, which can contain arbitrary text, is then added to the generated SQL statement's WHERE clause"; Looker's access filters inject a condition into the Explore's SQL; Power BI evaluates a DAX filter expression per row. Column-level security works by removing the column from the model's metadata — Power BI's object-level security makes it so that "for viewers that don't have the required permission, it's as if the secured tables or columns don't exist." In every case the mechanism operates on a query the layer constructed. A request that does not pass through the layer is not rewritten. So the security question is not "does the layer enforce the policy" — it does — but "can I enumerate every path to these bytes, and does each path carry the policy?"
[Critical] Have the bypass paths been enumerated explicitly? They are not exotic and every vendor documents its own:
Direct SQL in the same tool. Metabase states plainly that "row and column security permissions don't apply to the results of SQL questions" and that "groups with native query permissions (access to the SQL editor) can bypass row and column security." Superset states that "in SQL Lab, RLS is enforced only when the RLS_IN_SQLLAB feature flag is enabled" — so the default configuration has an unfiltered path built into the product.
Edit rights on the model. Power BI documents that "RLS only restricts data access for users with Viewer permissions. It doesn't apply to workspace Admin, Member, or Contributor roles", and that workspace members with those roles "have edit permission for the semantic model and, therefore, RLS doesn't apply to them." Object-level security carries the identical limitation. Anyone who can edit the model can remove the filter; the population with edit rights is therefore the population that sees everything.
Save, download, or export. Tableau's documentation on user filters is explicit that "you need to set permissions so that users cannot save or download it and remove the filter, thereby gaining access to all of the data" — and contrasts this with its virtual-connection data policies, which do not "carry the same risk of exposing information if an author neglects to properly secure permissions on the workbook or data source, because the policy is enforced on the server for every query."
A second engine on the same tables. Covered in depth in general/data-governance.md: Fabric's T-SQL row- and column-level security applies only through the SQL endpoint and is invisible to Spark; Databricks tables carrying row filters or column masks cannot be read through the Unity Catalog Iceberg REST endpoint at all. A policy defined for one engine is not a policy for the table.
Direct object storage. In a lakehouse, the gold tables are files. A principal with read on the container, bucket, or path gets every row and every column regardless of what any catalog, warehouse, or semantic model says. This is the path that most often survives an otherwise careful design, because it is granted for a pipeline and never reviewed.
Machine identities. Power BI states that "service principals can't be added to an RLS role. Accordingly, RLS isn't applied for apps using a service principal as the final effective identity." Any integration that authenticates as an application rather than as the end user has, by construction, no per-user filtering.
[Critical] Is policy enforced at the lowest layer that every path traverses? That is the whole design rule and it falls straight out of the previous two items. If users can reach object storage, storage and catalog controls are the only real boundary (Unity Catalog, Lake Formation, or the equivalent — with the per-engine enforcement matrix verified, per general/data-governance.md). If the only path is the warehouse, warehouse-native row access policies and column masks are a genuine boundary. If the only path is the semantic layer and you can prove it, model-level security is a boundary. The failure is not choosing the wrong layer; it is choosing a layer and never enumerating what sits beneath it.
[Critical] Does the end user's identity actually reach the enforcement point? This determines whether engine-level enforcement is even available. If the semantic layer or BI tool connects to the warehouse with a shared service account — which is the norm for import models, caches, and most embedded deployments — then the engine sees one principal and cannot apply per-user policy no matter how well configured it is. Enforcement then must happen above the engine, which in turn means every other path to the data must be closed, because the layer above is now the only thing standing there. Deciding this late is expensive; it is a foundational question about the connection architecture, not a feature toggle.
[Critical] Is it understood that an import or extract is a new copy with no inherited policy? Power BI states it directly: "if you're importing data into your Power BI dataset, the security roles in your data source aren't used… If you're using DirectQuery, the security roles in your data source are used." The same is true of a Tableau extract, a Qlik in-memory app, and any materialised export. Importing does not merely cache the data — it creates a second governed object whose only protection is whatever the model defines, and whose existence must be inventoried for erasure and access-review purposes (see general/data-governance.md on downstream copies).
[Recommended] Is defence in depth the actual posture rather than a single layer chosen and trusted? The realistic arrangement in most estates: engine- or catalog-level policy as the boundary (it holds regardless of path), plus semantic-layer filters for consistency and for the paths where identity does not reach the engine, plus a periodic reconciliation that the two agree. Two layers that disagree are their own defect — a user who sees different rows through the BI tool than through SQL will report it as a bug, and finding out which is correct is a governance question, not a support question.
[Recommended] Are column-level controls distinguished from row-level in the design, given that they fail differently? Power BI is explicit that RLS does not restrict columns: "if a user has access to a particular row of data, they can see all the columns of data for that row." Object-level security is the separate mechanism, and its failure mode is a broken-looking report — users without permission "receive a message that the field can't be found for all report visuals using that field." Row-level failures are silent (fewer rows); column-level failures are loud (visuals error). Design for both, and tell consumers which they are seeing.
[Recommended] Are caches, pre-aggregations, and materialised exports keyed by security context or scoped to data that carries no user-level filter? A cache populated under one user's entitlements and served to another is a cross-tenant leak with a confident presentation. This is the sharpest edge in the whole caching topic and it is easy to introduce by accident when adding an aggregate table to fix a latency complaint. dbt's Exports "save metrics queries as tables in your data platform" — that table is a new object with its own permissions, not an extension of the layer's policy.
[Optional] Is there a periodic test that the bypass paths are still closed — an actual query attempt as a restricted principal through object storage, through a second engine, and through the tool's SQL interface? Enumerating paths at design time and never re-testing is how a pipeline's storage grant, added a year later, silently becomes the estate's widest hole.

Versioning, Deprecation, and Change Discovery¶

[Critical] Are metric definitions in version control alongside the transformation code, reviewed in the same pull request as the models they depend on? A definition in a wiki and a definition in the layer diverge; a definition in the repository next to the model that implements it is reviewable, diffable, and attributable. This is the same argument general/data-modelling.md makes for grain statements and SCD registers, and it is the precondition for everything else in this section.
[Critical] Is a change to a metric definition treated as a silent restatement of history, and handled accordingly? When the definition of revenue changes, every historical number computed from it changes too — the chart redraws, last quarter's figure moves, and nothing on the page says why. This is the definitional analogue of the bi-temporal problem in general/data-modelling.md: reproducing what a report said on a given date requires knowing not only what the data was but what the definition was. Decide up front whether definition changes restate history or apply forward only, and record the decision. Retro-fitting it requires version history that was not kept.
[Critical] Is there a mechanism by which a consumer finds out a definition changed — and is it a notification rather than a badge? This is the part that is almost always missing. A changelog entry, a version bump, and a deprecation flag are all discoverable if the consumer goes looking, and consumers do not go looking; they look at the number. general/data-governance.md makes the same point about decertification being silent unless someone is subscribed. The workable mechanisms are lineage-driven (identify downstream reports and notify their owners), subscription-based (consumers register interest in a metric), or blocking (the change does not ship until dependent report owners acknowledge). Choose one; "we publish a changelog" is not one of them.
[Recommended] Does deprecation run through a window in which both versions are live and distinguishable, rather than a cutover? A renamed or redefined metric that flips in place gives consumers no opportunity to reconcile. Publishing revenue and revenue_v2 side by side for a stated period, with the old one flagged deprecated and a documented end date, lets consumers see the delta on their own data — which is the only explanation that convinces a finance controller. dbt's model versioning provides the mechanics for the model layer; the same discipline applies to metric definitions.
[Recommended] Is there a removal step, and does anything verify a deprecated metric is actually unused before it is removed? Deprecation without removal accumulates until the layer contains three revenue metrics and the discoverability benefit is gone. Usage telemetry from the BI platform is the evidence — see general/business-intelligence.md on usage metrics and retiring dead content.
[Recommended] Are metric definitions covered by tests that run in CI, not just by review? The cheap and high-value ones: the metric compiles; it returns a non-null result over a known slice; it reconciles to a known control total; and — for any metric with a prior version — the delta between versions is reported in the pull request rather than discovered in production.
[Optional] Is a metric's definition history queryable, so that a disputed historical figure can be traced to the definition in force at the time? Expensive to build, occasionally decisive during an audit, and impossible to reconstruct retrospectively.

Caching, Aggregate Awareness, and Query Cost¶

[Critical] Is the caching model of the chosen layer understood, including what invalidates each cache? Cube documents two levels: an in-memory cache "active by default" and pre-aggregations, "a layer of the aggregated data built and refreshed by Cube", which "requires explicit configuration to activate". Power BI's Import mode is a full in-memory copy refreshed on a schedule, and Direct Lake and DirectQuery push down instead — with fallback conditions that matter (see providers/azure/fabric.md). The architectural question in every case is the same: how stale can an answer be before it is wrong, and does anything tell the consumer which they are looking at?
[Critical] Is aggregate awareness understood as constrained by measure additivity rather than by product maturity? Looker describes using "aggregate awareness logic to find the smallest, most efficient table available in your database to run a query while still maintaining accuracy", with aggregate tables required to be persisted in the database as persistent derived tables. The instructive limitation is that "distinct counts aren't supported with aggregate awareness because you can't get accurate data if you try to aggregate distinct counts" — except where the Explore query and the aggregate table query are identical. That is not a gap in Looker; it is arithmetic. Distinct counts and ratios cannot be rolled up from a summary, which is the same additivity constraint general/data-modelling.md states for fact design, surfacing again one layer up. Any plan that assumes aggregates will accelerate the whole dashboard should be checked measure by measure.
[Critical] Has the point been recognised that a semantic layer makes expensive queries easier to issue, so per-query cost governance matters more after adoption rather than less? Every tile on every dashboard is a query, every filter interaction is another, and the layer's entire purpose is to let non-engineers generate them without writing SQL. On consumption-priced platforms this is a direct and immediate cost lever. Model the cost of the dashboard refresh pattern, not of a representative query.
[Recommended] Are aggregates and pre-aggregations built for measured hot paths rather than anticipated ones? The same discipline general/data-modelling.md applies to aggregate tables applies here: build from usage telemetry, test that each aggregate reconciles with its atomic source, and treat drift between them as a defect rather than as rounding.
[Recommended] Is the freshness contract for cached content stated to consumers rather than implied? A dashboard that says "as at 06:00" is trusted correctly. A dashboard that silently serves an eight-hour-old aggregate next to a real-time tile teaches users that the platform is unreliable, and they are right.
[Recommended] Where the layer adds a network hop, has the latency been measured against the interaction pattern rather than against a single query? A 200 ms overhead is invisible on a report load and painful on a slicer that fires twelve queries. Test the interaction, not the request.
[Optional] Is the cache warm-up and refresh cost included in the capacity model? Pre-aggregation refresh is compute that runs whether or not anyone opens the dashboard, and it competes with the pipeline schedule for the same resources.

When a Semantic Layer Is Over-Engineering¶

[Critical] Has the "do we need one" question been answered against the actual estate rather than against the industry conversation? The honest answer for a small estate is frequently no. One BI tool, one or two people authoring models, a single conformed star schema, and a metric count in the low tens — the BI tool's own model is the semantic layer, and it is sufficient. In that configuration a separate headless layer adds a service to operate, a second modelling language, an extra hop, and — most damagingly — a second place a definition can live, which raises the risk of contradictory definitions before it lowers it.
[Critical] Are the crossing signals written down so the decision gets revisited on evidence rather than on enthusiasm? The ones that reliably indicate a tool-independent layer has started to pay: a second BI tool in production; notebooks, applications, or agents consuming the same metrics; embedded analytics for external customers; more than a couple of independent model authors; a metric disagreement that took more than a day to adjudicate; or a regulatory reporting obligation that requires a defensible single definition. None of these is about data volume, which is the criterion most often used and the least predictive.
[Recommended] Has the cheap intermediate been considered before the full layer? A governed set of gold-layer views that encapsulate the metric logic in SQL, owned, tested, and certified in the catalog, solves most of the redefinition problem for a small estate at a fraction of the cost. It cannot express aggregation rules that depend on the user's grouping — which is a real limitation and precisely the point at which you have outgrown it — but for additive measures over a conformed star it works, and it introduces no new runtime.
[Recommended] If the layer is being adopted primarily to fix trust in the numbers, has it been established that the underlying cause is definitional rather than data quality or conformance? A semantic layer over inconsistent source integration produces consistent answers to the wrong question, and does it authoritatively. Verify that the disagreement is about what the metric means and not about which customer table is right; if it is the latter, the fix is upstream and the layer will conceal the problem rather than solve it.
[Optional] Has anyone estimated the ongoing maintenance rather than the build? A semantic layer is a living artefact that tracks every business-rule change, every reorganisation, and every source migration. The build is a quarter; the maintenance is permanent, and it needs a named owner with capacity or it decays into the thing it replaced.

Why This Matters¶

A platform is bought to make the organisation's numbers consistent. The lake gets governed, the pipelines get tested, the models get conformed — and then the numbers are still inconsistent, because the last mile was never in scope. The consumption layer is where users actually meet the platform, and it is the only layer whose output anyone outside the data team ever sees. A governed lake underneath an ungoverned BI tier delivers the exact failure the programme was funded to fix, at full cost, with the additional problem that the platform now gets blamed for it.

The redefinition failure is worth taking seriously precisely because nobody does anything wrong to cause it. Each analyst writes a defensible query. Each dashboard passes review. The definitions diverge because the definition was never an object — it existed only as an expression inside a query that was written, run, and discarded. Six months later there are four revenue figures, all correct in their own terms, and adjudicating between them requires reverse-engineering four queries written by people who have moved teams. The cost is not the rework. It is that the executive population learns that data-team numbers require verification, and once that is learned it does not unlearn.

The security question is where the reasoning most often goes wrong, and the error is subtle enough to survive review. A row-level filter in a semantic model genuinely works: the rows are genuinely not returned. It is easy to move from there to treating the model as a boundary. But the filter operates by rewriting a query the model generated, so it constrains that path and no other — and modern platforms deliberately provide many other paths. The vendors say so in their own documentation, in plain terms: Metabase's row and column security does not apply to SQL questions and can be bypassed by anyone with SQL editor access; Superset enforces RLS in SQL Lab only behind a feature flag; Power BI's RLS and OLS do not apply to anyone with edit rights on the model; Tableau warns that a user filter is removable by anyone who can download the workbook. Underneath all of them, a lakehouse's gold tables are files, and a storage grant issued for a pipeline is a complete bypass of every layer above it. The design rule that survives contact with all of this is unglamorous: enforce at the lowest layer that every path traverses, and enumerate the paths before deciding what "lowest" means.

Versioning is the part organisations discover late, usually during an audit or a board-pack dispute. A metric definition change silently restates every historical number derived from it. If nothing recorded which definition was in force when a figure was published, the figure cannot be reproduced — not because the data is gone, but because the meaning is. This is the same failure as missing bi-temporal history, one layer up, and it has the same property: the cost of prevention is a version field and a written policy, and the cost of retrofitting is history that was never kept. The related and even more common gap is notification. Deprecation flags and changelogs are discoverable by consumers who look; consumers look at the number. Unless the change is pushed at the people depending on it, a definition change is indistinguishable from a data error, and it will be reported as one.

Finally, the layer is not free and is frequently not warranted. For a single-BI-tool estate with a handful of model authors, the tool's own semantic model does the job, and introducing a headless layer alongside it creates the failure it was meant to prevent — two definition stores, competing, with an extra service to run. The value appears when consumption surfaces multiply, and it appears sharply then. Adopting on that signal rather than on the general proposition that semantic layers are good architecture is the difference between a layer that pays for itself and a layer that becomes the third place revenue is defined.

Common Decisions (ADR Triggers)¶

ADR: Single-Tool Semantic Model Versus Headless Semantic Layer¶

Context: Metric definitions need a governed home and the organisation must decide whether that home is inside the BI tool or independent of it.

Criterion	BI-tool model (Power BI semantic model, LookML)	Headless layer (dbt Semantic Layer, Cube, AtScale)	Warehouse-native (Snowflake semantic views, Databricks metric views)
Consumers served	That tool, plus whatever its endpoints reach	Any client that speaks a supported API	Anything that can query the platform
New runtime to operate	None	Yes — a service with its own scaling and failure modes	None
Security model	The tool's own, separate from the warehouse's	The layer's own, separate from the warehouse's	The platform's — one model, not two
Portability	Vendor language	Layer's language; interchange standards immature	Platform-bound by design
Modelling maturity	Long-established (DAX, LookML)	Established, narrower ecosystems	Newest, narrowest feature set
Best fit	One BI tool, few authors	Multiple consumption surfaces	Single-platform estate wanting one security model

Decision drivers: the number of distinct consumption surfaces (the dominant variable), whether the estate is committed to one data platform, whether identity propagates to the engine, appetite for operating another service, and whether the BI tool's own modelling can be constrained if a headless layer is adopted.

ADR: Enforcement Point for Row- and Column-Level Security¶

Context: Sensitive data must be filtered per user, and the estate has multiple access paths to the same tables.

Options: semantic-model enforcement (RLS roles, access filters) — closest to the user, easiest to author, binds only the layer's own queries; warehouse-native policies (row access policies, column masks) — binds every SQL client, requires end-user identity at the engine; catalog- or storage-level enforcement (Unity Catalog, Lake Formation, storage ACLs) — binds every engine and every direct read, coarsest to author and with documented per-engine gaps; or layered enforcement with reconciliation.

Decision drivers: whether users can reach object storage at all; whether the end user's identity reaches the engine or a service account is used; how many engines read the same tables; whether import/extract copies exist; and whether the requirement is a compliance boundary (in which case only a layer every path traverses qualifies) or a consistency control (in which case the semantic layer is appropriate and cheaper). Record which of the two it is — conflating them is the actual failure.

ADR: Warehouse Modelling Versus Semantic-Layer Logic¶

Context: A given piece of business logic could be implemented as a transformation in the warehouse or as a definition in the semantic layer.

Options: materialise in gold-layer tables (fast, testable, cacheable, but fixes the aggregation at build time); define in the semantic layer (evaluated after the user's grouping, required for non-additive and semi-additive measures, but re-derived per query); or both, with the layer's definition generated from the same source as the table.

Decision drivers: whether the logic can be correctly evaluated before the grouping is known (if not, the decision is made for you); whether the result must reconcile with a materialised table; query cost and latency; and who maintains each side. Default: joins and row-level transformations in the warehouse, aggregation rules in the layer.

ADR: Metric Change Policy — Restate or Apply Forward¶

Context: A governed metric's definition must change, and historical figures computed under the old definition are already published.

Options: restate — the new definition applies to all history and every historical chart moves; apply forward — the old definition remains in force for prior periods and the change is visible as a break; or dual-publish for a deprecation window with both versions queryable and a documented end date.

Decision drivers: whether prior figures were published externally or to a regulator; whether the change is a correction or a genuine redefinition (corrections generally restate, redefinitions generally do not); whether the layer can express a definition's valid-from date at all; and how consumers will be notified. This is the definitional twin of the erasure-versus-immutable-history and valid-time-versus-bi-temporal decisions in general/data-modelling.md.

ADR: Adopt a Semantic Layer Now Versus Governed Views¶

Context: Metric inconsistency has been identified as a problem and a semantic layer has been proposed as the fix.

Options: adopt a headless semantic layer; use the incumbent BI tool's model with certification and a governed measure set; or build governed gold-layer views encapsulating the metric SQL, owned and certified in the catalog.

Decision drivers: the number of consumption surfaces; whether the problematic metrics are non-additive (views cannot express them correctly); whether the underlying cause is definitional or a conformance failure upstream; available capacity to operate and maintain another service; and whether the BI tool's own modelling can be constrained. Adopting the full layer without the crossing signals produces a third definition store rather than one.

Reference Architectures¶

BI-tool-native (the common small-estate default) — conformed gold-layer star schemas in the warehouse → one certified semantic model in the BI tool holding all governed measures → reports built against that model only, with authors prevented from creating their own connections to the underlying tables. Security enforced in the warehouse where identity propagates, with model-level RLS as a second layer. Cheapest correct arrangement when there is one consumption surface; fails the moment a second surface appears.
Headless layer over a lakehouse — gold tables in an open table format → a tool-independent semantic layer holding metric definitions in version control → BI tools, notebooks, applications, and agents all querying the layer's APIs. Security enforced at the catalog and storage layer because the file path exists and cannot be closed; semantic-layer access policies applied additionally for consistency. Requires an explicit control preventing BI authors from bypassing the layer, or the estate acquires two definition stores.
Warehouse-native semantics — metric definitions expressed as first-class platform objects (semantic views, metric views) alongside the tables → consumed directly by SQL clients and by BI tools that support them. One security model rather than two, no additional runtime, and the definitions live where the data lives. The trade is platform binding and a narrower feature set than the dedicated products, which makes this the right shape for a single-platform estate and the wrong shape for a deliberately multi-platform one.
Layered enforcement with reconciliation — catalog/storage policy as the compliance boundary, warehouse policies for SQL clients, semantic-layer filters for BI consumers, plus a scheduled test that queries the same slice as the same restricted principal through each path and alerts on divergence. More machinery than most estates need, and the only arrangement that detects the case where two enforcement layers have quietly drifted apart.
Deprecation window for a metric change — new definition published under a new version alongside the old; both queryable; deprecation flag and end date set on the old; dependent reports identified from lineage and their owners notified with the measured delta on their own data; usage telemetry confirms the old version is unused; removal. The notification and the telemetry-confirmed removal are the two steps most often skipped, and skipping either turns the window into permanent duplication.

Reference Links¶

dbt Semantic Layer -- metric definitions on top of dbt models, powered by MetricFlow, and the tier requirement
dbt Semantic Layer architecture -- GraphQL, JDBC, and ADBC interfaces, and SQL generation against the data platform
About MetricFlow -- the semantic modelling framework: semantic models, entities, dimensions, measures
dbt metrics overview -- metric types (simple, ratio, cumulative, derived, conversion) and their configuration
dbt Semantic Layer exports -- materialising metric queries as tables in the data platform
dbt Semantic Layer cache -- result caching and its invalidation behaviour
dbt model versions -- versioning and deprecation mechanics for governed models
Cube introduction -- code-first semantic layer: data modelling, access control, and caching pillars
Cube pre-aggregations -- the two-level cache and the aggregated-data layer Cube builds and refreshes
Cube row-level security -- access policies driven by user attributes
AtScale Power BI DAX connectivity -- live DAX connection from Power BI to a tool-independent semantic layer
What is LookML? -- Looker's modelling language for dimensions, measures, and explores
Looker aggregate awareness -- automatic rollup selection, the PDT persistence requirement, and the distinct-count limitation
Looker aggregate_table parameter -- LookML definition of aggregate tables
Looker access_filter parameter -- row-level filtering driven by user attributes
Row-level security with Power BI -- DAX role filters, the Viewer-only limitation, service principals, and Import versus DirectQuery source-role behaviour
Object-level security with Power BI -- table and column metadata restriction, and the Viewer-only limitation
Object-level security (Analysis Services) -- the underlying tabular-model mechanism and its restrictions
Overview of semantic views (Snowflake) -- schema-level objects carrying dimensions, facts, and metrics
Metric views (Databricks) -- Unity Catalog semantics separating measures from grouping dimensions
Row filters and column masks (Databricks) -- catalog-level fine-grained policy
Row-level security (Snowflake) -- row access policies enforced by the engine
Column-level security (Snowflake) -- masking policies and their evaluation
BigQuery row-level security -- row access policies and documented limitations
Tableau row-level security options -- user filters versus virtual-connection data policies, and the download-removes-the-filter warning
Superset security -- RLS filter clauses injected into the WHERE clause, and the RLS_IN_SQLLAB flag
Metabase row and column security -- sandboxed tables, the plan requirement, and the SQL-editor bypass
Qlik section access -- load-script-level data reduction by row and column
Open Semantic Interchange announcement (Snowflake, 23 September 2025) -- the vendor-neutral semantic specification initiative and its founding coalition