Type of views (standard, secure & materialized) in Snowflake, their SQL construct, their limitations, cost implication with materialized views and best practices around it.

Snowflake Views Overview

Views (whether standard or materialized) are very common concepts in database or data warehouse world. A view is basically a named definition of a query and the main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins.

Many Reason To Use Views In Snowflake

1. Views can join and simplify multiple tables into a single virtual table
2. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data
3. Views can hide the complexity of data; for example a view could appear as Order2021 or Order2022, transparently partitioning the actual underlying table
4. Views (if not materialized view) take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
5. Views can increase security and reduce how much of a table or tables are exposed to the outside world.
6. To make column names more memorable and/or relevant, views can be utilised to offer aliases.
7. In a “multi-level” query, views can act as a stepping stone. A view of a query that counted the number of sales each salesperson had made, for instance, could be made. The salespeople might then be grouped based on how many sales they had made using a query against that view.

You can watch the complete hands on video tutorial

Type Of Views Supported By Snowflake

Snowflake Cloud Data Warehouse supports 3 type of views as given below.

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

The term “view” generically refers to all types of views; however, the term is used here to refer specifically to non-materialized view (secure or standard). A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query. The results are not stored for future use. Performance is slower than with materialized views. Non-materialized views are the most common type of view.

Snowflake View Example (SQL)

Here is a simple example of a view which which is created on a single table to server 2 different purposel. Two views are created to meet the needs of different types of employees, such as doctors and accountants at a hospital. Both these roles don’t have access to the base table and via view, we can give access to different parts of the table and to achive this, a view can help as shown below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Hopital table DDL
create or replace table hospital_table (patient_id integer,
                             patient_name varchar, 
                             billing_address varchar,
                             diagnosis varchar, 
                             treatment varchar,
                             cost number(10,2));

-- Insert sample records into Hospital Table
insert into hospital_table 
        (patient_id, patient_name, billing_address, diagnosis, treatment, cost) 
    values
        (1, 'Mark Knopfler', '1982 Telegraph Road', 'Industrial Disease', 
            'a week of peace and quiet', 2000.00),
        (2, 'Guido van Rossum', '37 Florida St.', 'python bite', 'anti-venom', 
            70000.00)
        ;
--  View which will be used by doctors
create view doctor_view as
    select 
        patient_id, 
        patient_name, 
        diagnosis, 
        treatment 
    from 
    hospital_table;

-- a view which will be used by account
create view accountant_view as
    select 
        patient_id, 
        patient_name,
        billing_address, 
        cost 
     from 
        hospital_table;

A view can be used almost anywhere that a table can be used (joins, subqueries, etc.). Any query expression that returns a valid result can be used to create a non-materialized view, such as:

1. Selecting some (or all) columns in a table.
2. Selecting a specific range of data in table columns.
3. Joining data from two or more tables.

Materialized View in Snowflake

Despite having a name that suggests it is a form of view, a materialised view functions more like a table. The output of a materialised view is stored in a manner similar to that of a table.

Because the data is pre-computed, querying a materialized view is faster than executing a query against the base table of the view. This performance difference can be significant when a query is run frequently or is sufficiently complex.

Therefore, materialised views can accelerate pricey aggregate, projection, and selection processes, particularly those that happen frequently and on huge data sets.

1
2
3
4
5
6
7
8
9
create or replace 
    materialized view my_material_vw as 
    select 
        patient_id, 
        patient_name, 
        diagnosis, 
        treatment 
    from 
        hospital_table;

Materialized View Auto Refresh

Snowflake runs a background process to refresh the materialized view and nothing need to be done to refresh the materialized view.

Materializing intermediate results incurs additional costs and hence before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.

Choosing the Right Time to Create a Materialized View

Materialized views are particularly useful when:

1. Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
2. Query results contain results that require significant processing, including:
   • Analysis of semi-structured data.
   • Aggregates that take a long time to calculate.
3. The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
4. The view’s base table does not change frequently.

Secure Views in Snowflake

Both non-materialized and materialized views can be defined as secure. Secure views have advantages over standard views, including improved data privacy and data sharing; however, they also have some performance impacts to take into consideration.

Here is an example of secure view where secure qualifier is used.

1
2
3
4
5
6
7
8
9
10
11
12
create or replace secure view my_secure_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;

Advantages of Snowflake Views

Snowflake Views (Standard, Secure & Materialized Views) offers following advantages

1. Views help you to write clearer, more modular SQL code
2. Views allow you to grant access to just a portion of the data in a table(s).
3. Materialized Views are designed to improve performance

Limitation of Snowflake Views

Snowflake Views (Standard, Secure & Materialized Views) come with certain limitations and they are

1. Snowflake Views (Standard, Secure & Materialized Views) offers following advantages.
2. Changes to a table are not automatically propagated to views created on that table.
3. Views are read-only (i.e. you cannot execute DML commands directly on a view).

Conclusion

Thus, the primary function of a view in SQL is to effectively aggregate data from several sources without the need to add yet another database table to hold that data. Tables and views from other databases can be among the numerous sources.

Since the view is defined by a query, you can use a view to constrain what data is returned from the source tables and views used to generate the view. A view allows the view author to return data an end user needs while protecting possibly sensitive data in the source tables/views the author does not want revealed.

Since a view is predetermined, it may be more effective than a query. In order to see the results they require, developers can use a view rather than a challenging query by including a sophisticated query within it. Therefore, it makes logical to create a view out of a frequently used query. The task of the developers is made simpler, and efficiency may increase.