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.

[johnb1]

I add the information that the mv is partitioned by four columns (the same holds for the largest source tables).

Information from the details field from the event log (according to your proposed query)

"technique_information":[{"maintenance_type":"MAINTENANCE_TYPE_NO_OP","incrementalization_issues":[{"issue_type":"DATA_HAS_CHANGED","prevent_incrementalization":true}]},{"maintenance_type":"MAINTENANCE_TYPE_PARTITION_OVERWRITE","incrementalization_issues":[{"issue_type":"PLAN_NOT_DETERMINISTIC","prevent_incrementalization":true,"operator_name":"Filter","expression_name":"CurrentDate","plan_not_deterministic_sub_type":"TIME_FUNCTION"}]},{"maintenance_type":"MAINTENANCE_TYPE_COMPLETE_RECOMPUTE","is_chosen":false,"is_applicable":true},{"maintenance_type":"MAINTENANCE_TYPE_GROUP_AGGREGATE","is_chosen":true,"is_applicable":true}],"

Incrementalizability check with EXPLAIN command

== Incremental Update Eligibility == The Materialized View can be incrementally refreshed. == Detailed Incrementalization Info == No issues detected.

Previous changes in largest source table (from operationMetrics field from DESCRIBE HISTORY):

• numTargetRowsMatchedUpdated: 114011636
• numTargetRowsInserted: 94445597
• numTargetRowsMatchedDeleted: 0
• numTargetBytesRemoved: 14789848976
• numTargetRowsUpdated: 114011636
• numTargetBytesAdded: 23081777848
• numTargetChangeFilesAdded: 503

Source configuration

Are deletion vectors enabled on the source tables? -> Yes

Is pipeline serverless? -> Yes

Refresh policy: REFRESH POLICY INCREMENTAL

[data_pulse]

Thanks, this is helpful. Two things stand out in the event log:

1. PARTITION_OVERWRITE was rejected because of PLAN_NOT_DETERMINISTIC (CurrentDate in a Filter, TIME_FUNCTION). Given that your MV and large sources are partitioned the same way, that technique looks like a good fit. As a diagnostic experiment, you could test an equivalent MV definition on a copy, with a fixed date in place of current_date() for the same refresh snapshot. Then check whether PARTITION_OVERWRITE becomes applicable and whether performance improves. If it does, you could look at making the date filter deterministic, for example by passing the date in as a parameter or moving the filter into a view on top of the MV.
2. GROUP_AGGREGATE based Incremental was chosen over COMPLETE_RECOMPUTE. Does the MV include a GROUP BY or DISTINCT? The last MERGE show about 208M rows (~114M updated, ~98M Inserted). If those rows spread across most groups or partitions, maintenance may need to re-read most of the table, which would fit the 175B rows read. Checking how many partitions that MERGE touched would help confirm this.

[johnb1]

Thank you for your inputs and suggestions @data_pulse 

The question is if there are any ways to decrease the cost of the incremental refresh considerably, such that it beats the cost of the full refresh in a significant way.

I will test creating a different mv without the CURRENT_DATE() function but a fixed date instead.

Question: So from the event log I can infer that maintenance_type = MAINTENANCE_TYPE_GROUP_AGGREGATE was chosen, but maintenance_type = MAINTENANCE_TYPE_PARTITION_OVERWRITE was not chosen, due to the use of the CURRENT_DATE() function?

No GROUP BY or DISTINCT clause is used in the query.

I don't know how to verify the number of partitions in the large source table affected by the last MERGE. Partition columns are REGION, YEAR, MONTH and DAY, so the new rows should not spread across many partitions. The refresh runs once a day.

[data_pulse]

@johnb1 

In order to decrease the incremental refresh cost, we have to identify where the additional processing overhead is coming from and that's what we are trying to triage here.

Yes, the event log shows PARTITION_OVERWRITE was blocked by the CurrentDate time function, so your fixed-date test is the right experiment. 

GROUP_AGGREGATE without GROUP BY is surprising. It may be a global aggregate, or an aggregate or DISTINCT hidden in a view, CTE or the source MV. EXPLAIN EXTENDED on the query should show whether an Aggregate node is in the plan.

With one refresh a day and a 55-minute full refresh versus 210 minutes incremental, you could try ALTER MATERIALIZED VIEW .. SET REFRESH POLICY AUTO and see what it picks. This probably allows Databricks to choose between incremental and full re-computation based on estimated cost.

Identify affected partitions: If Change Data Feed (CDF) is enabled on the source, you can check which partitions were affected by the MERGE:

SELECT
    REGION, YEAR, MONTH, DAY,
    _change_type,
    COUNT(*) AS changed_rows
FROM table_changes('catalog.schema.source_table', <merge_version>)
GROUP BY REGION, YEAR, MONTH, DAY, _change_type
ORDER BY changed_rows DESC;

Replace <merge_version> with the Delta version on which Merge has been run from DESCRIBE HISTORY on that table.

Also can you compare the Query profiles from Incremental refresh and Full refresh, It could give some leads on whether scan/join/shuffle n spill taking more time.

I know it’s a bit tedious to triage, but hunting down the mole is worth it to get a truly incremental refresh.

[johnb1]

I created two more materialized views:

