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:
- Significantly improved performance, especially on compute intensive queries
- 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:
- The underlying tables do not update regularly
- The query returns a small number of rows or columns
- The query is compute intensive