Durable Key
A durable key is an identifier the data warehouse assigns to an entity — a customer, an employee, a product — that stays the same for that entity’s entire life in the warehouse, regardless of changes to its source identifiers or its attributes. The Kimball Group introduces it for the case where natural keys prove unstable: a new durable key “must be created that is persistent and does not change.”
The problem it solves
Two other keys are already in play in a dimension, and neither follows one entity reliably over time:
| Key | Identifies | Changes when |
|---|---|---|
| Natural key | A record in a source system | The business reissues it, a system migration changes it, or another source uses a different one |
| Surrogate key | One row, or one historical version, of the dimension | Every time a tracked attribute changes and a new version is added |
| Durable key | The entity itself, across all versions and source identifiers | Never |
The Kimball Group’s example is an employee who leaves and later returns and receives a new employee number. The natural key has changed, so it can no longer link the two employment periods, while the surrogate key changes with every version anyway. A durable key assigned at first appearance and carried forward ties them together. The Kimball Group recommends making it a simple integer sequence starting at 1, independent of any source system’s numbering.
What it enables
- Reporting on current attributes across all history. Joining facts through the durable key to the entity’s current row lets every past sale be grouped by today’s region — an alternative to the as-it-was view that surrogate keys provide.
- Counting entities correctly. Distinct customers over time is a count of durable keys, not of surrogate keys (which counts versions) or natural keys (which may count one person twice).
- Merging identities. When two source records are found to be the same person, both can be mapped to one durable key without rewriting the fact rows. That still changes how the past reads: a report counting distinct customers over facts for both records goes from two to one. If reports must be able to show the count as it was, keep the mapping’s history — when each link took effect and was recorded, the earlier decision, and the source identities — so that the merged view is a deliberate restatement rather than an unnoticed one.
What it requires
A durable key is only as good as the rule that decides two records are the same entity. Someone has to own that rule — matching on email, on a government identifier, on manual review — and a mapping from source identifiers to durable keys has to be maintained as sources change. The key itself is trivial; the identity decisions behind it are not.
How durable, natural, and surrogate keys work together when dimensions keep history is worked through in Modeling Time: Three Clocks, History, and Corrections.
References: Kimball Group, Natural, Durable, and Supernatural Keys.
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.