Snowflake Materialized Views

Materialized views in Snowflake store pre-computed results of query expressions, dramatically improving performance for complex, frequently-executed queries.

Architecture Diagram 2: Materialized View Refresh Cycle

Creating Materialized Views

Basic Syntax

-- Simple materialized view
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
  order_date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_sales,
  AVG(amount) AS avg_sale
FROM orders
GROUP BY order_date;

-- Materialized view with JOIN
CREATE MATERIALIZED VIEW mv_customer_orders AS
SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS order_count,
  SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Advanced Materialized Views

-- Materialized view with filtering
CREATE MATERIALIZED VIEW mv_high_value_orders AS
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE amount > 1000
GROUP BY customer_id;

-- Materialized view with window functions
CREATE MATERIALIZED VIEW mv_customer_ranking AS
SELECT
  customer_id,
  SUM(amount) AS total_spent,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS spending_rank
FROM orders
GROUP BY customer_id;

How Materialized Views Work

Incremental Refresh

-- Check refresh status
SELECT
  name,
  refresh_state,
  last_refresh_time,
  refresh_error
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE name = 'MV_DAILY_SALES';

-- Manual refresh (if needed)
ALTER MATERIALIZED VIEW mv_daily_sales REFRESH;

Automatic Query Rewrite

-- This query automatically uses the materialized view
SELECT
  order_date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_sales
FROM orders
GROUP BY order_date;

-- Snowflake rewrites this to use mv_daily_sales
EXPLAIN SELECT
  order_date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_sales
FROM orders
GROUP BY order_date;

Materialized View Limitations

-- Valid materialized view patterns
CREATE MATERIALIZED VIEW mv_valid AS
SELECT
  column1,
  AGG_FUNC(column2) AS agg_result
FROM base_table
GROUP BY column1;

-- Invalid patterns (will fail)
-- SELECT DISTINCT column1 FROM base_table;
-- SELECT * FROM base_table LIMIT 10;
-- SELECT column1, (SELECT MAX(x) FROM t2) FROM base_table;

Performance Monitoring

-- Check materialized view size and refresh stats
SELECT
  mv.name,
  mv.table_schema,
  mv.data_retention_time_in_days,
  ts.table_size_bytes,
  ts.total_rows,
  mv.last_refresh_time
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS mv
JOIN INFORMATION_SCHEMA.TABLE_STORAGE_METRICS ts
  ON mv.name = ts.table_name
WHERE mv.name = 'MV_DAILY_SALES';

-- Monitor query rewrite usage
SELECT
  query_id,
  query_text,
  credits_used
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
WHERE query_text LIKE '%MV_DAILY_SALES%';

Drop Rules

-- Check drop rules for materialized views
SELECT
  name,
  drop_rule,
  source_table
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE name = 'MV_DAILY_SALES';

-- Force refresh to check drop rules
ALTER MATERIALIZED VIEW mv_daily_sales REFRESH;

Best Practices

-- Recommended: Simple aggregation with time grouping
CREATE MATERIALIZED VIEW mv_hourly_metrics AS
SELECT
  DATE_TRUNC('hour', event_time) AS event_hour,
  event_type,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM events
GROUP BY 1, 2;

-- Monitor refresh performance
SELECT
  name,
  refresh_start_time,
  refresh_end_time,
  TIMESTAMPDIFF('second', refresh_start_time, refresh_end_time) AS duration_seconds
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE name = 'MV_HOURLY_METRICS'
ORDER BY refresh_start_time DESC
LIMIT 5;

Summary

Key Takeaways

Materialized views store pre-computed query results for instant access without re-execution.

Incremental refresh maintains data freshness automatically as base tables change.

Query optimizer transparently rewrites queries to use materialized views — no code changes needed.

When to Use Materialized Views

Limitations to Remember

1. No DISTINCT operations allowed
2. No LIMIT clause permitted
3. No subqueries in SELECT list
4. GROUP BY clause is required
5. Limited JOIN support (not all patterns supported)