Refresh operations for materialized views

Preview

Materialized views in Databricks SQL are in Public Preview.

Materialized views are database objects that contain the results of a SQL query against one or more base tables. Some materialized views can be incrementally refreshed, automatically and incrementally propagating changes from the base tables.

This article explains the types of refresh operations you can apply to materialized views, and identifies the SQL operations, keywords, and clauses that support incremental refresh.

Note

To create and use materialized views, your workspace must meet the requirements.

Refresh types

Refresh operations are one of these types:

Incremental refresh: An incremental refresh processes changes in the underlying data after the last refresh and then appends that data to the table. Depending on the base tables and included operations, only certain types of materialized views can be incrementally refreshed.
Full refresh: A full refresh truncates the table and reprocesses all data available in the source with the latest definition. It is not recommended to perform full refreshes on sources that don’t keep the entire data history or have short retention periods, such as Kafka, because the full refresh truncates the existing data. You may be unable to recover old data if the data is no longer available in the source.

When materialized views are created using a SQL warehouse or serverless Delta Live Tables pipeline, they are automatically incrementally refreshed if their queries are supported. If a query includes unsupported expressions for an incremental refresh, a full refresh will be performed, potentially resulting in additional costs. To determine which refresh type is used, see Determining if an incremental or full refresh is used.

Support for materialized view incremental refresh

The following table lists support for incremental refresh by SQL keyword or clause. Keywords and clauses marked with a star (*) require row-tracking to be enabled for incremental refresh. See Use row tracking for Delta tables.

SQL keyword or clause	Support for incremental refresh
`SELECT` expressions*	Yes, expressions including deterministic built-in functions and immutable user-defined functions (UDFs) are supported.
`GROUP BY`	Yes
`WITH`	Yes, common table expressions are supported.
`UNION ALL`*	Yes
`FROM`	Supported base tables include Delta tables, materialized views, and streaming tables
`WHERE`, `HAVING`*	Filter clauses such as `WHERE` and `HAVING` are supported.
`INNER JOIN`*	Yes
`OVER`	Yes. `PARTITION_BY` columns must be specified for incrementalization on window functions.
`LEFT JOIN`	No
`QUALIFY`	Yes.
`EXPECTATIONS`	No. Materialized views that use expectations are always fully refreshed.

Note

Non-deterministic functions, for example, CURRENT_TIMESTAMP, are not supported.