Skip to main content

Amazon Redshift Destination

Connect Amazon Redshift as a destination to load data from your Supaflow sources into Redshift.

For an overview of capabilities and use cases, see the Amazon Redshift connector page. To use Redshift as a source, see Amazon Redshift Source.

Prerequisites​

Before you begin, ensure you have:

  • An active Amazon Redshift provisioned cluster or Serverless workgroup
  • Network connectivity between Supaflow and your Redshift endpoint
  • A Redshift database user that can create schemas, create tables, create temporary tables, and insert rows
  • An S3 bucket and prefix for Redshift load data movement
  • An IAM role that Supaflow can assume and that Redshift can use for destination loads
  • Firewall or security group rules that allow inbound connections from Supaflow, if your Redshift endpoint is not privately connected
Allow Supaflow Network Access

If your Redshift cluster or workgroup restricts inbound connections, add the Supaflow IP to your security group or firewall allowlist:

18.214.240.61

Redshift commonly listens on TCP port 5439. For private deployments, use your approved private network path instead of public IP allowlisting.

AWS Setup​

Redshift destinations require an S3 location and IAM role for data movement. You can use one IAM role for both Supaflow and Redshift, or configure a separate Redshift role in Advanced Settings.

Step 1: Create S3 Staging Permissions Policy​

  1. Log in to the AWS Console -> IAM -> Policies -> Create policy
  2. Click the JSON tab. Copy the following policy and paste it into the JSON editor:
