Snowflake - Views and Materialized Views - Working Session

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
www.udemy.com/...
I can be reachable on [e-mail].
======
VIEWS
======
USE DATABASE PUBLIC_DB;
// Create a schema for views
CREATE SCHEMA MYVIEWS;
// Requirement: Need Contact details for BRAZIL customers.
// Create customre view
CREATE OR REPLACE VIEW MYVIEWS.VW_CUSTOMER
AS
SELECT CST.C_CUSTKEY, CST.C_NAME, CST.C_ADDRESS, CST.C_PHONE FROM
SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER CST
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.NATION NTN
ON CST.C_NATIONKEY = NTN.N_NATIONKEY
WHERE NTN.N_NAME='BRAZIL';
// Query the view and see query profile how it is fetching data from underlying tables
SELECT * FROM MYVIEWS.VW_CUSTOMER;
// Turno off cached results and suspend warehouse
ALTER SESSION SET USE_CACHED_RESULT=FALSE;
SELECT * FROM MYVIEWS.VW_CUSTOMER;
// Grant access to role PUBLIC
GRANT USAGE ON DATABASE PUBLIC_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA PUBLIC_DB.MYVIEWS TO ROLE PUBLIC;
GRANT SELECT ON VIEW PUBLIC_DB.MYVIEWS.VW_CUSTOMER TO ROLE PUBLIC; -- role or user who needs BRAZIL customers data
=============
SECURE VIEWS
=============
// Requirement: Need all details of AMERICA customers
// Create secure view
CREATE SECURE VIEW MYVIEWS.SEC_VW_CUSTOMER
AS
SELECT CST.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER CST
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.NATION NTN
ON CST.C_NATIONKEY = NTN.N_NATIONKEY
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.REGION RGN
ON NTN.N_REGIONKEY = RGN.R_REGIONKEY
WHERE RGN.R_NAME='AMERICA';
// Query secure view
SELECT * FROM MYVIEWS.SEC_VW_CUSTOMER;
// Grant access to role PUBLIC
GRANT USAGE ON DATABASE PUBLIC_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA PUBLIC_DB.MYVIEWS TO ROLE PUBLIC;
GRANT SELECT ON VIEW PUBLIC_DB.MYVIEWS.SEC_VW_CUSTOMER TO ROLE PUBLIC; -- role or user who needs AMERICA customers data
// How to Identify a View is secure?
SELECT table_catalog, table_schema, table_name, is_secure
FROM public_db.information_schema.views;
SHOW VIEWS;
// Switch to public role and check
SHOW VIEWS; -- only owner can see the definition of view
==================
MATERIALIZED VIEWS
===================
//Try creating a mat view with multiple table - it won't work
CREATE MATERIALIZED VIEW MYVIEWS.MAT_VW_CUSTOMER
AS
SELECT CST.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER CST
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.NATION NTN
ON CST.C_NATIONKEY = NTN.N_NATIONKEY
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.REGION RGN
ON NTN.N_REGIONKEY = RGN.R_REGIONKEY
WHERE RGN.R_NAME='AMERICA';
// Requirement: I want to check frequently the High priority order details.
// Create materialized view
CREATE MATERIALIZED VIEW MYVIEWS.MAT_VW_ORDERS
AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
WHERE SUBSTRING(O_ORDERPRIORITY,1,1)='2';
-- AND YEAR(O_ORDERDATE)=2022 AND MONTH(O_ORDERDATE)=7
// Query mat view and see query profile, run after some time
SELECT * FROM MYVIEWS.MAT_VW_ORDERS;
// Grant access to PUBLIC role
GRANT USAGE ON DATABASE PUBLIC_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA PUBLIC_DB.MYVIEWS TO ROLE PUBLIC;
GRANT SELECT ON VIEW PUBLIC_DB.MYVIEWS.MAT_VW_ORDERS TO ROLE PUBLIC;
// How to see the mat views?
SHOW MATERIALIZED VIEWS;
// How to check the refresh history?
SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());

[@madhusudhanchinnala1986]

Thank you for explaining 😊

[@ChandraB-d3k]

very good, if possible explain about store procedures on different scenarios in snowflake.

[@epuriraghavendra3847]

Can you please explain dynamic tables and hybrid tables in Snowflake.

[@vijayreddy6295]

good explanation

[@pratikthole2414]

can you create video on information schema and public schema

[@shailu1243]

so to get only secured views we need to appy filter is_secure='yes' in the where clause

[@mrjana520]

yes

[@mayanknema3007]

Awesome Video.
I am facing many doubts in snowflakes while loading data. Can you please share me the contact details of someone with whom i can connect and clear all of my doubts?
I'll be so grateful to you and I am ready to pay also

[@mrjana520]

I can be reachable on [e-mail]

[@thimmareddylavanya6469]

Can we call normal view as standard view

[@mrjana520]

yes

[@muralikrishna-gk4hx]

Hi sir How to find one table having how many views

[@mrjana520]

it is not like that one table has many views, it is like one table can be used in any number of views and there is no option to find that..

[@mrjana520]

But you can write a query on INFORMATION_SCHMEA.VIEWS table where VIEW_DEFINITION columns contains the text that is your table name

[@muralikrishna-gk4hx]

Ok sir
You mean show views like '%emp'
This is way right sir