Create a sync to Amazon Redshift

Configure Amazon Redshift as your Celigo sync destination and let the sync manage the warehouse side for you: it creates the destination schema and tables on the first run, keeps them in step with your source as columns are added or widened, and loads data with Redshift's native bulk load. This article covers the destination settings specific to Redshift, what the first run creates, how schema changes are applied, the Celigo-to-Redshift data type mapping, and the limits to know before you start.

Prerequisites

An Amazon Redshift data-warehouse connection (see Set up a connection to Amazon Redshift). The sync lists only Redshift data-warehouse connections — the Amazon Redshift REST API connection can't be used as a sync destination. Either authentication type works; IAM role authentication is recommended.

Create a data ingestion sync

Follow the instructions in Create a data ingestion sync to create a sync and configure your source application.

Set up Amazon Redshift as your destination

1. Select Amazon Redshift as the destination that will ingest and store all source data from the sync.
2. Choose an established connection, or select Create connection to create a new one (see Set up a connection to Amazon Redshift).
   Caution: Avoid sharing connections with flows scheduled at the same time, as running them simultaneously can reduce performance.
3. Name your destination schema — Celigo creates a schema based on the objects, exports, and fields you selected when configuring the source application. Give the schema a meaningful name to help you identify the destination tables that correspond to it later. Enter the name — if the schema already exists in the database, the sync writes into it; if it doesn't, the first run creates it.
   
   A Redshift schema name must be 127 bytes or shorter, start with a letter or underscore, and contain only letters, digits, and underscores. The wizard rejects a name that breaks these rules before you can save.
   Note: This schema is for use only with this sync. Be careful when entering its name — you can't change it after you save and run the sync.

This sync names your destination tables using the following structure: <database>.<schema>.<table>

• <database> — The database set on the connection.
• <schema> — The destination schema name.
• <table> — The specific object, export, or custom table name selected for sync.

What the first run creates

On the first run, the sync creates whatever doesn't already exist in Redshift, so you don't write any DDL:

• The destination schema, if it doesn't exist. The public schema is never created, because every Redshift database already has one.
• One table per object, export, or normalized child array, with columns typed according to the data type mapping and with every Celigo-managed system column (see Understand Celigo sync system columns).
• Primary key columns are created as NOT NULL. A primary key can't be a column that maps to the Redshift SUPER type (an object, array, or variant field) — the run stops before any table is created and names the column.
• Column names longer than 127 bytes are shortened to fit. A shortened name or a Redshift reserved word gets an underscore appended. If two source fields shorten to the same column name, the run stops before any table is created and names both fields so you can rename one at the source or exclude it from the sync.

If the schema or a table can't be created, the run fails in the Load stage with an error that says which step failed and why.

How schema changes are applied in Redshift

The sync's schema drift policy decides whether a source change is applied to the destination (see Configure a schema drift policy). When it is, this is what happens in Redshift:

• A column is added — The column is added to the destination table and loads from the next run.
• A text column grows — The column is widened in place; existing values are untouched.
• A column changes to a different, compatible type — The sync converts the column to the new type and preserves the values already loaded.
• A column would shrink or lose precision — The change is rejected before anything is altered, and the run fails with an error naming the column, its current type, and the attempted type. Change the source field back, or exclude the field from the sync.
• A column is removed at the source — Nothing changes in Redshift; the column and its history remain, as for every destination.

A column defined as a Redshift IDENTITY column can't be used as a primary key — the run fails before loading and names the column.

Merge or append

Whether rows are updated or only added is set per object or export in the source step, not on the destination (see Create a sync from an application's objects). With a primary key set and the Merge ingestion mode, rows that match on the key are updated and new rows are inserted; without a primary key, every run appends.

Data type mapping

The data types sent to or inferred by Celigo syncs are transferred to Amazon Redshift as follows (see Understand how data types are inferred for what each Celigo data type represents).

System columns are created as BOOLEAN (_celigo_deleted), TIMESTAMP (_celigo_extracted_at, _celigo_loaded_at), and VARCHAR (_celigo_job_id, _celigo_id). See Understand Celigo sync system columns.

Learn more

• Celigo sync overview
• Understand how data types are inferred
• Create a data ingestion sync
• Troubleshoot sync run errors