All posts by Jason Pedreza

Guardrails and cost control for your agentic workloads with Amazon Redshift

Post Syndicated from Jason Pedreza original https://aws.amazon.com/blogs/big-data/guardrails-and-cost-control-for-your-agentic-workloads-with-amazon-redshift/

Note (November 2026): This post covers both MCP servers because the Redshift MCP Server has Redshift-specific settings, such as read-only by default and a cap on result rows, that the AWS MCP Server doesn’t have yet. We’ll update this post when the AWS MCP Server reaches parity.

Guardrails and cost control matter more as AI agents handle an increasing share of the queries in your analytics environments. Asked “Why did revenue drop in the West region last weekend?”, an agent finds the tables, writes and runs the SQL on Amazon Redshift, and follows up. This is agentic analytics, on your data warehouse and lakehouse.

Two risks decide whether these agentic workloads reach production. The OWASP Top 10 for LLM Applications names both:

  • Excessive agency: The agent can do too much. An agent acts on what a model decides. With more tools or permissions than its task needs, a misread request or an instruction hidden in a support ticket can become a real action, such as exporting customer records.
  • Unbounded consumption: The agent can spend too much. A retry loop can rerun a heavy query all night. A query can return millions of rows that the agent then sends to the model as input tokens. Finally, one SQL query that runs Amazon Bedrock model inference through Redshift ML invokes the model once for every row it processes.

Redshift includes the controls for both at no additional charge: per-agent roles, row-level and column-level security, dynamic data masking, read-only tools, workload management, and usage limits. You pay for queries from agents as you do for queries from any other client: for the compute and storage they use (Amazon Redshift pricing). Your agent’s model provider bills its model tokens, for example at Amazon Bedrock pricing when the agent uses a model in Amazon Bedrock. You pay for the Amazon Bedrock, Amazon Bedrock AgentCore, and monitoring controls in this post at their own rates.

In this post, we show you how to add guardrails and cost controls to agentic analytics on Redshift, using one model: six guardrails, three enforcement layers, and one cost envelope.

The layered guardrail model

Each request passes through the agent layer, the access layer, and the data layer before it reaches your data warehouse or lakehouse, and the cost envelope limits spend across them.

The layered guardrail model for agentic analytics on Amazon Redshift, read top to bottom in four numbered rows. 1, agentic patterns: conversational analytics through AI assistants with MCP servers or apps with Knowledge Bases, Enrich with Redshift ML, Act with scoped tools behind AgentCore Gateway, and Operate with Kiro and Redshift skills. 2, the agent layer, mostly probabilistic, screens prompts and tickets, guides reasoning, and checks the answer. 3, the access layer, deterministic, keeps SQL tools read-only, blocks infrastructure changes, and scopes writes. 4, the data layer, prompt-independent, applies per-agent identity, row, column, and masking policies, output limits, and compute limits. One storage box holds Redshift managed storage, and Apache Iceberg in Amazon S3 and S3 Tables with Lake Formation permissions. Below, the cost envelope adds model tokens, query compute, and data at rest, with no new meter.

Figure 1: The layered guardrail model. The lower the layer, the less a prompt can change it

Each guardrail type controls one thing and maps to an OWASP risk. Each layer is who enforces it:

  • Agent layer: Your agent or application, its framework, and Amazon Bedrock.
  • Access layer: The tools between the agent and your data, such as MCP servers and AgentCore Gateway (a capability of Amazon Bedrock AgentCore), and the IAM policies on the identities those tools use.
  • Data layer: Amazon Redshift and, for lakehouse tables, AWS Lake Formation.

The following table lists the six guardrail types. Each row names the guardrail type, the main OWASP risk it reduces, and the layer and controls that enforce it.

Guardrail type Main risk (OWASP) Who enforces it
Input Prompt injection (LLM01) Agent layer: Bedrock Guardrails, input validation
Reasoning and planning Excessive agency (LLM06) Agent layer: skills, business context, tool allow lists, step limits
Tool and action Excessive agency (LLM06) Access layer: tools, IAM, Policy in Amazon Bedrock AgentCore, threshold approval
Data Sensitive information disclosure (LLM02) Data layer: privileges, row and column policies, masking
Output Sensitive information disclosure (LLM02), Improper output handling (LLM05) Data layer. Agent layer for answers
Runtime and cost Unbounded consumption (LLM10) Data layer limits, token caps, AWS Budgets

The agent layer is the first filter, but it cannot be the only one. Most Amazon Bedrock Guardrails checks are model-based classifiers, some with a strength that you tune, so they catch most problems but not all. The guardrails that protect your data in Redshift therefore sit in the access and data layers, which apply fixed rules to the caller’s identity. You need both. AWS Identity and Access Management (IAM) policies decide which Redshift workgroups and APIs it can call, and Redshift and Lake Formation permissions decide what its SQL can touch. Fixed rules give the same answer whichever agent wrote the request, so a prompt injection that gets past the agent layer still cannot reach data that the agent’s identity is not granted.

Agentic analytics patterns on Amazon Redshift

Conversational analytics, the most common pattern, answers business questions from your data in self-service and managed delivery models. The others enrich data, take actions, or help administrators run the warehouse itself.

Pattern How it connects to Redshift Starting agency
Conversational analytics, self-service AI assistants (agents that a person works with directly) such as Kiro, Amazon Quick, and the Amazon SageMaker Data Agent, through the AWS MCP Server or Amazon Redshift MCP Server over the Redshift Data API Read-only
Conversational analytics, managed Amazon Bedrock Knowledge Bases turns end users’ questions into SQL Read-only
Enrich Redshift ML with Amazon Bedrock calls a model from SQL None: the model returns a column value
Act One scoped tool per action Scoped writes. Human approval for consequential ones
Operate Administrators review and tune the warehouse with Kiro, Redshift skills, and the MCP Server review_cluster tool Read-only. Changes ship as reviewed code

Every pattern needs the same two scopes for least privilege: IAM permissions that limit which workgroups and APIs an identity can call, and database permissions that limit what its SQL can read or change. What differs is which identity reaches Redshift:

  • Self-service (AI assistants with the Redshift MCP Server or AWS MCP Server). The AI assistant acts on behalf of the user, with the AWS profile in its MCP configuration. If your roles have different scopes, set up a profile for each and point the server at the narrowest one for the task, such as a read-only analyst role for sales questions. The setup steps are in access-layer/redshift-mcp-server and access-layer/aws-mcp-server in the aws-samples repository.
    • Redshift MCP Server: A user whose profile assumes a DataPlatformAdmin role runs every agent query as IAMR:DataPlatformAdmin, with that role’s grants. The server is read-only by default, and the agent can’t switch profiles. To use a broader profile, change it yourself in the MCP configuration.
    • AWS MCP Server: The agent can switch on each call among the profiles listed in the MCP Proxy for AWS configuration. List only the profiles you want it to use, and put the read-only one first so it’s the default. On provisioned clusters, a script can also choose a database user if the role allows redshift:GetClusterCredentials, so leave that permission out.
    • Both: The MCP configuration only controls the MCP tools. An agent that can run shell commands can use any profile on the machine, so the IAM roles and database grants are the real boundary.
    • SageMaker Data Agent: The agent runs SQL itself, including cells it fixes, as the signed-in person. Without trusted identity propagation, Redshift sees one IAMR:user-<id>@<project> database user per project member. With it, Redshift sees the person’s AWS IAM Identity Center user, so row-level security, masking, and grants apply to each person.
  • Custom agents, for example on Amazon Bedrock AgentCore. The database user comes from the role that calls the Data API: the AgentCore Runtime execution role, or the AWS Lambda function’s role behind AgentCore Gateway, such as IAMR:agent-support. If the agent acts for a specific person, use trusted identity propagation. The agent’s tool exchanges the person’s sign-in token through AWS IAM Identity Center, optionally federated with your external identity provider, and calls the Data API as that person. Redshift then applies their grants (writes included), row-level security, and masking, plus Lake Formation permissions if you enable that integration.
  • Managed (Amazon Bedrock Knowledge Bases). The knowledge base runs the SQL it generates as one identity. On Serverless workgroups and provisioned clusters, that is its service role with IAM or the user in an AWS Secrets Manager secret. Provisioned clusters also accept a named database user (authentication methods). Knowledge Bases can’t carry the actual user with trusted identity propagation, so when one identity serves many end users, row-level security can’t tell them apart. With one shared knowledge base, a West region manager and an East region manager query as the same database user, so both get every row that user can read. Instead, create one knowledge base or agent for each audience, such as one per sales region, and scope its role, grants, and row-level security policy to that audience.
