How to enable row tracking on Delta Live Tables?

[TejeshS]

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;

[Alberto_Umana]

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

AS

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

[TejeshS]

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

[Alberto_Umana]

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

[TejeshS]

This only works on Delta tables.

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

[Alberto_Umana]

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.

[TejeshS]

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.

• Delta Live Tables SQL language reference | Databricks on AWS 
• Delta Live Tables properties reference | Databricks on AWS

[TejeshS]

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.