Insert-triggered materialized views¶

Part of Streaming Transformations. See all pages in this section.

Insert-triggered materialized views (MVs) keep filtered projections or aggregates of streamed data up to date as rows are ingested, so queries can read precomputed results without a downstream transformation job. A target table can have multiple insert-triggered materialized views, so a single PIPE can keep multiple precomputed views of the same stream up to date.

Example¶

Insert-triggered materialized views can apply projections and filters, with or without aggregation. For example, the following views retain high-value orders and maintain order totals by region:

CREATE TABLE orders (
  order_id STRING,
  region   STRING,
  amount   NUMBER
);

-- Keep a filtered projection of high-value orders.
CREATE MATERIALIZED VIEW high_value_orders
  REFRESH USING INSERT ONLY
  AS
  SELECT order_id, region, amount
  FROM orders
  WHERE amount > 1000;

-- Maintain order totals by region.
CREATE MATERIALIZED VIEW order_totals_by_region
  REFRESH USING INSERT ONLY
  AS
  SELECT
    region,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
  FROM orders
  GROUP BY region;

-- Create the pipe before or after creating the views.
CREATE PIPE orders_pipe
  AS COPY INTO orders (order_id, region, amount)
  FROM (
    SELECT
      $1:order_id::STRING,
      $1:region::STRING,
      $1:amount::NUMBER
    FROM TABLE(DATA_SOURCE(TYPE => 'STREAMING'))
  );

How insert-triggered materialized views work¶

Insert-triggered materialized views are maintained as rows are inserted into their base table, which is the target table of the pipe, through Snowpipe Streaming or INSERT statements. The view does not apply updates, deletes, or truncates on the base table.

When rows are ingested, Snowflake evaluates each insert-triggered materialized view definition (filters, aggregates, and projections) and inserts the resulting rows into the view as part of the same ingest path. Snowflake does not backfill rows that were loaded before the view was created.

To commit synchronously with the target table, the materialized views become part of the query plan of the related PIPE. Materialized views can be created before or after creating the PIPE.

Supported view definitions¶

The definition of an insert-triggered materialized view follows the rules for a standard materialized view. In particular:

An insert-triggered materialized view queries a single table, which is the target table of the pipe. Joins, other views, and subqueries aren’t supported.
A view can project columns and filter rows with WHERE.
A view can aggregate with a limited set of aggregate functions, for example COUNT, APPROX_COUNT_DISTINCT (HLL), COUNT_IF, SUM, MIN, MAX, and AVG (except when used in PIVOT). Every GROUP BY key must be in the SELECT list. See materialized view for the complete list of supported aggregate functions.
A view can’t include UDFs, window functions, HAVING, ORDER BY, LIMIT, GROUPING SETS, ROLLUP, CUBE, or nested aggregates.
Functions in the definition must be deterministic.

Sizing¶

Each insert-triggered materialized view adds work to every streaming insert, so plan for how many views you define and how much each view stores:

Number of views: start with the views your queries need and add more only when a query requires one. Ingestion latency grows with the number of views on the target table.
Aggregate views: a view with GROUP BY stores one row per group. Its size follows the number of distinct groups, not the number of ingested rows.
Filtered projection views: a view without aggregation stores every row that passes its filter, so its size grows with ingested volume. A selective filter keeps it small.
Validate: measure end-to-end latency with your real number of views before you move to production.

Limitations¶

Dropping a materialized view currently stops the PIPE from ingesting into the target table. Mitigate by recreating the pipe.
Rows ingested before an insert-triggered materialized view exists aren’t backfilled into it.
Insert-triggered materialized views are only supported on standard Snowflake tables and Interactive tables.
Use 30 or fewer insert-triggered materialized views per target table. Each materialized view adds to end-to-end latency. Test your latency with your actual views before you go to production.
Limitations of standard materialized views apply.