Hash Keys vs Identity Columns: Choosing Surrogate Keys in the Lakehouse

Surrogate keys are one of those design decisions that seem straightforward until you need to rebuild data, backfill history, or optimize a large semantic model.

In a traditional data warehouse, integer surrogate keys generated by sequences or identity columns have long been the default choice. In modern lakehouse platforms such as Databricks and Microsoft Fabric, however, deterministic hash keys have become increasingly common because they simplify distributed processing and reprocessing scenarios.

Both approaches solve the same problem, but they come with very different trade-offs around idempotency, load ordering, storage, join performance, and downstream BI consumption.

This post compares hash-based surrogate keys and identity columns from a practical lakehouse perspective and discusses where each approach works best.

The two approaches:

• Hash key — derive the surrogate deterministically from the business key, e.g. sha2(concat_ws('|', cols...), 256) or xxhash64(concat_ws('|', cols...)).
• Identity column — let the engine generate a monotonic integer, via GENERATED BY DEFAULT or GENERATED ALWAYS AS IDENTITY.

#The one difference everything hangs on

A hash key is a pure function of the business key. Same input, same key, forever, on any cluster, in any environment, on any re-run.

An identity value is a function of when the row was written. Load order, reload, concurrency, and environment all change it.

#Hash keys

-- Deterministic surrogate from the business key.
-- xxhash64 hashes the columns directly into a 64-bit BIGINT,
-- so the "ab"+"c" vs "a"+"bc" delimiter trap goes away.
-- Still normalize first: trim, upper/lower per your collation, and a fixed token for NULLs.
SELECT
    xxhash64(
        upper(trim(customer_source)),   -- system of origin
        upper(trim(customer_id))        -- natural/business key
    ) AS customer_sk,
    *
FROM bronze.customers;

-- Need collision-proofing at extreme scale? sha2(concat_ws('|', ...), 256)
-- is the alternative: wider (32-byte) but cryptographically collision-safe.

Pros

• Deterministic and idempotent. Re-run the batch, backfill six months, rebuild from scratch — the keys are identical every time. Reprocessing stops being scary.
• No coordination. Every Spark task computes keys independently. No sequence, no shuffle to assign IDs, no single point that serializes your write.
• Decoupled fact and dimension loads. You can compute a fact's foreign key by hashing the business key on the fact side, without first loading the dimension to look up its integer. Load them in parallel; late-arriving dimension rows still line up.
• Stable across environments. Dev, test, and prod produce the same key for the same customer, which makes reconciliation and row-level diffing trivial.

Cons

• Width depends on the hash function. xxhash64 returns a 64-bit BIGINT — the same 8 bytes as an identity key, so there's no width or join penalty. A cryptographic hash like sha2(..., 256) is 32 bytes, which does cost storage, compression, and join speed — the price of collision safety. Pick the function for the scale you're at.
• It still bloats the semantic model. This is the one that surprises people, and even as a 64-bit integer it holds. A hash is effectively random and high-cardinality, and VertiPaq — the engine behind Power BI and Analysis Services tabular models — compresses random integers far worse than the dense, sequential integers an identity column produces. A sequential key packs into a tiny bit range with long run-length runs; a hash key needs a large dictionary and gets almost no run-length benefit, so the relationship columns on both the fact and dimension side stay big. On a wide fact table this can inflate the imported model size several-fold and drag query and refresh performance with it. If your Gold layer feeds a Power BI import model, this cost alone can decide the argument.
• Not human-friendly. No ordering, no range scans, no "this ID is newer than that one." Debugging a join by eye is miserable.
• Fragile to input normalization. Trailing spaces, casing, collation, NULL handling, and (for sha2) delimiter choice all change the hash. Get the normalization contract wrong once and you've silently minted duplicate dimension members. Standardize it in one place and never touch it casually.
• Collisions are a real tradeoff. xxhash64 is fast and compact but non-cryptographic and only 64-bit — at billions of rows the birthday-bound collision probability stops being negligible. If a single collision silently merging two dimension members is unacceptable, move to sha2(..., 256) and accept the wider key, or carry the business key as a tiebreaker.

#Identity columns

-- Monotonic integer surrogate. GENERATED ALWAYS blocks manual inserts;
-- GENERATED BY DEFAULT lets you override (useful for migrating historical keys).
CREATE TABLE silver.dim_customer (
    customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_source STRING,
    customer_id STRING
);

-- After manual inserts into a BY DEFAULT column, the sequence can drift
-- behind the real max and collide on the next insert. Realign it:
ALTER TABLE silver.dim_customer ALTER COLUMN customer_sk SYNC IDENTITY;

Pros

• Compact and fast. An 8-byte BIGINT joins faster, compresses better, and keeps your star-schema fact tables lean. For BI-facing Gold marts this genuinely matters.
• Familiar. Modelers and BI tools expect integer surrogate keys. It's the path of least resistance for a Kimball-style warehouse.
• Ordered. Monotonic values give you a cheap sense of insertion order and support range operations.

Cons

• Non-deterministic across reloads. A rebuild hands out different numbers; dev and prod disagree; any downstream system holding those integers is now wrong. Databricks guarantees IDENTITY values are unique and increasing — not consecutive, and not stable across an overwrite.
• Load-order coupling. Facts can't resolve their foreign key until the dimension exists and has been assigned its integer. That forces dimension-before-fact ordering and a surrogate-key lookup join on every fact load. More coupling, more orchestration, more ways to deadlock a backfill.
• Not idempotent. Re-running a load can mint new keys or duplicate members unless you carefully guard with MERGE — and even then the sequence bookkeeping (SYNC IDENTITY) is a sharp edge, especially after manual inserts or concurrent writers.

#Decision matrix

Dimension	Hash key	Identity column
Deterministic / idempotent	✅ Always	❌ Changes on reload
Parallel/distributed writes	✅ No coordination	⚠️ Wants a coordinator
Fact/dim decoupling	✅ Compute FK from business key	❌ Must look up the dim
Stable across environments	✅ Same everywhere	❌ Per-load
Join performance	✅ 8 bytes with xxhash64	✅ 8-byte BIGINT
Storage / compression	⚠️ Random distribution	✅ Sequential, compact
Semantic model (Power BI / VertiPaq) size	❌ Random 64-bit key	✅ Compresses tightly
Human-readable / ordered	❌ Opaque	✅ Monotonic
Reprocessing / backfill	✅ Safe	⚠️ Fragile

#Conclusion: pick by scenario

Neither key is "better." They're optimized for different problems, and the right call depends on where the key lives and what has to be true about it. Match the scenario:

• Distributed Spark writes, multiple environments, pipelines you reprocess. Hash key. Determinism and no-coordination are worth far more than the extra bytes, and you avoid the whole "the reload broke the joins" class of incident.
• Parallel fact and dimension loading, or late-arriving dimensions. Hash key. Computing the foreign key from the business key lets the two loads run independently.
• Reconciliation across dev/test/prod, or row-level diffing between systems. Hash key. The same business key producing the same surrogate everywhere is the whole game.
• A Gold star schema behind a Power BI import model. Integer surrogate. VertiPaq compresses a dense, sequential key far better than a random hash, so the relationship columns stay lean — a random 64-bit hash key can inflate the model several-fold.

#Related reading

• GENERATED BY DEFAULT vs GENERATED ALWAYS in Databricks — the identity-column mechanics and the SYNC IDENTITY drift problem in detail.