1 0
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:

  • 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>;

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

Leave a Reply

Your email address will not be published. Required fields are marked *