Surrogate Keys

• A surrogate key is a value generated by the warehouse, with no meaning outside it, whose only job is to identify a dimension row uniquely and stably. Typically a monotonically increasing integer or a hash; the mechanism matters less than the property.
• The decisive property is that a surrogate key identifies a row, not a business entity. In a versioned dimension there is one row per version, so customer_key 4471 means "customer C-9912 as they were between March and July", and that is exactly the level of precision a historical fact needs (SCD Type 2 in Practice).
• The fact load resolves the surrogate key at load time, by looking up the dimension version that was current when the event occurred. That lookup happens once, during the load, rather than in every analyst's query — which is why point-in-time correctness ends up costing nothing at query time.
• Surrogate keys also decouple the warehouse from source key churn. When the source renumbers, you update the natural key mapping in the dimension and every fact row is untouched, because facts never held the source id in the first place.
• They are cheap in the fact table too: a four- or eight-byte integer replaces a long composite or a string, on billions of rows, in exactly the column that every join shuffles (Cardinality: The Label That Took Down Monitoring).
• Hash-based surrogate keys — a deterministic hash of the natural key plus the version boundary — trade the need for a central sequence for the ability to compute the same key independently in parallel, which matters in distributed loads and makes rebuilds reproducible (Hash Table).

dim_customer, versioned, joined on the NATURAL key
+-------------+---------+---------+------------+------------+
| customer_id | country | segment | valid_from | valid_to   |
+-------------+---------+---------+------------+------------+
| C-9912      | PL      | SMB     | 2024-01-01 | 2025-03-14 |
| C-9912      | DE      | SMB     | 2025-03-14 | 2026-01-20 |
| C-9912      | DE      | Enterp. | 2026-01-20 | NULL       |
+-------------+---------+---------+------------+------------+
   customer_id is NOT unique. It was never going to be.

fct_orders joined ON f.customer_id = d.customer_id:

  order O-7781, revenue 500.00, placed 2025-06-02
    -> matches version 1 (PL, SMB)        500.00
    -> matches version 2 (DE, SMB)        500.00
    -> matches version 3 (DE, Enterprise) 500.00
                                        ---------
    one order, reported revenue           1500.00

   No error. Row count tripled. Revenue tripled. Every version of
   every customer contributes once.

Same data, joined on the SURROGATE key resolved at load time:
+--------------+-------------+---------+---------+------------+------------+
| customer_key | customer_id | country | segment | valid_from | valid_to   |
+--------------+-------------+---------+---------+------------+------------+
|         4470 | C-9912      | PL      | SMB     | 2024-01-01 | 2025-03-14 |
|         4471 | C-9912      | DE      | SMB     | 2025-03-14 | 2026-01-20 |
|         4472 | C-9912      | DE      | Enterp. | 2026-01-20 | NULL       |
+--------------+-------------+---------+---------+------------+------------+

  order O-7781 was loaded with customer_key = 4471, chosen because
  2025-06-02 falls inside [2025-03-14, 2026-01-20).

    -> matches exactly one row            500.00

  And when the CRM migration renumbers C-9912 to CUST-000441209,
  you update ONE column in three dimension rows. No fact is touched.

• Give every dimension a surrogate key and make it the only thing facts join on. Keep the natural key in the dimension as an attribute, because it is what you need for debugging and for reconciliation against the source.
• Generate the key deterministically if you can — a hash of (natural_key, valid_from) — so a full rebuild reproduces the same keys and does not orphan facts that were loaded earlier (Idempotent Data Pipelines).
• Reserve a key for the unknown member — conventionally -1 or 0 — so facts with missing or not-yet-arrived context load and remain countable instead of being dropped by an inner join (Dimension Tables).
• Resolve the key in the fact load against the dimension version valid at the event's timestamp, not against the current version. Getting this wrong is what turns an SCD2 dimension into an expensive Type 1 (SCD Type 2 in Practice).
• Never expose surrogate keys to consumers as business identifiers. They are internal plumbing; a report showing customer_key = 4471 has leaked an implementation detail that means nothing to anyone (Schema Leakage).
• Keep a mapping from natural key to surrogate key as a first-class artefact, because every debugging session and every source migration goes through it.

• A surrogate key guarantees uniqueness of a dimension row if the generation process guarantees it. A sequence does; a hash does up to collision probability; a ROW_NUMBER() recomputed on every full rebuild does not, and that last one is a real and common defect (Idempotent Data Pipelines).
• It guarantees stability against source key churn, and nothing about source *value* churn. If the source changes what a customer id means rather than what it is, no key strategy helps (Semantic Changes).
• It guarantees that a fact's dimension reference is a point-in-time reference, but only because the load resolved it that way. The key itself carries no timestamp and cannot be checked for correctness after the fact.
• It does not guarantee referential integrity. Analytical engines usually do not enforce foreign keys, so a fact can hold a surrogate key that no dimension row has (Database Constraints).