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 = '<string_literal>' ]
  [ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ]
  AS <select_statement>

Main Features

1. Created on top of a table containing pre-computed data set derived from a query specification and stored for later use.
2. Designed to improve query performance for workloads composed of common, repeated query patterns.
3. Can be used automatically by the optimizer.
4. Automatically maintained
5. Can be secured — Similar to a regular view, you can create a secured Materialized View.
6. Can be clustered

Example

For reference, here’s the configuration I have in this testing:

1. I’m using Snowflake Trial (Enterprise Edition; Version 5.30.2)
2. USE_CACHED_RESULT is disabled in my session. This is so the Result Cache…