AWS Learning Journey

Build an End-to-End Data Platform on AWS: From Database to Dashboard

The Phase 9 capstone lab: replicate the orders database into S3 with DMS, transform it with Glue into curated Parquet, query it with Athena and Redshift, orchestrate it with Step Functions, and get alerted through CloudWatch and SNS.

18 min read · 3524 words
Data EngineeringAWSData PipelinesTutorialBig Data
Build an End-to-End Data Platform on AWS: From Database to Dashboard cover image

AWS from Zero to Production — Phase 9, Part 11 of 11 · See the full series roadmap


Ten articles of concepts. Now we build the real thing.

The orders API you’ve deployed since Phase 4 writes to a PostgreSQL database. In this lab, that database becomes the source for an analytics platform — without a single analytics query touching production:

Source database (RDS PostgreSQL — the orders app)
    |
    v
AWS DMS (Serverless, full load)
    |
    v
Amazon S3 raw zone (Parquet, as delivered)
    |
    v
AWS Glue ETL (clean, type, dedupe, partition by date)
    |
    v
Amazon S3 curated Parquet zone
    |
    +----> Amazon Athena (ad hoc SQL)
    |
    +----> Amazon Redshift Serverless (Spectrum, COPY, UNLOAD)
    |
    v
Step Functions orchestration + CloudWatch alarm + SNS alerts

It’s the most moving parts of any lab in the series. Take it one step at a time, and use the checkpoints.

What you’ll build

Prerequisites

Cost note: This lab adds DMS Serverless capacity, Glue job DPU-hours, Athena scans (tiny here), Redshift Serverless RPU-hours, and a Secrets Manager secret — on top of the running Phase 6 stack. Redshift Serverless and DMS are the biggest items; they bill while running. Run the Cleanup section the same day. Check current pricing first.

Keep one terminal open. Later steps use shell variables from earlier ones.


Step 1: Set up variables and the lake bucket

export AWS_PROFILE=sandbox
export AWS_REGION=us-east-1
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
LAKE="orders-lake-$ACCOUNT_ID-$AWS_REGION"

aws s3api create-bucket --bucket "$LAKE"
aws s3api put-public-access-block --bucket "$LAKE" \
  --public-access-block-configuration \
  BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true

cat > lifecycle.json <<'EOF'
{
  "Rules": [
    { "ID": "expire-athena-results", "Filter": { "Prefix": "athena-results/" },
      "Status": "Enabled", "Expiration": { "Days": 7 } },
    { "ID": "abort-multipart", "Filter": {}, "Status": "Enabled",
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 7 } }
  ]
}
EOF
aws s3api put-bucket-lifecycle-configuration --bucket "$LAKE" \
  --lifecycle-configuration file://lifecycle.json

The bucket uses prefixes for zones — raw/, curated/, consumption/ — plus scripts/ and athena-results/. In production, you’d likely use separate buckets per zone (Part 2).

Collect values from the existing stack:

VPC_ID=$(aws ec2 describe-vpcs --filters Name=tag:Name,Values=orders-dev \
  --query 'Vpcs[0].VpcId' --output text)
PRIVATE_SUBNETS=$(aws ec2 describe-subnets \
  --filters Name=vpc-id,Values="$VPC_ID" Name=tag:Name,Values='*private*' \
  --query 'Subnets[].SubnetId' --output text)
DB_SG=$(aws ec2 describe-security-groups \
  --filters Name=vpc-id,Values="$VPC_ID" Name=group-name,Values=orders-dev-db \
  --query 'SecurityGroups[0].GroupId' --output text)
DB_HOST=$(aws rds describe-db-instances --db-instance-identifier orders-dev-db \
  --query 'DBInstances[0].Endpoint.Address' --output text)
DB_SECRET_ARN=$(aws rds describe-db-instances --db-instance-identifier orders-dev-db \
  --query 'DBInstances[0].MasterUserSecret.SecretArn' --output text)

echo "$LAKE | $VPC_ID | $DB_SG | $DB_HOST"

