Advanced Analytica Advanced Analytica: IBOM
← All connectors
Redshift logo

Destination connector

Redshift

Send governed data into Redshift with a clear, practical setup guide.

Version 4.0.13 certified generally available

Redshift

Set up Redshift entities in the AWS console, including a cluster, database, schema and user. Then configure an S3 staging bucket and the Redshift destination connector in the Airbyte UI.

Follow this guide to set up the Redshift destination connector.

Prerequisites

NOTE: The Redshift destination uses S3 staging with COPY as its loading method. Redshift best practices recommend this approach. Data is uploaded to S3 as multiple files with a manifest file. The COPY command then loads the data into Redshift.

Setup guide

Step 1: Set up Redshift entities

First, create Airbyte-specific Redshift entities: a database, schema and user. Give them permission to write data and manage staging operations in Redshift.

Run the following script in the Redshift Query Editor to create these entities:

  1. Log into your AWS account and go to the Redshift service.
  2. Open the Query Editor and connect to your cluster.
  3. Edit the following script. Set a more secure password and change other resource names 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 doesn’t support switching databases within a session. Select airbyte_database from the database dropdown before running the remaining statements.

TIP: Run the following command to confirm you’re connected to the correct database:

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. Confirm that the script ran successfully in the Query Editor.

NOTE: The integration can automatically create schemas in your Redshift database. This approach requires CREATE privileges on the database (GRANT CREATE ON DATABASE). If you create schemas manually, grant the Airbyte user only USAGE and CREATE on those schemas. The connector skips CREATE SCHEMA when the configured schema already exists. In this case, database-level CREATE isn’t required.

Step 2: Set up S3 staging

Configure an S3 bucket and IAM credentials to stage data. Airbyte stages data in S3 before the COPY command loads it into Redshift.

  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. This placement reduces networking costs and improves 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 all required and optional S3 configuration parameters.

NOTE: S3 staging doesn’t use the SSH Tunnel option when copying data. SSH Tunnel supports only the SQL connection. S3 is secured only through public HTTPS access. Later queries on the destination tables use the provided SSH Tunnel configuration.

Optional: SSH Bastion Host

You can use a Bastion host as an SSH gateway to a private Redshift cluster. Enter the bastion host, port and credentials in the destination configuration.

Step 3: Add Redshift in Airbyte

Set up Redshift as a destination in the Airbyte UI:

  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. Enter a name for this connector.
  4. Complete 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. The connector writes staging files to <bucket path>/<schema>/<table>/. Example: data_sync/redshift
