Introduction To Snowflake Views

With Examples

Summary

1. What Are Snowflake Views

2. Snowflake Views & Their Types
3. Snowflake Views Example (Create View)
4. Snowflake Secure Views Example (Create Secure View)
5. How to interact with snowflake view and SQL example
6. Snowflake View - Change in Underlying Objects
7. Snowflake Materialized Views Example
 8. Snowflake Recursive View Example
9. Snowflake Views on Stream Object
10. Snowflake Views on Temporary & Transient Table

How to use standard, secure & materialized views in Snowflake, their SQL
construct, their limitations, cost implication with materialized views and best
practices around it.

What Are Snowflake Views

A view allows the result of a query to be accessed as if it were a table. Views
serve a variety of purposes, including combining, segregating, and
protecting data.

Snowflake views allows data developer to wrap their complex SQL logic (join,
filter, group by et) and bring simplicity & modularity to their data retrival
process to their SQL expressions. You can watch the the complete video
how view works in snowflake and refer the SQL example covered in this
article. The video tutorial also covers how to create simple, secure and
materialized views and guide you to answer following questions

1. 🙋 What are standard, secure & materiazlied views in snowflake ?

2. 🙋 How to interact with views in snowflake?
3. 🙋 Difference between standard and secure views in snowflake?
4. 🙋 What are the limitation of views in snowflake?
5. 🙋 Can views be created on stream objects?
6. 🙋 Can views be ccreated on temporary and transient snowflake tables?
7. 🙋 What are snowflake views best practice?

You can watch the complete hands on video tutorial

All the SQLs used in above video are available in this article.

Snowflake Views & Their Types

Snowflake supports 3 type of views

1. Standard Views
2. Secure Views
3. Materialized Views (Materialized Views can also be marked as secure
view)

Snowflake Views Example (Create

View)
This section describe how to create simple views which can have different
kind of SQL operation.

1 select * from customer limit 10;

2 select * from customer_address limit 10;
3 select * from customer_demographics limit 10;
4
5 -- lets create our first view
6 create or replace view customer_vw_01 as
7 select
8 [Link] ||' ' ||cust.FIRST_NAME ||' ' ||
9 cust.LAST_NAME AS CUSTOMER_NAME,
10 case
11 when [Link]='M' then 'Male'
12 when [Link]='F' then 'Female'
13 else 'Not Disclosed'
14 end as Gender,
15 case
16 when demo.MARITAL_STATUS='U' then 'Married'
17 when demo.MARITAL_STATUS='U' then 'Unmarried'
18 when demo.MARITAL_STATUS='D' then 'Divorced'
19 when demo.MARITAL_STATUS='S' then 'Separated'
20 else 'Not Disclosed'
21 end as Marital_status,
22 demo.EDUCATION_STATUS ,
23 cust.BIRTH_DAY || '-'||cust.BIRTH_MONTH || '-'||cust.BIRTH_YEAR as
24 CUSTOMER_DOB,
25 cust.BIRTH_COUNTRY ,
26 add.STREET_NUMBER||', '||add.STREET_NAME||', '||add.STREET_TYPE||',
27 '||add.SUITE_NUMBER ||', '|| [Link]||', '|| [Link] ||', '||[Link]
28 as address,
29 '$' ||demo.PURCHASE_ESTIMATE as PURCHASE_ESTIMATE,
30 demo.CREDIT_RATING
31 from
 customer cust
join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
32 join customer_demographics demo on demo.DEMO_SK =
33 cust.CURRENT_HDEMO_SK
34 where
35 [Link] is not null and
36 cust.FIRST_NAME is not null and
37 cust.LAST_NAME is not null AND
38 [Link] is not null ;
39
40 -- output of the view
41 select * from customer_vw_01;
42
43 -- another view with group by clause and aggregation function
44 create or replace view customer_by_credit_rating_vw as
45 select demo.CREDIT_RATING, count(1) as customer_cnt
46 from
47 customer cust
48 join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
49 join customer_demographics demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK
50 where
51 [Link] is not null and
52 cust.FIRST_NAME is not null and
53 cust.LAST_NAME is not null AND
54 [Link] is not null
55 group by demo.CREDIT_RATING;

