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.

[ManoramTaparia]

I want to identify which rows were updated as part of the last refresh if it was an incremental refresh. Is there an external event log in the form of a table which could help in identifying this?
I was trying to achieve this using current_timestamp as a col in my SQL function, but then that makes it non-deterministic and so it runs as a COMPLETE_RECOMPUTE every time.

[guptaharsh]

So, I am using DLT declarative framework to work with MV in the gold layer. I am sharing code for the sample. So, can someone tell me that how we can do only the incremental refresh, as this code is doing a full refresh daily. I don't to want do again do the full refresh.

@Dlt.table(

        name=target_table,

        comment=comment

    )

    def final_table():

        return dlt.read.table(source_table)

• My source of Gold layer is a streaming table.
• I am using Advance compute. 
• Channel is Preview.