snow Flake Views

 Views and types of Views

Views :

A view is named database object that allows to show result of any query as a table. Views serve a variety of purposes, including combining, segregating, and protecting data. For example, you can create separate views that meet the needs of different types of employees. 

We push complex push underlying business logic and provide just as a table. using views we can limit  data / fields access to the right people.

Views are 3 types :

•       Regular View
•       Materialized View
•       Secured View

CREATE OR REPLACE VIEW distinct_emp as (

    with my_emp as (

        select 

        EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT,

        ROW_NUMBER() OVER(PARTITION BY EMP_ID ORDER BY 1) RN

        FROM EMP

    )

    SELECT EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT

    FROM my_emp

    WHERE RN = 1

    ORDER BY EMP_ID , RN

);

select * from distinct_emp;

when you call the view using select statement, the underlying query will execute at background and fetch data for you.

If you have access to view but do not have access to the underlying table that the view is referring to in Snowflake, you will encounter an error when trying to query the view. Snowflake's access control is enforced at the table level, so if you don't have privileges to access the underlying table, you won't be able to retrieve data from the view.

To check underling view definition

SELECT GET_DDL('view', 'CUSTOMER_TABLE_VW');

Describe View :  This will show you all fields and data types of the view

DESCRIBE VIEW distinct_emp;

Dropping a view : 

DROP VIEW CUSTOMER_TABLE_VW;

Materialized view 

• Materialized view will create a an underlying memory table or Cashed table. 
• The snapshot of the data will be stored in a memory table. 
• If there is no change in the source data, the result will be fetched from this memory table. So the data retrieval is very fast.
• Materialized view will have storage cost as well for the memory table.
• When there is a change in underlying table, this view will get updated automatically. In case of other traditional database we need to refresh them manually.
• Materialized views can not be created on multiple tables using joins. It always expect to have it on top of one single table.
• window functions not allowed in Materialized view definition.
• we can't refer other view in Materialized view definition. It should be either a table or other Materialized
• Delete/ update /insert operations are not allowed on Materialized views
• But when you update the original table, that will reflect in Materialized views

Drop Materialized view

drop MATERIALIZED view CUSTOMER_TABLE_MZ_VW;

Secured Views :

Both views and secure views allow you to create logical representations of data, secure views provide an extra level of data protection by allowing fine-grained control over column visibility and row-level access. They are particularly useful when you need to enforce strict data privacy and implement data-level security policies in Snowflake.

Secured views

 CREATE OR REPLACE SECURE VIEW distinct_emp_seq_vw as (

    with my_emp as (

        select 

        EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT,

        ROW_NUMBER() OVER(PARTITION BY EMP_ID ORDER BY 1) RN

        FROM EMP

    )

    SELECT EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT

    FROM my_emp

    WHERE RN = 1

    ORDER BY EMP_ID , RN

);

drop view distinct_emp_seq_vw ;

Secured  MATERIALIZED views :  

 CREATE OR REPLACE SECURE MATERIALIZED VIEW distinct_emp_seq_vw as (

    with my_emp as (

        select 

        EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT,

        ROW_NUMBER() OVER(PARTITION BY EMP_ID ORDER BY 1) RN

        FROM EMP

    )

    SELECT EMP_ID, NAME, JOB ,MANAGER, DOJ, SAL, COMM, DEPT

    FROM my_emp

    WHERE RN = 1

    ORDER BY EMP_ID , RN

);

Show Views will show all the views in the context database and schema 

show views;

/************* To get this views details at account level **************/

select * from "SNOWFLAKE"."ACCOUNT_USAGE"."VIEWS";

show views in account;