In this lesson, you will learn how to implement durable, system-generated identifiers to maintain historical data accuracy when dimension attributes change over time.
The Problem with Natural Keys
In a data warehouse, you often rely on "natural keys"—business-defined identifiers like Employee_ID or Product_SKU. However, natural keys fall apart when a business entity changes its attributes. Imagine an employee moves from the Sales department to the Marketing department. If you simply update the Department column in your dimension table, you lose the historical record of which department they belonged to during past sales transactions.
Enter Surrogate Keys
A Surrogate Key is a unique, system-assigned integer that acts as the primary key for a row in your dimension table, independent of any business logic. Instead of updating a record, you create a new row with a new Surrogate Key whenever an attribute changes.
Consider this table structure designed for tracking changes:
-- Dimension Table with Surrogate Keys
CREATE TABLE dim_employee (
employee_sk SERIAL PRIMARY KEY, -- The Surrogate Key
employee_natural_id INT, -- The business ID
department VARCHAR(50),
effective_start_date DATE,
effective_end_date DATE,
is_current BOOLEAN
);
When an employee changes departments, you keep the old row (setting is_current to FALSE) and insert a new row with a fresh employee_sk.
Why This Matters for Aggregation
Because your Fact table references the employee_sk rather than the employee_natural_id, your historical aggregations remain perfectly accurate. If you run a report on total sales by department for the year 2022, the Fact table joins to the specific employee_sk that was active at that time. This decouples your analytical reporting from the volatility of business-defined keys, ensuring that your aggregate calculations reflect the state of the world as it existed when the transaction occurred.
By abstracting the identity of your records behind a surrogate layer, you create a robust history that supports complex, time-variant analytical queries without needing to rewrite past transaction records.
Key Takeaway: Surrogate keys decouple your analytical data from business-defined IDs, allowing you to track historical changes accurately by creating new, uniquely keyed versions of records instead of overwriting existing data.