How to change the owner of zero-ETL-created objects in Redshift

In Amazon Redshift, when working with zero-ETL integrations, there are limitations on what alterations you can make to the replicated tables. As you've discovered, you cannot change the owner of a zero-ETL table directly using the ALTER TABLE command, as these tables only support ALTER SORTKEY operations.

For zero-ETL integrations, you can change the ownership at the database level using the ALTER DATABASE command with the OWNER TO clause:

ALTER DATABASE your_zero_etl_database OWNER TO new_owner;

This requires superuser privileges. You've already done this step successfully.

However, for the schema and individual tables created by the zero-ETL integration, the ownership restrictions are more strict. The tables are managed by the integration process, and their structure and ownership are controlled by the integration itself.

Unfortunately, there isn't a direct way to change the ownership of the zero-ETL tables without recreating the integration. The error message you received ("Zero-ETL table can only undergo Alter Sortkey") confirms this limitation.

If you need to grant access to these tables to other users, instead of changing ownership, consider:

1. Creating appropriate permissions using GRANT statements to allow the new user to access the data
2. Creating views owned by the new user that reference the zero-ETL tables
3. Setting up appropriate role-based access control

These approaches allow you to maintain the zero-ETL integration while providing the necessary access to other users without changing the underlying table ownership.
Sources
ALTER DATABASE - Amazon Redshift
ALTER TABLE - Amazon Redshift
Encrypted Cross Account Zero-ETL integration between DynamoDB and Redshift | AWS re:Post