I\u2019ve used both Snowflake views and materialized views with varying degrees of success. Both have their place and it\u2019s on us to determine when to use them based on what they offer. This guide will help you in understanding how views and materialized views work in Snowflake and show you practical examples along with potential benefits.<\/p>\n\n\n\n
Before we dive into when to use materialized views, let’s quickly refresh our understanding. A materialized view in Snowflake is essentially a pre-computed result set of a query that’s stored and maintained automatically. Think of it as a cache that Snowflake manages for you, keeping it in sync with your source data.<\/p>\n\n\n\n
Let’s compare traditional views with materialized views:<\/p>\n\n\n\n
<\/a> Traditional views serve as stored queries that execute at runtime. Their characteristics make them ideal for certain scenarios:<\/p>\n\n\n <\/a> On the other hand, materialized views basically are turned into a table that is refreshed anytime data is changed. Here is an overview of their attributes:<\/p>\n\n\n Let’s examine a typical business intelligence scenario that many organizations face. Consider a sales analysis system that needs to provide:<\/p>\n\n\n Here’s an example query that many organizations might run frequently:<\/p>\n\n\n\n This query performs several resource-intensive operations:<\/p>\n\n\n When comparing regular views and materialized views in Snowflake, several key performance metrics reveal distinct operational characteristics. Let’s examine their performance profiles using a real-world business intelligence scenario.<\/p>\n\n\n\n A regular view in Snowflake executes its underlying query logic with each access. For our sample sales analysis query on a medium-sized dataset (about 100 million sales records, 1 million customers, and 10,000 products over multiple years), this means consistently processing raw data through multiple steps. Each execution requires 45-60 seconds of processing time as Snowflake reads from all three source tables, executes join operations, and calculates aggregations from scratch.<\/p>\n\n\n\n This processing consumes approximately 0.5 compute credits per execution due to the repeated full-scale data processing requirements. However, regular views require no additional storage space as they only maintain the query definition. On the other hand, materialized views present a different performance model. The initial creation requires a one-time cost of approximately 60 seconds, comparable to a single execution of the regular view. Subsequent query execution times drop dramatically to 1-2 seconds, representing a 95% improvement over the regular view. This efficiency stems from accessing pre-computed results rather than processing raw data.<\/p>\n\n\n\n One thing that is an interesting trade off is computing costs. While you do have to complete the results, once you are using the materialized views in queries the amount of compute may be quite a bit lower. The compute credit consumption reflects this efficiency, using only 0.02 credits per query\u2014a 96% reduction compared to the regular view. This significant reduction occurs because the materialized view eliminates the need for repeated raw data processing and join operations.<\/p>\n\n\n\n Materialized views introduce two additional operational considerations. First, they require periodic refresh operations, taking a few seconds to update when source data changes. Snowflake optimizes these refreshes through incremental maintenance, processing only changed records. Second, they incur storage costs to store the pre-computed results and aggregated data.<\/p>\n\n\n\n Consider a scenario where this report runs multiple times daily:<\/p>\n\n\n Regular View Operation Costs:<\/strong><\/p>\n\n\n Materialized View Operation Costs<\/strong>:<\/p>\n\n\n The materialized view implementation delivers a net monthly savings of $5,432, representing a 90% reduction in operational costs.<\/p>\n\n\n\n The materialized view pattern demonstrates maximum effectiveness when a few different things occur. when the query frequency exceeds update frequency of the underlying data, allowing the benefits of pre-computation to outweigh refresh overhead. Implementation supports consistent reporting requirements, such as daily dashboards and regular reports. The queries involve complex calculations that benefit from reuse rather than recalculation. The source datasets are large, but the aggregated results maintain a significantly smaller footprint.<\/p>\n\n\n\n This performance analysis demonstrates that while both view types serve important purposes, materialized views offer substantial performance and cost benefits for frequently accessed, computation-heavy queries where data freshness requirements align with scheduled refresh intervals.<\/p>\n\n\n\n One of the smartest ways to decide whether you need a materialized view is to look at your query patterns. Snowflake makes this incredibly easy with its For example, these queries would be considered different:<\/p>\n\n\n\n To identify truly repetitive patterns, you might need to:<\/p>\n\n\n Here’s a query that will show you the number of times a query has been executed and some additional statistics that will help you to see at a high level how much the query has been used and what it is likely costing you:<\/p>\n\n\n\n When analyzing results, look for certain patterns. While your needs certainly may be different, here is what I do as a starting point:<\/p>\n\n\n Real-world benchmark from a customer case, where they are implementing a Daily Sales Dashboard.<\/p>\n\n\n\n Before implementing materialized views for commonly reused queries:<\/p>\n\n\n After identifying and implementing the materialized views:<\/p>\n\n\n In this section, I will discuss the sweet spot to realize that you need to use materialized views versus regular views. The sweet spot for materialized views occurs when you have complex queries that are executed frequently but don’t require real-time data updates. Let’s explore two common scenarios where materialized views shine.<\/p>\n\n\n\n Ideal scenarios for materialized views include complex aggregations that combine data from multiple tables and perform heavy computations are perfect candidates for materialization, especially when the underlying data updates are predictable and moderate. Consider a typical business intelligence scenario where you need daily sales summaries that combine transaction data with customer and product information.<\/p>\n\n\n\n This type of analysis typically involves:<\/p>\n\n\n Here’s an example materialized view for such a scenario:<\/p>\n\n\n\n In a typical enterprise environment, this view might process:<\/p>\n\n\n The performance impact is substantial when dealing with this scale of data:<\/p>\n\n\n Another sweet spot for materialized views is when you need to combine data from multiple tables to create comprehensive views of your data, often called “360-degree views.” These views typically bring together information from across your data warehouse but don’t require real-time updates.<\/p>\n\n\n\n Consider this example that creates a customer-centric view combining orders, products, and returns data:<\/p>\n\n\n\n This type of view typically processes:<\/p>\n\n\n The benefits of materialization in this scenario include:<\/p>\n\n\n In both scenarios, the materialized views provide significant benefits because:<\/p>\n\n\n While materialized views offer significant benefits in many scenarios, there are specific situations where they might not be the optimal solution. Let’s explore these cases to help you make informed decisions about when to use alternative approaches.<\/p>\n\n\n\n One of the primary scenarios where materialized views may not be ideal is when dealing with rapidly changing data. Consider real-time trading systems as an example. In such environments, data can change thousands of times per second, and maintaining materialized views becomes more costly than querying the base tables directly.<\/p>\n\n\n\n To determine if your data is too volatile for materialized views, you can analyze the refresh patterns using this query:<\/p>\n\n\n\n This query helps you understand:<\/p>\n\n\n Consider avoiding materialized views when you see patterns like:<\/p>\n\n\n For example, in a trading system scenario:<\/p>\n\n\n In these cases, consider alternatives such as:<\/p>\n\n\n Understanding storage costs is crucial when implementing materialized views in Snowflake. Let’s explore how to analyze these costs and interpret the results to make informed decisions about materialized view implementations.<\/p>\n\n\n\n To effectively monitor and analyze storage costs for your materialized views, you can use the following query:<\/p>\n\n\n\n Example result set<\/strong><\/p>\n\n\n\n This query and result set provides several key metrics that help you understand your storage utilization:<\/p>\n\n\n In this section we will look at some of the ways you can calculate and predict costs of storage.<\/p>\n\n\n\n The fundamental storage calculation in Snowflake converts raw byte measurements into more manageable gigabyte values using the expression bytes\/POW(1024,3). (Note the POW<\/a> function raises the number in the first parameter to the power in the second parameter.) This conversion follows the standard binary progression of digital storage units. Starting with the basic unit of a byte, we progress through kilobytes, megabytes, and finally to gigabytes. Each step up multiplies by 1024: a kilobyte contains 1024 bytes, a megabyte encompasses 1024 kilobytes (or 1024\u00b2 bytes), and a gigabyte consists of 1024 megabytes (or 1024\u00b3 bytes).<\/p>\n\n\n\n To illustrate this conversion, consider a materialized view consuming 1073741824 bytes of storage. When we apply our conversion formula (1073741824\/POW(1024,3)), the result simplifies to exactly 1.0 GB, providing a more intuitive measure of the storage footprint.<\/p>\n\n\n\n Snowflake’s storage cost calculation builds upon this size conversion by incorporating the standard rate of $40 per terabyte per month. The formula bytes\/POW(1024,3) * 40 provides a direct monthly cost estimate in dollars. This calculation takes the size in gigabytes and applies the proportional cost based on Snowflake’s terabyte pricing.<\/p>\n\n\n\n For practical application, consider a materialized view occupying 100GB of storage. The monthly cost calculation would be: 100 GB * ($40\/1024) \u2248 $3.91. This demonstrates how even relatively large, materialized views can maintain reasonable storage costs while providing significant performance benefits.<\/p>\n\n\n\n This cost framework helps data engineers and architects make informed decisions about materialized view implementations by providing clear visibility into the storage cost implications of their design choices. Understanding these metrics becomes particularly valuable when evaluating the trade-offs between query performance improvements and associated storage costs.<\/p>\n\n\n\n Effective monitoring of materialized views is crucial for maintaining optimal performance and cost efficiency. This section covers key monitoring strategies and maintenance practices to ensure your materialized views continue to provide value over time.<\/p>\n\n\n\n One of the most critical aspects to monitor is how your materialized views handle refresh operations. Refreshes occur when the underlying data changes, and understanding their patterns helps optimize view performance. Here’s a comprehensive monitoring query that tracks refresh operations:<\/p>\n\n\n\n <\/p>\n\n\n\n This query helps you understand:<\/p>\n\n\n When monitoring materialized views, focus on these essential metrics:<\/p>\n\n\n\n\n
\n
<\/a>Understanding Real-World Query Patterns<\/h3>\n\n\n\n
\n
WITH sales_summary AS (\n SELECT \n DATE_TRUNC('month', sale_date) as sale_month,\n product_category,\n region,\n SUM(amount) as total_sales,\n COUNT(DISTINCT customer_id) as unique_customers,\n AVG(amount) as avg_sale_amount\n FROM sales s\n JOIN products p ON s.product_id = p.product_id\n JOIN customers c ON s.customer_id = c.customer_id\n WHERE sale_date >= DATEADD(year, -1, CURRENT_DATE())\n GROUP BY 1, 2, 3\n)\nSELECT * \nFROM sales_summary;<\/pre>\n\n\n\n\n
SUM<\/code>, COUNT<\/code> DISTINCT<\/code>, AVG<\/code>)<\/li>\n<\/ul>\n<\/div>\n\n\n<\/a>Comparative Performance Analysis: Views vs. Materialized Views<\/h3>\n\n\n\n
<\/a>Performance profile<\/h2>\n\n\n\n
<\/a>Real-World Performance Impact<\/h2>\n\n\n\n
\n
\n
\n
<\/a>Optimal Implementation Scenarios<\/h2>\n\n\n\n
<\/a>Data-Driven Decision Making: Let Usage Guide You<\/h2>\n\n\n\n
SNOWHOUSE.ACCOUNT_USAGE.ACCESS_HISTORY<\/code> view. However, it’s important to understand how Snowflake tracks and matches queries before diving into the analysis. Snowflake’s ACCESS_HISTORY<\/code> view tracks queries based on their exact text representation. Here are key points to understand:<\/p>\n\n\n\n
\n
WHERE<\/code> clauses<\/li>\n<\/ul>\n<\/div><\/li>\n\n\n\n\n
WHERE<\/code> clause parameters<\/li>\n\n\n\nLIMIT<\/code> values<\/li>\n\n\n\nORDER BY<\/code> clauses<\/li>\n<\/ul>\n<\/div><\/li>\n<\/ul>\n<\/div>\n\n\nSELECT * FROM sales WHERE date = '2024-01-01';\n\nSELECT * FROM sales WHERE date = '2024-01-02';<\/pre>\n\n\n\n
\n
<\/a>Analyzing Query Patterns<\/h3>\n\n\n\n
-- Set session variables for configurable parameters\nSET cost_per_credit = 4;\nSET analysis_period_months = 1;\nSET min_execution_count = 100;\nWITH query_metrics AS (\n SELECT \n QUERY_TEXT,\n COUNT(*) as execution_count,\n AVG(TOTAL_ELAPSED_TIME)\/1000 \n as avg_execution_time_seconds,\n SUM(CREDITS_USED) as total_credits_used,\n SUM(BYTES_SCANNED)\/POW(1024, 3) as gb_scanned\n FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY\n WHERE START_TIME >= DATEADD(month, -$analysis_period_months, CURRENT_TIMESTAMP())\n GROUP BY QUERY_TEXT\n HAVING COUNT(*) > $min_execution_count\n)\nSELECT \n *,\n total_credits_used * $cost_per_credit as estimated_monthly_credit_cost\nFROM query_metrics\nORDER BY execution_count DESC;<\/pre>\n\n\n\n
<\/a>Interpreting Usage Metrics<\/h3>\n\n\n\n
\n
\n
\n
<\/a>The Sweet Spot: When to Pull the Trigger<\/h2>\n\n\n\n
<\/a>Complex Aggregations with Moderate Updates<\/h3>\n\n\n\n
\n
CREATE MATERIALIZED VIEW daily_sales_summary AS\nSELECT \n DATE_TRUNC('day', sale_timestamp) as sale_date,\n product_category,\n customer_region,\n customer_segment,\n SUM(sale_amount) as total_sales,\n COUNT(DISTINCT customer_id) as unique_customers,\n SUM(quantity) as units_sold,\n SUM(sale_amount)\/NULLIF(SUM(quantity), 0) as avg_unit_price\nFROM sales s\n JOIN customers c ON s.customer_id = c.customer_id\n JOIN products p ON s.product_id = p.product_id\nGROUP BY 1, 2, 3, 4;<\/pre>\n\n\n\n\n
\n
<\/a>Join-Heavy Queries<\/h3>\n\n\n\n
CREATE MATERIALIZED VIEW customer_360 AS\nSELECT \n c.customer_id,\n c.region,\n c.segment,\n COUNT(DISTINCT o.order_id) as total_orders,\n SUM(o.order_amount) as lifetime_value,\n AVG(o.order_amount) as avg_order_value,\n MAX(o.order_date) as last_order_date,\n COUNT(DISTINCT p.product_category) as categories_purchased,\n SUM(CASE WHEN r.return_id IS NOT NULL THEN 1 ELSE 0 END) as returns_count\nFROM customers c\n LEFT JOIN orders o ON c.customer_id = o.customer_id\n LEFT JOIN order_items oi ON o.order_id = oi.order_id\n LEFT JOIN products p ON oi.product_id = p.product_id\n LEFT JOIN returns r ON o.order_id = r.order_id\nGROUP BY 1, 2, 3;<\/pre>\n\n\n\n
\n
\n
\n
<\/a>When to Think Twice<\/h2>\n\n\n\n
<\/a>Highly Volatile Data<\/h3>\n\n\n\n
--Highly Volatile Data:\nSELECT\n materialized_view_name,\n COUNT(*) as refresh_count,\n AVG(TIMESTAMPDIFF('milliseconds', \n refresh_start_time, refresh_end_time)) \n as avg_refresh_ms,\n MAX(rows_updated) as max_rows_updated,\n SUM(rows_updated) as total_rows_updated,\n SUM(rows_updated)\/NULLIF(COUNT(*), 0) \n as avg_rows_per_refresh\nFROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY())\nWHERE refresh_end_time >= \n DATEADD(hour, -24, CURRENT_TIMESTAMP())\nGROUP BY materialized_view_name\nORDER BY refresh_count DESC;<\/pre>\n\n\n\n\n
\n
\n
\n
<\/a>Storage Cost Analysis:<\/h2>\n\n\n\n
<\/a>Analyzing Storage Usage<\/h2>\n\n\n\n
SELECT\n table_name,\n table_type,\n row_count,\n bytes\/POW(1024,3) as size_gb,\n bytes\/POW(1024,3) * 40 as monthly_storage_cost_dollars\nFROM information_schema.tables\nWHERE table_type = 'MATERIALIZED VIEW'\nORDER BY bytes DESC;<\/pre>\n\n\n\n
| table_name | table_type | row_count | size_gb | monthly_storage_cost_dollars |\n|---------------------|-------------------|-----------|---------|------------------------------|\n| SALES_SUMMARY_MV | MATERIALIZED VIEW | 1000000 | 2.5 | 0.10 \n| CUSTOMER_360_MV | MATERIALIZED VIEW | 500000 | 5.0 | 0.20 \n| DAILY_METRICS_MV | MATERIALIZED VIEW | 2000000 | 10.0 | 0.40 <\/pre>\n\n\n\n
\n
<\/a>Understanding Storage Metrics and Cost Calculations<\/h2>\n\n\n\n
<\/a>Storage Size Calculation<\/h3>\n\n\n\n
<\/a>Cost Calculation Framework<\/h3>\n\n\n\n
<\/a>Monitoring and Maintenance<\/h2>\n\n\n\n
<\/a>Performance Monitoring<\/a>Understanding Refresh Operations<\/h3>\n\n\n\n
WITH mv_stats AS (\nSELECT\n materialized_view_name,\n COUNT(*) as refresh_count,\n AVG(TIMESTAMPDIFF('seconds', refresh_start_time, refresh_end_time)) as avg_refresh_seconds,\n SUM(rows_updated) as total_rows_updated,\n SUM(bytes_updated)\/POW(1024,3) as gb_updated\nFROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY())\nWHERE refresh_end_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())\nGROUP BY 1\n)\nSELECT \n m.*,\n t.bytes\/POW(1024,3) as current_size_gb,\n t.row_count as total_rows,\n mv_stats.refresh_count as weekly_refreshes,\n mv_stats.avg_refresh_seconds,\n mv_stats.total_rows_updated,\n mv_stats.gb_updated\nFROM information_schema.materialized_views m\nJOIN information_schema.tables t \n ON m.table_name = t.table_name\nLEFT JOIN mv_stats \n ON m.table_name = mv_stats.materialized_view_name;<\/pre>\n\n\n\n\n
<\/a>Key Monitoring Metrics<\/h2>\n\n\n\n
<\/a>Refresh Operations<\/h3>\n\n\n
\n
<\/a>Performance Impact<\/h3>\n\n\n
\n
<\/a>Storage Utilization<\/h3>\n\n\n