Home › Data engineering › Gold data modelling
Data engineering · guide
Gold data modelling: grain, keys, facts and dimensions done right
Gold is where people, dashboards and AI read their answers. If gold is shaped well, every number agrees everywhere and new questions are easy. If it is shaped badly, totals double count, history breaks and every tool invents its own definition. This page teaches the shape from zero, then gives the rules and the decisions. How gold gets built from silver is the next page: Silver to gold.
- In plain words
- Where Kimball helps, and where not
- How a business ontology helps
- 1. Start with the grain
- 2a. Keys from zero: one dealer, one story
- 2. Keys: business, primary, surrogate
- 3. Join on surrogate key or business key
- 4. SCD Type 2 joins
- 5. Dimensions
- 5a. Kinds of dimension, with examples
- 5b. Bridge tables: why and when
- 6. Facts: the kinds
- 7. What goes in a fact (only IDs?)
- 8. A 'Type 1 fact' or a 'Type 2 fact'?
- 9. How to design a fact properly
- 10. Requirement patterns and their designs
- 11. The bus matrix
- 12. What else lives in gold
- 13. What depends on dashboards
- 14. Materialize or not
- 15. Pre-aggregate or not, and at what grain
- 16. Volume: fast Power BI and dashboards
- 17. Joining and grain problems
- 18. Shared dimensions, domain facts: schemas
- 19. Liquid clustering on what
- 20. Physical layout
- 21. Mistakes
- 22. Worked example
- 23. Decisions
- FAQ
Lesson first
In plain words
Think of a shop's receipt. The receipt line (2 tyres, ₹9,000) is a fact: it happened, and it has numbers. The shop, the tyre type, the day and the customer are dimensions: they describe the fact, and you slice the numbers by them ("sales by region by month"). Put the fact in the middle and the dimensions around it, and you get a star.
A star schema. The fact holds numbers; each dimension holds descriptions. The date dimension appears three times, once for each date on the order line.
Why this shape? Because almost every business question is "a number, sliced by some descriptions, over time". A star answers that with one join per description, it is easy for people and AI tools to read, and the same dimension can serve many facts, so sales and deliveries can be compared on the same dealer list.
| Word | What it means here |
|---|---|
| Gold | The layer people and tools read answers from. Shaped for questions, not for the source system. |
| Dimensional model | Tables arranged as facts (what happened, with numbers) and dimensions (who, what, where, when). Also called a star schema, because one fact with its dimensions looks like a star. |
| Business process | One thing the business does that makes events: taking an order, delivering, curing a tyre. One fact table per process. |
| Grain | What exactly one row of a fact means, in one sentence: "one row per order line". The most important decision in gold. |
| Measure | A number on a fact row that people add up or average: quantity, value, minutes late. |
| Business key | The key the source uses for a thing: dealer code D-0042, material code M-1188. Meaningful to people, can be reused or changed by the source. |
| Primary key | Whatever makes a row unique in its table. In a dimension it is the surrogate key; in a fact it is usually the grain columns. |
| Surrogate key (sk) | A meaningless number the warehouse gives each dimension row. Facts point to dimensions with it, never with the business key. |
| Durable key | One number per real-world member (dealer 42) that stays the same across all its history versions. |
| Conformed dimension | A dimension shared, unchanged, by several facts, so their numbers can be compared side by side. |
| SCD | Slowly changing dimension: the rule for what happens when a dimension value changes (overwrite it, keep history, ...). |
| Degenerate dimension | A business key kept on the fact with no table of its own, such as an order number. |
Method
Where Kimball helps, and where not
Ralph Kimball's dimensional modelling is the method this page follows: start from business processes, declare the grain, build conformed dimensions, plan with a bus matrix. On a lakehouse it still fits gold, because storage got cheaper but people, BI tools and AI still need clear, small, joinable tables. It is not the tool for every layer.
| Need | Why | Verdict |
|---|---|---|
| Shaping data for questions people ask | Business processes, grain, facts and dimensions map straight to how people ask | Pursue: this is Kimball's home ground |
| Many facts that must agree | Conformed dimensions and the bus matrix make sales, deliveries and returns comparable | Pursue |
| History of descriptions | SCD types give a named, tested choice per attribute | Pursue |
| BI tools, semantic layers and AI answers | Stars are what metric views, Power BI and text-to-SQL tools read best: few joins, clear names, one grain per table | Pursue |
| Integrating many messy sources | Kimball assumes clean, conformed input; matching, deduplicating and keeping source history is silver's job (or a data vault) | Pursue with care: not the tool for this layer |
| Sub-second or streaming answers | A star is batch or micro-batch; a live lane needs a store built for it | Drop for the live lane |
| Machine learning features | Models want one wide row per entity per time; build it from the star | Pursue with care: derive, don't replace |
| Exploring unknown data | Modelling first slows people who don't know the questions yet | Drop for exploration: let them read silver |
1. The first decision
Start with the grain
The grain is what one row of a fact means. Say it in one sentence before anything else: one row per order line, one row per press per hour, one row per dealer per material per month. Most broken gold models broke here: two grains mixed in one table, so totals double count.
- Atomic first. Choose the lowest level the source gives. You can always add up later; you can never split a total back into its lines.
- Every dimension must be true at the grain. If "delivery driver" is not known for an order line, it does not belong on the order-line fact.
- The grain is the primary key. order_id plus line_no; checked for uniqueness on every run, not just in tests.
- One grain per table. Order headers and order lines are two facts, or header values are shared out onto lines.
Coarser grains are built by adding up the atomic one. Ratios are the exception: add up their parts, then divide.
2a. Keys from zero
Keys from zero: one picture, one dealer, one story
Keys feel abstract until you see them on real rows. Start with something everyone knows.
A person, their passport numbers, their addresses, and letters sent to them.
Keep that picture. Now the same thing for a dealer, Patel Motors, which our tyre company calls D7. Patel Motors moved from the North region to the West on 12 Nov, and later a new ERP system started calling it DLR-0007.
Business keys come in from systems; the key map turns them into one durable key; each version gets a surrogate key; facts store keys.
| Key | In one line | In the example | Why it exists |
|---|---|---|---|
| Business key | The source's own code | D7 in the ERP; DLR-0007 in the new ERP | Comes from outside. Can change, be reused, or differ between systems. Kept as a column; never the link from facts. |
| Durable key | One number per real thing | dealer_dk 2 = Patel Motors | Given once by the key map and never changes, whatever the source does. Use it for 'today's view' of history. |
| Surrogate key (version key) | One number per dimension row | dealer_sk 102 (North), 103 (West) | With history (SCD Type 2), each version gets its own. Facts store it to remember the version true on their date. |
| Dimension primary key | What makes a dimension row unique | dealer_sk | The surrogate key. Unique, never null, never reused, never renumbered. |
| Fact primary key | What makes a fact row unique | line_id on fact_otif | The grain. Not a dealer key: many fact rows share one dealer. |
| Reserved keys | Special rows in every dimension | -1 unknown, -2 not applicable | So a fact never has an empty key and joins never drop rows. |
The story, run for real
Every table below came out of a run of the code on this page (Spark 3.5.1, 14 Nov 2026).
Step 1: first load. Two dealers arrive. The key map gives each a durable key (1 and 2), and each version a surrogate key (101 and 102). Note the typo "Patel Motor": we fix it in step 2. Two order lines are loaded. The build looks up each line's business key as of its promised date and stores the keys it finds.
| dealer_sk | dealer_dk | business_key | name | region | valid_from | valid_to | is_current |
|---|---|---|---|---|---|---|---|
| 101 | 1 | D1 | Sharma Tyres | North | 1 Jan 2024 | open | True |
| 102 | 2 | D7 | Patel Motor | North | 1 Mar 2024 | open | True |
| line_id | dealer_id (business key) | promised | dealer_sk | dealer_dk |
|---|---|---|---|---|
| L1 | D1 | 10 Nov 2026 | 101 | 1 |
| L3 | D7 | 11 Nov 2026 | 102 | 2 |
Step 2: three things happen on 12 to 13 Nov. The typo is fixed (a Type 1 change). D7 moves to the West (a Type 2 change). A new line L5 arrives for dealer D9, who is not in the dealer list yet.
| dealer_sk | dealer_dk | business_key | name | region | valid_from | valid_to | is_current | inferred |
|---|---|---|---|---|---|---|---|---|
| 101 | 1 | D1 | Sharma Tyres | North | 1 Jan 2024 | open | True | False |
| 102 | 2 | D7 | Patel Motors | North | 1 Mar 2024 | 12 Nov 2026 | False | False |
| 103 | 2 | D7 | Patel Motors | West | 12 Nov 2026 | open | True | False |
| 104 | 3 | D9 | (inferred) D9 | unknown | (from the start) | open | True | True |
- Typo fixed: rows 102 and 103 now say "Patel Motors". The keys did not change.
- Move: row 102 got an end date (12 Nov) and row 103 was added for the West.
- D9: a placeholder row, 104, was made with real keys and "unknown" values. This is called an inferred member.
| line_id | dealer_id | promised | dealer_sk | dealer_dk | dealer name | region on that date |
|---|---|---|---|---|---|---|
| L1 | D1 | 10 Nov 2026 | 101 | 1 | Sharma Tyres | North |
| L3 | D7 | 11 Nov 2026 | 102 | 2 | Patel Motors | North |
| L4 | D7 | 13 Nov 2026 | 103 | 2 | Patel Motors | West |
| L5 | D9 | 13 Nov 2026 | 104 | 3 | (inferred) D9 | unknown |
The old fact rows L1 and L3 were not touched. Compared with step 1, they are identical: yes. L3 still says North, because Patel Motors was in the North on 11 Nov. Like the letter sent to Pune.
Step 3: D9's real details arrive on 15 Nov. Row 104 is filled in, in place. Its key stays 104, so L5 needs no change: 0 fact rows rewritten. With the other approach, the unknown key -1, L5 would have stored -1 and would now have to be rewritten to 104: 1 fact row rewritten.
| when | dealer_sk | name | region | inferred |
|---|---|---|---|---|
| 13 Nov (placeholder) | 104 | (inferred) D9 | unknown | True |
| 15 Nov (real details) | 104 | Kumar Wheels | South | False |
Step 4: a new ERP calls Patel Motors "DLR-0007". The key map simply gets one more line pointing at the same durable key 2. A new fact L7 from the new system on 14 Nov gets dealer_sk 103 and dealer_dk 2: exactly the same as L4 from the old system.
| source | business_key | dealer_dk |
|---|---|---|
| ERP | D1 | 1 |
| ERP | D7 | 2 |
| ERP2 | DLR-0007 | 2 |
| ERP | D9 | 3 |
With hash keys, this would have broken. The hash of ("D7", 12 Nov) is 1202002986356869011, and the hash of ("DLR-0007", 12 Nov) is -2248177551702977334. Same dealer, same version, different key: they do not match. Old and new facts would point at different keys for one dealer.
Step 5: what goes wrong if keys are numbered at build time. Say someone rebuilds the dimension and numbers the rows 101, 102, 103... in order. A new dealer, D5 Gupta Tyres, has arrived in the meantime. It sorts before D7, so every number after it shifts by one. The facts still hold their old numbers:
| fact | stored dealer_sk | was | after a renumbering rebuild, the key now means |
|---|---|---|---|
| L1 | 101 | Sharma Tyres | Sharma Tyres, North |
| L3 | 102 | Patel Motors, North | Gupta Tyres, East |
| L4 | 103 | Patel Motors, West | Patel Motors, North |
| L5 | 104 | Kumar Wheels | Patel Motors, West |
No error, no warning. L3's sale now reports under a different dealer, and L4 and L5 under the wrong versions. That is why a surrogate key is assigned once, stored, and never renumbered.
Four kinds of change. Only renumbering hurts the facts.
So: if a surrogate key changes, does the fact change? Is the fact append-only?
| What happens | In the dimension | Must facts change? | Note |
|---|---|---|---|
| A typo in a name is fixed (Type 1) | Row 102 (and 103) edited in place; keys unchanged | No | All history now shows the right spelling, which is what you want |
| Dealer moves region (Type 2) | Row 102 closed (valid_to 12 Nov); new row 103 added | No | Old facts keep 102 (North, true then); new facts get 103 |
| A fact arrives before its dealer: inferred member | Placeholder row 104 created at fact load; filled in when the dealer arrives | No | Our run: 0 fact rows rewritten |
| A fact arrives before its dealer: unknown key (-1) | Fact stores -1; when the dealer arrives, the fact is re-keyed | Yes, those rows | Our run: 1 fact row rewritten (L5: -1 to 104) |
| Source system renames the code (D7 to DLR-0007) | Key map adds DLR-0007 -> durable 2; keys unchanged | No (key map). Yes (hash keys) | With hash keys every new fact gets a different number than the old facts |
| Two dealers found to be the same (merge) | Key map points both codes at one durable key; old versions stay | No | 'Today's view' joins on the durable key, so both show as one |
| Someone rebuilds the dimension with new numbers | Every key can change | Yes, all of them | Never do this: it is why keys are assigned once and stored |
| The fact's own numbers change (late delivery) | Not a dimension change | Yes, that fact row | Replaced in its window by the harness, logged as a restatement |
As it was, or as it is today: why facts carry two keys
L3 was sold when Patel Motors was in the North. Today Patel Motors is in the West. Which region should L3 count under? Both answers are useful, for different questions:
| line_id | dealer | promised | region as it was (join on dealer_sk) | region today (join dealer_dk to current row) |
|---|---|---|---|---|
| L1 | D1 | 10 Nov 2026 | North | North |
| L3 | D7 | 11 Nov 2026 | North | West |
| L4 | D7 | 13 Nov 2026 | West | West |
| L5 | D9 | 13 Nov 2026 | South | South |
- "How did each region do at the time?" Join on
dealer_sk, the version key stored on the fact. L3 counts under North. - "How do today's regions' dealers compare over the years?" Join the fact's
dealer_dkto the dealer's current row. L3 counts under West.
Two small integer columns on the fact give both views, and nothing ever needs rewriting.
The fact's own primary key
A fact's primary key is what makes one of its rows unique. That is its grain, not a dimension key. Many order lines share dealer 103, so dealer_sk cannot be a fact's primary key.
| Fact | Grain | Primary key | Note |
|---|---|---|---|
| fact_otif (transaction) | one row per order line | line_id | The order line's own id (a degenerate dimension) |
| fact_daily_stock (periodic snapshot) | one row per warehouse, product and day | warehouse_dk + product_dk + snapshot_date | Several columns together |
| fact_line_lifecycle (accumulating) | one row per order line | line_id | Same as its process |
| fact_rep_visit (factless) | one row per rep, dealer and day | rep_dk + dealer_dk + visit_date | The row IS the event |
| fact_otif_region_day (aggregate) | one row per day and region | promised_date + region | The grain it was added up to |
| fact_plan_vs_actual (consolidated) | one row per dealer and month | dealer_dk + month | The shared grain |
Use durable keys (or the business id) in a fact's primary key, not version keys. A version can change while the row stays the same row. Check the primary key is unique on every run, before writing.
How to make surrogate keys
| Way | How | Good | Bad | Verdict |
|---|---|---|---|---|
| Key map (assign once, store) | The first time a version is seen, it gets the next number (102, 103...) and the pair is saved. Later runs look it up. | Stable forever; small integers; survives rebuilds and source renames | Needs a small table and one writer at a time | Pursue: our recommendation |
| Hash of business key + version date | dealer_sk = hash('D7', 12 Nov 2026). No table: compute it anywhere. | No lookup table; same number on every rerun and every engine | Changes if the business key changes (our run: different hash for D7 and DLR-0007); 64-bit numbers; rare collisions to check | Pursue with care: when one stable source owns the code |
| Identity or row number at build | Number the rows each time the dimension is built. | Simple | A rebuild renumbers everything; old facts silently point at other dealers (our run below) | Drop |
| The source's code as the key | Facts store D7 directly. | No work | Breaks with history (which version?), with two systems, and when codes are reused | Drop |
The key map and version keys, as tested (keys.py)
def assign_durable_keys(keymap: DataFrame, incoming: DataFrame) -> DataFrame:
"""Key map: (source, business key) -> durable key. Known codes keep their number; new ones get the next.
incoming may carry 'same_as' = an already known business key (a new code for a known dealer)."""
known = incoming.join(keymap, ["source", "business_key"], "left_anti")
if "same_as" in incoming.columns: # a renamed or second-system code that points at a known dealer
alias = (known.where(F.col("same_as").isNotNull())
.join(keymap.select(F.col("business_key").alias("same_as"), "dealer_dk").dropDuplicates(["same_as"]), "same_as")
.select("source", "business_key", "dealer_dk"))
known = known.where(F.col("same_as").isNull())
else:
alias = keymap.limit(0)
start = keymap.agg(F.coalesce(F.max("dealer_dk"), F.lit(0))).first()[0]
rows = sorted((r.source, r.business_key) for r in known.select("source", "business_key").distinct().collect())
new = keymap.sparkSession.createDataFrame([(s, b, start + i + 1) for i, (s, b) in enumerate(rows)], KEYMAP_S)
return keymap.unionByName(alias).unionByName(new)
def assign_version_keys(skmap: DataFrame, versions: DataFrame, first: int = 101) -> DataFrame:
"""Version (surrogate) keys: (durable key, valid_from) -> dealer_sk, assigned once, never renumbered."""
todo = versions.select("dealer_dk", "valid_from").distinct().join(skmap, ["dealer_dk", "valid_from"], "left_anti")
start = skmap.agg(F.coalesce(F.max("dealer_sk"), F.lit(first - 1))).first()[0]
rows = sorted((r.dealer_dk, r.valid_from) for r in todo.collect())
new = skmap.sparkSession.createDataFrame([(d, v, start + i + 1) for i, (d, v) in enumerate(rows)], SKMAP_S)
return skmap.unionByName(new)The lookup that stores keys on a fact, and the two other ways (keys.py)
def hash_key(df: DataFrame, cols: list) -> DataFrame:
"""The other way: a key computed from the business key and version date. No table, same on every rerun,
but it changes if the business key changes."""
return df.withColumn("hash_sk", F.xxhash64(*cols))
def identity_key(df: DataFrame, first: int = 101) -> DataFrame:
"""The bad way: number the rows at build time. A rebuild renumbers everything."""
w = Window.orderBy("business_key", "valid_from")
return df.withColumn("dealer_sk", F.row_number().over(w) + first - 1)
def lookup_via_keymap(fact: DataFrame, keymap: DataFrame, dim: DataFrame, date_col: str) -> DataFrame:
"""For facts from any source: business key -> durable key (key map), then the version true on the date."""
f = fact.join(keymap.withColumnRenamed("business_key", "dealer_id"), ["source", "dealer_id"], "left")
d = dim.select(F.col("dealer_dk").alias("_dk"), "dealer_sk", "valid_from", "valid_to")
on = (F.col("dealer_dk") == F.col("_dk")) & (F.col(date_col) >= F.col("valid_from")) & (F.col(date_col) < F.col("valid_to"))
return f.join(d, on, "left").drop("_dk", "valid_from", "valid_to").fillna({"dealer_sk": -1, "dealer_dk": -1})All code and the 14 table-driven tests: _guides/code/gold_keys/.
2. Keys
Business key, primary key, surrogate key
Three kinds of key are easy to mix up. The business key is the source's name for a thing (dealer D-0042). The primary key is whatever makes a row unique in its own table. The surrogate key is a meaningless number the warehouse gives each dimension row, and it is the only thing a fact uses to point at a dimension.
The source's code is kept as a column, but facts link through warehouse keys, so history and source changes never break them.
| Key | What it is | Example | Rule |
|---|---|---|---|
| Business key (natural key) | Comes from the source; identifies the thing in the source's world | dealer_code D-0042 | Keep it as a column on the dimension, never as the link from facts |
| Durable key | One per real-world member, assigned once by a key map, never changes | dealer 42 | Store on facts that need today's values (current view) |
| Surrogate (version) key | One per dimension row; with SCD2, one per version | sk 701 (Pune), sk 702 (Delhi) | Store on facts that need the values true on the fact's date (as-of view) |
| Dimension primary key | The surrogate key | dealer_sk | Unique, not null, never reused |
| Fact primary key | The grain columns (often business keys plus dates), or a hash of them | order_id + line_no | Declare it and check it every run; a surrogate row id adds nothing |
| Reserved keys | -1 unknown (the fact had no value), -2 not applicable (the dimension does not apply) | dealer_sk = -1 | One row each in every dimension, so joins never drop facts |
How to make surrogate keys
| Method | Why | Verdict |
|---|---|---|
| Key map table (business key -> durable key, assigned once) | Stable across rebuilds, supports late members, small integers | Pursue |
| Hash of the business key (SHA-256 or 64-bit) | No lookup table, same key on every engine and every rerun | Pursue with care: check for collisions every run at large member counts |
| Identity column (auto-increment) | Simple | Drop for history dimensions: a rebuild renumbers everything and breaks facts |
| Source key as the link from facts | No work | Drop: breaks with history, with two sources, and when the source reuses keys |
The details (key maps, version keys, inferred members for late dimensions) are on the gold harness spec.
3. Joining
Join on the surrogate key or the business key?
Short answer: business keys are for building gold, surrogate keys are for reading it. The build looks up each fact's business key (as of its event time) and stores the surrogate key. Every reader then joins fact to dimension on the surrogate key with a plain equality join.
The most common gold bug: a business-key join against a history dimension multiplies every total.
| Join | On | What happens | Verdict |
|---|---|---|---|
| Fact to dimension | Surrogate key | Exactly one dimension row per fact row, by construction | Pursue |
| Fact to dimension on the business key | Business key | With SCD2, one fact row matches every version: totals multiply (fan-out) | Drop |
| Business key plus is_current | Business key | Gives today's view, but silently drops facts whose member is no longer current or not yet loaded | Pursue with care: prefer the durable key |
| Fact to fact (drill across) | Neither: never join two facts row to row | Different grains multiply rows (chasm trap) | Drop: add each up to the common grain on conformed dimensions, then join the results |
| Silver to gold, while building | Business key, as of the event date | This is the lookup that turns business keys into surrogate keys | Pursue: business keys belong to the build, surrogate keys to the readers |
| Gold to an outside system | Business key | Other systems do not know your surrogate keys | Pursue: always keep the business key as a column on the dimension |
4. History joins
SCD Type 2 joins, done right
| Topic | Rule |
|---|---|
| Where the as-of join happens | Once, in the build (silver to gold): business key plus event time finds the version, the fact stores its surrogate key. Readers then join on that key with a plain equality join. |
| The rule | valid_from <= event_time < valid_to: start included, end excluded. The current row has valid_to = 9999-12-31 (or null treated as open), never a gap between versions. |
| Which time | The business event time in the business time zone (order time), not the load time. A late fact must land on the version valid when it happened. |
| Event before the member's first version | Pick a rule and write it down: the first version (most common) or the unknown member. Never drop the row. |
| Member not loaded yet | Unknown member now, re-keyed when it arrives, or an inferred member with a real key filled in later. Both are on the harness page. |
| Backdated dimension change | A version inserted in the past changes which facts belong to it: re-key only the facts of that member in that date range. |
| Checks every run | One current row per member, no overlapping ranges, no gaps, every fact key exists in the dimension. Broken ranges cause fan-out or lost rows. |
| Performance | Look up only the members in the batch; broadcast small dimensions; use a range-join hint or bin by date for large ones; cluster the dimension by business key. |
Meaning first
How a business ontology helps
An ontology is a written map of the business: its concepts (dealer, material, order line), what identifies each one, how they relate, what words mean, and what can change. A good gold model is that map made into tables. Write the ontology (even a light one: a glossary with keys and relationships) and much of the model follows from it; skip it, and each team's gold invents its own meaning of "customer".
| In the ontology | Becomes in gold | What it buys |
|---|---|---|
| Business concepts (Dealer, Material, Plant, Order line, Delivery) | Dimensions for things, facts for events | Gold names come from the business, not from source tables |
| Identity of a concept (what makes a dealer one dealer) | Business keys and the key map | Two sources that say 'customer' resolve to one member, or are told apart on purpose |
| Relationships (a dealer places order lines; a press cures tyres) | Fact-to-dimension links and the grain | Which dimensions are true at a fact's grain is read off the ontology |
| Concepts shared across departments | Conformed dimensions and the bus matrix | Sales and manufacturing mean the same 'material' |
| Definitions and synonyms (OTIF, on-time delivery, delivery performance) | Metric views: descriptions, synonyms, certified measures | People and AI tools ask in their words and get one definition |
| Rules and constraints (an order line belongs to one order; a delivery cannot precede its order) | Grain checks, quality rules, as-of rules | Checks come from business facts, not from guesses |
| Change over time (a dealer can change region) | SCD type per attribute | History is kept where the business says the concept changes |
Verdict. Pursue a light ontology (a glossary of concepts, keys, relationships and definitions kept in Git, feeding the catalog's glossary and the metric views' descriptions). Pursue with care a full ontology platform: worth it when many domains and AI agents must agree on meaning; not as the first step.
5. Dimensions
Dimensions, and how they change
A dimension is a list of things with their descriptions: one row per dealer, with name, region and segment. When a description changes (a dealer moves region), you must choose what the warehouse remembers. That choice is the SCD type.
| Type | What it keeps | Example | Verdict |
|---|---|---|---|
| Type 0: keep the original | The value never changes after first load | Date of birth, first order date | Pursue where true |
| Type 1: overwrite | Only today's value; history lost | Corrected spellings, contact email | Pursue for attributes nobody reports on over time |
| Type 2: new row per change | Full history with valid_from, valid_to, is_current | Dealer region, material class, price band | Pursue for attributes reports slice history by |
| Type 3: previous value column | Current plus one previous value | Old and new sales territory during a reorganisation | Pursue with care: one step of history only |
| Type 4 / mini-dimension | Fast-changing attributes split into their own small dimension | Customer credit band changing monthly | Pursue when Type 2 would explode the row count |
| Type 6: 1 + 2 + 3 | Version rows plus a current-value column on every version | Report history by the region of the day and also by today's region | Pursue when both views are asked for often |
| Type 2 on every column | History for everything | - | Drop: rows multiply, nobody asks for most of it |
Kinds of dimension
| Kind | What it is | Verdict |
|---|---|---|
| Date dimension | One row per day with calendar and fiscal attributes (week, month, quarter, holiday, working day). Used many times on one fact under different names (order date, promised date, delivered date): a role-playing dimension. | Pursue: every model needs it |
| Conformed dimensions | Dealer, material, plant built once and used by every fact that mentions them. | Pursue: it is what makes facts comparable |
| Degenerate dimension | Order number kept on the fact; no table. | Pursue for document numbers |
| Junk dimension | Small flags and codes (rush order, channel, payment type) combined into one tiny dimension instead of many. | Pursue when a fact has many low-cardinality flags |
| Bridge table | Many-to-many between a fact and a dimension (one order line, several sales reps with shares). | Pursue with care: needs weighting factors |
| Outrigger (dimension pointing at a dimension) | Snowflaking: material -> brand table -> company table. | Drop mostly: flatten into the dimension unless the sub-dimension is huge and shared |
| Unknown and not-applicable members | Rows -1 and -2 in every dimension. | Pursue: no fact is ever dropped by a join |
5a. Kinds of dimension
The kinds of dimension, with examples
A dimension describes the who, what, where and when that numbers are sliced by. Most dimensions are plain lists (dealers, products). The other kinds solve one specific problem each. Learn the problem and the kind becomes obvious.
Twelve kinds. Green ones you will have in every mart; the others solve one problem each.
| Kind | What it is | Tyre example | Another business | Verdict |
|---|---|---|---|---|
| Conformed | One dimension, same keys and values, shared by every fact that needs it | dim_dealer used by orders, deliveries and rep visits | Bank: dim_customer used by transactions and support tickets | Pursue: always for shared things |
| Date (calendar) | One row per day: week, month, fiscal period, holiday, working day | Promised date with 'is_working_day' for OTIF | Retail: festival weeks (Diwali) as a column | Pursue: every mart has one |
| Role-playing | The same dimension used several times on one fact, under different names | Date as order, promised and delivered date | Airline: airport as origin and destination | Pursue: one table, one view per role |
| Changing (SCD types 0 to 6) | How a dimension keeps or drops history | Region Type 2, phone number Type 1 | Hospital: patient address Type 2 | Pursue per column; see the SCD table below |
| Degenerate | An id kept on the fact, with no table of its own | order_id on fact_otif | Invoice number, ticket number | Pursue |
| Junk | A small table of every combination of low-value flags | Rush yes/no, payment type, channel | E-commerce: gift wrap, coupon used, express | Pursue when a fact has several small flags |
| Mini-dimension | Fast-changing attributes split from a big dimension into their own | Dealer credit band, changing monthly | Bank: customer income band and age band | Pursue when Type 2 would multiply rows |
| Outrigger | A dimension that points to another dimension | Dealer to its city and state | Product to its supplier | Pursue with care: usually flatten the columns in |
| Shrunken (rolled up) | A smaller copy at a coarser level, for aggregate facts | dim_month and dim_region for the monthly plan | Retail: store group for weekly targets | Pursue for aggregate and consolidated facts |
| Hierarchy (parent-child) | Members nested at uneven depths | Sales org chart | Chart of accounts; product category tree | Flatten fixed levels into columns; bridge for uneven ones |
| Multi-valued | One fact row belongs to several members | Two reps on one order line | Hospital stay with several diagnoses; joint bank account | Pursue via a bridge table |
| Reference and audit | Small fixed lists; and how a row got there | Units, currencies; run id and source | Country codes; ICD codes | Pursue; audit is often just columns |
Role-playing: one date table, many dates
An order line has three dates. Do not build three date tables. Build one, and give the fact three keys into it, each read through a view named for its role.
One dim_date, three roles.
Junk: a home for small flags
Flags like "rush order" or "payment type" are too small for their own dimension, and too many to clutter every fact row. Put every combination in one small table and give the fact one key.
Three flags, eight combinations, one key on the fact.
Changing dimensions (SCD types)
How each column keeps history is a choice per column. Type 1 (overwrite) and Type 2 (new version) are the two you will use most; the keys story above shows both. The full list of SCD types is in the table just above, under 5. Dimensions.
5b. Bridge tables
Bridge tables: why, and in which scenarios
A fact row normally points at one member of each dimension: one dealer, one product, one date. A bridge is needed in exactly two situations:
- A fact row belongs to several members at once (multi-valued). One order line sold jointly by two reps. One hospital stay with three diagnoses. One account with two holders.
- Members sit in a tree of uneven depth (hierarchy), and people ask "the total under X". An org chart, a chart of accounts, a bill of materials.
Scenario 1: one order line, two sales reps
Order line L3 is worth 40,000 and was sold by Asha and Vik together, 60/40. Line L1 (50,000) was Asha's alone. The true total is 90,000.
Joined straight on, a shared sale is counted once per rep. A bridge shares it out.
| Asha | Vik | total | true total | |
|---|---|---|---|---|
| without a bridge | 90,000 | 40,000 | 130,000 | 90,000 |
| with the bridge | 74,000 | 16,000 | 90,000 | 90,000 |
Without a bridge, the company "sold" 130,000 instead of 90,000, and Asha's commission is paid on money that was half Vik's. With the bridge, the fact stores one group key (G2), and the bridge lists the group's members with weights that add up to 1:
| line_id | rep | weight | revenue | revenue shared |
|---|---|---|---|---|
| L1 | Asha | 1.0 | 50,000 | 50,000 |
| L3 | Asha | 0.6 | 40,000 | 24,000 |
| L3 | Vik | 0.4 | 40,000 | 16,000 |
Not every question needs the weight. "How many lines did each rep work on?" counts lines, with no sharing: Asha 2, Vik 1. Counts may ignore weights; sums must use them.
Scenario 2: the total under a manager
The Head of Sales asks for revenue under each person, at every level. The tree has uneven depth in real companies, so fixed columns (region, area) do not work. A hierarchy bridge holds one row for each pair of a person and anyone below them (including themselves, at depth 0). "Total under X" is then one join and a sum:
One row per (above, below) pair.
| person | revenue under them |
|---|---|
| Head | 90,000 |
| Asha | 74,000 |
| North mgr | 74,000 |
| Vik | 16,000 |
| West mgr | 16,000 |
More scenarios, in other businesses
| Scenario | Kind | Question it answers | Bridge |
|---|---|---|---|
| Several sales reps share an order line | Multi-valued | Commission and sales by rep | Bridge with weights (shares) |
| A hospital stay has several diagnoses | Multi-valued | Cost by diagnosis; stays per diagnosis | Bridge; weights for cost, none for counts |
| A joint bank account has two holders | Multi-valued | Balances by customer | Bridge with ownership share |
| A film has three genres | Multi-valued | Minutes watched by genre | Bridge; equal weights or counts only |
| An employee has several skills | Multi-valued | Headcount by skill | Bridge, no weights: count people |
| Org chart, chart of accounts, category tree of uneven depth | Hierarchy | Total under any manager or account | Hierarchy bridge (ancestor, descendant, depth) |
| A tyre is built from parts that are built from parts | Hierarchy | Cost of all parts under a product | Hierarchy bridge (bill of materials) |
When you do NOT need a bridge
| Situation | Do this instead | Verdict |
|---|---|---|
| Each fact row has exactly one member | A normal key on the fact. This is most dimensions. | No bridge |
| Several members, but one is 'main' | Keep the main one as a normal key (primary diagnosis, lead rep) | No bridge, if only the main one is reported |
| At most two or three, always the same roles | Separate keys: rep_1_key, rep_2_key | No bridge; simpler |
| A hierarchy with fixed levels | Flatten into columns: region, area, territory | No bridge |
Rules for bridges
| Rule | Why |
|---|---|
| Weights add up to 1 per group | Checked every run; a group off by any amount stops the build (tested) |
| Sums use the weight | revenue x weight, then add up: totals stay right |
| Counts may ignore the weight | 'How many lines did Vik work on?' counts distinct lines, never sums revenue |
| Groups are reused | The same set of reps with the same shares is one group, used by many fact rows |
| A bridge can have history | If shares change over time, give the bridge valid_from and valid_to, like an SCD2 dimension |
| Hide it from report users | Expose 'revenue by rep (shared)' in the metric view; most tools double count if users join the bridge themselves |
The bridge functions, as tested (bridge.py)
def naive_join(fact: DataFrame, credits: DataFrame) -> DataFrame:
"""The mistake: join every rep straight onto the fact. One line becomes two rows; revenue doubles."""
return fact.join(credits, "line_id")
def weights_ok(bridge: DataFrame, group: str) -> DataFrame:
"""Groups whose weights do not add up to 1 (checked every run)."""
return bridge.groupBy(group).agg(F.round(F.sum("weight"), 6).alias("total")).where("total != 1")
def allocate(fact: DataFrame, bridge: DataFrame, group: str, measure: str) -> DataFrame:
"""Share the measure out by weight: totals stay right."""
return (fact.join(bridge, group)
.withColumn(f"{measure}_allocated", F.col(measure) * F.col("weight")))
def rollup_hierarchy(fact: DataFrame, h_bridge: DataFrame, member: str, measure: str) -> DataFrame:
"""Hierarchy bridge: one row per (ancestor, descendant). Sum everything under each person in one join."""
return (fact.join(h_bridge, fact[member] == h_bridge["descendant"])
.groupBy("ancestor").agg(F.sum(measure).alias(f"{measure}_under")))6. Facts
The kinds of fact table
Facts differ by how their rows behave over time. Pick the kind from the business process, not from the source table.
Transaction facts collect events, periodic snapshots take a picture every period, accumulating snapshots follow one thing through its milestones.
| Kind | One row is | Example | How rows change | When |
|---|---|---|---|---|
| Transaction fact | One event | One row per order line, payment, sensor reading | Appended; corrected only by a declared correction rule | Most common; start here |
| Periodic snapshot | The state of a thing at the end of each period | Stock per material per day; account balance per month | Rebuilt for the open period; closed periods frozen | For levels and balances |
| Accumulating snapshot | One thing's whole life, with a date per milestone | One row per order line with ordered, promised, shipped, delivered dates and lags | Updated in place as milestones arrive | For processes with a start and an end |
| Factless fact | An event or coverage with no number | Dealer attended a training; product on promotion in a store on a day | Appended | For 'what did not happen' questions |
| Aggregate fact | A coarser grain of another fact | Dealer x material x day sales | Rebuilt for changed days only | Only when the atomic fact is too slow for a named question |
| Consolidated fact | Two processes at the same grain side by side | Plan vs actual per dealer per month | Rebuilt per period | Only on conformed dimensions |
Measures: what adds up
| Kind | Meaning | Examples | Rule |
|---|---|---|---|
| Additive | Adds up across every dimension | Quantity, net value, scrap kilograms | Store as is |
| Semi-additive | Adds up across some dimensions but not time | Stock on hand, account balance (add across materials, not across days) | Store; use last value or average over time |
| Non-additive | Never adds up | OTIF %, scrap rate, unit price, averages | Do not store the ratio as the measure: store numerator and denominator; the metric view divides |
7. Inside a fact
What goes in a fact: only IDs?
Mostly keys and numbers, but not only IDs. A fact holds the keys that place a row (who, what, when), the business keys that name the event itself, the numbers, and a few columns that let the platform prove where the row came from. Descriptions never.
| Column kind | Example | In the fact? |
|---|---|---|
| Surrogate keys to each dimension | dealer_sk, material_sk, order_date_key, promised_date_key | Yes: the links, small integers, never null (-1, -2) |
| Durable keys, where today's view is needed | dealer_dk | When reports need both as-it-was and as-it-is |
| Degenerate dimensions | order_id, line_no, invoice_no | Yes: business keys with no table of their own; also the grain |
| Measures | qty_ordered, qty_delivered, net_value_inr, on_time_in_full (0/1) | Yes: the reason the fact exists |
| Event timestamps at their natural precision | order_ts, delivered_ts | Yes, when lags or hours are asked; the date key covers day-level slicing |
| Lag and duration measures (accumulating snapshots) | days_order_to_delivery | Yes: computed once in the build, not in every report |
| Small flags | is_rush, is_cancelled | A few are fine; many go into a junk dimension |
| Audit columns | _run_id, _is_complete, _source_batch | Yes: lineage, completeness, reruns |
| Descriptive text | dealer_name, material_description, region | No: lives in dimensions; copied text means rewriting facts on every rename |
| Business keys of dimensions | dealer_code | No as a link; at most kept for traceability on very large facts, never joined on |
8. A common confusion
Is a fact "Type 1" or "Type 2"?
SCD types belong to dimensions, not facts. When people say "a Type 2 fact" they usually mean one of two different things: which dimension key the fact stores (the version true at the time, or today's), or how the fact's own rows change when the source corrects them (overwritten, versioned, or reversed). Separate the two questions, and each requirement maps to a clear design:
| Requirement | Example ask | Design | Note |
|---|---|---|---|
| Show values as they were on the fact's date | Sales by the dealer's region at the time of sale | Fact stores the dimension's version key (as-of lookup) | Often called a 'Type 2 fact', but the history lives in the dimension |
| Show today's values for all history | All past sales under today's regions after a reorganisation | Fact stores the durable key; join to the current version | Often called a 'Type 1 fact' |
| Both views | Finance wants as-it-was, sales wants as-it-is | Store both keys on the fact; the metric view exposes both | Cheap: two integer columns |
| The fact's own numbers get corrected, latest is enough | A delivery quantity fixed next day | Upsert by grain key: the row is overwritten | The fact behaves like Type 1 |
| The fact's corrections must be auditable | Prove what the report showed last month | Versioned fact: new row per change with valid_from and valid_to on the grain key | The fact behaves like Type 2; expensive, use only when required |
| Corrections must never change a posted row | Ledgers, invoices | Reversal rows: a minus row and a plus row | Totals stay additive; history is the rows themselves |
| Show what the report said on a past day | Audit of a published month | Time travel or snapshots of the gold table, or a versioned fact | Time travel retention is short; keep a snapshot if the period matters |
9. Designing a fact
How to design a fact properly: ten steps
- Name the business process and its owner (taking orders, sales team).
- Write the grain in one sentence, at the lowest level the source gives (one row per order line). Atomic grain first; aggregates come later.
- List the dimensions that are true at that grain (date, dealer, material, plant). A dimension that is not true for one row means the grain is wrong.
- List the measures that are true at that grain, and mark each additive, semi-additive or non-additive.
- Pick the fact kind (transaction, periodic snapshot, accumulating snapshot) from how rows change over time.
- Decide per dimension: as-of (version key) or current (durable key), or both.
- Declare the primary key (the grain columns) and check it is unique on every run.
- Declare how late data and corrections arrive: lookback window, correction rule, what happens when a dimension member is missing.
- Declare the window column (order date, promised date, cure hour) the harness rebuilds by, and when a window is complete.
- Write the contract: columns, types, owner, quality rules, freshness target. Only then write the build.
10. Requirements to design
Requirement patterns, and the design for each
Most gold asks fall into a few patterns. Recognise the pattern in the requirement, and the fact kind, keys and load follow.
| Pattern | The ask | Design | Load |
|---|---|---|---|
| Count and sum events | How many tyres did we sell per region per month? | Transaction fact at event grain; additive measures | Incremental by event-date window |
| Level at a point in time | Stock on hand at month end | Periodic snapshot; semi-additive measure (last value over time) | Rebuild the open period; freeze closed ones |
| Durations and lags of a process | Days from order to delivery; how many are late | Accumulating snapshot with one date per milestone and lag measures | Upsert by grain key as milestones arrive |
| Things that did not happen | Dealers with no orders this month; products not sold on promotion | Factless coverage fact, compared with the event fact | Rebuild per period |
| Plan against actual | Sales target vs actual per dealer per month | Two facts at different grains, drilled across conformed dimensions at the common grain (dealer x month) | Each fact loads alone; compare in the metric view |
| History as it was | Revenue by the region a dealer had at the time | Version key on the fact (as-of lookup); SCD Type 2 dimension | As-of lookup on event date |
| Restate by today's structure | Last year's revenue under this year's regions | Durable key on the fact; current view of the dimension | No fact change when regions change |
| Late data that changes closed numbers | A dealer's file arrives five days late | Window rebuild with a completeness flag; restatement logged | Lookback window per fact |
| Many-to-many | An order line credited to two sales reps | Bridge table with weights that add to 1 | Rebuild the bridge with the dimension |
| Ratios and percentages | OTIF %, scrap rate | Store numerator and denominator; define the ratio once in the metric view | Same as the fact |
11. Planning many facts
The bus matrix
The bus matrix is one table that lists the business processes (rows) against the conformed dimensions (columns). It is the plan for the whole gold layer: it shows which dimensions must be shared, so they are built once. Here it is for a tyre maker.
| Process (fact) | Date | Dealer | Material | Plant | Fact kind |
|---|---|---|---|---|---|
| Order lines (OTIF) | ✓ order, promised, delivered | ✓ | ✓ | - | Transaction (better: accumulating snapshot) |
| Sales (sell-in and sell-out) | ✓ month | ✓ | ✓ | - | Periodic snapshot (monthly) |
| Scrap | ✓ cure hour | - | ✓ | ✓ (via press) | Periodic snapshot (hourly) |
| Deliveries (proposed) | ✓ shipped, delivered | ✓ | ✓ | ✓ | Transaction |
A column with many ticks is a dimension to build first and build well. A fact with a tick under a dimension that does not exist yet (plant, date in the example above) is a gap.
12. Beyond facts and dimensions
What else lives in gold
Gold tables hold the truth; metric views define the numbers; materialized views keep popular answers ready.
| Object | What it is | Use it when | Who refreshes it | Verdict |
|---|---|---|---|---|
| Atomic fact and dimension tables | The star itself | Every model | The harness, incremental by window | Pursue: the source of truth for every number |
| Aggregate table (a gold table at a coarser grain) | Rows already added up, kept by the harness | A named question is slow or costly on the atomic fact, and the aggregate is reused | The harness, changed windows only | Pursue with care: one more table to keep in step |
| Materialized view | A query whose result is stored and refreshed by the platform | The same aggregate is read many times; the logic is a plain SQL query over gold | The platform, on a schedule or on change (a managed pipeline) | Pursue for read-heavy aggregates |
| Metric view (semantic layer) | One definition per measure and dimension; tools ask it questions | Always, for any number shown to people or AI | Nothing: it is a definition, computed at query time | Pursue: one number everywhere |
| Metric view with materialization | The metric view keeps pre-aggregated results it uses when a query fits them | A metric view is slow at the grain dashboards ask most | The platform (a managed pipeline per view) | Pursue with care: refresh uses compute even when nobody reads |
| Plain view | A saved query, nothing stored | Renames, filters, row security | Nothing | Pursue for thin layers only |
| Wide 'one big table' for a tool | Fact joined to all dimensions, flattened | A tool that cannot join, or a ML feature table | The harness | Pursue with care: build it from the star, never instead of it |
On Databricks we checked on Free Edition (13 Nov 2026) that both CREATE MATERIALIZED VIEW and metric-view materialization work; each creates a managed pipeline that refreshes on its schedule and uses compute even when nobody reads. Metric views in full, including materialization: Metric views, taught from zero. Power BI's own aggregations and storage modes: Power BI vs Databricks AI/BI.
13. Requirements that come and go
What depends on dashboard requirements, and what does not
The core of gold follows the business, not the screens. Dashboards change every quarter; if the core changed with them, every number would move. Only the speed layer (aggregates, materialized views, wide tables) is shaped by dashboards.
| Part of gold | Shaped by dashboards? | Why |
|---|---|---|
| Atomic grain of each fact | No | Set by the business process and the source; dashboards come and go |
| Conformed dimensions and keys | No | Shared by every fact and every tool |
| SCD type per attribute | Partly | Set by what the business must report over time, not by one chart; ask the owner, not the dashboard |
| Measures and their definitions | No | Defined once in the metric view; dashboards read them |
| Data quality rules, freshness targets, contracts | No | Promises of the data product |
| Aggregate tables and materialized views | Yes | Exist only to make named dashboard or AI questions fast or cheap |
| Pre-aggregation grain | Yes | Chosen from the grains the top dashboards actually read |
| Refresh cadence of aggregates | Yes | Matches how fresh the dashboard must be, never fresher than its inputs |
| Wide flattened tables for a tool | Yes | Only for a tool that cannot join |
| Which dimensions a metric view exposes | Partly | Business vocabulary first; dashboards may add filters |
14. Storing answers
When to materialize, and when not
| Situation | Decision |
|---|---|
| The same aggregate is read many times a day, and the query is slow or costly | Materialize (materialized view or aggregate table) |
| Few reads, or reads spread over many different slices | Do not: let the metric view compute it at query time |
| The atomic fact is small (tens of millions of rows) and clustered well | Do not: a warehouse answers in seconds anyway |
| Freshness must be minutes, and the base changes all the time | Do not, or use incremental refresh only: a full refresh each time costs more than the reads save |
| The aggregate needs completeness flags and the same checks as the fact | Aggregate table in the harness, not a materialized view |
| Plain SQL over gold, refresh on a schedule is fine | Materialized view (the platform refreshes it) |
| A metric view is slow at the grain dashboards ask most | Metric-view materialization at that grain |
| Nobody can say which question it speeds up | Do not |
15. Pre-aggregation
Pre-aggregate or not, and at what grain
Pre-aggregation trades storage and refresh compute for faster, cheaper reads. It pays only when the same coarse answer is read many times. When not: the atomic fact is small or well clustered, reads are spread over many slices, or nobody can name the question it speeds up. The rules:
| Rule | What it means |
|---|---|
| Look at what is asked | Take the top dashboards and AI questions; write down the grain each one reads (dealer x month, region x week). Query history tells you. |
| Keep the atomic fact | Never replace it: drill-down, reconciliation and new questions need it. |
| Aggregate one step up first | dealer x material x day usually serves most tiles and cuts rows 10 to 100 times. Go coarser only for a named slow question. |
| Only additive measures | Pre-aggregate sums and counts. For ratios, pre-aggregate the numerator and denominator; divide in the metric view. |
| Same dimensions, same keys | An aggregate uses the conformed dimensions (or their rolled-up versions, such as month instead of date), so it joins like the atomic fact. |
| Refresh by changed window | Recompute only the days or months the atomic fact changed; never the whole table. |
| Prove it pays | Compare query time and compute cost before and after. An aggregate nobody reads is pure cost: drop it. |
Which grain
| What is asked | Pre-aggregate at | Leave on the atomic fact |
|---|---|---|
| Dashboards show monthly trends by region | dealer x material x month, or region x month if the region never changes | Daily stays on the atomic fact for drill-down |
| Daily operational tiles | plant x material x day | Hourly drill-down reads the atomic fact |
| Executive summary | region x month, a handful of rows | Usually a materialized view is enough |
| AI answers over many slices | Usually none: the metric view over a well-clustered fact | Pre-aggregate only the slices query history shows are slow |
16. Volume
Handling volume so Power BI and dashboards stay fast
Speed comes from layers working together: a clustered star, aggregates at the grains people read, one semantic layer, and a BI model that asks the warehouse for little. No single trick fixes a slow dashboard built on a wide, unclustered table.
| Practice | What to do | Where |
|---|---|---|
| Keep the star; integer keys | Columnar engines and Power BI's engine compress integer keys and low-cardinality columns very well; wide text on facts kills compression. | Gold |
| Cluster facts by date and the most filtered key | Queries skip most files; liquid clustering on Delta, sort order on Iceberg; compact regularly. | Gold |
| Aggregates at the grains people read | Most dashboard tiles read a few coarse grains; serve them from aggregates or materialized views, keep the atomic fact for drill-down. | Gold |
| Metric views for definitions | Every tool asks the same definitions; the warehouse pushes filters and grouping down to clustered tables. | Semantic |
| Power BI: Import for small and medium models | Import the dimensions and aggregated facts into the Power BI model; fastest visuals; schedule incremental refresh by date partitions (RangeStart and RangeEnd). | BI |
| Power BI: DirectQuery for very large or very fresh facts | Queries go to the warehouse each time; keep visuals few and filtered; never on unclustered, unaggregated tables. | BI |
| Power BI: composite model with aggregations | Aggregated table imported, atomic fact in DirectQuery; Power BI answers from the aggregate when it can and goes to the detail only on drill-down. | BI |
| Power BI model hygiene | One-to-many relationships from dimension to fact, single direction; a marked date table; remove unused columns; no calculated columns on big facts. | BI |
| Warehouse settings | Serverless SQL warehouse with Photon, result cache on, sized for concurrency (scale-out clusters) not for single-query size. | Platform |
| Row security in one place | Row filters in the catalog or in the BI model, not both, so caches stay useful. | Platform |
| Measure before and after | Query history: slowest and most frequent queries, bytes read; fix the top ten first. | Operate |
Power BI storage modes, Direct Lake and row security in depth: Power BI and Fabric, in depth.
17. When numbers are wrong
Joining and grain problems
Almost every "the dashboard number is wrong" ticket is one of these. Each has a symptom you can see and a cause you can design away.
| Problem | Symptom | Cause | Fix |
|---|---|---|---|
| Fan-out | Totals too big after a join | One fact row matched several dimension rows (business-key join on SCD2, or a dimension that is not unique on its key) | Join on the surrogate key; check every dimension is unique on it every run |
| Chasm trap | Orders and deliveries both joined to dealer: totals multiply | Two facts joined through a shared dimension in one query | Aggregate each fact to the shared grain first, then join (drill across) |
| Fan trap | Header amount repeated on every line, summed | A measure at a coarser grain carried onto a finer grain | Keep header measures on a header fact, or allocate them to lines |
| Rows silently missing | Totals too small after a join | Inner join to a dimension where the member is missing or late | Unknown member -1 in every dimension; facts never have null keys; use the key, not an inner join on the business key |
| Many-to-many double count | Commission paid twice | Order line credited to two reps with no weights | Bridge table with weighting factors |
| Mixed grain | Day and month rows in one table; totals double | Two grains in one fact | One grain per fact; aggregates in their own tables |
| Grain mismatch across facts | Plan (monthly) vs actual (daily) compared day by day | Facts at different grains joined at the finer one | Compare at the coarser grain only |
| Semi-additive summed over time | Stock 'total' for the month is thirty times too big | Daily stock added across days | Use last value or average over time; the metric view encodes it |
| Ratio averaged | Average of dealers' OTIF % differs from company OTIF % | Averaging ratios with different denominators | Add up numerator and denominator, then divide |
| Time-zone day boundary | Day totals differ between report and source | Grain day cut in UTC instead of the business time zone | Cut business days in the business zone, declared in the contract |
18. Where things live
Shared dimensions, domain facts: the schema layout
Organise gold by business area, with one shared home for the conformed dimensions. Ownership follows the schema: whoever owns a schema writes to it, everyone else reads. In our tyre example: dev_gold.shared for date, dealer, material and plant (once they move there; today dealer sits with sales and material with manufacturing), dev_gold.sales for order lines and sales, dev_gold.manufacturing for scrap.
| What | Lives in | Owner and rule | Verdict |
|---|---|---|---|
| Conformed dimensions (date, dealer, material, plant) | <env>_gold.shared | Owned by the team that masters the data (master data, or the platform team for date); every domain reads, nobody else writes | Pursue |
| Facts of one business area | <env>_gold.<domain>: sales, manufacturing, finance | Owned by the domain team; their metric views and aggregates sit beside them | Pursue |
| Dimensions only one domain uses | With that domain's facts | Move to shared the day a second domain needs them | Pursue |
| Metric views | Next to the facts they read, in the domain schema | Certified views read by dashboards and AI; previews marked | Pursue |
| Aggregate tables and materialized views | Domain schema, named agg_<process>_<grain> | Owned with the fact they come from | Pursue |
| Cross-domain facts (plan vs actual across finance and sales) | The domain that owns the decision | Reads the other domain's facts only through conformed dimensions | Pursue with care |
| Copies of a shared dimension inside each domain | - | Drift: two dealer lists that disagree | Drop |
| One schema for all of gold | - | No ownership, no access boundaries | Drop beyond a small start |
19. Clustering
Liquid clustering on what?
Cluster on the columns queries and the harness filter by, in order of how often. For gold that is almost always the window column first, then the most used key.
| Table | Cluster on | Why |
|---|---|---|
| Transaction fact | The window column first (order or promised date), then the key most filtered on (dealer_sk) | Window replaces touch few files; date-range dashboards skip most files |
| Periodic snapshot | The period (month) and the main key | Same reason; snapshots are read by period |
| Accumulating snapshot | The grain key's date (order date) and the main key | Upserts by grain find their rows fast |
| SCD2 dimension | Business key (and is_current) | The build looks up by business key and event time |
| Small dimension (under a few million rows) | Nothing | It is broadcast; clustering adds upkeep for no gain |
| Aggregate table | Its date and its first dimension | Same as the fact it came from |
| What not to cluster on | A unique id (order_id) unless people look rows up by it; more than about four columns; columns nobody filters | High-cardinality ids spread every query; too many keys dilute all of them |
| Let the platform choose | Automatic clustering where available (Unity Catalog managed tables can pick keys from query history) | Pursue once query patterns are stable; check what it chose |
| Iceberg note | On Unity Catalog managed Iceberg we found clustering needs deletion vectors and row tracking turned off when the table is created (checked 13 Nov 2026) | Set at create; changing later needs a rebuild |
20. Physical layout
Layout, names and types
| Topic | Rule | Verdict |
|---|---|---|
| Clustering | Cluster facts by the window column and the most filtered key (promised_date, dealer_sk); dimensions by business key and is_current. | Pursue: liquid clustering on Delta; sort order on Iceberg |
| Partitioning | Partition only very large facts, and only by a coarse date (month). Small partitions make small files. | Pursue with care |
| File sizes | Aim for files of hundreds of megabytes; compact after many small window replaces. | Pursue: scheduled upkeep |
| Naming | fact_<process>, dim_<thing>, agg_<process>_<grain>, columns in plain words with units (qty_ordered, value_inr). | Pursue |
| Comments and contract | Every table and column described; the grain sentence in the table comment. | Pursue: AI tools read these |
| Nulls | No null foreign keys (use -1 or -2); nulls in measures only when truly unknown. | Pursue |
| Data types | Exact decimals for money; dates as dates; flags as booleans or plain-word codes. | Pursue |
Table chores (compaction, clean-up of old files) are on who maintains your tables.
21. Mistakes
Mistakes we see, and the fix
| Mistake | What goes wrong | Fix |
|---|---|---|
| Mixed grain in one fact | Order headers and order lines in one table; totals double count. | One fact per grain; header values allocated to lines or kept as a separate fact |
| Ratios stored as measures | Someone averages OTIF % across dealers and gets a wrong number. | Store numerator and denominator |
| Business key as the fact's link | History breaks when the dealer moves region; two sources clash. | Surrogate keys from a key map |
| Type 2 on everything | Dimension rows explode; nobody reports on most of the history. | Type 2 only on attributes reports slice by |
| Snowflaked dimensions | Five joins to get a brand name; slow and confusing for tools and AI. | Flatten into one dimension |
| No date dimension | Every report re-derives weeks, fiscal months and holidays differently. | One shared date dimension, used in roles |
| One big table instead of a star | Every change to a dimension rewrites billions of fact rows. | Keep the star; build wide tables from it when a tool needs them |
| Aggregates first | The atomic detail is never stored; a new question cannot be answered. | Atomic fact first, aggregates only for named questions |
| Facts reading facts in chains | Backfills need a careful order; one error spreads. | Build from silver or from one atomic fact, never three deep |
| Descriptive text on facts | Dealer names copied onto every fact row; renames need a fact rewrite. | Text lives in dimensions |
22. Worked example
A tyre maker's gold, as we built it
Our reference platform builds this gold every day from a simulated tyre company (orders, deliveries, dealer files, presses, lab results) and checks every number against the simulated world's own truth. On Databricks Free Edition on 13 Nov 2026, a 3-day run with eleven planted problems (late files, re-sent files, deleted orders, a dealer that appears late, a backdated correction) gave gold equal to the truth on every day. The last column is what we would change, following this page.
| Table | Grain | Kind and refresh | Dimension lookups | What we would change |
|---|---|---|---|---|
| fact_otif | One row per order line | Transaction, rebuilt by promised-date window (35-day lookback) | Dealer and material as of the order time; unknown member while a new dealer is missing | Better as an accumulating snapshot with order, promised, shipped and delivered dates |
| fact_sales | One row per dealer, material and month (sell-in and sell-out) | Periodic snapshot, monthly; a month is complete when every dealer that owes a file has sent it | Dealer and material as of the month | Late files restate the month; re-sent files change nothing |
| fact_scrap | One row per press, material and cure hour | Periodic snapshot, hourly (48-hour lookback) | Press and material as of the cure hour | Plant needs its own dimension (time zone) |
| dim_dealer, dim_material, dim_press | One row per version (SCD Type 2) | Built through one shared pipeline, no code per dimension | - | A date dimension and a plant dimension are missing |
23. Decisions
Decisions with verdicts
| Decision | Choice | Verdict |
|---|---|---|
| Modelling style for gold | Dimensional (star) with conformed dimensions | Pursue |
| Grain | Atomic first, declared in one sentence, checked every run | Pursue |
| Keys | Surrogate keys from a key map; facts store version and/or durable keys; -1 and -2 reserved | Pursue |
| History | SCD Type 2 only on attributes reports slice history by; Type 1 elsewhere | Pursue |
| Date dimension | One, role-playing | Pursue |
| Ratios | Numerator and denominator stored, divided in the metric view | Pursue |
| Semantic layer | Metric views over the star, certified measures only for dashboards and AI | Pursue |
| Pre-aggregation | Only for named slow questions, one step up first, refreshed by changed window | Pursue with care |
| Data vault in gold | Hubs, links and satellites as the reporting layer | Drop: a fine integration layer, a poor reading layer |
| One big table instead of the star | - | Drop |
FAQ
Questions people ask
What is the right way to model the gold layer?
Model gold dimensionally: one fact table per business process at a declared atomic grain, conformed dimensions with surrogate keys shared by every fact, SCD Type 2 only where reports need history, ratios stored as numerator and denominator, and a semantic layer (metric views) on top so every tool reads one definition per measure.
What is the difference between a business key, a primary key and a surrogate key?
A business key comes from the source (dealer code D-0042). A primary key is whatever makes a row unique in its table. A surrogate key is a meaningless number the warehouse assigns to each dimension row; it is the dimension's primary key, and facts link to dimensions with it, never with the business key.
What are the kinds of fact table?
Transaction facts (one row per event), periodic snapshots (one row per thing per period), accumulating snapshots (one row per thing updated at each milestone), factless facts (events with no number), aggregate facts (a coarser grain of another fact) and consolidated facts (two processes at the same grain).
How do I define the grain of a fact table?
Write one sentence that says what one row means, at the lowest level the source provides, such as one row per order line. Every dimension and measure must be true at that grain, and the grain columns become the fact's primary key, checked for uniqueness on every run.
At what level should I pre-aggregate?
Keep the atomic fact, then pre-aggregate one step up (for example dealer by material by day) only for named slow questions, using additive measures only (numerator and denominator for ratios), refreshed by changed window, and drop any aggregate nobody reads.
Should I use materialized views or aggregate tables in gold?
Use a materialized view when the aggregate is a plain SQL query over gold that is read many times and can be refreshed by the platform; use an aggregate table built by your own harness when it needs the same windows, completeness flags and checks as the atomic fact.
If a surrogate key changes, do I have to update the fact table?
A surrogate key should never change: it is assigned once per dimension version and stored. When a dimension changes, a Type 1 fix edits the row and a Type 2 change adds a new row with a new key; existing facts keep the key they stored, so no fact is rewritten. Facts are rewritten only for their own reasons: late data, corrections, or re-keying rows stored with the unknown key.
What is the difference between a business key, a durable key and a surrogate key?
The business key is the source's own code (D7) and can change or differ between systems. The durable key is one number per real thing, given once by a key map, that never changes. The surrogate key is one number per dimension row, so with SCD Type 2 each version gets its own. Facts store the surrogate key for 'as it was' and can also store the durable key for 'as it is today'.
What is the primary key of a fact table?
Its grain: the columns that make one row unique, such as the order line id, or warehouse plus product plus date for a daily stock snapshot. It is not a dimension's surrogate key, because many fact rows share one dimension member.
How do you generate surrogate keys in a data lakehouse?
Keep a key map: the first time a version is seen it gets the next integer, the pair is stored, and later runs look it up. Hashing the business key with the version date needs no table but changes when the business key changes. Never number rows at build time: a rebuild renumbers them and old facts point at the wrong members.
What are the types of dimensions?
Conformed, date, role-playing, changing (SCD types), degenerate, junk, mini-dimension, outrigger, shrunken, hierarchy, multi-valued, and reference or audit dimensions. Most marts use conformed, date and role-playing ones; the others each solve one specific problem.
When do you need a bridge table?
When one fact row belongs to several members of a dimension, such as an order line sold by two reps or a hospital stay with several diagnoses, and when members sit in an uneven hierarchy such as an org chart. The bridge lists each group's members with weights that add up to 1, so totals stay right.
Next
Where it connects
Next: Silver to gold, the harness that builds this →
Gold harness spec · Metric views · SCD Type 2 on Databricks · Medallion architecture · Hop 3: silver to gold · All data engineering