Identity Columns Best Practices for Databricks Lakehouse

[LaurentLeturgez]

The surrogate key problem nobody plans for

Sooner or later every data warehouse needs surrogate keys. You are building a dimension table and you want a single narrow column your fact tables can join on instead of a wide composite of source attributes, or when using SCD Type2 which is very important in every data warehouse. In a lot of systems, that means a sequence object, an auto-increment column, or a stored procedure that hands out the next number, plus a chunk of ETL whose only job is to manage all of it.

Databricks Lakehouse has identity columns built into Delta tables, so most of that machinery just goes away. There is no separate sequence object to create, no key table to maintain, and no extra step in your data pipelines that exists purely to assign IDs.

The catch is that identity columns do not behave exactly like the auto-increment you may be used to. If you assume they do, you will spend a frustrating afternoon figuring out why your row count and your maximum key value disagree. So before you reach for them, it helps to know what they actually do.

A note on identity column on Iceberg v3: Identity columns are a Delta feature, but  Apache Iceberg (incl. the v3 spec) has no GENERATED ... AS IDENTITY. Two workarounds worth a sentence: (1) keep the table as Delta with UniForm (Iceberg) so Iceberg readers still get the identity column; or (2) on native Iceberg, generate surrogate keys yourself, hash keys via xxhash64()/sha2() (idempotent, ideal for MERGE/SCD2) or uuid(). Note: Iceberg v3 row lineage (_row_id) is for change tracking, not a user-controlled surrogate key.

What you get with an identity column

You declare the column once, at table creation, and the table generates the values:

CREATE TABLE dim_customer (
  customer_sk BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL, 
  customer_id STRING,          -- business key from the source system
  customer_name STRING,
  country STRING
);

Two things are fixed and worth memorizing. The column has to be BIGINT. And you choose between two modes:

• GENERATED ALWAYS AS IDENTITY: the table always assigns the value. If you try to insert your own number into that column, the statement fails.

For example, the following statement creates 3 rows

INSERT INTO dim_customer(customer_id, customer_name, country) VALUES
('1', 'John Doe', 'USA'),
('2', 'Jane Smith', 'Canada'),
('3', 'Bob Johnson', 'Mexico');

When we try to assign a value to the customer_sk column, this raises an exception:

INSERT INTO dim_customer(customer_sk, customer_id, customer_name, country) VALUES
(42,'4', 'Raoul Ménard', 'France');

[DELTA_IDENTITY_COLUMNS_EXPLICIT_INSERT_NOT_SUPPORTED] Providing values for GENERATED ALWAYS AS IDENTITY column customer_sk is not supported. SQLSTATE: 42808

• GENERATED BY DEFAULT AS IDENTITY: the table assigns a value when you leave the column out, but you are allowed to insert an explicit value when you want to.

In this scenario, we can force a key assignment:

CREATE TABLE dim_customer (
 customer_sk BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
 customer_id STRING,          -- business key from the source system
 customer_name STRING,
 country STRING
);

INSERT INTO dim_customer(customer_id, customer_name, country) VALUES
('1', 'John Doe', 'USA'),
('2', 'Jane Smith', 'Canada'),
('3', 'Bob Johnson', 'Mexico');

INSERT INTO dim_customer(customer_sk, customer_id, customer_name, country) VALUES 
(42,'4', 'Raoul Ménard', 'France')

select * from dim_customer;
+-------------+-------------+---------------+---------+
| customer_sk | customer_id | customer_name | country |
+-------------+-------------+---------------+---------+
| 1           | 1           | John Doe      | USA     |
| 2           | 2           | Jane Smith    | Canada  |
| 3           | 3           | Bob Johnson   | Mexico  |
| 42          | 4           | Raoul Ménard  | France  |
+-------------+-------------+---------------+---------+
4 rows in set

You can also set a starting point and step:

customer_sk BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 5)

Finally, it’s a good practice to declare a NOT NULL constraint on the identity column in order to set a PRIMARY KEY later on.

That is the whole surface area. The interesting part is how it behaves under load, and that is where the best practices become crucial.

Note on widening to BIGINT: because an identity column must be BIGINT, migrating a table whose surrogate key was a narrower integer (for example INT) means widening it. Delta's type widening (delta.enableTypeWidening) is a metadata-only change,  it does not rewrite the existing data files, so the engine coerces the old narrow values to BIGINT at read time and some data-skipping benefits shrink until the files are rewritten. On a large table, run OPTIMIZE (or REORG) after widening so the values are physically stored as BIGINT and that read-time conversion cost goes away.

