Snowflake Views Vs. Materialized Views: What's the Difference?

In this article, we will explore the powerful capabilities of Snowflake views to simplify complex tables and streamline query workflows.

We'll begin by introducing what Snowflake views are, outlining their key differences, and discussing the pros and cons of each type. Additionally, we'll delve into various use cases that highlight how Snowflake non-materialized and materialized views can enhance query performance and address common workflow challenges.

So, if you're tired of struggling with unwieldy tables and lengthy query times, read on to discover how Snowflake views can make your life easier.

What Is a View and What Are the Different Types of Snowflake Views?

A view in Snowflake is a database object that allows you to see the results of a query as if it were a table. It's a virtual table that can be used just like a regular table in queries, joins, subqueries—and various other operations. Views serve various purposes, including combining, segregating, and protecting data.

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>.

Here's a simple example:

CREATE VIEW my_custom_view AS
SELECT column1, column2
FROM my_table
WHERE column3 = 'value';

What are the types of Views in Snowflake?

• Non-Materialized (referred to as “views”)
• Materialized Views
• Secure Views

What is a Non-Materialized View (Snowflake views)?

Non-materialized view is a virtual table whose results are generated by running a simple SQL query whenever the view is accessed. The query is executed dynamically each time the view is referenced in a query, so the results are not stored for later/future use. Non-materialized views are very useful in simplifying complex queries and reducing redundancy. It can help you remove unnecessary columns, refine and filter out unwanted rows, and rename columns in a table, making it easier to work with the data.

Non-materialized views are commonly referred to as simply "views" in Snowflake.

The benefit of non-materialized views is that they are really very easy to create, and they do not consume storage space because the results are not stored for later. But remember that they may result in slower query performance as the underlying query must be executed each time the view is referenced.

Non-materialized views have a variety of use cases, including making complex queries simpler, creating reusable views for frequently used queries, and ensuring secure access to data by limiting the columns and rows that particular users can see or access.

Now, let's create one simple example of a non-materialized view in Snowflake. So to do that, let's first create one sample demo table and insert some dummy data into it:

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

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'User1', 'HR', 50000),
       (2, 'User2', 'IT', 75000),
       (3, 'User3', 'Sales', 60000),
       (4, 'User4', 'IT', 80000),
       (5, 'User5', 'Marketing', 55000);

Now, let's create a view called "it_employees" that only includes the employees from the IT department:

CREATE VIEW it_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'IT';

So, when we query the "it_employees" view, we'll only see the data for the IT department employees:

SELECT * FROM it_employees;

What are Snowflake Materialized Views?

A Snowflake materialized view is a precomputed view of data stored in a table-like structure. It is used to improve query performance and reduce resource usage by precomputing the results of complex queries and storing them as cached result sets. Whenever subsequent queries are executed against the same data, Snowflake can access these materialized views directly rather than recomputing the query from scratch each time. However, it's important to note that the actual query using the materialized view is run on both the materialized data and any new data added to the table since the view was last refreshed. Overall, Snowflake materialized views can help improve query speed and optimize costs.

Note: Snowflake materialized views are exclusively accessible to users with an Enterprise Edition subscription.

How to Create a Materialized View?

Creating a materialized view in Snowflake is easy.

Here is a step-by-step example of how to create a materialized view in Snowflake

Step 1:  let's create a table “employees_table” and insert some dummy data:

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

INSERT INTO employees_table VALUES
  (1, 'User1', 'Sales', 50000),
  (2, 'User_2', 'Marketing', 60000),
  (3, 'User3', 'Sales', 55000),
  (4, 'User_4', 'Marketing', 65000),
  (5, 'User5', 'Sales', 45000);

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

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

The above query will create a materialized view called “materalized_view_employee_salaries” that calculates the total salaries for each department by aggregating the salaries in the “employees_table” table.

Note: GROUP BY clause is required in the query definition of the materialized view.

Step 3: You can then query the materialized view just like you would a regular table:

SELECT * FROM materalized_view_employee_salaries;

The output should show you the total salaries for each department, computed using the materialized view.

And that is how simple it is to create a Materialized view.

What are the benefits & limitations of Using a Snowflake Materialized View?

A Snowflake materialized view offers several benefits and limitations to consider when deciding whether to use it.

