This content originally appeared on DEV Community and was authored by Pramit Marattha
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 AS .
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;
Creating snowflake materialized view for employee salaries 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 are the key differences between Snowflake Views and Materialized Views?
Here are some key main differences between Snowflake non-materialized View and Materialized View:
| Feature | Snowflake Materialized Views | Non-Materialized Views |
|---|---|---|
| Query from multiple tables | No | Yes |
| Support for self-joins | No | Yes |
| Pre-computed dataset | Yes | No |
| Computes result on-the-fly | No | Yes |
| Query speed | Faster | Slower |
| Compute cost | Charged on base table update | Charged on query |
| Storage cost | Incurs cost | No cost |
| Suitable for complex queries | Yes | No |
| Suitable for simple queries | No | Yes |
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:
| Snowflake Non-Materialized Views | Snowflake Materialized Views | |
|---|---|---|
| Compute cost | Charged when queried | Charged when base table is updated |
| Storage cost | None | Incurs a cost for storing the pre-computed output |
| Suitable for | Frequently changing data | Infrequently changing data |
| Compute cost (frequency of updates) | More suitable for tables with constant streaming updates | Less suitable for frequently updated tables |
| Overall compute cost | Directly proportional to the size of the underlying base table | Directly proportional to the size of the underlying base table and frequency of updates |
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.
This content originally appeared on DEV Community and was authored by Pramit Marattha
Pramit Marattha | Sciencx (2023-05-18T06:32:49+00:00) Snowflake Views Vs. Materialized Views: What’s the Difference?. Retrieved from https://www.scien.cx/2023/05/18/snowflake-views-vs-materialized-views-whats-the-difference/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.