Redshift Materialized Views

In Amazon Redshift, you cannot alter an existing materialized view to change its BACKUP option. The BACKUP parameter (which determines whether the materialized view is included in automated and manual cluster snapshots) can only be specified when you initially create the materialized view using the CREATE MATERIALIZED VIEW statement.

Amazon Redshift has significant limitations on altering materialized views after they've been created. According to the documentation:

• You generally cannot alter a materialized view's definition (its SQL statement)
• You cannot rename a materialized view
• Certain operations like renaming columns, dropping columns, or changing column types in base tables can leave the materialized view in a state that cannot be refreshed

If you need to change the BACKUP option for a materialized view, you would need to:

1. Create a new materialized view with the desired BACKUP option
2. Drop the old materialized view

Note that if other objects depend on this materialized view, you'll need to handle those dependencies carefully, potentially by using CASCADE when dropping the view (though this will also drop dependent objects) or by recreating the dependent objects after creating the new materialized view.
Sources
CREATE MATERIALIZED VIEW - Amazon Redshift
Understanding materialized views - AWS Prescriptive Guidance

Hello @sravan No, you cannot directly alter a Materialized View in Redshift with a BACKUP option to ensure it's included in the snapshot. Redshift snapshots capture the base tables, and materialized views need to be refreshed after a restore operation.

Verify AWS Documentation The AWS Redshift documentation for CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW does not list a BACKUP option. This strongly suggests that you cannot directly control whether a materialized view is included in a snapshot using a BACKUP property.

Determine Default Backup Behavior for Materialized Views Materialized views are derived from base tables. Redshift snapshots capture the data in the base tables. When a Redshift cluster is restored from a snapshot, the base tables are restored. The materialized views are not automatically populated with data during the restore process. They must be refreshed.

Conclusion Since there is no BACKUP option for materialized views, and they are not automatically populated on restore, the focus should be on ensuring the base tables are backed up and that a process is in place to refresh the materialized views after a restore.

If the Answer is helpful, please click Accept Answer & UPVOTE, this can be beneficial to other community members.