Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Amazon Redshift will no longer support the use of Python UDFs after June 30, 2026. We will start enforcing it in phases. For more information on the details of Python end of life and migration options, see the blog post that was published on June 30, 2025.

CREATE MATERIALIZED VIEW

Creates a materialized view based on one or more Amazon Redshift tables. You can also base materialized views on external tables created using Spectrum or federated query. For information about Spectrum, see Amazon Redshift Spectrum. For information about federated query, see Querying data with federated queries in Amazon Redshift.

Syntax

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]
[ AUTO REFRESH { YES | NO } ]
AS query 

The following syntax creates a materialized view that stores its data as an Apache Iceberg table. For more information, see Materialized views stored as Apache Iceberg tables.

CREATE MATERIALIZED VIEW mv_name
USING ICEBERG
[LOCATION 's3://bucket/path/']
[PARTITIONED BY (partition_transform [, ...])]
[TABLE PROPERTIES ('property_name' = 'property_value' [, ...])]
AS query

where partition_transform is:

    column_name |
    YEAR( column_name ) |
    MONTH( column_name ) |
    DAY( column_name ) |
    HOUR( column_name ) |
    BUCKET( integer, column_name ) |
    TRUNCATE( integer, column_name )

Parameters

Usage notes

To create a materialized view, you must have the following privileges:

You can create a materialized view from a remote datasharing cluster by providing the external database name at the mv_name.

Iceberg materialized views

To create an Iceberg materialized view, you must have CREATE TABLE permission in the target Amazon Glue Data Catalog database. The IAM role associated with the external schema (the MV definer role) must have SELECT permission via Amazon Lake Formation on all source tables referenced in the query.

All identifiers in the materialized view definition (table names, column names, aliases) must be lowercase. The Amazon Glue Data Catalog stores identifiers in lowercase for Hive compatibility. Amazon Redshift rejects CREATE MATERIALIZED VIEW statements with USING ICEBERG that contain uppercase identifiers.

Creating or refreshing Iceberg materialized views is not supported when enable_case_sensitive_identifier is set to true. This restriction exists because the Amazon Glue Data Catalog does not support case-sensitive identifiers. If your cluster or workgroup has enable_case_sensitive_identifier enabled, set it to false for the session before creating or refreshing Iceberg materialized views:

SET enable_case_sensitive_identifier TO false;

Source tables must be in Apache Iceberg format version 2 or lower. Non-Iceberg tables cannot be used as source tables for Iceberg materialized views.

You can't use the following with Iceberg materialized views:

For more information about Iceberg materialized view capabilities and limitations, see Materialized views stored as Apache Iceberg tables.

Incremental refresh for materialized views in a datashare

Amazon Redshift supports automatic and incremental refresh for materialized views in a consumer datashare when the base tables are shared. Incremental refresh is an operation where Amazon Redshift identifies changes in the base table or tables that happened after the previous refresh and updates only the corresponding records in the materialized view. This runs more quickly than a full refresh and improves workload performance. You don't have to change your materialized-view definition to take advantage of incremental refresh.

There are a couple limitations to note for taking advantage of incremental refresh with a materialized view:

For more information about creating materialized views in a datashare, see Working with views in Amazon Redshift data sharing, which contains several query examples.

DDL updates to materialized views or base tables

When using materialized views in Amazon Redshift, follow these usage notes for data definition language (DDL) updates to materialized views or base tables.

Limitations

You can't define a materialized view that references or includes any of the following:

For Iceberg materialized views created with USING ICEBERG, the following additional limitations apply:

Examples

The following example creates a materialized view from three base tables that are joined and aggregated. Each row represents a category with the number of tickets sold. When you query the tickets_mv materialized view, you directly access the precomputed data in the tickets_mv materialized view.

