Natural Key

A natural key is an identifier that comes from the business or its operational systems rather than being generated by the data warehouse: a customer number, an order number, a product SKU, an employee ID, sometimes an email address. It is how a source system recognizes a record, and usually how people refer to it.

Why it is not enough on its own

Natural keys belong to systems the warehouse does not control, and the Kimball Group’s guidance lists the ways that shows:

• They change for business reasons. The Kimball Group’s example is an employee who leaves and returns and is given a new employee number, so one person has two natural keys.
• Different sources disagree. The same customer carries different identifiers in the billing and support systems, and identifiers from different systems may collide or be poorly governed.
• History breaks uniqueness. A dimension that keeps history adds a new row each time a tracked attribute changes, so one natural key has several rows and cannot be the dimension’s primary key.

For these reasons dimensions are usually keyed by a warehouse-assigned surrogate key, one per row or version, and a stable durable key can be added to follow one entity when its natural key changes.

Why it is still kept

Replacing the natural key as the join key does not mean discarding it. It remains an attribute of the dimension row, because it is needed to:

• match incoming source records to existing dimension rows during loading;
• reconcile the warehouse against the source system;
• let people look up the record they know by its business identifier;
• find the right dimension version for a fact that arrives late, by matching the natural key and the event’s date against each version’s effective dates.

The common mistake

In a dimension that keeps history, joining facts on the natural key alone — with nothing selecting which version was in effect — matches every version of that entity and multiplies each fact by the number of versions. The totals grow and nothing errors. Facts should join on the surrogate key they were loaded with, or on the natural key together with the version’s effective date range when starting from the source identifier. How this and other joins inflate totals is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.

References: Kimball Group, Natural, Durable, and Supernatural Keys; Kimball Group, Dimension Surrogate Keys; Kimball Group, Slowly Changing Dimensions, Part 2.