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 one paragraph. One fact table per business process, at a grain you can say in one sentence. Dimensions around it describe who, what, where and when, and are shared by every fact (conformed). Facts link to dimensions with surrogate keys, never with the source's own keys. History is kept (SCD Type 2) only where reports need it. Ratios are stored as their two parts. A semantic layer (metric views) on top defines each measure once. Aggregates and materialized views are added later, only for questions that are slow, at one step above the atomic grain.

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.

fact: order lineqty ordered, qty delivered,on time in full (0 or 1)date(order, promised,delivered)dealername, region,segmentmaterialtyre class, size,brandplantcountry, timezonethe fact in the middle holds numbers; each arm points to one dimension by its surrogate key

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.

WordWhat it means here
GoldThe layer people and tools read answers from. Shaped for questions, not for the source system.
Dimensional modelTables 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 processOne thing the business does that makes events: taking an order, delivering, curing a tyre. One fact table per process.
GrainWhat exactly one row of a fact means, in one sentence: "one row per order line". The most important decision in gold.
MeasureA number on a fact row that people add up or average: quantity, value, minutes late.
Business keyThe 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 keyWhatever 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 keyOne number per real-world member (dealer 42) that stays the same across all its history versions.
Conformed dimensionA dimension shared, unchanged, by several facts, so their numbers can be compared side by side.
SCDSlowly changing dimension: the rule for what happens when a dimension value changes (overwrite it, keep history, ...).
Degenerate dimensionA 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.

NeedWhyVerdict
Shaping data for questions people askBusiness processes, grain, facts and dimensions map straight to how people askPursue: this is Kimball's home ground
Many facts that must agreeConformed dimensions and the bus matrix make sales, deliveries and returns comparablePursue
History of descriptionsSCD types give a named, tested choice per attributePursue
BI tools, semantic layers and AI answersStars are what metric views, Power BI and text-to-SQL tools read best: few joins, clear names, one grain per tablePursue
Integrating many messy sourcesKimball 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 answersA star is batch or micro-batch; a live lane needs a store built for itDrop for the live lane
Machine learning featuresModels want one wide row per entity per time; build it from the starPursue with care: derive, don't replace
Exploring unknown dataModelling first slows people who don't know the questions yetDrop 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.
order lineatomic graindealer x material x dayfirst aggregateadd updealer x monthdashboard tilesadd upregion x monthexecutive viewadd upratios (OTIF %, scrap rate) never add up: store the numerator and denominator,add those, divide at the end

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.

Asha, the person(one durable key)passport P123, later P987(business keys: issued by others,can change)address 1: Pune, 2019 to 2024address 2: Delhi, 2024 to nowletter, 2023addressed to Puneletter, 2025addressed to DelhiWhen Asha moves, nobody rewrites the 2023 letter. It was sent to Pune, and that stays true.Letters are facts. Each address is one dimension version (one surrogate key). Asha is the durable key. Passports are business keys.

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.

1. business keys2. key map3. durable key4. version (surrogate) keysERP: D7new ERP: DLR-0007key mapboth -> 2dealer_dk 2Patel Motorsdealer_sk 102: Northuntil 12 Novdealer_sk 103: Westfrom 12 Novfact L3, promised 11 Novdealer_sk 102, dealer_dk 2fact L4, promised 13 Novdealer_sk 103, dealer_dk 2fact L7, from new ERP, 14 Novdealer_sk 103, dealer_dk 2each fact row's own key (its primary key) is its grain: line_id. Not a dealer key at all.

Business keys come in from systems; the key map turns them into one durable key; each version gets a surrogate key; facts store keys.

KeyIn one lineIn the exampleWhy it exists
Business keyThe source's own codeD7 in the ERP; DLR-0007 in the new ERPComes from outside. Can change, be reused, or differ between systems. Kept as a column; never the link from facts.
Durable keyOne number per real thingdealer_dk 2 = Patel MotorsGiven 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 rowdealer_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 keyWhat makes a dimension row uniquedealer_skThe surrogate key. Unique, never null, never reused, never renumbered.
Fact primary keyWhat makes a fact row uniqueline_id on fact_otifThe grain. Not a dealer key: many fact rows share one dealer.
Reserved keysSpecial rows in every dimension-1 unknown, -2 not applicableSo 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_skdealer_dkbusiness_keynameregionvalid_fromvalid_tois_current
1011D1Sharma TyresNorth1 Jan 2024openTrue
1022D7Patel MotorNorth1 Mar 2024openTrue
line_iddealer_id (business key)promiseddealer_skdealer_dk
L1D110 Nov 20261011
L3D711 Nov 20261022

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_skdealer_dkbusiness_keynameregionvalid_fromvalid_tois_currentinferred
1011D1Sharma TyresNorth1 Jan 2024openTrueFalse
1022D7Patel MotorsNorth1 Mar 202412 Nov 2026FalseFalse
1032D7Patel MotorsWest12 Nov 2026openTrueFalse
1043D9(inferred) D9unknown(from the start)openTrueTrue
  • 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_iddealer_idpromiseddealer_skdealer_dkdealer nameregion on that date
