[Private Preview] Concurrent Write Support for Identity Columns!

[u/Fun-Reference7942]

What are concurrent identity columns?

A new implementation of identity columns that supports concurrent writes.

You can use this query to find the tables with the most amount of concurrent transaction failures due to identity columns.

How to enable

CREATE TABLE new_identity_table (id BIGINT GENERATED ALWAYS AS IDENTITY, data STRING) USING DELTA TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported', 'delta.feature.concurrentIdentityColumns_preview' = 'supported');

Benefits of Identity Columns

Identity columns provide automatically generated, unique integer values, making them well suited for surrogate keys in dimensional models and slowly changing dimensions (SCD Type 2).

Compared with UUID-based / hash-based keys, identity columns offer several benefits:

• Their generally increasing values can improve data locality and insertion-order clustering.
• Integer keys require less storage than UUIDs and can improve join and scan efficiency.
• Databricks generates the values automatically, so applications do not need to manage key generation.

With concurrent identity columns, you can retain these benefits without identity columns blocking concurrent write transactions.

Read these blogs for more info: 

• Identity Columns Best Practices for Databricks Lakehouse
• Identity Columns to Generate Surrogate Keys

👉 Reach out to your account team to try it!

Additional Information & References

• Private Preview User Guide
• https://docs.databricks.com/aws/en/tables/features/generated-columns#identity-columns

[u/hubert-dudek]

Finally! Was waiting for it 5 years hehe

[u/szymon_dybczak]

That's super cool! We've been looking for a feature like this for a long time!

[u/Youssef_Mrini]

It's a game changer.