Use them for surrogate keys, not business keys

A surrogate key has no meaning. It exists so your fact tables can join to your dimensions via a single narrow column. That is exactly the job an identity column is good at.

In real systems, business keys change: a CRM migration renames CRM-1001 to CRM-NEW-1001 because of company mergers, or composite keys grow. If your fact table joined on the business key, every historical row would need updating, or worse, silently break.

With a surrogate key, the fact table is immune to business-key changes. Below we simulate a CRM migration that changes customer_id, then show that fact history still joins correctly because customer_sk never moves.

Simulate a CRM migration: Alice's business key changes, surrogate stays the same

UPDATE dim_customer
SET customer_id = 'CRM-NEW-1001'
WHERE customer_id = 'CRM-1001';

+-------------------+
| num_affected_rows |
+-------------------+
| 1                 |
+-------------------+

The fact table still joins perfectly. It references the SURROGATE, not the business key

SELECT
 f.order_id,
 d.customer_sk,
 d.customer_id   AS current_business_key,
 d.customer_name,
 f.order_date,
 f.amount
FROM fact_orders f
JOIN dim_customer d ON f.customer_sk = d.customer_sk
ORDER BY f.order_date;

+----------+-------------+----------------------+---------------+------------+--------+
| order_id | customer_sk | current_business_key | customer_name | order_date | amount |
+----------+-------------+----------------------+---------------+------------+--------+
| 1        | 1           | CRM-NEW-1001         | Alice Martin  | 2026-06-01 | 150.00 |
| 2        | 2           | CRM-1002             | Bob Chen      | 2026-06-05 | 230.50 |
| 3        | 1           | CRM-NEW-1001         | Alice Martin  | 2026-06-10 | 89.99  |
+----------+-------------+----------------------+---------------+------------+--------+

If the fact table had stored the BUSINESS KEY instead, after the CRM migration, the old key no longer matches and rows vanish from the join.

What if the fact table had stored the BUSINESS KEY instead?

After the CRM migration, the old key no longer matches → rows vanish from the join.

SELECT
 f.order_id,
 'CRM-1001' AS old_business_key_in_fact,  -- what fact would have stored
 d.customer_name,
 f.order_date,
 f.amount
FROM fact_orders f
JOIN dim_customer d
 ON 'CRM-1001' = d.customer_id   -- OLD key no longer exists in dim → no match!
ORDER BY f.order_date;

Query OK
Time: 0.531s

ALWAYS or BY DEFAULT depends on your data

Most of the time you need GENERATED ALWAYS. It stops anyone from sneaking a manual value in and corrupting the sequence later, which is exactly what you want for a key the warehouse owns end-to-end.

You reach for GENERATED BY DEFAULT when you need to load rows that already carry their own keys. Maybe you are consolidating two warehouses, or seeding a dimension with values another system assigned. In that case you carry the keys across:

CREATE TABLE dim_customer (
  customer_sk BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  customer_id STRING,
  customer_name STRING,
  country STRING
);


-- preserve keys that were already assigned elsewhere
INSERT INTO dim_customer (customer_sk, customer_id, customer_name, country)
SELECT source_sk, customer_id, customer_name, country
FROM   source_export;

There is a gotcha here. After that load, the table does not know you inserted values manually. Its internal counter still thinks the next value is 1, so the first auto-generated key will collide with your loaded data. Fix it with one statement:

ALTER TABLE dim_customer ALTER COLUMN customer_sk SYNC IDENTITY;

SYNC IDENTITY reads the real maximum in the column and moves the counter above it. New rows now get keys that sit safely past the data you loaded. It is a single command, and it is easy to forget right up until two rows fight over the same key.

Expect gaps, and do not read meaning into them

This is the behavior that surprises people. Identity values are unique and they increase. They are not contiguous.

Identity values are generated in parallel (within a single write) across Spark tasks rather than from a single shared counter. Each task works from its own slice of a pre-determined range, and any values a task doesn’t use are simply skipped. That’s why you’ll see gaps. The values are guaranteed to be unique and increasing, but never contiguous, so don’t read meaning into the jumps or rely on the sequence being gap-free.

What this means in practice:

• Do not use MAX(customer_sk) to count your rows. Use COUNT(*).
• Do not assume key 5000 was loaded before key 5001 in wall-clock time. If you need load order, add a timestamp column and trust that instead.
• Do not expose the surrogate key to end users as a stable external identifier. Rebuild the table, or create it in a different environment, and the numbers change.

