Introduction To Snowflake Views With Examples

Introduction To Snowflake Views With Examples

Summary

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.


select * from customer limit 10; 
select * from customer_address limit 10; 
select * from customer_demographics limit 10; 

-- lets create our first view
create or replace view customer_vw_01 as 
    select 
        cust.SALUTATION	||' ' ||cust.FIRST_NAME	||' ' || cust.LAST_NAME	AS CUSTOMER_NAME,
        case
          when demo.GENDER='M' then 'Male'
          when demo.GENDER='F' then 'Female'
          else 'Not Disclosed'
        end as Gender,
        case
          when demo.MARITAL_STATUS='U' then 'Married'
          when demo.MARITAL_STATUS='U' then 'Unmarried'
          when demo.MARITAL_STATUS='D' then 'Divorced'
          when demo.MARITAL_STATUS='S' then 'Separated'
          else 'Not Disclosed'
        end as Marital_status,
      demo.EDUCATION_STATUS	,
      cust.BIRTH_DAY || '-'||cust.BIRTH_MONTH || '-'||cust.BIRTH_YEAR as CUSTOMER_DOB,
      cust.BIRTH_COUNTRY	,
      add.STREET_NUMBER||', '||add.STREET_NAME||', '||add.STREET_TYPE||', '||add.SUITE_NUMBER ||', '|| add.STATE	||', '|| add.ZIP ||', '||add.COUNTRY	 as address, 
      '$' ||demo.PURCHASE_ESTIMATE as PURCHASE_ESTIMATE,
      demo.CREDIT_RATING
    from 
        customer cust 
        join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
        join customer_demographics demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK   
    where 
    cust.SALUTATION is not null and
    cust.FIRST_NAME is not null and 
    cust.LAST_NAME is not null AND
    add.COUNTRY is not null ;

-- output of the view
select * from customer_vw_01;

-- another view with group by clause and aggregation function
create or replace view customer_by_credit_rating_vw as 
select demo.CREDIT_RATING, count(1) as customer_cnt
from 
    customer cust 
    join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
    join customer_demographics demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK   
where 
cust.SALUTATION is not null and
cust.FIRST_NAME is not null and 
cust.LAST_NAME is not null AND
add.COUNTRY is not null 
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.


-- create a secure view 
    create or replace secure view my_secure_vw_02 as 
    select demo.CREDIT_RATING, count(1) as customer_cnt
    from 
        customer cust 
        join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
        join customer_demographics demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK   
    where 
    cust.SALUTATION is not null and
    cust.FIRST_NAME is not null and 
    cust.LAST_NAME is not null AND
    add.COUNTRY is not null 
    group by demo.CREDIT_RATING;

-- 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 
  select demo.CREDIT_RATING, count(1) as customer_cnt
  from 
      customer cust 
      join customer_address add on add.ADDRESS_SK = cust.CURRENT_ADDR_SK
      join customer_demographics demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK   
  where 
  cust.SALUTATION is not null and
  cust.FIRST_NAME is not null and 
  cust.LAST_NAME is not null AND
  add.COUNTRY is not null 
  group by demo.CREDIT_RATING;


select * from my_non_secure_vw_02;
-- lets see the view output
-- just added the order by clause to make 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.


-- list all views in the context
show views; -- many more columns (metadata)
show terse views; -- limited columns (metadata)

-- list all views in my account
show views in account;

-- list all views within db.schema (ttips.ch21_3)
show views in database ttips;
show views in schema ttips.ch21_3;

-- use the like keyword with show views
show terse views like 'MY_S%';
show views like 'MY_S%';

-- use start keywords 
show views in account starts with 'M';


-- use the describe keyword
desc view view_with_params;
    
-- use get_ddl to get the view definition
-- which can also be fetched using show views command
select get_ddl('view','view_with_params');

Snowflake View - Change in Underlying Objects


-- creating a table and adding a few records
create or replace table tbl_04(
    id number,
    first_name varchar,
    last_name varchar, 
    date_of_birbh date,
    active_flag boolean,
    city varchar
);
-- adding records   
insert into tbl_04 values 
    (1,'Joan','Luk','3/15/2003',TRUE,'New York'),
    (2,'Patrick','Renard','4/29/2003',FALSE,'Los Angeles'),
    (3,'Sim','Rose','8/25/2008',TRUE,'Chicago'),
    (4,'Lila','Vis','9/19/1997',TRUE,'Miami'),
    (5,'Charlie','Cook','3/19/2003',FALSE,'Dallas');
    
    
-- select and see the customer data which is my source table
select * from tbl_04;
 
-- force keyword
-- create view where underlying table does not exist
-- and table will be create in future
-- create a view using select * from style
create or replace force view my_view_with_force as
    select * from future_table ;
    
-- create a view using select * from style
create or replace  view my_view_04 as
    select * from tbl_04 ;

show views like 'MY_VIEW_04';
select get_ddl('view','MY_VIEW_04');
-- query the view
select * from my_view_04;

-- lets alter the table and add one extra column
alter table tbl_04 add column country1 number; 


select * from tbl_04; -- country column with 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

-- lets create a materialized view

-- very simple materialized SQL Construct
    create or replace materialized view my_mat_vw_05 as
        select * from tbl_04 ;
    
