Home › Data engineering › Gold data modelling › Silver to gold

Data engineering · guide

Silver to gold: the harness that builds facts and dimensions

Gold data modelling said what to build: facts and dimensions. This is how to build them from silver every night, without losing, doubling or silently changing a number, and without the cost growing with history. It is split into short pages, so you can read one at a time.

In one paragraph. One harness builds every gold table the same way. It reads only what changed in silver since its bookmark, works out which windows of the fact (days, months, hours) those changes touch, rebuilds just those windows with a small pure build per fact, looks up keys as of each event, checks everything before writing, replaces the windows, logs any number that changed after it was published, and moves the bookmark last. Because the build is pure, it is tested on a laptop with small tables and with a whole synthetic company that plants problems on purpose.

Start here

Which page do I read?

If your head is spinning, read one page and stop: the worked example. It follows one tiny tyre company through two nights, and every idea shows up as something that happens to real rows.

If you think in SQL, go to the SQL examples: eleven dimensions and eight facts, each as silver in, SQL, gold out.

If you are building or reviewing, use the three rulebook pages. They are reference, not a story.

Learn it

Start with the worked example. Everything else is easier after it.

The worked example

One small company, two nights, every idea shown on real rows. Start here.

Part A · 11 min read

When it goes wrong, and the tests

A crash halfway, a careless join, and the tests behind every build.

Part A · 7 min read

The six kinds of fact

Why there are six, with a bank and nine businesses, and how to pick.

Part B · 11 min read

Building each kind of fact

Reader, processor, writer for each fact kind, with the tested code.

Part B · 8 min read

Who makes the bookmark?

Can incremental loading miss a change? Tested seven ways.

Part A8 · 8 min read

Examples in SQL

Dimensions and facts as plain SQL on tiny tables, every result from a real run.

Fact examples in SQL

Eight facts in SQL: transaction, snapshots, factless, aggregate, plan against actual, allocation, adjustments.

19 min read

Dimension examples in SQL

Eleven dimensions in SQL: Type 1, 2, 3, date, junk, bridge, hierarchy, late dealers.

19 min read

Decide

Declarative pipeline or harness, SQL or dbt, fact or dimension.

Databricks declarative pipeline?

AUTO CDC and materialized views: what you gain, what you give up.

Part C · 9 min read

The fact in SQL and dbt

The OTIF fact in SQL and in dbt, and our harness against dbt.

Part D · 10 min read

Fact or dimension?

A delivery, and what to do when facts have versions.

Part E · 4 min read

The rulebook

For when you are building or reviewing. Three short reference pages.

Rulebook 1: the harness

The steps of a run, one harness for all, seeing inside it.

8 min read

Rulebook 2: loading rules

Incremental loads, watermarks, windows, completeness, late data.

8 min read

Rulebook 3: testing and decisions

Testing, reliability, cost, what exists, decisions and the FAQ.

13 min read

Words you will meet

WordWhat it means here
SilverClean copies of the source tables, still shaped like the source (an orders table, a deliveries table).
GoldTables shaped for questions: facts and dimensions. What dashboards, metric views and AI agents read.
FactA table of things that happened or were measured, with numbers (tyres ordered, tyres delivered).
DimensionA table that describes the who, what and where of a fact (the dealer, its name, its region).
GrainWhat one row of a fact means, in one sentence. Here: one row per order line.
Surrogate keyA number gold gives each dimension row (dealer_key 2), instead of the source's own id. It lets one dealer have several versions.
SCD Type 2Keeping history in a dimension: when a dealer moves region, the old row is closed and a new row opens. Both stay.
WindowA slice of the fact rebuilt as one piece. Here: one day of promised date.
Watermark (bookmark)The last change number this table has already used. The next run starts after it.
High markThe newest change number when a run starts. The run reads up to it and no further.
LookbackHow old a window may be and still be rebuilt automatically (35 days here). Older fixes need a correction plan.
CompletenessA window is complete when every feed that owes data for it has sent it.
RestatementA number that was already published and has now changed. Logged with old value, new value and reason.
IdempotentRunning it twice gives the same result as running it once.
Pure functionCode that only turns inputs into outputs: it reads no tables, writes nothing and never asks the time.
HarnessThe shared code that runs every gold table the same way. Only the business rule is written per table.

Related

Where it connects

← Gold data modelling · Gold harness spec · SCD Type 2 on Databricks · Metric views · Table maintenance · Hop 3: silver to gold · All data engineering