topic How to Update Identity Column for a Databricks Table in Data Engineering

How to Update Identity Column for a Databricks Table

LearnDB1234 — Tue, 29 Apr 2025 18:00:31 GMT

Hi All,

I have a databricks table with the below DDL:

CREATE TABLE default.Test ( ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), StopFromDateTime TIMESTAMP, StopToDateTime TIMESTAMP, User STRING) USING delta TBLPROPERTIES ( 'delta.autoOptimize.autoCompact' = 'true', 'delta.autoOptimize.optimizeWrite' = 'true', 'delta.columnMapping.mode' = 'name', 'delta.minReaderVersion' = '2', 'delta.minWriterVersion' = '6')

I have inserted 3 rows of data into the table and the table with the data looks like below:

ID StartDateTime StopDateTime User 1 2020-01-01 2020-01-02 Josh 2 2021-01-01 2021-01-02 Alex 3 2022-01-01 2022-01-02 Ross

When i look at the history of the table it has 4 versions, 1(Create),2,3&4(Insert). I had to delete the records for ID 2&3 so instead of deleting it i did a restore of the table to version 2 which has the first insert so my table now has the data like this

ID StartDateTime StopDateTime User 1 2020-01-01 2020-01-02 Josh

I have inserted another record into the table now for and after inserting the new record my table looks like this:

ID StartDateTime StopDateTime User 1 2020-01-01 2020-01-02 Josh 4 2023-01-01 2023-01-02 Steve

I was hoping the new ID would be 2 as i restored it to a version before ID 2&3 are inserted however its getting 4.

Can someone provide any inputs on how can i reset the identity on this table?

Re: How to Update Identity Column for a Databricks Table

Walter_C — Wed, 30 Apr 2025 12:36:00 GMT

When you restored your Delta table to an earlier version (version 2), you observed that the next inserted row received an ID of 4, not 2. This is because Delta Lake’s identity column maintains a high watermark of the last value used, even across restores. The identity generator is designed to guarantee uniqueness and avoid assigning duplicate values, so it will not reissue previously used identity values-even if the underlying data is reverted to a prior state.

By default, there is no built-in command to reset the identity column’s counter in a Delta table. The identity column will always continue from the highest value it has ever assigned, regardless of table restores, deletes, or truncations.

This behavior is intentional to prevent the risk of duplicate identity values, which could otherwise occur if the counter were allowed to rewind.

The only supported and safe way to reset the identity column is to recreate the table.

Re: How to Update Identity Column for a Databricks Table

LearnDB1234 — Wed, 30 Apr 2025 13:07:54 GMT

Hi Walter,

Thanks for the response, so when i drop and re-create the table i will be losing the data correct? So how can i move the data over if i have to drop and re-create? Or if i restore it to the version number which had 3 records and then delete 2&3 it would still start at 4 right? So any suggestions on how to drop and re-create the table and not lose the data?

Re: How to Update Identity Column for a Databricks Table

pdiamond — Thu, 08 May 2025 14:53:29 GMT

If you recreate the table using BIGINT GENERATED BY DEFAULT instead of BIGINT GENERATED ALWAYS you can manipulate the column values.

"When using the clause GENERATED BY DEFAULT AS IDENTITY, insert operations can specify values for the identity column."

https://docs.databricks.com/aws/en/delta/generated-columns#use-identity-columns-in-delta-lake