Databricks materialized view does a full recompute on every refresh - reading planning_information and making it incremental

#Symptom

A materialized view over a large fact table refreshes in 40 minutes. The source only gained a few thousand rows since the last run. DBU usage for the refresh is flat however small the change, and refresh time grows with the size of the source, not the size of the delta.

That is a FULL_RECOMPUTE. The engine either couldn't incrementalize the query or its cost model decided a full rebuild was cheaper.

#Step 1: confirm which technique ran

Every refresh writes a planning_information event to the view's event log:

SELECT timestamp, message
FROM   event_log(TABLE(prod.gold.daily_sales_mv))
WHERE  event_type = 'planning_information'
ORDER  BY timestamp DESC
LIMIT  20;

The message reads like Flow 'daily_sales_mv' has been planned to be executed as ROW_BASED. The full event payload is in the details column. Expand it to see the reasons a technique was rejected. Field names inside details vary by release, so read it raw first:

SELECT timestamp, details
FROM   event_log(TABLE(prod.gold.daily_sales_mv))
WHERE  event_type = 'planning_information'
ORDER  BY timestamp DESC
LIMIT  1;

DESCRIBE EXTENDED prod.gold.daily_sales_mv also shows the last refresh status. Use the event log for history.

#Step 2: work through the causes

#Enabling row tracking and deletion vectors on sources

ALTER TABLE prod.silver.sales SET TBLPROPERTIES (
  delta.enableRowTracking = true,
  delta.enableDeletionVectors = true
);

You don't need to enable the legacy change data feed (delta.enableChangeDataFeed) for this. Deletion vectors aren't required, but they make updates and deletes on the source cheaper to propagate.

Warning

Enabling row tracking or deletion vectors upgrades the table protocol. External readers that don't support those features (older Spark, some Synapse or Power BI connectors, other engines reading the files directly) can stop reading the table. Check who reads the source outside Databricks before you change it, and test on a clone first.

#The float trap

This is the most common silent blocker in finance and sales views:

-- Always full recompute: revenue is DOUBLE
CREATE OR REPLACE MATERIALIZED VIEW prod.gold.daily_sales_mv AS
SELECT order_date, region, SUM(revenue) AS revenue
FROM prod.silver.sales GROUP BY order_date, region;

-- Incrementalizable
CREATE OR REPLACE MATERIALIZED VIEW prod.gold.daily_sales_mv AS
SELECT order_date, region, SUM(CAST(revenue AS DECIMAL(18,2))) AS revenue
FROM prod.silver.sales GROUP BY order_date, region;

Floating-point sums aren't associative, so incremental maintenance can't guarantee the same answer as a rebuild. The engine refuses rather than drift.

#Time filters

A rolling window such as WHERE order_date >= current_date() - INTERVAL 90 DAYS is supported in WHERE. Using current_date() in the SELECT list or a join condition is not. Move it into the filter, or materialize the full history and filter in the consuming query.

#Joins

Inner and outer joins are supported. A view that joins many tables can still lose on the cost model. If the event log shows join-related rejections, materialize the heavy join once as its own view, then aggregate on top of it in a second view.

#Step 3: make it fail loudly next time

By default (REFRESH POLICY AUTO) the engine silently falls back to a full recompute. For views where full recompute would break an SLA or a budget, make the policy explicit:

CREATE OR REPLACE MATERIALIZED VIEW prod.gold.daily_sales_mv
REFRESH POLICY INCREMENTAL STRICT
AS SELECT order_date, region, SUM(CAST(revenue AS DECIMAL(18,2))) AS revenue
FROM prod.silver.sales GROUP BY order_date, region;

In Python pipelines:

from pyspark import pipelines as dp

@dp.materialized_view(refresh_policy="incremental_strict")
def daily_sales_mv():
    ...

Tip

EXPLAIN CREATE MATERIALIZED VIEW ... tells you whether a query is structurally incrementalizable before you deploy it. It doesn't predict cost-model decisions at runtime, so keep watching the event log after go-live.

After a source rewrite or a logic change, run one deliberate full refresh and let later runs go incremental again:

REFRESH MATERIALIZED VIEW prod.gold.daily_sales_mv FULL;

#Monitor it across all views

Run a scheduled query over each critical view's event log and alert when the latest technique is FULL_RECOMPUTE:

SELECT 'daily_sales_mv' AS mv, max_by(message, timestamp) AS last_plan
FROM   event_log(TABLE(prod.gold.daily_sales_mv))
WHERE  event_type = 'planning_information'
HAVING last_plan LIKE '%FULL_RECOMPUTE%';

Attach it to a Databricks SQL alert that triggers when rows are returned.

#Prevent it next time

• Build MVs on serverless only, and on Delta sources with row tracking enabled.
• Cast monetary and other aggregated measures to DECIMAL in silver, so every downstream view is incrementalizable.
• Use REFRESH POLICY INCREMENTAL STRICT for views with refresh-time SLAs.
• Add EXPLAIN CREATE MATERIALIZED VIEW to code review for new views.
• Alert on FULL_RECOMPUTE in planning_information, not only on refresh failures.