Amazon RDS for MySQL - Redshift Cluster Zero-ETL Integration: schema change

When you make schema changes to your source RDS for MySQL database in a zero-ETL integration with Amazon Redshift, the behavior depends on the type of change:

For tables that require resynchronization after schema changes, they will enter a "Syncing" state and won't be accessible in Amazon Redshift during this period. Certain operations that can trigger this resynchronization include:

• Adding a column in a specific position within a table
• Adding a timestamp column with a default value of CURRENT_TIMESTAMP
• Performing multiple column operations in a single command

For simpler schema changes, you can use the ALTER DATABASE command with the INTEGRATION REFRESH TABLES statement to resynchronize specific tables if they aren't properly reflected in Amazon Redshift:

ALTER DATABASE dbname INTEGRATION REFRESH TABLES table1, table2;

If you modify an existing integration with filtering rules, the new rules will apply from that point forward, and Amazon Redshift will drop tables that are no longer part of the filter.

For tables without primary keys, they may fail to replicate properly. The monitoring dashboard in Amazon Redshift will display these tables with a "Failed" status, and the overall zero-ETL integration status will change to "Needs attention." To resolve this, you'll need to either identify an existing key that can serve as a primary key or add a synthetic primary key to the table.

It's recommended to test any schema changes in a non-production environment before implementing them in production. You can also leverage the ability to create multiple zero-ETL integrations (up to 5) from a single RDS database to different Redshift clusters for development, staging, and production purposes.
Sources
Troubleshooting Amazon RDS zero-ETL integrations with Amazon Redshift - Amazon Relational Database Service
Handle tables without primary keys while creating Amazon Aurora MySQL or Amazon RDS for MySQL zero-ETL integrations with Amazon Redshift | AWS Database Blog
Amazon RDS for MySQL zero-ETL integration with Amazon Redshift, now generally available, enables near real-time analytics | AWS News Blog