مشاركة عبر

REFRESH POLICY clause (pipelines)

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 (pipelines) 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 in Databricks SQL. 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 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:

  • AGGREGATE_NOT_TOP_NODE: GROUP BY with complex expressions above it is not supported.
  • EXPRESSION_NOT_DETERMINSTIC: A non-deterministic function, like RAND, is used in the query.
  • INPUT_NOT_IN_DELTA: One or more source datasets are not Delta tables.
  • OPERATOR_NOT_INCREMENTALIZABLE: An operator, such as a complex join, prevents incrementalization.
  • ROW_TRACKING_NOT_ENABLED: Source tables that require row tracking do not have row tracking enabled.
  • SUBQUERY_EXPRESSION_NOT_INCREMENTALIZABLE: One or more subqueries in your query are not incrementalizable.
  • UDF_NOT_DETERMINISTIC: One or more UDFs used in the expression are not marked as deterministic.
  • WINDOW_WITHOUT_PARTITION_BY: Window specifications without PARTITION_BY are not incrementalizable.

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 in Databricks SQL. See EXPLAIN CREATE MATERIALIZED VIEW.

Examples

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