To gain an in-depth understanding of materialized views, consider reading the first article in this series. Today, we will discuss the specific implementations and features of materialized views in Snowflake. Materialized views are one of the three most common mechanisms Snowflake uses to view, materialize, and transform data.
What is materialized views?
A materialized view can expedite query performance in analytics by storing the precomputed results of complex queries. This removes the need for expensive computations on the underlying tables, leading to faster response times. This advantage grows with the complexity of the pipeline and the volume of the data being summarized.
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use.
Comparison with tables, regular views, and cached results
In Snowflake, there are three concepts that need to be distinguished from materialized views: tables, regular views, and cached results.
Snowflake temporarily caches query results, enabling faster retrieval if the same query is re-run without table changes. While this is efficient, it is less flexible.
Materialized views and cached query results both enhance query performance, with the former being more flexible but typically slower. Materialized views leverage their cache for unchanged data and the base table for modified data, making them faster than tables.
Unlike materialized views, regular views don't cache data and can't improve performance by caching. They can, however, help generate a more efficient query plan. Both materialized and regular views boost data security by controlling data exposure at the row or column level.
When to use materialized views?
In the fast-paced world of data management, quick and efficient data access and processing are key. Materialized Views shine in this aspect. So, when should you opt for them? The materialized view is useful in the below scenario:
- The underlying base table of the Materialized View does not change frequently.
- The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
- Query results contain results that require significant processing, including: analysis of semi-structured data and aggregates that take a long time to calculate.
- If a query is based on a large table, but you frequently only need a few rows or columns based on certain criteria, an MV can be beneficial.
Advantages of materialized views
Snowflake's materialized views offer unique features:
- They enhance performance for repeated subquery results.
- They are automatically maintained by Snowflake, with a service updating them post-base table changes.
- The data accessed is always current, irrespective of the DML performed on the base table. If a query is run before the update, Snowflake either updates the view or uses the up-to-date parts and retrieves new data from the base table.
How to use materialized views in Snowflake？
How to create a materialized view
In Snowflake, the process of creating a materialized view involves a few steps. The first step is to identify a frequently executed, computationally intensive query for materialization. By materializing this query, you can enhances the performance of future executions.
The subsequent step involves executing the CREATE MATERIALIZED VIEW statement, which facilitates the definition of the name, schema, and underlying query for the materialized view. Specifying the name and schema aids in the effective organization and management of the materialized views.
Following the creation of the materialized view, the REFRESH MATERIALIZED VIEW statement is used to populate it with data. This statement updates the materialized view with the latest data derived from the underlying query, ensuring the information it reflects is the most recent.
It's worth noting that materialized views in Snowflake are presently read-only. Direct data writing into them is not possible. When the need arises to update the data in a materialized view, a refresh, either full or incremental, is required.
How to refresh a materialized view
To update a materialized view with the latest data, Snowflake offers two methods: full and incremental refreshes. A full refresh recomputes the view completely, suitable for smaller datasets or infrequent updates for thorough up-to-dateness. Incremental refresh, more efficient for larger datasets or frequent updates, adjusts the view with recent changes using metadata and logs to track modifications in base tables.
How to alter and drop a materialized view
To adjust a materialized view's structure in Snowflake, employ the ALTER MATERIALIZED VIEW command for renaming, schema changes, or query adjustments.
Renaming optimizes naming clarity or conformity. Altering the schema facilitates reorganization or consolidation within the database. Adjusting the query refines stored data, offering the flexibility to adapt to changing data needs.
If a materialized view becomes redundant, remove it with DROP MATERIALIZED VIEW, permanently erasing it and its data. Remember, this action is irreversible.
Use cases
- Business Intelligence (BI) and Reporting:
For a company needing to generate daily, weekly, and monthly sales reports from a large transactional database, materialized views can precompute and store aggregated data. This approach speeds up report generation and gives users quick access to the latest figures.
- E-commerce Product Recommendations:
An e-commerce website providing personalized product recommendations can benefit from materialized views. By storing and updating user-product interaction data, the system can quickly generate recommendations without querying the entire user history each time.
- Geospatial Data Analysis:
A mapping application providing near real-time traffic updates and route recommendations can use materialized views to store geospatial data indexed for quick retrieval. This ensures updates and recommendations are generated quickly, even with vast amounts of dynamic location data.
- Financial Analysis and Risk Management:
Financial institutions can use materialized views to store and aggregate trading data, enabling rapid risk assessment and financial analysis. This is especially crucial in fast-paced financial markets.
- Inventory Management and Supply Chain Optimization:
Companies managing large inventories can use materialized views to store inventory data and demand forecasting results. This enables quick decision-making in inventory management and supply chain optimization.
Resources
DDL commands for materialized views
Materialized views are essential database objects. Snowflake provides these DDL commands for creating and maintaining materialized views:
DML operations on materialized views
Snowflake does not permit standard DML operations (e.g., INSERT, UPDATE, DELETE) on materialized views, nor does it allow users to truncate them.
For more details, see Limitations on Working With Materialized Views.
Access control rivileges
There are three types of privileges related to materialized views:
- Privileges on the schema containing the materialized view.
- Privileges directly on the materialized view.
- Privileges on the database objects (e.g., tables) accessed by the materialized view.
For granting and revoking privileges on materialized views, use these standard commands:
>
>
>
In this blog post, we introduced materialized views in Snowflake. We began by exploring what materialized views are and how they differ from tables, regular views, and cached results. Then, we delved into the process of creating materialized views and the methods for refreshing them as the underlying data changes. Finally, we presented some classic use cases and basic commands in Snowflake. > >
