DOCS
← Back to Glossary
Surrogate key
A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.
Overview
A surrogate key exists purely to identify a row uniquely within a database; it carries no meaning to the business and is never shown to end users. This is in contrast to a natural key (like an email address or a source system's customer_id), which comes from the real-world entity itself. In dimensional modeling, dimension tables almost always use surrogate keys as their primary key, while the natural key from the source system is kept as a regular attribute column for traceability.
Surrogate keys solve several problems natural keys create: natural keys can change (an email address, a product SKU), can be reused across source systems with different formats, and can't distinguish between multiple historical versions of the same entity — which matters for slowly changing dimensions, where one customer's customer_id might map to several customer_key surrogate values over time, one per historical version.
Related terms
Natural key →
A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
Composite key →A composite key is a primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique on its own.
Dimension table →A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
foreign key →A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.
FAQS
Natural keys can change, get reused, or vary in format across source systems, and they can't represent multiple historical versions of the same entity. Surrogate keys are stable, compact, and let dimension tables track history cleanly, especially with slowly changing dimensions.