None of this is a defect. It only causes trouble when someone quietly depends on properties the key was never supposed to have.

What’s coming next

Today, identity generation runs in parallel within a single write, but multiple writers inserting into the same identity column simultaneously are not yet supported. We’re working on lifting that limitation so independent jobs can insert concurrently. More details to come.

Match upserts on the business key

Identity columns and MERGE work well together as long as you remember what the surrogate key is for. You match on the business key and let the insert branch generate the surrogate:

MERGE INTO dim_customer t
USING staged_updates s
ON    t.customer_id = s.customer_id      -- match on the business key
WHEN MATCHED THEN
  UPDATE SET customer_name = s.customer_name, country = s.country
WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, country)
  VALUES (s.customer_id, s.customer_name, s.country);

Notice the insert leaves customer_sk out entirely. The table fills it in. If you ever find yourself joining on the surrogate key inside a MERGE condition, stop and check your logic, because the surrogate does not exist for those new rows yet.

SCD Type 2 is where surrogate keys earn their keep

Overwriting a row in place in a dimension table is what is called the SCD Type 1. It is fine when you only care about the current state. But plenty of dimensions need history: when did this customer move countries, and what was their segment when that order was placed? That is a slowly changing dimension Type 2; instead of overwriting the row, you close it off and add a new one.

The moment you do that, the business key stops being unique. One customer_id now has several rows, one per version, so you can no longer join a fact to a customer on the business key alone; it no longer identifies a single row. This is the textbook case for surrogate keys, and it shows exactly why they earn their keep: the surrogate identifies one specific version, so your fact table points at the version that was current when the event happened, not at the customer in general, and the identity column mints a fresh key for every version for free.

If you build this SCD Type 2 logic, we encourage you to do so in a Spark Declarative Pipeline or via a Streaming Table in Databricks SQL instead of hand-writing the expire-and-insert; AUTO CDC does both steps for you. You declare the keys, the column to sequence by, and the SCD type, and AUTO CDC closes the current version and opens a new one on every change.

And identity columns are fully supported here: you declare the surrogate key directly on the streaming table that AUTO CDC writes into, exactly as you would on any other Delta table. Declare customer_sk once, never assign it yourself, and let the table mint a fresh key for every version.

-- Ingest the raw CDC events from the landing volume with Auto Loader
CREATE OR REFRESH STREAMING TABLE staged_changes
AS SELECT * FROM STREAM read_files(
  '/Volumes/main/sales/cdc_landing/staged_changes',
  format => 'json',
  schemaHints => 'change_ts TIMESTAMP'
);

-- Declare the SCD Type 2 target (identity surrogate key) and its AUTO CDC flow in one statement
CREATE OR REFRESH STREAMING TABLE dim_customer
(customer_sk   BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
 change_ts     TIMESTAMP,
 customer_id   STRING,      -- the business key
 customer_name STRING,
 country       STRING,
 __START_AT    TIMESTAMP,   -- SCD Type 2: row valid-from
 __END_AT      TIMESTAMP)   --SCD Type 2: row valid-to (NULL = current)
TBLPROPERTIES (pipelines.channel = 'PREVIEW')
FLOW AUTO CDC -- AUTO CDC does the expire-and-insert for you
FROM STREAM(staged_changes)
KEYS (customer_id)          -- the business key
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY change_ts       -- how to order the changes
COLUMNS * EXCEPT (operation, _rescued_data)
STORED AS SCD TYPE 2;

The surrogate key lives right on the streaming table AUTO CDC populates. customer_sk never appears in the data you feed in: AUTO CDC expires the old version, inserts the new one, and the identity column hands each version its own key. It also tracks the validity window for you in __START_AT and __END_AT (Please note that we must explicitly declare these columns because we used a explicit table schema).

The principle is unchanged: the business key tells you who, the surrogate tells you which version, and you let the table generate the key for free. The declarative pipeline just does the expire-and-insert bookkeeping for you, with no deterministic-hash workaround required. If you are not running a Declarative Pipeline, you can build the very same result by hand.

The same pattern using MERGE statement

Declarative Pipelines with AUTO CDC are the approach we recommend, and for most teams they are all you will ever need (it also works very well on a Databricks Lakehouse endpoint). Even so, it is worth seeing the same expire-and-insert spelled out with MERGE: it runs anywhere you have Delta, it is handy whenever a pipeline isn't driving the load with AUTO CDC, and writing the two steps by hand makes the surrogate key's role explicit. The design is identical to what the pipeline did for you above; MERGE just puts the moving parts in plain sight.

