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.
Where we are
Gold to semantic
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
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.
| Challenge | What goes wrong | Tyre-world example | How we solve it | Verdict | Status |
|---|---|---|---|---|---|
| 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. | solved | designed |
| 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. | solved | building |
| 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. | solved | designed |
| 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. | solved | gap |
| 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. | solved | building |
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-pattern | Why it hurts | Do this instead |
|---|---|---|
| Business logic in BI tools or views | The 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 gold | A 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