Surrogate Keys vs Natural Keys

Every dimension and fact needs a primary key, and every join needs one. The choice between surrogate and natural keys looks small but shapes everything about how the warehouse evolves.

Definitions

• Natural key: identifier from the source system (order_id from the OLTP database, customer_email, sku).
• Surrogate key: warehouse-generated identifier (auto-increment integer, hash, UUID) that's independent of source.

Why surrogate keys (almost always)

1. Type 2 SCDs need them — a customer with three versions needs three distinct keys, all with the same natural ID. Surrogates give you that uniqueness; natural keys force you to either include an effective date in the join (awkward) or merge versions.

2. Source ID changes don't propagate — if the source renames or re-uses customer_id (which happens — system migrations, re-platforming), the surrogate insulates downstream models.

3. Multi-source merging — if customers come from Shopify (string IDs) and Salesforce (integer IDs) and you need to model them together, surrogates unify them.

4. Storage efficiency — integers compress and join faster than strings (especially UUIDs). On a 1B-row fact, the difference is measurable.

5. Stable references — facts reference dim surrogate keys forever. If the source ID changes, the surrogate stays — the historical fact never needs rewriting.

Why keep natural keys around

Every dimension row should carry the natural key alongside the surrogate. Reasons:

• Traceability — if a metric looks wrong, you can trace back to the source system: "this customer_key 18934 was customer_id 'cust_7' in Shopify".
• Joining to source — sometimes you need to re-join with a fresh source pull. The natural key is the bridge.
• Identifying SCD versions — dim_customer.customer_id is the same across versions; dim_customer.customer_key differs.

The convention: surrogate _key, natural _id. customer_key is the warehouse PK; customer_id is the source identifier.

Generating surrogate keys

1) Auto-increment integer (SERIAL / IDENTITY)

CREATE TABLE dim_customer (
    customer_key  INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id   VARCHAR,
    ...
);

Simple, compact, fast joins. The downside: requires a sequence, which doesn't always play well with distributed warehouses (Snowflake, BigQuery handle it but with caveats).

2) Hash-based surrogate

customer_key = MD5(customer_id || '|' || effective_from)

Deterministic — same input always produces the same hash. Critical for idempotent dbt builds: re-running the model produces the same keys, so downstream facts don't change.

Used heavily in dbt warehouses. The convention is MD5 for stability and speed; SHA256 if you want extra collision resistance (overkill for most cases).

dbt has a built-in macro: dbt_utils.generate_surrogate_key(['customer_id', 'effective_from']).

3) UUID

customer_key UUID DEFAULT gen_random_uuid()

Globally unique without coordination. Useful for distributed generation. Downsides: 36 bytes per UUID (vs 8 for BIGINT), no natural sort order, slow joins compared to integers.

Rare in warehouses; common in OLTP.

Composite keys — usually avoid

You'll see fact tables with composite primary keys: (order_date_key, customer_key, product_key). Avoid if possible. Composite keys are:

• Harder to join on (always 3+ columns).
• Awkward in SCD logic.
• Slower than single-key joins (every join condition checks 3 columns).

Better: add a surrogate key on the fact too. fct_orders.order_fact_key BIGINT. Single-column joins everywhere.

Common key-strategy mistakes

1. Natural keys as primary keys in dimensions — breaks Type 2 SCDs. Always use surrogate keys for the PK.

2. Reusing OLTP source IDs as warehouse keys — couples warehouse evolution to source-system stability.

3. Generating surrogate keys at ETL time without determinism — re-running the build produces different keys, breaking downstream references. Use deterministic hashing.

4. Long string keys — customer_email as a join key is 50 bytes per row. On a 1B-row fact, that's tens of GB just in join columns. Use surrogates.

5. Dropping the natural key from the dimension — saves a column at the cost of traceability. Always keep it.

Naming convention

Stable across most warehouses:

Concept	Convention
Surrogate PK on dimension	<entity>_key (e.g., customer_key)
Natural ID on dimension	<entity>_id (e.g., customer_id)
FK on fact	<entity>_key (matches dimension PK)
Source-system traceability	natural <entity>_id carried through

Takeaway

Surrogate keys for PKs and FKs everywhere; natural keys carried on the dimension for traceability. Deterministic generation (hash-based) for dbt-style warehouses. Composite keys avoided when possible. Integer keys outperform string keys on big joins.