<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: kienly</title>
    <description>The latest articles on DEV Community by kienly (@kienly).</description>
    <link>https://dev.to/kienly</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F323027%2Fbe90005a-6511-4655-a9af-35ca9cdc4562.png</url>
      <title>DEV Community: kienly</title>
      <link>https://dev.to/kienly</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/kienly"/>
    <language>en</language>
    <item>
      <title>From ETL Jobs to Agentic Orchestrators</title>
      <dc:creator>kienly</dc:creator>
      <pubDate>Sun, 08 Nov 2026 05:42:29 +0000</pubDate>
      <link>https://dev.to/aws-builders/from-etl-jobs-to-agentic-orchestrators-3332</link>
      <guid>https://dev.to/aws-builders/from-etl-jobs-to-agentic-orchestrators-3332</guid>
      <description>&lt;p&gt;I have spent most of my career on the boring, important part of data work: the pipelines that run at 2 a.m. and that nobody thinks about until they break. And I have always liked pipelines for one simple reason. They do not improvise. Give a workflow engine a schedule, a schema, and a known sequence of steps, and it will run the same plan every single day, without getting tired or distracted or "creative."&lt;/p&gt;

&lt;p&gt;That predictability is the whole point. It is also the reason I was skeptical when people started talking about putting AI agents in charge of data operations.&lt;/p&gt;

&lt;p&gt;This post is about where I landed. Short version: an agent is a great front door to a pipeline, but it should never be the thing that decides how the pipeline runs. The pipeline should keep that job. Here is how to set it up that way with AWS Step Functions and Amazon Bedrock AgentCore Gateway.&lt;/p&gt;




&lt;h2&gt;
  
  
  Why the boring part is still the hard part
&lt;/h2&gt;

&lt;p&gt;Pipelines are predictable on paper. In practice, the operational work around them is anything but.&lt;/p&gt;

&lt;p&gt;Late files arrive in Amazon S3 hours after the batch window closed. An API throttles during a busy week. Someone in finance messages the on-call channel: "Can you re-run yesterday's partition? We fixed a reference table." The pipeline itself is automated, but the control plane around it is still manual. Someone reads the message, checks the S3 key, opens the console or the CLI, builds a JSON payload, starts the run, watches it, and reports back.&lt;/p&gt;

&lt;p&gt;It is repetitive, low-risk work that eats time. So it is natural to want an AI agent to handle it.&lt;/p&gt;

&lt;p&gt;The trap is what that usually looks like. The first instinct is to hand the agent the keys to the cloud. Give it permission to call &lt;code&gt;glue:StartJobRun&lt;/code&gt;, &lt;code&gt;redshift-data:ExecuteStatement&lt;/code&gt;, &lt;code&gt;lambda:InvokeFunction&lt;/code&gt;, and &lt;code&gt;s3:CopyObject&lt;/code&gt;, and you have quietly asked a language model to act as a distributed transaction coordinator. I would not trust a person to do that job from a chat window at midnight, and I do not think a model should either. If the agent gets one parameter wrong on step three of a five-step load, or times out while a Spark cluster is starting, or retries a load that was not idempotent, you are left with a half-written table and no single place that says what happened.&lt;/p&gt;

&lt;h2&gt;
  
  
  A better split: the agent asks, the workflow does
&lt;/h2&gt;

&lt;p&gt;AWS recently made &lt;a href="https://aws.amazon.com/about-aws/whats-new/2026/10/step-functions-workflows-ai-agent-tools/" rel="noopener noreferrer"&gt;Step Functions workflows available as tools for AI agents&lt;/a&gt; through Amazon Bedrock AgentCore Gateway, using the Model Context Protocol (MCP). It was announced alongside OpenAI GPT-6.1 Terra on Bedrock, which is covered in the &lt;a href="https://aws.amazon.com/blogs/aws/aws-weekly-roundup-gpt-6-1-terra-on-bedrock-step-functions-as-agent-tools-and-more-november-2-2026/" rel="noopener noreferrer"&gt;AWS Weekly Roundup&lt;/a&gt;. The useful part for us: you can expose a workflow you already trust as a set of MCP tools, without writing a wrapper Lambda or any glue code.&lt;/p&gt;

&lt;p&gt;The design rule I keep coming back to is this:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;The agent decides when to act and with what input. Step Functions decides how the workflow runs.&lt;/strong&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;Everything else in this post follows from that split.&lt;/p&gt;




&lt;h2&gt;
  
  
  1. The architecture, in one picture
&lt;/h2&gt;

&lt;p&gt;First, a quick note on MCP. It is an open protocol, built on JSON-RPC, that lets an AI application discover tools (&lt;code&gt;tools/list&lt;/code&gt;) and call them (&lt;code&gt;tools/call&lt;/code&gt;). The appeal is that you expose a tool server once, and any compliant agent can use it, without custom adapters for every model or framework.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://docs.aws.amazon.com/bedrock-agentcore/latest/devguide/gateway-target-connector-step-functions.html" rel="noopener noreferrer"&gt;Amazon Bedrock AgentCore Gateway&lt;/a&gt; acts as that managed MCP boundary. It ships with a built-in Step Functions connector, &lt;code&gt;connectorId: "states"&lt;/code&gt;. When you register a Step Functions target, the Gateway generates MCP-compliant schemas for the state machine operations you choose, and it calls Step Functions with its own IAM service role.&lt;/p&gt;

