'consumer database and producer database have different collations' when querying my Redshift catalog from Athena

When consumer database and producer database have different database-level collations, Amazon Redshift does not support cross-database and cross-cluster queries as per mentioned in the documentation here [1]. It is a database collation limitation and database collation can be changed using the “Alter database” but this action only works when the database is empty.

As a workaround, depending on your use case, you can try use Column or Expression collation instead of database level. Find additional information here. [2]

-- Sample query ---
create materialized view customer_mv AS
select collate(cust_nbr,'case_insensitive') as cust_nbr_ci  ,cust_name from ci_db.customer;
---------------------

References:

1. https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html#r_CREATE_DATABASE-collation
2. https://aws.amazon.com/blogs/big-data/case-insensitive-collation-support-for-string-processing-in-amazon-redshift/

This error occurs because Amazon Redshift doesn't support cross-database queries when the consumer database and producer database have different database-level collations.

When working with database collation in Amazon Redshift, there are specific limitations that apply:

1. All system tables or views, including PG catalog tables and Amazon Redshift system tables, are case-sensitive.

2. Cross-database and cross-cluster queries are not supported when the consumer database and producer database have different database-level collations.

3. Case-insensitive collation is not supported in leader node-only queries.

In your case, it appears that your Redshift database has collation set to case_sensitive=off, which is creating a collation mismatch when Athena (as the consumer) tries to query your Redshift cluster (as the producer).

For data sharing to work properly, the producer and consumer databases must use the same collation settings. You'll need to ensure that both databases have matching collation configurations to resolve this issue.

If you're specifically trying to work with mixed-case identifiers in your federated queries, you might need to set the enable_case_sensitive_identifier parameter to true before creating the external schema. This is particularly important when working with databases, schemas, tables, or columns that have mixed-case names.
Sources
Considerations for data sharing reads and writes in Amazon Redshift - Amazon Redshift
CREATE DATABASE - Amazon Redshift
Examples of using a federated query - Amazon Redshift