How to move data from Read Only db to full access DB Redshift

When using the zero-ETL integration between Amazon Aurora and Amazon Redshift, the data is indeed initially loaded into a read-only database in Redshift. However, you can still perform transformations and analysis on this data without manually moving it to a new database. Here's how you can work with the data:

1. Create a destination database: After successfully creating the zero-ETL integration, you need to create a destination database within your target Amazon Redshift cluster or workgroup. This step is crucial for accessing and working with the replicated data.

2. Use materialized views: Instead of moving the data to a new database, you can create materialized views in Redshift based on the read-only data. Materialized views allow you to transform and aggregate the data as needed, and they can be automatically refreshed to reflect the latest changes from the source.

3. Automatic updates: The zero-ETL integration ensures that data becomes available in Amazon Redshift within seconds of being written to Aurora. This means you don't need to manually refresh the data or set up timed runs. The data in Redshift will be automatically updated as changes occur in the Aurora source.

4. Perform analytics and transformations: Once you have created the destination database and set up materialized views, you can use Amazon Redshift's analytics and machine learning capabilities on the data. This allows you to run complex queries, create reports, and perform transformations without moving the data to a separate database.

5. Near real-time access: The zero-ETL integration provides near real-time access to the data, with updates typically available within 15 seconds. This eliminates the need for hourly refreshes and ensures you're working with the most up-to-date information.

By leveraging these features of the zero-ETL integration, you can effectively work with the replicated data in Redshift without manually moving it to a new database or setting up frequent refresh schedules. The integration is designed to provide seamless, near real-time data access while allowing you to perform necessary transformations and analytics directly in Redshift.

Sources
Creating Aurora zero-ETL integrations with Amazon Redshift - Amazon Aurora
Create a zero-ETL integration for Aurora - Amazon Redshift
Amazon Aurora MySQL zero-ETL integration with Amazon Redshift is now generally available | AWS News Blog
Zero-ETL Integration – Amazon Aurora with Amazon Redshift - AWS