topic Re: How to enable row tracking on Delta Live Tables? in Data Engineering

How to enable row tracking on Delta Live Tables?

TejeshS — Mon, 06 Jan 2025 13:47:48 GMT

We are encountering a scenario where we need to enable support for Incremental Processing on Materialized views having DLT base tables. However, we have observed that the compute is being executed with the COMPLETE_RECOMPUTE mode instead of INCREMENTAL. Therefore, we need to activate row tracking functionality on the DLT base tables utilized within the Materialized views.

Query Example ->

CREATE OR REPLACE MATERIALIZED VIEW catalog_name.gold_schema.test_mv
AS
SELECT col_a, col_b
FROM catalog_name.silver_schema.base_dlt_table;

Re: How to enable row tracking on Delta Live Tables?

Alberto_Umana — Mon, 06 Jan 2025 13:52:28 GMT

Hello @TejeshS,

Thanks for your question!

You need to set the delta.enableRowTracking property to true on the DLT base table. This can be done using the TBLPROPERTIES clause when creating or altering the table.

Example:

CREATE OR REPLACE TABLE catalog_name.silver_schema.base_dlt_table

TBLPROPERTIES ('delta.enableRowTracking' = 'true')

SELECT col_a, col_b

FROM source_table;

Once row tracking is enabled on the base table, you can create the materialized view as usual. The materialized view will now be able to use the row tracking information to perform incremental refreshes

Re: How to enable row tracking on Delta Live Tables?

TejeshS — Mon, 06 Jan 2025 13:55:55 GMT

What to do, if tables are already created. I don't see any ALTER command support for DLT tables for handling TBLPROPERTIES

Re: How to enable row tracking on Delta Live Tables?

Alberto_Umana — Mon, 06 Jan 2025 14:25:05 GMT

Hi @TejeshS,

Can you try with:

ALTER TABLE catalog.schema.table
SET TBLPROPERTIES (delta.enableRowTracking = true);

Also you can see this by running DESCRIBE DETAIL on the table

Re: How to enable row tracking on Delta Live Tables?

TejeshS — Mon, 06 Jan 2025 15:05:46 GMT

This only works on Delta tables.

When we try ALTER table command on DLT it throws an error -

Re: How to enable row tracking on Delta Live Tables?

Alberto_Umana — Mon, 06 Jan 2025 15:39:41 GMT

Oh I see, sorry! the only way would be, but will recreate the table.

CREATE OR REPLACE TABLE catalog.schema.table
TBLPROPERTIES (delta.enableRowTracking = true);

However, this command will replace the existing table with a new one that has the delta.enableRowTracking property enabled. Note that this approach will recreate the table.

Re: How to enable row tracking on Delta Live Tables?

TejeshS — Mon, 06 Jan 2025 16:10:42 GMT

We are using a custom DLT framework and require an automated approach to enable this property. Additionally, based on the available documentation, it seems that this property is not directly accessible for enabling.

Re: How to enable row tracking on Delta Live Tables?

TejeshS — Mon, 06 Jan 2025 16:18:14 GMT

Moreover, we have CDF enabled DLT tables, but as per documentation we see a limitation if CDF is enabled then row Tracking won't be possible. Use row tracking for Delta tables | Databricks on AWS

But as per our use case we need incremental processing of Materialized Views based on DLT tables.