Materialized views for repeated queries in Snowflake - Commands & Configuration

Introduction

When you run the same query many times, it can slow down your work. Materialized views save the results of these queries so you can get answers faster without running the full query each time.

When you have reports that run the same complex query every day.

When you want to speed up dashboards that show the same data repeatedly.

When you want to reduce the load on your database by avoiding repeated heavy queries.

When you want to keep a snapshot of data that updates automatically as the source data changes.

Commands

This command creates a materialized view named mv_sales_summary that stores the total sales per region. It saves the query result so future queries run faster.

Terminal

CREATE MATERIALIZED VIEW my_schema.mv_sales_summary AS SELECT region, SUM(amount) AS total_sales FROM my_schema.sales GROUP BY region;

Expected Output

Materialized view MY_SCHEMA.MV_SALES_SUMMARY successfully created.

This command queries the materialized view to get the precomputed total sales by region quickly.

Terminal

SELECT * FROM my_schema.mv_sales_summary;

Expected Output

REGION   | TOTAL_SALES

---------|------------

East     | 15000

West     | 12000

North    | 18000

South    | 13000

This command manually refreshes the materialized view to update its data with the latest changes from the sales table.

Terminal

ALTER MATERIALIZED VIEW my_schema.mv_sales_summary REFRESH;

Expected Output

Materialized view MY_SCHEMA.MV_SALES_SUMMARY refresh started.

This command deletes the materialized view when it is no longer needed to free up space and resources.

Terminal

DROP MATERIALIZED VIEW my_schema.mv_sales_summary;

Expected Output

Materialized view MY_SCHEMA.MV_SALES_SUMMARY successfully dropped.

Key Concept

If you remember nothing else from this pattern, remember: materialized views store query results to speed up repeated data retrieval.

Common Mistakes

Creating a materialized view without grouping or aggregation on large tables.

This can cause the materialized view to be very large and slow to refresh, defeating the purpose of speeding up queries.

Use materialized views for queries that aggregate or filter data to keep the stored results manageable and efficient.

Not refreshing the materialized view after source data changes.

The materialized view will show outdated data, leading to incorrect query results.

Schedule automatic or manual refreshes to keep the materialized view data current.

Using materialized views for queries that change very frequently with small updates.

Frequent refreshes can cause overhead and reduce performance benefits.

Use materialized views for queries where data changes are less frequent or where eventual consistency is acceptable.

Summary

Create a materialized view to save the results of a repeated query.

Query the materialized view to get faster results than running the full query each time.

Refresh the materialized view to keep data up to date.

Drop the materialized view when it is no longer needed.