Three access paths and the identity Amazon Redshift sees on each. A self-service AI assistant with the Redshift MCP Server or AWS MCP Server acts with the user’s AWS profile, so its queries run as a database user such as IAMR:DataPlatformAdmin. A custom agent in AgentCore Runtime, or a Lambda tool behind AgentCore Gateway, uses its execution role, such as IAMR:agent-support. Bedrock Knowledge Bases uses one identity for all its end users: its service role, a database user on provisioned clusters, or a Secrets Manager user, so row-level security can’t tell those users apart. Every path needs an IAM policy and database permissions. A band says one identity per agent gives it its own policies, cost, and audit.

Figure 2: Who Redshift sees on each access path

Decide how much agency to grant

Grant agency, what an agent can do, on two planes. The data plane is SQL on Redshift managed storage or Apache Iceberg tables, governed by Redshift and AWS Lake Formation permissions. The control plane is API calls that create, resize, manage, or delete infrastructure, governed only by IAM. The AWS Agentic AI Security Scoping Matrix bounds agency with permissions and autonomy with oversight. Following it, start read-only (Scope 1), then add scoped writes with a human in the loop for consequential ones (Scope 2). Keep agents off the control plane, and widen autonomy (Scopes 3 and 4) only within those boundaries.

Agent layer: Guide the request

The agent layer, mostly outside Redshift, is the first filter. Its controls run in your agent, Amazon Bedrock, and Amazon Bedrock AgentCore, so you set them up and pay for them there. These include the step, retry, and token limits that cap the agent’s own model spend.

  • Input. Bedrock Guardrails filters prompt injection attempts and denied topics, and ApplyGuardrail works with your choice of model.
  • Skills. The redshift-guide skill gives agents Redshift-specific knowledge, so fewer queries fail. The Agent Toolkit for AWS (aws configure agent-toolkit, AWS Command Line Interface (AWS CLI) 2.35.0 or later) and the AWS Data Analytics Plugin deliver skills to Kiro and other AI assistants.
  • Business context. Business context and skill assets in the AWS Glue Data Catalog tell agents which tables and joins to use. They are in preview in the US East (N. Virginia), US East (Ohio), US West (Oregon), and Europe (Ireland) Regions.
  • Agent configuration and oversight. Limit an agent’s tools before it runs. In Kiro, for example, the MCP configuration lists the tools to approve automatically (autoApprove) and the tools to hide from the agent (disabledTools). Add project instructions, and set step and retry limits in agent frameworks.

Access layer: Limit what the agent can call

The access layer decides which tools an agent can call and what each call can do. Redshift enforces its guardrails the same way for every prompt.

Data plane: A narrow tool surface on every path

Figure 3 shows the tool guardrail each connection path needs, plus least-privilege IAM and database permissions. Each path has a worked example in the access-layer folder of the aws-samples repository, with setup, verification, and clean-up steps.

Four access paths, each with what the agent can send, its guardrail, and how it connects. Redshift MCP Server: any SQL. Read-only mode: nothing a query changes is kept. One Data API call per tool call. AWS MCP Server: Python in aws___run_script. An agent profile and IAM policies on aws:ViaAWSMCPService. One Data API call per statement. Custom MCP server: only tool parameters. Fixed SQL and validated parameters, with AgentCore Policy checking each call at AgentCore Gateway. Bedrock Knowledge Bases: questions turned into SQL. One database and up to 50 tables, with descriptions and example queries steering the SQL. One session per Retrieve call. A band adds CONNECTION LIMIT and statement_timeout on every path, and SESSION TIMEOUT for idle driver sessions.

Figure 3: Access paths and their guardrails

Agents with shell access can bypass an MCP server with the AWS CLI, so scope IAM on every path. To allow only the AWS MCP Server path, see AWS MCP Server only, later in this section.

The agent’s IAM policy, access-layer/shared/agent-data-plane-policy.json in the aws-samples repository, has three statements:

  • Discover lets the agent list workgroups. It uses "Resource": "*" because ListWorkgroups doesn’t support resource-level permissions, so it can’t limit where SQL runs.
  • RunSqlOnOneWorkgroup lets the agent sign in and run SQL on one workgroup only. This is the statement that limits where SQL runs. It includes redshift-data:BatchExecuteStatement, which the Redshift MCP Server needs before it can run writes or named transactions. Without that action, the server stays read-only by refusing writes itself. With it, writes still need ACCESS_MODE=read-write in the MCP configuration, which is off by default, and the agent’s database grants still limit what a write can change.
  • ReadOwnStatements lets the agent check status and read results only for statements it ran itself.

Custom MCP server: Tools that run fixed SQL. Expose only tools that each run one fixed, parameterized statement against a curated view, under the server’s own IAM role with SELECT on that view only. Have each tool set a fixed StatementName and SQL comment, so AWS CloudTrail and query history name the tool. In the sample server (access-layer/custom-mcp-server/sales_metrics_mcp.py), the agent supplies only a sales region, which the server checks against an allowed list, so prompt injection can at most make the tool query a different allowed region.

AWS MCP Server: Scope the credentials, not the script. Its aws___run_script sends SQL through the Data API as the caller’s mapped database user, so an administrator profile means administrator access. Four guardrails close that gap:

  • A dedicated agent profile in the MCP Proxy for AWS, for an agent role with read-only grants.
  • No way to switch database users. Allow redshift-serverless:GetCredentials or redshift:GetClusterCredentialsWithIAM, not redshift:GetClusterCredentials or Secrets Manager secrets, which let a caller choose another user.
  • Agent workgroups only. A service control policy in AWS Organizations denies Data API SQL through AWS managed MCP servers unless the workgroup is tagged agent-access=true. The same statement also works in an IAM policy or permissions boundary. SCPs skip the management account, so test on a sandbox OU first:
{
  "Sid": "AgentsQueryOnlyAgentWorkgroups",
  "Effect": "Deny",
  "Action": ["redshift-data:ExecuteStatement", "redshift-data:BatchExecuteStatement"],
  "Resource": "*",
  "Condition": {
    "Bool": { "aws:ViaAWSMCPService": "true" },
    "StringNotEquals": { "aws:ResourceTag/agent-access": "true" }
  }
}
  • AWS MCP Server only. To stop the agent’s shell from going around the server, deny redshift-data:* on the agent role unless the call comes through it. Also deny direct driver sign-in when aws:ViaAWSService is false: redshift-serverless:GetCredentials, redshift:GetClusterCredentialsWithIAM and redshift:GetClusterCredentials, plus GetIdentityCenterAuthToken when you use trusted identity propagation (access-layer/aws-mcp-server/deny-data-api-outside-aws-mcp.json). Attach it to agent roles or permission sets, not as an SCP. IAM can’t tell the local Redshift MCP Server from the AWS CLI, so on that path read-only database grants are the boundary:
{
  "Sid": "DataApiOnlyThroughAwsMcp",
  "Effect": "Deny",
  "Action": "redshift-data:*",
  "Resource": "*",
  "Condition": { "BoolIfExists": { "aws:ViaAWSMCPService": "false" } }
}

