Skip to main content

Amazon Redshift Source

Connect Amazon Redshift as a source to replicate accessible tables into your destination.

For an overview of capabilities and use cases, see the Amazon Redshift connector page. If Snowflake is your target, use the Redshift to Snowflake migration page for the overview and the step-by-step Redshift to Snowflake tutorial for the full setup. To load data into Redshift, see Amazon Redshift Destination.

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 with SELECT access to the tables you want to sync
  • Metadata access to information_schema and pg_catalog
  • An S3 bucket and prefix for Redshift source data movement
  • An IAM role that Supaflow can assume and that Redshift can use for source reads
  • 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 sources require an S3 location and IAM role for data movement. Leave Use UNLOAD For Source enabled unless Supaflow support asks you to switch to the JDBC fallback. You can use one IAM role for both Supaflow and Redshift, or use a separate Redshift COPY/UNLOAD 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 UNLOAD. 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 UNLOAD. See AWS's role-based access control and UNLOAD documentation for the Redshift-side setup.

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

GRANT ASSUMEROLE
ON 'arn:aws:iam::<account-id>:role/<redshift-unload-role>'
TO supaflow_reader
FOR UNLOAD;

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

Create Database User​

Create a dedicated read-only user for Supaflow:

CREATE USER supaflow_reader PASSWORD 'your_secure_password';

Grant access to each schema and table you want to sync:

GRANT USAGE ON SCHEMA your_schema TO supaflow_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO supaflow_reader;

Replace your_schema with your Redshift schema name. Repeat the grants for every schema you want Supaflow to read.

What Gets Synced​

Supaflow supports Redshift tables across all schemas the configured user can access and has SELECT permission on.

You choose which schemas, tables, and columns to include in each pipeline.

Sync Modes​

Full refresh: On each sync, all rows are read from the selected object. Use full refresh for small tables, lookup tables, or objects without a reliable cursor column.

Incremental sync: Only rows in the selected cursor window are fetched. Use incremental sync for tables with a reliable date or timestamp column that advances when rows are inserted or updated.

Supaflow tracks cursor positions between syncs. The Redshift source does not read database logs or detect hard deletes from the source. If an object does not have a reliable cursor column, use full refresh.

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 Redshift database to connect to
Example: dev

Default Schema

Default schema used when a schema is not specified
Default: public

User*

Redshift database user
Example: supaflow_reader

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 Redshift UNLOAD output
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

Snowflake destinations

When the destination is Snowflake, it can load directly from the staged files in this bucket and prefix through a Snowflake storage integration. See Snowflake Destination: Direct S3 External-Stage Loads for the setup.

IAM Role ARN*

IAM role ARN Supaflow assumes with the External ID to read and write staged files
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 UNLOAD. 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

Use UNLOAD For Source

Use Redshift UNLOAD to export source reads to S3 before local processing
Default: enabled

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 your connection and save the source.

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 write UNLOAD files to S3​

Problem:

  • Source sync fails during UNLOAD
  • Error mentions S3 access denied or s3:PutObject

Solutions:

  1. Confirm the role is attached to Redshift for UNLOAD access.
  2. Grant the role S3 write access to the configured staging prefix.
  3. Grant ASSUMEROLE if required: 
    GRANT ASSUMEROLE
    ON 'arn:aws:iam::<account-id>:role/<redshift-unload-role>'
    TO supaflow_reader
    FOR UNLOAD;
  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 UNLOAD.

Tables missing​

Problem:

  • Connection succeeds, but expected objects do not appear
  • Schema discovery returns fewer objects than expected

Solutions:

  1. Grant schema and table access: 
    GRANT USAGE ON SCHEMA your_schema TO supaflow_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO supaflow_reader;
  2. Confirm metadata access:
    • The user must be able to read Redshift metadata from information_schema and pg_catalog
  3. Refresh schema in Supaflow after changing grants.

Query reached usage limit​

Problem:

  • Sync fails with Query reached usage limit
  • Many objects fail at once 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 syncs off-peak or reduce the selected object scope for the first run.

Source reads are slow or expensive​

Problem:

  • Initial syncs consume more Redshift capacity than expected
  • Large full refreshes take a long time

Solutions:

  1. Use incremental sync for large tables with reliable cursor columns.
  2. Limit pipeline selection to the tables and columns you need.
  3. Run large initial syncs off-peak.
  4. Review Redshift WLM, query monitoring, and Serverless usage limits for the Supaflow user.

Support​

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

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