Amazon Redshift

Amazon AWS Data Warehouse

Synopsis

Creates a target that loads data into Amazon Redshift using S3 staging and the COPY command. The target handles data file creation, S3 upload, and Redshift COPY operations with support for multiple tables and file formats. Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud.

Schema

- name: <string>
  description: <string>
  type: amazonredshift
  pipelines: <pipeline[]>
  status: <boolean>
  properties:
    key: <string>
    secret: <string>
    session: <string>
    region: <string>
    endpoint: <string>
    username: <string>
    password: <string>
    database: <string>
    schema: <string>
    port: <numeric>
    staging_bucket: <string>
    staging_prefix: <string>
    iam_role: <string>
    table: <string>
    tables: <table[]>
    name: <string>
    format: <string>
    compression: <string>
    extension: <string>
    batch_size: <numeric>
    max_size: <numeric>
    part_size: <numeric>
    timeout: <numeric>
    field_format: <string>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration

The following fields are used to define the target:

Field	Required	Default	Description
name	Y		Target name
description	N	-	Optional description
type	Y		Must be amazonredshift
pipelines	N	-	Optional post-processor pipelines
status	N	true	Enable/disable the target

AWS Credentials

Field	Required	Default	Description
key	N*	-	AWS access key ID for S3 authentication
secret	N*	-	AWS secret access key for S3 authentication
session	N	-	Optional session token for temporary credentials
region	Y	-	AWS region (e.g., us-east-1, eu-west-1)

* = Conditionally required. AWS credentials (key and secret) are required unless using IAM role-based authentication on AWS infrastructure.

Redshift Connection

Field	Required	Default	Description
endpoint	Y	-	Redshift cluster endpoint (without port or database)
database	Y	-	Redshift database name
username	Y	-	Redshift database username
password	Y	-	Redshift database password
schema	N	public	Redshift schema name
port	N	5439	Redshift port number

S3 Staging Configuration

Field	Required	Default	Description
staging_bucket	Y	-	S3 bucket name for staging files
staging_prefix	N	redshift-staging/	S3 key prefix for staging files
iam_role	N	-	IAM role ARN for Redshift to access S3 (recommended)
part_size	N	5	S3 multipart upload part size in MB

note

Using an IAM role is recommended for production. If not provided, AWS credentials will be used in the COPY command.

Table Configuration

Field	Required	Default	Description
table	N*	-	Single table name for data loading
tables	N*	-	Array of table configurations for multiple tables
name	N	"vmetric.{{.Timestamp}}.{{.Extension}}"	File name template for staged files
format	N	-	File format: parquet, json, or avro. Always set it. Left unset, the file is staged as JSON while the load reads it as Parquet, and every batch is refused
compression	N	zstd	File compression format. Leave it at the default with json, because the load step does not declare compression and a compressed line-format file is refused
extension	N	Matches format	File extension override

* = Either table or tables must be specified.

warning

Always set format explicitly. The staged file and the COPY command have to use the same format, and when format is not set the file is staged as JSON while the COPY command reads it as Parquet, so the cluster refuses every batch. Every example on this page sets it. With parquet or avro, each table also needs a file schema.

Table Array Configuration

When using the tables array, each table can have the following properties:

Field	Required	Default	Description
table	Y	-	Table name
name	Y	-	File name template for staged files
schema	N	Parent schema	Avro/Parquet schema: a Library name, built-in name, file path, or inline JSON (required for avro and parquet formats)
format	N	Parent format	File format for this table
compression	N	Parent compression	Compression format for this table
extension	N	Matches format	File extension override

Batch Configuration

Field	Required	Default	Description
batch_size	N	100000	Maximum number of events per file
max_size	N	33554432	Maximum file size in bytes before S3 upload (32 MB)
timeout	N	300	Timeout in seconds for COPY operations
field_format	N	-	Data normalization format. See applicable Normalization section

Scheduling

See Scheduling and Pool Behavior for interval and cron fields shared by all targets.

Debug Options

Field	Required	Default	Description
debug.status	N	false	Enable debug logging
debug.dont_send_logs	N	false	Process logs but don't send to target (testing)

