Skip to main content

Materialized Views in Snowflake


What is a Materialized View?​

A Materialized View (MV) in Snowflake is a database object that physically stores the results of a query.
Unlike regular views—which recompute the query every time—MVs persist the data on disk.
Snowflake automatically refreshes MVs in the background to stay current with changes in underlying tables.


Why Use Materialized Views?​

  • ✅ Performance Boost:

    • MVs store precomputed data, so repetitive, expensive joins or aggregations don’t need to be recalculated each time.
    • This ensures queries on MVs return faster, reducing wait times for end-users.
  • ✅ Compute Efficiency:

    • Running heavy queries directly on big tables can consume lots of compute credits.
    • By using an MV, Snowflake serves results from stored data, saving warehouse resources.
  • ✅ Great for Dashboards:

    • Dashboards and BI tools often run the same queries repeatedly.
    • MVs eliminate redundant work, speeding up report loads and improving the user experience.
  • ✅ Automatic Maintenance:

    • No need to manage refresh schedules manually.
    • Snowflake automatically detects data changes in base tables and updates MVs incrementally.

Example SQL: Creating and Using MVs​

Create a Base Table​

CREATE TABLE sales (
sale_id BIGINT,
customer_id BIGINT,
sale_date DATE,
amount FLOAT
);

Create a Materialized View​

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total_sales
FROM sales
GROUP BY 1;

Query the Materialized View​

SELECT *
FROM mv_monthly_sales
WHERE month = '2025-05-01';

Why? Instead of recalculating monthly sales from millions of records each time, this approach fetches the results instantly from the MV, dramatically reducing query latency.


Use Case​

A retail company’s sales table has billions of rows from daily transactions. Management often needs monthly sales summaries for reporting dashboards. Without MVs, calculating these totals on-the-fly would be resource-intensive and slow. With an MV, these summaries are precomputed and instantly available, ensuring smooth dashboard refreshes and cost savings on warehouse compute.


Automatic Maintenance & Refreshing​

  • ✅ Incremental Refresh:

    • Snowflake automatically updates the MV by reprocessing only the changed data.
    • This makes refreshes faster and more efficient than fully recomputing the view.
  • ✅ Asynchronous Refresh:

    • Refreshes happen in the background—queries see recent, but not necessarily real-time data.
    • This strikes a balance between performance and data freshness.

Why? This hands-off approach means MVs stay current without manual effort, allowing your team to focus on other performance-tuning tasks.


Best Practices​

  • ✅ Use MVs for read-heavy workloads with repetitive queries—like dashboards, KPIs, and periodic reporting.

  • ✅ Avoid MVs on tables with frequent updates (e.g., real-time ETL loads) to limit refresh overhead.

  • ✅ Continuously monitor credit usage—refreshes can impact warehouse costs if underlying tables change a lot.

  • ✅ Build MVs on columns that are heavily queried to maximize performance gains.

  • ✅ Periodically review MVs—recreate them if your reporting logic changes significantly.


Limitations​

❌ Refresh Overhead:

  • Refreshing MVs consumes warehouse compute—especially for big tables or frequent data changes.
  • This can increase costs if not carefully monitored.

❌ DML Impact:

  • If you frequently insert, update, or delete data in the base table, MVs may slow down those DML operations because of the extra work to maintain consistency.
  • This can become a bottleneck for tables with high transactional activity.

❌ No Non-Deterministic Functions:

  • MVs can’t include functions like RANDOM(), CURRENT_TIMESTAMP(), or any logic that changes every run.
  • This ensures the MV’s data stays consistent and doesn’t change unpredictably.

❌ Limited SQL Syntax:

  • MVs can’t contain window functions, UNIONs, or some subqueries.
  • This limits how complex your MV logic can be.

❌ No Cross-Database References:

  • MVs can only use tables/views within the same database—no cross-database joins or aggregations.

Why? These limitations help ensure MVs remain predictable and performant by focusing only on static, deterministic logic.


Roles & Privileges Required​

