RougeWarehouse | Orchestrating Redshift with the Data API: Lambda, Step Functions and EventBridge

Every Redshift team eventually needs to run SQL from somewhere that is not a SQL client: a Lambda function reacting to an S3 event, a Step Functions state machine that loads ten tables in order, an Airflow task, an EventBridge schedule that refreshes a materialized view at 06:00. The instinct is to open a JDBC/ODBC connection from that code. On serverless compute that is the wrong instinct — you end up packaging a driver, putting the function in a VPC, managing a connection pool that a 15-minute Lambda timeout will outlive, and storing a password somewhere.

The Amazon Redshift Data API removes all of that. It is an HTTPS API: you call ExecuteStatement, you get a statement ID back immediately, and you poll or get notified when it finishes. No persistent connection, no driver, no VPC attachment, no password in your code. It works against provisioned clusters and Redshift Serverless workgroups alike.

This tutorial builds a small but realistic pipeline: an S3 arrival triggers a COPY, a MERGE runs after it, and a materialized view refresh closes the job — orchestrated by Step Functions, with EventBridge notifications instead of polling.

Prerequisites and permissions

You need an RA3 cluster or a Serverless workgroup, and an IAM role for the caller with:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:BatchExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift-data:ListStatements",
        "redshift-data:CancelStatement"
      ],
      "Resource": "*"
    },
    {
      "Effect": "Allow",
      "Action": "redshift-serverless:GetCredentials",
      "Resource": "arn:aws:redshift-serverless:us-east-1:111111111111:workgroup/*"
    }
  ]
}

For a provisioned cluster, swap the second statement for redshift:GetClusterCredentialsWithIAM on the cluster ARN (or redshift:GetClusterCredentials if you are still mapping to a dbuser). ListStatements, DescribeStatement and CancelStatement are scoped per-caller: a principal can only see statements it submitted, which is worth knowing when you are debugging someone else's job.

Three authentication styles are available. Prefer the first two:

Style	Parameters	Use when
Temporary credentials with IAM identity	`--workgroup-name` or `--cluster-identifier` + `--database`	Default. The caller's IAM identity maps to a Redshift user/role.
Secrets Manager	`--secret-arn`	You need a specific database user, e.g. a service account with tight grants.
Temporary credentials with `--db-user`	`--cluster-identifier --db-user`	Legacy provisioned clusters only.

Your first statement

aws redshift-data execute-statement \
  --workgroup-name analytics-wg \
  --database warehouse \
  --sql "CALL etl.load_orders();" \
  --statement-name nightly-orders \
  --with-event

The response returns immediately:

{
  "Id": "9d1f1c3a-1e0b-4a7f-b7a1-6f2a8a0d1234",
  "WorkgroupName": "analytics-wg",
  "Database": "warehouse",
  "CreatedAt": "2026-02-11T06:00:02.114000+00:00"
}

That ID is the handle for everything else. Check on it:

aws redshift-data describe-statement --id 9d1f1c3a-1e0b-4a7f-b7a1-6f2a8a0d1234

Status moves through SUBMITTED → PICKED → STARTED → FINISHED, or lands on FAILED / ABORTED. DescribeStatement also gives you Duration, RedshiftQueryId (join this to SYS_QUERY_HISTORY when you need the real execution plan) and, on failure, Error with the Redshift message. Log RedshiftQueryId from every pipeline step — it is the difference between "the load failed" and "the load failed because a varchar(40) in column 7 got 63 bytes".

For a SELECT, fetch rows with get-statement-result (paginated, and capped — the Data API is not a bulk export mechanism; use UNLOAD to S3 for anything large):

aws redshift-data get-statement-result --id 9d1f1c3a-...

Parameters, not string concatenation

The Data API supports named parameters. Use them; they are your SQL-injection defence when a pipeline passes in a partition date or a tenant ID.

aws redshift-data execute-statement \
  --workgroup-name analytics-wg \
  --database warehouse \
  --sql "DELETE FROM staging.orders WHERE load_date = :load_date AND tenant_id = :tenant" \
  --parameters '[{"name":"load_date","value":"2026-02-10"},{"name":"tenant","value":"acme"}]'

Two caveats that bite people:

Parameters are typed as strings and cast by Redshift, so :load_date compared against a date column works, but comparing to a timestamptz may need an explicit ::timestamptz.
You cannot parameterise identifiers. A table name has to be interpolated by your code — validate it against an allowlist.

Multi-statement batches and transactions

BatchExecuteStatement runs several statements in a single transaction on one session:

aws redshift-data batch-execute-statement \
  --workgroup-name analytics-wg \
  --database warehouse \
  --sqls "TRUNCATE staging.orders" \
         "COPY staging.orders FROM 's3://acme-lake/orders/2026-02-10/' IAM_ROLE default FORMAT PARQUET" \
         "MERGE INTO core.orders USING staging.orders s ON core.orders.order_id = s.order_id WHEN MATCHED THEN UPDATE SET status = s.status WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.status, s.amount)" \
  --with-event

If any statement fails, the whole batch rolls back — which is exactly what you want for a load-then-merge sequence, and much simpler than coordinating rollback across three Step Functions states. DescribeStatement on a batch returns HasResultSet per sub-statement in SubStatements[], each with its own status and error.

Use a batch when the statements are one logical transaction. Use separate ExecuteStatement calls when they are genuinely independent steps that you want to retry individually.

Stop polling: `--with-event`

