Uses an identifier the source already carries, an order number for instance, rather than one generated later. Learn prefers natural keys, if stable, to generated surrogates, since they work well for joins and clustering.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Natural key in context, with comparison tables and the common traps.
Related terms
- Inferred member
Placeholder added to a dimension when a fact load meets a natural key that doesn't exist yet. Attributes are marked Unknown and a flag records it as inferred, so the later dimension load can fill in the real values.
- Surrogate key
Instead of the source system's natural key, a generated value such as an identity column (
BIGINT GENERATED ALWAYS AS IDENTITY). Values are unique, gaps are possible, and an identity column rules out concurrent transactions on the table.