Materialized Views - Strange Difference in Refresh Performance

[johnb1]

I have built the same materialized view twice with only one difference: mv01 does incremental refresh and mv02 does full refresh.

The mv reads from 7 tables and 1 mv, performs unions, joins, anti-joins as well as filters, and calculates columns on the fly. Two of the source tables are particularly large, one has 168B rows and 12238 GB of total file size and the other one has 52B rows and 9567 GB of total file size. The other source tables are much smaller: btw. 10M and 100M rows and between 0.5 and 20 GB total file size. The source mv has 10M rows and 0.2 GB of total file size. All source tables have row tracking enabled and some of the source tables (including the two large ones) have change data feed enabled.

The refresh of mv01 (incremental) took 210 min, completed 566,966 tasks, read 175B rows and 32.34 TB of data, and wrote 1.88 TB. The refresh of mv02 (full) took 55 min, completed 170,177 tasks, read 30B rows and 0.17 TB of data, and wrote 0.78 TB. The initial refresh for both mv's is comparable to the (full) refresh of mv02.

This observation is completely contrary to what I would expect. Why is the incremental more costly than the full refresh? Shouldn't it be the other way around? Why is the difference in cost that pronounced? Why is the difference in read data even more pronounced?

[data_pulse]

@johnb1 

The key check here is which technique mv01 actually used for the 210-minute run. You can verify this from the pipeline UI or query the event logs:

SELECT timestamp, origin.update_id, message, details
FROM event_log(TABLE(catalog.schema.mv01))
WHERE event_type = 'planning_information'
ORDER BY timestamp DESC

The logs should show whether it used ROW_BASED, PARTITION_OVERWRITE, FULL_RECOMPUTE etc. The planning details can also reveal which techniques were considered or rejected, along with their estimated costs.

Also, the significant difference in data read (32.34 TB incremental vs. 0.17 TB full) suggests the incremental plan may be doing substantial additional work, potentially around join maintenance or repeated scans of the large source tables

Worth checking:

• Incrementalizability: Try EXPLAIN CREATE MATERIALIZED VIEW .. AS SELECT  using the original MV definition. Check the Incremental Update Eligibility and Detailed Incrementalization Info sections for any unsupported operators or expressions.
• Change volume: How much data changed in the source tables since the previous successful refresh?
• Query profile: Compare scans, shuffle activity, join operators, and spill metrics between both runs to identify where the additional processing occurs.
• Source configuration: Are deletion vectors enabled on the source tables, and is pipeline serverless?
• Refresh policy: Is mv01 using AUTO or INCREMENTAL? With AUTO, the optimizer can choose between incremental maintenance and full re-computation based on estimated cost.

If ROW_BASED is confirmed and consistently costlier, you could schedule REFRESH MATERIALIZED VIEW mv01 FULL as a stopgap, and raise it with Databricks support. It looks like the cost model chose the plan that’s incremental in name only.