Snowflake materialized view, simply explained

A materialized view has the flexibility of a view and the speed of a table, but there are some caveats to this. In this post I look at the benefits, how to implement it, and most importantly when you should use it.

What is a materialized view?

Materialized views are a subset of normal views where the data is pre-loaded from the underlying tables, and is kept up to date with any change that occurs in those tables.

Due to this updating, a materialized view is significantly faster than querying the original tables or creating a standard view, but incurs a cost because of the compute and storage needed to update the data. In addition to being less compute intensive, you have the peace of mind from knowing that the materialized view will always return the latest data when queried.

Key benefits of materialized views

To summarise, the key benefits for a materialized view are:

1. Significantly improved performance, especially on compute intensive queries
2. Data is always up to date

How to create a materialized view in Snowflake

Creating a materialized view is very similar to creating a normal view where, in its simplest form, all we need is a select statement and a name:

create materialized view <name> as <select_statement>

create materialized view cheap_products 
as select * from products where price < 10

We can also add common expression such as create or replace if we want to replace the materialized view if it already exists:

create or replace materialized view <name> as <select_statement>

create or replace materialized view cheap_products 
as select * from products where price < 10

Or we could only create the new materialized view if it doesn’t already exist using the following syntax:

create materialized view if not exists <name> as <select_statement>

create materialized view if not exists cheap_products 
as select * from products where price < 10

It’s also possible to add documentation to our new materialized view, as we can choose to add a comment like this:

create materialized view <name> 
comment=<comment> 
as <select_statement>

create materialized view cheap_products 
comment="This is a comment"
as select * from products where price < 10

When should you use materialized views?

We’ve seen so far that materialized views are a helpful addition to your database, but given the extra cost that they can incur you should only employ them for certain use cases. Here are a few situations where it can make sense to create one:

1. The underlying tables do not update regularly
2. The query returns a small number of rows or columns
3. The query is compute intensive

Related articles

Materialized view vs table
View vs materialized view
Create table in Snowflake
Create or replace table in Snowflake
Create table as select in Snowflake

References

Snowflake materialized view documentation