Dimensional Modeling - Durable Surrogate Key

In dimensional modeling, a durable surrogate key (DSK) is:

---

Definition

A durable surrogate key is a surrogate key value in a dimension table that never changes for a given business entity, even if that entity is logically deleted, merged, or re-created in the source system.

• "Durable" = stays the same over time

• "Surrogate" = not derived from business data (e.g., not a natural key like a customer ID from a source system)

---

Why it exists

In typical slowly changing dimensions (SCD), surrogate keys are stable during the entity’s lifetime in the dimension table.
But in some ETL setups, if a record is deleted in the source and re-inserted later, the surrogate key might get reassigned — breaking history tracking in fact tables.

A durable surrogate key solves this by:

1. Assigning an ID to the entity the first time it ever appears

2. Keeping that same ID forever if it reappears later, no matter the source system key changes

---

Example

Imagine a Customer Dimension:

Durable_SK	Current_SK	Customer_Natural_Key	Name	Start_Date	End_Date
1001	2001	A123	Alice	2021-01-01	2022-03-31
1001	2002	A123	Alice L.	2022-04-01	9999-12-31

• Durable_SK = 1001 (always the same for Alice across all her history)

• Current_SK = 2001, 2002 (different surrogate keys for each version in SCD2)

If Alice’s record was deleted in the source in 2023 and then added back in 2024 with a new source ID, she would still get Durable_SK = 1001.

---

Benefits

• Maintains permanent linkage between facts and the same business entity, across all history and re-creations

• Helps in data lineage and cross-system entity tracking

• Makes fact table joins and entity analytics more consistent

 

Here’s the side-by-side comparison for the three terms:

Key Type	Who Creates It	Tied to Source System?	Can Change Over Time?	Purpose	Example
Natural Key	Operational source system	Yes	Yes (subject to business rules)	Identify an entity in a specific system	Employee_ID = A123 in HR system
Surrogate Key (SK)	Data warehouse / BI system	No	No for a single version, but a new SK is generated for each historical version (SCD2)	Link facts to a specific version of a dimension row	SK = 2001 for “Alice” version 1; SK = 2002 for “Alice” version 2
Durable Supernatural Key (or Durable Surrogate Key, DSK)	Data warehouse / BI system	No	Never changes for that real-world entity, even across multiple SCD versions or source re-creations	Provide a permanent, system-independent ID for the entity, not just a version	DSK = 1001 for “Alice” (same for all historical versions)

---

How they relate in Slowly Changing Dimension Type 2

Using the employee example:

DSK	SK	Emp_Natural_Key	Name	Start_Date	End_Date
1001	2001	A123	Alice	2021-01-01	2022-03-31
1001	2002	A123	Alice L.	2022-04-01	9999-12-31

• Natural Key: A123 (might change if employee is rehired)

• Surrogate Keys: 2001 and 2002 (different for each historical record)

• Durable Key: 1001 (permanent across all versions and re-creations)