L1D110 Nov 20261011Sharma TyresNorth
L3D711 Nov 20261022Patel MotorsNorth
L4D713 Nov 20261032Patel MotorsWest
L5D913 Nov 20261043(inferred) D9unknown

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.

whendealer_sknameregioninferred
13 Nov (placeholder)104(inferred) D9unknownTrue
15 Nov (real details)104Kumar WheelsSouthFalse

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.

sourcebusiness_keydealer_dk
ERPD11
ERPD72
ERP2DLR-00072
ERPD93

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:

factstored dealer_skwasafter a renumbering rebuild, the key now means
L1101Sharma TyresSharma Tyres, North
L3102Patel Motors, NorthGupta Tyres, East
L4103Patel Motors, WestPatel Motors, North
L5104Kumar WheelsPatel 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.

1. Fix a typo (Type 1)dimension:row 102 edited in placefact:L3 -> 102: unchanged2. Dealer moves (Type 2)dimension:new row 103 addedfact:L3 -> 102: unchanged; L4 -> 1033. Late dealer (inferred)dimension:row 104 filled in laterfact:L5 -> 104: unchanged4. Rebuild renumbers keysdimension:102 now means Gupta Tyresfact:L3 -> 102: now WRONGFacts store keys once. A dimension change never needs a fact change, as long as keys are 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?