&lt;p&gt;Here is the whole flow:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4r0pexcxpip81pn3968p.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4r0pexcxpip81pn3968p.png" alt=" " width="800" height="502"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Each layer has a narrow job:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;The operator and the agent.&lt;/strong&gt; You ask in plain language. The agent, running on a Bedrock model such as GPT-6.1 Terra, turns that into structured parameters, picks the right tool, checks the status, and tells you what happened. It is good at the translation. It is not in charge of the run.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;AgentCore Gateway.&lt;/strong&gt; This is the protocol and security boundary. It exposes the Step Functions operations you selected, generates their input schemas, injects the values you pinned (such as &lt;code&gt;stateMachineArn&lt;/code&gt;), and authenticates with a tightly scoped role.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Step Functions.&lt;/strong&gt; This is where the real work is decided: state transitions, payload validation, error handling, retries with backoff, and failure notifications. The workflow is the source of truth for what happens next. Step Functions also integrates with a long list of AWS services, so the orchestration logic does not have to be rewritten to become agent-accessible.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;The data services.&lt;/strong&gt; S3, Glue, DynamoDB and SNS do the heavy lifting: storage, Spark processing, audit records, and alerts. They only accept requests from the Step Functions execution role. The agent never talks to them directly.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Why this beats giving the agent direct access
&lt;/h3&gt;

&lt;p&gt;Compare that with handing an agent tool definitions for &lt;code&gt;s3:HeadObject&lt;/code&gt;, &lt;code&gt;glue:StartJobRun&lt;/code&gt;, &lt;code&gt;redshift-data:ExecuteStatement&lt;/code&gt;, and &lt;code&gt;sns:Publish&lt;/code&gt;. In that setup, the plan for the pipeline lives inside the model's context window. If the agent process dies while a 20-minute Glue job is running, nothing starts the downstream load or sends the failure alert. An agent with direct Glue or warehouse permissions could also be talked into running something it should not, through prompt injection hidden in a ticket or a filename.&lt;/p&gt;

&lt;p&gt;With Step Functions in the middle, the agent's reach shrinks to one entry point: the workflow. It cannot reorder the steps, skip validation, turn off alerts, or quietly widen its own permissions.&lt;/p&gt;




&lt;h2&gt;
  
  
  2. The scenario: a corrected sales extract at an odd hour
&lt;/h2&gt;

