Redshift

Overview

The Redshift connector runs SQL through the Redshift Data API, so Tessel needs no inbound network path to your cluster. Queries can start runs on a schedule or run mid-workflow as a lookup, and every statement shows up in the trace of its run.

Teams use it to post accrual entries to NetSuite at month end, to sync revenue totals into QuickBooks and to move large extracts through Amazon S3 for other teams.

Supported deployments

• Provisioned clusters on RA3 and DC2 nodes

• Redshift Serverless workgroups

• Credentials from Secrets Manager or temporary IAM database credentials

How the Data API works

Tessel calls ExecuteStatement, waits for the statement with DescribeStatement and pages through rows with GetStatementResult. Rows are checked against the expected columns. Loads use COPY from a staged manifest, so a retry never loads the same file twice.

aws redshift-data execute-statement \
  --workgroup-name analytics \
  --database dev \
  --secret-arn arn:aws:secretsmanager:us-east-1:123456789012:secret:tessel \
  --sql "SELECT * FROM finance.open_invoices WHERE due_date < CURRENT_DATE"

aws redshift-data execute-statement \
  --workgroup-name analytics \
  --database dev \
  --secret-arn arn:aws:secretsmanager:us-east-1:123456789012:secret:tessel \
  --sql "SELECT * FROM finance.open_invoices WHERE due_date < CURRENT_DATE"

aws redshift-data execute-statement \
  --workgroup-name analytics \
  --database dev \
  --secret-arn arn:aws:secretsmanager:us-east-1:123456789012:secret:tessel \
  --sql "SELECT * FROM finance.open_invoices WHERE due_date < CURRENT_DATE"

Large exports

The Data API returns up to 100 MB of results per statement. For anything larger, use the unload action, which writes Parquet files to an S3 prefix you choose. The Amazon S3 connector can then pick the files up in the same workflow.

Permissions needed

Tessel signs in through a cross-account IAM role with an external ID and calls the Data API as a database user you create. Grant that user read access to the schemas workflows query and write access to one staging schema.

• redshift-data:ExecuteStatement, redshift-data:DescribeStatement and redshift-data:GetStatementResult

• redshift-serverless:GetCredentials or redshift:GetClusterCredentials

• SELECT on read schemas and INSERT on the staging schema only