The short answer. A surrogate key is a promise: once given to a version, it never changes. So when a dimension changes, facts do not change. A Type 1 fix edits the dimension row, a Type 2 change adds a new row, and an inferred member is filled in, all without touching a fact. The fact's keys are written once, when the fact row is loaded. Facts are rewritten only for their own reasons: late data or a correction to their own numbers (the window is replaced), or re-keying rows that were stored with the unknown key -1.
What happensIn the dimensionMust facts change?Note
A typo in a name is fixed (Type 1)Row 102 (and 103) edited in place; keys unchangedNoAll 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 addedNoOld facts keep 102 (North, true then); new facts get 103
A fact arrives before its dealer: inferred memberPlaceholder row 104 created at fact load; filled in when the dealer arrivesNoOur 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-keyedYes, those rowsOur 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 unchangedNo (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 stayNo'Today's view' joins on the durable key, so both show as one
Someone rebuilds the dimension with new numbersEvery key can changeYes, all of themNever do this: it is why keys are assigned once and stored
The fact's own numbers change (late delivery)Not a dimension changeYes, that fact rowReplaced 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_iddealerpromisedregion as it was (join on dealer_sk)region today (join dealer_dk to current row)
L1D110 Nov 2026NorthNorth
L3D711 Nov 2026NorthWest
L4D713 Nov 2026WestWest
L5D913 Nov 2026SouthSouth
  • "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_dk to 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.

FactGrainPrimary keyNote
fact_otif (transaction)one row per order lineline_idThe order line's own id (a degenerate dimension)
fact_daily_stock (periodic snapshot)one row per warehouse, product and daywarehouse_dk + product_dk + snapshot_dateSeveral columns together
fact_line_lifecycle (accumulating)one row per order lineline_idSame as its process
fact_rep_visit (factless)one row per rep, dealer and dayrep_dk + dealer_dk + visit_dateThe row IS the event
fact_otif_region_day (aggregate)one row per day and regionpromised_date + regionThe grain it was added up to
fact_plan_vs_actual (consolidated)one row per dealer and monthdealer_dk + monthThe 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

WayHowGoodBadVerdict
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 renamesNeeds a small table and one writer at a timePursue: our recommendation
Hash of business key + version datedealer_sk = hash('D7', 12 Nov 2026). No table: compute it anywhere.No lookup table; same number on every rerun and every engineChanges if the business key changes (our run: different hash for D7 and DLR-0007); 64-bit numbers; rare collisions to checkPursue with care: when one stable source owns the code
Identity or row number at buildNumber the rows each time the dimension is built.SimpleA rebuild renumbers everything; old facts silently point at other dealers (our run below)Drop
The source's code as the keyFacts store D7 directly.No workBreaks with history (which version?), with two systems, and when codes are reusedDrop
What our build does today: our framework uses hash keys (a SHA-256 of the source dataset, the business key and, for history, the version start; ADR-004), with -1 and -2 reserved, and has no durable key yet. That works while each dealer has one stable code in one source. It breaks in the step 4 case: a new system with new codes, or the same dealer in two sources, gives new keys. Moving to a key map with a durable key is proposed, not decided.
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.

source rowdealer_code D-0042key mapD-0042 -> durable 42version 1: Punesk 701, 2024 to Mar 2026version 2: Delhisk 702, Mar 2026 to nowfact row storessk 701 or 702 (as of)and/or 42 (today)business key comes from the source; durable key never changes; version (surrogate) key changes with history

The source's code is kept as a column, but facts link through warehouse keys, so history and source changes never break them.

KeyWhat it isExampleRule
Business key (natural key)Comes from the source; identifies the thing in the source's worlddealer_code D-0042Keep it as a column on the dimension, never as the link from facts
Durable keyOne per real-world member, assigned once by a key map, never changesdealer 42Store on facts that need today's values (current view)
Surrogate (version) keyOne per dimension row; with SCD2, one per versionsk 701 (Pune), sk 702 (Delhi)Store on facts that need the values true on the fact's date (as-of view)
Dimension primary keyThe surrogate keydealer_skUnique, not null, never reused
Fact primary keyThe grain columns (often business keys plus dates), or a hash of themorder_id + line_noDeclare 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 = -1One row each in every dimension, so joins never drop facts

How to make surrogate keys

MethodWhyVerdict
Key map table (business key -> durable key, assigned once)Stable across rebuilds, supports late members, small integersPursue
Hash of the business key (SHA-256 or 64-bit)No lookup table, same key on every engine and every rerunPursue with care: check for collisions every run at large member counts
Identity column (auto-increment)SimpleDrop for history dimensions: a rebuild renumbers everything and breaks facts
Source key as the link from factsNo workDrop: 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.

fact: 1 order lineqty 10, dealer D-0042D-0042 version 1 (Pune)D-0042 version 2 (Delhi)D-0042 version 3 (Goa)join ondealer_code3 rows, qty 30the total is three times too bigjoin on the surrogate key instead: exactly one dimension row per fact row, by construction

The most common gold bug: a business-key join against a history dimension multiplies every total.

JoinOnWhat happensVerdict
Fact to dimensionSurrogate keyExactly one dimension row per fact row, by constructionPursue
Fact to dimension on the business keyBusiness keyWith SCD2, one fact row matches every version: totals multiply (fan-out)Drop
Business key plus is_currentBusiness keyGives today's view, but silently drops facts whose member is no longer current or not yet loadedPursue with care: prefer the durable key
Fact to fact (drill across)Neither: never join two facts row to rowDifferent grains multiply rows (chasm trap)Drop: add each up to the common grain on conformed dimensions, then join the results
Silver to gold, while buildingBusiness key, as of the event dateThis is the lookup that turns business keys into surrogate keysPursue: business keys belong to the build, surrogate keys to the readers
Gold to an outside systemBusiness keyOther systems do not know your surrogate keysPursue: always keep the business key as a column on the dimension

4. History joins

SCD Type 2 joins, done right

TopicRule
Where the as-of join happensOnce, 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 rulevalid_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 timeThe 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 versionPick a rule and write it down: the first version (most common) or the unknown member. Never drop the row.
Member not loaded yetUnknown 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 changeA 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 runOne current row per member, no overlapping ranges, no gaps, every fact key exists in the dimension. Broken ranges cause fan-out or lost rows.
PerformanceLook 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 ontologyBecomes in goldWhat it buys
Business concepts (Dealer, Material, Plant, Order line, Delivery)Dimensions for things, facts for eventsGold names come from the business, not from source tables
Identity of a concept (what makes a dealer one dealer)Business keys and the key mapTwo 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 grainWhich dimensions are true at a fact's grain is read off the ontology
Concepts shared across departmentsConformed dimensions and the bus matrixSales and manufacturing mean the same 'material'
Definitions and synonyms (OTIF, on-time delivery, delivery performance)Metric views: descriptions, synonyms, certified measuresPeople 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 rulesChecks come from business facts, not from guesses
Change over time (a dealer can change region)SCD type per attributeHistory 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.

TypeWhat it keepsExampleVerdict
Type 0: keep the originalThe value never changes after first loadDate of birth, first order datePursue where true
Type 1: overwriteOnly today's value; history lostCorrected spellings, contact emailPursue for attributes nobody reports on over time
Type 2: new row per changeFull history with valid_from, valid_to, is_currentDealer region, material class, price bandPursue for attributes reports slice history by
Type 3: previous value columnCurrent plus one previous valueOld and new sales territory during a reorganisationPursue with care: one step of history only
Type 4 / mini-dimensionFast-changing attributes split into their own small dimensionCustomer credit band changing monthlyPursue when Type 2 would explode the row count
Type 6: 1 + 2 + 3Version rows plus a current-value column on every versionReport history by the region of the day and also by today's regionPursue when both views are asked for often
Type 2 on every columnHistory for everything-Drop: rows multiply, nobody asks for most of it

Kinds of dimension

KindWhat it isVerdict
Date dimensionOne 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 dimensionsDealer, material, plant built once and used by every fact that mentions them.Pursue: it is what makes facts comparable
Degenerate dimensionOrder number kept on the fact; no table.Pursue for document numbers
Junk dimensionSmall 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 tableMany-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 membersRows -1 and -2 in every dimension.Pursue: no fact is ever dropped by a join
As of then, or as of today? With Type 2, a fact can show the dealer's region on the order date (store the version key) or today's region (store the durable key). Ask which the reports need; many need both, and storing both keys costs little. See SCD Type 2: AUTO CDC or own PySpark and time travel as SCD2.

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.

Conformedone dealer list for every factDatea row per day, with months, holidaysRole-playingdate used as order, promised, deliveredChanging (SCD)keeps history, or notDegenerateorder_id on the fact, no tableJunka bag of small flags in one tableMini-dimensionfast-changing bands split offOutriggera dimension pointing to anotherShrunkenmonth, region: a coarser copyHierarchyorg chart, category treeMulti-valuedseveral members per fact rowReference and auditunits, currencies; run ids

Twelve kinds. Green ones you will have in every mart; the others solve one problem each.

KindWhat it isTyre exampleAnother businessVerdict
ConformedOne dimension, same keys and values, shared by every fact that needs itdim_dealer used by orders, deliveries and rep visitsBank: dim_customer used by transactions and support ticketsPursue: always for shared things
Date (calendar)One row per day: week, month, fiscal period, holiday, working dayPromised date with 'is_working_day' for OTIFRetail: festival weeks (Diwali) as a columnPursue: every mart has one
Role-playingThe same dimension used several times on one fact, under different namesDate as order, promised and delivered dateAirline: airport as origin and destinationPursue: one table, one view per role
Changing (SCD types 0 to 6)How a dimension keeps or drops historyRegion Type 2, phone number Type 1Hospital: patient address Type 2Pursue per column; see the SCD table below
DegenerateAn id kept on the fact, with no table of its ownorder_id on fact_otifInvoice number, ticket numberPursue
JunkA small table of every combination of low-value flagsRush yes/no, payment type, channelE-commerce: gift wrap, coupon used, expressPursue when a fact has several small flags
Mini-dimensionFast-changing attributes split from a big dimension into their ownDealer credit band, changing monthlyBank: customer income band and age bandPursue when Type 2 would multiply rows
OutriggerA dimension that points to another dimensionDealer to its city and stateProduct to its supplierPursue with care: usually flatten the columns in
Shrunken (rolled up)A smaller copy at a coarser level, for aggregate factsdim_month and dim_region for the monthly planRetail: store group for weekly targetsPursue for aggregate and consolidated facts
Hierarchy (parent-child)Members nested at uneven depthsSales org chartChart of accounts; product category treeFlatten fixed levels into columns; bridge for uneven ones
Multi-valuedOne fact row belongs to several membersTwo reps on one order lineHospital stay with several diagnoses; joint bank accountPursue via a bridge table
Reference and auditSmall fixed lists; and how a row got thereUnits, currencies; run id and sourceCountry codes; ICD codesPursue; 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.

fact_otiforder_date_keypromised_date_keydelivered_date_keyview: order_dateview: promised_dateview: delivered_datedim_dateone table, one row per day,read by all three viewsone date table, three roles: each role is a view with its own column names

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.

fact without a junk dimensionis_rush Y / Npayment cash / creditchannel dealer / online4 flag columns on every rowdim_order_profile (junk)1: N, cash, dealer2: N, cash, online3: N, credit, dealer...8: Y, credit, onlinefact keeps ONE column:order_profile_key = 3

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:

  1. 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.
  2. 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.

without a bridge: join reps straight onL3revenue 40,000Asha: 40,000Vik: 40,000one sale of 40,000 now counts as 80,000with a bridge and weightsL3rep_group G2bridge G2Asha 0.6Vik 0.4Asha 24,000Vik 16,000= 40,000weights add to 1, so totals stay right

Joined straight on, a shared sale is counted once per rep. A bridge shares it out.

AshaViktotaltrue total
without a bridge90,00040,000130,00090,000
with the bridge74,00016,00090,00090,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_idrepweightrevenuerevenue shared
L1Asha1.050,00050,000
L3Asha0.640,00024,000
L3Vik0.440,00016,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:

Head of SalesNorth managerWest managerAshaVikbridge rows forthe Head:Head-Head 0Head-managers 1Head-Asha 2Head-Vik 2one row per (person above, person below): 'sum everything under X' becomes one join

One row per (above, below) pair.

personrevenue under them
Head90,000
Asha74,000
North mgr74,000
Vik16,000
West mgr16,000

More scenarios, in other businesses

ScenarioKindQuestion it answersBridge
Several sales reps share an order lineMulti-valuedCommission and sales by repBridge with weights (shares)
A hospital stay has several diagnosesMulti-valuedCost by diagnosis; stays per diagnosisBridge; weights for cost, none for counts
A joint bank account has two holdersMulti-valuedBalances by customerBridge with ownership share
A film has three genresMulti-valuedMinutes watched by genreBridge; equal weights or counts only
An employee has several skillsMulti-valuedHeadcount by skillBridge, no weights: count people
Org chart, chart of accounts, category tree of uneven depthHierarchyTotal under any manager or accountHierarchy bridge (ancestor, descendant, depth)
A tyre is built from parts that are built from partsHierarchyCost of all parts under a productHierarchy bridge (bill of materials)

When you do NOT need a bridge

SituationDo this insteadVerdict
Each fact row has exactly one memberA 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 rolesSeparate keys: rep_1_key, rep_2_keyNo bridge; simpler
A hierarchy with fixed levelsFlatten into columns: region, area, territoryNo bridge

Rules for bridges

RuleWhy
Weights add up to 1 per groupChecked every run; a group off by any amount stops the build (tested)
Sums use the weightrevenue 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 reusedThe same set of reps with the same shares is one group, used by many fact rows
A bridge can have historyIf shares change over time, give the bridge valid_from and valid_to, like an SCD2 dimension
Hide it from report usersExpose '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.

transactionperiodic snapshotaccumulating snapshotone row per event, never changedmonth endmonth endmonth endone row per thing per period, same shape every periodorderedpromisedshippeddeliveredone row per thing, updated as it reaches each milestone

Transaction facts collect events, periodic snapshots take a picture every period, accumulating snapshots follow one thing through its milestones.

KindOne row isExampleHow rows changeWhen
Transaction factOne eventOne row per order line, payment, sensor readingAppended; corrected only by a declared correction ruleMost common; start here
Periodic snapshotThe state of a thing at the end of each periodStock per material per day; account balance per monthRebuilt for the open period; closed periods frozenFor levels and balances
Accumulating snapshotOne thing's whole life, with a date per milestoneOne row per order line with ordered, promised, shipped, delivered dates and lagsUpdated in place as milestones arriveFor processes with a start and an end
Factless factAn event or coverage with no numberDealer attended a training; product on promotion in a store on a dayAppendedFor 'what did not happen' questions
Aggregate factA coarser grain of another factDealer x material x day salesRebuilt for changed days onlyOnly when the atomic fact is too slow for a named question
Consolidated factTwo processes at the same grain side by sidePlan vs actual per dealer per monthRebuilt per periodOnly on conformed dimensions

Measures: what adds up

KindMeaningExamplesRule
AdditiveAdds up across every dimensionQuantity, net value, scrap kilogramsStore as is
Semi-additiveAdds up across some dimensions but not timeStock on hand, account balance (add across materials, not across days)Store; use last value or average over time
Non-additiveNever adds upOTIF %, scrap rate, unit price, averagesDo 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 kindExampleIn the fact?
Surrogate keys to each dimensiondealer_sk, material_sk, order_date_key, promised_date_keyYes: the links, small integers, never null (-1, -2)
Durable keys, where today's view is neededdealer_dkWhen reports need both as-it-was and as-it-is
Degenerate dimensionsorder_id, line_no, invoice_noYes: business keys with no table of their own; also the grain
Measuresqty_ordered, qty_delivered, net_value_inr, on_time_in_full (0/1)Yes: the reason the fact exists
Event timestamps at their natural precisionorder_ts, delivered_tsYes, when lags or hours are asked; the date key covers day-level slicing
Lag and duration measures (accumulating snapshots)days_order_to_deliveryYes: computed once in the build, not in every report
Small flagsis_rush, is_cancelledA few are fine; many go into a junk dimension
Audit columns_run_id, _is_complete, _source_batchYes: lineage, completeness, reruns
Descriptive textdealer_name, material_description, regionNo: lives in dimensions; copied text means rewriting facts on every rename
Business keys of dimensionsdealer_codeNo 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:

RequirementExample askDesignNote
Show values as they were on the fact's dateSales by the dealer's region at the time of saleFact 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 historyAll past sales under today's regions after a reorganisationFact stores the durable key; join to the current versionOften called a 'Type 1 fact'
Both viewsFinance wants as-it-was, sales wants as-it-isStore both keys on the fact; the metric view exposes bothCheap: two integer columns
The fact's own numbers get corrected, latest is enoughA delivery quantity fixed next dayUpsert by grain key: the row is overwrittenThe fact behaves like Type 1
The fact's corrections must be auditableProve what the report showed last monthVersioned fact: new row per change with valid_from and valid_to on the grain keyThe fact behaves like Type 2; expensive, use only when required
Corrections must never change a posted rowLedgers, invoicesReversal rows: a minus row and a plus rowTotals stay additive; history is the rows themselves
Show what the report said on a past dayAudit of a published monthTime travel or snapshots of the gold table, or a versioned factTime travel retention is short; keep a snapshot if the period matters

9. Designing a fact

How to design a fact properly: ten steps

  1. Name the business process and its owner (taking orders, sales team).
  2. Write the grain in one sentence, at the lowest level the source gives (one row per order line). Atomic grain first; aggregates come later.
  3. 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.
  4. List the measures that are true at that grain, and mark each additive, semi-additive or non-additive.
  5. Pick the fact kind (transaction, periodic snapshot, accumulating snapshot) from how rows change over time.
  6. Decide per dimension: as-of (version key) or current (durable key), or both.
  7. Declare the primary key (the grain columns) and check it is unique on every run.
  8. Declare how late data and corrections arrive: lookback window, correction rule, what happens when a dimension member is missing.
  9. Declare the window column (order date, promised date, cure hour) the harness rebuilds by, and when a window is complete.
  10. Write the contract: columns, types, owner, quality rules, freshness target. Only then write the build.
The test of a good fact: you can say its grain in one sentence, every column is either a key, a measure or a degenerate dimension, every measure says whether it adds up, and the harness can rebuild any day of it without touching the others.

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.

PatternThe askDesignLoad
Count and sum eventsHow many tyres did we sell per region per month?Transaction fact at event grain; additive measuresIncremental by event-date window
Level at a point in timeStock on hand at month endPeriodic snapshot; semi-additive measure (last value over time)Rebuild the open period; freeze closed ones
Durations and lags of a processDays from order to delivery; how many are lateAccumulating snapshot with one date per milestone and lag measuresUpsert by grain key as milestones arrive
Things that did not happenDealers with no orders this month; products not sold on promotionFactless coverage fact, compared with the event factRebuild per period
Plan against actualSales target vs actual per dealer per monthTwo 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 wasRevenue by the region a dealer had at the timeVersion key on the fact (as-of lookup); SCD Type 2 dimensionAs-of lookup on event date
Restate by today's structureLast year's revenue under this year's regionsDurable key on the fact; current view of the dimensionNo fact change when regions change
Late data that changes closed numbersA dealer's file arrives five days lateWindow rebuild with a completeness flag; restatement loggedLookback window per fact
Many-to-manyAn order line credited to two sales repsBridge table with weights that add to 1Rebuild the bridge with the dimension
Ratios and percentagesOTIF %, scrap rateStore numerator and denominator; define the ratio once in the metric viewSame 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)DateDealerMaterialPlantFact 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