Bedrock Knowledge Bases: One database, listed tables, read-only grants. Inclusions (up to 50 tables) and exclusions narrow the generated SQL but aren’t a substitute for guardrails. A timeout of up to 200 seconds (60 in access-layer/bedrock-knowledge-base/kb-redshift-config.json) cancels the query. Scope the service role to one workgroup (and redshift-serverless:GetCredentials on Amazon Redshift Serverless) with SELECT on included tables only. Allow DescribeStatement, GetStatementResult, and CancelStatement on its own statements only. The sync fails without CancelStatement (access-layer/bedrock-knowledge-base/kb-service-role-policy.json).

Scope every write, and keep a human in the loop where it matters

Give an agent one scoped tool per action, never a generic write tool. A write can run on its own when its tool limits it to one validated, low-impact action, so approvals don’t slow routine work. Consequential, irreversible, or above-threshold writes need a human in the loop:

Mechanism How it works Can the agent bypass it?
Approve as data (recommended for data writes) The tool records a request. A people-only identity approves it No: Redshift enforces it
Approve in the agent The framework asks a person first Depends on the framework
Approve as code review Schema and infrastructure changes ship as reviewed code No: your pipeline enforces it

In the Act pattern, an agent writes to Redshift as part of a workflow, such as issuing a refund. Figure 4 shows how it combines both kinds of write:

  1. The agent’s only write tool, refund_order, runs as an identity that can only EXECUTE one procedure.
  2. The SECURITY DEFINER procedure checks the order and caps the amount at the remaining refundable amount and $500, locking support.refunds against concurrent over-refunds. A failed check writes nothing and returns an error the agent can report.
  3. Refunds up to $100 are approved. Larger ones wait for the refund_approvers role, through a second procedure that rejects self-approval.
  4. A payment process pays approved refunds only.
The Act pattern in four numbered steps. A support agent calls 1, the refund_order tool, which runs as an action identity that can call one procedure and read no tables. 2, the support.refund_order procedure in Redshift, checks the order and caps the amount at what is left and $500. Refunds up to $100 are approved, refunds from $100 to $500 wait for approval, and larger ones are rejected with an error and nothing written. 3, approve_refund, can be run only by refund_approvers, records the approver, and rejects self-approval. 4, the payment process, pays approved refunds only. A band explains that the agent’s identity has no table privileges, so it can’t go around the procedure.

Figure 4: A scoped action tool, with human approval above a threshold

The procedures and grants are in act-refund/refund_procedures.sql. Expose refund_order as its own tool, under an identity separate from the agent’s read path, so the read identity has no write privileges. Scheduled writes that no agent starts, such as a nightly Enrich job that labels tickets with Redshift ML, need a different control. Review the job’s SQL once before you deploy it, then run it under its own identity that no agent can use.

Control plane: Keep infrastructure changes out of agents’ reach

Control plane calls skip database grants, so IAM is the only gate, and an AI assistant’s credentials often reach its shell and the AWS CLI too.

Give every AI assistant a scoped identity. Run Kiro and other AI assistants, and their MCP servers, under an agent-role AWS profile with read-only control plane actions and a permissions boundary. Don’t allow redshift:Get*, which includes GetClusterCredentials. Share the profile through one AWS IAM Identity Center permission set (access-layer/shared/aws-config-example.ini). Role chaining caps sessions at 1 hour.

Deny infrastructure changes through managed MCP servers. With the aws:ViaAWSMCPService condition key, a service control policy can deny create, delete, modify, resize, pause, resume, reboot, restore, and tagging actions made through an agent but not directly (access-layer/shared/deny-infra-changes-through-agents.json). Tagging matters because the data plane deny trusts the agent-access tag.

Data layer: Decide what the agent can reach

The data layer decides what an agent can actually reach, on every query. As the Redshift MCP Server documentation puts it, “The database user’s privileges are the security boundary, not this server.”

Give every agent its own identity: One IAM role, database user, and database role. The IAM role agent-support signs in through the Data API as IAMR:agent-support. On Redshift Serverless, a RedshiftDbRoles tag can grant its database role at sign-in (the aws-samples repository shows the tag). Agents acting for a person can use trusted identity propagation. Grant the role after the user’s first sign-in:

-- One database role per agent: privileges go to the role, never to the user
CREATE ROLE agent_support;
GRANT USAGE ON SCHEMA sales TO ROLE agent_support;
GRANT SELECT ON sales.orders, sales.payments TO ROLE agent_support;
GRANT ROLE agent_support TO "IAMR:agent-support";

Then narrow what it sees (data-layer/fine-grained-access.sql):

  • Row-level security (RLS) filters rows only on tables you attach it to and makes a table default-deny, so attach policies for other roles first.
  • Column privileges remove columns, such as email.
  • Dynamic data masking transforms values, such as a card’s last four digits.

Limit what agents can discover. Turn on metadata security so catalog views and SHOW list only objects the role can access, so agents cannot plan around hidden tables (superusers and ACCESS CATALOG roles see all). Test on non-production first:

ALTER SYSTEM SET metadata_security = true; -- superuser only; applies to the whole cluster or workgroup
GRANT ACCESS CATALOG TO ROLE agent_monitoring; -- a monitoring role that must still see every object

Extend the rules to the lakehouse and other warehouses, so no path skips your filters. Redshift enforces Lake Formation permissions on Iceberg tables, including Amazon S3 Tables (a capability of Amazon S3), and federated permissions carry RLS and masking to an agent warehouse. Plain datashares do not carry RLS filters.

Treat free text as untrusted: Keep Redshift ML instructions in the static PROMPT prefix, and pass row text only as input.

Allow only approved models. The model’s IAM role decides at inference time, so allow bedrock:InvokeModel only on approved model ARNs. By default only superusers can create models. Grant CREATE MODEL to an ML platform role, with ASSUMEROLE ... FOR CREATE MODEL only on approved IAM roles.

Audit each agent’s reach with HAS_TABLE_PRIVILEGE (observe/detection/agent-table-privileges.sql). Until you revoke the default PUBLIC ASSUMEROLE grant, assume every user can use every IAM role attached to the warehouse. The AWS Identity and Access Management Access Analyzer (IAM Access Analyzer) check-access-not-granted check can fail deployments that grant redshift:GetClusterCredentials.

Test as the agent: Superusers and sys:secadmin bypass RLS.

Output guardrails: What the agent sees, keeps, and says

An agent has three outputs: results it reads, copies it keeps, and its answer. Redshift controls what enters the agent and its database-side copies. The application and agent layer govern the rest.

What the agent sees and says

The data layer’s policies double as output guardrails: results hold only permitted rows and readable columns, masked values stay masked, and Lake Formation filters do the same for Iceberg tables.

The sensitive information filtering in Bedrock Guardrails doesn’t inspect toolResult content, so mask in Redshift.

Cap result size too: provisioned clusters can abort on return_row_count, and the Redshift MCP Server’s MAX_RESULT_ROWS (1,000 by default) refuses larger results, protecting the context window, not compute.

Data-layer controls end when results leave Redshift, so check the answer too: Bedrock Guardrails can mask PII and flag ungrounded answers, and the generated SQL makes the answer traceable.

What the agent keeps: Database-side exports and copies

A copy starts with none of the source table’s policies, so treat every database-side write path as an output path:

Export path Guardrail
UNLOAD to Amazon S3 REVOKE ASSUMEROLE ON ALL FROM PUBLIC FOR ALL (which also blocks COPY and CREATE MODEL). Grant ASSUMEROLE ... FOR UNLOAD to pipeline roles only
CETAS and INSERT into external tables Agent roles own no external schemas
Writes to Apache Iceberg tables No Lake Formation write permissions for agent-facing roles
CTAS, SELECT INTO, and INSERT ... SELECT REVOKE CREATE ON SCHEMA public FROM PUBLIC. No CREATE or INSERT for agents
Datashares No datashare privileges for agent roles