S3 Filename Pattern (Optional)The pattern for S3 staging file names. Supported placeholders: {date} (UTC date as yyyy_MM_dd), {date:<format>} (UTC date in a custom Java SimpleDateFormat pattern, for example {date:yyyy_MM}), {timestamp} and {timestamp:millis} (epoch milliseconds), {timestamp:micro} (epoch microseconds), {part_number}, {sync_id}, and {format_extension} (.csv.gz). Unsupported placeholders are left in the file name as-is. Spaces are replaced with underscores, and .csv.gz is appended if the resolved name doesn’t already end with it. If not provided, file names use a timestamp and a random suffix.
Purge Staging Files and Tables (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 tables and columns with 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 to a final Redshift table with typed columns.

Final table schema

The final table includes these fields alongside the columns declared in your stream schema:

  • _airbyte_raw_id: A UUID that Airbyte assigns to each processed event. Column type: VARCHAR(36).
  • _airbyte_extracted_at: A timestamp showing when the event was pulled from the data source. Column type: TIMESTAMP WITH TIME ZONE.
  • _airbyte_meta: A JSON object containing record metadata, including changes applied during syncing. Column type: SUPER.
  • _airbyte_generation_id: An identifier for the sync generation that produced the record. Column type: BIGINT.

See Airbyte metadata fields for more information about these fields.

NOTE: From version 4.0.0, the Redshift destination writes data directly to final tables with direct load. It no longer creates raw tables (_airbyte_raw_*). If you’re upgrading from an older version, see the migration guide for details.

Schema naming

  • Redshift lowercases all schema, table and column names. It replaces special characters with underscores, following the rules in Redshift Names & Identifiers.
  • Identifiers can contain up to 127 characters. Longer names are truncated to 118 characters, followed by an underscore and an 8-character hash suffix to prevent 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 limits the size of certain data types. If a value exceeds a limit, Airbyte sets it to null. Airbyte 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 a nested string exceeds this limit, Airbyte nulls the entire SUPER value. It records the change in _airbyte_meta. See the AWS documentation on SUPER type and SUPER limitations.
  • BIGINT: Stores values from -2^63 to 2^63-1. If an integer falls outside this range, Airbyte nulls the value. It records the change in _airbyte_meta.
  • NUMERIC(38, 9): Redshift supports a maximum precision of 38 and a maximum scale of 9. Redshift silently rounds source values with a scale above 9. This change isn’t 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, it updates destination tables as follows:

  • New columns: The connector adds them to the destination table.
  • Changed column types: The connector adds a temporary column with the new type. It casts existing data into the new column, drops the original column and renames the temporary column. If a non-null value can’t be cast, the connector sets it to NULL. It records a DESTINATION_TYPECAST_ERROR change in _airbyte_meta.
  • Removed columns: The connector keeps them in the destination table. It doesn’t drop columns that no longer exist in the source.

This feature requires CREATE and ALTER TABLE privileges on destination schemas and tables. The sync fails if another database object depends on a column or table the connector must drop. To resolve this, drop dependent objects before syncing and recreate them afterwards. Alternatively, enable the Drop tables and columns with CASCADE option.

Supported sync modes

The Redshift destination connector supports these sync modes:

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

Encryption

SSL encrypts all Redshift connections.

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’re in separate VPCs.
  2. Configure an SSH Bastion Host to tunnel to the private cluster. See Step 2.
  3. For Airbyte Cloud, allow the Airbyte IP addresses in your Redshift cluster’s security group and network policy.

‘S3 access denied’ during staging

Make sure your IAM credentials have read and write permissions to the staging S3 bucket. Check that:

  • The Access Key ID and Secret Access Key are correct.
  • The IAM user’s policy allows s3:PutObject, s3:GetObject, s3:DeleteObject and s3:ListBucket on the staging bucket.
  • No S3 bucket policy blocks 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 with a NULL value in any primary key column aren’t written to the final table. This behaviour is intentional for performance reasons.

Changelog

Expand to review
VersionDatePull RequestSubject
4.0.132026-10-2289657Upgrade to Bulk CDK 1.1.9: fix critical CVEs in Apache MINA (mina-core 2.0.31), MINA SSHD 2.20.0 and BouncyCastle 1.86; netty 4.1.139; AWS SDK 2.57.2.
4.0.122026-10-1587619Upgrade to Bulk CDK 1.1.1.
4.0.112026-10-1588985Suppress a SpotBugs false positive (NP_NONNULL_PARAM_VIOLATION) on the suspend createNamespace so the connector lint passes; no functional change.
4.0.102026-09-0385284Use svv_all_schemas instead of information_schema.schemata for schema existence check to support users with USAGE/CREATE-only grants.
4.0.92026-09-0185046fix: preserve empty strings on load instead of converting them to NULL; replace EMPTYASNULL with NULL AS sentinel for VARCHAR columns
4.0.82026-09-0184991Upgrade to Bulk CDK 1.0.25.
4.0.72026-08-1884459Skip CREATE SCHEMA when the configured schema already exists so check/sync work with schema-only grants
4.0.62026-08-0782274Set ColumnDropBehavior.RETAIN: stop dropping columns during schema evolution
4.0.52026-08-0483700Ignore NULL primary key records during dedup insert for performance; revert NULL-safe PK matching to plain equijoin
4.0.42026-07-2983245fix: use NULL-safe primary key matching in upsert to prevent silent mismatches when PK columns contain NULL values
4.0.32026-07-1481552fix: narrow SQLException handling to only treat table-not-found as missing
4.0.22026-06-0879161fix: validate nested string sizes within SUPER columns to prevent COPY error 1224
4.0.12026-06-0479135fix: 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-2675286Fix 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#29640Fix 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
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.