A surrogate key is an identifier generated by the data platform, with no business meaning, used as the primary key of a dimension table. It is typically an integer sequence (1, 2, 3...), a hash of other columns, or a UUID. Fact tables reference dimensions through these keys.
The alternative is a natural key: an identifier that comes from the source system or the real world, such as an email address, an employee ID, or a product SKU. Natural keys work in operational databases, but they cause problems in analytical models.
Why Not Use Natural Keys?
Natural keys change and collide. Suppose a warehouse uses email address as the customer key and a sales fact table holds 50 million rows keyed on john.doe@gmail.com. When John changes his email, every historical fact row would need to be rewritten to keep joins working.
With a surrogate key, John is customer 1045. His email is just an attribute in the customer dimension. Changing it updates one dimension row, and the fact table is untouched.
Natural keys also collide when data comes from several sources. Customer 500 in the CRM and customer 500 in the billing system may be different people. A surrogate key gives each integrated entity one identifier regardless of where it came from.
Surrogate Keys and SCD Type 2
Type 2 slowly changing dimensions keep history by adding a new row whenever a tracked attribute changes. When employee E-500 is promoted from Analyst to Manager, the dimension holds two rows for the same natural key. Each needs its own surrogate key:
| employee_key | employee_id | title | valid_from | valid_to |
|---|---|---|---|---|
| 1045 | E-500 | Analyst | 2024-01-01 | 2026-03-31 |
| 1046 | E-500 | Manager | 2026-04-01 | null |
Facts from March point to 1045 and facts from June point to 1046, so reports show each sale under the title that was true when it happened.
Generating Surrogate Keys on a Lakehouse
Traditional warehouses generate surrogate keys with sequences or identity columns. The Apache Iceberg table spec has no identity or auto-increment column, and distributed engines writing in parallel make a global counter hard anyway. (Delta Lake does offer identity columns, with documented limits on concurrent writes.) Common approaches on Iceberg:
| Method | Example | Pros | Cons |
|---|---|---|---|
| Hash of natural key (plus effective date for SCD2) | xxhash64(employee_id, valid_from) or md5(...); dbt’s generate_surrogate_key macro | Deterministic, so reruns and MERGE produce the same key; no coordination between writers | Wider than an integer; small collision risk with short hashes |
| UUID | uuid() | Unique without coordination | Not deterministic, so a rerun creates new keys; poor compression |
| Offset row number | max(key) + row_number() in a single writer job | Compact integers | Requires one writer at a time; fragile under retries |
For most lakehouse pipelines, a deterministic hash is the practical choice because it makes pipelines idempotent: rebuilding a dimension from scratch yields the same keys, so existing fact rows still join. Keep the natural key as a regular column for lookups and auditing.
For worked examples, see Slowly Changing Dimensions: Types 1-3 with Examples, Dimensional Modeling: Facts, Dimensions, and Grains, and Idempotent Pipelines: Build Once, Run Safely Forever.
Summary
Surrogate keys separate analytical joins from source-system identifiers that change or collide, and they make SCD Type 2 history possible. On Iceberg, where there is no identity column, deterministic hash keys are usually the safest way to generate them.
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.