Benefits of using a Snowflake materialized view include:

• Accelerated query performance for complex queries that require significant processing time.
• Reduced query latency by providing pre-computed results for frequently executed queries.
• Efficient incremental updates of large datasets.
• Minimized resource usage and reduced compute costs by executing queries only against new data added to a table rather than the entire dataset.
• A consistent interface for users to access frequently used data while shielding them from the underlying complexity of the database schema.
• Faster query performance for geospatial and time-series data, which may require specialized indexing and querying techniques that can benefit from pre-computed results.

However, it's important to note that Snowflake materialized views also come with some limitations, including:

• The ability to query only a single table.
• No support for joins, including self-joins.
• The inability to query materialized views, non-materialized views, or user-defined table functions.
• The inability to include user-defined functions, window functions, HAVING clauses, ORDER BY clauses, LIMIT clauses, or GROUP BY keys that are not within the SELECT list.
• The inability to use GROUP BY GROUPING SETS, GROUP BY ROLLUP, or GROUP BY CUBE.
• The inability to include nested subqueries within a Snowflake materialized view.
• The limited set of allowed aggregate functions, with no support for nested aggregate functions or combining DISTINCT with aggregate functions.
• The inability to use aggregate functions AVG, COUNT, MIN, MAX, and SUM as window functions.
• The requirement that all functions used in a Snowflake materialized view must be deterministic.
• The inability to create a Snowflake materialized view using the Time Travel feature.

While Snowflake materialized views can provide significant performance benefits, it's important to consider their limitations when deciding whether to use them.

What is the difference between materialized view and standard view in Snowflake?

Here are some key main differences between Snowflake non-materialized View and Materialized View:

What are the cost differences between Snowflake views and Snowflake materialized views?

There are significant differences between the costs of Snowflake Views and Snowflake Materialized views, as noted below:

What are Snowflake Secure Views?

Snowflake secure views are a type of view in Snowflake that provides enhanced data privacy and security. These views prevent unauthorized users from accessing the underlying data in the base tables and restrict the visibility of the view definition to authorized users only.

Secure views are created using the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command and are recommended for use when limiting access to sensitive data. BUT, remember that they may execute more slowly than non-secure views, so the trade-off between data privacy/security and query performance should be carefully considered.

You can refer to this official Snowflake documentation to learn more about secure views.

Conclusion

In conclusion, both Snowflake non-materialized views and Snowflake materialized views offer benefits and drawbacks, and choosing between the two depends on the specific use case. Non-materialized views are suitable for ad-hoc queries or constantly changing data, while materialized views are ideal for frequently queried data that is relatively static. Materialized views can provide significant performance gains but come at the cost of increased storage and compute usage, as well as additional costs each time the base table is updated. It's important to carefully evaluate your needs and use cases before selecting a view type to ensure optimal query performance and cost efficiency.

FAQs

What are the types of Snowflake views?

The types of Snowflake views are non-materialized (referred to as "views"), materialized views, and secure views.

What are the advantages of using a Snowflake materialized view?

• Accelerated query performance
• Reduced query latency
• Efficient incremental updates of large datasets.
• Minimized resource usage
• Faster query performance for geospatial and time-series data

What are the disadvantage of using a Snowflake materialized view?

Limitations of using a materialized view include the inability to query multiple tables, lack of support for joins, and limitations on functions, clauses, and nested subqueries that can be used.

What are Snowflake secure views?

Snowflake secure views provide enhanced data privacy and security by preventing unauthorized users from accessing the underlying data in the base tables and restrict the visibility of the view definition to authorized users only.

What is the difference between materialized view and standard view in Snowflake?

1. Snowflake Materialized Views

• Query from multiple tables: No
• Support for self-joins: No
• Pre-computed dataset: Yes
• Computes result on-the-fly: No
• Query speed: Faster
• Compute cost: Charged on base table update
• Storage cost: Incurs cost
• Suitable for complex queries: Yes
• Suitable for simple queries: No

2. Non-Materialized Views

• Query from multiple tables: Yes
• Support for self-joins: Yes
• Pre-computed dataset: No
• Computes result on-the-fly: Yes
• Query speed: Slower
• Compute cost: Charged on query
• Storage cost: No cost
• Suitable for complex queries: No
• Suitable for simple queries: Yes