❄️ Snowflake Materialized Views: Precompute and Store Query Results for Fast Retrieval

As data grows exponentially, analytics queries can become slower and more expensive. Snowflake offers a powerful solution: Materialized Views (MVs) — precomputed and stored query results that automatically refresh as underlying data changes.

Think of a materialized view as a snapshot of a complex SQL query that is kept up-to-date. Instead of recalculating results every time a user queries the data, Snowflake reads directly from the stored results, making queries blazing fast.

🧩 What Are Snowflake Materialized Views?

A Materialized View in Snowflake is a database object that stores the physical results of a query for quick retrieval. Unlike regular views, which recompute data every time they’re queried, materialized views cache the query output in storage.

Whenever the base table changes, Snowflake automatically refreshes the view to keep it synchronized.

🔍 Regular View vs Materialized View

⚙️ How Materialized Views Work

Here’s the internal flow:

1. Create Materialized View → Define SQL logic.
2. Snowflake Executes Query Once → Stores result.
3. Automatic Refresh → Keeps view synced with base table.
4. Query Execution → Uses precomputed data for faster performance.

🧭 ** – How Snowflake Materialized Views Work**

🧠 Why Use Materialized Views?

Materialized views are used to:

• Speed up repeated or complex queries
• Reduce compute cost by avoiding reprocessing
• Simplify BI dashboards and ETL pipelines
• Optimize aggregations, joins, and filters

🧠 Example 1: Basic Materialized View

Let’s start simple — precomputing sales totals by region.

-- Step 1: Create base table
CREATE OR REPLACE TABLE sales (
    region STRING,
    amount NUMBER,
    sale_date DATE
);

-- Step 2: Create Materialized View
CREATE OR REPLACE MATERIALIZED VIEW mv_sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

-- Step 3: Query the materialized view
SELECT * FROM mv_sales_summary;

🧾 Explanation

• The first query creates a base table (sales).
• The materialized view (mv_sales_summary) stores aggregated totals.
• When queried, results are fetched instantly from the precomputed data.

💡 Optimization Tip:

Materialized views work best when base data doesn’t change frequently — e.g., hourly or daily batch updates.

🧠 Example 2: Materialized View with Filters

This example demonstrates filtering active customers.

-- Step 1: Base customer table
CREATE OR REPLACE TABLE customers (
    customer_id NUMBER,
    name STRING,
    status STRING,
    region STRING
);

-- Step 2: Create filtered materialized view
CREATE OR REPLACE MATERIALIZED VIEW mv_active_customers AS
SELECT customer_id, name, region
FROM customers
WHERE status = 'ACTIVE';

-- Step 3: Query materialized view
SELECT * FROM mv_active_customers;

🧾 Explanation

• The view precomputes and stores only active customers.
• Any update to status in the base table automatically triggers a refresh.
• When queried, it avoids full table scans.

💡 Optimization Tip:

Combine filters with clustering keys for even faster retrieval.

ALTER MATERIALIZED VIEW mv_active_customers CLUSTER BY (region);

🧠 Example 3: Materialized View with Joins

Here’s an advanced example combining sales and product details.

-- Step 1: Create base tables
CREATE OR REPLACE TABLE sales (
    sale_id NUMBER,
    product_id NUMBER,
    amount NUMBER
);

CREATE OR REPLACE TABLE products (
    product_id NUMBER,
    category STRING
);

-- Step 2: Create materialized view for pre-joined data
CREATE OR REPLACE MATERIALIZED VIEW mv_sales_by_category AS
SELECT
    p.category,
    SUM(s.amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category;

-- Step 3: Query the precomputed data
SELECT * FROM mv_sales_by_category;

🧾 Explanation

• Joins between sales and products are precomputed and stored.
• Future queries on sales by category become instant.
• Snowflake automatically refreshes when either base table changes.

📊 Benefits of Snowflake Materialized Views

🧠 How Snowflake Refreshes Materialized Views

• Automatic Refresh: Happens when underlying data in base tables changes.

• Manual Refresh: Can be triggered using:

  ALTER MATERIALIZED VIEW mv_sales_summary REFRESH;

• Deferred Refresh: You can also suspend refreshes during heavy ETL loads:

  ALTER MATERIALIZED VIEW mv_sales_summary SUSPEND REBUILD;

🧭 ** – Refresh Cycle of Materialized Views**

⚙️ Snowflake Materialized View Storage & Cost

Snowflake stores materialized views in micro-partitioned storage (same as tables).

You incur:

• Storage Cost: For persisted results.
• Compute Cost: For refresh operations.

However, overall cost is much lower than repeatedly running heavy queries.

🧠 How to Check Materialized View Usage

You can monitor materialized view performance using:

SELECT
  database_name,
  schema_name,
  name,
  bytes,
  refresh_type,
  last_refresh_time
FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEWS;

💡 Best Practices for Materialized Views

🧠 Interview Preparation Tips

Remember this mnemonic:

“P.A.R.T.” → Precompute, Automate, Refresh, Tune

🧠 Interview Questions & Answers

📊 Common Issues and Fixes

🧠 Optimization Strategies

1. Cluster Base Tables

   ALTER TABLE sales CLUSTER BY (region);

   → Improves micro-partition pruning for materialized views.

2. Add Selective Filters

   CREATE MATERIALIZED VIEW mv_recent_sales AS
   SELECT * FROM sales WHERE sale_date > DATEADD('month', -1, CURRENT_DATE());

   → Reduces refresh size.

3. Monitor Refresh Costs

   SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY;

   → Helps identify expensive refresh patterns.

🧠 Example Use Case in Real Business Scenario

Scenario:

A retail analytics team frequently runs this query:

SELECT region, category, SUM(sales_amount)
FROM transactions
GROUP BY region, category;

Problem:

Each query scans billions of rows → very expensive and slow.

Solution:

Create a materialized view:

CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT region, category, SUM(sales_amount) AS total_sales
FROM transactions
GROUP BY region, category;

✅ Result:

• Query performance improved by 90%
• Compute costs reduced by 70%
• BI dashboards refresh instantly

🧭 ** – Materialized View Query Lifecycle**

🧠 How to Remember This Concept Easily

Use the keyword “STORED VIEW” — because a Materialized View = Stored Result of a Query.

Memory Hook:

“Materialized = Made Material (Stored Physically)”

That’s the easiest way to recall what makes it different from a regular view.

🧩 Why Learning Materialized Views Is Important

🧠 Common Mistakes to Avoid

🧾 Comparison: Snowflake Materialized Views vs Cached Results

🧠 Performance Monitoring Commands

-- View all materialized views in your account
SHOW MATERIALIZED VIEWS;

-- Check refresh statistics
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY;

🧭 ** – Optimization Lifecycle**

🎯 Conclusion

Snowflake Materialized Views are a cornerstone of data performance optimization. They transform heavy analytical queries into precomputed, ready-to-serve datasets — ideal for dashboards, reports, and real-time analytics.

With automatic refresh, incremental updates, and query acceleration, materialized views ensure that data is always fresh and fast.

💡 Final Thought:

“Materialized Views don’t just store data — they store time saved.”

✅ Quick Recap