Snowflake Materialized Views: A Fast, Zero-Maintenance, Accurate Solution

If you could automatically reduce your network resource load and simultaneously speed up query results, you would, wouldn’t you? In fact, that might be a key focus of your job. Snowflake has you covered with a brand new generally available feature: Snowflake Materialized Views and Maintenance (Snowflake MVs).

A materialized view (MV) is a database object that contains the results of a query. Unlike a view, it’s not a window into a database. Rather, it is a separate object holding query results with data refreshed periodically. MVs speed delivery of query results against very large sets of data. So why isn’t everyone using them?

Older versions of MVs have their problems. For example, refreshing data periodically can lead to inconsistent or out-of-date results when you access MVs.  And beyond that, data manipulation language (DML) operations (for adding, deleting, and modifying data) traditionally experienced slow-downs when they used MVs. In a business climate where outdated data can mean being outdone by competitors, performance degradation isn’t acceptable.

Snowflake Modernizes Materialized Views

Snowflake now offers a new, modern approach to MV capabilities through a version that avoids the pain points of traditional approaches. Snowflake MVs:

• Ensure optimal speed (no slowdowns)
• Deliver query results via MVs that are always current and consistent with the main data table
• Provide exceptional ease of use via a maintenance service that continuously runs and updates MVs in the background

Implementing Snowflake MVs enhances data performance by helping you filter data so you can perform resource-intensive operations and store the results, eliminating the need to continuously or frequently perform resource-intensive operations.

To prevent MVs from becoming out-of-date, Snowflake performs automatic background maintenance of MVs. When a base table changes, a background service automatically updates all MVs defined on the table.

Additionally, Snowflake MVs can help you detect data anomalies quickly, enabling rapid response to problems. For example, you can create one MV that contains the most recent data and another MV that stores unusual data. You can then create a non-materialized view that joins the two tables and shows recent data that matches unusual historical data, helping you quickly detect situations such as an impending denial-of-service attack.

Several Snowflake customers have been testing MVs and the company is now pleased to announce the general availability of Snowflake MVs in the Enterprise Edition. The initial version of Snowflake MVs includes the ability to:

• Define numerous MVs with different clustering keys, leading to improved query performance for different access patterns
• Materialize single table aggregations
• Materialize query results and analysis against semi-structured data using FLATTEN queries or approximate query processing (sampling)

Unlike the cost, maintenance, and performance degradation associated with traditional data warehouse MV solutions, Snowflake MVs give you faster performance with little to no manual effort—yet another demonstration of Snowflake’s commitment to fast, zero-maintenance, cloud-built data warehouse solutions.

Read more about Snowflake Materialized Views and automatic maintenance here and here.