Launch Deal · 50% off 1st month ·

2.2 — Prepare Data for a Dimensional Model

DP-700 Domain 2 · Skill 2.2

What You Need to Know

A star schema splits data into fact tables (measurable events such as sales or clicks, at one grain) and dimension tables (descriptive context such as product, customer and date). Preparing data for it means reshaping source rows, assigning keys and managing history.

Facts and dimensions

Fact table Dimension table
Holds Numeric measures (quantity, amount) plus a key for each dimension Descriptive attributes (name, color, category)
Grain One row per event at a stated level, such as one order line One row per member (or per version, with SCD Type 2)
Keys Foreign keys to the dimensions' surrogate keys A surrogate key plus the source's business (natural) key
Size Long and growing Smaller and wide

When you split a flat source table, put the descriptive columns that belong to the entity (for a product: ID, name, color, category) in the dimension. Keep event columns such as date, transaction ID and amount in the fact.

Surrogate keys

  • A surrogate key is a meaningless integer that you generate. It protects the model from changes to source keys and makes SCD Type 2 history possible, because each version of a member gets its own key.
  • In a Fabric warehouse, use a BIGINT IDENTITY column (no custom seed or increment; values are unique but can have gaps). Alternatives are ROW_NUMBER() plus the current maximum key, or a hash of the business key.
  • In a lakehouse, generate keys in Spark (for example monotonically_increasing_id() offset by the current maximum, or a Delta identity column) and keep the mapping stable between loads.
  • Use the smallest integer type that fits. Wide hash or string keys make joins and Direct Lake models slower.
  • Fabric warehouses don't enforce primary or foreign keys (PRIMARY KEY NONCLUSTERED ... NOT ENFORCED), so the load process itself must check integrity.

Slowly changing dimensions (SCD)

Type When an attribute changes Use it when
Type 0 Nothing changes The value is fixed, such as an original credit limit
Type 1 Overwrite the row in place You don't need history, for example a corrected spelling
Type 2 Expire the current row (set its end date and IsCurrent = 0) and insert a new version with a new surrogate key Reports must show facts against the attribute value at the time of the event
Type 3 Keep the previous value in an extra column You only need "current and previous"

Loading order and edge cases

  1. Load dimensions first, so that every member exists before facts look up their keys. Outrigger dimensions (such as geography) go before the dimensions that use them.
  2. Load facts: look up each surrogate key, using the current version for SCD Type 2 (or the version valid on the event date).
  3. If a lookup finds no dimension member, don't drop the fact row. Point it at an Unknown member (for example key −1). Or insert an inferred member that has only the business key and IsInferredMember = 1, and fill in its details when they arrive (late-arriving dimensions are covered in lesson 2.6).
  4. Date dimension keys are usually calculated in YYYYMMDD format rather than looked up.

Key Concept: Facts are events at a grain. Dimensions are context with history. Surrogate keys separate the model from the source. Load dimensions before facts.

Exam Traps

  • Putting transaction columns (date, transaction ID, amount) into a dimension
  • Overwriting a changed attribute (Type 1) when the requirement says to track changes
  • Truncating and reloading a dimension that has generated surrogate keys
  • Dropping fact rows whose dimension key isn't found yet
  • Using MD5 or other string hash keys and then wondering why joins are slow and models are large

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.