Details

Data Loading Process

The Redshift target uses a three-stage process to load data:

1. File Creation: Accumulates data in memory and creates files in the specified format
2. S3 Upload: Uploads the file to the staging S3 bucket
3. COPY Command: Executes a Redshift COPY command to load data from S3 into the table
4. Cleanup: Deletes the staging file from S3 after successful load

Supported File Formats

The target supports the following file formats for COPY operations:

Parquet (default)

• Columnar format optimized for analytics
• Best compression and query performance
• Recommended for most use cases

JSON

• Semi-structured data format
• Uses auto option for schema detection
• Useful for nested or variable schemas

Avro

• Binary format with schema evolution support
• Uses auto option for schema detection
• Good for schema changes over time

Authentication Methods

S3 Authentication

• Static credentials (access key and secret key)
• IAM role-based authentication on AWS infrastructure

Redshift Authentication

• Username and password required
• Connection uses SSL by default

COPY Command Authentication

• IAM role (recommended): Redshift assumes role to access S3
• Static credentials: Embedded in COPY command (not recommended for production)

IAM Permissions

The Redshift target requires two sets of permissions: one for S3 staging operations (DataStream identity) and one for the COPY command (Redshift IAM role).

DataStream identity (for S3 staging):

IAM Action	Purpose
sts:GetCallerIdentity	Validate credentials at initialization
s3:PutObject	Upload staging files to S3 (also covers multipart upload lifecycle)
s3:DeleteObject	Clean up staged files after COPY
s3:AbortMultipartUpload	Abort failed multipart uploads

note

Per the AWS Service Authorization Reference, s3:PutObject covers CreateMultipartUpload, UploadPart, and CompleteMultipartUpload. Only s3:AbortMultipartUpload requires its own IAM action.

Minimum IAM policy for DataStream:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "STSIdentity",
      "Effect": "Allow",
      "Action": "sts:GetCallerIdentity",
      "Resource": "*"
    },
    {
      "Sid": "S3StagingAccess",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:AbortMultipartUpload"
      ],
      "Resource": "arn:aws:s3:::STAGING_BUCKET/*"
    }
  ]
}

Redshift IAM role (for COPY command, when iam_role is configured):

IAM Action	Purpose
s3:GetObject	Read staged files from S3
s3:ListBucket	List objects in the staging bucket

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "S3ReadForCopy",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::STAGING_BUCKET",
        "arn:aws:s3:::STAGING_BUCKET/*"
      ]
    }
  ]
}

note

When using inline credentials instead of an IAM role, the same identity that uploads to S3 must also have s3:GetObject permission for Redshift to read the staged files via the COPY command.

Connection Pool

The target maintains a connection pool to Redshift with the following settings:

• Maximum connections: 5
• Minimum connections: 1
• Connection lifetime: 1 hour
• Idle timeout: 30 minutes

S3 Staging

Files are staged in S3 before loading into Redshift. The S3 key structure is:

{staging_prefix}{table_name}/{file_path}

After successful COPY, the staging file is automatically deleted from S3.

Multiple Tables

You can configure multiple tables and route data to each of them. Every table is staged and loaded with the target's format, so keep the entries on the same format:

tables:
  - table: events
    schema: public
    format: parquet
  - table: logs
    schema: analytics
    format: parquet

Data is routed to tables based on the SystemS3 field in the log message.

Error Handling

The target handles errors at each stage:

• S3 upload failures return an error and retry
• COPY command failures return detailed error messages
• Connection pool handles reconnections automatically
• Staging files are retained on error for troubleshooting

Performance Considerations

File Size: Larger files generally provide better COPY performance. Use batch_size and max_size to control file sizes.

Format: Parquet provides the best compression and load performance for analytical queries.

Parallelism: The queue parallelism setting controls concurrent workers. Redshift COPY operations are I/O intensive, so setting parallelism to 1 is recommended to avoid connection pool exhaustion.

IAM Role: Using an IAM role for COPY operations is more secure and performs better than embedding credentials.

Examples

