Materialized Views & Clustering: Performance Guide

In Snowflake, as your data grows, queries that scan entire tables can become painfully slow. Two powerful features—materialized views and automatic clustering—help you keep performance high without manual tuning. Materialized views store pre-computed results of expensive aggregations, automatically refreshing as base data changes. Automatic clustering reorganizes table data on disk based on one or more clustering keys, reducing the amount of data scanned for range-filtered queries. Together, they form a robust foundation for self-optimizing data warehouses. This tutorial will guide you through creating, managing, and debugging these features with production-ready examples. You'll learn when to use each, how to monitor their effectiveness, and how to avoid common pitfalls that can degrade performance or increase costs.

What Are Materialized Views in Snowflake?

Materialized views in Snowflake are pre-computed result sets that are automatically refreshed when the underlying base table changes. Unlike standard views, which execute the query each time they are accessed, materialized views store the actual data, dramatically reducing query latency for complex aggregations. They are ideal for dashboards, reporting, and any scenario where the same expensive query runs repeatedly. Snowflake handles the refresh automatically, but you can control the refresh interval or trigger it manually. Materialized views incur storage costs and compute costs for refresh, so they are best used for queries that run frequently and are expensive to compute from scratch.

-- Create a materialized view for daily sales by product
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT 
    sale_date,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount
FROM sales
GROUP BY sale_date, product_id;

-- Query the materialized view
SELECT * FROM daily_sales_mv
WHERE sale_date = '2024-01-15'
ORDER BY total_amount DESC;

Creating and Managing Materialized Views

To create a materialized view, use the CREATE MATERIALIZED VIEW statement followed by a SELECT query. The query must be deterministic and cannot include non-deterministic functions like CURRENT_TIMESTAMP. You can also specify a clustering key on the materialized view itself to further optimize queries against it. Managing materialized views includes checking their refresh status, altering refresh intervals, and dropping them when no longer needed. Use SHOW MATERIALIZED VIEWS to list all views and their properties. The refresh is automatic but you can force a refresh with ALTER MATERIALIZED VIEW ... REFRESH.

-- List all materialized views
SHOW MATERIALIZED VIEWS IN DATABASE mydb;

-- Check refresh history
SELECT * FROM INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE VIEW_NAME = 'DAILY_SALES_MV'
ORDER BY REFRESH_START_TIME DESC;

-- Force a refresh
ALTER MATERIALIZED VIEW daily_sales_mv REFRESH;

-- Drop a materialized view
DROP MATERIALIZED VIEW daily_sales_mv;

Automatic Clustering in Snowflake

Automatic clustering reorganizes the micro-partitions of a table based on one or more clustering keys. This reduces the amount of data scanned for queries that filter on those keys. Snowflake automatically reclusters data as new data is inserted, updated, or deleted. You define clustering keys using the CLUSTER BY clause when creating or altering a table. The system then maintains the clustering order over time. Clustering is especially beneficial for large tables (hundreds of gigabytes or more) and for queries with range predicates (e.g., WHERE date BETWEEN ...).

-- Create a table with a clustering key on sale_date
CREATE TABLE sales (
    sale_id INTEGER,
    sale_date DATE,
    product_id INTEGER,
    quantity INTEGER,
    amount DECIMAL(10,2)
) CLUSTER BY (sale_date);

-- Alternatively, add clustering key to existing table
ALTER TABLE sales CLUSTER BY (sale_date, product_id);

-- Check clustering depth
SELECT SYSTEM$CLUSTERING_DEPTH('sales');

-- View clustering information
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SALES';

Combining Materialized Views and Clustering

For maximum performance, combine materialized views with clustering. Cluster the base table on the columns used in the materialized view's GROUP BY or WHERE clauses. This speeds up both the materialized view refresh and any queries that scan the base table. Additionally, you can cluster the materialized view itself if you frequently query it with filters. However, note that materialized views inherit clustering from the base table by default, but you can override it.

-- Base table with clustering
CREATE TABLE sales (
    sale_id INTEGER,
    sale_date DATE,
    product_id INTEGER,
    quantity INTEGER,
    amount DECIMAL(10,2)
) CLUSTER BY (sale_date);

-- Materialized view with its own clustering
CREATE MATERIALIZED VIEW daily_sales_mv
CLUSTER BY (sale_date)
AS
SELECT sale_date, product_id, SUM(quantity) AS total_qty, SUM(amount) AS total_amt
FROM sales
GROUP BY sale_date, product_id;

-- Query that benefits from both
SELECT * FROM daily_sales_mv
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY total_amt DESC;

Monitoring and Maintaining Performance

Snowflake provides several tools to monitor the performance of materialized views and clustering. Use the ACCOUNT_USAGE schema and INFORMATION_SCHEMA to track refresh history, clustering depth, and credit consumption. The QUERY_HISTORY view can show whether queries are using materialized views. For clustering, you can manually recluster a table using ALTER TABLE ... RECLUSTER if automatic clustering is not keeping up. Regular monitoring helps you identify when to adjust clustering keys or drop unused materialized views.

-- Check if a query used a materialized view
SELECT query_id, query_text, materialized_view_name
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    DATEADD('day', -1, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP))
WHERE query_text LIKE '%daily_sales_mv%';

-- View clustering history
SELECT * FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY(
    DATEADD('day', -7, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP))
WHERE TABLE_NAME = 'SALES';

-- Manual recluster if needed
ALTER TABLE sales RECLUSTER;

Best Practices and Common Pitfalls

When using materialized views and clustering, follow these best practices: 1) Match materialized view definitions exactly to your most common query patterns. 2) Use clustering keys on columns that appear in WHERE clauses with range predicates. 3) Avoid clustering on high-cardinality columns like primary keys. 4) Monitor clustering depth and refresh history regularly. 5) Test with EXPLAIN to ensure materialized views are being used. Common pitfalls include creating materialized views with complex joins (not allowed), using too many clustering keys, and neglecting to drop unused materialized views, which incur storage costs.

-- Good: clustering on date column used in range queries
CREATE TABLE orders CLUSTER BY (order_date) AS SELECT * FROM raw_orders;

-- Bad: clustering on high-cardinality column like order_id
-- (won't group data well)

-- Good: materialized view matching common query
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
FROM sales GROUP BY month;

-- Check if view is used
EXPLAIN SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
FROM sales WHERE sale_date >= '2024-01-01' GROUP BY month;