EXPLAIN CREATE MATERIALIZED VIEW

Applies to: Databricks SQL Databricks Runtime 17.3 and above

Reports whether the query for a materialized view can be incrementally refreshed. Prepend EXPLAIN to a CREATE MATERIALIZED VIEW statement to check incrementalization eligibility before you create the materialized view or run an expensive refresh.

To learn about materialized view incrementalization, see Incremental refresh for materialized views.

What EXPLAIN reports

EXPLAIN CREATE MATERIALIZED VIEW checks whether the query is structurally eligible for incremental refresh. The Incremental Update Eligibility section of the output reports one of two results:

• The Materialized View can be incrementally refreshed: The query pattern supports incremental refresh.
• The Materialized View cannot be incrementally refreshed: The query is not structurally eligible for incremental refresh. Under the AUTO and FULL refresh policies, the materialized view uses a full recompute. Under INCREMENTAL or INCREMENTAL STRICT, the CREATE fails because incremental refresh is not possible. The Detailed Incrementalization Info section lists what prevents incrementalization.

Structural eligibility is not a guarantee that an incremental refresh runs. Under the default AUTO refresh policy, the cost model makes the final decision at runtime and can still choose a full recompute for an eligible materialized view. For details, see Eligibility and runtime behavior.

When to use EXPLAIN

Run EXPLAIN CREATE MATERIALIZED VIEW:

• Before you deploy a new materialized view, to verify that the query pattern supports incremental refresh.
• When you debug slow refreshes, to confirm that the materialized view is eligible. If it is not, rewrite the query.
• After you rewrite a query, to verify that the new version is eligible.
• When you migrate from dbt or another tool, to validate that the transformed queries benefit from incremental refresh.

Syntax

EXPLAIN [CREATE MATERIALIZED VIEW query]

Parameters

• query

  A SQL query that creates a materialized view. Prepend EXPLAIN to the query.

  Note

  CREATE MATERIALIZED VIEW queries from Lakeflow pipelines may not work with EXPLAIN without update. For example:

  • Expectations (CONSTRAINT...EXPECT clauses) must be removed from the query.
  • Source datasets may need to be qualified with a catalog, schema, or other path that is not needed when run in the context of a pipeline.

Examples

The following examples show the output for an eligible query and for two queries that cannot be incrementally refreshed.

Eligible for incremental refresh

A query that applies a filter, projection, and aggregation to a Delta Lake table is eligible:

EXPLAIN CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(revenue) AS total_revenue, COUNT(*) AS order_count
FROM catalog.schema.orders
WHERE order_date >= '2024-01-01'
GROUP BY region;

== Incremental Update Eligibility ==
The Materialized View can be incrementally refreshed.

== Detailed Incrementalization Info ==
No issues detected.

Not eligible: uses LIMIT

A query that uses LIMIT is not incrementalizable, because limit operators cannot be maintained incrementally:

EXPLAIN CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, total_spend
FROM catalog.schema.customer_summary
ORDER BY total_spend DESC
LIMIT 100;

== Incremental Update Eligibility ==
The Materialized View cannot be incrementally refreshed.

== Detailed Incrementalization Info ==
- OPERATOR_NOT_INCREMENTALIZABLE: Operators GlobalLimit, LocalLimit are not incrementalizable. Consider rewriting the query to avoid using them.

Not eligible: non-Delta Lake source

A query that reads from a non-Delta Lake source, such as CSV files, is not incrementalizable:

EXPLAIN CREATE MATERIALIZED VIEW external_data AS
SELECT * FROM csv.`/path/to/files/`;

== Incremental Update Eligibility ==
The Materialized View cannot be incrementally refreshed.

== Detailed Incrementalization Info ==
- INPUT_NOT_IN_DELTA: Tables are not in Delta format. Consider converting them to Delta tables.

Eligibility and runtime behavior

EXPLAIN reports whether the query structure supports incremental refresh. It does not predict what the optimizer does at runtime. Under the default REFRESH POLICY AUTO, the cost model makes the final decision and can choose a full recompute even for an eligible materialized view, for example when it estimates that operator nesting or the current data volume make a full recompute more efficient. For the full list of refresh policies, see Refresh policy.

If an eligible materialized view consistently uses full recompute under AUTO, you can:

• Set REFRESH POLICY INCREMENTAL to prefer incremental refresh over the cost-based choice. For syntax, see REFRESH POLICY clause.
• Check the pipeline event log for INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL events to understand why the cost model rejected the incremental plan. For details, see Pipeline event log and the CostModelRejectionSubType values in Pipeline event log schema.

Common cost-model rejection reasons include:

• EXCESSIVE_OPERATOR_NESTING: The query definition is complex and has many levels of operator nesting, which the cost model considers risky for incremental processing.
• CHANGESET_SIZE_THRESHOLD_EXCEEDED and TABLE_SIZE_THRESHOLD_EXCEEDED: The cost model estimates that a full recompute is cheaper for the current data volume.

A cost-model rejection does not mean the materialized view cannot incrementalize. It means the optimizer chose not to. Setting REFRESH POLICY INCREMENTAL is a supported way to override that choice.