Skip to main content

Redshift

Availability
Available CoreAvailable StandardAvailable PlusAvailable ProAvailable Enterprise FlexAvailable Self-Managed EnterpriseAvailable PyAirbyte
Support Level
Airbyte
Connector Version
4.0.1 (last updated 8 days ago)
Sync Success Rate
Usage Rate
Definition ID
f7a7d195-377f-cf5b-70a5-be6b819019dc

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 the Airbyte UI.

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

Prerequisites​

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 Airbyte-specific entities in Redshift​

To set up the Redshift destination connector, you first need to create Airbyte-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 Airbyte data (if it does not already exist)
CREATE
DATABASE airbyte_database;
  1. 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();
  1. Run the following script to create the schema, user, and grants:
-- create a schema for Airbyte data (if it does not already exist)
CREATE SCHEMA IF NOT EXISTS airbyte_schema;

-- create Airbyte 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;
  1. Verify the script ran successfully in the Query Editor.

NOTE: Our integration automatically creates the necessary schemas in your Redshift database. To enable this, ensure the connection user has CREATE privileges on the database. If you prefer to create schemas manually, grant USAGE and CREATE privileges on those schemas to the Airbyte user.

Step 2: Set up S3 staging​

Airbyte 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 Airbyte​

Navigate to the Airbyte UI to set up Redshift as a destination:

  1. Log into your Airbyte 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​

FieldDescription
HostThe endpoint of your Redshift cluster or serverless workgroup. Provisioned clusters end with .redshift.amazonaws.com; serverless workgroups end with .redshift-serverless.amazonaws.com. Example: my-cluster.abc123xyz.us-east-1.redshift.amazonaws.com
PortPort of the database. Default: 5439
UsernameThe username you created in Step 1 to allow Airbyte to access the database. Example: airbyte_user
PasswordThe password associated with the username.
DatabaseThe name of the database you want to sync data into. This database must already exist within your Redshift cluster. Example: airbyte_database
Default SchemaThe default schema tables are written to if the source does not specify a namespace. Default: public

S3 Staging fields​

FieldDescription
S3 Bucket NameThe name of the staging S3 bucket you created in Step 2. Example: airbyte-staging-bucket
S3 Bucket RegionThe region of the S3 staging bucket. Place in the same region as your Redshift cluster to reduce costs. Example: us-east-1
S3 Access Key IDThe AWS Access Key ID for an IAM user with read and write permissions to the staging bucket.
S3 Secret Access KeyThe corresponding AWS Secret Access Key for the Access Key ID.
S3 Bucket Path (Optional)The directory under the S3 bucket where staging data will be written. If not provided, defaults to the root directory. Example: data_sync/redshift
S3 Filename Pattern (Optional)The pattern for S3 staging file names. Supported placeholders: {date}, {date:yyyy_MM}, {timestamp}, {timestamp:millis}, {timestamp:micros}, {part_number}, {sync_id}, {format_extension}. Do not use empty spaces or unsupported placeholders.
Purge Staging Data (Optional)Whether to delete the staging files from S3 after completing the sync. Default: true. Set to false to retain files for debugging or auditing.

Advanced fields​

FieldDescription
JDBC URL Params (Optional)Additional properties to pass to the JDBC URL string when connecting to the database, formatted as key=value pairs separated by &. Example: key1=value1&key2=value2
SSH Tunnel Method (Optional)Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use.
Drop CASCADE (Optional)Whether to use CASCADE when dropping tables and columns. Warning: This deletes data in all dependent objects (views, etc.), including during schema evolution. Default: false.

Output schema​

Airbyte 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 Airbyte 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 Airbyte 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​

Airbyte typeRedshift type
STRINGVARCHAR(65535)
STRING (BASE64)VARCHAR(65535)
STRING (BIG_NUMBER)VARCHAR(65535)
STRING (BIG_INTEGER)VARCHAR(65535)
NUMBERDECIMAL(38,9)
INTEGERBIGINT
BOOLEANBOOLEAN
STRING (TIMESTAMP_WITH_TIMEZONE)TIMESTAMPTZ
STRING (TIMESTAMP_WITHOUT_TIMEZONE)TIMESTAMP
STRING (TIME_WITH_TIMEZONE)TIMETZ
STRING (TIME_WITHOUT_TIMEZONE)TIME
DATEDATE
OBJECTSUPER
ARRAYSUPER
UNKNOWNVARCHAR(65535)

Precision and size limits​

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

  • VARCHAR: Maximum 65,535 bytes.
  • SUPER: Maximum 16 MB per record. 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, Airbyte 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, Airbyte 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:

Sync ModeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

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 Airbyte 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 Airbyte Cloud, ensure the Airbyte 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.

Reference​

Config fields reference

Field
Type
Property name
string
database
string
host
string
password
integer
port
string
schema
string
username
boolean
drop_cascade
string
jdbc_url_params
object
tunnel_method
object
uploading_method

Changelog​