&lt;p&gt;Let's make this concrete. An upstream e-commerce platform uploads a corrected daily sales file to S3, well outside the midnight batch. An engineer (or a Slack ops bot) asks the agent:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;em&gt;"Start the sales ingestion pipeline for &lt;code&gt;raw/sales/2026-11-05/sales.parquet&lt;/code&gt; and tell me if it succeeds."&lt;/em&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;No shell scripts, no console clicking. The agent works through four steps, using the &lt;a href="https://docs.aws.amazon.com/step-functions/latest/dg/tutorial-agentcore-gateway.html" rel="noopener noreferrer"&gt;AWS Step Functions and AgentCore Gateway integration&lt;/a&gt;:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Discover the tools and pull out the input.&lt;/strong&gt; The agent lists the MCP tools on the Gateway target, finds &lt;code&gt;StartExecution&lt;/code&gt;, and puts &lt;code&gt;raw/sales/2026-11-05/sales.parquet&lt;/code&gt; into the &lt;code&gt;sourceKey&lt;/code&gt; field. It never needs the state machine ARN, because that is pinned in the Gateway configuration.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Run the workflow.&lt;/strong&gt; The Gateway calls &lt;code&gt;states:StartExecution&lt;/code&gt; on the pinned Standard workflow. The run goes through these states:

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;ValidateInput&lt;/code&gt;: a &lt;code&gt;Choice&lt;/code&gt; state that checks that &lt;code&gt;sourceKey&lt;/code&gt; is present and matches &lt;code&gt;raw/sales/*.parquet&lt;/code&gt; before any compute is started.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;StartGlueJob&lt;/code&gt;: a synchronous task (&lt;code&gt;arn:aws:states:::glue:startJobRun.sync&lt;/code&gt;) that starts the PySpark job with the source key and waits for it to finish. Transient failures are retried with exponential backoff.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;RecordSuccess&lt;/code&gt;: writes an audit record (execution ARN, source key, completion time, status) to a DynamoDB table.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;NotifyFailure&lt;/code&gt; and &lt;code&gt;WorkflowFailed&lt;/code&gt;: if validation or the Glue job fails after its retries, a &lt;code&gt;Catch&lt;/code&gt; routes to an SNS notification and ends in an explicit &lt;code&gt;Fail&lt;/code&gt; state.&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Watch it.&lt;/strong&gt; When &lt;code&gt;StartExecution&lt;/code&gt; returns an &lt;code&gt;executionArn&lt;/code&gt;, the agent calls &lt;code&gt;DescribeExecution&lt;/code&gt; until the status leaves &lt;code&gt;RUNNING&lt;/code&gt;. If something fails, or you want the details, it calls &lt;code&gt;GetExecutionHistory&lt;/code&gt; and points to the exact state where things went wrong.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Summarize.&lt;/strong&gt; Once the run is finished, the agent turns the status, duration, and audit record into a short, readable answer.&lt;/li&gt;
&lt;/ol&gt;

&lt;h3&gt;
  
  
  Why Standard workflows, not Express
&lt;/h3&gt;

&lt;p&gt;Step Functions has two workflow types. For agent-driven pipelines, I would use &lt;strong&gt;Standard&lt;/strong&gt; every time, for two reasons:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Runs can be long.&lt;/strong&gt; Glue and Redshift jobs often take longer than the five-minute limit on Express workflows. Standard workflows can run for up to a year.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;You need the history.&lt;/strong&gt; Standard workflows keep a full execution history that &lt;code&gt;DescribeExecution&lt;/code&gt; and &lt;code&gt;GetExecutionHistory&lt;/code&gt; can query. Express workflows send logs to CloudWatch asynchronously and do not support these calls in the same way, which is a problem when the agent needs to answer "what happened?"&lt;/li&gt;
&lt;/ul&gt;




&lt;h2&gt;
  
  
  3. The implementation, piece by piece
&lt;/h2&gt;

&lt;p&gt;The repository is deployed with AWS SAM (Serverless Application Model). The full, copy-paste version lives in the &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator" rel="noopener noreferrer"&gt;demo repository&lt;/a&gt;. Four design choices took me the most time to get right, and they are the ones I would look at first.&lt;/p&gt;

&lt;h3&gt;
  
  
  1. The state machine: validate before you spend money
&lt;/h3&gt;

&lt;p&gt;The first defense against a bad agent call is an explicit &lt;code&gt;ValidateInput&lt;/code&gt; state in the Amazon States Language definition (&lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/state-machine/data-pipeline.asl.json" rel="noopener noreferrer"&gt;&lt;code&gt;state-machine/data-pipeline.asl.json&lt;/code&gt;&lt;/a&gt;). &lt;code&gt;StartExecution&lt;/code&gt; takes its &lt;code&gt;input&lt;/code&gt; as a serialized JSON string, and the Gateway's schema cannot check the contents of that string. So the workflow has to. The &lt;code&gt;Choice&lt;/code&gt; state below rejects bad input right away, which costs next to nothing compared with starting a Glue job:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="nl"&gt;"ValidateInput"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Type"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"Choice"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Comment"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"Ensure sourceKey is provided and targets the raw/sales/ prefix with a .parquet extension"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Choices"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="nl"&gt;"And"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"Variable"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"$.sourceKey"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"IsPresent"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="kc"&gt;true&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"Variable"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"$.sourceKey"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"IsString"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="kc"&gt;true&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"Variable"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"$.sourceKey"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt;
          &lt;/span&gt;&lt;span class="nl"&gt;"StringMatches"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"raw/sales/*.parquet"&lt;/span&gt;&lt;span class="w"&gt;
        &lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="nl"&gt;"Next"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"StartGlueJob"&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Default"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"FormatValidationFailure"&lt;/span&gt;&lt;span class="w"&gt;
&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="w"&gt;
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;StartGlueJob&lt;/code&gt; uses the &lt;code&gt;.sync&lt;/code&gt; integration pattern (&lt;code&gt;arn:aws:states:::glue:startJobRun.sync&lt;/code&gt;). Step Functions starts the job, waits, and only moves to &lt;code&gt;RecordSuccess&lt;/code&gt; once Spark has finished. You do not write any polling code.&lt;/p&gt;

&lt;p&gt;One detail that has bitten people, including me in early drafts: if you route the error path to &lt;code&gt;NotifyFailure&lt;/code&gt; and that state ends with &lt;code&gt;"End": true&lt;/code&gt;, Step Functions considers the error handled and marks the whole execution &lt;code&gt;SUCCEEDED&lt;/code&gt;. The agent then reports a success that never happened. The fix is to send the error path to a &lt;code&gt;"Type": "Fail"&lt;/code&gt; state.&lt;/p&gt;

&lt;h3&gt;
  
  
  2. The Gateway target: pin what the agent should never choose
&lt;/h3&gt;

&lt;p&gt;The Gateway target is defined in &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/gateway/target.json" rel="noopener noreferrer"&gt;&lt;code&gt;gateway/target.json&lt;/code&gt;&lt;/a&gt;. It sets &lt;code&gt;"connectorId": "states"&lt;/code&gt; and lists the operations to expose. The important part is pinning:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;State-machine operations&lt;/strong&gt; (&lt;code&gt;StartExecution&lt;/code&gt;, &lt;code&gt;ListExecutions&lt;/code&gt;, &lt;code&gt;DescribeStateMachine&lt;/code&gt;) work on a &lt;code&gt;stateMachineArn&lt;/code&gt;. We pin that value in the target. The Gateway removes the parameter from the schema the agent sees and fills it in at call time. The agent has no way to pick a different state machine.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Execution operations&lt;/strong&gt; (&lt;code&gt;DescribeExecution&lt;/code&gt;, &lt;code&gt;GetExecutionHistory&lt;/code&gt;, &lt;code&gt;StopExecution&lt;/code&gt;) work on an &lt;code&gt;executionArn&lt;/code&gt;, which is different for every run. It cannot be pinned, so the agent passes the ARN it got back from &lt;code&gt;StartExecution&lt;/code&gt;. Access is controlled by IAM resource scoping instead.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  3. Two tiers of IAM, because Step Functions has two ARN formats
&lt;/h3&gt;

&lt;p&gt;This one trips people up. State-machine actions and execution actions are checked against different resource ARNs:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;State Machine ARN format:
arn:aws:states:&amp;lt;region&amp;gt;:&amp;lt;account-id&amp;gt;:stateMachine:&amp;lt;state-machine-name&amp;gt;

Execution ARN format:
arn:aws:states:&amp;lt;region&amp;gt;:&amp;lt;account-id&amp;gt;:execution:&amp;lt;state-machine-name&amp;gt;:&amp;lt;execution-name&amp;gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In &lt;code&gt;template.yaml&lt;/code&gt;, the &lt;code&gt;AgentCoreGatewayServiceRole&lt;/code&gt; splits these into two statements:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;Policies&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;PolicyName&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;StepFunctionsMCPConnectorLeastPrivilege&lt;/span&gt;
    &lt;span class="na"&gt;PolicyDocument&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;Version&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"&lt;/span&gt;&lt;span class="s"&gt;2012-10-17"&lt;/span&gt;
      &lt;span class="na"&gt;Statement&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
        &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;Sid&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;StateMachineLevelOperations&lt;/span&gt;
          &lt;span class="na"&gt;Effect&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;Allow&lt;/span&gt;
          &lt;span class="na"&gt;Action&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:StartExecution&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:ListExecutions&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:DescribeStateMachine&lt;/span&gt;
          &lt;span class="na"&gt;Resource&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;!GetAtt&lt;/span&gt; &lt;span class="s"&gt;SalesIngestionStateMachine.Arn&lt;/span&gt;
        &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="na"&gt;Sid&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;ExecutionLevelOperations&lt;/span&gt;
          &lt;span class="na"&gt;Effect&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;Allow&lt;/span&gt;
          &lt;span class="na"&gt;Action&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:DescribeExecution&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:GetExecutionHistory&lt;/span&gt;
            &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="s"&gt;states:StopExecution&lt;/span&gt;
          &lt;span class="na"&gt;Resource&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;!Sub&lt;/span&gt; &lt;span class="s2"&gt;"&lt;/span&gt;&lt;span class="s"&gt;arn:aws:states:${AWS::Region}:${AWS::AccountId}:execution:${SalesIngestionStateMachine.Name}:*"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Because the execution pattern includes the state machine name, the role can only inspect or stop executions of this one pipeline. If someone tricks the agent into stopping a payroll run in the same account, IAM denies it at the API.&lt;/p&gt;

&lt;h3&gt;
  
  
  4. Calling the Gateway over MCP from Python
&lt;/h3&gt;

&lt;p&gt;The client, &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/agent/invoke_agent.py" rel="noopener noreferrer"&gt;&lt;code&gt;agent/invoke_agent.py&lt;/code&gt;&lt;/a&gt;, speaks standard MCP JSON-RPC over SigV4-signed HTTPS. It first calls &lt;code&gt;tools/list&lt;/code&gt; to check the generated schemas, then starts the run with &lt;code&gt;tools/call&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;mcp_start_request&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;jsonrpc&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;2.0&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;req-start-1&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;method&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;tools/call&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;params&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
        &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;name&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;states_StartExecution&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;arguments&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
            &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;name&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sa"&gt;f&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales-ingest-&lt;/span&gt;&lt;span class="si"&gt;{&lt;/span&gt;&lt;span class="n"&gt;uuid&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;uuid4&lt;/span&gt;&lt;span class="p"&gt;().&lt;/span&gt;&lt;span class="nb"&gt;hex&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="si"&gt;:&lt;/span&gt;&lt;span class="mi"&gt;8&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="si"&gt;}&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;input&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;dumps&lt;/span&gt;&lt;span class="p"&gt;({&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sourceKey&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;source_key&lt;/span&gt;&lt;span class="p"&gt;})&lt;/span&gt;
        &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;After that, the client polls &lt;code&gt;states_DescribeExecution&lt;/code&gt; with backoff until the status is no longer &lt;code&gt;RUNNING&lt;/code&gt;, then pulls &lt;code&gt;states_GetExecutionHistory&lt;/code&gt; for the step-level timing.&lt;/p&gt;




&lt;h2&gt;
  
  
  4. Guardrails, and the mistakes I would avoid
&lt;/h2&gt;

&lt;p&gt;I want to be direct about this part. A system prompt that says &lt;em&gt;"never run any workflow except the sales pipeline, and never pass a bad S3 key"&lt;/em&gt; is a wish, not a control. Prompts can be injected, context gets confused, and models change. The protections that actually hold are the ones enforced outside the model: IAM policies, Gateway pinning, and input validation in the state machine.&lt;/p&gt;

&lt;p&gt;Here is how I think about the main risks:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Operational risk&lt;/th&gt;
&lt;th&gt;What it looks like in practice&lt;/th&gt;
&lt;th&gt;Deterministic mitigation&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Prompt injection&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;A malicious string in a ticket or filename tells the agent to start a cleanup workflow or pass odd arguments to Glue.&lt;/td&gt;
&lt;td&gt;Pin &lt;code&gt;stateMachineArn&lt;/code&gt; in the Gateway target. Enforce the &lt;code&gt;raw/sales/*.parquet&lt;/code&gt; pattern in the &lt;code&gt;ValidateInput&lt;/code&gt; Choice state.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Unauthorized execution&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;The agent tries to find and start a finance or HR workflow in the same account.&lt;/td&gt;
&lt;td&gt;Pin the ARN in the Gateway, and scope &lt;code&gt;states:StartExecution&lt;/code&gt;, &lt;code&gt;ListExecutions&lt;/code&gt;, and &lt;code&gt;DescribeStateMachine&lt;/code&gt; in IAM to that one ARN. Never grant &lt;code&gt;states:*&lt;/code&gt; or &lt;code&gt;Resource: "*"&lt;/code&gt;.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Stopping the wrong run&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;The agent is asked to cancel a stuck job and picks up an execution ARN that belongs to something important.&lt;/td&gt;
&lt;td&gt;Scope &lt;code&gt;StopExecution&lt;/code&gt;, &lt;code&gt;DescribeExecution&lt;/code&gt;, and &lt;code&gt;GetExecutionHistory&lt;/code&gt; to &lt;code&gt;execution:PinnedStateMachineName:*&lt;/code&gt;.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Runaway retries&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;A schema error makes the agent call &lt;code&gt;StartExecution&lt;/code&gt; over and over, burning compute.&lt;/td&gt;
&lt;td&gt;Keep retries inside the state machine with &lt;code&gt;MaxAttempts&lt;/code&gt; and &lt;code&gt;BackoffRate&lt;/code&gt;. Use the &lt;code&gt;name&lt;/code&gt; parameter for idempotency, and cap tool calls per session.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;No audit trail&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;A pipeline changed warehouse tables overnight, and nobody can tell whether a person, a schedule, or an agent started it.&lt;/td&gt;
&lt;td&gt;Use Standard workflows, turn on CloudTrail for Step Functions and Gateway calls, and prefix agent-started execution names (for example &lt;code&gt;agent-run-*&lt;/code&gt;).&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Long runs&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;A 45-minute job outlasts the agent's conversation turn.&lt;/td&gt;
&lt;td&gt;Start the run, return the &lt;code&gt;executionArn&lt;/code&gt; straight away, and poll in later turns (or use a callback).&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h3&gt;
  
  
  Four mistakes I have seen (and made)
&lt;/h3&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Using the state machine ARN for execution actions.&lt;/strong&gt; The deploy works, &lt;code&gt;StartExecution&lt;/code&gt; works, and then the agent gets an &lt;code&gt;AccessDeniedException&lt;/code&gt; when it checks status. Execution APIs need the &lt;code&gt;:execution:Name:*&lt;/code&gt; form.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Leaving &lt;code&gt;stateMachineArn&lt;/code&gt; unpinned during testing.&lt;/strong&gt; The Gateway then makes it a required argument. Now you are hardcoding account IDs and regions into the prompt, and inviting the model to make up ARNs.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Using an Express workflow for agent monitoring.&lt;/strong&gt; &lt;code&gt;DescribeExecution&lt;/code&gt; and &lt;code&gt;GetExecutionHistory&lt;/code&gt; are designed around Standard workflows. The tools will fail or give you nothing useful.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Swallowing errors.&lt;/strong&gt; If the failure path ends with &lt;code&gt;"End": true&lt;/code&gt;, the execution reports &lt;code&gt;SUCCEEDED&lt;/code&gt;. Your agent will happily tell the user the pipeline worked just as the on-call pager goes off.&lt;/li&gt;
&lt;/ol&gt;




&lt;h2&gt;
  
  
  5. Where GPT-6.1 Terra fits
&lt;/h2&gt;

&lt;p&gt;The Gateway is half of an agent. The other half is the model that reads the request, plans the tool calls, and writes the summary. AWS announced in the &lt;a href="https://aws.amazon.com/blogs/aws/aws-weekly-roundup-gpt-6-1-terra-on-bedrock-step-functions-as-agent-tools-and-more-november-2-2026/" rel="noopener noreferrer"&gt;weekly roundup&lt;/a&gt; that &lt;strong&gt;OpenAI GPT-6.1 Terra&lt;/strong&gt; is generally available on Amazon Bedrock. AWS describes it as a fast, cost-efficient model for everyday enterprise work.&lt;/p&gt;

&lt;p&gt;One honest note: any speed, latency, or price figures from AWS or OpenAI are vendor-reported. Measure tool-call accuracy, latency, and cost per run against your own payloads before you put a number in an SLA.&lt;/p&gt;

&lt;p&gt;With that caveat, a fast, cheap model suits this kind of work well. Here are four patterns I find useful:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Short tool loops.&lt;/strong&gt; A typical request means &lt;code&gt;StartExecution&lt;/code&gt;, then &lt;code&gt;DescribeExecution&lt;/code&gt;, then maybe &lt;code&gt;GetExecutionHistory&lt;/code&gt;: three to five calls. A fast model keeps that feeling responsive, which is what makes an ops bot pleasant to use.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Triage with context.&lt;/strong&gt; When a run fails, &lt;code&gt;GetExecutionHistory&lt;/code&gt; returns the state transitions and the error. A model can read that, check a knowledge base of runbooks and table contracts, and reply with the cause and the likely fix. A Glue error caused by a column type change is a good example.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Read-only assistants.&lt;/strong&gt; Analysts often want to know whether today's partition has loaded before a dashboard goes out. A Gateway target that exposes only &lt;code&gt;ListExecutions&lt;/code&gt;, &lt;code&gt;DescribeExecution&lt;/code&gt;, and &lt;code&gt;GetExecutionHistory&lt;/code&gt; lets a model answer status questions in Slack, with no start or stop permissions.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Backfill coordination.&lt;/strong&gt; Over a multi-day backfill, an agent can walk through verified partitions, start runs with a concurrency limit, check each one, and stop as soon as a data-quality check fails.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  When I would not use this pattern
&lt;/h3&gt;

&lt;p&gt;Not every workflow belongs behind an agent. I would skip it in three cases:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Scheduled or event-driven jobs.&lt;/strong&gt; A nightly run at 02:00 UTC, or a run triggered by an S3 upload, should be an EventBridge rule that starts Step Functions. Adding a model there only adds cost, latency, and another way to fail.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;High-volume, low-latency streams.&lt;/strong&gt; Thousands of events per second with tight latency targets belong in Kinesis, Kafka, Lambda, or Express workflows with deterministic routing. Agent tool calls are built for control-plane decisions at human speed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Simple read-only questions.&lt;/strong&gt; "What were total sales in the Northeast yesterday?" is a query, not a workflow. Send it to a text-to-SQL tool on Athena, Redshift, or a semantic layer.&lt;/li&gt;
&lt;/ol&gt;




&lt;h2&gt;
  
  
  6. Before you go to production
&lt;/h2&gt;

&lt;p&gt;Before this goes from a sandbox into a real account, I would check these:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;[ ] &lt;strong&gt;Standard workflows&lt;/strong&gt; for anything an agent can reach, so the history is durable and queryable.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Pin &lt;code&gt;stateMachineArn&lt;/code&gt;&lt;/strong&gt; in the Gateway target for every operation that takes it.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Two-tier IAM.&lt;/strong&gt; State-machine ARN for state-machine actions, the &lt;code&gt;:execution:Name:*&lt;/code&gt; pattern for execution actions. No wildcard &lt;code&gt;states:*&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Timeouts, retries, and catches in the ASL.&lt;/strong&gt; Use &lt;code&gt;TimeoutSeconds&lt;/code&gt;, &lt;code&gt;Retry&lt;/code&gt; with backoff for transient errors, and a &lt;code&gt;Catch&lt;/code&gt; that alerts and ends in a &lt;code&gt;Fail&lt;/code&gt; state.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Validate inputs at the start&lt;/strong&gt; of the state machine, before any expensive compute.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Log every MCP call&lt;/strong&gt; on the Gateway, with the caller and the payload.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;CloudTrail and CloudWatch Logs&lt;/strong&gt; enabled for Step Functions, for forensics later.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Separate environments.&lt;/strong&gt; Separate Gateway targets and roles for dev, staging, and production, so a staging agent cannot touch production.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Rate limits.&lt;/strong&gt; Guard Glue and warehouse capacity against runaway loops with concurrency checks, idempotent names, or throttling.&lt;/li&gt;
&lt;li&gt;[ ] &lt;strong&gt;Human approval for risky work.&lt;/strong&gt; Truncations, full reloads, and expensive cluster starts should wait on a &lt;code&gt;waitForTaskToken&lt;/code&gt; callback that a person approves in Slack or through a signed link.&lt;/li&gt;
&lt;/ul&gt;




&lt;h2&gt;
  
  
  7. Closing thoughts
&lt;/h2&gt;

&lt;p&gt;The instinct, when building agents, has been to give models direct access to cloud infrastructure. I understand the appeal. But a language model makes a poor distributed transaction coordinator. When an agent touches raw connectors, retries, idempotency, and audit trails turn into text in a prompt, and that is hard to debug at 3 a.m.&lt;/p&gt;

&lt;p&gt;Putting Step Functions Standard workflows behind AgentCore Gateway gives you a cleaner split:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;The agent decides &lt;em&gt;when&lt;/em&gt; and &lt;em&gt;what&lt;/em&gt;.&lt;/strong&gt; It reads the request and fills in the parameters. Step Functions decides &lt;em&gt;how&lt;/em&gt;: retries, error handling, and state history.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Boundaries beat instructions.&lt;/strong&gt; A prompt is a request. IAM scoping, ARN pinning, and validation in the state machine are rules.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;You can see what happened.&lt;/strong&gt; Standard workflows keep a full history, so the agent can answer accurately without anyone digging through log streams.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;AI agents do not remove the need for data engineering discipline. If anything, they make it more important. Our job is to design the state machines and the security boundaries that let an agent run a production platform safely, and to be honest with ourselves about where the agent should stop.&lt;/p&gt;




&lt;h3&gt;
  
  
  Resources &amp;amp; Demo Repository
&lt;/h3&gt;

&lt;p&gt;The complete deployable project, including the SAM template, least-privilege IAM roles, the PySpark Glue job, and the MCP client, is in this repository:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;State machine definition:&lt;/strong&gt; &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/state-machine/data-pipeline.asl.json" rel="noopener noreferrer"&gt;&lt;code&gt;state-machine/data-pipeline.asl.json&lt;/code&gt;&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;AgentCore Gateway config:&lt;/strong&gt; &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/gateway/target.json" rel="noopener noreferrer"&gt;&lt;code&gt;gateway/target.json&lt;/code&gt;&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SAM template:&lt;/strong&gt; &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/template.yaml" rel="noopener noreferrer"&gt;&lt;code&gt;template.yaml&lt;/code&gt;&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Python agent client:&lt;/strong&gt; &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/agent/invoke_agent.py" rel="noopener noreferrer"&gt;&lt;code&gt;agent/invoke_agent.py&lt;/code&gt;&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Setup guide:&lt;/strong&gt; &lt;a href="https://github.com/kien-ly/aws-step-functions-mcp-orchestrator/blob/main/docs/env-setup.md" rel="noopener noreferrer"&gt;&lt;code&gt;docs/env-setup.md&lt;/code&gt;&lt;/a&gt;
&lt;/li&gt;
&lt;/ul&gt;

</description>
      <category>agents</category>
      <category>ai</category>
      <category>architecture</category>
      <category>data</category>
    </item>
    <item>
      <title>Building a Near Real-Time Analytics Pipeline with AWS Zero-ETL</title>
      <dc:creator>kienly</dc:creator>
      <pubDate>Fri, 16 Jan 2026 05:23:10 +0000</pubDate>
      <link>https://dev.to/aws-builders/building-a-near-real-time-analytics-pipeline-with-aws-zero-etl-2gjb</link>
      <guid>https://dev.to/aws-builders/building-a-near-real-time-analytics-pipeline-with-aws-zero-etl-2gjb</guid>
      <description>&lt;blockquote&gt;
&lt;p&gt;TL;DR: This guide shows how to replicate transactional data from Amazon Aurora (MySQL) to Amazon Redshift using AWS Zero-ETL, including Aurora binlog configuration, a sample SAM template, and deployment notes.&lt;/p&gt;
&lt;/blockquote&gt;




&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;AWS Zero-ETL enables near real-time replication from Aurora to Redshift without custom ETL code. This guide covers the required Aurora settings, a Lambda ingestion example, deployment with AWS SAM, and the steps to configure the Zero-ETL integration for analytics in Redshift.&lt;/p&gt;

&lt;h2&gt;
  
  
  What is Zero-ETL?
&lt;/h2&gt;

&lt;p&gt;Zero-ETL is AWS's managed integration that reduces the need for custom extract-transform-load pipelines by streaming change data from Aurora into Redshift.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;flowchart LR
    subgraph Traditional
        S1[Source] --&amp;gt; E[Extract] --&amp;gt; T[Transform] --&amp;gt; L[Load] --&amp;gt; D1[DW]
    end
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;flowchart LR
    subgraph ZeroETL["Zero-ETL"]
        S2[Aurora] --&amp;gt;|CDC Auto| D2[Redshift]
    end
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Key benefits:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;No pipeline code to maintain&lt;/li&gt;
&lt;li&gt;Near real-time replication (seconds to minutes latency)&lt;/li&gt;
&lt;li&gt;Transactional consistency&lt;/li&gt;
&lt;li&gt;Automatic schema mapping between source and target&lt;/li&gt;
&lt;/ul&gt;




&lt;h2&gt;
  
  
  Prerequisites
&lt;/h2&gt;

&lt;p&gt;Ensure you have the following before proceeding:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;AWS CLI v2 configured with appropriate permissions&lt;/li&gt;
&lt;li&gt;AWS SAM CLI installed (&lt;code&gt;pip install aws-sam-cli&lt;/code&gt;)&lt;/li&gt;
&lt;li&gt;Python 3.12+&lt;/li&gt;
&lt;li&gt;Docker (for SAM local testing)&lt;/li&gt;
&lt;li&gt;An AWS account with permissions for RDS, Redshift, Lambda, VPC&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Step 1: Project Structure
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;zero-etl-demo/
├── template.yaml
├── samconfig.toml
├── src/lambda/
│   ├── handler.py
│   ├── container.py
│   ├── service.py
│   └── requirements.txt
├── db/schema.sql
└── scripts/setup_zero_etl.sh
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 2: SAM Template (Infrastructure as Code)
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Aurora MySQL Configuration
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;DBClusterParameterGroup&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;Type&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;AWS::RDS::DBClusterParameterGroup&lt;/span&gt;
  &lt;span class="na"&gt;Properties&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;Family&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;aurora-mysql8.0&lt;/span&gt;
    &lt;span class="na"&gt;Parameters&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;binlog_format&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;ROW&lt;/span&gt;
      &lt;span class="na"&gt;aurora_enhanced_binlog&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"&lt;/span&gt;&lt;span class="s"&gt;1"&lt;/span&gt;
      &lt;span class="na"&gt;binlog_row_image&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;FULL&lt;/span&gt;

&lt;span class="na"&gt;AuroraDBCluster&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;Type&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;AWS::RDS::DBCluster&lt;/span&gt;
  &lt;span class="na"&gt;Properties&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;Engine&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;aurora-mysql&lt;/span&gt;
    &lt;span class="na"&gt;EngineVersion&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;8.0.mysql_aurora.3.04.0&lt;/span&gt;
    &lt;span class="na"&gt;StorageEncrypted&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="kc"&gt;true&lt;/span&gt;
    &lt;span class="na"&gt;DBClusterParameterGroupName&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;!Ref&lt;/span&gt; &lt;span class="s"&gt;DBClusterParameterGroup&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Note: After CloudFormation creates the cluster with the parameter group, reboot the writer instance via Console or CLI for binlog parameters to apply.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Zero-ETL requirements:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Engine version must be Aurora MySQL 3.04.0+&lt;/li&gt;
&lt;li&gt;Storage encryption must be enabled&lt;/li&gt;
&lt;li&gt;Binary logging must be ROW format&lt;/li&gt;
&lt;li&gt;Writer instance must be rebooted after parameter group changes&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Lambda Function
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;IngestFunction&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;Type&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;AWS::Serverless::Function&lt;/span&gt;
  &lt;span class="na"&gt;Properties&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;Runtime&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;python3.12&lt;/span&gt;
    &lt;span class="na"&gt;Handler&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;handler.lambda_handler&lt;/span&gt;
    &lt;span class="na"&gt;VpcConfig&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;SecurityGroupIds&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
        &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="kt"&gt;!Ref&lt;/span&gt; &lt;span class="s"&gt;LambdaSecurityGroup&lt;/span&gt;
      &lt;span class="na"&gt;SubnetIds&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
        &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="kt"&gt;!Ref&lt;/span&gt; &lt;span class="s"&gt;PrivateSubnet1&lt;/span&gt;
        &lt;span class="pi"&gt;-&lt;/span&gt; &lt;span class="kt"&gt;!Ref&lt;/span&gt; &lt;span class="s"&gt;PrivateSubnet2&lt;/span&gt;
    &lt;span class="na"&gt;Events&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;ScheduledIngestion&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
        &lt;span class="na"&gt;Type&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;Schedule&lt;/span&gt;
        &lt;span class="na"&gt;Properties&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt;
          &lt;span class="na"&gt;Schedule&lt;/span&gt;&lt;span class="pi"&gt;:&lt;/span&gt; &lt;span class="s"&gt;rate(5 minutes)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 3: Lambda Code with Dependency Injection
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;flowchart TB
    Handler --&amp;gt; Container
    Container --&amp;gt; APIClient
    Container --&amp;gt; Database
    Container --&amp;gt; Processor
    APIClient --&amp;gt; Service[ZeroETLService]
    Database --&amp;gt; Service
    Processor --&amp;gt; Service
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h3&gt;
  
  
  DI Container
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;ServiceContainer&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;__init__&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_factories&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
        &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_instances&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;

    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;register&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;factory&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;Callable&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_factories&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;factory&lt;/span&gt;

    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;resolve&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_instances&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_instances&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_factories&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;](&lt;/span&gt;&lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;self&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;_instances&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h3&gt;
  
  
  Lambda Handler
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;lambda_handler&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;event&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;context&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
    &lt;span class="n"&gt;container&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt;
    &lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;container&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;create_container&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
        &lt;span class="n"&gt;service&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;container&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;resolve&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;zero_etl_service&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;summary&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;service&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;run_full_ingestion&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;

        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
            &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;statusCode&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;200&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;summary&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;success&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="mi"&gt;207&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;body&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;dumps&lt;/span&gt;&lt;span class="p"&gt;({&lt;/span&gt;
                &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;run_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;summary&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
                &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;total_records&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;summary&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_records&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="p"&gt;})&lt;/span&gt;
        &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="k"&gt;finally&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;container&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;container&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;dispose&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 4: Database Schema
&lt;/h2&gt;

&lt;p&gt;Note: Foreign keys with &lt;code&gt;CASCADE&lt;/code&gt; are not supported by Zero-ETL.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- This will fail with Zero-ETL&lt;/span&gt;
&lt;span class="k"&gt;FOREIGN&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;CASCADE&lt;/span&gt;

&lt;span class="c1"&gt;-- Use RESTRICT instead&lt;/span&gt;
&lt;span class="k"&gt;FOREIGN&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;RESTRICT&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;255&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;email&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;255&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;UNIQUE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;ENGINE&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;InnoDB&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;posts&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;title&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;500&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;FOREIGN&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;RESTRICT&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;ENGINE&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;InnoDB&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 5: Deploy the Stack
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sam build
sam deploy &lt;span class="nt"&gt;--guided&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;After deployment, obtain the Aurora endpoint, reboot the writer instance to apply binlog settings, and run the schema script.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="c"&gt;# Get Aurora endpoint&lt;/span&gt;
&lt;span class="nv"&gt;ENDPOINT&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="si"&gt;$(&lt;/span&gt;aws cloudformation describe-stacks &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--stack-name&lt;/span&gt; zero-etl-demo &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--query&lt;/span&gt; &lt;span class="s1"&gt;'Stacks[0].Outputs[?OutputKey==`AuroraClusterEndpoint`].OutputValue'&lt;/span&gt; &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--output&lt;/span&gt; text&lt;span class="si"&gt;)&lt;/span&gt;

&lt;span class="c"&gt;# Reboot writer instance&lt;/span&gt;
aws rds reboot-db-instance &lt;span class="nt"&gt;--db-instance-identifier&lt;/span&gt; zero-etl-demo-instance-1

&lt;span class="c"&gt;# Wait for instance to be available, then run schema&lt;/span&gt;
mysql &lt;span class="nt"&gt;-h&lt;/span&gt; &lt;span class="nv"&gt;$ENDPOINT&lt;/span&gt; &lt;span class="nt"&gt;-u&lt;/span&gt; admin &lt;span class="nt"&gt;-p&lt;/span&gt; &amp;lt; db/schema.sql
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 6: Set Up Zero-ETL Integration
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;aws rds create-integration &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--integration-name&lt;/span&gt; zero-etl-demo &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--source-arn&lt;/span&gt; arn:aws:rds:us-east-1:123456789:cluster:my-cluster &lt;span class="se"&gt;\&lt;/span&gt;
    &lt;span class="nt"&gt;--target-arn&lt;/span&gt; arn:aws:redshift-serverless:us-east-1:123456789:namespace/my-ns
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Note: Initial synchronization may take 20–30 minutes.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 7: Query Analytics in Redshift
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt; &lt;span class="n"&gt;zero_etl_db&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;INTEGRATION&lt;/span&gt; &lt;span class="s1"&gt;'arn:aws:rds:...'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Posts per user&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;u&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;post_count&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;zero_etl_db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;public&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;users&lt;/span&gt; &lt;span class="n"&gt;u&lt;/span&gt;
&lt;span class="k"&gt;LEFT&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;zero_etl_db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;public&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;posts&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;u&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;u&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;u&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;post_count&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Data Flow Summary
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;sequenceDiagram
    participant EB as EventBridge
    participant L as Lambda
    participant API as JSONPlaceholder
    participant A as Aurora
    participant R as Redshift

    EB-&amp;gt;&amp;gt;L: Trigger (5 min)
    L-&amp;gt;&amp;gt;API: GET /users, /posts
    API--&amp;gt;&amp;gt;L: JSON
    L-&amp;gt;&amp;gt;A: Batch INSERT
    Note over A,R: Zero-ETL CDC (auto)
    A--&amp;gt;&amp;gt;R: Replicate
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Monitoring
&lt;/h2&gt;

&lt;p&gt;Metrics to monitor include:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Lambda: Duration, Errors&lt;/li&gt;
&lt;li&gt;RDS: ZeroETLIntegrationLatency (or equivalent)&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Cost Considerations
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Service&lt;/th&gt;
&lt;th&gt;Est. Monthly Cost&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Aurora MySQL&lt;/td&gt;
&lt;td&gt;~$200&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Lambda&lt;/td&gt;
&lt;td&gt;~$5&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;NAT Gateway&lt;/td&gt;
&lt;td&gt;~$35&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Redshift&lt;/td&gt;
&lt;td&gt;~$50&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Total&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;~$290&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Limitations
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Foreign keys with CASCADE are not supported — use RESTRICT instead.&lt;/li&gt;
&lt;li&gt;Reboot is required after parameter group changes for binlog settings to apply.&lt;/li&gt;
&lt;li&gt;Initial synchronization can take 20–30 minutes.&lt;/li&gt;
&lt;li&gt;Aurora engine version requirements apply (3.04.0+).&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;AWS Zero-ETL provides a managed replication path between Aurora and Redshift that reduces operational overhead and supports near-real-time analytics. It is suitable for cases where replication without custom ETL logic is desired.&lt;/p&gt;




&lt;h2&gt;
  
  
  Resources
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/zero-etl.html" rel="noopener noreferrer"&gt;AWS Zero-ETL Documentation&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Concepts.Aurora_Fea_Regions_DB-eng.Feature.Zero-ETL.html" rel="noopener noreferrer"&gt;Aurora MySQL Supported Versions&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-whatis.html" rel="noopener noreferrer"&gt;Redshift Serverless&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;Questions or feedback are welcome. Connect via LinkedIn for further discussion.&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>aws</category>
      <category>database</category>
      <category>dataengineering</category>
    </item>
  </channel>
</rss>
