Getting Started with Snowflake Materialized Views

In Snowflake, the views are crucial for organizing, selecting, and retrieving data while not copying the data itself. Instead, if performance is a concern—such as in querying large data sets—then Snowflake materialized views are perfect.

In this blog, we’ll explore:

• What views are and the different types in Snowflake?
• What materialized views offer over regular views
• How to create different views
• A comparison table to highlight key differences
• And, of course, a conclusion to wrap it all up!

Ready? Let’s start by talking about views in general.

What are Views?

In simple terms, a view is similar to a saved query. It helps you plan how you are going to disperse your data in a dynamic manner without physically storing the result. When you query a view, it retrieves the data in real-time based on the underlying tables.

You can use the CREATE VIEW command to create a view in Snowflake. The basic syntax for creating a view is:

CREATE VIEW <name> AS <select_statement>

Different Types of Views

Snowflake offers three main types of views:

1. Standardized Views (also known as Regular Views)
   • These are what you’ll often hear referred to as regular views. They are essentially queries saved for future use.
   • Every time you run a query against a standard view, Snowflake fetches the most up-to-date data from the underlying table(s). This means you’re always getting real-time data, but the downside is that it may take longer to execute queries, especially on large datasets.
2. Materialized Views
   • Materialized views physically store the results of the view’s query in the database. This allows for much faster data retrieval because the query doesn’t need to be recomputed each time.
   • Materialized views are particularly useful when you have frequently accessed data or complex queries that take time to run. Snowflake automatically refreshes materialized views whenever the underlying data changes, ensuring they stay up to date.
3. Secure Views
   • Secure views provide an extra layer of security, ensuring that no one can see the underlying SQL logic of the view itself. This is important if you’re dealing with sensitive or proprietary data.
   • They work just like standard views but with added security to mask the underlying query logic.

How Views Differ from Regular Tables?

Now, let’s get to the specifics. While tables contain actual data, views only contain the SQL logic that returns the data that is to be displayed. Each time you execute a view, it executes the SQL statement contained in it to retrieve data from the concerned tables. On the other hand, querying a table just retrieves the stored data, which is why querying tables usually takes less time than querying views but takes more storage space.

Think of a table as a physical book on a shelf. The book exists; it’s always there, and you can grab it whenever you need to read it. On the other hand, a view is like a recipe card that tells you how to bake a cake. You still need to gather all the ingredients (data from tables) every time you want that cake!

What are Materialized Views?

Now, let’s take a look at snowflake materialized views.

A materialized view is actually a middle ground between a table and a view. It is a view but with one slight difference, and that is the view that holds the query result in a physical table. That’s why Snowflake doesn’t compute the result of that materialized view from the base table when you execute a query on a materialized view. It just retrieves the actual data, which was calculated earlier, and saves a lot of time compared to queries on Regular views as well as Regular Tables.

Note: Materialized views require Snowflake Enterprise Edition.

Benefits Over Regularized Views

1. Faster Query Performance:
   • Since materialized views store pre-computed data, they can dramatically improve the speed of queries, especially for large and complex datasets. Snowflake doesn’t need to run the entire query each time—it just retrieves the stored result.
2. Efficient for Frequently Accessed Data
   • Materialized views are particularly beneficial when you have frequently accessed, relatively stable data. You don’t want to keep recalculating the same results every time; materialized views save you from that overhead.
3. Automatic Refresh 
   • Snowflake manages the materialized view for you. Whenever data in the underlying table changes, Snowflake automatically refreshes the materialized view. This ensures that the view’s data is always up to date without any manual intervention.

However, materialized views also consume more storage than normal views since materialized views are stored physically. Thus, you can incur additional costs. As such, before creating any materialized views, you should consider whether the costs are offset by the savings from reusing these results frequently enough.

How to Create Different Views

1. Standardized Views

Note: Non-materialized views are commonly referred to as simply “views” in Snowflake.

Step 1: You need to create a Database.

CREATE DATABASE emp;

Step 2: Create a table.

CREATE TABLE employees (
id INTEGER,
name VARCHAR(50),
department VARCHAR(50),
salary INTEGER
);

Step 3: Insert Values into the Table.

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Joe', 'HR', 50000),
       (2, 'Sath', 'IT', 75000),
       (3, 'Kayley', 'Sales', 60000),
       (4, 'Amber', 'IT', 80000),
       (5, 'Sid', 'Marketing', 55000);

Step 4: Create a View.

CREATE VIEW hr_emp AS
SELECT *
FROM employees
WHERE department = 'HR';

The above code creates a view of the employees Table of the HR department.
The output is as shown below:

Step 5: Query your view.

SELECT * FROM HR_EMP;

The output is as shown below:

2. Materialized Views

Steps 1-3: It is the same as the above, i.e., you need to create a Database and a Table and insert values into the Table.

Step 4: Create a materialized view that aggregates the salaries by department.

CREATE MATERIALIZED VIEW emp_sal
AS SELECT
  department,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department;

The output of the code will be as below in the screenshot:

The above query creates a materialized view called emp_sal that calculates the total salaries for each department.

Note: The GROUP BY clause is required in the query definition for creating materialized views.

Step 5: Query your materialized view.

SELECT * FROM emp_sal;

The below screenshot shows the output of the above query.

Difference between Materialized Views, Regular Views, and Regular Tables

Let’s break down the key differences between regular views, materialized views, and our regular, plain old tables.

Feature	Regular Table	Regular View	Materialized View
Performance Benefits			✓
Security Benefits		✓	✓
Simplifies Query Logic		✓	✓
Supports Clustering	✓		✓
Uses Storage	✓		✓
Uses Credit for Maintenance			✓

When Should you Use Materialized Views?

Materialized views are a good fit when you’re dealing with:

• Large datasets where performance is crucial
• Frequently run queries on relatively static data
• Complex queries that take time to compute but don’t change frequently

On the other hand, if you’re working with highly dynamic data that changes frequently, regular views may still be the way to go. They ensure you’re always working with the most up-to-date data without needing to store any results physically.

Conclusion

Snowflake materialized views provide you with the optimal comfort of both speed and efficiency, particularly when dealing with large data. They also require less time to execute queries because relevant results are precalculated and stored. But they are costly with the use of storage space in this respect.

In other words, if performance is your number one priority and a given view does not change frequently, then materialized views are worth looking into. On the other hand, you should use regular views if you require fresh data that is always updated in real-time and if you can afford to spend a little extra time in querying.

You should now understand when and why the Snowflake materialized views are useful. And when your time comes to fine-tune your data performance, materialized views are an advantage in your Snowflake toolbox!

Frequently Asked Questions on Snowflake Materialized Views