When to Create a Materialized View:Regular vs Materialized View

Read Time:1 Minute, 30 Second

During previous post, we discussed about MV while this post will be discussing the need of creating Materialized View.

Query results contain results that require significant processing, including:
- Analysis of semi-structured data.
- Aggregates that take a long time to calculate.
The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
The view’s base table does not change frequently.

Create a regular view when any of the following are true:

Both materialized views and cached query results provide query performance benefits:

Materialized views are more flexible than, but typically slower than, cached results.
Materialized views are faster than tables because of their “cache” (i.e. the query results for the view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed.

The query optimizer can automatically rewrite queries against the base table or regular views to use the materialized view instead
You can use a materialized views by itself, or you can use it in a join.
Snowflake does not allow standard DML (e.g. INSERT, UPDATE, DELETE) on materialized views.
Snowflake does not allow users to truncate materialized views.
Materialized views consume storage space.
You can create a materialized views on shared data.
To create a MV, you need the CREATE MATERIALIZED VIEW privilege on the schema that will contain the materialized view. You need to execute a statement similar to:

grant create materialized view on schema <schema_name> to role <role_name>;