silverclean, one row persource recordgold tablesatomic factsconformed dimensionsaggregate tablesmetric viewsone definition permeasure (semantic)materialized viewspre-aggregated,refreshed on a scheduledashboardsGenie / AI answersPower BInotebooks, MLdata sharing

Gold tables hold the truth; metric views define the numbers; materialized views keep popular answers ready.

ObjectWhat it isUse it whenWho refreshes itVerdict
Atomic fact and dimension tablesThe star itselfEvery modelThe harness, incremental by windowPursue: the source of truth for every number
Aggregate table (a gold table at a coarser grain)Rows already added up, kept by the harnessA named question is slow or costly on the atomic fact, and the aggregate is reusedThe harness, changed windows onlyPursue with care: one more table to keep in step
Materialized viewA query whose result is stored and refreshed by the platformThe same aggregate is read many times; the logic is a plain SQL query over goldThe 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 questionsAlways, for any number shown to people or AINothing: it is a definition, computed at query timePursue: one number everywhere
Metric view with materializationThe metric view keeps pre-aggregated results it uses when a query fits themA metric view is slow at the grain dashboards ask mostThe platform (a managed pipeline per view)Pursue with care: refresh uses compute even when nobody reads
Plain viewA saved query, nothing storedRenames, filters, row securityNothingPursue for thin layers only
Wide 'one big table' for a toolFact joined to all dimensions, flattenedA tool that cannot join, or a ML feature tableThe harnessPursue 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 goldShaped by dashboards?Why
Atomic grain of each factNoSet by the business process and the source; dashboards come and go
Conformed dimensions and keysNoShared by every fact and every tool
SCD type per attributePartlySet by what the business must report over time, not by one chart; ask the owner, not the dashboard
Measures and their definitionsNoDefined once in the metric view; dashboards read them
Data quality rules, freshness targets, contractsNoPromises of the data product
Aggregate tables and materialized viewsYesExist only to make named dashboard or AI questions fast or cheap
Pre-aggregation grainYesChosen from the grains the top dashboards actually read
Refresh cadence of aggregatesYesMatches how fresh the dashboard must be, never fresher than its inputs
Wide flattened tables for a toolYesOnly for a tool that cannot join
Which dimensions a metric view exposesPartlyBusiness vocabulary first; dashboards may add filters

