Why — The Problem This Solves

Every AWS account running EC2 instances has the same invisible problem: you cannot answer basic fleet questions without logging into dozens of consoles, running scripts, or waiting for a weekly report.

Questions like:

• Which servers are missing critical patches right now?
• How many Windows vs Linux instances are running in prod?
• What version of Java is installed across the fleet?
• Which instances haven't reported to SSM in the last 24 hours?

In a small environment you can click around the SSM console to answer these. In an enterprise with hundreds or thousands of EC2 instances across multiple accounts, that becomes impossible. The data exists — SSM is already collecting it — but it's fragmented: one JSON file per instance, stored in S3, with no way to query across all of it at once.

The Business Value

This platform turns a manual, hours-long process into a self-service, 30-minute automated pipeline:

What You Need to Know — Skills & Tools

This project is deliberately complex — it's Week 4 of an enterprise lab series. Before attempting it, you should be comfortable with the following:

Required AWS Knowledge

Required Engineering Skills

Concepts to Understand Before Starting

Architecture — How It Fits Together

Why Step Functions Instead of a Single Lambda?

Glue crawlers and ETL jobs run for minutes — sometimes 10–15 minutes for a large fleet. Lambda has a 15-minute maximum timeout, but more importantly, keeping a Lambda alive just to poll a job wastes money and is fragile. Step Functions' Wait states are free — the state machine pauses and resumes when the job finishes, using zero compute while waiting.

Why Two S3 Buckets?

The raw bucket holds everything SSM exports, unchanged. If the ETL logic ever needs to be corrected or the schema changes, we can reprocess from raw without re-running the SSM sync. The curated bucket holds clean, queryable Parquet — the "source of truth" for analysts. This separation is the foundation of every production data lake.

How We Built It — Step by Step

Step 1: Terraform Modules

Everything is infrastructure-as-code across six Terraform modules: s3, iam, glue, lambda, api-gateway, and step-functions. The dev environment wires them together via terraform/environments/dev/main.tf.

module "glue" {
  source              = "../../modules/glue"
  project_name        = var.project_name
  environment         = var.environment
  raw_bucket_name     = module.s3.raw_bucket_name
  curated_bucket_name = module.s3.curated_bucket_name
  glue_role_arn       = module.iam.glue_role_arn
}

Step 2: SSM Resource Data Sync

SSM Resource Data Sync is a built-in AWS feature that continuously exports EC2 fleet data (patch compliance, inventory, installed applications) to S3 as NDJSON files. One file per instance, organized in partition-style paths:

ssm/AWS:ComplianceSummary/
  accountid=684346483786/
    region=us-east-1/
      resourcetype=ManagedInstanceInventory/
        i-0abc123def456.json   ← one file per instance

Step 3: Glue Crawler

The Glue crawler scans the raw S3 bucket and registers the SSM data structures in the Glue Data Catalog. This creates queryable tables like aws_compliancesummary and aws_instancedetailedinformation — giving the ETL job a schema to work from.

Step 4: Glue ETL Job (PySpark + boto3)

The ETL job reads raw NDJSON files, transforms them into clean typed columns, and writes four Parquet tables to the curated bucket. The most important design decision: we use boto3 instead of Spark's native S3 reader to load the files.

def read_ssm_prefix(bucket, prefix):
    records = []
    paginator = s3_client.get_paginator("list_objects_v2")

    for page in paginator.paginate(Bucket=bucket, Prefix=prefix):
        for obj in page.get("Contents", []):
            if obj["Size"] == 0:
                continue
            key = obj["Key"]
            # Extract account + region from partition path
            acct_match   = re.search(r"accountid=([^/]+)", key)
            region_match = re.search(r"region=([^/]+)",   key)

            body   = s3_client.get_object(Bucket=bucket, Key=key)["Body"].read()
            record = json.loads(body)
            record["_account_id"] = acct_match.group(1)   if acct_match   else ""
            record["_region"]     = region_match.group(1) if region_match else ""
            records.append(record)

    return records   # list of dicts → spark.createDataFrame(records)

Step 5: Step Functions Orchestration

A Step Functions state machine ties the whole pipeline together. It polls the Glue crawler and ETL job using a Wait → Check → Choice loop rather than hard-coded timeouts — so the pipeline self-adapts to however long each step takes.

"WaitForCrawler": {
  "Type": "Wait",
  "Seconds": 30,
  "Next": "CheckCrawlerStatus"
},
"CheckCrawlerStatus": {
  "Type": "Task",
  "Resource": "${glue_trigger_arn}",
  "Parameters": {
    "action": "get_crawler_status",
    "ticket_id.$": "$.ticket_id"
  },
  "ResultPath": "$.crawler_status",
  "Next": "IsCrawlerReady"
},
"IsCrawlerReady": {
  "Type": "Choice",
  "Choices": [
    {
      "Variable": "$.crawler_status.ready",
      "BooleanEquals": true,
      "Next": "StartETLJob"
    }
  ],
  "Default": "WaitForCrawler"   ← loop back if not ready
}

Step 6: ServiceNow Webhook & Ticket Closure

The pipeline is triggered by a ServiceNow webhook. The webhook_receiver Lambda validates the HMAC-SHA256 signature on every inbound request before starting the Step Functions execution.

def verify_hmac(body: str, signature: str, secret: str) -> bool:
    expected = hmac.new(
        secret.encode(), body.encode(), hashlib.sha256
    ).hexdigest()
    return hmac.compare_digest(f"sha256={expected}", signature)

# In handler:
secret = ssm_client.get_parameter(
    Name=HMAC_SECRET_PARAM, WithDecryption=True
)["Parameter"]["Value"]

if not verify_hmac(body, signature, secret):
    return {"statusCode": 401, "body": json.dumps({"error": "Unauthorized"})}

When the pipeline finishes, the status_updater Lambda calls the ServiceNow REST API to close the RITM ticket with a direct Athena console link in the close notes.

Step 7: Querying with Athena

With Parquet data in S3 and tables registered in the Glue Data Catalog, Athena can query the full fleet for patch compliance and OS inventory in seconds.

SELECT
    compliance_status,
    COUNT(*)         AS instance_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM fleet.patch_compliance
GROUP BY compliance_status
ORDER BY instance_count DESC;

SELECT
    platform_name,
    platform_version,
    COUNT(*) AS instance_count
FROM fleet.inventory
GROUP BY platform_name, platform_version
ORDER BY instance_count DESC;

Challenges — What Actually Went Wrong

Every real build has friction. Here are the six issues that slowed us down and exactly how each was fixed.

Security — Controls at Every Layer

Cost — What This Actually Costs

This pipeline is designed to run on-demand (triggered by ServiceNow, not continuously). Here’s the cost breakdown per pipeline run in dev, based on a fleet of ~50 EC2 instances.

Cost Optimisation Tips

• Enable Glue job bookmarks to skip files already processed — reduces ETL run time significantly after the first run
• Use G.025X worker type instead of G.1X for small fleets (<500 instances) — 4× cheaper
• Schedule SSM Resource Data Sync to run every 6 hours instead of continuously to reduce S3 write costs
• Set S3 Intelligent-Tiering on the curated bucket after 30 days

Cleanup — Tearing It Down

Everything except SSM Resource Data Sync and S3 bucket contents is managed by Terraform and can be destroyed in one command.

cd terraform/environments/dev
terraform destroy -var-file=terraform.tfvars

# Expected output:
# Destroy complete! Resources: 44 destroyed.

Takeaways — What I’d Do Differently

Six lessons from this build that apply beyond this specific project.