topic Re: does delta live tables supports identity columns in Data Engineering

does delta live tables supports identity columns

karthik_p — Fri, 01 Dec 2023 17:13:34 GMT

we are able to test identity columns using sql/python, but when we are trying same using DLT, we are not seeing values under identity column. it is always empty for coloumn we created "id BIGINT GENERATED ALWAYS AS IDENTITY"

Re: does delta live tables supports identity columns

karthik_p — Sun, 03 Dec 2023 16:19:32 GMT

Hi @Retired_mod @above example is for materialized view and also it uses String type and as per documentation DLT, identity column only supports

1. streaming use case

2. sql syntax streaming is not supported

I am not seeing any python example to add column on that fly or update. Also streaming uses inference, I tried to use merge schema but still same issue. If we are able to change schema on the fly for python that should confirm about identity column please

Re: does delta live tables supports identity columns

karthik_p — Wed, 06 Dec 2023 16:22:17 GMT

@Retired_mod thank you for quick response, we are able to generate for streaming and materialized views. but only confusion that i am seeing is, in terms of limitations that are mentioned in DLT

Identity columns are not supported with tables that are the target of APPLY CHANGES INTO and might be recomputed during updates for materialized views. For this reason, Databricks recommends only using identity columns with streaming tables in Delta Live Tables. See Use identity columns in Delta Lake.

Based on above limitation, does this means do we need to use only for streaming use cases. when we tested for creation of Materialized Views also it worked ( data from data frames are passed into MV)

Re: does delta live tables supports identity columns

nicole_o — Wed, 03 Jan 2024 19:19:39 GMT

Hi Kaniz,

I tried the solution above with streaming tables. I defined sales as returning a streaming table, but the identity columns are not populated. They always show as null. Any help is much appreciated.

Re: does delta live tables supports identity columns

Gowrish — Fri, 29 Aug 2025 00:03:32 GMT

Hi @karthik_p,

I have no success in having identity column working for a streaming table. Would you be able to share an example if it working for you?

Re: does delta live tables supports identity columns

Gowrish — Fri, 29 Aug 2025 02:03:22 GMT

Hi,

i see from the following databricks documentaion - https://docs.databricks.com/aws/en/dlt/limitations

it states the following which kind of giving an impression that you can define identity column to a steaming table

Identity columns might be recomputed during updates to a materialized views. Because of this, Databricks recommends using identity columns in Lakeflow Declarative Pipelines only with streaming tables.

however streaming table syntax has no mention of identity column. and in the limitations section, it states that identity columns are not supported

https://docs.databricks.com/aws/en/dlt-ref/dlt-sql-ref-create-streaming-table#limitations

i guess either one if the documentation needs to be fixed to avoid confusion for starters like me