Skip to main content
Last updated on

REFRESH POLICY clause (pipelines)

Adds a refresh policy to the materialized view, controlling when to incrementalize the refresh. Applies to the CREATE MATERIALIZED VIEW (pipelines) statement.

To learn about incrementalization, see Incremental refresh for materialized views. You can check whether a SQL query is incrementalizable with the EXPLAIN MATERIALIZED VIEW statement in Databricks SQL. See EXPLAIN MATERIALIZED VIEW.

Syntax​

SQL
REFRESH POLICY refresh_policy

refresh_policy:
  AUTO | INCREMENTAL | INCREMENTAL STRICT | FULL

Parameters​

  • refresh_policy

    Defines a refresh policy for the materialized view. If the REFRESH POLICY is omitted, AUTO is the default policy.

    The refresh policy defines how a refresh handles incrementalization of the materialized view.

    • AUTO

      The system automatically selects incremental or full refresh, based on the cost model.

      State

      Behavior

      Incremental is available for the refresh.

      Uses the cost model to determine which is cheaper, incremental or full.

      Incremental is not available for refresh.

      Performs a full refresh.

      Create or re-initialization is required (for example, on schema change)

      Performs a full refresh.

    • INCREMENTAL

      The system uses incremental refreshes when possible. On CREATE, if the query cannot be incrementalized, the create statement fails.

      State

      Behavior

      Incremental is available for the refresh.

      Performs an incremental refresh.

      Incremental is not available for the refresh.

      Performs a full refresh.

      Create or re-initialization is required, but incrementalization is possible for the query.

      Performs a full refresh.

      Create or re-initialization is required, and incrementalization is not possible for the query.

      The operation fails.

    • INCREMENTAL STRICT

      The system uses incremental refreshes. On CREATE, if the query cannot be incrementalized, the create statement fails.

      State

      Behavior

      Incremental is available for the refresh.

      Performs an incremental refresh.

      Incremental is not available for the refresh.

      The refresh fails.

      Create or re-initialization is required, but incrementalization is possible for the query.

      Performs a full refresh.

      Create or re-initialization is required, and incrementalization is not possible for the query.

      The operation fails.

    • FULL

      The system always uses a full refresh.

      State

      Behavior

      Incremental is available for the refresh.

      Performs a full refresh.

      Incremental is not available for the refresh.

      Performs a full refresh.

      Create or re-initialization is required.

      Performs a full refresh.

Behavior on failure​

When a refresh fails because it can't meet the refresh policy (for REFRESH POLICY INCREMENTAL (STRICT)), the system returns an error class of MATERIALIZED_VIEW_NOT_INCREMENTALIZABLE with detailed information that describes the reason for not being incrementalizable. For example:

  • OPERATOR_NOT_SUPPORTED: An operator, such as a complex join, prevents incrementalization.
  • EXPRESSION_NOT_DETERMINSTIC: A non-deterministic function, like RAND, is used in the query.

To understand what makes a query incrementalizable, see Incremental refresh for materialized views.

To check whether a SQL query is incrementalizable, use the EXPLAIN MATERIALIZED VIEW statement in Databricks SQL. See EXPLAIN MATERIALIZED VIEW.

Examples​

SQL
-- Create a materialized view with an incremental policy
CREATE OR REFRESH MATERIALIZED VIEW my_mv
REFRESH POLICY INCREMENTAL
AS SELECT a, sum(b) FROM my_catalog.example.my_table GROUP BY a;