Snowflake: Query Optimization Using Materialized View

Photo by Marc-Olivier Jodoin

Requirements

1. Snowflake Enterprise edition and up.
2. CREATE MATERIALIZED VIEW privilege on the Schema where the MV will be created.
3. SELECT privilege on the MV’s source table.
4. SELECT privilege on the MV itself (Only if you will directly query the MV)

Snowflake Dynamic Optimization

Before we get into the Materialized View, it is important to note about the dynamic query optimization engine that sits in the Cloud Services layer of Snowflake. It formulates the most efficient plan to execute a query based on data profiles and statistics that it collects and maintain automatically as the data is loaded into Snowflake.

This enables automatic usage of Materialized Views. Querying the base table as usual may result for the optimizer to redirect your query to a Materialized View created on top of that table, given that the MV contains all of the data required by your query and that it can provide better performance (i.e. less micro-partition to access, data is pre-aggregated / pre-computed, etc.).

Syntax

CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
  [ COPY GRANTS ]
  ( <column_list> )
  [ COMMENT =…