• mv03 without the current_date() function and with REFRESH POLICY INCREMENTAL
• mv04 with REFRESH POLICY AUTO.

The refresh pipeline of mv03 ran 199 min this morning. The refresh pipeline of mv01 ran 208 min. The refresh pipelines of mv02 (REFRESH POLICY FULL) and mv04 (REFRESH POLICY AUTO) both ran 55 min.

Planning information from event log of refresh pipeline of mv03

{"maintenance_type": "MAINTENANCE_TYPE_NO_OP",
"incrementalization_issues": [{"issue_type": "DATA_HAS_CHANGED", "prevent_incrementalization": true}]},
{"maintenance_type": "MAINTENANCE_TYPE_COMPLETE_RECOMPUTE", "is_chosen": false, "is_applicable": true},

-> The issue PLAN_NOT_DETERMINISTIC is not detected anymore, but it still chose maintenance_type = MAINTENANCE_TYPE_GROUP_AGGREGATE.

I ran EXPLAIN EXTENDED on the query and searched for the keyword "aggregate" in the output (which includes the Parsed Logical Plan). No hit.

Planning information from event of refresh pipelines of mv02 and mv04

{"maintenance_type": "MAINTENANCE_TYPE_NO_OP", "incrementalization_issues": [{"issue_type": "DATA_HAS_CHANGED", "prevent_incrementalization": true}]},

{"incrementalization_issues": [{"issue_type": "INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL", "prevent_incrementalization": true, "cost_model_rejection_subtype": "NUM_JOINS_THRESHOLD_EXCEEDED", "source_rules": ["INCREMENTALIZATION_RULE_DEFAULT"]}]},

{"maintenance_type": "MAINTENANCE_TYPE_PARTITION_OVERWRITE", "incrementalization_issues": [ {"issue_type": "PLAN_NOT_DETERMINISTIC", "prevent_incrementalization": true, "operator_name": "Filter", "expression_name": "CurrentDate", "plan_not_deterministic_sub_type": "TIME_FUNCTION"}]},

{"maintenance_type": "MAINTENANCE_TYPE_COMPLETE_RECOMPUTE", "is_chosen": true, "is_applicable": true}

[johnb1]

I ran the query with the table_changes() function. 219 partitions were affected by the operations between yesterdays's last MERGE and today's last MERGE. change_type is insert, update_preimage or update_postimage. Overall, there were 169M rows inserted and 128M rows updated in today's daily run.

I do not know how to compare the Query profiles from Incremental refresh and Full refresh.

[data_pulse]

@johnb1  

Thanks for running these additional tests and the efforts. The results help narrow down the issue considerably.

• current_date() wasn’t the cause: Removing it eliminated PLAN_NOT_DETERMINISTIC, but the refresh still took 199 minutes. So, while the fixed-date test was useful, it hasn't improved incremental performance.
• The cost model got it right:  In mv04’s log, AUTO rejected the incremental plan (NUM_JOINS_THRESHOLD_EXCEEDED) and chose a full recompute, which matches FULL at 55 min. REFRESH POLICY INCREMENTAL was forcing a plan the model rejected. This corrects my earlier comment.
• Practical answer: Use AUTO. It matches FULL now and can go incremental if the plan or data changes.

If you want incremental to win, the join count is the hurdle. 

On reducing join complexity: One possible optimization is to identify redundant joins or declare PRIMARY KEY / FOREIGN KEY constraints with RELY on the underlying tables, where the relationships are valid. This allows the optimizer to eliminate certain unnecessary joins. Docs reference

I ran a small experiment with a 100K row Delta dataset to validate whether these optimizations influence the MV refresh strategy:

Auto Variant	Optimized Joins	Selected Maintenance
Baseline	6	COMPLETE_RECOMPUTE
Removed redundant LEFT JOINs	2	ROW_BASED
Declared valid PK/FK constraints with RELY	2	ROW_BASED

In my small test, reducing joins was enough to move the planner from COMPLETE_RECOMPUTE to ROW_BASED, so incremental maintenance became an option. Whether it’s also faster depends on the workload, so it’s worth testing on yours. A good way to approach it:

1. Find redundant joins first. Check the optimized plan for joins that don’t change the result, such as LEFT JOINs to tables whose columns aren’t used or that can’t add rows.
2. Use RELY where the relationships are guaranteed. Declare PK/FK/unique constraints only where they genuinely hold, since Databricks doesn’t enforce them and the optimizer will trust them.
3. Re-check the event log. If NUM_JOINS_THRESHOLD_EXCEEDED disappears, the incremental plan is back in play.
4. Measure with AUTO. Compare against the 55-minute full refresh and keep whichever wins. If incremental still doesn’t beat it, you’ve lost nothing, since AUTO keeps choosing the cheaper option.

Next step: Compare the query profiles of mv03 and mv04: Open each run’s query profile from Query History (filter by the MV name and run time, find the REFRESH statement) or from the pipeline update details. Open the incremental run (mv03) and the full run (mv04) in two tabs and compare:

• Total duration, and rows and bytes read
• The scans of the two large tables (rows read, files read vs. pruned)
• The slowest operators, especially the joins
• Shuffle size and spill

The GROUP_AGGREGATE label despite no aggregation in the plan is still worth raising with Databricks Support.