Usage of Zero-ETL Integration Tables (from Aurora-Postgres => Redshift Serverless)

The error you're encountering is due to the limitations of zero-ETL integration tables in Amazon Redshift. When using zero-ETL integration between Aurora PostgreSQL and Amazon Redshift, there are certain restrictions on how you can interact with the replicated data.

Zero-ETL integration is designed to provide near real-time access to data from Aurora in Redshift without the need for complex ETL pipelines. However, the tables created through this integration are read-only in Redshift. This means you cannot perform write operations, including SELECT INTO or CREATE TABLE AS operations, on these tables.

In your case, the dbt model is trying to create a new table (total_users__dbt_tmp) based on data from the zero-ETL integrated table (postgres_db.public.users). This operation is not supported because it involves writing data derived from the zero-ETL table.

To work around this limitation, you have a few options:

1. Use views instead of materialized tables in your dbt models when working with zero-ETL integrated data. Views will allow you to query the data without attempting to write it to a new table.

2. If you need to materialize the results, consider using an intermediate step where you first query the zero-ETL table and store the results in a regular Redshift table. Then, you can perform further transformations on this intermediate table.

3. For complex transformations that require writing data, you may need to consider alternative approaches such as performing the transformations in the source Aurora database or using other ETL tools to copy and transform the data in Redshift.

It's important to note that zero-ETL integration is designed for seamless read access to operational data in near real-time. For more complex data transformation workflows, you might need to combine zero-ETL with other data integration strategies to meet your specific requirements.

Remember that only the integration source can update data in the database created from the integration. To change the schema of a table, you would need to run DDL or DML commands against tables in the source Aurora database, not in Redshift.
Sources
Creating destination databases in Amazon Redshift - Amazon Redshift
Adding data to a source Aurora DB cluster and querying it in Amazon Redshift - Amazon Aurora
How Infosys used Amazon Aurora zero-ETL integration with Amazon Redshift for near real-time analytics and insights | AWS Database Blog