14. Storing answers

When to materialize, and when not

SituationDecision
The same aggregate is read many times a day, and the query is slow or costlyMaterialize (materialized view or aggregate table)
Few reads, or reads spread over many different slicesDo not: let the metric view compute it at query time
The atomic fact is small (tens of millions of rows) and clustered wellDo not: a warehouse answers in seconds anyway
Freshness must be minutes, and the base changes all the timeDo 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 factAggregate table in the harness, not a materialized view
Plain SQL over gold, refresh on a schedule is fineMaterialized view (the platform refreshes it)
A metric view is slow at the grain dashboards ask mostMetric-view materialization at that grain
Nobody can say which question it speeds upDo 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:

RuleWhat it means
Look at what is askedTake 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 factNever replace it: drill-down, reconciliation and new questions need it.
Aggregate one step up firstdealer 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 measuresPre-aggregate sums and counts. For ratios, pre-aggregate the numerator and denominator; divide in the metric view.
Same dimensions, same keysAn 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 windowRecompute only the days or months the atomic fact changed; never the whole table.
Prove it paysCompare query time and compute cost before and after. An aggregate nobody reads is pure cost: drop it.

Which grain

What is askedPre-aggregate atLeave on the atomic fact
Dashboards show monthly trends by regiondealer x material x month, or region x month if the region never changesDaily stays on the atomic fact for drill-down
Daily operational tilesplant x material x dayHourly drill-down reads the atomic fact
Executive summaryregion x month, a handful of rowsUsually a materialized view is enough
AI answers over many slicesUsually none: the metric view over a well-clustered factPre-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.

