A natural key (also called a business key) is a column, or set of columns, that uniquely identifies a row using information that has meaning outside the database. Common examples include a vehicle’s VIN, a book’s ISBN, a product SKU, a customer’s email address, or a national ID number.
In operational (OLTP) applications, developers often use natural keys as the primary key because they are easy for people to read and understand. When that data moves into a data lakehouse or cloud data warehouse for analytics, using natural keys as the joins between fact and dimension tables makes pipelines fragile.
Why Natural Keys Break
A good primary key is unique and never changes. Natural keys often fail one or both rules.
1. They Change
Suppose a system uses email address as the key linking Customers to Transactions. When a customer changes their email, every historical transaction row that references the old address must be updated to keep the links intact. On a large fact table, that is an expensive rewrite.
2. They Are Not Always Unique or Present
Identifiers assumed to be unique often are not in practice. A pipeline keyed on a national ID number fails for customers who do not have one, and data entry errors can produce two people with the same value. Strict uniqueness constraints then block legitimate records.
The Role of Natural Keys in the Warehouse
Because of this, dimensional models usually link fact and dimension tables with surrogate keys: meaningless generated values such as integers, UUIDs, or hashes.
Natural keys still matter. Their job is integration and deduplication. When a pipeline ingests customers from Salesforce (keyed by salesforce_id) and Zendesk (keyed by zendesk_id), a shared natural key such as a normalized email address shows that two records describe the same person. The pipeline joins on the natural key, then assigns a surrogate key that the rest of the model uses. The natural key stays as an attribute on the dimension so analysts can still search by it.
Natural Keys on Apache Iceberg Tables
Open table formats change one important detail: they do not enforce primary keys. Apache Iceberg lets you declare identifier fields on a table schema, and Apache Flink uses them for upserts (the Iceberg Flink docs show PRIMARY KEY (id) NOT ENFORCED). The engine does not reject duplicate values. Uniqueness is the pipeline’s job, usually handled with MERGE INTO keyed on the natural key.
| Natural key | Surrogate key | |
|---|---|---|
| Meaning | Business value (email, SKU, VIN) | None; generated |
| Stability | Can change or be missing | Stable once assigned |
| Main lakehouse use | Matching and deduplicating sources in bronze and silver layers; MERGE INTO match conditions | Joining facts to dimensions in gold tables |
| Slowly changing dimensions | Identifies the entity across versions | Identifies each version of the entity |
| Enforced by Iceberg? | No (identifier fields are declarative) | No (generated by the pipeline) |
In a Type 2 slowly changing dimension, the natural key ties all historical versions of a customer together while each version gets its own surrogate key. For a full walkthrough of facts, dimensions, and grain, read Dimensional Modeling: Facts, Dimensions, and Grains.
Summary of Technical Value
Natural keys give data real-world meaning, but they change and are not always unique, so they make poor join keys in analytical models. Use them to match and deduplicate records during ingestion, keep them as dimension attributes, and join gold tables on surrogate keys. On Iceberg, remember that neither kind of key is enforced by the table format, so uniqueness checks belong in the pipeline.
Learn More
To learn more about the Data Lakehouse, read The Lakehouse Built for Everyone by Alex Merced. You can find this and other books by Alex Merced at books.alexmerced.com.