Amazon Redshift targets

Target preparation

Ensure that the Redshift database user has the following minimum permissions required for Database Ingestion and Replication:

grant create on database <database_name> to <username>;
grant create on schema <schema_name> to <username>;

If you use Redshift role-based access control (RBAC), grant these permissions to the appropriate roles. With these permissions, users can perform all Database Ingestion and Replication operations, including deploying tasks, having target tables created at runtime, and redeploying, resynchronizing, and resuming jobs.

To check if a user already has the permissions required to access the database and schema, use the following system functions:

select has_database_privilege('<username>', '<databaseName>', 'create');
select has_schema_privilege('<username>', '<schemaName>', 'create');

Note: If you create a new database user, you must explicitly grant the permissions needed to access the existing database, schema, and table objects to that user.

Usage considerations

•You can use either Amazon Redshift or Amazon Redshift Serverless targets in database ingestion and replication jobs.

•Before writing data to Amazon Redshift target tables, database ingestion and replication jobs stage the data in an Amazon S3 bucket. You must specify the name of the bucket when you configure the database ingestion and replication task. The database ingestion and replication jobs use the COPY command to load the data from the Amazon S3 bucket to the Amazon Redshift target tables. For more information about the COPY command, see the Amazon Web Services documentation.

•When you define a connection for an Amazon Redshift target, provide the access key and secret access key for the Amazon S3 bucket in which you want the database ingestion and replication jobs to stage the data before loading it to the Amazon Redshift target tables.

•Incremental load jobs and combined initial and incremental load jobs generate a recovery table named INFORMATICA_CDC_RECOVERY on the target to store internal service information. The data in the recovery table prevents jobs that are restarted after a failure from propagating previously processed data again. The recovery table is generated in the same schema as the target tables.

•Database ingestion and replication jobs convert infinity and NaN values in FLOAT columns in source tables to null values in Amazon Redshift target tables.