CREATE MATERIALIZED VIEW tickets_mv AS
    select   catgroup,
    sum(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

The following example creates a materialized view similar to the previous example and uses the aggregate function MAX().

CREATE MATERIALIZED VIEW tickets_mv_max AS
    select   catgroup,
    max(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

SELECT name, state FROM STV_MV_INFO;

The following example uses a UNION ALL clause to join the Amazon Redshift public_sales table and the Redshift Spectrum spectrum.sales table to create a material view mv_sales_vw. For information about the CREATE EXTERNAL TABLE command for Amazon Redshift Spectrum, see CREATE EXTERNAL TABLE. The Redshift Spectrum external table references the data on Amazon S3.

CREATE MATERIALIZED VIEW mv_sales_vw as
select salesid, qtysold, pricepaid, commission, saletime from public.sales
union all
select salesid, qtysold, pricepaid, commission, saletime from spectrum.sales

The following example creates a materialized view mv_fq based on a federated query external table. For information about federated query, see CREATE EXTERNAL SCHEMA.

CREATE MATERIALIZED VIEW mv_fq as select firstname, lastname from apg.mv_fq_example;

select firstname, lastname from mv_fq;
 firstname | lastname
-----------+----------
 John      | Day
 Jane      | Doe
(2 rows)

The following example shows the definition of a materialized view.

SELECT pg_catalog.pg_get_viewdef('mv_sales_vw'::regclass::oid, true);

pg_get_viewdef
---------------------------------------------------
create materialized view mv_sales_vw as select a from t;

The following sample shows how to set AUTO REFRESH in the materialized view definition and also specifies a DISTSTYLE. First, create a simple base table.

CREATE TABLE baseball_table (ball int, bat int);

Then, create a materialized view.

CREATE MATERIALIZED VIEW mv_baseball DISTSTYLE ALL AUTO REFRESH YES AS SELECT ball AS baseball FROM baseball_table;

Now you can query the mv_baseball materialized view. To check if AUTO REFRESH is turned on for a materialized view, see STV_MV_INFO.

The following sample creates a materialized view that references a source table in another database. It assumes that the database containing the source table, database_A, is in the same cluster or workgroup as your materialized view, which you create in database_B. (You can substitute your own databases for the sample.) First, create a table in database_A called cities, with a cityname column. Make the column's data type a VARCHAR. After you create the source table, run the following command in database_B to create a materialized view whose source is your cities table. Make sure to specify the source table's database and schema in the FROM clause:

CREATE MATERIALIZED VIEW cities_mv AS
SELECT  cityname
FROM    database_A.public.cities;

Query the materialized view you created. The query retrieves records whose original source is the cities table in database_A:

select * from cities_mv;

When you run the SELECT statement, cities_mv returns the records. Records are refreshed from the source table only when a REFRESH statement is run. Also, note that you can't update records directly in the materialized view. For information about refreshing the data in a materialized view, see REFRESH MATERIALIZED VIEW.

For details about materialized view overview and SQL commands used to refresh and drop materialized views, see the following topics:

The following example creates an Iceberg materialized view in an Amazon S3 Table Bucket with partitioning.

CREATE MATERIALIZED VIEW "my-bucket@s3tablescatalog".analytics.sales_by_region
USING ICEBERG
PARTITIONED BY (region)
AS SELECT region, count(*) as order_count, sum(amount) as total_sales
   FROM "my-bucket@s3tablescatalog".production.orders
   GROUP BY region;

The following example creates an Iceberg materialized view in a general-purpose Amazon S3 bucket with a partition transform and compression specified.

CREATE MATERIALIZED VIEW awsdatacatalog.mydb.daily_revenue
USING ICEBERG
LOCATION 's3://my-analytics-bucket/mvs/daily_revenue/'
PARTITIONED BY (day(order_date))
TABLE PROPERTIES ('write.parquet.compression-codec' = 'zstd')
AS SELECT order_date, sum(amount) as revenue, count(*) as num_orders
   FROM awsdatacatalog.mydb.orders
   GROUP BY order_date;

The following example creates an Iceberg materialized view using an external schema with two-part notation.

CREATE MATERIALIZED VIEW my_ext_schema.customer_totals
USING ICEBERG
LOCATION 's3://my-analytics-bucket/mvs/customer_totals/'
AS SELECT customer_id, count(*) as order_count, sum(amount) as total_spent
   FROM my_ext_schema.orders
   GROUP BY customer_id;