✅ Checkpoint: All values print, and none says None.


Step 2: Prepare DMS access

The DMS VPC role

DMS needs a service role named exactly dms-vpc-role to manage network interfaces. Create it if it doesn’t exist:

cat > dms-trust.json <<'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": ["dms.amazonaws.com", "dms.us-east-1.amazonaws.com"] },
    "Action": "sts:AssumeRole"
  }]
}
EOF

aws iam get-role --role-name dms-vpc-role >/dev/null 2>&1 || {
  aws iam create-role --role-name dms-vpc-role --assume-role-policy-document file://dms-trust.json
  aws iam attach-role-policy --role-name dms-vpc-role \
    --policy-arn arn:aws:iam::aws:policy/service-role/AmazonDMSVPCManagementRole
}

Outside us-east-1? Change the Region in the second service principal.

A source secret for DMS

DMS can read source credentials from Secrets Manager, but it needs host and port in the secret. Build a new secret from the RDS-managed one — the password flows through a pipe and is never printed:

DMS_SECRET_ARN=$(aws secretsmanager get-secret-value --secret-id "$DB_SECRET_ARN" \
    --query SecretString --output text \
  | jq --arg host "$DB_HOST" '. + {host: $host, port: 5432}' \
  | aws secretsmanager create-secret --name lab9/dms-source \
      --secret-string file:///dev/stdin --query ARN --output text)

Production note: This copy won’t follow future rotations of the RDS-managed secret. In production, create a dedicated read-only replication user with its own rotated secret.

The DMS access role

One role lets DMS read that secret and write to the raw zone:

aws iam create-role --role-name lab9-dms-access \
  --assume-role-policy-document file://dms-trust.json

cat > dms-access.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": "secretsmanager:GetSecretValue",
      "Resource": "$DMS_SECRET_ARN" },
    { "Effect": "Allow", "Action": ["s3:PutObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::$LAKE/raw/*" },
    { "Effect": "Allow", "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::$LAKE" }
  ]
}
EOF
aws iam put-role-policy --role-name lab9-dms-access \
  --policy-name dms-access --policy-document file://dms-access.json
DMS_ROLE_ARN=$(aws iam get-role --role-name lab9-dms-access --query Role.Arn --output text)

Network

aws dms create-replication-subnet-group \
  --replication-subnet-group-identifier lab9-dms-subnets \
  --replication-subnet-group-description "orders-dev private subnets" \
  --subnet-ids $PRIVATE_SUBNETS

DMS_SG=$(aws ec2 create-security-group --vpc-id "$VPC_ID" \
  --group-name lab9-dms --description "DMS replication" \
  --query GroupId --output text)

aws ec2 authorize-security-group-ingress --group-id "$DB_SG" \
  --protocol tcp --port 5432 --source-group "$DMS_SG"

DMS runs in the private subnets, reaches the database through the database security group, and reaches S3 through the NAT gateway.

✅ Checkpoint: lab9-dms-access exists, and orders-dev-db allows port 5432 from lab9-dms.


Step 3: Replicate the database into the raw zone

Create the endpoints:

SOURCE_EP=$(aws dms create-endpoint \
  --endpoint-identifier lab9-orders-source \
  --endpoint-type source --engine-name postgres \
  --database-name orders --ssl-mode require \
  --postgre-sql-settings "SecretsManagerAccessRoleArn=$DMS_ROLE_ARN,SecretsManagerSecretId=$DMS_SECRET_ARN" \
  --query Endpoint.EndpointArn --output text)

TARGET_EP=$(aws dms create-endpoint \
  --endpoint-identifier lab9-lake-raw \
  --endpoint-type target --engine-name s3 \
  --s3-settings "{\"ServiceAccessRoleArn\":\"$DMS_ROLE_ARN\",\"BucketName\":\"$LAKE\",\"BucketFolder\":\"raw/orders_db\",\"DataFormat\":\"parquet\",\"ParquetVersion\":\"parquet-2-0\"}" \
  --query Endpoint.EndpointArn --output text)

Choose what to replicate:

