2.5 — Star Schemas, Slowly Changing Dimensions and Bridge Tables
DP-600 Domain 2 · Skill 2.5
What You Need to Know
A semantic model is only as good as the tables under it. Building a star schema in the lakehouse or warehouse (the gold layer) is core DP-600 work.
Star schema essentials
- Fact tables hold measures at a declared grain (one row per order line, or per daily balance), plus a surrogate key for each dimension.
- Dimension tables hold descriptive attributes, a surrogate key (small integer) and the source's business key.
- Keep the schema star-shaped: flatten snowflaked hierarchies (product → subcategory → category) into one dimension.
- Add a date dimension with an integer
YYYYMMDDkey and the attributes reports need (year, quarter, month name, fiscal periods).
Slowly changing dimensions
| Type | Behavior | Columns you need |
|---|---|---|
| Type 1 | Overwrite the attribute (no history) | Optional ModifiedDate |
| Type 2 | Expire the current row and insert a new version with a new surrogate key | ValidFrom, ValidTo, IsCurrent |
Type 2 in a warehouse (simplified):
-- 1. Expire changed current rows
UPDATE d SET ValidTo = @LoadDate, IsCurrent = 0
FROM gold.DimCustomer d JOIN staging.Customer s ON d.CustomerBK = s.CustomerBK
WHERE d.IsCurrent = 1 AND (d.City <> s.City OR d.Segment <> s.Segment);
-- 2. Insert new versions and brand-new customers
INSERT INTO gold.DimCustomer (CustomerBK, Name, City, Segment, ValidFrom, ValidTo, IsCurrent)
SELECT s.CustomerBK, s.Name, s.City, s.Segment, @LoadDate, '9999-12-31', 1
FROM staging.Customer s
LEFT JOIN gold.DimCustomer d ON d.CustomerBK = s.CustomerBK AND d.IsCurrent = 1
WHERE d.CustomerBK IS NULL;
In a lakehouse, the same logic is a Delta MERGE (update to expire, then insert the new versions) or two steps in PySpark. Fact loads look up the current version's surrogate key, or the version valid on the event date.
Bridge tables (many-to-many)
Some relationships are naturally many-to-many: an account with several owners, a patient with several diagnoses, a product in several campaigns.
DimCustomer ──< BridgeAccountCustomer >── DimAccount ──< FactBalance
- The bridge table holds one row per pair (AccountKey, CustomerKey), and optionally an allocation weight.
- In the semantic model, relate the bridge to both dimensions. Filtering from DimCustomer to the facts usually needs the bridge to DimAccount relationship to filter in both directions (or
CROSSFILTERinside specific measures) so the filter can reach the fact table. - Be aware of double counting: a balance belongs to every owner, so totals across customers can exceed the real total. Weights solve this when an allocation is needed.
Key Concept: Star schema = facts at a grain + flattened dimensions with surrogate keys. Type 2 = expire and insert with a new key. Many-to-many = a bridge table, with deliberate filter direction.
Exam Traps
- Overwriting (Type 1) when the requirement says reports must show historical values
- Reusing the same surrogate key for a new Type 2 version
- Leaving a snowflake (product → subcategory → category as three model tables) when one flattened dimension is better for performance and usability
- Creating a direct many-to-many relationship between two dimensions without a bridge, then wondering why totals are wrong
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
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.
Sources
- Dimensional modeling in Fabric Data Warehouse, Microsoft Learn
- Dimensional modeling: load tables, Microsoft Learn
- Understand star schema and the importance for Power BI, Microsoft Learn
- Many-to-many relationship guidance, Microsoft Learn