select * from customer_by_country_vw;

Snowflake Secure Views Example

(Create Secure View)
This section describe how to create a secure view in snowflake.

1 -- create a secure view

2 create or replace secure view
3 my_secure_vw_02 as
4 select demo.CREDIT_RATING, count(1)
5 as customer_cnt
6 from
7 customer cust
8 join customer_address add on
9 add.ADDRESS_SK = cust.CURRENT_ADDR_SK
10 join customer_demographics demo
11 on demo.DEMO_SK = cust.CURRENT_HDEMO_SK
12 where
13 [Link] is not null and
14 cust.FIRST_NAME is not null and
15 cust.LAST_NAME is not null AND
16 [Link] is not null
17 group by demo.CREDIT_RATING;
18
19 -- see the output of the view
 select * from secure_vw_02;

-- let me create same view without secure

keyword
create or replace view
my_non_secure_vw_02 as
20 select demo.CREDIT_RATING, count(1) as
21 customer_cnt
22 from
23 customer cust
24 join customer_address add on
25 add.ADDRESS_SK = cust.CURRENT_ADDR_SK
26 join customer_demographics demo on
27 demo.DEMO_SK = cust.CURRENT_HDEMO_SK
28 where
29 [Link] is not null and
30 cust.FIRST_NAME is not null and
31 cust.LAST_NAME is not null AND
32 [Link] is not null
33 group by demo.CREDIT_RATING;
34
35
36 select * from my_non_secure_vw_02;
37 -- lets see the view output
38 -- just added the order by clause to make
39 sure result is not fetched from cache

select * from my_non_secure_vw_02 order

by 2 desc; -- query profile shows
everything
select * from my_secure_vw_02 order by 2
desc; -- query profile does not show much

How to interact with snowflake

view and SQL example
You can interact with views using Snowflake Legacy WebUI or via Snowsight.
Howver show, describe and get_ddl() are handy SQL operation which allows
you to interact with views. Here are exmaple used in this video.

1 -- list all views in the context

2 show views; -- many more columns
3 (metadata)
4 show terse views; -- limited columns
5 (metadata)
6
7 -- list all views in my account
8 show views in account;
9
10 -- list all views within [Link]
11 (ttips.ch21_3)
12 show views in database ttips;
 show views in schema ttips.ch21_3;

13 -- use the like keyword with show views

14 show terse views like 'MY_S%';
15 show views like 'MY_S%';
16
17 -- use start keywords
18 show views in account starts with 'M';
19
20
21 -- use the describe keyword
22 desc view view_with_params;
23
24 -- use get_ddl to get the view definition
25 -- which can also be fetched using show
26 views command
27 select
get_ddl('view','view_with_params');

Snowflake View - Change in