The MCP Server’s read-only mode denies UNLOAD and CALL, but other paths bypass it, so privileges are the durable line (data-layer/exports-and-copies.sql). Audit exports in SYS_UNLOAD_HISTORY, SYS_QUERY_HISTORY, and CloudTrail S3 data events.

Figure 5 summarizes which layer controls each output.

Grid of output guardrails by layer for what the agent sees, keeps, and says. Agent layer: optional ApplyGuardrail screening of retrieved text, retained results governed by your application, and Bedrock Guardrails checks on the answer with the SQL kept. Access layer: the MCP Server MAX_RESULT_ROWS cap, and read-only mode that denies UNLOAD and CALL, with one approved write tool and approval above a threshold. Data layer: row-level security, column privileges, masking, and Lake Formation filters, plus ASSUMEROLE scoped for UNLOAD, s3:PutObject only to approved buckets, and no write or datashare privileges for agent roles. Data layer controls end when the result leaves Redshift.

Figure 5: What the agent sees, keeps, and says, and which layer controls each

The cost envelope: Predictable by design

Each agent question has three cost parts (Figure 1): model tokens at Bedrock rates, query compute on Redshift provisioned clusters or Amazon Redshift Serverless RPUs that you already pay for (older RA3 and DC2 clusters also bill lakehouse scans per terabyte), and data at rest. These compute guardrails are part of Redshift, and you pay only for the Redshift compute your queries use:

Guardrail Provisioned Serverless
Separate lane for agents WLM queues by user role Queues by user role, or a dedicated workgroup
Stop a runaway query Query monitoring rules that log, hop, or abort Workgroup query limits or queue rules that log or abort
Cap each statement statement_timeout per agent user Same as provisioned
Cap connections CONNECTION LIMIT and SESSION TIMEOUT per agent user Same as provisioned
Cap capacity Fixed node count. Concurrency scaling limits Max capacity in RPUs
Cap spend per period Concurrency scaling and Spectrum limits RPU-hour limits per day, week, or month

Set three ceilings on day one (Figure 6): a per-statement timeout for each agent user, max capacity on the agent workgroup, and a daily usage limit. Queue rules are checked every 10 seconds, so the timeout is the tighter cap.

Nested boxes showing the cost envelope, from outer to inner: per account (AWS Budgets alerts and AWS Cost Anomaly Detection), per Bedrock principal (cost by IAM principal in CUR 2.0 with activated agent tags, and a budget action that denies the agent’s Bedrock calls), per period (usage limits on RPU-hours), per workload (a dedicated workgroup or queue with max capacity), per query (query limits and statement_timeout), and per LLM call (a trusted tool fixes maxTokens, input size, and row count. Approved models only). The outer two act on billing data and the inner ones in real time. A band lists the three ceilings to set on day one.

Figure 6: The cost envelope. Each ceiling catches what the one inside it misses

Max capacity and the usage limit do different jobs (see Billing for on-demand compute capacity). Max capacity caps RPUs. The usage limit logs, alerts, or stops user queries once the period’s RPU-hours are used (cost-envelope/serverless-ceilings.sh). emit-metric only publishes a metric. Alarm on it, or choose Alert on the AWS Management Console.

Each Serverless workgroup or RG cluster accepts at most 2,000 connections. In our tests, Data API tools opened one connection per call, but an idle open driver transaction kept Serverless compute running until the 1-hour timeout. On Serverless, deny redshift-serverless:GetCredentials when aws:ViaAWSService is false. In our tests, this blocked driver sign-in, not direct Data API calls. On each non-superuser agent user (superusers ignore connection limits), set:

ALTER USER "IAMR:agent-support" SET statement_timeout TO 120000;
ALTER USER "IAMR:agent-support" CONNECTION LIMIT 10 SESSION TIMEOUT 300; -- size the limit to the agent's concurrency

Spend less per answer

  • Reuse work: The result cache, composition for new queries, and materialized views for repeated joins.
  • Right-size the engine. For lake-heavy agents, RG instances cost 30% less per vCPU than RA3, with no per-terabyte scanning charges.
  • Spend and store less. Every byte saved is a byte agents don’t read. For local tables, keep ENCODE AUTO so Redshift selects optimal column compression by data type. Iceberg tables use zstd compression by default, helping reduce storage and scan costs (cost-envelope/storage-compression.sql).

Control model spend per agent

Bedrock attributes inference cost to the calling IAM principal (Introducing granular cost attribution for Amazon Bedrock). Since Redshift ML calls Bedrock with the role in CREATE EXTERNAL MODEL, give each agent its own role. A trusted tool, the only identity allowed to run the model, fixes maxTokens and bounds input (cost-envelope/model-spend-summarizer.sql), and a budget action can deny bedrock:InvokeModel and bedrock:InvokeModelWithResponseStream to the role as a period-level brake, not a real-time stop.

Per-agent users make SYS_QUERY_HISTORY a compute ledger. Tag both ledgers per AGENTCOST05-BP01. Because the Redshift MCP Server in read-only mode refuses SET query_group, attribute by IAM role.

Observe and prove

Security reviewers ask what the agent did, and FinOps reviewers ask what it cost. Per-agent identity answers both.

Figure 7 shows how the following records feed back into tighter guardrails.

A closed loop of five numbered steps. 1, Attribute: agent-* names for agent identities plus listed exceptions, self-reported application names for tracking only, and an estimated cost on a shared workgroup. 2, Audit: SYS_QUERY_HISTORY for what ran, CloudTrail for who and when, and AgentCore and Bedrock logs joined on role and time. 3, Retain: system tables kept in S3 Tables past 7 days, to profile agents across model changes. 4, Review: run as a monitoring identity with sys:monitor, audit IGNORE RLS and tag-granted roles, and alarm on shadow agents and loops. 5, Tighten: give a shadow agent its own identity or block it, stop loops with step and retry limits, and narrow grants and ceilings.

Figure 7: The observe and prove loop

What ran, and who called it. SYS_QUERY_HISTORY lists each agent user’s queries, and an agent- prefix on every agent identity lets one filter cover them. CloudTrail adds who and when. With trusted identity propagation, both identify the person.

Which client ran it. SYS_CONNECTION_LOG records each session’s self-reported application name, which we use for tracking, not as a control. CloudTrail records a shared role’s session name and SourceIdentity, which name a person only when your identity provider sets them or a trust policy condition key requires them (observe/attribution/queries-per-client.sql).

Find shadow agents, which are MCP calls from an identity without the agent- prefix. The application_name setting and the CloudTrail userAgent or userIdentity.invokedBy fields mark MCP traffic (observe/detection/shadow-agents.sql).

Keep a long-term record: System table integration with Amazon S3 Tables keeps system views, normally held 7 days, in read-only Iceberg tables as long as you set. Protect Bedrock invocation logs, which hold unmasked input, with Amazon CloudWatch Logs data protection.

Catch runaway loops with a scheduled check for repeated query shapes and errors (observe/detection/runaway-loops.sql).

Conclusion

In this post, we showed how the layered guardrail model runs agentic analytics on Amazon Redshift, with most controls built into Redshift. The access layer keeps a human in the loop for consequential writes, and the data layer decides what each query can reach.

To get started:

  1. Connect safely. Run the Amazon Redshift MCP Server read-only with a per-agent IAM role, and install redshift-guide from the Agent Toolkit for AWS.
  2. Bound the data. Grant only needed tables and columns, attach RLS and masking, revoke ASSUMEROLE and CREATE from PUBLIC, and test metadata security on non-production first.
  3. Bound the spend. Set a statement timeout, max capacity, and a daily usage limit, and test that the usage-limit alarm fires.

