Databricks Managing Materialized Views in Delta Live Tables: Selective Refresh Behavior

[MR07]

Hi Community,

I have 200 complex SQL Queries and I can't create a Streaming tables using these queries. So, I have created as Materialized Views in Delta Live Tables using these SQL queries and the DLT pipeline should be run continuously.

My question is: If any record of underlying table is inserted, updated or deleted, the only respective materialized view refreshed? Or It should run all the Materialized views in the pipeline?

[steyler-db]

Hello team, thanks for reaching out us, it will be a pleasure to help you on this ask. 

That's a great catch to run through a materialized, view and regarding the question: If any record of underlying table is inserted, updated or deleted, the only respective materialized view refreshed? Or It should run all the Materialized views in the pipeline?

The answer is yes , In Delta Live Tables, when a record of the underlying table is inserted, updated, or deleted, only the respective materialized view is refreshed. This is because Delta Live Tables are designed to incrementally compute changes from the base tables, thus ensuring that the materialized views are updated as the underlying data changes.

However, it's important to note that the Delta Live Tables runtime cannot detect changes in non-Delta data sources. In such cases, the table is still updated regularly but with a higher default trigger interval to prevent excessive re-computation from slowing down any incremental processing happening on compute.

For more details you can refer to the following docs:
[1] Materialized views:  https://docs.databricks.com/en/sql/user/materialized-views.html  
[2] Refreshing materialized views: https://www.databricks.com/glossary/materialized-views 

[MR07]

Hi @steyler-db ,

Thanks for your response. I would like to clarify my question further.I have 100 streaming bronze tables defined in a Bronze DLT pipeline, which is executed in continuous mode. Additionally, I have created a Silver DLT pipeline using 200 complex SQL queries as materialized views, and this pipeline also runs continuously. In this scenario, when a record is inserted, updated, or deleted in a bronze table within the Bronze DLT pipeline, should the respective materialized view be executed in the Silver DLT pipeline?

You mentioned that the Delta Live Tables runtime cannot detect changes in non-Delta data sources. Could you please provide an example of what non-Delta data sources are and explain this point further?