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.
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.
When it goes wrong, and the tests
A crash halfway, a careless join, and the tests behind every build.
The six kinds of fact
Why there are six, with a bank and nine businesses, and how to pick.
Building each kind of fact
Reader, processor, writer for each fact kind, with the tested code.
Who makes the bookmark?
Can incremental loading miss a change? Tested seven ways.
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.
Dimension examples in SQL
Eleven dimensions in SQL: Type 1, 2, 3, date, junk, bridge, hierarchy, late dealers.
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.
The fact in SQL and dbt
The OTIF fact in SQL and in dbt, and our harness against dbt.
The rulebook
For when you are building or reviewing. Three short reference pages.
Rulebook 3: testing and decisions
Testing, reliability, cost, what exists, decisions and the FAQ.
Words you will meet
| Word | What it means here |
|---|---|
| Silver | Clean copies of the source tables, still shaped like the source (an orders table, a deliveries table). |
| Gold | Tables shaped for questions: facts and dimensions. What dashboards, metric views and AI agents read. |
| Fact | A table of things that happened or were measured, with numbers (tyres ordered, tyres delivered). |
| Dimension | A table that describes the who, what and where of a fact (the dealer, its name, its region). |
| Grain | What one row of a fact means, in one sentence. Here: one row per order line. |
| Surrogate key | A 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 2 | Keeping history in a dimension: when a dealer moves region, the old row is closed and a new row opens. Both stay. |
| Window | A 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 mark | The newest change number when a run starts. The run reads up to it and no further. |
| Lookback | How old a window may be and still be rebuilt automatically (35 days here). Older fixes need a correction plan. |
| Completeness | A window is complete when every feed that owes data for it has sent it. |
| Restatement | A number that was already published and has now changed. Logged with old value, new value and reason. |
| Idempotent | Running it twice gives the same result as running it once. |
| Pure function | Code that only turns inputs into outputs: it reads no tables, writes nothing and never asks the time. |
| Harness | The 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