PracticeWhat to doWhere
Keep the star; integer keysColumnar 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 keyQueries skip most files; liquid clustering on Delta, sort order on Iceberg; compact regularly.Gold
Aggregates at the grains people readMost 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 definitionsEvery tool asks the same definitions; the warehouse pushes filters and grouping down to clustered tables.Semantic
Power BI: Import for small and medium modelsImport 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 factsQueries go to the warehouse each time; keep visuals few and filtered; never on unclustered, unaggregated tables.BI
Power BI: composite model with aggregationsAggregated 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 hygieneOne-to-many relationships from dimension to fact, single direction; a marked date table; remove unused columns; no calculated columns on big facts.BI
Warehouse settingsServerless SQL warehouse with Photon, result cache on, sized for concurrency (scale-out clusters) not for single-query size.Platform
Row security in one placeRow filters in the catalog or in the BI model, not both, so caches stay useful.Platform
Measure before and afterQuery 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.

ProblemSymptomCauseFix
Fan-outTotals too big after a joinOne 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 trapOrders and deliveries both joined to dealer: totals multiplyTwo facts joined through a shared dimension in one queryAggregate each fact to the shared grain first, then join (drill across)
Fan trapHeader amount repeated on every line, summedA measure at a coarser grain carried onto a finer grainKeep header measures on a header fact, or allocate them to lines
Rows silently missingTotals too small after a joinInner join to a dimension where the member is missing or lateUnknown 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 countCommission paid twiceOrder line credited to two reps with no weightsBridge table with weighting factors
Mixed grainDay and month rows in one table; totals doubleTwo grains in one factOne grain per fact; aggregates in their own tables
Grain mismatch across factsPlan (monthly) vs actual (daily) compared day by dayFacts at different grains joined at the finer oneCompare at the coarser grain only
Semi-additive summed over timeStock 'total' for the month is thirty times too bigDaily stock added across daysUse last value or average over time; the metric view encodes it
Ratio averagedAverage of dealers' OTIF % differs from company OTIF %Averaging ratios with different denominatorsAdd up numerator and denominator, then divide
Time-zone day boundaryDay totals differ between report and sourceGrain day cut in UTC instead of the business time zoneCut 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.