To go further, read Query Amazon Redshift using natural language with Kiro and Integrate Amazon Bedrock with Amazon Redshift ML. The complete SQL and CLI samples from this post are in the aws-samples repository. The repository README lists the prerequisites and the clean-up steps for what the samples create. Leave questions or feedback in the comments.


About the authors

Jason Pedreza

Jason Pedreza

Jason is a Senior Analytics Specialist Solutions Architect at AWS, where he helps customers architect petabyte-scale data platforms for AI-driven analytics. He focuses on modern lakehouse design, large-scale data migrations, and building the data foundations that power ML and generative AI applications.

Dan Jin

Dan Jin

Dan is a Sr. Analytics Specialist Solutions Architect at AWS based out of the San Francisco Bay Area. With over 20 years of experience in database and data analytics products, he has deep expertise in data system architecture and technical solution design. He focuses on helping customers modernize their data warehouses on Amazon Redshift and adopt AI-driven analytics.

Simplify data ingestion from Amazon S3 to Amazon Redshift using auto-copy

Post Syndicated from Jason Pedreza original https://aws.amazon.com/blogs/big-data/simplify-data-ingestion-from-amazon-s3-to-amazon-redshift-using-auto-copy/

Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that makes it simple and cost-effective to analyze your data using standard SQL and your existing business intelligence (BI) tools. Tens of thousands of customers today rely on Amazon Redshift to analyze exabytes of data and run complex analytical queries, making it the most widely used cloud data warehouse.

Data ingestion is the process of getting data to Amazon Redshift. You can leverage one of the many zero-ETL integration methods to make data available in Amazon Redshift directly. However, if your data is in your Amazon S3 bucket, then you can simply load data from Amazon Simple Storage Service (Amazon S3) to Amazon Redshift using the COPY command. A COPY command is the most efficient way to load a table from S3 because it uses the Amazon Redshift’s massively parallel processing (MPP) architecture to read and load data in parallel.

Amazon Redshift launched auto-copy support to simplify data loading from Amazon S3 into Amazon Redshift. You can now setup continuous file ingestion rules to track your Amazon S3 paths and automatically load new files without the need for additional tools or custom solutions. This also enables end users to have the latest data available in Amazon Redshift shortly after the source data is available.

This post shows you how to build automatic file ingestion pipelines in Amazon Redshift when source files are located on Amazon S3 by using a simple SQL command. In addition, we show you how to enable auto-copy using auto-copy jobs, how to monitor jobs, considerations, and best practices.

Overview of the auto-copy feature in Amazon Redshift

The auto-copy feature in Amazon Redshift leverages the S3 event integration to automatically load data into Amazon Redshift and simplifies automatic data loading from Amazon S3 with a simple SQL command. You can enable Amazon Redshift auto-copy by creating auto-copy jobs. A auto-copy job is a database object that stores, automates, and reuses the COPY statement for newly created files that land in the S3 folder.

The following diagram illustrates this process.

S3 event integration and auto-copy jobs have the following benefits:

  • Users can now load data from Amazon S3 automatically without having to build a pipeline or using an external framework
  • auto-copy jobs offer automatic and incremental data ingestion from an Amazon S3 location without the need to implement a custom solution
  • This functionality comes at no additional cost
  • Existing COPY statements can be converted into auto-copy jobs by appending the JOB CREATE <job_name> parameter
  • It keeps track of loaded files and minimizes data duplication.
  • It can be quickly set up using a simple SQL statement using your choice of JDBC/ODBC clients.
  • It has automatic error handling of bad quality data files.
  • It has a mechanism to load-once for each file. This means that there is no need to generate explicit manifest files.

Prerequisites

To get started with auto-copy, you need the following prerequisites:

  • An AWS account
  • An encrypted Amazon Redshift provisioned cluster or Amazon redshift serverless workgroup
  • An Amazon S3 bucket
  • Add following to the Amazon S3 bucket policy
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "Auto-Copy-Policy-01",
                "Effect": "Allow",
                "Principal": {
                    "Service":"redshift.amazonaws.com"
                        
                    
                },
                "Action": [
                    "s3:GetBucketNotification",
                    "s3:PutBucketNotification",
                    "s3:GetBucketLocation"
                ],
                "Resource": "arn:aws:s3:::<<your-s3-bucket-name>>",
                "Condition": {
                    "StringEquals": {
                        "aws:SourceArn": "arn:aws:redshift:<region-name>:<aws-account-id>:integration:*",
                        "aws:SourceAccount": "<aws-account-id>"
                    }
                }
            }
        ]
    }

Set up Amazon S3 event Integration

An Amazon S3 event integration facilitates seamless and automated data ingestion from S3 buckets into an Amazon Redshift data warehouse, streamlining the process of transferring and storing data for analytical purposes

  1. Sign in to the AWS Management Console and Navigate to Amazon Redshift home page. Under Integrations section choose S3 event integrations

  2. Choose Create S3 event integration

  3. Enter Integration name and Description, choose Next

  4. Choose Browse S3 buckets, a dialog box pops up, select the Amazon S3 bucket and choose Continue

  5. Amazon S3 bucket is selected. Choose Next
  6. Choose Browse Redshift data warehouse

  7. Choose the Amazon Redshift data warehouse and choose Continue

  8. Then Amazon Redshift resource policy needs access to S3 event integration. In case of Resource policy error, check Fix it for me and choose Next

  9. Add Tags as required and choose Next

  10. Review changes and choose Create S3 event integration
  11. An S3 event integration is created. Wait until the status of S3 event integration is Active

Set up auto-copy jobs

In this section, we demonstrate how to automate data loading of files from Amazon S3 into Amazon Redshift. With the existing COPY syntax, we add the JOB CREATE parameter to perform a one-time setup for automatic file ingestion. See the following code:

COPY <table-name>
FROM 's3://<s3-object-path>'
[COPY PARAMETERS...]
JOB CREATE <job-name> [AUTO ON | OFF];

Auto ingestion is enabled by default on auto-copy jobs. Files already present at the S3 location will not be visible to the auto-copy job. Only files added after JOB creation are tracked by Amazon Redshift.

Automate ingestion from a single data source

With a auto-copy job, you can automate ingestion from a single data source by creating one job and specifying the path to the S3 objects that contain the data. The S3 object path can reference a set of folders that have the same key prefix.

In this example, we have multiple files that are being loaded on a daily basis containing the sales transactions across all the stores in the US. For this we can create a store_sales folder in the bucket.

The following code creates the store_sales table:

DROP TABLE IF EXISTS public.store_sales;
CREATE TABLE IF NOT EXISTS public.store_sales
(
  ss_sold_date_sk int4,            
  ss_sold_time_sk int4,     
  ss_item_sk int4 not null,      
  ss_customer_sk int4,           
  ss_cdemo_sk int4,              
  ss_hdemo_sk int4,         
  ss_addr_sk int4,               
  ss_store_sk int4,           
  ss_promo_sk int4,           
  ss_ticket_number int8 not null,        
  ss_quantity int4,           
  ss_wholesale_cost numeric(7,2),          
  ss_list_price numeric(7,2),              
  ss_sales_price numeric(7,2),
  ss_ext_discount_amt numeric(7,2),             
  ss_ext_sales_price numeric(7,2),              
  ss_ext_wholesale_cost numeric(7,2),           
  ss_ext_list_price numeric(7,2),               
  ss_ext_tax numeric(7,2),                 
  ss_coupon_amt numeric(7,2), 
  ss_net_paid numeric(7,2),   
  ss_net_paid_inc_tax numeric(7,2),             
  ss_net_profit numeric(7,2),
  primary key (ss_item_sk, ss_ticket_number)
 ) 
DISTKEY (ss_item_sk) 
SORTKEY (ss_sold_date_sk);

