Row IDs for DLTs

[fernandomendi]

Hi all,

I have a DLT pipeline where I am reading from a cloud source and want to mode data through some tables onto a final Gold layer table. I would like to use SQL to write my DLTs. I would also like to have a row_id for each row to identify each independently.

• I have tried enabling delta.enableRowTracking then querying _metadata.row_id but I get an UNRESOLVED_COLUMN error.
• I have also tried to use IDENTITY columns which appear to be correctly created in the table definition in UC but just get set to "null" when I run an update.

Can anyone help here?

Many thanks in advance!

[SP_6721]

Hi @fernandomendi 

In Delta Live Tables (DLT), if you want to assign a unique identifier to each row, enabling delta.enableRowTracking and selecting _metadata.row_id directly in your SQL query is a valid approach, just be sure to include it explicitly to avoid UNRESOLVED_COLUMN errors. As for identity columns, they aren’t supported for updates or merges and will return NULL when used in that context. They’re intended for use in streaming tables, so it's best to avoid updating them.

[fernandomendi]

Thanks for the reply but I am still facing problems,
When I try to query _metadata.row_id directly in SQL I still get the same error (see attached screenshots).