The short answer
A standard database view is simply a saved SQL query. Every time a user selects from a standard view, Redshift re-executes the underlying join and aggregation logic from scratch.
A Materialized View executes the query once and stores the resulting dataset physically on disk as a table. Subsequent queries read the precomputed result directly in milliseconds without touching raw base tables.
CREATE MATERIALIZED VIEW mv_daily_category_revenue AS
SELECT
order_date,
category_id,
SUM(amount) AS total_revenue,
COUNT(order_id) AS total_orders
FROM sales
GROUP BY order_date, category_id;Incremental refresh mechanics
When new rows are appended to the base tables, you do not want to recalculate 5 years of history. Redshift supports incremental refresh:
REFRESH MATERIALIZED VIEW mv_daily_category_revenue;If the underlying base table changes consist only of INSERT statements (such as daily batch ingestion), Redshift reads only the newly added rows and updates the materialized view incrementally.
However, if base tables undergo UPDATE or DELETE operations, or if the view definition contains non-deterministic functions (like GETDATE()) or unsupported , Redshift cannot perform an incremental update and must fall back to a full, expensive recalculation.
Automatic query rewrite
Redshift features automatic query rewrite. If an analyst submits a query against the raw sales table matching the aggregation in mv_daily_category_revenue, Redshift automatically routes the query to read the materialized view instead, speeding up legacy dashboard queries without requiring analysts to edit their SQL code.