Next, we create the auto-copy job to automatically load the gzip-compressed files into the store_sales table:

COPY store_sales
FROM 's3://aws-redshift-s3-auto-copy-demo/store_sales'
IAM_ROLE 'arn:aws:iam::**********:role/Redshift-S3'
gzip delimiter '|' EMPTYASNULL
region 'us-east-1'
JOB CREATE job_store_sales AUTO ON;

Each day’s sales transactions are loaded to their own folder in Amazon S3.

Now upload the files for transaction sold on 2002-12-31. Each folder contains multiple gzip-compressed files.

Since the auto-copy job is already created, it automatically loads the gzip-compressed files located in the S3 object path specified in the COPY command to the store_sales table.

Let’s run a query to get the daily total of sales transactions across all the stores in the US:

SELECT ss_sold_date_sk, count(1)
  FROM store_sales
GROUP BY ss_sold_date_sk;

The output shown comes from the transactions sold on 2002-12-31.

The following day, incremental sales transactions data are loaded to a new folder in the same S3 object path.

As new files arrive to the same S3 object path, the auto-copy job automatically loads the unprocessed files to the store_sales table in an incremental fashion.

All new sales transactions for 2003-01-01 are automatically ingested, which can be verified by running the following query:

SELECT ss_sold_date_sk, count(1)
  FROM store_sales
GROUP BY ss_sold_date_sk;

Automate ingestion from multiple data sources

We can also load an Amazon Redshift table from multiple data sources. When using a pub/sub pattern where multiple S3 buckets populate data to an Amazon Redshift table, you have to maintain multiple data pipelines for each source/target combination. With new parameters in the COPY command, this can be automated to handle data loads efficiently.

In the following example, the Customer_1 folder has Green Cab Company sales data, and the Customer_2 folder has Red Cab Company sales data. We can use the COPY command with the JOB parameter to automate this ingestion process.

The following screenshot shows sample data stored in files. Each folder has similar data but for different customers.

The target for these files in this example is the Amazon Redshift table cab_sales_data.

Define the target table cab_sales_data:

DROP TABLE IF EXISTS cab_sales_data;
CREATE TABLE IF NOT EXISTS cab_sales_data
(
  vendorid                VARCHAR(4),
  pickup_datetime         TIMESTAMP,
  dropoff_datetime        TIMESTAMP,
  store_and_fwd_flag      VARCHAR(1),
  ratecode                INT,
  pickup_longitude        FLOAT4,
  pickup_latitude         FLOAT4,
  dropoff_longitude       FLOAT4,
  dropoff_latitude        FLOAT4,
  passenger_count         INT,
  trip_distance           FLOAT4,
  fare_amount             FLOAT4,
  extra                   FLOAT4,
  mta_tax                 FLOAT4,
  tip_amount              FLOAT4,
  tolls_amount            FLOAT4,
  ehail_fee               FLOAT4,
  improvement_surcharge   FLOAT4,
  total_amount            FLOAT4,
  payment_type            VARCHAR(4),
  trip_type               VARCHAR(4)
)
DISTSTYLE EVEN
SORTKEY (passenger_count,pickup_datetime);

You can define two auto-copy jobs as shown in the following code to handle and monitor the ingestion of sales data belonging to different customers, in our case Customer_1 and Customer_2. These jobs monitor the Customer_1 and Customer_2 folders and load new files that are added here.

COPY public.cab_sales_data
FROM 's3://aws-redshift-s3-auto-copy-demo/Customer_1'
IAM_ROLE 'arn:aws:iam::**********:role/Redshift-S3'
DATEFORMAT 'auto'
IGNOREHEADER 1
DELIMITER ','
IGNOREBLANKLINES
REGION 'us-east-1'
JOB CREATE job_green_cab AUTO ON;

COPY public.cab_sales_data
FROM 's3:// aws-redshift-s3-auto-copy-demo/Customer_2'
IAM_ROLE 'arn:aws:iam::**********:role/Redshift-S3'
DATEFORMAT 'auto'
IGNOREHEADER 1
DELIMITER ','
IGNOREBLANKLINES
REGION 'us-east-1'
JOB CREATE job_red_cab AUTO ON;

After setting up the two jobs, we can upload the relevant files into their respective folders. This will make sure that the data is loaded efficiently as soon as the files arrive. Each customer is assigned its own vendorid, as shown in the following output:

SELECT vendorid,
       sum(passenger_count) as total_passengers 
  FROM cab_sales_data
GROUP BY vendorid;

result 1

Manually run a auto-copy job

There might be scenarios wherein the auto-copy job needs to be paused, meaning it needs to stop looking for new files, for example, to fix a corrupted data pipeline at the data source.

In that case, either use the auto-copy job ALTER command to set AUTO to OFF or create a new auto-copy job with AUTO OFF. Once this is set, auto copy will no longer look for new files.

If necessary, users can manually invoke auto-copy job which will do the work and ingest if new files are found.

auto-copy job RUN <auto-copy job Name>

You can disable “AUTO ON” in the existing auto-copy job using the following command:

auto-copy job ALTER <auto-copy job Name> AUTO OFF

The following table compares the syntax and data duplication between a regular copy statement and the new auto-copy job

. Copy Auto-copy job
Syntax COPY <table-name>
FROM 's3://<s3-object-path>'
[COPY PARAMETERS...]
COPY <table-name>
FROM 's3://<s3-object-path>'
[COPY PARAMETERS...]
JOB CREATE <job-name>;
Data Duplication If it is run multiple times against the same S3 folder, it will load the data again, resulting in data duplication. It will not load the same file twice, preventing data duplication.

Error handling and monitoring for auto-copy jobs

auto-copy jobs continuously monitor the S3 folder specified during job creation and perform ingestion whenever new files are created. New files created under the S3 folder are loaded exactly once to avoid data duplication.

By default, if there are data or format issues with the specific files, the auto-copy job will fail to ingest the files with a load error and log details to the system tables. The auto-copy job will remain AUTO ON with new data files and will continue to ignore previously failed files.

Amazon Redshift provides the following system tables for users to monitor or troubleshoot auto-copy jobs as needed:

  • List auto-copy jobs – Use SYS_COPY_JOB to list the auto-copy jobs stored in the database:
SELECT * 
  FROM sys_copy_job;

  • Get a summary of a auto-copy job – Use the SYS_LOAD_HISTORY view to get the aggregate metrics of a auto-copy job operation by specifying the copy_job_id. It shows the aggregate metrics of the files that have been processed by a auto-copy job.
SELECT *
  FROM sys_load_history
 WHERE copy_job_id = 274978;

  • Get details of a auto-copy job – Use STL_LOAD_COMMITS to get the status and details of each file that was processed by a auto-copy job:
SELECT *
  FROM stl_load_commits
 WHERE copy_job_id = 274978
ORDER BY curtime ASC;

  • Get exception details of a auto-copy job – Use STL_LOAD_ERRORS to get the details of files that failed to ingest from a auto-copy job:
SELECT   query,
    slice,
    starttime , 
    filename,
    line_number,
    colname,
    type,
    err_code,
    err_reason,
    copy_job_id,
    raw_line,
    raw_field_value
  FROM stl_load_errors
 WHERE copy_job_id = 274978;

Auto-copy job best practices

In an auto-copy job, when a new file is detected and ingested (automatically or manually), Amazon Redshift stores the file name and doesn’t run this specific job when a new file is created with the same file name.