The surrogate key does the same job here: it pins down one specific version, while the business key, customer_id, now repeats across the versions of a customer. Your facts still join to the version that was current at event time, never to the customer in general.

You declare the table the same way you always would, an identity surrogate key alongside the business key, plus the validity window the pipeline maintained for you automatically:

CREATE TABLE dim_customer (
  customer_sk   BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
  customer_id   STRING,        -- business key, repeats across versions
  customer_name STRING,
  country       STRING,
  valid_from    TIMESTAMP,
  valid_to      TIMESTAMP,
  is_current    BOOLEAN
);

Loading it is a two-step job. First, expire the current row whose attributes have changed. Then insert the new version, leaving customer_sk out so the table mints a fresh key:

1. Close the old version
   
   

   MERGE INTO dim_customer t
   USING staged_changes s
   ON t.customer_id = s.customer_id AND t.is_current = true
   WHEN MATCHED AND (t.country <> s.country OR t.customer_name <> s.customer_name) THEN
     UPDATE SET is_current = false, valid_to = s.change_ts;

2. Add the new version (and any brand-new customers)

INSERT INTO dim_customer (customer_id, customer_name, country, valid_from, valid_to, is_current)
SELECT customer_id, customer_name, country, change_ts, NULL, true
FROM   staged_changes;

The pattern matches everything earlier in this post. You find the right history with the business key. You let the identity column generate the surrogate for every version. The business key tells you who; the surrogate key tells you which version of them. And because gaps in the key never mattered, the fact that each version grabs the next free number bothers nobody.

Document the model with constraints

Databricks Lakehouse supports primary key and foreign key constraints as informational metadata. They are not enforced at write time, but they document the model and the query optimizer can use them.

ALTER TABLE dim_customer
  ADD CONSTRAINT pk_dim_customer PRIMARY KEY (customer_sk);

This is documentation that lives in the catalog instead of a forgotten wiki page. Add the foreign keys from your fact tables back to the dimensions while you are at it, so anyone new to the warehouse can read the model straight from the schema.

Where identity columns do not belong

A few places to keep them out of:

• Don’t use the identity columns or surrogate keys in your clustering fields. Cluster on the columns you actually filter by, like a date, or better use an automatic clustering (CLUSTER BY AUTO) and enable predictive optimization.
• High-throughput streaming ingestion. A single Structured Streaming append still works: leave the column out and let the table mint the key, but expect much larger gaps. High-throughput ingestion paths like Zerobus currently do not support identity columns, so keep it off those targets and assign the surrogate downstream.
  
  

The takeaway

Identity columns let the table do the boring work of handing out keys, and they do it without the sequence objects and helper ETL you would otherwise build and babysit.

Let the table generate the keys. Match your ETL pipelines on business keys. Treat the surrogate as what it is, a fast internal join key and nothing more. Get those three things right and surrogate keys go back to being a detail you never have to think about.

If you are coming from a legacy data warehouse, treat the move as an opportunity to simplify rather than just port. The Oracle sequence, the SQL Server IDENTITY, the trigger that fires on every insert and the little key table someone stood up years ago and nobody dares touch … none of that needs to come across. Replace this with one GENERATED AS ALWAYS IDENTITY column and delete the ETL that used to feed it. A platform modernization is the cheapest opportunity you will ever get to drop that technical debt/complexity.

Call to action: try it on your next dimension table.

Pick one dimension and build it with an identity column. Use GENERATED ALWAYS if the warehouse owns the keys, or GENERATED BY DEFAULT followed by SYNC IDENTITY if you're loading values that already exist. Point a fact table at it, run a MERGE, and watch it stay clean. Once you've seen it work end to end, the rest of your model is just repetition.

No workspace? No problem. Everything in this post runs on any Databricks workspace and if you don't have one, spin up a free Databricks Free Edition account in a couple of minutes and paste the CREATE TABLE statements straight into a SQL editor or notebook. No cloud account, no credit card, no cluster setup — just open a query and start generating keys.

Go deeper:

- Identity columns in Databricks: the full syntax reference for GENERATED ALWAYS/BY DEFAULT, START WITH, INCREMENT BY, and SYNC IDENTITY
- AUTO CDC APIs in Lakeflow Declarative Pipelines let the pipeline do the SCD Type 2 expire-and-insert for you
- Implementing a dimensional data warehouse with Databricks SQL: the surrogate-key patterns in a full end-to-end warehouse build