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.