Redshift Materialized View Auto Refresh Failed for Kinesis Streaming Ingestion

Modify a streaming Materialized View (MV) to have a new column is not possible (check the following documentation on ALTER MATERIALIZED VIEWS).

In order to add a new column to a MV, follow these steps:

1. Create a new MV "new_streaming_MV", including the additional column/s. To turn on auto refresh, use AUTO REFRESH YES. The default behavior is manual refresh.
2. Back up the data in the old MV into the new MV using the ALTER TABLE APPEND command. The following command will move the data records from "old_streaming_MV" into the "new_streaming_MV": ALTER TABLE <new_streaming_MV> APPEND FROM <old_streaming_MV>;
3. Manually refresh the Materialized View using the command: REFRESH MATERIALIZED VIEW <new_streaming_MV>;
4. You can now drop the "old_streaming_MV".

Regarding the delay on the autorefresh of the streaming MV, Amazon Redshift considers multiple factors in order to complete refresh of the most important materialized views with minimal impact to active workloads in your cluster. These factors include current system load, the resources needed for refresh, available cluster resources, and how often the materialized views are used. Amazon Redshift prioritizes your workloads over autorefresh and might stop autorefresh to preserve the performance of user workload.

Please, refer to this additional documentation about Refreshing Materialized Views and Redshift Streaming Ingestion:

• https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh.html
• https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-streaming-ingestion.html