The following are the recommended best practices when working with files using the auto-copy job:

  • Use unique file names for each file in a auto-copy job (for example, 2022-10-15-batch-1.csv). However, you can use the same file name as long as it’s from different auto-copy jobs:
    • job_customerA_sales – s3://redshift-blogs/sales/customerA/2022-10-15-sales.csv
    • job_customerB_sales – s3://redshift-blogs/sales/customerB/2022-10-15-sales.csv
  • Do not update file contents. Do not overwrite existing files. Changes in existing files will not be reflected to the target table. The auto-copy job doesn’t pick up updated or overwritten files, so make sure they’re renamed as new file names for the auto-copy job to pick up.
  • Run regular COPY statements (not a job) if you need to ingest a file that was already processed by your auto-copy job. (COPY statement without a JOB CREATE syntax doesn’t track loaded files.) For example, this is helpful in scenarios where you don’t have control of the file name and the initial file received failed. The following figure shows a typical workflow in this case.

  • Delete and recreate your auto-copy job if you want to reset file tracking history and start over. You can drop auto-copy job using following command.
    auto-copy job DROP <auto-copy job Name>

auto-copy job considerations

Here are the main things to consider when using auto-copy:

For additional details on other considerations for auto-copy, refer to the AWS documentation.

Customer feedback

GE Aerospace is a global provider of jet engines, components, and systems for commercial and military aircraft. The company has been designing, developing, and manufacturing jet engines since World War I.

“GE Aerospace uses AWS analytics and Amazon Redshift to enable critical business insights that drive important business decisions. With the support for auto-copy from Amazon S3, we can build simpler data pipelines to move data from Amazon S3 to Amazon Redshift. This accelerates our data product teams’ ability to access data and deliver insights to end users. We spend more time adding value through data and less time on integrations.”

– Alcuin Weidus Sr Principal Data Architect at GE Aerospace

Conclusion

This post demonstrated how to automate data ingestion from Amazon S3 to Amazon Redshift using the auto-copy feature. This new functionality helps make Amazon Redshift data ingestion easier than ever, and will allow SQL users to get access to the most recent data using a simple SQL command.

Users can begin ingesting data to Redshift from Amazon S3 with simple SQL commands and gain access to the most up-to-date data without the need for third-party tools or custom implementation.


About the authors

Tahir Aziz is an Analytics Solution Architect at AWS. He has worked with building data warehouses and big data solutions for over 15+ years. He loves to help customers design end-to-end analytics solutions on AWS. Outside of work, he enjoys traveling and cooking.

Omama Khurshid is an Acceleration Lab Solutions Architect at Amazon Web Services. She focuses on helping customers across various industries build reliable, scalable, and efficient solutions. Outside of work, she enjoys spending time with her family, watching movies, listening to music, and learning new technologies.

Raza Hafeez is a Senior Product Manager at Amazon Redshift. He has over 13 years of professional experience building and optimizing enterprise data warehouses and is passionate about enabling customers to realize the power of their data. He specializes in migrating enterprise data warehouses to AWS Modern Data Architecture.

Jason Pedreza is an Analytics Specialist Solutions Architect at AWS with data warehousing experience handling petabytes of data. Prior to AWS, he built data warehouse solutions at Amazon.com. He specializes in Amazon Redshift and helps customers build scalable analytic solutions.

Nita Shah is an Analytics Specialist Solutions Architect at AWS based out of New York. She has been building data warehouse solutions for over 20 years and specializes in Amazon Redshift. She is focused on helping customers design and build enterprise-scale well-architected analytics and decision support platforms.

Eren Baydemir, a Technical Product Manager at AWS, has 15 years of experience in building customer-facing products and is currently focusing on data lake and file ingestion topics in the Amazon Redshift team. He was the CEO and co-founder of DataRow, which was acquired by Amazon in 2020.

Eesha Kumar is an Analytics Solutions Architect with AWS. He works with customers to realize the business value of data by helping them build solutions using the AWS platform and tools.

Satish Sathiya is a Senior Product Engineer at Amazon Redshift. He is an avid big data enthusiast who collaborates with customers around the globe to achieve success and meet their data warehousing and data lake architecture needs.

 Hangjian Yuan is a Software Development Engineer at Amazon Redshift. He’s passionate about analytical databases and focuses on delivering cutting-edge streaming experiences for customers.

ETL orchestration using the Amazon Redshift Data API and AWS Step Functions with AWS SDK integration

Post Syndicated from Jason Pedreza original https://aws.amazon.com/blogs/big-data/etl-orchestration-using-the-amazon-redshift-data-api-and-aws-step-functions-with-aws-sdk-integration/

Extract, transform, and load (ETL) serverless orchestration architecture applications are becoming popular with many customers. These applications offers greater extensibility and simplicity, making it easier to maintain and simplify ETL pipelines. A primary benefit of this architecture is that we simplify an existing ETL pipeline with AWS Step Functions and directly call the Amazon Redshift Data API from the state machine. As a result, the complexity for the ETL pipeline is reduced.

As a data engineer or an application developer, you may want to interact with Amazon Redshift to load or query data with a simple API endpoint without having to manage persistent connections. The Amazon Redshift Data API allows you to interact with Amazon Redshift without having to configure JDBC or ODBC connections. This feature allows you to orchestrate serverless data processing workflows, design event-driven web applications, and run an ETL pipeline asynchronously to ingest and process data in Amazon Redshift, with the use of Step Functions to orchestrate the entire ETL or ELT workflow.

This post explains how to use Step Functions and the Amazon Redshift Data API to orchestrate the different steps in your ETL or ELT workflow and process data into an Amazon Redshift data warehouse.

AWS Lambda is typically used with Step Functions due to its flexible and scalable compute benefits. An ETL workflow has multiple steps, and the complexity may vary within each step. However, there is an alternative approach with AWS SDK service integrations, a feature of Step Functions. These integrations allow you to call over 200 AWS services’ API actions directly from your state machine. This approach is optimal for steps with relatively low complexity compared to using Lambda because you no longer have to maintain and test function code. Lambda functions have a maximum timeout of 15 minutes; if you need to wait for longer-running processes, Step Functions standard workflows allows a maximum runtime of 1 year.

You can replace steps that include a single process with a direct integration between Step Functions and AWS SDK service integrations without using Lambda. For example, if a step is only used to call a Lambda function that runs a SQL statement in Amazon Redshift, you may remove the Lambda function with a direct integration to the Amazon Redshift Data API’s SDK API action. You can also decouple Lambda functions with multiple actions into multiple steps. An implementation of this is available later in this post.

We created an example use case in the GitHub repo ETL Orchestration using Amazon Redshift Data API and AWS Step Functions that provides an AWS CloudFormation template for setup, SQL scripts, and a state machine definition. The state machine directly reads SQL scripts stored in your Amazon Simple Storage Service (Amazon S3) bucket, runs them in your Amazon Redshift cluster, and performs an ETL workflow. We don’t use Lambda in this use case.

Solution overview

In this scenario, we simplify an existing ETL pipeline that uses Lambda to call the Data API. AWS SDK service integrations with Step Functions allow you to directly call the Data API from the state machine, reducing the complexity in running the ETL pipeline.

The entire workflow performs the following steps:

  1. Set up the required database objects and generate a set of sample data to be processed.
  2. Run two dimension jobs that perform SCD1 and SCD2 dimension load, respectively.
  3. When both jobs have run successfully, the load job for the fact table runs.
  4. The state machine performs a validation to ensure the sales data was loaded successfully.

The following architecture diagram highlights the end-to-end solution:

We run the state machine via the Step Functions console, but you can run this solution in several ways:

You can deploy the solution with the provided CloudFormation template, which creates the following resources:

  • Database objects in the Amazon Redshift cluster:
    • Four stored procedures:
      • sp_setup_sales_data_pipeline() – Creates the tables and populates them with sample data
      • sp_load_dim_customer_address() – Runs the SCD1 process on customer_address records
      • sp_load_dim_item() – Runs the SCD2 process on item records
      • sp_load_fact_sales (p_run_date date) – Processes sales from all stores for a given day
    • Five Amazon Redshift tables:
      • customer
      • customer_address
      • date_dim
      • item
      • store_sales
  • The AWS Identity and Access Management (IAM) role StateMachineExecutionRole for Step Functions to allow the following permissions:
    • Federate to the Amazon Redshift cluster through getClusterCredentials permission avoiding password credentials
    • Run queries in the Amazon Redshift cluster through Data API calls
    • List and retrieve objects from Amazon S3
  • The Step Functions state machine RedshiftETLStepFunction, which contains the steps used to run the ETL workflow of the sample sales data pipeline

