Surrogate keys on Delta at scale: identity columns, their concurrency limits and hash-key alternatives on Azure Databricks

#The problem

A dimension table needs a surrogate key. The obvious answer is GENERATED ALWAYS AS IDENTITY. It works in development. In production, the first time two writers hit the same table (a backfill alongside the nightly job, or two streams feeding one dimension), one of them fails with a concurrent modification error, and retries don't help.

Before you choose identity columns, understand what they cost and what they guarantee.

#What identity columns actually guarantee

From the Databricks documentation:

The concurrency line is what matters at scale. Delta normally lets non-conflicting writers commit in parallel, using optimistic concurrency and, on newer runtimes, row-level conflict detection. Identity values come from a high-water mark stored in table metadata, so every write that generates values changes the same metadata. Two such writes always conflict.

Warning

On a table with an identity column, plan for exactly one writer at a time. That includes MERGE, INSERT, streaming foreachBatch sinks and ad-hoc backfills. Concurrent writers fail rather than corrupt data, but a failed job at 03:00 is still an incident.

#Creating the table correctly

Choose ALWAYS or BY DEFAULT deliberately:

CREATE TABLE prod.gold.dim_customer (
  customer_sk   BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  customer_id   STRING NOT NULL,          -- natural/business key
  customer_name STRING,
  segment       STRING,
  valid_from    TIMESTAMP,
  valid_to      TIMESTAMP,
  is_current    BOOLEAN
)
CLUSTER BY (customer_id);

• GENERATED ALWAYS rejects explicit values. Use it when the table is the only source of truth for keys.
• GENERATED BY DEFAULT accepts explicit values. Use it when you migrate keys from a legacy warehouse and must keep existing numbers. After the load, realign the high-water mark:

-- After inserting explicit keys into a BY DEFAULT column
ALTER TABLE prod.gold.dim_customer ALTER COLUMN customer_sk SYNC IDENTITY;

SYNC IDENTITY moves the high-water mark past the largest existing value, so newly generated keys don't collide with the migrated ones. Run it after every bulk load that supplies explicit keys.

Caution

Don't rebuild a dimension with CREATE OR REPLACE TABLE ... AS SELECT to "refresh" it. CTAS can't carry the identity definition, and a full rebuild reassigns every key. Facts that already reference the old keys then point at the wrong customers. Rebuild only through a migration that maps old keys to new ones.

#MERGE patterns that keep keys stable

Leave the identity column out of the INSERT clause and let Delta generate it:

MERGE INTO prod.gold.dim_customer AS t
USING staged_customers AS s
  ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND (t.customer_name <> s.customer_name OR t.segment <> s.segment) THEN
  UPDATE SET valid_to = s.load_ts, is_current = false
WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, segment, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.customer_name, s.segment, s.load_ts, NULL, true);

Facts look up the key afterwards by joining on the business key and is_current, or on the validity window for late-arriving facts. Because keys aren't contiguous, never derive anything from key arithmetic, such as "last 1,000 customers" or max(sk) - min(sk) as a row count.

#Serialising writers

If a table with an identity column has several producers, serialise them yourself:

• Route all writes through one job and set max concurrent runs = 1 on it.
• Make backfills tasks inside that job rather than separate jobs.
• For streams, write to a staging table and run one MERGE task into the dimension, instead of several streams merging directly.

#When a hash key is the better design

Many lakehouse teams drop sequential keys altogether. A deterministic hash of the business key is computed independently by every writer, needs no coordination, and survives full rebuilds unchanged.

CREATE TABLE prod.gold.dim_customer_h (
  customer_sk   BIGINT NOT NULL,
  customer_id   STRING NOT NULL,
  customer_name STRING,
  segment       STRING
) CLUSTER BY (customer_id);

INSERT INTO prod.gold.dim_customer_h
SELECT xxhash64(source_system, customer_id) AS customer_sk,
       customer_id, customer_name, segment
FROM   prod.silver.customers;

For SCD Type 2, include the version start in the hash, for example xxhash64(source_system, customer_id, valid_from).

Tip

If you choose xxhash64, add a collision check to the load and fail on any hit. It costs one aggregation:

SELECT customer_sk, count(DISTINCT customer_id) AS ids
FROM prod.gold.dim_customer_h
GROUP BY customer_sk HAVING ids > 1;

If it ever returns rows, switch that dimension to sha2 or add the source system to the hash input.

#Why not row_number() plus an offset?

max(sk) + row_number() OVER (ORDER BY ...) gives contiguous keys, but it needs a global sort (one task for the whole batch) and is still unsafe with two writers, since both read the same max. It is the worst of both options at scale.

#Checking an existing table

Find identity columns, and the tables that carry the concurrency restriction, before you design parallel loads:

SELECT table_catalog, table_schema, table_name, column_name,
       identity_generation, identity_start, identity_increment
FROM   prod.information_schema.columns
WHERE  is_identity = 'YES';

SHOW CREATE TABLE prod.gold.dim_customer;   -- shows the GENERATED clause
DESCRIBE HISTORY prod.gold.dim_customer;    -- look for overlapping writers and failed ops

If the information_schema identity columns aren't populated in your build, SHOW CREATE TABLE is the reliable fallback.

#Prevent it next time

• Decide per table: a single-writer dimension can use identity keys. Anything fed by multiple producers gets a hash key.
• Record the decision in the table comment (COMMENT ON TABLE ... IS 'single writer: job dim_customer_load') so nobody adds a second writer later.
• Set max concurrent runs to 1 on every job that writes to an identity table, and keep backfills inside that job.
• Run SYNC IDENTITY after any load with explicit keys, and add the hash collision check to CI for hash-keyed tables.
• Never rebuild an identity-keyed dimension with CTAS. Use MERGE or a planned key migration.