Automating Dimension Versioning with SQL Procedures

In this lesson, you will learn how to encapsulate the complex logic of SCD Type 2 updates into reusable stored procedures to ensure consistent and error-free dimension versioning.

Moving Beyond Manual Updates

When managing Type 2 dimensions, manual UPDATE and INSERT statements are prone to human error—like forgetting to set the is_current flag or failing to calculate the correct end_date. In a professional data warehouse, we bundle this logic into a stored procedure that accepts new source records and performs the "close-and-create" sequence automatically.

The Automation Logic

A robust stored procedure for dimension versioning typically performs three distinct steps:

1. Identify Changes: Use a JOIN or EXCEPT clause to compare incoming source data with your current dimension table.
2. Close Expired Records: Update existing rows where the natural key matches an incoming record but the attributes have changed. You set the end_date to the current timestamp and flip is_current to FALSE.
3. Insert New Versions: Append the new, updated records with a fresh surrogate key, is_current = TRUE, and a null or future end_date.

Here is a simplified example of how this procedure structure looks:

CREATE PROCEDURE sp_update_customer_dimension()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 1. Close expired records
    UPDATE dim_customers
    SET end_date = CURRENT_DATE, is_current = FALSE
    WHERE natural_key IN (SELECT natural_key FROM stg_customers)
    AND is_current = TRUE;

    -- 2. Insert new versions
    INSERT INTO dim_customers (natural_key, attr, is_current, start_date)
    SELECT natural_key, attr, TRUE, CURRENT_DATE
    FROM stg_customers;
END;
$$;

Why Use Procedures?

By centralizing this code, you create a "single source of truth" for your logic. If your business rules for versioning change (e.g., adding an audit_id for lineage), you only need to update the procedure rather than hunting down every individual ETL script in your pipeline. Furthermore, wrapping these operations in a transaction ensures that the dimension never enters a partial state where an old record is closed, but the new one fails to insert.

Key Takeaway: Automating SCD Type 2 logic with stored procedures ensures consistent historical tracking and reduces the risk of data integrity errors in your dimension tables.