Underlying Objects
1 -- creating a table and adding a few records
2 create or replace table tbl_04(
3 id number,
4 first_name varchar,
5 last_name varchar,
6 date_of_birbh date,
7 active_flag boolean,
8 city varchar
9 );
10 -- adding records
11 insert into tbl_04 values
12 (1,'Joan','Luk','3/15/2003',TRUE,'New
13 York'),
14 (2,'Patrick','Renard','4/29/2003',FALSE,'Los
15 Angeles'),
16 (3,'Sim','Rose','8/25/2008',TRUE,'Chicago'),
17 (4,'Lila','Vis','9/19/1997',TRUE,'Miami'),
18
19 (5,'Charlie','Cook','3/19/2003',FALSE,'Dallas');
20
21
22 -- select and see the customer data which is my
23 source table
24 select * from tbl_04;
25
26 -- force keyword
27 -- create view where underlying table does not
28 exist
29 -- and table will be create in future
30 -- create a view using select * from style
 create or replace force view my_view_with_force
as
select * from future_table ;
31
-- create a view using select * from style
32
create or replace view my_view_04 as
33
select * from tbl_04 ;
34
35
show views like 'MY_VIEW_04';
36
select get_ddl('view','MY_VIEW_04');
37
-- query the view
38
select * from my_view_04;
39
40
-- lets alter the table and add one extra column
41
alter table tbl_04 add column country1 number;
42
43
44
select * from tbl_04; -- country column with
45
null value should come
select * from my_view_04; -- lets see what
happens with view

Snowflake Materialized Views

Example
Snowflake Materialized views are very powerful concept and it is covered in
chapter-21.3 in detail. The SQL used in video are given below

1 -- lets create a materialized view

2
3 -- very simple materialized SQL Construct
4 create or replace materialized view my_mat_vw_05 as
5 select * from tbl_04 ;
6
7 -- very simple materialized SQL Construct
8 create or replace secure materialized view
9 my_scure_mat_vw_05 as
10 select * from tbl_04 ;
11
12 -- position of keyword matters
13 -- else it will end with error
14 -- SQL compilation error: syntax error line 1 at position 32
15 unexpected 'secure'.
16 create or replace materialized secure view
17 my_scure_mat_vw_05_1 as
18 select * from tbl_04 ;
19
20 -- can I use limit to materialized view
21 create or replace secure materialized view
22 my_scure_mat_limit as
23 select * from tbl_04 limit 1;
24
 -- can I use join or other clauses
-- SQL compilation error: error line 0 at position -1 Invalid
materialized view definition. More than one table referenced in
the view definition
25
26
create or replace materialized view my_mat_with_join as
27
select demo.CREDIT_RATING, count(1) as customer_cnt
28
from
29
customer_1m cust
30
join customer_demographics_1m demo on demo.DEMO_SK =
31
cust.CURRENT_HDEMO_SK
32
where
33
[Link] is not null and
34
cust.FIRST_NAME is not null and
35
cust.LAST_NAME is not null
36
group by demo.CREDIT_RATING;
37
38
-- list them
39
show views like '%MAT%';
40
41
-- describe it
42
describe view MY_MAT_VW_05;
43
-- get_ddl
select get_ddl('view','MY_MAT_VW_05');

-- Materialized view takes space

Snowflake Recursive View

Example
Snowflake supports recursive identifier and that way, a recursive view can be
created. Here is an example of Snowflake recursive view and you can watch
this video how it works in snowflake cloud data warehouse.

1 create or replace table node_tree (node_name varchar,

2 node_id integer, parent_node_id integer);
3
4 insert into node_tree (node_name, node_id,
5 parent_node_id) values
6 ('President', 1, null), -- The President has no
7 manager.
8 ('Vice President Engineering', 10, 1),
9 ('Programmer', 100, 10),
10 ('QA Engineer', 101, 10),
11 ('Vice President HR', 20, 1),
12 ('Health Insurance Analyst', 200, 20);
13
14
15 select * from node_tree;
16
17 create or replace recursive view node_tree_hierarchy
18 (node_name, node_id, parent_node_id,
 "PARENT_NODE_ID (SHOULD BE SAME)", "PARENT NODE NAME")
as (
-- Start at the top of the hierarchy ...

select node_name, node_id, parent_node_id, null

19
as "PARENT_NODE_ID (SHOULD BE SAME)", 'President' as
20
"PARENT NODE NAME"
21
from node_tree
22
where node_name = 'President'
23
24
union all
25
26
-- ... and work our way down one level at a time.
27
select node_tree.node_name,
28
node_tree.node_id,
29
node_tree.parent_node_id,
30
node_tree_hierarchy.node_id as
31
"PARENT_NODE_ID (SHOULD BE SAME)",
32
node_tree_hierarchy.node_name as "PARENT
33
NODE NAME"
34
from node_tree inner join node_tree_hierarchy
35
where node_tree_hierarchy.node_id =
36
node_tree.parent_node_id
);

select * from node_tree_hierarchy;

Snowflake Views on Stream

Object
Stream objects can also be accessed like a table and hence snowflake allows
you to create view on the top of stream objects. It has some limitation and
part of the video in ch-31.3 covers it in detail. The SQL used in video section
given below.

1 -- so lets create table08

2 create or replace table tbl_08(
3 id number,
4 first_name varchar,
5 last_name varchar,
6 date_of_birbh date,
7 active_flag boolean,
8 city varchar
9 );
10
11 -- insert some record
12 insert into tbl_08 values
13 (1,'Joan','Luk','3/15/2003',TRUE,'New York'),
14 (2,'Patrick','Renard','4/29/2003',FALSE,'Los
15 Angeles'),
16 (3,'Sim','Rose','8/25/2008',TRUE,'Chicago'),
17 (4,'Lila','Vis','9/19/1997',TRUE,'Miami'),
18
19 (5,'Charlie','Cook','3/19/2003',FALSE,'Dallas');
20
21
22 -- select and see the table data which is my source
23 table
24 select * from tbl_08;
25
26
27 -- lets create a stream object to track the changes
28 in source table
29 create or replace stream stream_tbl_08 on table
30 tbl_08;
31
32 -- no cdc, so there will not be any data
33 select * from stream_tbl_08;
34
35 -- insert 2 records (6,7), delete one (1) and
36 update 2 (2,3)
37 insert into tbl_08 values
38
39 (6,'Ryan','Clark','4/13/2003',TRUE,'Philadelphia'),
40
41 (7,'Davis','Bashful','2/15/2003',TRUE,'Houston');
42 -- (2 reords in stream)
43 -- update 2 records
44 update tbl_08 set city ='Atlanta' where id = 2; --
45 Los Angeles to Atlanta (2 reords in stream)
46 update tbl_08 set city ='Atlanta' where id = 3; --
47 Chicago to Atlanta (2 reords in stream)
48
49 -- delete one record
50 delete from tbl_08 where id = 1; -- (1 reord in
51 stream)
52
53 -- I should have total 7 records
54 select * from stream_tbl_08;
55
56 -- lets create a view
57 create or replace view tbl8_cdc_view as
58 select * from stream_tbl_08;
59
60 -- lets query view
61 select * from tbl8_cdc_view;
62
63
64 -- I can also create view with additional filter
65 criteria
66 create or replace view tbl8_cdc_insert_view as
67 select * from stream_tbl_08
68 where
69 metadata$action = 'INSERT' and
70 metadata$isupdate = false;
71
72 select * from tbl8_cdc_insert_view;
 -- view to fetch only delete operation
create or replace view tbl8_cdc_delete_view as
select * from stream_tbl_08
where
metadata$action = 'DELETE' and
metadata$isupdate = false;
73
select * from tbl8_cdc_delete_view;
74
75
76
-- view to fetch only update operations
77
create or replace view tbl8_cdc_updated_view as
78
select * from stream_tbl_08
79
where
80
metadata$isupdate = true
81
order by id,metadata$isupdate desc;
82
83
select * from tbl8_cdc_updated_view;
84
85
-- lets check if we can create secure and/or
86
materialized view on stream object
create or replace secure view
secure_tbl8_cdc_view as
select * from stream_tbl_08;

create or replace materialized view

mat_tbl8_cdc_view as
select * from stream_tbl_08;

Snowflake Views on Temporary &

Transient Table
There are cases where we need to create veiws on temporary or transient
tables. Following are the SQL example used in the Ch-21.3 video.

1 create or replace temporary table

2 tbl_tmp(
3 id number,
4 first_name varchar,
5 last_name varchar,
6 date_of_birbh date,
7 active_flag boolean,
8 city varchar
9 );
10
11 create or replace transient table
12 tbl_trans(
13 id number,
14 first_name varchar,
15 last_name varchar,
16 date_of_birbh date,
17 active_flag boolean,
 city varchar
);

18 -- insert 1 record per table

19 insert into tbl_tmp values
20 (1,'Joan','Luk','3/15/2003',TRUE,'New
21 York');
22 insert into tbl_trans values
23 (1,'Joan','Luk','3/15/2003',TRUE,'New
24 York');
25
26
27 select * from tbl_tmp;
28 select * from tbl_trans;
29
30 create or replace view
31 vw_on_tmp_trans_tble AS
32 select * from tbl_tmp
33 union all
select * from tbl_trans;

select * from vw_on_tmp_trans_tble;