View vs materialized view, what are the differences?

Views and materialized views are methods for creating a virtual table in SQL. But, which one should you use? In this post I will look at what they are, their similarities and differences, and help you decide which is best.

What are views and materialized views?

Views and materialized views are both built on the same concept of creating a virtual table from an editable SQL query, but they have different methods of delivery.

What is a view?

A view is a virtual table that doesn’t store any data, but instead runs a defined SQL query to get data from one of more tables in a database. It’s best used when you need to access the same data infrequently.

What is a materialized view?

Materialized views are a subset of normal views where the data in the virtual table is pre-loaded from the underlying tables, and is kept up to date with any change that occurs. 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.

Similarities and differences of views and materialized views

Given that both views and materialized views are derived from the same concept, they have some obvious similarities. But there are some key differences that you should be aware of when making a decision of which to use.

Similarities between view and materialized view

1. Both create a virtual table which can be queried
2. Both are defined by an editable SQL query which provides a high degree of flexibility

Difference between view and materialized view

1. Materialized views store data whilst views don’t and have to load the data from the query each time
2. Materialized views are kept up to date with each change in the underlying tables, whilst views are run fresh each time they are queried
3. Materialized views can incur a significant cost due to their data storage and compute, whilst normal views tend to not have the same cost impact

Why use a materialized view instead of a view?

The key difference between views and materialized views is that a materialized view stores data and updates when the underlying data changes. This can lead to spiraling compute and storage costs, so you should be careful when deciding to use them. Therefore, there are only a few situations where it makes sense to use a materialized view over a view, these are when:

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

Create views and materialized views in SQL

For most SQL database technologies, views and materialized views are created in a similar way. Here I will show an example of how to create them in Snowflake:

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

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

create or replace view <name> as <select_statement>

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

Is a materialized view faster than a view?

Yes, querying a materialized view is considerably faster than a view. This is due to the way materialized views are built, as they store the queried data and update whenever a change is made to the underlying tables. This speed comes at a cost though, which should be taken into account when choosing between the two.

View vs materialized view, which is best?

Views and materialized views are both methods of creating a virtual table from an editable SQL query, but they are used for very different use cases. You should by default use a view, and only consider using a materialized view when the underlying data doesn’t update regularly and the query is intensive, such that you avoid large costs.

Related articles

Materialized view vs table
View vs table

References

Snowflake materialized view documentation
Snowflake view documentation