cat > table-mappings.json <<'EOF'
{
  "rules": [{
    "rule-type": "selection",
    "rule-id": "1",
    "rule-name": "orders",
    "object-locator": { "schema-name": "public", "table-name": "orders" },
    "rule-action": "include"
  }]
}
EOF

Create and start a DMS Serverless replication:

REPL_ARN=$(aws dms create-replication-config \
  --replication-config-identifier lab9-orders-to-lake \
  --source-endpoint-arn "$SOURCE_EP" \
  --target-endpoint-arn "$TARGET_EP" \
  --replication-type full-load \
  --table-mappings file://table-mappings.json \
  --compute-config "MinCapacityUnits=1,MaxCapacityUnits=2,ReplicationSubnetGroupId=lab9-dms-subnets,VpcSecurityGroupIds=$DMS_SG" \
  --query ReplicationConfig.ReplicationConfigArn --output text)

aws dms start-replication --replication-config-arn "$REPL_ARN" \
  --start-replication-type start-replication

DMS Serverless provisions capacity first, so this takes a while — often 10–20 minutes. Check progress:

aws dms describe-replications \
  --filters Name=replication-config-arn,Values="$REPL_ARN" \
  --query 'Replications[0].[Status,ReplicationStats.FullLoadProgressPercent,StopReason]' \
  --output text

When it stops with a full-load-complete reason, look at the raw zone:

aws s3 ls "s3://$LAKE/raw/orders_db/" --recursive

✅ Checkpoint: You see a file like raw/orders_db/public/orders/LOAD00000001.parquet.

Want continuous replication? Use full-load-and-cdc instead. That requires rds.logical_replication = 1 in the database’s parameter group — and your Glue job would then need to merge inserts, updates, and deletes (Part 9).


Step 4: Catalog the raw data

Create a role for Glue:

cat > glue-trust.json <<'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": "glue.amazonaws.com" },
    "Action": "sts:AssumeRole"
  }]
}
EOF

aws iam create-role --role-name lab9-glue --assume-role-policy-document file://glue-trust.json
aws iam attach-role-policy --role-name lab9-glue \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole

