topic Re: Materialized Views - Strange Difference in Refresh Performance in Data Engineering

Materialized Views - Strange Difference in Refresh Performance

johnb1 — Fri, 30 Oct 2026 10:29:43 GMT

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?

Re: Materialized Views - Strange Difference in Refresh Performance

data_pulse — Fri, 30 Oct 2026 11:43:48 GMT

@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.

Re: Materialized Views - Strange Difference in Refresh Performance

johnb1 — Fri, 30 Oct 2026 14:32:48 GMT

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

Re: Materialized Views - Strange Difference in Refresh Performance

data_pulse — Fri, 30 Oct 2026 15:21:55 GMT

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

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.
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.

Re: Materialized Views - Strange Difference in Refresh Performance

johnb1 — Fri, 30 Oct 2026 16:44:55 GMT

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.

Re: Materialized Views - Strange Difference in Refresh Performance

data_pulse — Fri, 30 Oct 2026 17:20:35 GMT

@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.

Re: Materialized Views - Strange Difference in Refresh Performance

johnb1 — Sat, 31 Oct 2026 10:00:25 GMT

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}

Re: Materialized Views - Strange Difference in Refresh Performance

johnb1 — Sat, 31 Oct 2026 09:45:47 GMT

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.

Re: Materialized Views - Strange Difference in Refresh Performance

data_pulse — Sat, 31 Oct 2026 12:27:51 GMT

@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:

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.
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.
Re-check the event log. If NUM_JOINS_THRESHOLD_EXCEEDED disappears, the incremental plan is back in play.
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.

Re: Materialized Views - Strange Difference in Refresh Performance

Louis_Frolio — Sat, 07 Nov 2026 17:56:42 GMT

Hello @johnb1, I took a look at both internal and external documentation and here is what I found.

Thanks for posting all the test results; they make this easy to follow. Credit to @data_pulse for the troubleshooting path (event log planning_information, then swapping out CURRENT_DATE(), then trying AUTO). That's what found the real cause.

You're seeing expected behavior. Incremental doesn't always mean cheaper, and your numbers show why.

The full refresh is cheap because of pruning. Your two big sources are around 22 TB combined, but the full refresh read 0.17 TB. The date predicate plus partitioning on REGION/YEAR/MONTH/DAY prunes over 99 percent of the data before any join runs. A full recompute here recomputes a small recent window, not 22 TB of history. That's the bar incremental has to clear.

The incremental refresh is expensive because of the joins. Incremental maintenance has to work out how every changed row propagates through each union, join, and anti-join. For a join, the changed rows on one side get joined against the current state of the other side, and that probe is driven by join keys, not by your date filter. Unless the join condition includes the partition columns, the pruning that saved the full refresh doesn't apply, and with seven-plus joins the big tables get read more than once. 32 TB read is about 1.5 times the raw size of the two big tables, which fits. The changeset is also large relative to that window: one MERGE on one table added about 23 GB of files (numTargetBytesAdded), already more than a tenth of what the full refresh reads, and updates count twice on the change side (update_preimage plus update_postimage), so 128M updates and 169M inserts are closer to 425M change rows. The write side agrees: the full refresh wrote 0.78 TB, which is the whole MV, while the incremental run wrote 1.88 TB, since changes across 219 partitions mean most of the window gets rewritten anyway.

AUTO got it right. The mv04 log shows the cost model rejected the incremental plan (INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL, subtype NUM_JOINS_THRESHOLD_EXCEEDED) and chose a full recompute, matching FULL at 55 min. REFRESH POLICY INCREMENTAL is documented as an override of the cost model: it runs the incremental plan whenever one is structurally possible. The docs are explicit that a cost-model rejection means the optimizer chose not to incrementalize, not that it can't. An older Community thread with Databricks engineers mentions a configurable join threshold for the Enzyme engine (pipelines.enzyme.numberOfJoinsThreshold, default 2 at the time). I can't confirm the current default, and you've already run the experiment showing that forcing incremental loses here.

