Launch Deal · 50% off 1st month ·

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 YYYYMMDD key 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 CROSSFILTER inside 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.

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.