How to Create Materialized View in Redshift

How do I create and refresh a materialized view in Redshift?

Creates a pre-computed, queryable snapshot of data that can be refreshed on demand or automatically.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does a Redshift materialized view solve?

Materialized views store the result of a costly query, letting dashboards and APIs read pre-aggregated data instead of re-scanning large tables. This cuts latency and cluster workload.

When should I choose a materialized view over a normal view?

Use one when the underlying data changes less frequently than it’s queried, and performance matters more than always-current results.Ideal for daily sales summaries, cohort reports, and API endpoints.

How do I create a materialized view?

Issue CREATE MATERIALIZED VIEW with distribution, sort keys, and optional AUTO REFRESH.Define only the columns you actually need to keep the view small.

CREATE MATERIALIZED VIEW sales_summary_mv AUTO REFRESH YES DISTKEY(customer_id) SORTKEY(order_date) AS SELECT c.id AS customer_id, DATE_TRUNC('month', o.order_date) AS month, SUM(o.total_amount) AS monthly_spend FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, DATE_TRUNC('month', o.order_date);

How do I refresh a materialized view?

Run REFRESH MATERIALIZED VIEW schema.view_name;.Schedule it in your orchestrator or let Redshift auto-refresh if the view was created with AUTO REFRESH YES.

How do I drop or alter a materialized view?

Use DROP MATERIALIZED VIEW to remove it, or ALTER MATERIALIZED VIEW to toggle auto-refresh, change backup settings, or rename the view.

What are best practices for materialized views?

Choose DISTKEY and SORTKEY that match common filter columns, store only required columns, and refresh during low-traffic windows.Monitor STV_RECENTS for lock contention.

Can I incrementally refresh?

Redshift automatically tracks changes with MV logs. Ensure base tables have primary keys to maximize incremental refresh efficiency.

How do I check last refresh time?

Query SVV_MV_INFO to inspect last_refresh, refresh_type, and if the next automatic refresh is scheduled.

Why How to Create Materialized View in Redshift is important

How to Create Materialized View in Redshift Example Usage


-- Monthly spend per customer, refreshed nightly
CREATE MATERIALIZED VIEW reporting.monthly_customer_spend_mv
AUTO REFRESH NO
DISTSTYLE KEY
DISTKEY(customer_id)
SORTKEY(month)
AS
SELECT c.id AS customer_id,
       DATE_TRUNC('month', o.order_date) AS month,
       SUM(o.total_amount) AS total_spend
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
GROUP  BY c.id, DATE_TRUNC('month', o.order_date);

-- Later in a cron job
REFRESH MATERIALIZED VIEW reporting.monthly_customer_spend_mv;

How to Create Materialized View in Redshift Syntax


-- Create
CREATE MATERIALIZED VIEW [schema.]view_name
AUTO REFRESH {YES | NO}
BACKUP {YES | NO}
DISTSTYLE {AUTO | EVEN | KEY | ALL}
DISTKEY (column)
SORTKEY (column [, ...])
AS select_statement;

-- Refresh
REFRESH MATERIALIZED VIEW [schema.]view_name;

-- Alter
ALTER MATERIALIZED VIEW [schema.]view_name
  AUTO REFRESH {YES | NO}
  OWNER TO new_owner
  RENAME TO new_name;

-- Drop
DROP MATERIALIZED VIEW [IF EXISTS] [schema.]view_name;

Common Mistakes

Not refreshing after base tables change. This leaves dashboards showing stale data. Fix by scheduling regular REFRESH or using AUTO REFRESH YES.
Setting AUTO REFRESH YES on wide, high-churn views. This causes frequent heavy background work. Disable auto refresh and trigger it during off-peak hours instead.