Recommended Free Tools
Amazon Redshift can store a materialized view’s query results as an Apache Iceberg table in Amazon S3 or an S3 Table Bucket. Redshift writes the data as Parquet and registers the Iceberg table in AWS Glue Data Catalog, where Iceberg-compatible engines such as Athena, Spark, and Trino can read it. You refresh the view manually; depending on its SQL and the source snapshots available, Redshift applies eligible changes incrementally or recomputes the result.
What is an Iceberg materialized view in Redshift?
It is a stored query result that Redshift manages as an Iceberg table, rather than as a materialized view kept in Redshift-managed storage. The underlying result is written to S3 as Parquet and registered in AWS Glue Data Catalog. This makes the output available to other engines that can read Iceberg tables, while Redshift remains responsible for refreshing and dropping the Redshift-created view. See AWS’s guide to materialized views stored as Apache Iceberg tables.
Because the result is physically stored, a reader can query the materialized output without rerunning the defining query against its source tables for every read. That can suit repeated analytical queries, but AWS’s feature documentation does not establish a particular performance improvement; results depend on the workload and should be measured in your environment.
How does Redshift create and refresh one?
- Define the view: Write a query over supported Iceberg source tables and create the materialized view with
USING ICEBERG. The target database must exist in Glue Data Catalog. - Store the result: Redshift executes the query and writes the result as Parquet data in S3 or an S3 Table Bucket, then registers the Iceberg table in Glue.
- Refresh after source changes: Run
REFRESH MATERIALIZED VIEW. Redshift compares source Iceberg snapshots with those recorded at the preceding refresh and uses incremental processing when the definition and available snapshots qualify. Otherwise, it recomputes the result. - Query the output: Use Redshift or another Iceberg-compatible engine, such as Athena, Spark, or Trino, to read the resulting table.
To inspect refresh history for the local cluster, query SVL_MV_REFRESH_STATUS; its records indicate whether a refresh was incremental or full. Each cluster keeps its own history. Use SHOW TABLES to locate Iceberg materialized views in supported catalog paths.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →#1 Best Overall
How is an Iceberg-stored view different from a view on an Iceberg source?
The distinction is where the materialized result is stored. A conventional Redshift materialized view is stored in Redshift-managed storage. An Iceberg-stored view uses USING ICEBERG and writes its result to S3 as an Iceberg table. A third, different arrangement is an ordinary Redshift materialized view whose query reads an Iceberg source table.
| Implementation | Where the result is stored | Refresh behavior relevant to this feature | Who can read the result |
|---|---|---|---|
| Ordinary Redshift materialized view | Redshift-managed storage | Uses Redshift materialized-view refresh options; behavior depends on the view and configuration. | Redshift users and workloads. |
| Redshift materialized view defined on an Iceberg source | Redshift-managed storage | General Redshift refresh guidance can include autorefresh for views defined on Iceberg tables. | Redshift users and workloads. |
Redshift materialized view stored as Iceberg (USING ICEBERG) |
Parquet data in S3 or an S3 Table Bucket, registered in Glue Data Catalog | Manual refresh; eligible definitions may refresh incrementally, while others require a full refresh. | Redshift and other Iceberg-compatible engines. |
For the Iceberg-stored case, AWS explicitly says autorefresh is unsupported. Do not apply guidance about autorefresh for views defined on Iceberg sources to a view whose own output is stored as Iceberg. The feature-specific rules are in AWS’s CREATE MATERIALIZED VIEW documentation and REFRESH MATERIALIZED VIEW documentation.
Which queries can refresh incrementally?
Incremental refresh is limited to supported SQL shapes. AWS documents eligible patterns that include SELECT queries with FROM, WHERE, and GROUP BY using COUNT and SUM, as well as inner joins between Iceberg sources. Eligibility depends on the complete view definition, not just whether it contains one supported function.
The following constructs make a view ineligible for incremental refresh under the documented rules, so refresh requires full recomputation:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems- Outer joins:
LEFT,RIGHT, andFULL. - Set operations:
UNION,UNION ALL,INTERSECT,EXCEPT, andMINUS. - Aggregates other than
COUNTandSUM, including distinct aggregates such asCOUNT(DISTINCT)andSUM(DISTINCT). - Window functions, subqueries, or
DISTINCT. GROUPING SETS,ROLLUP, orCUBE.
Even a definition that supports incremental refresh may need a full refresh if the source snapshots from its previous refresh have expired. A change to the materialized-view data made by an external engine or tool also forces full recomputation at the next refresh. Check the refresh status rather than assuming that each refresh was incremental.
What must be in place before creating one?
Supported source tables and Redshift deployment
- Every source must be an Apache Iceberg table in format version 2 or earlier. Native Redshift tables and other non-Iceberg sources are not allowed in an Iceberg-stored view. AWS says these materialized views cannot be created on Iceberg v3 tables; see its Iceberg v3 guidance.
- Sources must be in the same AWS account and Region as the materialized view.
- AWS documents support for Redshift Serverless and provisioned clusters using RG instance types. RA3 and DC2 instance types are not supported for this feature.
Glue, IAM, and refresh permissions
- The target Glue Data Catalog database must already exist, and the creator needs permission to create tables in it.
- The IAM role recorded as the view definer needs
SELECTpermission on every source table. - The caller who runs a refresh needs
ALTERpermission on the materialized view, and the definer role must retain its source-tableSELECTpermissions.
Identifiers and unsupported features
- Table names, column names, aliases, and all other identifiers in the definition must be lowercase. Case-sensitive identifiers must be disabled both when creating and refreshing the view:
enable_case_sensitive_identifier = false. - The feature does not support
BACKUP,DISTSTYLE,DISTKEY, orSORTKEYoptions. - Temporary or system tables, user-defined functions, and mutable functions are not supported.
These gates are specific to the Iceberg-stored feature. Confirm the table format, deployment type, identifier setting, and role permissions before building the view definition.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What operational issues affect refreshes?
Manual scheduling and freshness
Since USING ICEBERG views do not autorefresh, choose a schedule or invoke refreshes based on how current downstream consumers need the data to be. The time between refreshes is also the period during which readers may see an older materialized result.
Snapshots, compaction, and external writes
Incremental refresh relies on source snapshot history. If the snapshot captured at the last refresh is expired, Redshift cannot calculate the delta from it and performs a full recomputation. For general-purpose S3 storage, AWS recommends regular compaction with an external tool and management of snapshot expiration. S3 Table Buckets handle compaction and file optimization automatically. Avoid modifying the materialized-view data through another engine or tool if you want to preserve incremental refresh eligibility.
Concurrent refresh attempts
More than one cluster can attempt a refresh. Redshift uses optimistic concurrency through Glue: one refresh can succeed, while another attempt may abort if a competing refresh has already completed. Design orchestration to handle a failed concurrent attempt rather than treating every abort as evidence of a broken view.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