Basic Configuration

The minimum configuration for a Redshift target:

targets:
  - name: basic_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      table: "events"
      format: "json"

With IAM Role

Configuration using IAM role for COPY operations (recommended):

targets:
  - name: iam_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "events"
      format: "json"

Multiple Tables

Configuration with multiple tables:

targets:
  - name: multi_table_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      schema: "public"
      format: "parquet"
      tables:
        - table: "events"
          format: "parquet"
        - table: "logs"
          format: "parquet"
        - table: "metrics"
          schema: "analytics"
          format: "parquet"

JSON Format

Configuration using JSON format:

targets:
  - name: json_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "json_events"
      format: "json"

With Batch Limits

Configuration with batch size and file size limits:

targets:
  - name: batched_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "events"
      format: "json"
      batch_size: 100000
      max_size: 104857600

Custom Schema

Configuration with custom schema:

targets:
  - name: custom_schema_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      schema: "events_schema"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "application_logs"
      format: "json"

With Staging Prefix

Configuration with custom staging prefix:

targets:
  - name: custom_prefix_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      staging_prefix: "datastream/staging/"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "events"
      format: "json"

With Field Normalization

Using field normalization for standard format:

targets:
  - name: normalized_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "normalized_events"
      format: "json"
      field_format: "cim"

With Checkpoint Pipeline

Configuration with checkpoint pipeline for reliability:

targets:
  - name: reliable_redshift
    type: amazonredshift
    pipelines:
      - checkpoint
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "critical_events"
      format: "json"

Scheduled Loading

Configuration with scheduled data loading:

targets:
  - name: scheduled_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "hourly_events"
      format: "json"
      interval: "1h"

Custom Port

Configuration with custom Redshift port:

targets:
  - name: custom_port_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      port: 5440
      staging_bucket: "my-redshift-staging"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      table: "events"
      format: "json"

Debug Configuration

Configuration with debugging enabled:

targets:
  - name: debug_redshift
    type: amazonredshift
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "analytics"
      username: "admin"
      password: "MyPassword123"
      staging_bucket: "my-redshift-staging"
      table: "test_events"
      format: "json"
      debug:
        status: true
        dont_send_logs: true

Production Configuration

Configuration for production with optimal settings:

targets:
  - name: production_redshift
    type: amazonredshift
    pipelines:
      - checkpoint
    properties:
      key: "AKIAIOSFODNN7EXAMPLE"
      secret: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
      region: "us-east-1"
      endpoint: "prod-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com"
      database: "production"
      username: "datastream_user"
      password: "SecurePassword123"
      schema: "events"
      staging_bucket: "prod-redshift-staging"
      staging_prefix: "datastream/"
      iam_role: "arn:aws:iam::123456789012:role/RedshiftCopyRole"
      format: "parquet"
      batch_size: 500000
      max_size: 524288000
      timeout: 600
      field_format: "cim"
      tables:
        - table: "application_events"
          format: "parquet"
        - table: "security_events"
          format: "parquet"
        - table: "audit_logs"
          schema: "audit"
          format: "parquet"

Troubleshooting

This section covers the errors you are most likely to see with the amazonredshift target, what causes each one, and how to fix it.

Where to look:

• Director logs. Target errors are tagged with the target name and carry "Section":"SenderPool". The part after Reason: or after the last colon is the actual cause.
• The target's connection status in the web interface. It shows the same reason as the log line, prefixed with connection failed for <target name>:.

See Target Delivery Errors for how Director logs and retries target failures.

Which permission is missing?

Match the error you see against this table first. The upload and the COPY command are authorized separately, so read the missing right together with the place it has to be granted.

