does delta live tables supports identity columns

[karthik_p]

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"

[karthik_p]

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

[nicole_o]

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.

[karthik_p]

@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)

[Gowrish]

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?

[Gowrish]

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