REFRESH (MATERIALIZED VIEW or STREAMING TABLE)

Applies to: check marked yes Databricks SQL

Refresh the data for a streaming table or a materialized view. The data is refreshed synchronously by default. You can track the status of the refresh by executing DESCRIBE EXTENDED.

Note

Create and refresh operations on materialized views and streaming tables are powered by serverless Lakeflow pipelines. You can use Catalog Explorer to view details about the backing pipelines in the UI. See What is Catalog Explorer?.

Note

REFRESH MATERIALIZED VIEW also applies to materialized metric views. Use it to manually refresh the materializations that back a metric view. See Materialization for metric views.

Syntax

REFRESH { MATERIALIZED VIEW | [ STREAMING ] TABLE } table_name
  [ FULL ] [ WHERE predicate ] [ SYNC | ASYNC ]

You can specify any subset of FULL, WHERE, and SYNC or ASYNC in the same command.

Parameters

  • table_name

    Identifies the materialized view or streaming table to refresh. The name must not include a temporal specification or options specification. If the object cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

  • FULL

    Whether to perform a full refresh.

    • For materialized views, a full refresh processes all data available in the source.
    • For streaming tables, a full refresh truncates the table and processes all data available in the source with the latest definition of the streaming table.

    It is not recommended to call full refreshes on sources that don't keep the entire history of the data or have short retention periods, such as Kafka, as the full refresh truncates the existing data. You may not be able to recover old data if the data is no longer available in the source.

  • WHERE predicate

    Applies to: check marked yes streaming table created with a FLOW REPLACE WHERE clause

    Overrides the flow's REPLACE WHERE predicate for this single refresh. The refresh deletes only the rows matching predicate from the target and recomputes them from the source. The flow's defined predicate is unchanged and applies again on the next refresh, so a predicate override is a one-time operation that doesn't modify the table definition. Use it for one-time backfills or corrections. See REPLACE WHERE flows for standalone streaming tables.

    The WHERE clause is only supported on a streaming table whose flow was created with FLOW REPLACE WHERE. Specifying WHERE on any other streaming table, or on a materialized view, returns an error.

  • SYNC

    Whether to perform a synchronous refresh. The command blocks until the materialized view or streaming table is created and the initial data load finishes.

    This is the default behavior.

  • ASYNC

    Whether to perform an asynchronous refresh, which starts a background job on Lakeflow pipelines. The command returns immediately before the data load completes with a link to the pipeline backing the materialized view or streaming table. You can visit the link to see the status of the refresh.

    You must specify ASYNC if you want to perform asynchronous refreshes. The operation is performed synchronously if no keyword is specified.

Examples

-- Refreshes the materialized view to reflect the latest available data
> REFRESH MATERIALIZED VIEW catalog.schema.view_name;

-- Refreshes the streaming table to process the latest available data
-- The current catalog and schema will be used to qualify the table
> REFRESH STREAMING TABLE st_name;

-- Truncates the table and processes all data from scratch for the streaming table
> REFRESH STREAMING TABLE cat.db.st_name FULL;

-- Overrides the REPLACE WHERE predicate for a single asynchronous refresh.
-- Only rows matching id = 3 are recomputed. The flow's static predicate is unchanged.
> REFRESH STREAMING TABLE rep_st WHERE id = 3 ASYNC;
  • Last updated on