Snowflake Materialized Views

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

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;