Type 2: Preserve Full History

Concepts: dmScdType2

Keeping Every Version of a Dimension Row Type 2 creates a new row for every change. The old row is marked as expired. The new row is marked as current. Both rows have different surrogate keys. Fact rows from before the change point to the old SK. Fact rows after the change point to the new SK. Revenue is attributed to the correct city at the time of each transaction. Two rows, same customer_id, different surrogate keys. Fact rows from January 2024 join to SK 1001 (Portland). Fact rows from July 2024 join to SK 1002 (Seattle). Revenue attribution is correct at every point in time. Key Components Point-in-Time Joins The power of Type 2: any fact row can be joined to the correct version of the dimension. If the fact stores the surrogate key (assigned at load time), the join is a simple equali