Managing Surrogate Keys in Versioned Dimensions

In this lesson, you will learn how to generate and assign unique surrogate keys to specific historical versions of a dimension member during your ETL process.

When we implement Type 2 row versioning, we don’t just store the current state of an entity (like a customer); we store a history of their states. Because the natural key (e.g., Customer_ID) repeats for every version of that customer, we cannot use it as our primary identifier in the fact table. Instead, we use a Surrogate_Key—a system-generated, integer-based unique identifier that points to one specific version of a record.

The Logic of Assignment

During the ETL process, your job is to ensure that every record—whether it is a brand-new entity or a new version of an existing entity—receives a fresh, unique surrogate key.

Imagine you have a customer, "Alice," whose address changes. Your ETL logic follows these steps:

1. Lookup: Identify if the incoming Natural_Key exists in the dimension table.
2. Version Check: Compare the incoming attributes (like Address) with the current active version.
3. Key Generation: If the record is new or the attributes have changed, you must assign a new Surrogate_Key.

In a SQL-based ETL pipeline, this is often handled using an identity column or a sequence generator:

-- Example: Assigning a new key for an updated customer record
INSERT INTO Dim_Customer (
    Customer_SK, -- This is your surrogate key
    Customer_ID, -- The natural key
    Address,
    Effective_Date,
    Is_Current
)
VALUES (
    NEXT VALUE FOR Customer_Sequence, 
    'CUST-101', 
    '123 New St', 
    '2023-10-27', 
    1
);

By assigning a unique Customer_SK to every version, you ensure that any fact records associated with Alice "pointing" to the 2022 version of her address remain logically separated from fact records pointing to her 2023 address. The fact table maintains its integrity because it links to a specific version of the dimension member rather than just the member itself.

Key Takeaway: Never reuse a surrogate key; every distinct version of a dimension record must have a unique identifier to ensure historical accuracy in your star schema.