Unable to Enable Redshift History for Cross Account ZeroETL of Aurora

The issue you're experiencing with enabling History Mode for your cross-account Zero-ETL integration between Aurora PostgreSQL and Amazon Redshift is likely related to permissions or connectivity constraints in the cross-account setup.

History Mode is a feature that allows you to track every version of records in your source tables within Amazon Redshift, creating Type 2 Slowly Changing Dimension (SCD 2) tables without writing any code. When enabled, it adds tracking columns to your target tables such as _record_is_active, _record_create_time, and _record_delete_time to monitor changes in the source data.

For your specific error, there are a few potential causes:

1. Connection Issues: The error suggests that the console may not be properly connected to the database in the target data warehouse that was created for the zero-ETL integration. Ensure you're accessing the correct database that was created as part of the integration.

2. Cross-Account Permissions: Since your setup spans two AWS accounts, you may need additional permissions beyond the standard zero-ETL integration setup. The cross-account nature of your integration might require specific IAM roles and permissions to enable History Mode.

3. Greyed Out Options: The fact that the individual table options are greyed out suggests that there might be a state issue with the integration or the tables themselves.

To resolve this issue:

1. Verify that you're connected to the correct database in Redshift that was created for the zero-ETL integration.

2. Check that the IAM role used for the integration has all necessary permissions across both accounts.

3. For the tables that failed to sync, you might need to resynchronize them using the ALTER DATABASE command:

ALTER DATABASE dbname INTEGRATION REFRESH TABLES table1, table2;

4. If the issue persists, you might need to review the cross-account setup to ensure all necessary permissions are in place for both the integration itself and for managing features like History Mode.

Remember that History Mode increases storage usage and costs since it preserves the complete history of data changes without deleting records in the target tables.
Sources
Amazon Redshift announces support for History Mode for zero-ETL integrations - AWS
Troubleshooting Aurora zero-ETL integrations - Amazon Aurora
History mode - Amazon Redshift
Encrypted Cross Account Zero-ETL integration between DynamoDB and Redshift | AWS re:Post