Incremental refresh of materialized view in serverless DLT

[SharathE]

Hello, Every time that I run a delta live table materialized view in serverless , I get a log of "COMPLETE RECOMPUTE" . How can I achieve incremental refresh in serverless in DLT pipelines?

[MehdiModarressi]

Hi @SharathE 

Incremental refresh in DLT Materialized Views is dependent on many different factors, such as:

• What is the source of the query/function that is creating the MV
• The type of operation(s) that is happening within your query/function
• which DLT channel you are on (current/preview)
• If you are using Serverless or not (Generally, the latest and greatest features are available in serverless DLT)

But If your source for the MV is not Streaming Tables (e.g., Delta table), then you need to make sure to have the following features enabled on your source Delta tables:

• delta.enableChangeDataFeed": "true"
• "delta.enableRowTracking": "true"

However, looking at the JSON output of planning the MV in your DLT logs will provide you with more information on why a specific method has been selected. 

Hope this helps. 

[drewipson]

Make sure you are using the aggregates and SQL restrictions outlined in this article. https://docs.databricks.com/en/optimizations/incremental-refresh.html

If a SQL function is non-deterministic (current_timestamp() is a common one) you will have a COMPLETE_RECOMPUTE every time.