Simply Snowflake Shorts — Part 4
MATERIALIZED VIEWS:
In this part of SSS, we would discuss about “Materialized View”.
What is a Materialized view ? Materialized views are Snowflake objects which are intended to improve the performance of query workloads by having precomputed dataset stored for a later use. And as the data is precomputed querying a materialized view is faster than executing a query against a table. This is a feature available in Snowflake’s Enterprise Edition and above.
How can we create this kind of view and how do we differentiate it between standard view ? Below are few ways of creating them the below syntax displays about creating “materialized view” & “secure materialized view” & also on how we can easliy identify them:
-- Steps to create the materialized view:
create or replace materialized view vw_mat_cust_metadata
as
select * from "DEMO_DB"."DEMO_SCHEMA"."TBL_CUST_METADATA";
-- Steps to create a secure materialized view:
create or replace secure materialized view vw_mat_date_dim
as
select * from "DEMO_DB"."DEMO_SCHEMA"."TBL_DATE_DIM";
Also, we can use the command “show views” and in the o/p we can check for the column “is_materialized” to know if the view is materialized OR not. Below is the snapshot from the console:
Some basic differences of “Standard view” and “Materialized view” are as follows:
When to use “Materialized view” ?? Now, that we know what is a materialized view we should also be cognizant of the fact that when to use it so that it adds right value in terms of optimizing the performance of the workloads. Below are some cases where we can use it:
Materialized View considerations:
Below summary also has some more important features/considerations which we should keep in mind whenever we are using the materialized view.
For more reads :
https://docs.snowflake.com/en/user-guide/views-materialized
THANK YOU !!
Please keep reading my blogs it is only going to encourage me in posting more such content. You can find me on LinkedIn by clicking here and on Medium here. Happy Learning :)
Awarded as “Data Superhero by Snowflake for year 2023”, click here for more details.
Disclaimer: The views expressed here are mine alone and do not necessarily reflect the view of my current, former, or future employers.