Error text	Missing right	Where it is granted
failed to upload to s3: ... api error AccessDenied: Access Denied	s3:PutObject and s3:AbortMultipartUpload	The IAM policy of the identity in key and secret, on arn:aws:s3:::my-bucket/*
Staged files pile up under staging_prefix and nothing is logged	s3:DeleteObject	The same policy, the same resource
failed to ping redshift: ... FATAL: password authentication failed for user "my_user"	A database user that may log in to database	The cluster. Create the user and let it connect to the database
failed to execute COPY command: ERROR: permission denied ..., typically SQLSTATE 42501	USAGE on the schema and INSERT on the table	The database, with GRANT USAGE ON SCHEMA my_schema TO my_user; and GRANT INSERT ON my_schema.my_table TO my_user;
failed to execute COPY command: followed by cluster text that typically names S3ServiceException or the IAM role	s3:GetObject and s3:ListBucket for the cluster's role, plus the association with the cluster	The role in iam_role, on arn:aws:s3:::my-bucket and arn:aws:s3:::my-bucket/*
operation error STS: GetCallerIdentity, ... api error InvalidClientTokenId	No right is missing. The access key pair is wrong, disabled, or from another account	See the STS entry below

The credentials in key and secret, or the role of the Director host, upload the staged file to staging_bucket. The cluster then reads that file back with the role in iam_role. The two identities are not the same, and granting one does not grant the other, so a target can upload every batch and still fail on every COPY command. An error that names S3: PutObject comes from the upload, and an error inside failed to execute COPY command comes from the cluster reading the bucket.

The role in iam_role also has to trust the Redshift service and be associated with the cluster, because a role with the right policy that is not attached fails the same way as a role with no policy at all. When iam_role is empty, the pair in key and secret is sent with the COPY command instead, so that pair then needs s3:GetObject as well as s3:PutObject.

"failed to ping redshift" with "password authentication failed"

[Error] [director] [target-<target id>] [basic_redshift] Failed to reinitialize target "basic_redshift" (attempt 1). Reason: failed to ping redshift: failed to connect to `user=my_user database=my_database`: my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com:5439 (my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com): server error: FATAL: password authentication failed for user "my_user" (SQLSTATE 28000)

Cause: the cluster answered and refused the login. The value in password does not match the user in username, or that user does not exist on the cluster. A message about the database instead, typically SQLSTATE 3D000, means database names a database the cluster does not have.

Fix: check username and password against the cluster, and remember that the user has to be able to connect to database. If either field uses a ${VAR} or $secret{...} reference, note that an unresolvable reference is reported as failed to resolve password: with the reason after the colon. A password authentication failed message therefore means the value did reach the cluster and was rejected there.

No data is lost. The target does not start, Director retries with a backoff up to once a minute, and records wait in the queue until the login succeeds.

"failed to ping redshift" with a dial error, a timeout, or "no such host"

Failed to reinitialize target "basic_redshift" (attempt 7). Reason: failed to ping redshift: failed to connect to `user=my_user database=my_database`: my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com:5439 (my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com): dial error: timeout: context deadline exceeded

Cause: the cluster did not answer on endpoint and port. The detail at the end says how far the attempt got:

• dial error: timeout: context deadline exceeded or i/o timeout: the packets never arrive. The cluster's security group is the usual reason. It has to admit inbound TCP on 5439, or on your port, from the address of the Director host.
• dial error: dial tcp ...: connect: connection refused or hostname resolving error: lookup ...: no such host: the host answers but nothing listens on that port, or endpoint cannot be resolved. Check both fields for typos.

Fix: in the Amazon Redshift console open the cluster, then Properties > Network and security settings, and add an inbound rule to its VPC security group for that port from the Director host. A cluster that is not publicly accessible also needs a route: run Director inside the VPC, or reach it over peering or a VPN. Confirm the cluster is not paused. This check is bounded to 10 seconds and timeout does not raise it, so a very slow link fails here even when the cluster is reachable.

No data is lost. This is retried until it is fixed, and the queue grows in the meantime.

"failed to upload to s3" with "AccessDenied"

Sender worker 2 execute() failed for basic_redshift: target broken: failed to finalize target cache: failed to upload to s3: operation error S3: PutObject, https response error StatusCode: 403, RequestID: ..., HostID: ..., api error AccessDenied: Access Denied

Cause: the identity that stages files may not write to staging_bucket. Above part_size the file is uploaded in parts, and the same denial then reads upload multipart failed, upload id: ..., cause: operation error S3: UploadPart, ... api error AccessDenied: Access Denied. Other upload failures look similar:

• api error NoSuchBucket: The specified bucket does not exist: staging_bucket is wrong. Use the bare bucket name, not an s3:// URL.
• api error ExpiredToken, or a redirect naming another endpoint, typically PermanentRedirect: the token in session has expired, or the bucket is not in region. Static session tokens are never refreshed, so prefer a key pair or a role on the Director host, and set region to the bucket's own region.
• context deadline exceeded: the upload did not finish within timeout seconds, 300 by default. Raise timeout or lower max_size.

Fix: grant s3:PutObject, s3:AbortMultipartUpload and s3:DeleteObject on arn:aws:s3:::my-bucket/* to the identity in key and secret. When the bucket enforces SSE-KMS with a customer managed key, that identity typically also needs kms:GenerateDataKey on the key.

No data is lost. The batch is retried until the upload succeeds, about every 5 seconds, so expect one of these lines per retry.

"failed to execute COPY command" with "Check 'stl_load_errors'"

Sender worker 0 execute() failed for basic_redshift: record rejected by target: failed to execute COPY command: ERROR: Load into table 'my_table' failed. Check 'stl_load_errors' system table for details. (SQLSTATE XX000)
Sender worker 0 deterministic failure for basic_redshift after 4 attempts — dropping (giving up): record rejected by target: failed to execute COPY command: ERROR: Load into table 'my_table' failed. Check 'stl_load_errors' system table for details. (SQLSTATE XX000)

Cause: the file reached the bucket and the cluster read it, but the rows do not fit the table. The cluster keeps the detail, not Director, so query its own load error table: select * from stl_load_errors order by starttime desc limit 10;. The colname, err_reason and raw_field_value columns name the column and the value that failed. A value too long for the column, a value that cannot be converted to the column type, and a NOT NULL column that your records do not carry are the usual reasons. The cluster sometimes reports the same thing directly, as SQLSTATE class 22... or 23502, for example ERROR: value too long for type character varying(64) (SQLSTATE 22001).

Fix: widen or retype the column, or correct the field in the pipeline that feeds the target. When the whole file format is the mismatch rather than a single column, see the next entry.

This one does lose data. The batch is redelivered 4 times and then dropped, which the dropping (giving up) line records. Fix the cause before the batches behind it follow.

Every batch is refused when "format" is not set

Sender worker 0 execute() failed for basic_redshift: record rejected by target: failed to execute COPY command: ERROR: Load into table 'my_table' failed. Check 'stl_load_errors' system table for details. (SQLSTATE XX000)

Cause: the staged file and the COPY command have to use the same format, and both follow format. A target that leaves format out stages the file as JSON while the COPY command reads it as Parquet, so the cluster refuses the whole batch. An entry under tables whose format differs from the top-level one fails the same way, because the COPY command uses the top-level value for every table. The wording varies here: for a columnar mismatch the cluster typically reports a SQLSTATE XX000 message about the file itself rather than the load error table line above.

Fix: set format at the top level of properties and give every entry under tables the same value. format: "json" needs nothing else and is what the examples on this page use. format: "parquet" and format: "avro" additionally need a file schema, covered below.

This loses data while it lasts, because each refused batch is dropped after 4 deliveries. The target's dropped counter in the stats view shows how much.

"failed to execute COPY command" with "does not exist" or "permission denied"

Sender worker 0 execute() failed for basic_redshift: target broken: failed to finalize target cache: failed to execute COPY command: ERROR: relation "my_schema.my_table" does not exist (SQLSTATE 42P01)

Cause: the target never creates tables. The table in table, or in a tables entry, has to exist already in schema, and the user in username has to be allowed to load it. A permission message, typically SQLSTATE 42501, means the table is there but the user has no INSERT on it. A FATAL: message about too many clients or a terminated connection means the cluster restarted, resized, or is at its connection limit, and Director reconnects on its own.

Fix: create the table with columns that match your records, then grant USAGE on the schema and INSERT on the table to the user, as in the permission table above. schema defaults to public, so a table in another schema is not found until you set it. Keep the schema in schema, because a qualified value in table is rejected as an invalid name.

No data is lost. The batch is retried until the table and the grants are in place.

"failed to execute COPY command" with an S3 or IAM role message from the cluster

Sender worker 1 execute() failed for basic_redshift: target broken: failed to finalize target cache: failed to execute COPY command: ERROR: ... (SQLSTATE XX000)

Cause: the cluster could not read the staged file. Everything after ERROR: is the cluster's own text, which typically names S3ServiceException with an access denial, or says the cluster is not authorized to assume the IAM role. The upload already succeeded, so this is not the write policy on key and secret. It is the identity the cluster reads with.

Fix:

1. Grant s3:GetObject on arn:aws:s3:::my-bucket/* and s3:ListBucket on arn:aws:s3:::my-bucket to the role in iam_role. Check that the field holds a role ARN, such as arn:aws:iam::000000000000:role/RedshiftCopyRole, and not an instance profile or a policy ARN.
2. Give that role a trust policy for redshift.amazonaws.com, and associate it with the cluster under Properties > Associated IAM roles. If iam_role is empty, the pair in key and secret is used instead and needs s3:GetObject.
3. On a cluster with Enhanced VPC Routing the read goes through your VPC, so it typically needs an S3 gateway endpoint or a NAT route.

No data is lost, but the staged file of every attempt stays in the bucket. Purge staging_prefix once the batches load again.

"redshift target requires either iam_role or access key/secret"

Sender worker 0 execute() failed for basic_redshift: target broken: failed to finalize target cache: redshift target requires either iam_role or access key/secret

Cause: the COPY command needs an identity the cluster can use, and neither iam_role nor a key and secret pair is set. This is what you see when Director runs on AWS with an instance or pod role: the upload works because that role covers it, and nothing is left for the cluster to read with.

Fix: set iam_role to the ARN of a role the cluster can assume. Filling in key and secret also works, but those credentials then appear in the statement the cluster runs and in its query history.

No data is lost, and the check happens after the upload, so each retry leaves another staged file behind. Purge staging_prefix afterwards.

"InvalidClientTokenId", "SignatureDoesNotMatch" or "ExpiredToken" from STS

[Error] [director] [target-<target id>] [basic_redshift] Failed to reinitialize target "basic_redshift" (attempt 4). Reason: operation error STS: GetCallerIdentity, https response error StatusCode: 403, RequestID: ..., api error InvalidClientTokenId: The security token included in the request is invalid.

Cause: the AWS credentials are checked when the target starts, and this check failed. InvalidClientTokenId means the access key does not exist, was deleted, or belongs to another account. SignatureDoesNotMatch means secret does not go with key, usually a truncated or pasted-over value. ExpiredToken means session has expired. A reason ending in failed to retrieve credentials means key or secret was empty, so the credentials of the Director host were used instead: environment variables, a shared AWS profile, or an instance role. On a host with none of those the text ends in no EC2 IMDS role found. Filling in only one of the two fields produces this as well. A reason that starts with failed to resolve access key or failed to resolve secret key is different again: the ${VAR} or $secret{...} reference in that field could not be read, and the text after the colon says why.

Fix: correct the key pair, or remove both fields and let the host's own role supply them. A host role covers the upload only, so iam_role is then required for the COPY command.

No data is lost. The target never starts, nothing is staged, and records wait in the queue.

"invalid schema name", "invalid table name", or "failed to parse redshift connection string"

Failed to reinitialize target "basic_redshift" (attempt 2). Reason: invalid schema name: my-schema
Failed to reinitialize target "basic_redshift" (attempt 2). Reason: failed to parse redshift connection string: cannot parse `postgres://my_user:xxxxx@my-cluster.abc123xyz789.us-east-1.redshift.amazonaws.com:5439/my_database?sslmode=require`: ...
Sender worker 0 execute() failed for basic_redshift: target broken: failed to finalize target cache: invalid table name: my-table

Cause: schema and table have to be bare SQL identifiers: a letter or an underscore, then letters, digits and underscores. Hyphens, dots, quotes and a leading digit are refused, so a qualified name in table fails too. endpoint has to be the bare hostname of the cluster, with no https:// prefix, no :5439 suffix and no database path.

Fix: put the schema in schema, the table in table, the port in port and the database in database, each as a plain value. The password is redacted in the connection string error, so that line can be shared as it is.

The first two stop the target before anything is staged, and nothing is lost. invalid table name is found after the file has already been uploaded, so every retry adds one more staged object. Purge staging_prefix once the name is corrected.

"schema is required for parquet format" or "invalid schema format"

Failed to reinitialize target "basic_redshift" (attempt 3). Reason: invalid table configuration: schema is required for parquet format
Failed to reinitialize target "basic_redshift" (attempt 3). Reason: invalid schema format: invalid field format: my_schema

Cause: parquet and avro write typed files, so they need a file schema. Under tables, the schema of an entry is that file schema: a Library name, a built-in model name, a path to a schema file, or inline JSON. The second message means the value was not recognized as any of those and was then read as an inline field list. A Redshift schema name in that position produces exactly this.

Fix: with format: "json" no file schema is needed. With parquet or avro, list your tables under tables and give every entry its own schema naming a real Avro or Parquet schema, while the top-level schema stays the Redshift schema. A catch-all table in these formats cannot serve both, because the top-level schema is then read as the file schema as well, so use format: "json" for the catch-all.

No data is lost. The target does not start until the schema resolves, and records wait in the queue.

"context deadline exceeded", "no such host", or certificate errors

Failed to reinitialize target "basic_redshift" (attempt 5). Reason: operation error STS: GetCallerIdentity, exceeded maximum number of attempts, 3, https response error StatusCode: 0, RequestID: , request send failed, Post "https://sts.us-east-1.amazonaws.com/": dial tcp: lookup sts.us-east-1.amazonaws.com: no such host

Cause: Director could not reach one of the endpoints this target depends on.

Fix: allow outbound traffic from the Director host to all three of sts.us-east-1.amazonaws.com:443 and my-bucket.s3.us-east-1.amazonaws.com:443, both with your own region and bucket in the name, and the cluster endpoint on TCP 5439 or on your port. The AWS calls honor HTTPS_PROXY, HTTP_PROXY and NO_PROXY from the environment of the Director service. When that proxy inspects TLS, the host has to trust its certificate authority, otherwise the reason contains x509: certificate signed by unknown authority. Requests to the instance metadata endpoint bypass the proxy, which is expected. The connection to the cluster does not go through the proxy at all and always runs over TLS, so it needs a direct network path.

No data is lost. Every one of these is retried until the path works.

The target is healthy but nothing arrives in the table

Check these in order.

1. debug.dont_send_logs is enabled. Records are processed by the pipeline and then dropped before staging. Nothing is uploaded, nothing is copied, and no counter moves. With debug.status also enabled, startup logs Log sending is disabled for this target. Set debug.dont_send_logs: false.
2. Rows load but the columns are empty. With format: "json" the cluster matches JSON keys to column names. Keys with no column are typically ignored, and columns with no key are typically loaded as NULL, so a naming mismatch looks like a successful load of empty rows. Align the field names with the columns, or set field_format to normalize them.
3. Fields are missing with parquet or avro. Records are shaped to the configured schema before they are written, so a field the schema does not declare never reaches the file. Extend the schema.
4. Records match no table, or the target loads on a schedule. With tables and no catch-all table, a record whose routing value matches no entry fails with file holder not found and is redelivered instead of being loaded. With interval or cron set, batches are staged and copied only when the schedule fires.
5. Staged files pile up in the bucket. The staged file is deleted after a successful load, but a failed delete is not reported. If objects accumulate under staging_prefix while rows do arrive, grant s3:DeleteObject, or add an S3 lifecycle rule on the prefix. Files from failed batches are kept on purpose.
6. A redelivered payload is loaded only once. After a restart between a load and the queue acknowledgment, the payload is redelivered and the tables that already took it are skipped. That is deduplication, not a lost batch.