How to Use Snowflake Create View

Creating views based on your queries is crucial for giving users access to data as if it were a table while also allowing them to perform complex operations. By encapsulating these queries as reusable objects, views prevent direct alterations to underlying tables. This is especially useful when you need consistent, reusable logic or want to simplify the data for users. Additionally, views enhance security by exposing only the necessary columns and rows, keeping the full table structure hidden.

In this tutorial, we’ll explore how to Snowflake create view for different scenarios, including non-materialized and materialized views. We’ll also look at how to improve data privacy and sharing using secure views, as well as how to simulate recursive queries with recursive views.

Difference between Views and Tables

Feature	Table	Views
Definition	A database object used to store data in relational databases in the form of rows and columns.	A virtual table created as a result of a query of a table (stored query).
Data Storage	Actually stores data in the database.	Does not store data; derives data from the base table of the view.
Data Source	Stores the data directly in the database.	Stored as a SELECT statement in the data dictionary.
Base Structure	Stores data in rows and columns.	Based on the table (base table) that it queries.

Syntax of Snowflake CREATE VIEW command

CREATE [ OR REPLACE ] [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>

  [ ( <column_list> ) ]

 AS <select_statement>;

Here’s a quick rundown of the key options:

• OR REPLACE: Optional keyword that allows you to replace an existing view with the new one if it already exists.
• SECURE: Optional keyword for creating secure views that protect sensitive data by hiding the view definition and preventing indirect data exposure through optimizations.
• TEMPORARY: Optional keyword indicating the view is temporary and only exists during the session.
• IF NOT EXISTS: Optional clause to prevent an error if the view already exists. view_name: The name of the new view.
• select_statement: The SQL SELECT statement that defines the view.

To read in-detail about CREATE VIEW in Snowflake, you can also refer to their official documentation.
Snowflake has two types of Views: Materialized Views and Non Materialized Views.

Non Materialized Views

A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query. Performance is slower than with materialized views.

Example:
Suppose we have a table called patient_billing that stores patient billing information.

CREATE DATABASE IF NOT EXISTS hospital_management;

CREATE SCHEMA IF NOT EXISTS patient_records;

CREATE TABLE patient_billing (patient_id INTEGER,
                             patient_name VARCHAR, 
                             billing_address VARCHAR,
                             diagnosis VARCHAR, 
                             treatment VARCHAR,
                             cost NUMBER(10,2));

INSERT INTO patient_billing 
    (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),
    (3, 'Ada Lovelace', '100 Analytical Engine Rd.', 'mathematical fever', 'rest and hydration', 500.00),
    (4, 'Nikola Tesla', '3 Tesla Coil Ave.', 'electric shock', 'therapy and observation', 10000.00),
    (5, 'Marie Curie', '1891 Radium Blvd.', 'radiation poisoning', 'specialized treatment', 120000.00);

OUTPUT:

Create Views

CREATE VIEW doctor_view AS
    SELECT patient_ID, patient_name, diagnosis, treatment FROM patient_billing;

CREATE VIEW accountant_view AS
    SELECT patient_ID, patient_name, billing_address, cost FROM patient_billing;

Show the cost of each treatment (without showing personally identifying information about specific patients):

SELECT treatment, cost 
    FROM doctor_view AS dv, accountant_view AS av
    WHERE av.patient_ID = dv.patient_ID;

Output:

Materialized Views

 A materialized view behaves more like a table that gets refreshed in a scheduled manner and requires zero maintenance. The term “zero maintenance” means it is automatically maintained by Snowflake, but it has a cost attached to it.  In materialized view, if there is any change in underlying tables, which is part of Materialized view query, data are synced using Snowflake-provided compute.

Note: Snowflake does not allow standard DML operations (e.g., INSERT, UPDATE, DELETE) on materialized views, and it does not allow users to truncate materialized views.

To learn more about Materialized Views and how to create them, refer to our blog on Snowflake Materialized Views.

Secure Views

The main problem with a normal view is the fear of getting exposed to underlying details of tables (like metadata), user-defined functions, etc, to unauthorized users who have ‘select’ on that view. Commands like ‘SHOW VIEW’ can easily expose the underlying view definition. To overcome this issue, Snowflake provides a way to secure views.

Therefore, secure views offer enhanced data privacy and better data sharing compared to standard views.  When deciding whether to use a secure view, you should consider the purpose of the view and weigh the trade-off between data privacy/security and query performance. Making the view secure adds a penalty on performance.

Implementing a Secure View:

Let’s say we want to create a secure view that shows the medication names that each customer is using without revealing the Customer_ID. This will help pharmacists check what medications are taken by customers but without leaking personal identifiers.

We will be using the previously created view from the previous example on the materialized view,  CustomerMedicationsView:

CREATE OR REPLACE SECURE VIEW CustomerMedicationsView AS 
SELECT m.Medication_Name, COUNT(cm.Customer_ID) AS Number_of_Customers_Taking 
FROM Customer_Medications cm 
JOIN Medications m ON cm.Medication_ID = m.Medication_ID GROUP BY m.Medication_Name;

Output:

The code creates a secure view called CustomerMedicationsView, which displays the names of medications and the number of customers taking each one, without revealing customer-specific details like Customer_ID. The view joins the Customer_Medications and Medications tables to match customer data with medication names and groups the results by each medication to count how many customers are taking it. 

By using a secure view, sensitive information (such as customer IDs) is hidden, ensuring privacy while allowing authorized users (e.g., pharmacists) to see aggregated medication data. This is useful for protecting personal data while still providing necessary insights.

How to determine whether a created view is secured or not?

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure. For example, for a view named CustomerMedicationsView, we can see the IS_SECURE column updated as True that denotes that the view is Secure.

As I mentioned above, the definition of a secure view is only exposed to authorized users (i.e., users who have been granted the role that owns the view).

In the above screenshot, the owner of the view is ACCOUNTADMIN, and only they can see the view definition (text column). Any other user would see the view as secure as False.

Best Practices for Using Secure Views:

• Watch Out for Sequence-Generated Columns: If you’re using auto-increment IDs in your views, users might figure out how much data is being created over time. To avoid this, hide those columns, use random IDs (like UUIDs), or scramble the data to keep things private.
• Be Careful with Query Performance Clues: Even though Snowflake hides the exact amount of data scanned, users can guess the data size based on how long a query takes. For highly sensitive data, you might want to create separate tables for each user or role instead of relying on views.
• Secure Data Sharing: If you’re sharing data with secure views, make sure to use functions like CURRENT_ACCOUNT to control who sees what, ensuring only authorized users from the right accounts can access the data.

Recursive Views

 In Snowflake, while recursive views as such aren’t natively supported, recursive common table expressions (CTEs) can be used to achieve recursion in queries. Let’s extend the example to a scenario where we track related medications (e.g., similar drugs or drugs with potential interactions). We can simulate recursion to find all medications related to a given medication using a recursive CTE.

Implementing Recursive Views:

We want to find all medications that are related to a given medication, like Aspirin. Some medications directly interact with Aspirin, while others may interact through a chain of relationships (indirectly). 

For example:
Aspirin interacts with Ibuprofen. Ibuprofen interacts with Simvastatin. So, Simvastatin is indirectly related to Aspirin through Ibuprofen. We can use a recursive query to find both direct and indirect interactions.

Let’s assume we already have a table called Drug_Interactions that shows which drugs interact with each other.

1. Base Case: We start by finding all medications that interact directly with Aspirin.
2. Recursive Case: We then find any medications that interact with those related drugs, and we repeat this process until we find all related medications.

Drug_Interactions Table:

Create Recursive View:

WITH RECURSIVE RelatedMedications AS (

    -- Start by finding medications that directly interact with Aspirin (ID = 1)
    SELECT Medication_1_ID AS Medication_ID, Medication_2_ID, Interaction_Description
    FROM Drug_Interactions
    WHERE Medication_1_ID = 1

    UNION ALL

    -- Recursively find medications that interact with those already found
    SELECT di.Medication_1_ID AS Medication_ID, di.Medication_2_ID, di.Interaction_Description
    FROM Drug_Interactions di
    JOIN RelatedMedications rm ON di.Medication_1_ID = rm.Medication_2_ID
)

-- Show all related medications
SELECT DISTINCT Medication_ID, Medication_2_ID, Interaction_Description
FROM RelatedMedications;

Output:

This query begins by identifying all medications that directly interact with Aspirin, where the Medication_1_ID is set to 1. After finding these direct interactions, it recursively searches for medications that interact with the medications already related to Aspirin, such as those interacting with Ibuprofen. The process continues, uncovering all indirect relationships. The final result is a comprehensive list of medications that are either directly or indirectly related to Aspirin through their interactions.

This is useful when you want to explore all medications related to a specific one, especially in cases where drugs have multiple layers of interactions. Instead of manually checking each interaction, the query automatically finds both direct and indirect relationships.

Advantages of Using Views in Snowflake:

1. Views Promote Modular and Reusable Code: Views break down complex queries into smaller, easier-to-understand pieces. This helps with debugging and improves code readability. Multiple queries can reference the same view, making it highly reusable.
2. Views Enable Granular Data Access: You can grant specific access to parts of a table using views, ensuring that different user roles (e.g., medical vs. billing staff) see only the necessary data. This helps maintain security and privacy across roles.
3. Materialized Views Improve Query Performance: Materialized views store a copy of query results, making future queries faster by avoiding full table scans. They can also be clustered differently to optimize performance for specific query patterns.

Conclusion

Creating views in Snowflake is a powerful way to simplify data access while ensuring security and integrity. Non-materialized views offer straightforward query encapsulation, while materialized views enhance performance for frequent queries. Secure views protect sensitive data, allowing for meaningful insights without compromising privacy. Additionally, using recursive CTEs enables exploration of complex data relationships. By following best practices, you can promote modular, reusable code and improve data governance. Ultimately, mastering views in Snowflake empowers you to optimize your data architecture, enhance efficiency, and support informed decision-making in your analytics projects.

FAQ on Snowflake Create View