- 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:
- The results of the view change often.
- The query is not resource intensive so it is not costly to re-run it.
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>;