How to Update Identity Column for a Databricks Table

[LearnDB1234]

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?

[Walter_C · Accepted solution]

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. 

[LearnDB1234]

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?

[pdiamond]

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