CURRENT_DATE() was a side issue. Non-deterministic time functions in a WHERE clause are supported for incremental refresh, which is why EXPLAIN reported no issues. They block PARTITION_OVERWRITE specifically. Removing it in mv03 cleared the flag, but partition overwrite still wasn't chosen and the refresh still took 199 min, so I wouldn't restructure around a fixed date. My read, unconfirmed: partition overwrite needs the planner to prove that a change in any input can only touch a bounded set of MV partitions, and your smaller tables and the source MV aren't partition-aligned.

Recommendations

Keep REFRESH POLICY AUTO (the default). It made the right call, and it'll go incremental on days when the change volume is small. Use INCREMENTAL only when you're sure incremental is always cheaper, and INCREMENTAL STRICT when an unexpected full refresh should fail loudly.
If you want incremental to win, cut the join count:
- Remove unused or redundant joins, including ones hidden in CTEs or upstream views.
- Declare PRIMARY KEY / FOREIGN KEY ... RELY constraints where the data supports them (data_pulse's suggestion too).
- Split the MV into a chain: per-source filtering and computed columns in a first layer of MVs with zero or one join each, then join those in a second MV. MVs are a supported incremental source for other MVs, and each stage gets its own eligibility check. Only a test will tell you whether the chain beats 55 minutes.
Check eligibility before deploying with EXPLAIN CREATE MATERIALIZED VIEW. It reports structural eligibility, not what the cost model picks at runtime, so keep watching the event log:

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

Incremental refresh doesn't need legacy change data feed on the sources; row tracking and deletion vectors are the two features the docs recommend. Your MERGE added 503 change files, so if nothing else consumes CDF on those tables, that's write cost you can drop.

On GROUP_AGGREGATE with no GROUP BY

Quick thing to rule out: do your unions use UNION or UNION ALL? Plain UNION implies a DISTINCT, which the Spark optimizer rewrites as an Aggregate over all columns, and that's exactly what GROUP_AGGREGATE maintains. EXCEPT does the same (anti-join plus DISTINCT). In EXPLAIN EXTENDED it appears as Distinct in the parsed and analyzed plans and only becomes Aggregate or HashAggregate in the optimized and physical plans, so a text search for "aggregate" can miss it. The support table lists UNION ALL specifically. If duplicates can't occur or are acceptable, UNION ALL removes the aggregate. If you're already on UNION ALL and nothing in the plan explains it, open a Support ticket with the event log and the query, as data_pulse suggested.

Comparing query profiles

Open Query History in the workspace sidebar, filter to the run window, and find the REFRESH statements for mv03 and mv04 (or open the pipeline update and click the flow). Compare the scan nodes on the two big tables: files read versus pruned, rows read, and how many times each table appears. MV refresh profiles now also carry incrementalization insights (FLOW_FULL_RECOMPUTE) that explain a fallback to full recompute.

References:

Incremental refresh for materialized views (support table, techniques, refresh policy, optimize section): https://docs.databricks.com/aws/en/ldp/incremental-refresh
EXPLAIN CREATE MATERIALIZED VIEW (eligibility versus the cost model, rejection reasons): https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-explain-materialized-view
REFRESH POLICY clause: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view-refresh-policy
Pipeline event log schema (CostModelRejectionSubType values): https://docs.databricks.com/aws/en/ldp/monitor-event-log-schema
Query profile and the FLOW_FULL_RECOMPUTE insight: https://docs.databricks.com/aws/en/sql/user/queries/query-profile and https://docs.databricks.com/aws/en/sql/user/queries/performance-insights#flow-full-recompute
Background on the Enzyme engine and its cost model, from the Databricks team: https://arxiv.org/pdf/2603.27775
Earlier Community thread on NUM_JOINS_THRESHOLD_EXCEEDED: https://community.databricks.com/t5/data-engineering/materialized-view-refresh-num-joins-threshold-exceeded/td-p/100761

If AUTO settles your refresh times, please mark the thread as solved so others can find it.

Regards,
Louis.

Re: Materialized Views - Strange Difference in Refresh Performance

Fastlearning272 — Sun, 08 Nov 2026 05:08:15 GMT

The incremental refresh may actually be doing more work because of the complexity of the query and how changes propagate through joins, unions, and anti-joins. I’d be interested to know whether the query plan shows expensive change detection or recomputation. Have you compared the execution plans of both refresh modes? That might reveal where the additional processing is coming from. 🔍🖼