🔑 Creating an MV:

  • You need the OWNERSHIP privilege on the base table.
  • Roles like SYSADMIN typically have these privileges.

🔑 Querying an MV:

  • Any user/role with SELECT privilege on the MV can query it.

Why? This ensures that only authorized users can create, refresh, or query MVs, maintaining data security and compliance.


Impact on Performance Tuning​

  • ✅ Query Acceleration:

    • Since MVs store precomputed results, queries that hit the MV skip the expensive calculations.
    • This reduces latency and improves the experience for dashboards and reports.
  • ✅ Reduced Load on Base Tables:

    • By offloading repetitive queries to MVs, the underlying tables remain less burdened, improving overall database health.
  • ✅ Warehouse Efficiency:

    • Queries on MVs require less compute power—leading to credit savings for repeated analytics tasks.
    • Faster queries mean your warehouses can auto-suspend sooner, controlling costs even further.
  • ✅ Shift Heavy Compute to Background:

    • Snowflake handles the MV refreshes in the background, so your active workloads are uninterrupted.
    • This also ensures that prime-time user queries aren’t slowed down by data transformation tasks.
  • ✅ Ideal for BI Dashboards & Reports:

    • MVs make dashboards load faster—key for interactive analytics and satisfying end users.
    • The reduced lag also frees up compute for other high-priority workloads.

Advantages & Disadvantages of Materialized Views in Snowflake​

✅ Advantages​

  • Performance Boost: MVs store precomputed results, so queries are much faster than querying large base tables directly.
  • Compute Efficiency: They reduce compute load on frequently queried data, saving Snowflake credits and improving concurrency.
  • Ease of Use: MVs are auto-refreshed by Snowflake, eliminating manual refresh jobs or complex ETL logic for most reporting scenarios.
  • Improved User Experience: Dashboards and reports that rely on MVs load significantly faster, enhancing end-user satisfaction.

❌ Disadvantages​

  • Refresh Overhead: Even though refreshes happen in the background, they still consume warehouse credits, especially with large or frequently changing tables.
  • DML Performance Impact: Insert, update, and delete operations on base tables can slow down because of the overhead of updating dependent MVs.
  • Limited Flexibility: MVs don’t support non-deterministic functions or complex queries (e.g., window functions, unions, or cross-database references).
  • Not Real-Time: MV data is eventually consistent, meaning it may not reflect the absolute latest updates until the next refresh finishes.

⚠️ Caveat: Refresh Overhead​

Although MVs automatically refresh to stay up-to-date, this refresh process still consumes compute resources. For tables with frequent data changes (like high-velocity transactional systems), the constant refreshes can significantly increase warehouse usage and impact costs.

Why? Because Snowflake’s MV refresh process analyzes data changes and updates the MV’s stored results, it requires extra CPU cycles—potentially causing performance and cost concerns if not properly monitored.


Final: Why Materialized Views are Considered for Performance Tuning in Snowflake​

Materialized Views in Snowflake are considered a key performance-tuning tool because they:

✔️ Store precomputed results—eliminating real-time recalculations for repeated, heavy queries. ✔️ Accelerate dashboards and reports—speeding up access to critical KPIs and analytics. ✔️ Reduce load on core tables and compute warehouses, improving concurrency and freeing up resources for other tasks. ✔️ Shift compute-intensive work to background refresh jobs, keeping interactive workloads fast and responsive. ✔️ Help control costs by reducing credits burned for frequent queries, leading to predictable performance and budget management.

In short:

  • ✅ Materialized Views help deliver consistently fast query performance while optimizing compute usage—a win-win for performance and cost-efficiency in Snowflake!

🚀 Summary​

✔️ Materialized Views = Precomputed data → Fast queries ✔️ Lower compute needs for repeated queries → Credit savings ✔️ Shift heavy compute to background refresh jobs ❌ Monitor refresh costs → Don’t let them balloon for dynamic tables

Materialized Views are one of the key performance-tuning tools in Snowflake. They let you speed up repetitive, heavy queries and reduce load on your main tables and warehouses—while Snowflake manages the refreshes behind the scenes!