Photo by Shiro hatori on Unsplash

Materialized Views in Snowflake: An Overlooked Efficiency Booster?

In the realm of database design and management, it’s often the less-known features that can make the most significant impact on your system’s efficiency and performance. One such feature is the materialized view. While not as commonly discussed as tables or indexes, materialized views can be an extremely powerful tool when used correctly. In this post, we’ll uncover the concept of materialized views, their advantages, and how they can contribute to a well-optimized database system.

What is a Materialized View?

A materialized view is a database object that contains the results of a query. Unlike a standard view, which is just a saved SQL query that is executed each time the view is called, a materialized view stores the actual result of the query in a separate schema, much like a table.

The idea behind materialized views is simple but powerful: by storing the result of a query, you can avoid the need to re-run complex queries and calculations each time the data is accessed.

Note: Materialized views require Enterprise Edition.

When to Use Materialized Views

Materialized views are particularly useful in scenarios where:

1. Your Database Faces Heavy Read Operations: If your database faces heavy read operations, especially those involving complex calculations (aggregations, joins, etc.), materialized views can save substantial processing time by storing the result.
2. Your Data Doesn’t Change Frequently: Materialized views work best when the underlying data isn’t updated frequently. If your data changes often, the overhead of maintaining the materialized view (i.e., updating it each time the underlying data changes) could outweigh the benefits.
3. You Need to Improve Query Performance: If certain queries in your system are slow, creating a materialized view based on those queries can dramatically improve performance.

The Pros and Cons of Materialized Views

Like all database features, materialized views come with their own set of benefits and drawbacks:

Pros:

1. Query Performance: As discussed, materialized views can significantly speed up query performance by storing the results of complex queries.
2. Reduced Load: By reducing the need to re-run complex queries, materialized views can lessen the load on your database, freeing up resources for other operations.

Cons:

1. Storage Space: Since materialized views store query results, they require additional storage space. Depending on the size and complexity of your queries, this could be a consideration.
2. Maintenance Overhead: Whenever the underlying data changes, the materialized view must be updated (either manually or automatically, depending on your database system). This can add to the maintenance overhead.

Implementing Materialized Views in Snowflake.

In Snowflake, creating a materialized view involves selecting columns from an existing table. However, as pointed out by David Atlas, it’s important to note that materialized views do not support joins in Snowflake.

Here is an example of creating a materialized view:

CREATE MATERIALIZED VIEW IF NOT EXISTS my_materialized_view AS 
SELECT s.customer_id, s.date, 
AVG(s.sales) as average_sales, 
COUNT(DISTINCT s.product_id) as unique_products 
FROM sales_table s 
GROUP BY s.customer_id, s.date 
CLUSTER BY (s.customer_id, s.date);

In this example, the materialized view my_materialized_view is created based on a SELECT statement that retrieves the customer_id, date, average sales, and the count of unique product_id from the sales_table. The results are grouped by customer_id and date. The CLUSTER BY clause is used to optimize the performance of queries that filter based on the customer_id and date.

Restrictions of Materialized Views in Snowflake

While materialized views bring tremendous benefits to data retrieval speed and database performance, they do come with certain restrictions. It’s essential to be aware of these limitations when planning to use materialized views in your Snowflake database.

1. DML Operations: Direct Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE are not allowed on a materialized view. These views are read-only, meaning you can only SELECT from them.
2. Non-Deterministic Functions: Functions that produce different results every time they’re called, such as CURRENT_DATE(), CURRENT_TIMESTAMP(), and RANDOM(), cannot be included in a materialized view.
3. Certain SQL Constructs: Snowflake doesn’t allow specific SQL constructs within materialized views. These include subqueries, Common Table Expressions (CTEs), sequences, and JOINs across databases.
4. Temporary Tables or Views: Materialized views can’t reference temporary tables or views.
5. Specific Snowflake Functions: There are some Snowflake-specific functions and operations that cannot be used in the definition of a materialized view. This includes metadata functions like GET_DDL, data loading functions like COPY INTO, commands for working with named stages such as SELECT ... FROM @<named stage>, lateral flatten functions, and certain window, set and multiset, and date/time functions.
6. User-Defined JavaScript Functions and Table Functions: While Snowflake does support JavaScript for user-defined functions, these can’t be used in a materialized view. Similarly, table functions, which return a table data type, are not permitted.
7. Streams and Tasks: Snowflake’s STREAM object (which tracks changes to a table) and TASK object (which allows scheduling of SQL statements) cannot be included in a materialized view.

Altering a Materialized View

Snowflake maintains materialized views automatically. This means that when data in the underlying table changes, the materialized view is updated in the background.

However, if you want to control when the materialized view is updated, Snowflake provides SUSPEND and RESUME options in the ALTER MATERIALIZED VIEW statement.

The SUSPEND option suspends the maintenance (updates) and use of the materialized view. While the view is suspended, updates to the base table are not propagated to the materialized view. This means that the data in the materialized view could become outdated if the base table changes.

To suspend updates to a materialized view, use the following command:

ALTER MATERIALIZED VIEW my_materialized_view SUSPEND;

The RESUME option resumes using the materialized view and its maintenance. If the view is clustered, it also implicitly resumes reclustering of that view. This means that updates to the base table will once again be propagated to the materialized view, and the view's data will be accessible for querying.

To resume updates to a materialized view, use the following command:

ALTER MATERIALIZED VIEW my_materialized_view RESUME;

For more details on these and other ALTER MATERIALIZED VIEW options, refer to the Snowflake documentation.

Dropping a Materialized View in Snowflake

There may be situations where you no longer need a materialized view. In such scenarios, you can drop the view using the DROPcommand, like you would with any other object. This will remove the view from the database. Here’s an example:

DROP MATERIALIZED VIEW IF EXISTS my_materialized_view;

Be careful when using this command, as dropping a materialized view will permanently delete it and the data it contains from your database.

Describing a Materialized View in Snowflake

If you want to inspect the structure of a materialized view, Snowflake offers the DESCRIBE command. This command displays the names, data types, and other details of the columns in the view. Here's an example:

DESCRIBE MATERIALIZED VIEW my_materialized_view;

Showing Materialized Views in Snowflake

To display a list of all the materialized views in your current schema, you can use the SHOW command. Here's an example:

SHOW MATERIALIZED VIEWS;

This command returns a list of all materialized views in the current schema. Each row in the result includes the name of a materialized view, along with information such as its owner, schema, database, and creation time. It’s a handy command to get a quick overview of all the materialized views in your schema.

While not suitable for every scenario, materialized views can be an overlooked efficiency booster in your database design toolkit. They can dramatically improve query performance and reduce the load on your database, provided the benefits outweigh the extra storage space and maintenance overhead. By understanding and judiciously using this powerful feature, you can take a big step toward a well-optimized database system.