cat > glue-s3.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": ["s3:GetObject", "s3:PutObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::$LAKE/*" },
    { "Effect": "Allow", "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::$LAKE" }
  ]
}
EOF
aws iam put-role-policy --role-name lab9-glue --policy-name lake-access \
  --policy-document file://glue-s3.json

Create a database and crawl the raw table:

aws glue create-database --database-input Name=orders_raw

aws glue create-crawler \
  --name lab9-raw-orders \
  --role lab9-glue \
  --database-name orders_raw \
  --targets "{\"S3Targets\":[{\"Path\":\"s3://$LAKE/raw/orders_db/public/orders/\"}]}"

aws glue start-crawler --name lab9-raw-orders

Wait a minute or two, then:

aws glue get-tables --database-name orders_raw \
  --query 'TableList[].[Name,StorageDescriptor.Columns[].Name]' --output json

✅ Checkpoint: A table named orders with columns id, item, and created_at.


Step 5: Transform raw into curated with Glue

Write the job script, raw_to_curated.py:

import sys

from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql import functions as F

args = getResolvedOptions(sys.argv, ["JOB_NAME", "RAW_DB", "RAW_TABLE", "CURATED_PATH"])

glue = GlueContext(SparkContext.getOrCreate())
spark = glue.spark_session
job = Job(glue)
job.init(args["JOB_NAME"], args)

raw = glue.create_dynamic_frame.from_catalog(
    database=args["RAW_DB"], table_name=args["RAW_TABLE"]
).toDF()

curated = (
    raw.select(
        F.col("id").cast("bigint").alias("order_id"),
        F.trim(F.col("item")).alias("item"),
        F.col("created_at").cast("timestamp").alias("created_at"),
    )
    .filter(F.col("item").isNotNull() & (F.length("item") > 0))
    .dropDuplicates(["order_id"])
    .withColumn("order_date", F.to_date("created_at"))
)

if curated.limit(1).count() == 0:
    raise ValueError("No valid orders found in the raw zone; refusing to overwrite curated data.")

(
    curated.repartition("order_date")
    .write.mode("overwrite")
    .partitionBy("order_date")
    .parquet(args["CURATED_PATH"])
)

job.commit()

What it does:

Upload it and create the job:

aws s3 cp raw_to_curated.py "s3://$LAKE/scripts/raw_to_curated.py"

aws glue create-job \
  --name lab9-raw-to-curated \
  --role lab9-glue \
  --glue-version 5.0 \
  --worker-type G.1X \
  --number-of-workers 2 \
  --command "Name=glueetl,ScriptLocation=s3://$LAKE/scripts/raw_to_curated.py,PythonVersion=3" \
  --default-arguments "{\"--RAW_DB\":\"orders_raw\",\"--RAW_TABLE\":\"orders\",\"--CURATED_PATH\":\"s3://$LAKE/curated/sales/orders/\",\"--enable-metrics\":\"true\"}"

RUN_ID=$(aws glue start-job-run --job-name lab9-raw-to-curated --query JobRunId --output text)

Check it (a few minutes):

aws glue get-job-run --job-name lab9-raw-to-curated --run-id "$RUN_ID" \
  --query 'JobRun.[JobRunState,ExecutionTime,ErrorMessage]' --output text

aws s3 ls "s3://$LAKE/curated/sales/orders/"

✅ Checkpoint: The run SUCCEEDED, and you see folders like order_date=2026-10-31/.


Step 6: Query with Athena

Create a workgroup with a 1 GB per-query limit:

aws athena create-work-group --name lab9 --configuration "{
  \"ResultConfiguration\": {\"OutputLocation\": \"s3://$LAKE/athena-results/\"},
  \"EnforceWorkGroupConfiguration\": true,
  \"BytesScannedCutoffPerQuery\": 1073741824,
  \"PublishCloudWatchMetricsEnabled\": true
}"

A small helper to run SQL and print results:

athena_sql() {
  local qid state
  qid=$(aws athena start-query-execution --work-group lab9 \
    --query-string "$1" --query QueryExecutionId --output text)
  while true; do
    state=$(aws athena get-query-execution --query-execution-id "$qid" \
      --query QueryExecution.Status.State --output text)
    case "$state" in SUCCEEDED|FAILED|CANCELLED) break ;; esac
    sleep 2
  done
  echo "[$state]"
  if [ "$state" = "SUCCEEDED" ]; then
    aws athena get-query-results --query-execution-id "$qid" \
      --query 'ResultSet.Rows[].Data[*].VarCharValue' --output text
  else
    aws athena get-query-execution --query-execution-id "$qid" \
      --query QueryExecution.Status.StateChangeReason --output text
  fi
}

Define the curated table explicitly — no crawler needed for data you own:

athena_sql "CREATE DATABASE IF NOT EXISTS sales_curated"

athena_sql "CREATE EXTERNAL TABLE IF NOT EXISTS sales_curated.orders (
  order_id bigint,
  item string,
  created_at timestamp
)
PARTITIONED BY (order_date date)
STORED AS PARQUET
LOCATION 's3://$LAKE/curated/sales/orders/'"

athena_sql "MSCK REPAIR TABLE sales_curated.orders"

Now ask business questions:

athena_sql "SELECT order_date, COUNT(*) AS orders
FROM sales_curated.orders
GROUP BY order_date ORDER BY order_date"

athena_sql "SELECT item, COUNT(*) AS orders
FROM sales_curated.orders
WHERE order_date = current_date
GROUP BY item ORDER BY orders DESC LIMIT 5"

In the Athena console, open the lab9 workgroup’s recent queries and look at data scanned — kilobytes, thanks to Parquet and partitions.

✅ Checkpoint: You see order counts per day, and today’s top items.


Step 7: Query and load with Redshift Serverless

Create a role Redshift can use to read the lake and catalog, and write exports:

cat > redshift-trust.json <<'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": ["redshift.amazonaws.com", "redshift-serverless.amazonaws.com"] },
    "Action": "sts:AssumeRole"
  }]
}
EOF
aws iam create-role --role-name lab9-redshift --assume-role-policy-document file://redshift-trust.json

cat > redshift-access.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": ["s3:GetObject", "s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": ["arn:aws:s3:::$LAKE", "arn:aws:s3:::$LAKE/curated/*"] },
    { "Effect": "Allow", "Action": ["s3:PutObject", "s3:DeleteObject", "s3:GetObject"],
      "Resource": "arn:aws:s3:::$LAKE/consumption/*" },
    { "Effect": "Allow", "Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:GetTable",
        "glue:GetTables", "glue:GetPartition", "glue:GetPartitions", "glue:BatchGetPartition"],
      "Resource": "*" }
  ]
}
EOF
aws iam put-role-policy --role-name lab9-redshift --policy-name lake-read \
  --policy-document file://redshift-access.json
REDSHIFT_ROLE_ARN=$(aws iam get-role --role-name lab9-redshift --query Role.Arn --output text)

Create the namespace and workgroup. The admin password is generated and stored in Secrets Manager:

aws redshift-serverless create-namespace \
  --namespace-name lab9 \
  --admin-username lab9admin \
  --manage-admin-password \
  --db-name dev \
  --iam-roles "$REDSHIFT_ROLE_ARN" \
  --default-iam-role-arn "$REDSHIFT_ROLE_ARN"

aws redshift-serverless create-workgroup \
  --workgroup-name lab9 \
  --namespace-name lab9 \
  --base-capacity 8 \
  --no-publicly-accessible

Without subnet IDs, the workgroup uses your default VPC, which has subnets in enough AZs. Wait until it’s available:

aws redshift-serverless get-workgroup --workgroup-name lab9 \
  --query workgroup.status --output text

Cost control: Set a usage limit right away, in the console under Workgroup → Limits, or with aws redshift-serverless create-usage-limit. Smaller base capacities may be available in your Region — check current options.

A helper for the Redshift Data API — no drivers or network access needed:

rs_sql() {
  local sid state
  sid=$(aws redshift-data execute-statement --workgroup-name lab9 --database dev \
    --sql "$1" --query Id --output text)
  while true; do
    state=$(aws redshift-data describe-statement --id "$sid" --query Status --output text)
    case "$state" in FINISHED|FAILED|ABORTED) break ;; esac
    sleep 2
  done
  echo "[$state]"
  if [ "$state" = "FAILED" ]; then
    aws redshift-data describe-statement --id "$sid" --query Error --output text
  elif [ "$(aws redshift-data describe-statement --id "$sid" --query HasResultSet --output text)" = "True" ]; then
    aws redshift-data get-statement-result --id "$sid" \
      | jq -r '.Records[] | map(.[]) | @tsv'
  fi
}

Query the lake with Spectrum

rs_sql "CREATE EXTERNAL SCHEMA IF NOT EXISTS lake
FROM DATA CATALOG DATABASE 'sales_curated'
IAM_ROLE default"

rs_sql "SELECT order_date, COUNT(*) FROM lake.orders GROUP BY 1 ORDER BY 1"

Redshift is reading the same Parquet files and catalog table as Athena — no copy.

Load into the warehouse with COPY

rs_sql "CREATE TABLE IF NOT EXISTS fact_orders (
  order_id   BIGINT,
  item       VARCHAR(200),
  created_at TIMESTAMP
) SORTKEY (created_at)"

rs_sql "TRUNCATE fact_orders"

rs_sql "COPY fact_orders
FROM 's3://$LAKE/curated/sales/orders/'
IAM_ROLE default
FORMAT AS PARQUET"

rs_sql "SELECT item, COUNT(*) AS orders FROM fact_orders GROUP BY item ORDER BY orders DESC LIMIT 5"

COPY reads every Parquet file under the prefix, in parallel. The partition column order_date lives in the folder names, not the files, so the table has three columns — and you can derive the date from created_at.

Export with UNLOAD

rs_sql "UNLOAD ('SELECT TRUNC(created_at) AS order_day, COUNT(*) AS orders FROM fact_orders GROUP BY 1')
TO 's3://$LAKE/consumption/sales/daily_orders/'
IAM_ROLE default
FORMAT AS PARQUET
ALLOWOVERWRITE"

aws s3 ls "s3://$LAKE/consumption/sales/daily_orders/"

✅ Checkpoint: Spectrum and fact_orders return the same counts, and Parquet files appear in the consumption zone.


Step 8: Orchestrate with Step Functions

Create an SNS topic for data alerts and subscribe your email:

TOPIC_ARN=$(aws sns create-topic --name lab9-data-alerts --query TopicArn --output text)
aws sns subscribe --topic-arn "$TOPIC_ARN" --protocol email --notification-endpoint you@example.com

Confirm the subscription from your inbox.

Create the state machine’s role:

cat > sfn-trust.json <<'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": "states.amazonaws.com" },
    "Action": "sts:AssumeRole"
  }]
}
EOF
aws iam create-role --role-name lab9-pipeline --assume-role-policy-document file://sfn-trust.json

cat > sfn-access.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow",
      "Action": ["glue:StartJobRun", "glue:GetJobRun", "glue:GetJobRuns", "glue:BatchStopJobRun"],
      "Resource": "arn:aws:glue:$AWS_REGION:$ACCOUNT_ID:job/lab9-raw-to-curated" },
    { "Effect": "Allow",
      "Action": ["athena:StartQueryExecution", "athena:GetQueryExecution", "athena:StopQueryExecution"],
      "Resource": "arn:aws:athena:$AWS_REGION:$ACCOUNT_ID:workgroup/lab9" },
    { "Effect": "Allow",
      "Action": ["glue:GetDatabase", "glue:GetTable", "glue:GetPartitions",
                 "glue:BatchCreatePartition", "glue:CreatePartition"],
      "Resource": "*" },
    { "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:PutObject", "s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": ["arn:aws:s3:::$LAKE", "arn:aws:s3:::$LAKE/*"] },
    { "Effect": "Allow", "Action": "sns:Publish", "Resource": "$TOPIC_ARN" }
  ]
}
EOF
aws iam put-role-policy --role-name lab9-pipeline --policy-name pipeline \
  --policy-document file://sfn-access.json
SFN_ROLE_ARN=$(aws iam get-role --role-name lab9-pipeline --query Role.Arn --output text)

Define the pipeline:

cat > pipeline.json <<EOF
{
  "Comment": "Raw to curated orders, then refresh partitions",
  "StartAt": "TransformOrders",
  "States": {
    "TransformOrders": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Parameters": { "JobName": "lab9-raw-to-curated" },
      "Retry": [{ "ErrorEquals": ["Glue.AWSGlueException"], "IntervalSeconds": 60,
                  "MaxAttempts": 2, "BackoffRate": 2 }],
      "Catch": [{ "ErrorEquals": ["States.ALL"], "ResultPath": "\$.error", "Next": "NotifyFailure" }],
      "Next": "RefreshPartitions"
    },
    "RefreshPartitions": {
      "Type": "Task",
      "Resource": "arn:aws:states:::athena:startQueryExecution.sync",
      "Parameters": {
        "QueryString": "MSCK REPAIR TABLE sales_curated.orders",
        "WorkGroup": "lab9"
      },
      "Catch": [{ "ErrorEquals": ["States.ALL"], "ResultPath": "\$.error", "Next": "NotifyFailure" }],
      "Next": "NotifySuccess"
    },
    "NotifySuccess": {
      "Type": "Task",
      "Resource": "arn:aws:states:::sns:publish",
      "Parameters": { "TopicArn": "$TOPIC_ARN", "Message": "Orders pipeline succeeded." },
      "End": true
    },
    "NotifyFailure": {
      "Type": "Task",
      "Resource": "arn:aws:states:::sns:publish",
      "Parameters": {
        "TopicArn": "$TOPIC_ARN",
        "Message.$": "States.Format('Orders pipeline FAILED: {}', States.JsonToString(\$.error))"
      },
      "Next": "Failed"
    },
    "Failed": { "Type": "Fail" }
  }
}
EOF

SFN_ARN=$(aws stepfunctions create-state-machine \
  --name lab9-orders-pipeline \
  --definition file://pipeline.json \
  --role-arn "$SFN_ROLE_ARN" \
  --query stateMachineArn --output text)

The \$ escapes keep $.error literal inside the heredoc, while $TOPIC_ARN is filled in.

Success notifications: This lab sends one so you can see the pipeline work. In production, you’d usually drop it and alert on failures only (Part 8).

Run it:

EXEC_ARN=$(aws stepfunctions start-execution --state-machine-arn "$SFN_ARN" \
  --query executionArn --output text)

Open Step Functions in the console and watch the graph turn green, step by step.

✅ Checkpoint: The execution succeeds, and you receive the success email.


Step 9: Alarm on pipeline failures

aws cloudwatch put-metric-alarm \
  --alarm-name lab9-pipeline-failed \
  --alarm-description "The orders data pipeline failed" \
  --namespace AWS/States \
  --metric-name ExecutionsFailed \
  --dimensions Name=StateMachineArn,Value="$SFN_ARN" \
  --statistic Sum --period 300 --evaluation-periods 1 \
  --threshold 0 --comparison-operator GreaterThanThreshold \
  --treat-missing-data notBreaching \
  --alarm-actions "$TOPIC_ARN"

Now break it on purpose. Point the job at a table that doesn’t exist, and run the pipeline:

aws glue update-job --job-name lab9-raw-to-curated --job-update "{
  \"Role\": \"lab9-glue\",
  \"Command\": {\"Name\": \"glueetl\", \"ScriptLocation\": \"s3://$LAKE/scripts/raw_to_curated.py\", \"PythonVersion\": \"3\"},
  \"GlueVersion\": \"5.0\", \"WorkerType\": \"G.1X\", \"NumberOfWorkers\": 2,
  \"DefaultArguments\": {\"--RAW_DB\": \"orders_raw\", \"--RAW_TABLE\": \"does_not_exist\",
    \"--CURATED_PATH\": \"s3://$LAKE/curated/sales/orders/\", \"--enable-metrics\": \"true\"}
}"

aws stepfunctions start-execution --state-machine-arn "$SFN_ARN"

✅ Checkpoint: The Glue step fails, the pipeline’s NotifyFailure sends an email with the error, and a few minutes later the CloudWatch alarm fires too. The curated data is untouched — the failed job never overwrote it.

Fix the job by running the same update-job command with "--RAW_TABLE": "orders", then run the pipeline again.


Step 10 (optional): Refresh with new data

Generate more orders through the API (Phase 8, Step 6), then reload the raw zone and rerun the pipeline:

aws dms start-replication --replication-config-arn "$REPL_ARN" \
  --start-replication-type reload-target

# Wait until the reload finishes, then:
aws stepfunctions start-execution --state-machine-arn "$SFN_ARN"

Re-run the Athena and Redshift queries — the new orders appear. Because each step is idempotent, running the pipeline twice gives the same result, not double counts.

Going further: Schedule the pipeline daily with EventBridge Scheduler, switch DMS to full load + CDC with an Iceberg MERGE, add Glue Data Quality rules, and govern the curated database with Lake Formation (Part 10).


Cleanup

Work through this list — several of these resources bill by the hour.

# Redshift Serverless
aws redshift-serverless delete-workgroup --workgroup-name lab9
# wait until the workgroup is gone, then:
aws redshift-serverless delete-namespace --namespace-name lab9

# DMS
aws dms stop-replication --replication-config-arn "$REPL_ARN" 2>/dev/null
aws dms delete-replication-config --replication-config-arn "$REPL_ARN"
aws dms delete-endpoint --endpoint-arn "$SOURCE_EP"
aws dms delete-endpoint --endpoint-arn "$TARGET_EP"
aws dms delete-replication-subnet-group --replication-subnet-group-identifier lab9-dms-subnets
aws ec2 revoke-security-group-ingress --group-id "$DB_SG" \
  --protocol tcp --port 5432 --source-group "$DMS_SG"
aws ec2 delete-security-group --group-id "$DMS_SG"
aws secretsmanager delete-secret --secret-id "$DMS_SECRET_ARN" --force-delete-without-recovery

# Orchestration and alerts
aws cloudwatch delete-alarms --alarm-names lab9-pipeline-failed
aws stepfunctions delete-state-machine --state-machine-arn "$SFN_ARN"
aws sns delete-topic --topic-arn "$TOPIC_ARN"

# Glue and Athena
aws glue delete-job --job-name lab9-raw-to-curated
aws glue delete-crawler --name lab9-raw-orders
aws glue delete-database --name orders_raw
aws glue delete-database --name sales_curated
aws athena delete-work-group --work-group lab9 --recursive-delete-option

# IAM roles (inline policies first)
aws iam delete-role-policy --role-name lab9-pipeline --policy-name pipeline
aws iam delete-role --role-name lab9-pipeline
aws iam delete-role-policy --role-name lab9-redshift --policy-name lake-read
aws iam delete-role --role-name lab9-redshift
aws iam delete-role-policy --role-name lab9-glue --policy-name lake-access
aws iam detach-role-policy --role-name lab9-glue \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
aws iam delete-role --role-name lab9-glue
aws iam delete-role-policy --role-name lab9-dms-access --policy-name dms-access
aws iam delete-role --role-name lab9-dms-access

# The lake bucket and everything in it
aws s3 rb "s3://$LAKE" --force

Keep dms-vpc-role if you’ll use DMS again — it has no cost. Finally, if you’re done with the application stack too:

cd infra && terraform destroy

✅ Final checkpoint: No Redshift Serverless workgroups, DMS replications, Glue jobs, or lab9 resources remain.


Troubleshooting

DMS fails to connect to the source Cause: the database security group doesn’t allow the DMS security group, the secret is missing host or port, or SSL isn’t set. Fix: check the ingress rule, the secret’s keys (without printing values), and --ssl-mode require.

DMS can’t write to S3 Cause: the access role’s policy or trust. Fix: check s3:PutObject on raw/*, and that the trust allows dms.amazonaws.com.

The Glue job fails with AccessDeniedException on the catalog Cause: Lake Formation permissions are enforced in your account. Fix: grant lab9-glue permissions on the databases in Lake Formation, or use the default IAM-only settings in a sandbox.

Athena returns zero rows Cause: partitions aren’t registered. Fix: run MSCK REPAIR TABLE sales_curated.orders again.

Redshift COPY fails Cause: column count or types don’t match the Parquet files, or the role isn’t the namespace default. Fix: check sys_load_error_detail, and the namespace’s IAM roles.

Query exhausted resources or a query is cancelled in Athena Cause: the workgroup’s 1 GB limit. Fix: that’s the guardrail working — narrow the query.


What you accomplished

This is the data half of the capstone architecture.


Quick self-check

Test yourself before moving on:

  1. Why replicate with DMS instead of querying the orders database directly?
  2. What makes the Glue job safe to run twice?
  3. How can Athena and Redshift Spectrum query the same data without copying it?
  4. What stopped the broken run from wiping out curated data?

Answers

  1. Analytics queries would load the production OLTP database; DMS reads it once and lands a copy in the lake.
  2. It overwrites curated output from a full raw load — the same input always produces the same output.
  3. They share the Glue Data Catalog table pointing at the same S3 files.
  4. The job failed before writing, and the empty-result check refuses to publish nothing.

What’s next

Phase 10 zooms out to architecture: event-driven design, distributed systems patterns, high availability and disaster recovery, and cost and performance optimization.

Which part of the platform took you longest to get working? Tell me in the comments — I’ll add it to Troubleshooting.


Share this article

LinkedIn post draft