WhatLives inOwner and ruleVerdict
Conformed dimensions (date, dealer, material, plant)<env>_gold.sharedOwned by the team that masters the data (master data, or the platform team for date); every domain reads, nobody else writesPursue
Facts of one business area<env>_gold.<domain>: sales, manufacturing, financeOwned by the domain team; their metric views and aggregates sit beside themPursue
Dimensions only one domain usesWith that domain's factsMove to shared the day a second domain needs themPursue
Metric viewsNext to the facts they read, in the domain schemaCertified views read by dashboards and AI; previews markedPursue
Aggregate tables and materialized viewsDomain schema, named agg_<process>_<grain>Owned with the fact they come fromPursue
Cross-domain facts (plan vs actual across finance and sales)The domain that owns the decisionReads the other domain's facts only through conformed dimensionsPursue with care
Copies of a shared dimension inside each domain-Drift: two dealer lists that disagreeDrop
One schema for all of gold-No ownership, no access boundariesDrop 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.

TableCluster onWhy
Transaction factThe 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 snapshotThe period (month) and the main keySame reason; snapshots are read by period
Accumulating snapshotThe grain key's date (order date) and the main keyUpserts by grain find their rows fast
SCD2 dimensionBusiness key (and is_current)The build looks up by business key and event time
Small dimension (under a few million rows)NothingIt is broadcast; clustering adds upkeep for no gain
Aggregate tableIts date and its first dimensionSame as the fact it came from
What not to cluster onA unique id (order_id) unless people look rows up by it; more than about four columns; columns nobody filtersHigh-cardinality ids spread every query; too many keys dilute all of them
Let the platform chooseAutomatic clustering where available (Unity Catalog managed tables can pick keys from query history)Pursue once query patterns are stable; check what it chose
Iceberg noteOn 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

