How do you generate surrogate keys for a warehouse dimension when the platform has no reliable sequence?

## What a warehouse surrogate key is for In a dimensional model each dimension row carries an identifier the *model* owns rather than the source system: the surrogate key. Fact rows store that key instead of the source's business key. Two warehouse-specific reasons drive this. First, the source identifier is outside your control — it can be re-issued after a system migration, reformatted, or merged when two customer records are deduplicated. Second, a dimension that keeps history has more than one row per business entity, so the business key can no longer identify a row on its own. The question is not *whether* to mint a key but *how* the value is produced. ## Option 1 — a sequence or identity column A counter is the cheapest key you can have: 4 or 8 bytes, narrow joins, and values that roughly follow load order. The catch in a warehouse is that the value is *assigned at load time*, not derived from the data. Consequences: - **Rebuilds renumber.** A full refresh, a re-run after a bad load, or a migration to another platform gives the same customer a different number, while fact rows already loaded still carry the old one. You must either never rebuild the dimension alone, rebuild dependent facts in the same operation, or persist a key map. - **Assignment serializes.** The fact loader has to look the key up in the dimension, adding a join to every load. - **Ordering is not a contract.** Distributed warehouses hand each loading slice a block of values, so identity columns are typically unique but not gap-free and not ordered. Never sort or range-filter on a surrogate key. ## Option 2 — a deterministic hash Compute the key as a digest over the business key, normalized, and — for a dimension that versions rows — over whatever identifies the version. Same input, same key, forever, on any machine, in any order. That buys three things: rebuilds are safe because the key is derived rather than assigned; the build parallelizes with no central counter; and the fact loader can *compute* a dimension key from columns it already holds instead of joining back. The costs are real. The key is 16-32 bytes (worse if stored as a hex string), and it lands in the fact table, which is the biggest object you own. Keys are opaque, so debugging means joining back to see who a key belongs to. And the key is only as stable as the normalization recipe — change trimming or case rules and every key in the warehouse changes at once. Also note that for a history-keeping dimension the hash must cover the version, so a loader that knows only the business key still needs a point-in-time lookup to decide *which* version a fact belongs to. ```sql -- key derived from data, not assigned by a counter SELECT digest(UPPER(TRIM(customer_id)) || '|' || CAST(effective_from AS VARCHAR)) AS customer_sk FROM stg_customer; ``` ## Option 3 — a random UUID Generated without coordination and available everywhere, but derived from nothing. It shares the sequence's rebuild problem — a re-run mints new values — while being as wide as a hash. In a warehouse this is usually the worst of both, and it mostly appears when the identifier was minted upstream and simply carried in. That case is fine: it is then a *business* key that happens to look like a UUID, and you still decide separately how to key the dimension. ## Choosing - **Single loader, rare full rebuild, storage-sensitive fact tables:** a sequence or identity column, plus a written rule that the dimension is never rebuilt without rebuilding dependent facts. - **Frequent full refreshes, parallel or multi-environment builds, keys that must line up between dev and prod or across systems:** a deterministic hash. - **Anything else:** default to the hash. Reproducibility is worth more than eight bytes per fact row in most shops, and the moment someone re-runs a model you find out which choice you made. ## Rules that hold either way Keep the business-key columns physically in the dimension row: the surrogate is for joining, the business key is for reconciling against the source and for a human reading a row. Reserve fixed key values for the unknown and not-applicable members so facts never carry a NULL dimension reference. Publish the key recipe as a contract — anything downstream that stores a key depends on it. And never expose a surrogate value in a report or a URL: it is an internal join token that can legitimately change when the model is rebuilt.