Home › Learn › Hard problems by layer › Gold to semantic

Hard problems by layer · 8 of 10

Gold to semantic: hard problems and anti-patterns

The hard challenges and common anti-patterns of gold to semantic in a data platform, with how each is solved and tested. This hop must turn gold into numbers people use, each defined once, aggregated correctly and honest about completeness.

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

Where we are

Gold to semantic

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

What this hop must achieve. Turn gold into numbers people use, each defined once, aggregated correctly and honest about completeness.

This is a hop: the step that moves data from one layer to the next. Handled by: semantic layer generators, metadata store.

One of the two hardest steps. Numbers leave the data team here and reach people, tools and agents, so one definition has to hold everywhere. Most of the work is business decisions, which is why they are declared and tested rather than coded by hand.

The hard part

5 challenges, and how they are solved

5 solvedStatus: 2 building, 2 designed, 1 gap
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
Aggregating ratios wrongly
G2S-01
Averaging OTIF percentages across plants gives the wrong total; inventory summed over months is meaningless.OTIF by region.Each measure declares whether it is additive, semi-additive or non-additive; ratios are built from their parts.

assurance rules SERV-04.

solveddesigned
One number, many definitions
G2S-02
The dashboard, the conversation interface and a spreadsheet each compute OTIF differently.OTIF counting or not counting cancelled lines.One certified definition per metric, generated from the manifest, as the only source for every consumer.

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

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

solvedbuilding
Comparing across facts
G2S-03
OTIF and sales by region cannot be compared because the regions differ.Region from the dealer table versus region from the plant table.Conformed dimensions declared once and reused by every metric.

assurance rules SERV-04.

solveddesigned
Time intelligence
G2S-04
Year to date and fiscal periods computed differently each time.Fiscal year starting in April.One shared calendar with business dates and fiscal periods.

assurance rules LATE-04.

Shift-left checks that guard it: SL-CLEAN-16.

solvedgap
Incomplete windows inside a ratio
G2S-05
Lines not yet due count as missed, so the ratio looks worse than the truth.OTIF counted every line of the month; over complete windows it is 0.8961.Ratios count complete windows only; counts of rows still cover every row.

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 hop.

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.
One wide table as the only goldA single wide table repeats dimension values, is hard to change, and hides grain.Build a star schema; add a wide view or a semantic-layer view on top when a use case needs it.

FAQ

Questions people ask

What are the hardest problems in the gold to semantic step?

Aggregating ratios wrongly; One number, many definitions; Comparing across facts; Time intelligence; Incomplete windows inside a ratio.

What is the most common mistake at gold to semantic?

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 layer

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