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.
Example
Copy code
CREATE SEQUENCE seq_product_key START 1;
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY DEFAULT nextval('seq_product_key'), -- surrogate key
product_id VARCHAR, -- natural key from the source system
product_name VARCHAR,
category VARCHAR
);
-- Or with a UUID surrogate key
CREATE TABLE dim_product_uuid (
product_key UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id VARCHAR,
product_name VARCHAR
);
Fact tables then reference the surrogate key, not the natural key, when joining to dimensions — which is also what makes SCD Type 2 possible, since each historical version of a dimension row gets its own surrogate key.
DuckDB angle
DuckDB supports both common surrogate key patterns shown above: CREATE SEQUENCE with nextval() for compact integer keys, and the UUID type with gen_random_uuid() for globally unique identifiers that don't require coordinating a single counter — useful when dimension rows are generated by parallel or distributed loading jobs before landing in DuckDB or MotherDuck.
Related terms
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.
