Categories:
Table, View, & Sequence DDL

TRUNCATE MATERIALIZED VIEW

Removes all rows from a materialized view, but leaves the view intact (including all privileges and constraints on the materialized view).

Note that this is different from DROP MATERIALIZED VIEW, which removes the materialized view from the system.

See also:
ALTER MATERIALIZED VIEW , CREATE MATERIALIZED VIEW

Syntax

TRUNCATE MATERIALIZED VIEW <name>

Parameters

name

Specifies the identifier for the materialized view to truncate. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. "My Object").

If the materialized view identifier is not fully-qualified (in the form of db_name.schema_name.materialized_view_name or schema_name.materialized_view_name), then the command looks for the materialized view in the current schema for the session.

Usage Notes

  • If you truncate a materialized view, the background maintenance service automatically updates the materialized view. If any queries are executed on the view while it is in the process of being updated, Snowflake ensures consistent results by retrieving any rows, as needed, from the base table.

    However, the maintenance service uses computing resources to update the materialized view and it is usually more efficient (i.e. less costly) to let an out-of-date materialized view “catch up” naturally over time than to truncate the view. As such, we do not generally recommend truncating a materialized view.

Examples

The example below truncates a materialized view, and also illustrates that queries on the view still show correct results:

-- Create a basic table.
CREATE OR REPLACE TABLE temp (i number);

-- Populate it with some rows.
INSERT INTO temp SELECT seq8() FROM table(generator(rowcount=>20)) v;

-- Create a materialized view based on the table.
CREATE MATERIALIZED VIEW mv1 AS SELECT col1, col2 FROM temp;

-- Verify that the rows in the base table are also visible in the
-- materialized view.
SELECT COUNT (*) FROM mv1;

----------+
 count(*) |
----------+
 20       |
----------+

-- Truncate the materialized view (but not the table).
TRUNCATE MATERIALIZED VIEW IF EXISTS mv1;

-- Although the materialized view was truncated, Snowflake ensures that
-- any query on the materialized view returns up-to-date results; however
-- the query may be slowed as Snowflake updates the materialized
-- view or looks up data in the base table.

SELECT COUNT (*) FROM mv1;

----------+
 count(*) |
----------+
 20       |
----------+