TopicRuleVerdict
ClusteringCluster 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
PartitioningPartition only very large facts, and only by a coarse date (month). Small partitions make small files.Pursue with care
File sizesAim for files of hundreds of megabytes; compact after many small window replaces.Pursue: scheduled upkeep
Namingfact_<process>, dim_<thing>, agg_<process>_<grain>, columns in plain words with units (qty_ordered, value_inr).Pursue
Comments and contractEvery table and column described; the grain sentence in the table comment.Pursue: AI tools read these
NullsNo null foreign keys (use -1 or -2); nulls in measures only when truly unknown.Pursue
Data typesExact 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

MistakeWhat goes wrongFix
Mixed grain in one factOrder 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 measuresSomeone averages OTIF % across dealers and gets a wrong number.Store numerator and denominator
Business key as the fact's linkHistory breaks when the dealer moves region; two sources clash.Surrogate keys from a key map
Type 2 on everythingDimension rows explode; nobody reports on most of the history.Type 2 only on attributes reports slice by
Snowflaked dimensionsFive joins to get a brand name; slow and confusing for tools and AI.Flatten into one dimension
No date dimensionEvery report re-derives weeks, fiscal months and holidays differently.One shared date dimension, used in roles
One big table instead of a starEvery change to a dimension rewrites billions of fact rows.Keep the star; build wide tables from it when a tool needs them
Aggregates firstThe atomic detail is never stored; a new question cannot be answered.Atomic fact first, aggregates only for named questions
Facts reading facts in chainsBackfills need a careful order; one error spreads.Build from silver or from one atomic fact, never three deep
Descriptive text on factsDealer 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.

TableGrainKind and refreshDimension lookupsWhat we would change
fact_otifOne row per order lineTransaction, rebuilt by promised-date window (35-day lookback)Dealer and material as of the order time; unknown member while a new dealer is missingBetter as an accumulating snapshot with order, promised, shipped and delivered dates
fact_salesOne 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 itDealer and material as of the monthLate files restate the month; re-sent files change nothing
fact_scrapOne row per press, material and cure hourPeriodic snapshot, hourly (48-hour lookback)Press and material as of the cure hourPlant needs its own dimension (time zone)
dim_dealer, dim_material, dim_pressOne 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

DecisionChoiceVerdict
Modelling style for goldDimensional (star) with conformed dimensionsPursue
GrainAtomic first, declared in one sentence, checked every runPursue
KeysSurrogate keys from a key map; facts store version and/or durable keys; -1 and -2 reservedPursue
HistorySCD Type 2 only on attributes reports slice history by; Type 1 elsewherePursue
Date dimensionOne, role-playingPursue
RatiosNumerator and denominator stored, divided in the metric viewPursue
Semantic layerMetric views over the star, certified measures only for dashboards and AIPursue
Pre-aggregationOnly for named slow questions, one step up first, refreshed by changed windowPursue with care
Data vault in goldHubs, links and satellites as the reporting layerDrop: 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