In this lesson, you will learn how to use system-generated surrogate keys to protect your analytical reports from changes in source system data.
Why Source Keys Fail
In your source databases (like a CRM or ERP), every record has a unique ID—often called a "Natural Key" (e.g., employee_id = 501). However, relying on these in your data warehouse is dangerous. What happens if a company merges, resets its ID sequences, or recycles an old employee_id for a new hire? If you use the natural key, your historical sales data for the "old" 501 will suddenly point to the "new" 501, corrupting your analytics.
The Surrogate Key Solution
A Surrogate Key is a meaningless, system-generated integer (usually an IDENTITY or SEQUENCE column) created specifically for your data warehouse. It acts as the primary key for your dimension table, completely decoupling your analytical model from the source system.
Illustrative Example
Imagine you are tracking employees in a dimension table. Instead of using their employee_id, you assign a dim_employee_key.
| dim_employee_key | source_employee_id | name | department |
|---|---|---|---|
| 1001 | 501 | Alice | Sales |
| 1002 | 501 | Alice | Marketing |
By using the surrogate key (1001 or 1002), you can maintain two distinct records for the same source person. This allows your fact tables to point to a specific version of that employee as they move through time or change roles, without ever needing to touch the original source_employee_id.
Implementation Strategy
When loading data into your warehouse, you generate these keys during the transformation process:
-- Generating a surrogate key during a load
INSERT INTO dim_employee (dim_employee_key, source_id, name)
SELECT
NEXT VALUE FOR employee_seq, -- The system-generated surrogate
s.employee_id, -- The source system identifier
s.name
FROM staging_employees s;
By decoupling the data, you ensure that even if the source system changes its ID format or reuses identifiers, your analytical models remain consistent and structurally sound.
Key Takeaway: Surrogate keys decouple your analytical models from volatile source system identifiers, ensuring your historical data remains accurate and immune to source-side system changes.