Redshift S3 Staging Policy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowBucketListForStagingPrefix",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::<YOUR_BUCKET_NAME>",
      "Condition": {
        "StringLike": {
          "s3:prefix": [
            "<STAGING_PREFIX>",
            "<STAGING_PREFIX>/*"
          ]
        }
      }
    },
    {
      "Sid": "AllowObjectReadWriteDeleteInStagingPrefix",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:DeleteObject",
        "s3:AbortMultipartUpload",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": "arn:aws:s3:::<YOUR_BUCKET_NAME>/<STAGING_PREFIX>/*"
    }
  ]
}

  1. Modify these values:

    • Replace <YOUR_BUCKET_NAME> with your S3 bucket name
    • Replace <STAGING_PREFIX> with the S3 prefix Supaflow can use for Redshift staging
    • If you want full-bucket access, remove the Condition block and use arn:aws:s3:::<YOUR_BUCKET_NAME>/* for object access
    • Supaflow uses the explicit Bucket Region value you configure, so this minimal policy does not require s3:GetBucketLocation

  2. Click Next -> name the policy (for example, SupaflowRedshiftStagingPolicy) -> Create policy

Step 2: Create IAM Role with Trust Policy​

  1. Navigate to IAM -> Roles -> Create role
  2. Select Custom trust policy. Copy the following trust policy and paste it into the JSON editor:
Trust Policy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowSupaflowAssumeRole",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::805595753828:root"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<YOUR_EXTERNAL_ID>"
        }
      }
    },
    {
      "Sid": "AllowRedshiftAssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "redshift.amazonaws.com",
          "redshift-serverless.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

  1. Modify this value:

    • Replace <YOUR_EXTERNAL_ID> with a unique secret string
    • Use the same value in the Supaflow External ID field

  2. Click Next

  3. Attach the S3 staging policy you created

  4. Enter a descriptive role name, such as SupaflowRedshiftStagingAccess

  5. Click Create role

Separate Redshift Role

If your AWS security model requires separate roles, create one role that Supaflow can assume for S3 read/write access, then create a second role that Redshift can assume for COPY. Put the Redshift role ARN in Redshift COPY and UNLOAD IAM Role ARN and associate that role with your Redshift cluster or Serverless namespace. Both roles need access to the same staging bucket and prefix.

Step 3: Attach the Role to Redshift​

Attach the IAM role to your Redshift cluster or Serverless namespace so Redshift can use it for COPY. See AWS's role-based access control, COPY authorization, and COPY documentation for the Redshift-side setup.

If your Redshift database restricts role usage, grant the Supaflow database user permission to use the role for COPY:

GRANT ASSUMEROLE
ON 'arn:aws:iam::<account-id>:role/<redshift-copy-role>'
TO supaflow_writer
FOR COPY;

Replace the role ARN and user name with your actual values.

Create Destination User​

Create a dedicated Redshift user for Supaflow. A read-only user is not sufficient for a destination because Supaflow creates schemas, creates tables, writes rows, and evolves tables as source schemas change.

CREATE USER supaflow_writer PASSWORD 'your_secure_password';

Grant the user permission to create schemas and temporary tables in the target database:

GRANT CREATE, TEMPORARY ON DATABASE your_database TO supaflow_writer;

If you want Supaflow to load into an existing schema instead of creating schemas, grant usage and create on that schema:

GRANT USAGE, CREATE ON SCHEMA your_schema TO supaflow_writer;

Supaflow's setup validation checks that the destination user can read metadata, create temporary tables, create a schema, create a table, and insert rows.

Configuration​

Step 1: Connection​

Host*

Redshift cluster or Serverless workgroup endpoint, without protocol or port
Example: example.abc123.us-east-1.redshift.amazonaws.com

Port*

Redshift JDBC port
Default: 5439

Database*

Name of the target Redshift database
Example: dev

Default Schema

Default target schema for loaded tables
Default: public

User*

Redshift database user
Example: supaflow_writer

Password*

Password for the Redshift database user
Stored encrypted

Use SSL

Use TLS for the Redshift JDBC connection
Default: enabled

Step 2: S3 Staging​

S3 Staging Bucket*

S3 bucket used for staged load files
Example: my-redshift-stage

S3 Staging Prefix

S3 prefix Supaflow can use for staged files
Default: supa-prefix

Bucket Region*

AWS region of the S3 staging bucket
Example: us-east-1

IAM Role ARN*

IAM role ARN Supaflow assumes with the External ID to read and write staged files. Redshift COPY also uses this role unless a separate Redshift role is configured.
Example: arn:aws:iam::123456789012:role/SupaflowRedshiftStagingAccess

External ID*

External ID for secure role assumption. Must match the value in your trust policy.
Stored encrypted

Step 3: Advanced Settings (Optional)​

Redshift COPY and UNLOAD IAM Role ARN

Separate role ARN for Redshift to use when executing COPY for destination loads. Leave empty to use the main IAM Role ARN.

S3 Endpoint

Custom S3-compatible endpoint URL. Leave empty for standard AWS S3.

Noop Query

Simple query used to validate Redshift connectivity
Default: SELECT 1

JDBC Connection Properties

Additional Redshift JDBC properties as key=value pairs separated by semicolons
Example: loginTimeout=30;socketTimeout=60

Query Timeout

Maximum seconds a Redshift query may run. Use 0 for no timeout.
Default: 0

Step 4: Test & Save​

After configuring all required properties, click Test & Save to verify the connection, destination write permissions, and S3 role assumption.

Schema Evolution​

Supaflow creates target schemas and tables on the first load and keeps them in step with your source:

  • New columns are added to the Redshift target table
  • New tables are created when you add objects to the pipeline
  • Supported type changes are applied during schema evolution
  • Removed columns are kept in the destination; existing data is not dropped automatically

If an overwrite path recreates a target table, Supaflow preserves existing manual DISTKEY or SORTKEY settings when the referenced columns still exist.

The Redshift destination maps semi-structured source values to Redshift SUPER and binary values to VARBYTE. XML columns are not supported on the current Redshift destination path.

Troubleshooting​

Cannot connect to the Redshift endpoint​

Problem:

  • Test & Save cannot establish the connection to Redshift
  • Error mentions a connection timeout, connection refused, or an unreachable host

Solutions:

  1. Allow the Supaflow IP through your security group or firewall:
    • Add 18.214.240.61 for inbound access on the Redshift port you entered in Supaflow, usually 5439
    • For private deployments, use your approved private network path instead
  2. Confirm the endpoint is reachable. A Redshift Serverless workgroup or provisioned cluster must be publicly accessible, or reachable from Supaflow over a private network path.
  3. Check the Host and Port. Enter the endpoint in Host without protocol or port, and set Port to your Redshift listener (default 5439).

Access denied when assuming role​

Problem:

  • Test connection fails while assuming the IAM role
  • Error mentions sts:AssumeRole or an external ID mismatch

Solutions:

  1. Verify the trust policy:
    • Principal should include arn:aws:iam::805595753828:root
    • External ID must match the value entered in Supaflow
  2. Check the Role ARN:
    • Copy the complete ARN from the IAM role summary page
    • Confirm the role exists in the same AWS account you expect
  3. Wait for IAM propagation:
    • IAM changes can take a few minutes to apply

Redshift cannot read staged files from S3​

Problem:

  • Load fails during COPY
  • Error mentions S3 access denied, missing S3 permissions, or role authorization

Solutions:

  1. Confirm the role is attached to Redshift for COPY access.
  2. Grant the role S3 read access to the configured staging prefix.
  3. Grant ASSUMEROLE if required: 
    GRANT ASSUMEROLE
    ON 'arn:aws:iam::<account-id>:role/<redshift-copy-role>'
    TO supaflow_writer
    FOR COPY;
  4. Check Bucket Region in Supaflow. Supaflow uses this explicit value instead of requiring s3:GetBucketLocation, and Redshift requires the correct S3 bucket region for cross-region COPY.

Insufficient Redshift permissions​

Problem:

  • Connection test passes, but setup validation or loads fail
  • Error mentions CREATE SCHEMA, CREATE TABLE, temporary tables, or insert permission

Solutions:

  1. Grant destination write permissions: 
    GRANT CREATE, TEMPORARY ON DATABASE your_database TO supaflow_writer;
  2. If loading into an existing schema, grant schema permissions: 
    GRANT USAGE, CREATE ON SCHEMA your_schema TO supaflow_writer;
  3. Use a destination-specific user. A source read-only user is not sufficient for Redshift destination loads.

COPY row errors​

Problem:

  • Load completes with warnings or fails in strict error mode
  • Job details mention Redshift COPY row errors

Solutions:

  1. Open Job Details and review the per-object error artifact.
  2. Check recent schema changes in the source object.
  3. Review source values for Redshift type limits, especially very large strings, semi-structured values, or unsupported XML columns.

Query reached usage limit​

Problem:

  • Loads fail with Query reached usage limit
  • Failures occur on a Redshift Serverless workgroup

Solutions:

  1. Check Redshift Serverless usage limits for the workgroup.
  2. Increase the usage limit, wait for the limit period to reset, or change the breach action according to your AWS cost controls.
  3. Schedule large initial loads off-peak or reduce selected pipeline scope for the first run.

Support​

Need help? Contact us at support@supa-flow.io

Last updated on by pungaa
AI Tools
Ask ChatGPTClaudeAsk Claude
On this page