The --with-event flag makes Redshift emit an EventBridge event when the statement reaches a terminal state. This is the single biggest win over a JDBC connection: your Lambda does not sit awake for eleven minutes waiting on a COPY, burning GB-seconds.

The event looks like this:

{
  "source": "aws.redshift-data",
  "detail-type": "Redshift Data Statement Status Change",
  "detail": {
    "statementId": "9d1f1c3a-...",
    "state": "FINISHED",
    "statementName": "nightly-orders",
    "redshiftQueryId": 1234567,
    "principal": "arn:aws:sts::111111111111:assumed-role/etl-runner/..."
  }
}

A rule that catches only failures:

{
  "source": ["aws.redshift-data"],
  "detail-type": ["Redshift Data Statement Status Change"],
  "detail": { "state": ["FAILED", "ABORTED"] }
}

Point that at an SNS topic and you have alerting on every Data API job in the account for about ten minutes of work. Set --statement-name on every call so the alert says nightly-orders and not a UUID.

Orchestrating with Step Functions

Step Functions has a native redshiftdata:executeStatement integration, including the .sync variant that waits for the statement to finish without you writing a poller. A three-step pipeline:

{
  "Comment": "Nightly orders load",
  "StartAt": "CopyStaging",
  "States": {
    "CopyStaging": {
      "Type": "Task",
      "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement.waitForTaskToken",
      "Parameters": {
        "WorkgroupName": "analytics-wg",
        "Database": "warehouse",
        "Sql": "CALL etl.copy_orders(:load_date)",
        "StatementName": "copy-orders",
        "Parameters": [
          { "Name": "load_date", "Value.$": "$.loadDate" }
        ],
        "ClientToken.$": "$$.Task.Token"
      },
      "TimeoutSeconds": 3600,
      "Next": "MergeCore"
    },
    "MergeCore": {
      "Type": "Task",
      "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement",
      "Parameters": {
        "WorkgroupName": "analytics-wg",
        "Database": "warehouse",
        "Sql": "CALL etl.merge_orders()",
        "StatementName": "merge-orders"
      },
      "Next": "RefreshMV"
    },
    "RefreshMV": {
      "Type": "Task",
      "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement",
      "Parameters": {
        "WorkgroupName": "analytics-wg",
        "Database": "warehouse",
        "Sql": "REFRESH MATERIALIZED VIEW reporting.orders_daily",
        "StatementName": "refresh-orders-daily"
      },
      "End": true
    }
  }
}

Two design notes:

Idempotency. ClientToken deduplicates submissions, so a Step Functions retry does not run the COPY twice. Derive it from the business key (orders-2026-02-10) rather than a random value, and your whole pipeline becomes safe to replay.
Put the SQL in stored procedures. Long inline SQL inside a state machine definition is unversioned, unreviewable and awkward to escape. Deploy etl.copy_orders as a stored procedure through your normal migration process and let the orchestrator call it by name. Your state machine then changes only when the shape of the pipeline changes.

Calling it from Lambda

Python, no driver, no VPC:

import boto3, os

rsd = boto3.client("redshift-data")

def handler(event, context):
    key = event["Records"][0]["s3"]["object"]["key"]
    load_date = key.split("/")[1]

    resp = rsd.execute_statement(
        WorkgroupName=os.environ["WORKGROUP"],
        Database=os.environ["DATABASE"],
        Sql="CALL etl.copy_orders(:load_date)",
        Parameters=[{"name": "load_date", "value": load_date}],
        StatementName=f"copy-orders-{load_date}",
        ClientToken=f"copy-orders-{load_date}",
        WithEvent=True,
    )
    return {"statementId": resp["Id"]}

The function returns in under a second regardless of how long the COPY takes. Failure handling lives in the EventBridge rule, not in the function.

Quotas and limits worth knowing before you design around them

Result sets are retained for 24 hours; fetch them or lose them.
There is a maximum SQL statement size (in the low hundreds of KB) and a cap on statements per batch — fine for procedure calls, not for generated 10 MB INSERT ... VALUES blobs.
The API is rate-limited per account and region. A fan-out that fires thousands of statements at once will get throttled; batch related work, or queue it.
Queries still queue in Redshift like any other query. The Data API changes how you submit, not how the warehouse schedules. If a Data API job is slow, look at concurrency scaling and query priority, not at the API.
Statements you submit under one IAM principal are not visible to ListStatements under another. Use a single dedicated role for the pipeline so that operators can see the whole picture.

When not to use it

The Data API is asynchronous and result-set limited. It is a poor fit for interactive BI (use a driver and a connection pool), for pulling millions of rows into an application (use UNLOAD to S3 and read the files), and for sessions that need temp tables to persist across calls — each ExecuteStatement may land on a different session, so a #temp table created in call one will not exist in call two. BatchExecuteStatement is the exception: one transaction, one session, so temp tables survive within the batch.

Where to start

If you have Lambda functions today with a psycopg2 layer and a VPC configuration that exists only so they can reach Redshift, migrating them to the Data API typically removes more code than it adds and cuts cold-start time noticeably. Start with the one job that times out most often.

RougeWarehouse's consultants build and operate this kind of orchestration on Amazon Redshift — Data API pipelines, Step Functions state machines, stored-procedure-based ELT and the monitoring around them. If you'd like a review of how your loads are scheduled today, get in touch.

Orchestrating Redshift with the Data API: Lambda, Step Functions and EventBridge