Prerequisites

As a prerequisite for deploying the solution, you need to set up an Amazon Redshift cluster and associate it with an IAM role. For more information, see Authorizing Amazon Redshift to access other AWS services on your behalf. If you don’t have a cluster provisioned in your AWS account, refer to Getting started with Amazon Redshift for instructions to set it up.

When the Amazon Redshift cluster is available, perform the following steps:

  1. Download and save the CloudFormation template to a local folder on your computer.
  2. Download and save the following SQL scripts to a local folder on your computer:
    1. sp_statements.sql – Contains the stored procedures including DDL and DML operations.
    2. validate_sql_statement.sql – Contains two validation queries you can run.
  3. Upload the SQL scripts to your S3 bucket. The bucket name is the designated S3 bucket specified in the ETLScriptS3Path input parameter.
  4. On the AWS CloudFormation console, choose Create stack with new resources and upload the template file you downloaded in the previous step (etl-orchestration-with-stepfunctions-and-redshift-data-api.yaml).
  5. Enter the required parameters and choose Next.
  6. Choose Next until you get to the Review page and select the acknowledgement check box.
  7. Choose Create stack.
  8. Wait until the stack deploys successfully.

When the stack is complete, you can view the outputs, as shown in the following screenshot:

Run the ETL orchestration

After you deploy the CloudFormation template, navigate to the stack detail page. On the Resources tab, choose the link for RedshiftETLStepFunction to be redirected to the Step Functions console.

The RedshiftETLStepFunction state machine runs automatically, as outlined in the following workflow:

  1. read_sp_statement and run_sp_deploy_redshift – Performs the following actions:
    1. Retrieves the sp_statements.sql from Amazon S3 to get the stored procedure.
    2. Passes the stored procedure to the batch-execute-statement API to run in the Amazon Redshift cluster.
    3. Sends back the identifier of the SQL statement to the state machine.
  2. wait_on_sp_deploy_redshift – Waits for at least 5 seconds.
  3. run_sp_deploy_redshift_status_check – Invokes the Data API’s describeStatement to get the status of the API call.
  4. is_run_sp_deploy_complete – Routes the next step of the ETL workflow depending on its status:
    1. FINISHED – Stored procedures are created in your Amazon Redshift cluster.
    2. FAILED – Go to the sales_data_pipeline_failure step and fail the ETL workflow.
    3. All other status – Go back to the wait_on_sp_deploy_redshift step to wait for the SQL statements to finish.
  5. setup_sales_data_pipeline – Performs the following steps:
    1. Initiates the setup stored procedure that was previously created in the Amazon Redshift cluster.
    2. Sends back the identifier of the SQL statement to the state machine.
  6. wait_on_setup_sales_data_pipeline – Waits for at least 5 seconds.
  7. setup_sales_data_pipeline_status_check – Invokes the Data API’s describeStatement to get the status of the API call.
  8. is_setup_sales_data_pipeline_complete – Routes the next step of the ETL workflow depending on its status:
    1. FINISHED – Created two dimension tables (customer_address and item) and one fact table (sales).
    2. FAILED – Go to the sales_data_pipeline_failure step and fail the ETL workflow.
    3. All other status – Go back to the wait_on_setup_sales_data_pipeline step to wait for the SQL statements to finish.
  9. run_sales_data_pipeline – LoadItemTable and LoadCustomerAddressTable are two parallel workflows that Step Functions runs at the same time. The workflows run the stored procedures that were previously created. The stored procedure loads the data into the item and customer_address tables. All other steps in the parallel sessions follow the same concept as described previously. When both parallel workflows are complete, run_load_fact_sales runs.
  10. run_load_fact_sales – Inserts data into the store_sales table that was created in the initial stored procedure.
  11. Validation – When all the ETL steps are complete, the state machine reads a second SQL file from Amazon S3 (validate_sql_statement.sql) and runs the two SQL statements using the batch_execute_statement method.

The implementation of the ETL workflow is idempotent. If it fails, you can retry the job without any cleanup. For example, it recreates the stg_store_sales table each time, then deletes the target table store_sales with the data for the particular refresh date each time.

The following diagram illustrates the state machine workflow:

In this example, we use the task state resource arn:aws:states:::aws-sdk:redshiftdata:[apiAction] to call the corresponding Data API action. The following table summarizes the Data API actions and their corresponding AWS SDK integration API actions.

Amazon Redshift Data API Actions AWS SDK Integrations API Actions
BatchExecuteStatement batchExecuteStatement
ExecuteStatement executeStatement
DescribeStatement describeStatement
CancelStatement cancelStatement
GetStatementResult getStatementResult
DescribeTable describeTable
ListDatabases listDatabases
ListSchemas listSchemas
ListStatements listStatements
ListTables listTables

To use AWS SDK integrations, you specify the service name and API call, and, optionally, a service integration pattern. The AWS SDK action is always camel case, and parameter names are Pascal case. For example, you can use the Step Functions action batchExecuteStatement to run multiple SQL statements in a batch as a part of a single transaction on the Data API. The SQL statements can be SELECT, DML, DDL, COPY, and UNLOAD.

Validate the ETL orchestration

The entire ETL workflow takes approximately 1 minute to run. The following screenshot shows that the ETL workflow completed successfully.

When the entire sales data pipeline is complete, you may go through the entire execution event history, as shown in the following screenshot.

Schedule the ETL orchestration

After you validate the sales data pipeline, you may opt to run the data pipeline on a daily schedule. You can accomplish this with Amazon EventBridge.

  1. On the EventBridge console, create a rule to run the RedshiftETLStepFunction state machine daily.
  2. To invoke the RedshiftETLStepFunction state machine on a schedule, choose Schedule and define the appropriate frequency needed to run the sales data pipeline.
  3. Specify the target state machine as RedshiftETLStepFunction and choose Create.

You can confirm the schedule on the rule details page.

Clean up

Clean up the resources created by the CloudFormation template to avoid unnecessary cost to your AWS account. You can delete the CloudFormation stack by selecting the stack on the AWS CloudFormation console and choosing Delete. This action deletes all the resources it provisioned. If you manually updated a template-provisioned resource, you may see some issues during cleanup; you need to clean these up independently.

Limitations

The Data API and Step Functions AWS SDK integration offers a robust mechanism to build highly distributed ETL applications within minimal developer overhead. Consider the following limitations when using the Data API and Step Functions:

Conclusion

In this post, we demonstrated how to build an ETL orchestration using the Amazon Redshift Data API and Step Functions with AWS SDK integration.

To learn more about the Data API, see Using the Amazon Redshift Data API to interact with Amazon Redshift clusters and Using the Amazon Redshift Data API.


About the Authors

Jason Pedreza is an Analytics Specialist Solutions Architect at AWS with over 13 years of data warehousing experience. Prior to AWS, he built data warehouse solutions at Amazon.com. He specializes in Amazon Redshift and helps customers build scalable analytic solutions.

Bipin Pandey is a Data Architect at AWS. He loves to build data lake and analytics platforms for his customers. He is passionate about automating and simplifying customer problems with the use of cloud solutions.

David Zhang is an AWS Solutions Architect who helps customers design robust, scalable, and data-driven solutions across multiple industries. With a background in software development, David is an active leader and contributor to AWS open-source initiatives. He is passionate about solving real-world business problems and continuously strives to work from the customer’s perspective. Feel free to connect with him on LinkedIn.