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 a serverless Delta Live Tables pipeline. You can use Catalog Explorer to view details about the backing pipelines in the UI. See What is Catalog Explorer?.

Syntax 

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

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 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.

  • 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 Delta Live Tables. The command returns immediately before the data load completes with a link to the Delta Live Tables 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 TABLE cat.db.st_name FULL;