Del via

REFRESH POLICY clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Important

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Azure Databricks previews.

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 CREATE MATERIALIZED VIEW statement. See EXPLAIN CREATE MATERIALIZED VIEW.

Syntax

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 CREATE MATERIALIZED VIEW statement. See EXPLAIN CREATE MATERIALIZED VIEW.

Examples

-- 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;
  • Last updated on