Incremental Load on Materialized Views

[Dharinip]

Is incremental load possible on Materialized views. I would like to get some tutorials or videos on how to perform incremental refresh on MVs in gold layers. Also is it mandatory to have PKs for performing incremental loads in MVs.

[koji_kawamura]

Hi @Dharinip 

Yes, Databricks materialized views support incremental updates. You can refer this doc page for details. It uses a DLT pipeline internally and a serverless SQL warehouse is required. It utilizes row tracking and PKs are not mandatory.

I created a very simple example notebook to demonstrate updating a materialized view incrementally.

[Dharinip]

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