Adding auto incremented identity column to deltalake table

[UsefGamal]

Hi everyone,

I have implemented a complete medallion architecture based on lakehouses delta tables (bronze - silver - gold) , i am trying to add an auto generated identity column which will act as an index for the gold layer. 

There is no problem with the truncate insert merge in the silver layer as i overwrite the the table with the new data.

I am stuck on implementing that fir the silver layer update insert merge (upsert) as the data coming from the bronze lakehouse delta tables with no identity column. And the target delta table (the silver lakehouse delta table) supposed to have that identity column so there will be mismatch in the schema between the two, so what will be the approach to solve this issue

[jwinchell40]

@UsefGamal - Are you using Fabric Notebooks to do the upserts/merge?  In the Fabric Notebooks you can create an auto-incrementing id; using a simliar pattern to what was provided for the data warehouse exmaple.

You can use a window w/monotonically_increasing_id() to create an auto-number function.  Prior to the next data load, you would need to get the last number used for the new seed.

Then when merging your dataframes to prep for the update, you would autonumber the "new" rows and then take your seed value + rownumber to assign a key for the row number.

SImplified explanation above; but it is a process I've been using with success.

[UsefGamal · Accepted solution]

Yes i am using Notebooks to Upserts / merge

And i already solved it by similar approach,  just forgot to tell here.

My current solution was that i added the monotonically_increasing_id column to the delta tables at the bronze layer and added the current date of the data load for uniqiness of values then they update the silver delta tables like any other table with consistent schema between the two delta tables

[v-nikhilan-msft]

Hi @UsefGamal 
Thanks for using Fabric Community.
Currently we cannot create new tables using SQL endpoint of the lakehouse. But you can create unique identifiers in Warehouse tables:
Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft ...

If you need this table in Lakehouse, you can create a shortcut to this table in the desired lakehouse.

Hope this helps. Please let me know if you have any further questions.

[v-nikhilan-msft]

Hi @UsefGamal 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @UsefGamal 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks