Materialized Views in Amazon Redshift: Auto-Refresh, Incremental Refresh and Where They Break

Materialized views are the cheapest performance win in Amazon Redshift that most teams under-use. A materialized view (MV) stores the result of a query as physical data, then refreshes only the rows affected by changes in the base tables. Dashboards that re-aggregate the same fact table forty times an hour stop paying for that work, and queries that never mention the MV can still be rewritten to use it automatically.

They are also the feature that most often surprises people in production: an MV that silently drops to full refresh, an auto-refresh that never seems to run, or an MV that quietly serves data that is four hours old. This tutorial covers the mechanics and the failure modes.

Creating a materialized view

CREATE MATERIALIZED VIEW mv_daily_orders
AUTO REFRESH YES
AS
SELECT
    o.order_date::date        AS order_day,
    o.channel_id,
    COUNT(*)                  AS order_count,
    SUM(o.net_amount)         AS net_revenue,
    SUM(o.discount_amount)    AS discount_total
FROM sales.orders o
WHERE o.order_status <> 'cancelled'
GROUP BY 1, 2;

The MV is populated at creation time. You can set sort and distribution keys on it exactly as you would on a table, and you should — an MV backing a BI tool usually wants the filter column as the sort key:

CREATE MATERIALIZED VIEW mv_daily_orders
DISTKEY (channel_id)
SORTKEY (order_day)
AUTO REFRESH YES
AS SELECT ...;

Refresh it manually at any time:

REFRESH MATERIALIZED VIEW mv_daily_orders;

Incremental vs full refresh: the rule that actually matters

Redshift decides per MV, at creation time whether it can be refreshed incrementally. Incremental refresh reads only the base-table changes recorded since the last refresh and applies them. Full refresh re-runs the entire query. On a two-billion-row fact table that difference is seconds versus tens of minutes, and it is the single most important thing to verify.

As a working rule, aggregates with SUM, COUNT, MIN, MAX, inner joins, WHERE filters and GROUP BY are incrementally refreshable. Things that commonly force full refresh include:

• COUNT(DISTINCT ...), MEDIAN, percentile and other holistic aggregates
• Window functions (ROW_NUMBER, LAG, ranking of any kind)
• Outer joins, and joins to external or federated tables
• DISTINCT, UNION / INTERSECT / EXCEPT, LIMIT, ORDER BY
• Subqueries in the select list, and most correlated subqueries
• Mutable functions such as CURRENT_DATE, GETDATE(), RANDOM()

That last one bites often. A view defined as WHERE order_date >= CURRENT_DATE - 90 is not incrementally refreshable, and it also means the MV's contents depend on when it was refreshed. Push the rolling window into the querying view instead of the MV:

-- MV keeps all history and refreshes incrementally
CREATE MATERIALIZED VIEW mv_daily_orders AUTO REFRESH YES AS
SELECT order_date::date AS order_day, channel_id,
       COUNT(*) AS order_count, SUM(net_amount) AS net_revenue
FROM sales.orders
WHERE order_status <> 'cancelled'
GROUP BY 1, 2;

-- Regular view applies the rolling window at query time
CREATE OR REPLACE VIEW rpt.orders_last_90d AS
SELECT * FROM mv_daily_orders
WHERE order_day >= CURRENT_DATE - 90;

Check what you actually got:

SELECT name, schema, is_stale, state, autorefresh, autorewrite
FROM SVV_MV_INFO
WHERE schema = 'analytics';

The state column tells you whether the MV is incrementally maintainable (1) or requires recompute (0). Treat a 0 on your largest MV as a design bug, not a fact of life — usually one COUNT(DISTINCT) is responsible, and it can be replaced with an APPROXIMATE COUNT(DISTINCT ...) or with a pre-aggregated HLLSKETCH column:

-- HyperLogLog sketches aggregate and stay incrementally refreshable
CREATE MATERIALIZED VIEW mv_daily_users AUTO REFRESH YES AS
SELECT event_date::date AS event_day,
       HLL_CREATE_SKETCH(user_id::varchar) AS users_sketch
FROM events.page_views
GROUP BY 1;

-- Query side: combine sketches across any date range
SELECT HLL_CARDINALITY(HLL_COMBINE(users_sketch)) AS monthly_users
FROM mv_daily_users
WHERE event_day BETWEEN '2026-01-01' AND '2026-01-31';

Auto-refresh, and why it sometimes does nothing

AUTO REFRESH YES hands scheduling to Redshift. The service watches base-table changes and refreshes when it judges the benefit worth the resources, using spare capacity. This is deliberately not a cron: under sustained heavy load, auto-refresh is deprioritised, and an MV can stay stale for longer than your SLA allows.

So decide which of the two contracts you are signing:

• Best effort freshness. AUTO REFRESH YES, monitor is_stale, accept variable lag. Right for exploratory dashboards.
• Deterministic freshness. AUTO REFRESH NO plus an explicit REFRESH MATERIALIZED VIEW at the end of your ELT DAG, in dependency order. Right for anything with a stated freshness SLA or a downstream reconciliation.

Mixing the two is where teams get hurt: a nightly pipeline that loads facts at 02:00 and an auto-refresh MV that happens to fire at 01:50 produces a dashboard that is a day behind, once a month, unreproducibly.

When you refresh explicitly, remember that MVs stacked on MVs are not refreshed transitively. Refresh from the bottom up:

REFRESH MATERIALIZED VIEW mv_orders_enriched;   -- base layer
REFRESH MATERIALIZED VIEW mv_daily_orders;      -- built on the above

Automatic query rewrite

Redshift can rewrite a query that hits the base tables so it reads a suitable MV instead, without the query mentioning the MV. That is what makes MVs useful under BI tools you do not control.

Rewrite only happens if the MV is fresh enough and the query is compatible, so verify rather than assume. Run EXPLAIN on the base-table query and look for a scan on the MV's backing relation:

EXPLAIN
SELECT order_date::date, SUM(net_amount)
FROM sales.orders
WHERE order_status <> 'cancelled'
GROUP BY 1;

If the plan still scans sales.orders, the rewrite did not fire — commonly because the predicate is not a superset-compatible match, or the MV is stale. Session-level control:

SET mv_enable_aqmv_for_session TO TRUE;

Redshift also creates automated materialized views on its own, based on observed workload, and drops them when they stop paying off. They cost storage and refresh cycles, so if you are chasing an unexplained line on the bill, list them:

SELECT * FROM SVV_MV_INFO WHERE mv_name LIKE '%auto_mv%';

Automated MVs are a good signal-generator: whatever Redshift chose to materialize is a query pattern worth modelling deliberately.

MVs over external and streaming data

Two special cases matter in a lakehouse setup.

Spectrum / external tables. An MV over an external table in S3 (including Iceberg via the catalog) can only be fully refreshed, since Redshift cannot see the change log of files it does not own. Materializing a slow external scan is still very often the right move — you simply have to schedule the refresh and size it accordingly.

Streaming ingestion. MVs are the landing mechanism for Kinesis and MSK streams, and this MV must be refreshed to advance the stream offset:

CREATE EXTERNAL SCHEMA kds
FROM KINESIS
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftStreamingRole';

CREATE MATERIALIZED VIEW mv_clicks_raw AUTO REFRESH YES AS
SELECT approximate_arrival_timestamp,
       refresh_time,
       JSON_PARSE(kinesis_data) AS payload
FROM kds."clickstream";

Keep the streaming MV thin — parse and land, nothing else — and do shredding in a second layer. Heavy transformation inside a streaming MV lengthens refresh, and if refresh lags past the stream retention window you lose records permanently.

Monitoring refresh cost and staleness

Two queries belong in every Redshift runbook. First, staleness:

SELECT schema, name, is_stale, autorefresh, state
FROM SVV_MV_INFO
WHERE is_stale = 't';

Second, refresh history and whether refreshes are going incremental in practice:

SELECT mv_name,
       status,
       refresh_type,
       start_time,
       DATEDIFF(second, start_time, end_time) AS refresh_seconds
FROM SYS_MV_REFRESH_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
ORDER BY refresh_seconds DESC
LIMIT 50;

A refresh_type that flips from incremental to recompute usually means the MV was invalidated — a base table was truncated, altered, or reloaded wholesale. Full-reload ELT patterns (TRUNCATE then COPY) defeat incremental refresh entirely; that is one more argument for MERGE-based upserts on the base tables.

MV or dbt table? A short decision rule

Materialized views are not a replacement for a modelling layer, and dbt models are not a replacement for MVs. Use an MV when:

• the logic is a single query, mostly aggregation or a narrow join
• freshness needs to be minutes, not hours
• you want automatic query rewrite for BI tools you cannot modify
• the refresh genuinely qualifies as incremental

Use a dbt (or equivalent) table/incremental model when:

• the transformation is multi-step, tested, and needs version control and lineage
• you need window functions, SCD Type 2 logic, or deduplication
• the refresh would be full anyway, in which case an incremental dbt model with a MERGE strategy is usually cheaper and far more debuggable

In most warehouses we review, the answer is both: dbt owns the conformed model layer, and a thin set of MVs sits on top of the largest facts to absorb dashboard concurrency.

A five-step rollout

1. Find the repeat offenders — the aggregate queries with the highest cumulative execution time in SYS_QUERY_HISTORY over the last week.
2. Write one MV per pattern, no COUNT(DISTINCT), no window functions, no CURRENT_DATE.
3. Confirm state = 1 in SVV_MV_INFO before you go further.
4. Choose auto-refresh or pipeline-driven refresh explicitly, and write the choice down next to the MV definition.
5. Add staleness and refresh-duration checks to monitoring, and review SYS_MV_REFRESH_HISTORY monthly for MVs that have degraded to recompute.

Done this way, MVs typically remove a double-digit percentage of dashboard workload from the cluster without touching a single BI query.

---

Need help? RougeWarehouse designs and tunes MPP data warehouses on Amazon Redshift — modelling, refresh strategy, performance optimization and administration, for direct engagements and as a subcontractor to agencies and consultancies. Get in touch to talk through your workload.