Redshift

Setting up the Redshift destination connector involves setting up Redshift entities (cluster, database, schema, user) in the AWS console, configuring an S3 bucket for staging, and configuring the Redshift destination connector using Pipeloom.

This page describes the step-by-step process of setting up the Redshift destination connector.

Prerequisites

• An AWS account with access to Amazon Redshift
• A Redshift cluster (provisioned or serverless)
• An S3 bucket for staging data
• AWS IAM credentials with read and write permissions to the S3 bucket

NOTE: The Redshift destination uses S3 staging with COPY as the loading method. This is the recommended approach described by Redshift best practices. Data is uploaded to S3 as multiple files along with a manifest file, then loaded into Redshift via the COPY command.

Setup guide

Step 1: Set up Pipeloom-specific entities in Redshift

To set up the Redshift destination connector, you first need to create Pipeloom-specific Redshift entities (a database, schema, and user) with the appropriate permissions to write data into Redshift and manage staging operations.

You can use the following script in the Redshift Query Editor to create the entities:

1. Log into your AWS account and navigate to the Redshift service.
2. Open the Query Editor and connect to your cluster.
3. Edit the following script to change the password to a more secure password and to change the names of other resources as needed.

-- create a Database for Pipeloom data (if it does not already exist)
CREATE
DATABASE airbyte_database;

4. Switch your connection to airbyte_database in the Query Editor. Redshift does not support switching databases within a session, so you must select airbyte_database from the database dropdown before running the remaining statements.

TIP: You can verify you are connected to the correct database by running:

SELECT CURRENT_DATABASE();

5. Run the following script to create the schema, user, and grants:

-- create a schema for Pipeloom data (if it does not already exist)
CREATE SCHEMA IF NOT EXISTS airbyte_schema;

-- create Pipeloom user
CREATE
USER airbyte_user PASSWORD 'your_secure_password_here';

-- grant permissions on the database
GRANT CREATE
ON DATABASE airbyte_database TO airbyte_user;

-- grant permissions on the target schema
GRANT USAGE, CREATE
ON SCHEMA airbyte_schema TO airbyte_user;

6. Verify the script ran successfully in the Query Editor.

NOTE: Our integration can automatically create schemas in your Redshift database. That path requires CREATE privileges on the database (GRANT CREATE ON DATABASE). If you prefer to pre-create schemas manually, grant only USAGE and CREATE on those schemas to the Pipeloom user — the connector skips CREATE SCHEMA when the configured schema already exists, so database-level CREATE is not required in that case.

Step 2: Set up S3 staging

Pipeloom stages data in S3 before loading it into Redshift via the COPY command. You need to configure an S3 bucket and IAM credentials for this purpose.

1. Create an S3 bucket if you don't already have one for staging.
2. Place the S3 bucket in the same AWS region as your Redshift cluster to minimize networking costs and improve performance.
3. Create an IAM user (or use an existing one) with read and write permissions to the staging bucket.
4. Generate an access key for the IAM user.

See the S3 Staging fields table for the full list of required and optional S3 configuration parameters.

NOTE: S3 staging does not use the SSH Tunnel option for copying data. SSH Tunnel supports the SQL connection only. S3 is secured through public HTTPS access only. Subsequent queries on the destination tables are executed using the provided SSH Tunnel configuration.

Optional: SSH Bastion Host

This connector supports the use of a Bastion host as a gateway to a private Redshift cluster via SSH Tunneling. Enter the bastion host, port, and credentials in the destination configuration.

Step 3: Set up Redshift as a destination in Pipeloom

Navigate to Pipeloom to set up Redshift as a destination:

1. Log into your Pipeloom account.
2. In the left navigation bar, click Destinations. In the top-right corner, click + new destination.
3. On the destination setup page, select Redshift from the Destination type dropdown and enter a name for this connector.
4. Fill in the required fields using the configuration reference below.
5. Click Set up destination.

Connection fields

S3 Staging fields

Advanced fields

Output schema

Pipeloom writes each stream directly into a final table in Redshift with typed columns.

Final Table schema

The final table contains these fields, in addition to the columns declared in your stream schema:

• _airbyte_raw_id: A UUID assigned by Pipeloom to each event that is processed. Column type: VARCHAR(36).
• _airbyte_extracted_at: A timestamp representing when the event was pulled from the data source. Column type: TIMESTAMP WITH TIME ZONE.
• _airbyte_meta: A JSON object containing metadata about the record, such as changes applied during syncing. Column type: SUPER.
• _airbyte_generation_id: An identifier for the generation of the sync that produced this record. Column type: BIGINT.

See Pipeloom metadata fields for more information about these fields.

NOTE: As of version 4.0.0, the Redshift destination writes data directly to final tables with direct load. Raw tables ( _airbyte_raw_*) are no longer created. If you are upgrading from an older version, see the migration guide for details.

Schema naming

• Redshift lowercases all schema, table, and column names and replaces special characters with underscores, following the rules defined in Redshift Names & Identifiers.
• Identifiers are limited to 127 characters. Names that exceed this limit are truncated to 118 characters with an underscore and an 8-character hash suffix to avoid collisions.

Data type map

Precision and size limits

Redshift enforces size limits on certain data types. When a value exceeds a limit, Pipeloom nulls the value and records the change in the _airbyte_meta column.

• VARCHAR: Maximum 65,535 bytes.
• SUPER: Maximum 16 MB per record. Individual string scalars nested within a SUPER value are limited to 65,535 bytes. If any nested string exceeds this limit, Pipeloom nulls the entire SUPER value and records the change in _airbyte_meta. See the AWS documentation on SUPER type and SUPER limitations.
• BIGINT: Stores values in the range -2^63 to 2^63-1. If an integer value falls outside this range, Pipeloom nulls the value and records the change in _airbyte_meta.
• NUMERIC(38, 9): Redshift supports a maximum precision of 38 and scale of 9. If the source value has a scale greater than 9, Redshift silently rounds it — this is not recorded in _airbyte_meta. If the precision exceeds 38, Pipeloom nulls the value and records the change in _airbyte_meta.

Schema evolution

This connector supports automatic schema evolution. When the source schema changes, the connector automatically adds new columns to destination tables. The connector requires CREATE and ALTER TABLE privileges on destination schemas and tables to support this feature.

Supported sync modes

The Redshift destination connector supports the following sync modes:

Encryption

All Redshift connections are encrypted using SSL.

Troubleshooting

'Cannot connect to Redshift cluster'

If your Redshift cluster is in a private VPC, you may need to:

1. Allow connections from Pipeloom to your Redshift cluster (if they exist in separate VPCs).
2. Configure an SSH Bastion Host (see Step 2) to tunnel through to the private cluster.
3. For Pipeloom, ensure the Pipeloom IP addresses are allowed in your Redshift cluster's security group and network policy.

'S3 access denied' during staging

Ensure your IAM credentials have read and write permissions to the staging S3 bucket. Verify that:

• The Access Key ID and Secret Access Key are correct.
• The IAM user has a policy allowing s3:PutObject, s3:GetObject, s3:DeleteObject, and s3:ListBucket on the staging bucket.
• There is no S3 bucket policy blocking access.

Namespace support

This destination supports namespaces. The namespace maps to a Redshift schema.

Limitations

NULL primary keys in dedup syncs

When using Incremental Sync - Append + Deduped or Full Refresh - Overwrite + Deduped, records where any primary key column contains a NULL value are not written to the final table. This is by design for performance reasons.