REFRESH POLICY clause

Applies to: Databricks SQL Databricks Runtime

Adds a refresh policy to the materialized view, controlling when to incrementalize the refresh. Applies to the CREATE MATERIALIZED VIEW 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. 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 is not possible to be incrementalized, the create 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 is not possible to be incrementalized, the create 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. See EXPLAIN MATERIALIZED VIEW.

Examples

SQL

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