Home › Learn › Hard problems by layer › Semantic layer

Hard problems by layer · 9 of 10

Semantic layer: hard problems and anti-patterns

The hard challenges and common anti-patterns of semantic layer in a data platform, with how each is solved and tested. This layer must keep every business definition in one governed place that people, tools and agents all read.

↑ All layersSource systemsIngestionBronzeBronze to silverSilverSilver to goldGoldGold to semanticSemantic layerSemantic to UIAll challenges

Where we are

Semantic layer

SourcesystemsIngestionBronzeBronze tosilverSilverSilver togoldGoldGold tosemanticSemanticlayerSemantic toUI
The ten layers and hops from source to screen. The dark box is this page.

What this layer must achieve. Keep every business definition in one governed place that people, tools and agents all read.

Handled by: semantic layer generators, metadata store.

The hard part

5 challenges, and how they are solved

3 solved1 assisted1 people decideStatus: 4 building, 1 designed
Solved means the engine does it once the decision is declared, and a test proves it. Assisted means the tool suggests and checks, and a person confirms. People decide means a business decision the tool records and enforces.

Every example comes from the tyre world, a synthetic tyre company whose fourteen simulated days carry planted problems with known right answers, so each fix is tested, not just described.

ChallengeWhat goes wrongTyre-world exampleHow we solve itVerdictStatus
Definitions nobody agreed
SEM-01
Two teams certify different OTIF formulas.Partial deliveries counted or not.The tool versions, certifies and tests definitions and flags two that disagree; the definition itself is a business decision.

assurance rules SERV-04; decisions ADR-022 Metrics are certified before conversation interfaces and agents use them.

people decidebuilding
Definitions that change silently
SEM-02
Last year's number changes and nobody knows why.OTIF definition changed in January.Versioned definitions with a restatement note.

assurance rules SERV-04.

solveddesigned
Words people actually use
SEM-03
Questions in plain language miss because people say sell-through, sell-out or sales.Synonyms for OTIF.Synonyms and descriptions declared with each metric; new synonyms suggested from questions asked.

assurance rules SERV-06.

Shift-left checks that guard it: SL-SEM-07.

assistedbuilding
Security inside metrics
SEM-04
A regional manager sees other regions' totals through an aggregate.Region-level sales.Row filters declared once and applied in every metric.

assurance rules SEC-02.

Shift-left checks that guard it: SL-SEC-01, SL-SEC-02, SL-SEC-03, SL-SEC-04, SL-SEC-05, SL-SEC-08 and 1 more.

solvedbuilding
Provisional numbers that look final
SEM-05
A partial window is read as final.Sales while one dealer's file is missing.The complete flag from gold is carried into every metric and shown.

assurance rules SERV-02.

solvedbuilding

What not to do

Common anti-patterns

An anti-pattern is a habit that looks reasonable and keeps causing the same damage. These are the ones seen most often at this layer.

Anti-patternWhy it hurtsDo this instead
Business logic in BI tools or viewsThe same metric gets computed differently in each dashboard, and nobody can test it.Put calculations in gold build() or in the semantic layer's metric definitions.
Pointing a conversational query tool at raw silver tablesThe tool then guesses joins and meanings and gives plausible but wrong answers.Point it at metric definitions and described gold tables.

FAQ

Questions people ask

What are the hardest problems in the semantic layer layer?

Definitions nobody agreed; Definitions that change silently; Words people actually use; Security inside metrics; Provisional numbers that look final.

What is the most common mistake at semantic layer?

Business logic in BI tools or views. Put calculations in gold build() or in the semantic layer's metric definitions.

Next

Where to go next

Next: Semantic to UI

↑ All layers · All 69 challenges, filterable · Shift left: fail fast on data code · Medallion architecture