Materialized view vs table, what are the differences?

Materialized view vs table in SQL, what are the differences?

Materialized views and tables are used for organising and storing data in a SQL database. But what are the differences between them, and in which use cases should you use one over the other?

Stephen Allwright
Stephen Allwright

Materialized views and tables are used for organising and storing data in a SQL database. But what are the differences between them, and in which use cases should you use one over the other?

What are tables and materialized views in SQL?

Tables and materialized views are methods of organising data in SQL with different use cases, but what are they?

What is a table?

A table is the essential building block of a SQL database. It is a store of data which can be updated, replaced, and most importantly queried. Typically, tables are organised into a star schema made up of dimension and fact tables.

What is a materialized view?

Materialized views are virtual tables created from an editable SQL query. They are a subset of normal views where the data is pre-loaded from the underlying tables, and are 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.

How is a materialized view different from a table?

Materialized views and tables both store data but they are fundamentally different objects in SQL. A materialized view is built on top of existing tables, whilst a table is the original data storage object that is used.

Another major difference is that a materialized view is self updating whenever the underlying tables change, whilst to update a table a job has to be run to append or replace the data.

Why use materialized view instead of a table in SQL?

If you are wanting to add new data to your database then this must be done by adding it to a table, however if you are wanting to transform existing tabular data then this is a situation where we can discuss whether or not to use a materialized view or a table.

Generally accepted database practices suggest that data should only exist in one place instead of being copied, so if you are performing a transformation then you should be using a view to do this. This view could either be a standard view or a materialized view, where the following situations would make a materialized view the best decision:

  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

Is materialized view faster than a table in SQL?

It is always faster to query a single table than any kind of view built on top of that table. However, the materialized view will be faster when you start joining multiple tables together as it stores the data resulting from the query and updates when the underlying tables change.

Creating materialized views and tables in SQL

Creating materialized views and tables in SQL requires similar commands regardless of which technology you are using. In the following examples I will be showing how it can be done in Snowflake.

Create a materialized view in Snowflake

The creation of a materialized view is a straight forward process where you just need to define the select statement you require.

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

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

Create a table in Snowflake

There are several ways to create a table. You could either create a brand new table and load data into it afterwards, or create a table as a select statement which will load the resulting data from that select statement into a new table. I will show both examples here:

create or replace table <name> (<column_name> <data_type>)
create or replace table <name> as <select_statement>

create or replace table cheap_products (
	product_id number,
	price number)

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

Materialized view vs table, which should you use?

Materialized views and tables are different methods of storing data and are used for fundamentally different use cases. If you need to store data long term and use it as part of a data model then you should create a table, however if you want to join multiple tables and query this infrequently, as part of an analysis for example, then a materialized view would be the better choice.


Tables

Create a temporary table in Snowflake
Create table as select in Snowflake
Delete table in Snowflake

Views

View vs table

Functions

Select all columns except one or more
Snowflake ifnull function

References

Snowflake materialized view documentation
Snowflake table documentation
Star schema explanation

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.