Home › Learn › Hard problems by layer › Gold

Hard problems by layer · 7 of 10

Gold: hard problems and anti-patterns

The hard challenges and common anti-patterns of gold in a data platform, with how each is solved and tested. This layer must model the business correctly (grain, fact type, shared dimensions, history) and publish only what passed its checks.

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

Where we are

Gold

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. Model the business correctly (grain, fact type, shared dimensions, history) and publish only what passed its checks.

Handled by: gold harness, metadata store, data expectation services.

The hard part

7 challenges, and how they are solved

2 solved4 assisted1 people decideStatus: 4 building, 3 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
Undeclared grain
GLD-01
Nobody can say what one row means, so numbers cannot be added up safely.Is OTIF per order line or per delivery?Grain declared per fact and tested unique; a grain is suggested from key profiling and confirmed by the modeller.

assurance rules DES-04.

Shift-left checks that guard it: SL-CLEAN-31, SL-CLEAN-32.

assistedbuilding
The wrong fact type
GLD-02
Snapshots summed over time, or an order's current stage impossible to answer.Sales are transactions; inventory is a periodic snapshot; order to delivery is an accumulating snapshot.Fact type declared per fact; the harness builds the matching pattern.

assurance rules DES-04.

Shift-left checks that guard it: SL-CLEAN-31, SL-CLEAN-32.

assisteddesigned
Dimensions that do not conform
GLD-03
Two dashboards disagree on the same region.One plant dimension shared by OTIF and scrap.Shared dimensions declared once; the bus matrix (which facts share which dimensions) generated from the declarations.

assurance rules SERV-04.

assisteddesigned
History per attribute
GLD-04
Sales move to a new region retroactively, or history that matters is lost.Dealer region keeps history; phone does not.History type declared per attribute; joins as of the event.

assurance rules SCD-04.

assistedbuilding
Roles, bridges and facts about absence
GLD-05
Order date and delivery date confused; a dealer in two groups counted twice; nothing counts what did not happen.One date dimension used as order, promised and delivery date.Role-playing dimensions, bridge tables with weights and factless facts declared and generated; which absences matter is a business decision.

assurance rules DES-04.

Shift-left checks that guard it: SL-CLEAN-31, SL-CLEAN-32.

people decidedesigned
Keys that change on every rebuild
GLD-06
Identity or sequence keys differ after a rerun, breaking every join and saved report.Rebuilding gold after a correction.Deterministic hash surrogate keys with reserved members for unknown and not applicable.

assurance rules KEY-01; decisions ADR-004 Deterministic hash surrogate keys with reserved members -1 and -2.

solvedbuilding
Publishing unchecked numbers
GLD-07
A broken build reaches dashboards before anyone looks.A gold window with orphan rows above the limit.Checks before publish (keys, totals against the build, orphan rate); a failure publishes nothing.

assurance rules PUB-01.

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
Undeclared grainNobody can tell a duplicate from a valid row, and measures get summed twice.Declare the grain in metadata and check uniqueness before publish.
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.
Identity or sequence surrogate keysKeys change on every rebuild and differ between environments, breaking joins and comparisons.Hash of source + business key (+ valid_from for SCD type 2).
Quality checks only in silverJoins and aggregates create their own errors (orphans, double counts) that silver checks can never see.Check grain, orphan rate, reconciliation and completeness in gold too.
Materialized views for everythingMulti-input materialized views give no control over windows, completeness or quality, and can cost more to refresh.Use a table written by the window recompute job.
Dropping business keys from factsWithout them, re-keying orphans and tracing a row back to the source are impossible.Keep both the business key and the surrogate key.

FAQ

Questions people ask

What are the hardest problems in the gold layer?

Undeclared grain; The wrong fact type; Dimensions that do not conform; History per attribute; Roles, bridges and facts about absence.

What is the most common mistake at gold?

Undeclared grain. Declare the grain in metadata and check uniqueness before publish.

Next

Where to go next

Next: Gold to semantic

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