topic Re: Materialized Views Incremental Load in Data Engineering

Materialized Views Incremental Load

Dharinip — Tue, 18 Mar 2025 14:22:02 GMT

My question is: can materialized views be updated incrementally. For example:

In my case, we store the data in Iron layer and it gets flattened in bronze and silver layer as separate tables. The required transformations happen from silver to gold layer.

All the Iron, bronze and silver layers are streaming tables as SCD type 2 and gets incrementally loaded.

The Gold layer are materialized views. Every time the pipeline runs, the materialized views are fully refreshed. But we wanted to have those incrementally loaded.
Example:

SILVER

Table: Client

ClientID	ClientName	Address	Status	CreatedDate	StartDate	Enddate	RunDate
1	ABC	Florida	New	03.15.2025	03.15.2025	03.16.2025	03.15.2025
1	ABC	Miami	New	03.16.2025	03.16.2025	03.17.2025	03.16.2025
1	ABC	Miami	Active	03.17.2025	03.17.2025	NULL	03.17.2025
2	QWE	Kansas	Active	03.15.2025	03.15.2025	03.18.2025	03.15.2025
2	QWE	Kansas	Inactive	03.18.2025	03.18.2025	NULL	03.18.2025
3	GHJ	Buffalo	Active	03.18.2025	03.18.2025	NULL	03.18.2025


GOLD

Materialized View: Client

ID	NAME	Address	Status	CreatedDate	StartDate	Enddate	RunDate
1	ABC	Miami	Active	03.17.2025	03.17.2025	NULL	03.17.2025	These should also be incrementally loaded.
2	QWE	Kansas	Inactive	03.18.2025	03.18.2025	NULL	03.18.2025
3	GHJ	Buffalo	Active	03.18.2025	03.18.2025	NULL	03.18.2025

Re: Materialized Views Incremental Load

Brahmareddy — Tue, 18 Mar 2025 16:16:22 GMT

Hi Dharinip,

How are you doing today?, As per my understanding, Right now, Materialized Views in Databricks always do a full refresh, so they can’t be updated incrementally like your streaming tables. Since your Iron, Bronze, and Silver layers are already handling changes incrementally with SCD Type 2, the best way to optimize your Gold layer is to replace the Materialized View with a Delta table that updates incrementally. Instead of refreshing everything, you can use a MERGE INTO statement to update only the new or changed records from the Silver layer. This way, your Gold table will always stay up to date without the heavy full refresh. Materialized Views are great for static or pre-aggregated data, but for your case, an incremental Delta table will work much better. Let me know if you need help setting it up!

Regards,

Brahma

Re: Materialized Views Incremental Load

guptaharsh — Fri, 12 Sep 2025 11:15:37 GMT

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)