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.
Overview
A natural key (also called a business key) is a column, or set of columns, that uniquely identifies an entity using data that already has meaning outside the database: an email address, a national ID number, a vehicle VIN, a source system's customer_id. It's the identifier a business person would recognize and use.
Natural keys are useful and often necessary — they're how you match and deduplicate records coming from a source system, and how you'd look a record up manually. But dimensional models generally avoid using them as the primary key of a dimension table, in favor of surrogate keys, for a few practical reasons: natural keys can change (an employee ID gets reissued, an email address changes), can collide across source systems using different formats or numbering schemes, and can't represent more than one historical version of the same entity, which slowly changing dimensions require.
Example
Copy code
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY, -- surrogate key, used for joins
customer_id VARCHAR, -- natural key from the CRM system
email VARCHAR, -- also a natural/business identifier
customer_name VARCHAR
);
-- Look up the surrogate key from the natural key during loading
SELECT customer_key
FROM dim_customer
WHERE customer_id = 'CRM-00219' AND is_current;
The natural key is still kept in the dimension table as a regular column — it's essential for matching incoming source records to the correct dimension row during loads, and for tracing a warehouse record back to its system of origin.
DuckDB angle
Natural keys are ordinary columns in DuckDB with no special type — often given a UNIQUE constraint (per current version, for SCD Type 2 tables) rather than PRIMARY KEY, since the same natural key can legitimately appear on multiple historical dimension rows.
Related terms
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.
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.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
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.
Slowly changing dimensions (SCD) →Slowly changing dimensions (SCD) are techniques for handling changes to dimension attributes over time — such as a customer moving to a new region — while preserving or overwriting historical values as needed.
FAQS
It can, and is common in simple operational schemas. Dimensional warehouses typically avoid it for dimension tables, though, because natural keys can change and can't represent multiple historical versions of an entity, which surrogate keys handle cleanly.