Expand to review
VersionDatePull RequestSubject
4.0.22026-06-0579161fix(destination-redshift): validate nested string sizes within SUPER columns to prevent COPY error 1224
4.0.12026-06-0479135fix(destination-redshift): resolve sslmode/sslfactory conflict in jdbc_url_params
4.0.02026-06-0279095Full rewrite using direct load (removal of raw tables), pre-insertion data validation with _airbyte_meta tracking, updated dependencies: Redshift JDBC 2.2.7, AWS SDK v2 2.31.1
3.5.42026-03-2375286Fix misleading SSH error when SQLException has null sqlState during connection check
3.5.32025-03-2456355Upgrade to airbyte/java-connector-base:2.0.1 to be M4 compatible.
3.5.22025-01-1451500Use a non root base image
3.5.12025-01-0649903Use a base image: airbyte/java-connector-base:1.0.0
3.5.02024-09-1845435upgrade all dependencies
3.4.42024-08-2044476Increase message parsing limit to 100mb
3.4.32024-08-2244526Revert protocol compliance fix
3.4.22024-08-1542506Fix bug in refreshes logic (already mitigated in platform, just fixing protocol compliance)
3.4.12024-08-1444020Simplify Redshift Options
3.4.02024-07-2342445Respect the drop cascade option on raw tables
3.3.12024-07-1541968Don't hang forever on empty stream list; shorten error message on INCOMPLETE stream status
3.3.02024-07-0240567Support for refreshes and resumable full refresh. WARNING: You must upgrade to platform 0.63.7 before upgrading to this connector version.
3.2.02024-07-0240201Add _airbyte_generation_id column, and add sync_id to _airbyte_meta column
3.1.12024-06-2639008Internal code changes
3.1.02024-06-2639141Remove nonfunctional "encrypted staging" option
3.0.02024-06-0438886Remove standard inserts mode
2.6.42024-05-3138825Adopt CDK 0.35.15
2.6.32024-05-3138803Source auto-conversion to Kotlin
2.6.22024-05-1438189adding an option to DROP CASCADE on resets
2.6.12024-05-13#38126Adapt to signature changes in StreamConfig
2.6.02024-05-08#37713Remove option for incremental typing and deduping
2.5.02024-05-06#34613Upgrade Redshift driver to work with Cluster patch 181; Adapt to CDK 0.33.0; Minor signature changes
2.4.32024-04-10#36973Limit the Standard inserts SQL statement to less than 16MB
2.4.22024-04-05#36365Remove unused config option
2.4.12024-04-04#36846Remove duplicate S3 Region
2.4.02024-03-21#36589Adapt to Kotlin cdk 0.28.19
2.3.22024-03-21#36374Supress Jooq DataAccessException error message in logs
2.3.12024-03-18#36255Mark as Certified-GA
2.3.02024-03-18#36203CDK 0.25.0; Record nulling for VARCHAR > 64K & record > 16MB (super limit)
2.2.02024-03-14#35981CDK 0.24.0; _airbyte_meta in Raw table for tracking upstream data modifications.
2.1.102024-03-07#35899Adopt CDK 0.23.18; Null safety check in state parsing
2.1.92024-03-04#35316Update to CDK 0.23.11; Adopt migration framework
2.1.82024-02-09#35354Update to CDK 0.23.0; Gather required initial state upfront, remove dependency on svv_table_info for table empty check
2.1.72024-02-09#34562Switch back to jooq-based sql execution for standard insert
2.1.62024-02-08#34502Update to CDK version 0.17.0
2.1.52024-01-30#34680Update to CDK version 0.16.3
2.1.42024-01-29#34634Use lowercase raw schema and table in T+D CDK changes
2.1.32024-01-26#34544Proper string-escaping in raw tables
2.1.22024-01-24#34451Improve logging for unparseable input
2.1.12024-01-24#34458Improve error reporting
2.1.02024-01-24#34467Upgrade CDK to 0.14.0
2.0.02024-01-23#34077Destinations V2
0.8.02024-01-18#34236Upgrade CDK to 0.13.0
0.7.152024-01-11#34186Update check method with svv_table_info permission check, fix bug where s3 staging files were not being deleted.
0.7.142024-01-08#34014Update order of options in spec
0.7.132024-01-05#33948Fix NPE when prepare tables fail; Add case sensitive session for super; Bastion heartbeats added
0.7.122024-01-03#33924Add new ap-southeast-3 AWS region
0.7.112024-01-04#33730Internal code structure changes
0.7.102024-01-04#33728Allow users to disable final table creation
0.7.92024-01-03#33877Fix Jooq StackOverflowError
0.7.82023-12-28#33788Thread-safe fix for file part names (s3 staging files)
0.7.72024-01-04#33728Add option to only type and dedupe at the end of the sync
0.7.62023-12-20#33704Only run T+D on a stream if it had any records during the sync
0.7.52023-12-18#33124Make Schema Creation Separate from Table Creation
0.7.42023-12-13#33369Use jdbc common sql implementation
0.7.32023-12-12#33367DV2: fix migration logic
0.7.22023-12-11#33335DV2: improve data type mapping
0.7.12023-12-11#33307DV2: improve data type mapping No changes
0.7.02023-12-05#32326Opt in beta for v2 destination
0.6.112023-11-29#32888Use the new async framework.
0.6.102023-11-06#32193Adopt java CDK version 0.4.1.
0.6.92023-10-10#31083Fix precision of numeric values in async destinations
0.6.82023-10-10#31218Clarify configuration groups
0.6.72023-10-06#31153Increase jvm GC retries
0.6.62023-10-06#31129Reduce async buffer size
0.6.52023-08-18#28619Fix duplicate staging object names in concurrent environment (e.g. async)
0.6.42023-08-10#28619Use async method for staging
0.6.32023-08-07#29188Internal code refactoring
0.6.22023-07-24#28618Add hooks in preparation for destinations v2 implementation
0.6.12023-07-14#28345Increment patch to trigger a rebuild
0.6.02023-06-27#27993destination-redshift will fail syncs if records or properties are too large, rather than silently skipping records and succeeding
0.5.02023-06-27#27781License Update: Elv2
0.4.92023-06-21#27555Reduce image size
0.4.82023-05-17#26165Internal code change for future development (install normalization packages inside connector)
0.4.72023-05-01#25698Remove old VARCHAR to SUPER migration Java functionality
0.4.62023-04-27#25346Internal code cleanup
0.4.52023-03-30#24736Improve behavior when throttled by AWS API
0.4.42023-03-29#24671Fail faster in certain error cases
0.4.32023-03-17#23788S3-Parquet: added handler to process null values in arrays
0.4.22023-03-10#23931Added support for periodic buffer flush
0.4.12023-03-10#23466Changed S3 Avro type from Int to Long
0.4.02023-02-28#23523Add SSH Bastion Host configuration options
0.3.562023-01-26#21890Fixed configurable parameter for number of file buffers
0.3.552023-01-26#20631Added support for destination checkpointing with staging
0.3.542023-01-18#21087Wrap Authentication Errors as Config Exceptions
0.3.532023-01-03#17273Flatten JSON arrays to fix maximum size check for SUPER field
0.3.522022-12-30#20879Added configurable parameter for number of file buffers (this version has a bug and will not work; use 0.3.56 instead)
0.3.512022-10-26#18434Fix empty S3 bucket path handling
0.3.502022-09-14#15668Wrap logs in AirbyteLogMessage
0.3.492022-09-01#16243Fix Json to Avro conversion when there is field name clash from combined restrictions (anyOf, oneOf, allOf fields)
0.3.482022-09-01Added JDBC URL params
0.3.472022-07-15#14494Make S3 output filename configurable.
0.3.462022-06-27#14190Correctly cleanup S3 bucket when using a configured bucket path for S3 staging operations.
0.3.452022-06-25#13916Use the configured bucket path for S3 staging operations.
0.3.442022-06-24#14114Remove "additionalProperties": false from specs for connectors with staging
0.3.432022-06-24#13690Improved discovery for NOT SUPER column
0.3.422022-06-21#14013Add an option to use encryption with staging in Redshift Destination
0.3.402022-06-17#13753Deprecate and remove PART*SIZE_MB fields from connectors based on StreamTransferManager
0.3.392022-06-02#13415Add dropdown to select Uploading Method.
PLEASE NOTICE: After this update your uploading method will be set to Standard, you will need to reconfigure the method to use S3 Staging again.
0.3.372022-05-23#13090Removed redshiftDataTmpTableMode. Some refactoring.
0.3.362022-05-23#12820Improved 'check' operation performance
0.3.352022-05-18#12940Fixed maximum record size for SUPER type
0.3.342022-05-16#12869Fixed NPE in S3 staging check
0.3.332022-05-04#12601Apply buffering strategy for S3 staging
0.3.322022-04-20#12085Fixed bug with switching between INSERT and COPY config
0.3.312022-04-19#12064Added option to support SUPER datatype in _airbyte_raw*** table
0.3.292022-04-05#11729Fixed bug with dashes in schema name
0.3.282022-03-18#11254Fixed missing records during S3 staging
0.3.272022-02-25#10421Refactor JDBC parameters handling
0.3.252022-02-14#9920Updated the size of staging files for S3 staging. Also, added closure of S3 writers to staging files when data has been written to an staging file.
0.3.242022-02-14#10256Add -XX:+ExitOnOutOfMemoryError JVM option
0.3.232021-12-16#8855Add purgeStagingData option to enable/disable deleting the staging data
0.3.222021-12-15#8607Accept a path for the staging data
0.3.212021-12-10#8562Moving classes around for better dependency management
0.3.202021-11-08#7719Improve handling of wide rows by buffering records based on their byte size rather than their count
0.3.192021-10-21#7234Allow SSL traffic only
0.3.172021-10-12#6965Added SSL Support
0.3.162021-10-11#6949Each stream was split into files of 10,000 records each for copying using S3 or GCS
0.3.142021-10-08#5924Fixed AWS S3 Staging COPY is writing records from different table in the same raw table
0.3.132021-09-02#5745Disable STATUPDATE flag when using S3 staging to speed up performance
0.3.122021-07-21#3555Enable partial checkpointing for halfway syncs
0.3.112021-07-20#4874allow additionalProperties in connector spec
Was this page helpful?