-- very simple materialized SQL Construct
    create or replace secure materialized view my_scure_mat_vw_05 as
        select * from tbl_04 ;
    
-- position of keyword matters
-- else it will end with error
-- SQL compilation error: syntax error line 1 at position 32 unexpected 'secure'.
    create or replace  materialized secure view my_scure_mat_vw_05_1 as
        select * from tbl_04 ;
        
-- can I use limit to materialized view
    create or replace secure materialized view my_scure_mat_limit as
        select * from tbl_04 limit 1;
        
-- 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

    create or replace materialized view my_mat_with_join as 
      select demo.CREDIT_RATING, count(1) as customer_cnt
      from 
          customer_1m cust 
          join customer_demographics_1m demo on demo.DEMO_SK = cust.CURRENT_HDEMO_SK   
      where 
      cust.SALUTATION is not null and
      cust.FIRST_NAME is not null and 
      cust.LAST_NAME is not null 
      group by demo.CREDIT_RATING;
      
-- list them
    show views like '%MAT%';
    
-- describe it
    describe view MY_MAT_VW_05;
-- 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.


create or replace table node_tree (node_name varchar, node_id integer, parent_node_id integer);

insert into node_tree (node_name, node_id, parent_node_id) values
    ('President', 1, null),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);
            
            
    select * from node_tree;        

create or replace recursive view node_tree_hierarchy 
        (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 as "PARENT_NODE_ID (SHOULD BE SAME)", 'President' as "PARENT NODE NAME"
            from node_tree
            where node_name = 'President'
      
        union all
        
      -- ... and work our way down one level at a time.
      select node_tree.node_name, 
             node_tree.node_id, 
             node_tree.parent_node_id, 
             node_tree_hierarchy.node_id as "PARENT_NODE_ID (SHOULD BE SAME)", 
             node_tree_hierarchy.node_name as "PARENT NODE NAME"
        from node_tree inner join node_tree_hierarchy
        where node_tree_hierarchy.node_id = 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.


-- so lets create table08
    create or replace table tbl_08(
        id number,
        first_name varchar,
        last_name varchar, 
        date_of_birbh date,
        active_flag boolean,
        city varchar
    );
    
   -- insert some record
   insert into tbl_08 values 
    (1,'Joan','Luk','3/15/2003',TRUE,'New York'),
    (2,'Patrick','Renard','4/29/2003',FALSE,'Los Angeles'),
    (3,'Sim','Rose','8/25/2008',TRUE,'Chicago'),
    (4,'Lila','Vis','9/19/1997',TRUE,'Miami'),
    (5,'Charlie','Cook','3/19/2003',FALSE,'Dallas');
    
    
-- select and see the table data which is my source table
    select * from tbl_08;


-- lets create a stream object to track the changes in source  table
    create or replace stream stream_tbl_08 on table tbl_08;
    
-- no cdc, so there will not be any data
select * from stream_tbl_08;

-- insert 2 records (6,7), delete one (1) and update 2 (2,3)
insert into tbl_08 values 
    (6,'Ryan','Clark','4/13/2003',TRUE,'Philadelphia'),
    (7,'Davis','Bashful','2/15/2003',TRUE,'Houston'); -- (2 reords in stream)
-- update 2 records
update tbl_08 set city ='Atlanta' where id = 2; -- Los Angeles to Atlanta (2 reords in stream)
update tbl_08 set city ='Atlanta' where id = 3; -- Chicago to Atlanta (2 reords in stream)

-- delete one record
delete from tbl_08 where id = 1; -- (1 reord in stream)

-- I should have total 7 records
select * from stream_tbl_08;

-- lets create a view
    create or replace view tbl8_cdc_view as
    select * from stream_tbl_08;

-- lets query view
   select * from tbl8_cdc_view;


-- I can also create view with additional filter criteria
create or replace view tbl8_cdc_insert_view as
    select * from stream_tbl_08 
    where 
        metadata$action = 'INSERT' and
        metadata$isupdate = false;
        
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;
        
select * from tbl8_cdc_delete_view;
        
        
-- view to fetch only update operations
create or replace view tbl8_cdc_updated_view as
    select * from stream_tbl_08 
    where 
        metadata$isupdate = true
        order by id,metadata$isupdate desc;
        
select * from tbl8_cdc_updated_view;

-- lets check if we can create secure and/or 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.


create or replace temporary table tbl_tmp(
    id number,
    first_name varchar,
    last_name varchar, 
    date_of_birbh date,
    active_flag boolean,
    city varchar
);

create or replace transient table tbl_trans(
    id number,
    first_name varchar,
    last_name varchar, 
    date_of_birbh date,
    active_flag boolean,
    city varchar
);
    
-- insert 1 record per table    
insert into tbl_tmp values (1,'Joan','Luk','3/15/2003',TRUE,'New York');
insert into tbl_trans values (1,'Joan','Luk','3/15/2003',TRUE,'New York');


select * from tbl_tmp;
select * from tbl_trans;

create or replace view vw_on_tmp_trans_tble AS
select * from tbl_tmp
union all 
select * from tbl_trans;

select * from vw_on_tmp_trans_tble;