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:
If AUTO settles your refresh times, please mark the thread as solved so others can find it.
Regards,
Louis.
