Home › Learn › Hard problems by layer

Data engineering · free guide · 10 layers and hops · 69 challenges

Hard problems by layer, and how to solve them

Every layer of a data platform, from the source system to the screen, has its own hard problems and its own common mistakes. This guide lists them layer by layer, with a worked example from a synthetic tyre company and how each one is solved and tested.

The picture

Ten steps from source to screen

Data moves through layers, places where it rests (bronze keeps what arrived, silver cleans one source, gold combines sources into facts, the semantic layer defines the numbers people use), and hops, the steps that move it from one layer to the next (drawn dashed). Each box shows the first few challenges and how many are solved, assisted or left to people.

the data flow, from the source system to the screen1Source systems4 solved · 3 assisted · 0 decide· No contract with the source· Deletes are not signalled2Ingestion6 solved · 1 assisted · 0 decide· Push and pull behave differently· Reruns that double the data3Bronze5 solved · 0 assisted · 0 decide· Changing data on the way in· Rows that cannot be traced4Bronze to silver8 solved · 0 assisted · 0 decide· A different pipeline per table· Changes arriving out of order5Silver4 solved · 1 assisted · 0 decide· Conforming too early· The wrong kind of history6Silver to gold12 solved · 1 assisted · 0 decide· Rows dropped in joins· Conforming keys across sourceshardest7Gold2 solved · 4 assisted · 1 decide· Undeclared grain· The wrong fact type8Gold to semantic5 solved · 0 assisted · 0 decide· Aggregating ratios wrongly· One number, many definitionshardest9Semantic layer3 solved · 1 assisted · 1 decide· Definitions nobody agreed· Definitions that change silently10Semantic to UI6 solved · 0 assisted · 1 decide· Dashboards re-querying raw tables· A conversation interface roaming raw…
Read 1 to 5 left to right, then 6 to 10 right to left. The two boxes with a thick border are the hardest steps.

Why there

The hardest problems sit at silver to gold and gold to semantic

Before silver, the work is making one source faithful: duplicates, resent files, schema changes, deletes, changes that arrive out of order. That is hard but mechanical, so one engine with the same steps for every source handles it. From silver to gold, several sources meet for the first time, and they have to agree on keys, grain, time and meaning. From gold to semantic, numbers reach people, dashboards, conversational tools and agents, and one definition has to hold everywhere. Those steps need business judgment, not only code.

Bronze to silver: make one source faithfulone sourcethe same stepsfor every sourceduplicates, bad files, schema changes,deletes, out-of-order changeshard, but mechanicalSilver to gold: make many sources agreeordersdeliveriesdealersone fact, one numberkeys, grain, time, meaningdropped rows, keys, grain, history,completeness, late data, definitionsneeds judgment about meaning and timing
One source faithful is mechanical. Many sources agreeing needs judgment about meaning and timing.

The approach

Declare the business decisions; the engine builds them; the tests prove them

Tools such as dbt or a semantic layer give you a place to write the answer, but they do not know the answer. The answer is a set of business decisions: what one row of a fact means, which history applies, how long to wait for a late file, what a metric counts. Write those decisions down once, as data. The engine then builds the joins, the windows and the metric definitions from them, and the tests check the results against known answers. Of the 69 challenges here, 55 are solved this way, 11 are assisted and 3 stay with people.

Four rules behind most of it

RuleWhat it means
Keep event time; never trust arrival orderEvery record has two times: when it happened and when it reached us. Joins, windows and history use when it happened.
Never drop; mark itA row with no match gets an unknown member, a late row gets a flag, a partial window gets a provisional flag. Dropping hides the problem; marking makes it visible and fixable.
Make every output recomputable per windowWrites replace a window instead of appending to it, so a late file or a correction simply means rerunning that window.
Write the business policy down, per datasetHow long to wait for late data, whether to publish provisional numbers, when a period closes, what a metric means. These are decisions, so they are declared, versioned and tested.

The layers

One page per layer and hop

FAQ

Questions people ask

What are the hardest problems in a data platform?

Making several sources agree in gold (keys, grain, history, completeness, late data, corrections) and making one number hold everywhere in the semantic layer (definitions, additivity, versions, provisional numbers). Earlier layers are hard but mechanical.

Can a framework solve these problems?

Most of them, once the business decision is declared. Of the 69 listed here, 55 are solved by the engine once declared, 11 are assisted and 3 stay with people.

Related

Where it connects

Medallion architecture explains the layers themselves; Shift left shows the code checks that guard many of these fixes; DataTwin, explained shows how the tyre world tests them.