topic Re: Row IDs for DLTs in Data Engineering

Row IDs for DLTs

fernandomendi — Tue, 03 Jun 2025 10:37:18 GMT

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!

Re: Row IDs for DLTs

SP_6721 — Tue, 03 Jun 2025 15:14:56 GMT

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.

Re: Row IDs for DLTs

fernandomendi — Tue, 03 Jun 2025 18:04:24 GMT

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