Structuring Type 2 Row Versioning

In this lesson, you will learn how to preserve a complete audit trail of dimension attribute changes by creating new records for every update rather than overwriting existing data.

The Concept of Type 2 Versioning

In earlier lessons, we covered Type 1 (Overwrite), which is perfect for fixing typos. However, analytical teams often need to know the state of a record as it existed at a specific point in time. If a customer moves from New York to California, a Type 1 update would lose the fact that they lived in New York last year. Type 2 Row Versioning solves this by maintaining a history of changes.

The Anatomy of a Type 2 Table

To track history effectively, your dimension table needs three specific structural components beyond your standard attributes:

1. Surrogate Key (SK): A unique, system-generated primary key for every specific version of the entity. You never use the natural business key (like customer_id) as the primary key of the dimension table.
2. Effective Date (start_date): The date the record version became active.
3. Expiration Date (end_date): The date the record version ceased to be current (often set to a high-value date like 9999-12-31 for the active record).
4. Current Flag (is_current): A boolean (True/False) that allows you to instantly filter for the most recent version of an entity.

Visualizing the Change

Imagine a customer record updates:

SK	Customer_ID	City	Start_Date	End_Date	Is_Current
101	CUST01	New York	2022-01-01	2023-05-14	False
102	CUST01	California	2023-05-15	9999-12-31	True

When the City changed, we didn't delete the New York record. We closed it out by updating the end_date and is_current flag, then inserted a brand new row with a new SK. This allows your fact table to join to the exact record that was active at the time of the transaction.

Key Takeaway: Type 2 Row Versioning transforms static tables into historical timelines by using surrogate keys and date ranges to ensure that every change creates a new, traceable version of the data.