Snowflake Materialized Views: A Comprehensive Guide 101

This post provides a comprehensive overview of Snowflake Materialized Views.

Table of Contents

• Introduction to Snowflake Materialized Views
• Benefits of Snowflake Materialized Views
• Intended Use Cases of Snowflake Materialized Views
• The Flow of Snowflake Materialized Views
• Creating Snowflake Materialized Views
• Joining Snowflake Materialized Views
• Access Control on Snowflake Materialized Views
• Estimating and Controlling Costs of Snowflake Materialized Views
• Limitations of Snowflake Materialized Views
• Conclusion

Introduction to Snowflake Materialized Views

Materialized Views are precomputed views that periodically cache the results of a query for increased performance and efficiency. Business Intelligence (BI) users usually need to perform complex and expensive queries on large tables in your Snowflake Data Warehouse—for example, SELECT statements that perform aggregations on tables that contain billions of rows.

If you have some query logic that is often used by your Data Analysts, you can materialize that SQL as a table, and then the results of that query are going to be very fast when required by a downstream calculation, in the Business Intelligence (BI) layer, or for other business use cases.

This feature is supported in some of the major open-source and commercial Data Warehousing solutions such as Azure SQL Database, BigQuery, MaxCompute, PostgreSQL, Redshift, Oracle, etc.

The following are the key characteristics of Snowflake’s Materialized Views:

• Zero maintenance: No user inputs are required.
• Always fresh: A materialized view is always consistent with the base table.
• Smart tuning: Snowflake will reroute any query to use a materialized view if the query can be resolved by querying the materialized view.

Benefits of Snowflake Materialized Views

Snowflake Materialized Views provide the following benefits:

• Better performance and/or efficiency with queries that have aggregate functions.
• There are enormous cost savings for computations that can be incurred because Snowflake charges you for scanning data and this can get very expensive.
• Automatic query optimization. The Snowflake query optimizer can exploit materialized views to automatically rewrite/reroute incoming queries. 
• The same resilience and high availability as Snowflake tables.
• Automatic refresh triggers that repopulate the materialized tables.
• Partial refresh where the refresh triggers identify only the records that are new in the table since the last refresh event, and then recalculates and incrementally updates the data in the base table.

Intended Use Cases of Snowflake Materialized Views

Snowflake Materialized Views target the following use cases:

• Business Intelligence (BI) users who run a lot of analytical queries on historical data, and current data.
• Improved performance and efficiency for predictable and repeated queries that require significant processing. The Snowflake engine is always recomputing aggregate calculations on a regular basis to optimize query performance.
• Streaming aggregation: Snowflake materialized views natively support real-time streaming capabilities to provide access to up-to-date decisions.

The Flow of a Snowflake Materialized Views

1. Application users create transactions.
2. Data is committed to a source table.
3. An internal trigger in the Snowflake’s source table populates the materialized view log table.
4. A fast refresh is initiated.
5. DML changes that have been created since the last refresh are applied to the materialized view.
6. Users can now query data from the materialized view which contains the latest snapshot of the source table’s data.

Creating Snowflake Materialized Views

You can create a Materialized View through the Snowflake web UI, the snowsql command-line tool, or the Snowflake API. For example, in the Snowflake web UI you can use the following DML statement that creates a simple table, loads data into it, and creates a materialized view:

create table inventory (product_id integer, wholesale_price float,description varchar);
create or replace materialized view wholesale_materialized_view as
  select product_id, wholesale_price from inventory;
 
insert into inventory (product_id, wholesale_price, description) values
  (1, 1.00, 'cog');

Select data from the view:

select product_id, wholesale_price from wholesale_materialized_view;
+------------+-----------------+
| PRODUCT_ID | WHOLESALE_PRICE |
|------------+-----------------|
|          1 |               1 |
+------------+-----------------+

For more detailed examples, check out these SQL commands used to create and manage materialized views:

• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW
• DROP MATERIALIZED VIEW
• DESCRIBE MATERIALIZED VIEW
• SHOW MATERIALIZED VIEWS

Joining Snowflake Materialized Views

You can join a Materialized View with another table or another view. The following example shows a ‘create table‘ statement followed by a creating a non-materialized view statement that shows profits by joining a Materialized View to the table:

create table sales (product_id integer, quantity integer, price float);
 
insert into sales (product_id, quantity, price) values 
   (1,  1, 1.99);
 
create or replace view profits as
  select m.product_id, sum(ifnull(s.quantity, 0)) as quantity,
      sum(ifnull(quantity * (s.price - m.wholesale_price), 0)) as profit
    from wholesale_materialized_view as m left outer join sales as s on s.product_id = m.product_id
    group by m.product_id;

Select data from the materialized view:

select * from profits;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
+------------+----------+--------+

Access Control on Snowflake Materialized Views

Access granted to a Materialized View is done:

• On the schema that contains the materialized view.
• On the materialized view itself.
• On the database objects (e.g. tables) that the materialized view accesses.

To create a materialized view, you have to have the CREATE MATERIALIZED VIEW privilege on the schema that will contain the materialized view. To grant the necessary permissions, execute the following statement:

 
grant create materialized view on schema <schema_name> to role <role_name>;

Estimating and Controlling Costs of Snowflake Materialized Views

Materialized Views impact your costs for both storage and compute resources. As of the time of writing this post, Snowflake does not provide tools to estimate the costs of maintaining materialized views.

You can view the billing costs for maintaining materialized views using either the Snowflake web interface or by running the following SQL statement:

select * from table(information_schema.materialized_view_refresh_history()); 

You can reduce the cost of running materialized views by carefully choosing how many views to create, which tables to create them on, and each view’s definition (including the number of rows and columns in that view).

Limitations of Snowflake Materialized Views

The following limitations apply to the use of Snowflake’s Materialized Views:

• Materialized Views are only available on the Snowflake Enterprise Edition.
• Materialized Views are only as up-to-date as the last time you ran the query.
• A Materialized View can query only a single table.
• There is limited query support. Snowflake Materialized Views do not support all ANSI SQL functionality. Snowflake only supports queries containing aggregate, filter, and table scans.
• There is a large chunk of aggregate functions that are not allowed in a Materialized View definition.
• Materialized Views are not monitored by Snowflake.
• Snowflake does not provide resource monitors to prevent unexpected credit usage.

To explore more about Snowflake Materialized Views, refer to the following links:

• Read the materialized views FAQ.
• Learn how to Create and use materialized views.
• Learn about Snowflake Materialized Views best practices.

Conclusion

In this article, you gained a basic understanding of Snowflake Materialized Views. You learned about the detailed steps to set up these Materialized Views.

You can make more of your insights by centralizing your various data sources into Snowflake faster with Hevo Data. Hevo supports real-time streaming from 100+ data sources and allows you to easily replicate these sources into your Snowflake data warehouse with just a few clicks. Our data ingestion tool stands out in terms of ease of use, the fact that it is zero maintenance, and that we have a knowledgeable and highly responsive customer support team that supports multiple time zones.

You can get started with Hevo using the following steps:

Step 1: Input your desired data source.

Step 2: Select your mode of extraction, for example: Change Stream or OpLog.

Step 3: Authorize your data source.

Step 4: Select your target data warehouse.

Step 5: Enable automatic schema mapping to infer and replicate the schema from your source table and confirm.

The data will now start flowing in!

VISIT OUR WEBSITE TO EXPLORE HEVO

Check out the short video to get a product overview.

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Please share your thoughts on Snowflake Materialized Views in the comments!