Glossary

Surrogate key

An artificial, meaningless identifier generated by the warehouse to uniquely identify a dimension or fact row.

A surrogate key is an identifier — typically a simple integer or hash — that a data warehouse generates itself to uniquely identify a row in a dimension table, instead of reusing the "natural key" that identifies the same entity in a source system, such as a customer ID or SKU. It carries no business meaning of its own.

Surrogate keys matter most in a slowly changing dimension Type 2 setup: a single customer can have several dimension rows over time, one per version of their attributes, and each needs its own surrogate key while sharing the same natural key. Fact tables then join to the surrogate key that was current when the event happened, not to the natural key, which is what lets a fact table stay correctly linked to history even as dimension attributes change.

Surrogate keys also insulate a warehouse from source-system churn — if an upstream system reuses or reformats its IDs, or two systems are merged and their natural keys collide, the warehouse's own keys are unaffected. The trade-off is an extra lookup step during loading to translate natural keys into surrogate keys, a step often handled by a data vault hub table, and a pitfall is generating a new surrogate key for what should be an unchanged row, which duplicates history unnecessarily.

Last reviewed September 22, 2026

In the index now

Related terms

Dimension table A table of descriptive attributes, such as customer or product details, that a fact table references for context. Fact table The table in a dimensional model that stores measurable, numeric events, each row linked to the dimensions describing it. Slowly changing dimension The techniques for recording how a dimension table's attributes change over time without breaking historical reporting. Data vault A modeling methodology that splits data into hubs, links, and satellites to make a warehouse auditable and adaptable to change. Star schema A dimensional modeling pattern with a central fact table connected to descriptive dimension tables, shaped like a star. Normalization Structuring a database so each fact is stored in exactly one place, reducing redundancy and inconsistency.