Medallion Architecture Data Pipeline

Build a Bronze/Silver/Gold data lake pipeline on AWS with Glue and Athena.

Introduction

30 Second Summary

Every online order you place generates data. That data needs to flow through multiple stages of cleaning and transformation before anyone can use it for business reports or machine learning predictions.

In this project, you will build a serverless data pipeline on AWS that ingests e-commerce data from multiple sources, transforms it through industry-standard quality layers, and delivers it ready for analytics and machine learning.

What You'll Build

A production-grade data pipeline using the medallion architecture pattern on AWS, processing CSV order data and JSON clickstream logs through Bronze, Silver, and Gold layers using AWS Glue ETL, orchestrated by AWS Step Functions, and queryable via Amazon Athena.

By the end of this project, you'll have:

  • An S3-based data lake with a proper medallion layer structure (Bronze, Silver, Gold) and partitioned Parquet output optimized for analytics.
  • Automated PySpark ETL jobs in AWS Glue that clean, deduplicate, and aggregate multi-source data through each layer.
  • A fully orchestrated pipeline using AWS Step Functions that coordinates crawlers and ETL jobs end-to-end, with Gold layer results queryable in Amazon Athena.
  • Secret Mission: Add automated data quality validation and event-driven triggers so your pipeline runs itself when new data lands.

Are there any prerequisites?

You will need an AWS account with admin access, basic Python knowledge, and familiarity with SQL. The project uses the AWS Management Console and AWS CLI, both of which you will set up in Step 1.

Set Up AWS Environment and CLI

Before you can build a data pipeline, you need a way to talk to AWS from your terminal. The AWS CLI lets you create and manage AWS resources with commands instead of clicking through the console.

In this step, you will install the AWS CLI, configure it with your credentials, and create the IAM role that your pipeline services will use to access other AWS resources.

In this step, get ready to:
  • Install and configure the AWS CLI with your credentials.
  • Create an IAM role for AWS Glue and Step Functions.
  • Verify your setup by listing S3 buckets from the command line.
Install and configure the AWS CLI

The AWS CLI is a command-line tool that lets you interact with AWS services directly from your terminal. You need it installed and configured before you can create any resources.

  • Check if the AWS CLI is already installed by running this command:
aws --version

✔️ I see a version number

The AWS CLI is already installed. Move on to configuring your credentials below.

ⓧ Command not found

You need to install the AWS CLI. Follow the instructions for your operating system.

macOS

  • Download the macOS installer by opening this URL in your browser:
  • https://awscli.amazonaws.com/AWSCLIV2.pkg
  • Run the downloaded .pkg file and follow the on-screen instructions.
  • When the installer finishes, close and reopen your terminal.
  • Confirm the install worked by running:
aws --version

You should see a version string starting with aws-cli/2.

Windows

  • Download the Windows installer by opening this URL in your browser:
  • https://awscli.amazonaws.com/AWSCLIV2.msi
  • Run the downloaded .msi file and follow the on-screen instructions.
  • When the installer finishes, close and reopen your command prompt.
  • Confirm the install worked by running:
aws --version

You should see a version string starting with aws-cli/2.

Now configure the CLI with your AWS credentials so it can authenticate requests on your behalf.

  • Run the configuration command:
aws configure

What does this command do?

This command prompts you for four values that it saves to your local machine. These credentials allow the CLI to make authenticated requests to AWS on your behalf.

  • When prompted, enter your AWS Access Key ID and press Enter.
  • Enter your AWS Secret Access Key and press Enter.
  • Enter us-east-1 for the default region name.
  • Enter json for the default output format.

Where do I find my access keys?

In the AWS Management Console, click your username in the top right corner, then select Security credentials. Scroll down to Access keys and create a new key pair if you do not have one.

Copy the Access Key ID and Secret Access Key when they appear. The secret key is shown only once.

Getting a credentials error?

Make sure you copied the full access key ID and secret without extra spaces. If you accidentally pressed Enter too early, run aws configure again to overwrite the saved values.

help me fix my AWS CLI credentials configuration

Create the IAM role for pipeline services

AWS services like AWS Glue and Step Functions need permission to access other resources in your account. You grant these permissions by creating an IAM role with a trust policy that lets those services assume the role.

  • Create a file called trust-policy.json on your Desktop by running this command:
cd ~/Desktop
  • Create the trust policy file by running this command:
cat > trust-policy.json << 'EOF'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "glue.amazonaws.com",
          "states.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
EOF

What is a trust policy?

A trust policy defines which AWS services are allowed to "assume" this role. Here, you are telling AWS that both glue.amazonaws.com and states.amazonaws.com (Step Functions) can use this role's permissions.

  • Create the IAM role named medallion-pipeline-role by running this command:
aws iam create-role \
  --role-name medallion-pipeline-role \
  --assume-role-policy-document file://trust-policy.json

You should see JSON output showing the new role's ARN and creation details.

  • Attach the S3 full access policy by running:
aws iam attach-role-policy \
  --role-name medallion-pipeline-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess
  • Attach the Glue service role policy by running:
aws iam attach-role-policy \
  --role-name medallion-pipeline-role \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
  • Attach the Athena full access policy by running:
aws iam attach-role-policy \
  --role-name medallion-pipeline-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonAthenaFullAccess
  • Attach the Step Functions full access policy by running:
aws iam attach-role-policy \
  --role-name medallion-pipeline-role \
  --policy-arn arn:aws:iam::aws:policy/AWSStepFunctionsFullAccess

What do these policies grant?

AmazonS3FullAccess lets the role read and write to any S3 bucket. AWSGlueServiceRole grants permissions for Glue crawlers and ETL jobs to run.

AmazonAthenaFullAccess enables the role to run queries in Athena. AWSStepFunctionsFullAccess allows orchestrating state machines.

  • Verify the policies are attached by running:
aws iam list-attached-role-policies --role-name medallion-pipeline-role

You should see all four policies listed in the JSON output.

Seeing an access denied error?

Your IAM user needs permission to create roles and attach policies. Make sure you are using an account with admin access. If you see a permissions error, check that your aws configure credentials belong to a user with the AdministratorAccess policy.

help me fix IAM permission errors when creating a role

Verify your setup by listing S3 buckets

A quick way to confirm that your CLI is correctly configured is to run a simple AWS command.

  • List your existing S3 buckets by running:
aws s3 ls

You should see a list of any existing buckets in your account, or an empty response if this is a fresh account. Either result confirms your CLI is working.

Command timed out or returned an error?

Double-check that your access key and secret key are correct by running aws configure again. Also make sure your system clock is accurate. AWS rejects requests with timestamps that are too far off.

help me debug why aws s3 ls is failing

Your AWS environment is ready. Next up, you will create the S3 data lake structure with Bronze, Silver, and Gold layers.

Create the Data Lake Structure on S3

Your IAM role and CLI are ready. Now you need a physical home for the data that flows through the pipeline.

In this step, you will create the Amazon S3 bucket structure that mirrors the medallion architecture layers. You will also upload sample e-commerce data to the Bronze layer so the ETL jobs have something to process later.

In this step, get ready to:
  • Create the S3 data lake bucket with medallion layer prefixes.
  • Upload sample e-commerce data to the Bronze layer.
  • Create a separate bucket for Athena query results.
Create the data lake bucket with layer prefixes

The medallion architecture organizes data into progressive quality layers. Each layer lives under its own prefix (folder) inside a single S3 bucket.

  • Create your data lake bucket by running this command:
aws s3 mb s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]] --region us-east-1

Why include your account ID in the bucket name?

S3 bucket names must be globally unique across all AWS accounts. Including your 12-digit account ID guarantees no one else has already taken the name.

You can find your account ID by running aws sts get-caller-identity and looking at the Account field.

  • Verify the bucket was created by running:
aws s3 ls | grep medallion-datalake

You should see your new bucket listed with today's date.

Don't see your bucket?

  • Make sure the bucket name is all lowercase with no spaces or underscores. S3 bucket names only allow lowercase letters, numbers, and hyphens.
  • Check that your AWS CLI region matches the region you specified. Run aws configure get region to confirm.
  • If you see a "BucketAlreadyExists" error, try a different account ID value or append a short random string.

Help me troubleshoot my S3 bucket creation error.

Now you need to create the empty Silver and Gold layer prefixes. Bronze prefixes will be created automatically when you upload data in the next substep.

  • Create the Silver and Gold folder markers by running these commands:
aws s3api put-object --bucket medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]] --key silver/
aws s3api put-object --bucket medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]] --key gold/

What does this command do?

S3 does not have real folders. Instead, it uses key prefixes to simulate a folder structure. By creating zero-byte objects with a trailing slash (silver/ and gold/), you create visible folder markers in the S3 console.

  • Confirm the layer structure by listing the bucket contents:
aws s3 ls s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]]/

You should see gold/ and silver/ listed as prefixes (PRE).

Upload sample source data to the Bronze layer

The Bronze layer stores raw data exactly as it arrives from source systems. You will upload two types of e-commerce data: CSV order records and JSON clickstream events.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a project folder and move into it by running these commands:
mkdir medallion-pipeline
cd medallion-pipeline
  • Create a file named sample_orders.csv inside your medallion-pipeline folder. Open it in your text editor and paste the following content:
order_id,customer_id,customer_email,order_date,order_amount,quantity,product_category,payment_method
ORD-001,CUST-101,alice@example.com,2024-01-15,129.99,2,Electronics,credit_card
ORD-002,CUST-102,bob@example.com,2024-01-15,49.50,1,Books,debit_card
ORD-003,CUST-101,alice@example.com,2024-01-16,89.00,3,Clothing,credit_card
ORD-004,CUST-103,carol@example.com,2024-01-16,210.00,1,Electronics,paypal
ORD-005,CUST-104,dave@example.com,2024-01-17,35.75,2,Books,credit_card
ORD-006,CUST-102,bob@example.com,2024-01-17,150.00,1,Home,debit_card
ORD-007,CUST-105,eve@example.com,2024-01-18,72.50,4,Clothing,credit_card
ORD-008,CUST-101,alice@example.com,2024-01-18,299.99,1,Electronics,credit_card
ORD-001,CUST-101,alice@example.com,2024-01-15,129.99,2,Electronics,credit_card
ORD-009,CUST-106,frank@example.com,2024-01-19,45.00,2,Books,paypal
ORD-010,CUST-103,carol@example.com,2024-01-19,180.00,3,Home,credit_card

What is in this data?

This CSV contains 11 rows of simulated e-commerce order data. Notice that ORD-001 appears twice. This intentional duplicate lets you confirm the deduplication logic works when you build the Bronze-to-Silver ETL job later.

  • Save sample_orders.csv.
  • Create a second file named sample_clickstream.json in the same medallion-pipeline folder. Paste the following content:
[
  {"event_id": "EVT-001", "user_id": "CUST-101", "event_timestamp": "2024-01-15T10:23:45", "event_date": "2024-01-15", "page_url": "/products/electronics", "traffic_source": "google_ads", "session_duration_sec": 245},
  {"event_id": "EVT-002", "user_id": "CUST-102", "event_timestamp": "2024-01-15T11:05:12", "event_date": "2024-01-15", "page_url": "/products/books", "traffic_source": "organic_search", "session_duration_sec": 180},
  {"event_id": "EVT-003", "user_id": "CUST-103", "event_timestamp": "2024-01-16T09:45:30", "event_date": "2024-01-16", "page_url": "/checkout", "traffic_source": "direct", "session_duration_sec": 320},
  {"event_id": "EVT-004", "user_id": "CUST-104", "event_timestamp": "2024-01-17T14:20:00", "event_date": "2024-01-17", "page_url": "/products/books", "traffic_source": "social_media", "session_duration_sec": 95},
  {"event_id": "EVT-005", "user_id": "CUST-105", "event_timestamp": "2024-01-18T16:30:22", "event_date": "2024-01-18", "page_url": "/products/clothing", "traffic_source": "email_campaign", "session_duration_sec": 410},
  {"event_id": "EVT-006", "user_id": "CUST-101", "event_timestamp": "2024-01-18T17:10:05", "event_date": "2024-01-18", "page_url": "/cart", "traffic_source": "google_ads", "session_duration_sec": 275},
  {"event_id": "EVT-007", "user_id": "CUST-106", "event_timestamp": "2024-01-19T08:55:18", "event_date": "2024-01-19", "page_url": "/products/books", "traffic_source": "organic_search", "session_duration_sec": 150},
  {"event_id": "EVT-008", "user_id": "CUST-103", "event_timestamp": "2024-01-19T12:40:33", "event_date": "2024-01-19", "page_url": "/checkout", "traffic_source": "direct", "session_duration_sec": 360},
  {"event_id": "EVT-001", "user_id": "CUST-101", "event_timestamp": "2024-01-15T10:23:45", "event_date": "2024-01-15", "page_url": "/products/electronics", "traffic_source": "google_ads", "session_duration_sec": 245}
]

What is in this data?

This JSON array contains 9 clickstream events simulating user browsing behaviour on an e-commerce site. Each event records which page the user visited, where they came from (traffic source), and how long they stayed.

Like the orders file, EVT-001 appears twice as a deliberate duplicate for the ETL job to clean.

  • Save sample_clickstream.json.
  • Confirm both files exist by running:
ls

You should see sample_clickstream.json and sample_orders.csv in the output.

  • Upload both files to the Bronze layer by running these commands:
aws s3 cp sample_orders.csv s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]]/bronze/orders/
aws s3 cp sample_clickstream.json s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]]/bronze/clickstream/

What does this command do?

The aws s3 cp command copies a local file to an S3 path. The destination path bronze/orders/ and bronze/clickstream/ creates the Bronze layer prefix structure automatically.

  • Verify the uploads by listing the Bronze layer contents:
aws s3 ls s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-aws-account-id"]]/bronze/ --recursive

You should see both files listed with their sizes: bronze/orders/sample_orders.csv and bronze/clickstream/sample_clickstream.json.

Upload failed or files missing?

  • Make sure you are in the medallion-pipeline folder. Run pwd to check your current directory.
  • Double check the bucket name matches exactly what you created earlier, including your account ID.
  • If you see an "Access Denied" error, confirm your IAM user has S3 write permissions by checking your CLI credentials.

Help me troubleshoot my S3 upload error.

Create the Athena query results bucket

Amazon Athena requires a separate S3 location to store query results. This keeps analytics output cleanly separated from your data lake.

  • Create the Athena results bucket by running this command:
aws s3 mb s3://medallion-athena-results-[[ACCOUNT_ID="your-12-digit-aws-account-id"]] --region us-east-1

Why a separate bucket for query results?

Athena writes query results (CSV output, metadata) to S3 every time you run a query. Using a dedicated bucket prevents these outputs from mixing with your curated data lake layers.

  • Verify the bucket was created:
aws s3 ls | grep medallion-athena

You should see medallion-athena-results- followed by your account ID in the output.

Bucket creation failed?

  • Ensure the bucket name uses the same account ID as your data lake bucket.
  • Confirm the --region flag matches the region you used for the data lake bucket.

Help me fix my Athena results bucket creation error.

Your data lake foundation is in place. You have a structured S3 bucket with raw data sitting in the Bronze layer, ready to be discovered and cataloged. Next up, you will configure AWS Glue to automatically detect the schema of your data files.

Configure AWS Glue Data Catalog

Your raw data is sitting in S3, but it's just files. No tool knows what columns exist, what types they are, or how to query them.

The AWS Glue Data Catalog solves this by maintaining a central metadata repository. In this step, you will create databases for each medallion layer and run a Crawler to automatically discover the schemas of your Bronze data.

In this step, get ready to:
  • Create three Glue Data Catalog databases for each medallion layer.
  • Create and run a Glue Crawler on the Bronze layer.
  • Verify the discovered tables and inspect their schemas.
Create the Data Catalog databases

Each medallion layer needs its own logical database in the Data Catalog. This keeps Bronze, Silver, and Gold metadata separate and queryable independently.

  • Create the Bronze database by running this command:
aws glue create-database --database-input '{"Name": "bronze_db"}'
  • Create the Silver database by running:
aws glue create-database --database-input '{"Name": "silver_db"}'
  • Create the Gold database by running:
aws glue create-database --database-input '{"Name": "gold_db"}'

What do these commands do?

Each command creates a logical container in the Glue Data Catalog. The --database-input flag takes a JSON object with a Name field. Glue stores database names in lowercase for Hive compatibility.

These databases do not hold data themselves. They hold metadata (table definitions, column names, types) that points back to S3 locations.

  • Verify all three databases were created by running:
aws glue get-databases --query 'DatabaseList[].Name'

You should see bronze_db, silver_db, and gold_db in the output.

Not seeing all three databases?

Make sure you ran all three create-database commands. Each command produces no output on success, so it is easy to miss one.

If you see an AlreadyExistsException, the database was already created in a previous attempt. That is fine.

Help me troubleshoot my Glue Data Catalog database creation.

Create and run the Bronze layer Crawler

A Glue Crawler scans data in S3, detects its format (CSV, JSON, Parquet), infers column names and types, and registers tables in the Data Catalog automatically.

  • Create the Bronze Crawler by running this command:
aws glue create-crawler \
  --name bronze-crawler \
  --role medallion-pipeline-role \
  --database-name bronze_db \
  --targets '{"S3Targets": [{"Path": "s3://[[S3_BUCKET="medallion-datalake-YOUR-ACCOUNT-ID"]]/bronze/"}]}'

What does this command do?

  • --name bronze-crawler gives the crawler a name you will reference later in Step Functions.
  • --role medallion-pipeline-role tells the crawler which IAM role to use for S3 access.
  • --database-name bronze_db tells the crawler where to write discovered table metadata.
  • --targets points the crawler at the bronze/ prefix. It will discover each subfolder (orders/ and clickstream/) as a separate table.
  • Start the crawler by running:
aws glue start-crawler --name bronze-crawler

The crawler takes about 1-2 minutes to scan your files and register the tables.

  • Check the crawler's status by running:
aws glue get-crawler --name bronze-crawler --query 'Crawler.State'

Wait until the output shows READY. If it still says RUNNING, wait 30 seconds and run the command again.

Crawler stuck in RUNNING state?

Glue Crawlers typically finish within 2 minutes for small datasets. If yours is still running after 5 minutes, check that the S3 bucket path is correct and that the IAM role has S3 access.

Help me debug why my Glue Crawler is not finishing.

Verify the discovered tables and inspect schemas

Once the crawler state is READY, it has finished registering tables in bronze_db.

  • List the tables the crawler created by running:
aws glue get-tables --database-name bronze_db --query 'TableList[].Name'

You should see two tables: orders and clickstream.

  • Inspect the schema of the orders table by running:
aws glue get-table --database-name bronze_db --name orders --query 'Table.StorageDescriptor.Columns'

What should I see?

The output shows an array of columns the crawler inferred from your CSV header row. You should see columns like order_id, customer_id, customer_email, order_date, order_amount, quantity, product_category, and payment_method.

The crawler infers all CSV columns as string type by default. This is expected. The Bronze-to-Silver ETL job in the next step will cast them to proper types (DoubleType, IntegerType, timestamps).

  • Inspect the clickstream table schema by running:
aws glue get-table --database-name bronze_db --name clickstream --query 'Table.StorageDescriptor.Columns'

You should see columns including event_id, user_id, event_timestamp, event_date, page_url, traffic_source, and session_duration_sec.

Why does schema-on-read matter?

This approach is called schema-on-read. The raw data stays untouched in S3 (the Bronze layer), while the Data Catalog holds metadata about its structure. You can query it immediately via Athena without transforming it first.

The real value comes in the next steps, where you use these catalog entries as inputs to ETL jobs that clean, cast, and aggregate the data into Silver and Gold.

Seeing only one table instead of two?

Make sure both sample_orders.csv and sample_clickstream.json were uploaded to their correct prefixes (bronze/orders/ and bronze/clickstream/) in the previous step. The crawler creates one table per subfolder.

If a file was uploaded to the wrong prefix, move it and re-run the crawler with aws glue start-crawler --name bronze-crawler.

Help me figure out why my Glue Crawler only created one table.

Your Data Catalog is configured and the Bronze layer schemas are registered. Next up, you will build the ETL job that reads this raw data and transforms it into clean, typed Parquet in the Silver layer.

Build the Bronze-to-Silver ETL Job

Your Bronze layer now holds raw data from two sources, but raw data is messy. The orders CSV has duplicate rows, untyped columns, and inconsistent formatting.

The Silver layer is where you clean and conform this data. In this step, you will write a PySpark ETL script that deduplicates records, casts types, standardizes timestamps, and outputs compressed Parquet files. Then you will register it as an AWS Glue ETL job and run it.

In this step, get ready to:
  • Write the Bronze-to-Silver PySpark ETL script and upload it to S3.
  • Create and configure the AWS Glue ETL job with proper worker settings.
  • Run the job and verify Parquet output in the Silver layer.
Write the Bronze-to-Silver ETL script

The ETL script reads raw CSV and JSON from the Bronze layer, cleans both datasets, and writes the results to the Silver layer as partitioned Parquet.

  • Create a file called bronze_to_silver.py on your local machine with the following content:
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import col, to_timestamp, trim, lower, lit, current_timestamp
from pyspark.sql.types import DoubleType, IntegerType

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'S3_BUCKET'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

bucket = args['S3_BUCKET']

# Read raw orders from Bronze (CSV)
orders_df = spark.read.option("header", "true").csv(f"s3://{bucket}/bronze/orders/")

# Read raw clickstream from Bronze (JSON)
clickstream_df = spark.read.json(f"s3://{bucket}/bronze/clickstream/")

# Clean orders: deduplicate, cast types, handle nulls, standardize timestamps
orders_clean = (
    orders_df
    .dropDuplicates(["order_id"])
    .filter(col("order_id").isNotNull())
    .withColumn("order_amount", col("order_amount").cast(DoubleType()))
    .withColumn("quantity", col("quantity").cast(IntegerType()))
    .withColumn("order_date", to_timestamp(col("order_date"), "yyyy-MM-dd"))
    .withColumn("customer_email", trim(lower(col("customer_email"))))
    .withColumn("processed_at", current_timestamp())
    .filter(col("order_amount") > 0)
)

# Clean clickstream: deduplicate, cast types, standardize
clickstream_clean = (
    clickstream_df
    .dropDuplicates(["event_id"])
    .filter(col("event_id").isNotNull())
    .withColumn("event_timestamp", to_timestamp(col("event_timestamp")))
    .withColumn("session_duration_sec", col("session_duration_sec").cast(IntegerType()))
    .withColumn("processed_at", current_timestamp())
)

# Write to Silver layer as partitioned Parquet
orders_clean.write.mode("overwrite").partitionBy("order_date").parquet(f"s3://{bucket}/silver/orders/")
clickstream_clean.write.mode("overwrite").partitionBy("event_date").parquet(f"s3://{bucket}/silver/clickstream/")

job.commit()

What does this script do?

The script performs four stages of data cleaning on each source:

  • Deduplication removes duplicate rows based on primary key (order_id or event_id). Your sample data intentionally includes duplicates to prove this works.
  • Null filtering drops rows with missing primary keys so downstream queries never fail on nulls.
  • Type casting converts string columns to proper numeric and timestamp types so Athena can perform aggregations.
  • Partitioned Parquet output writes the cleaned data in columnar format, partitioned by date. This reduces query costs in Athena by up to 99% compared to scanning raw text files.
  • Upload the script to your S3 bucket's scripts folder by running this command:
aws s3 cp bronze_to_silver.py s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/scripts/bronze_to_silver.py
  • Verify the upload succeeded by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/scripts/

You should see bronze_to_silver.py listed in the output.

Upload failed or file not found?

  • Make sure you are running the command from the same directory where you saved bronze_to_silver.py.
  • Double-check your bucket name matches the one you created in Step 2.
  • Confirm your AWS CLI is configured with credentials that have S3 write access.

Help me debug why my S3 upload is failing for my Glue ETL script.

Create the AWS Glue ETL job

Now you will register the script as a Glue ETL job with the right compute settings. The G.1X worker type gives each worker 4 vCPUs and 16 GB of memory, which is plenty for this dataset.

  • Create the Glue ETL job by running this command:
aws glue create-job \
    --name bronze-to-silver-etl \
    --role medallion-pipeline-role \
    --command '{"Name":"glueetl","ScriptLocation":"s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/scripts/bronze_to_silver.py","PythonVersion":"3"}' \
    --glue-version "4.0" \
    --number-of-workers 2 \
    --worker-type "G.1X" \
    --default-arguments '{"--job-bookmark-option":"job-bookmark-enable","--S3_BUCKET":"[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]"}'

What do these settings mean?

  • --glue-version "4.0" uses Spark 3.3.0 with Python 3.10, the latest recommended Glue version.
  • --worker-type "G.1X" allocates 1 DPU per worker (4 vCPUs, 16 GB memory, 94 GB disk).
  • --number-of-workers 2 is the minimum for a Glue ETL job. For small datasets like ours, this is sufficient.
  • --job-bookmark-option set to job-bookmark-enable means subsequent runs only process new data, avoiding reprocessing.
  • --S3_BUCKET passes your bucket name into the script as a runtime parameter via getResolvedOptions.
  • Verify the job was created by running:
aws glue get-job --job-name bronze-to-silver-etl

You should see a JSON response containing your job definition with the name bronze-to-silver-etl, the script location, and all the settings you specified.

Seeing an EntityNotFoundException?

  • If the create-job command failed silently, check that the IAM role name medallion-pipeline-role matches the role you created in Step 1.
  • Ensure the script location S3 path is correct and the file actually exists there.
  • On Windows, you may need to escape the JSON differently. Replace single quotes with double quotes and escape internal double quotes with a backslash.

Help me debug why my aws glue create-job command is failing.

Run the job and verify the Silver layer output

The job is configured. Now you will trigger a run and confirm it produces clean Parquet files in your Silver layer.

  • Start the job run by running this command:
aws glue start-job-run --job-name bronze-to-silver-etl

What happens next?

This command returns a JobRunId immediately. The job itself takes approximately 3-5 minutes to complete because Glue needs to spin up the Spark cluster, run your script, and tear down.

At $0.44 per DPU-hour with 2 G.1X workers, a 5-minute run costs roughly $0.07.

  • Check the job run status by running this command (it may take a few minutes to complete):
aws glue get-job-runs --job-name bronze-to-silver-etl

Look for "JobRunState": "SUCCEEDED" in the output. If the state shows RUNNING, wait another minute and run the command again.

  • Once the job succeeds, verify the Silver layer has Parquet files by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/silver/orders/ --recursive

You should see Parquet files organized in folders named by order_date partition values.

  • Also verify the clickstream output by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/silver/clickstream/ --recursive

You should see Parquet files partitioned by event_date.

Job state shows FAILED?

  • Run aws glue get-job-runs --job-name bronze-to-silver-etl and look for the ErrorMessage field. It will tell you exactly what went wrong.
  • An Access Denied error means your IAM role is missing S3 permissions. Verify medallion-pipeline-role has the AmazonS3FullAccess policy attached.
  • A script error (Python exception) usually means the bucket name in --S3_BUCKET does not match your actual bucket name. Double-check the --default-arguments you passed during create-job.

Help me debug why my Glue ETL job bronze-to-silver-etl is failing.

Why Parquet instead of CSV for the Silver layer?

Parquet is a columnar storage format that compresses data dramatically. AWS benchmarks show Parquet provides 87% less storage, 34x faster queries, and 99% less data scanned in Athena compared to text files like CSV.

When Athena queries your Gold layer later, it only reads the columns it needs instead of scanning entire rows. Partitioning by date adds another optimization. Athena skips entire folders that fall outside your query's date range.

✔️ Awesome, I've got everything!

Great. Double check you have Parquet files in both silver/orders/ and silver/clickstream/ before moving on.

ⓧ I'd like to double check the full code

Here is the complete bronze_to_silver.py script. Make sure yours matches exactly:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import col, to_timestamp, trim, lower, lit, current_timestamp
from pyspark.sql.types import DoubleType, IntegerType

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'S3_BUCKET'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

bucket = args['S3_BUCKET']

# Read raw orders from Bronze (CSV)
orders_df = spark.read.option("header", "true").csv(f"s3://{bucket}/bronze/orders/")

# Read raw clickstream from Bronze (JSON)
clickstream_df = spark.read.json(f"s3://{bucket}/bronze/clickstream/")

# Clean orders: deduplicate, cast types, handle nulls, standardize timestamps
orders_clean = (
    orders_df
    .dropDuplicates(["order_id"])
    .filter(col("order_id").isNotNull())
    .withColumn("order_amount", col("order_amount").cast(DoubleType()))
    .withColumn("quantity", col("quantity").cast(IntegerType()))
    .withColumn("order_date", to_timestamp(col("order_date"), "yyyy-MM-dd"))
    .withColumn("customer_email", trim(lower(col("customer_email"))))
    .withColumn("processed_at", current_timestamp())
    .filter(col("order_amount") > 0)
)

# Clean clickstream: deduplicate, cast types, standardize
clickstream_clean = (
    clickstream_df
    .dropDuplicates(["event_id"])
    .filter(col("event_id").isNotNull())
    .withColumn("event_timestamp", to_timestamp(col("event_timestamp")))
    .withColumn("session_duration_sec", col("session_duration_sec").cast(IntegerType()))
    .withColumn("processed_at", current_timestamp())
)

# Write to Silver layer as partitioned Parquet
orders_clean.write.mode("overwrite").partitionBy("order_date").parquet(f"s3://{bucket}/silver/orders/")
clickstream_clean.write.mode("overwrite").partitionBy("event_date").parquet(f"s3://{bucket}/silver/clickstream/")

job.commit()

Your Silver layer is live. The raw, messy data from Bronze is now clean, typed, and stored as efficient Parquet. Next up, you will build the Silver-to-Gold ETL job that joins these datasets together and computes business aggregations.

Build the Silver-to-Gold ETL Job

Your Silver layer now holds cleaned, deduplicated data in efficient Parquet format. The next challenge is turning this conformed data into business-ready insights.

The Gold layer is where raw records become actionable analytics. In this step, you will build a second AWS Glue ETL job that aggregates Silver data into three Gold tables: daily revenue summaries, customer lifetime value, and traffic source conversion metrics.

In this step, get ready to:
  • Create and run a Silver-to-Gold ETL job that computes business aggregations.
  • Verify the Gold layer contains analytics-ready Parquet data.
  • Run a Glue Crawler on the Gold layer to register tables in the Data Catalog.
Create the Silver-to-Gold ETL script

This second ETL job reads the cleaned Parquet data from the Silver layer, computes aggregations using PySpark groupBy and window functions, and writes three separate Gold tables.

  • Create a file called silver_to_gold.py on your local machine with the following content:
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import (
    col, sum as _sum, count, countDistinct,
    avg, year, month, dayofmonth, round as _round
)
from pyspark.sql.window import Window

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'S3_BUCKET'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

bucket = args['S3_BUCKET']

What does this setup do?

This is the same boilerplate pattern as the Bronze-to-Silver job. It initializes a Spark session through the Glue context and reads the S3_BUCKET parameter so the script knows which bucket to read from and write to.

The imports are different this time. Functions like _sum, count, countDistinct, and avg are aggregation functions. The year, month, and dayofmonth functions extract date parts for partitioning.

  • Next, add the code that reads the Silver layer data. Paste this below the setup code:
# Read cleaned data from Silver layer
orders_df = spark.read.parquet(f"s3://{bucket}/silver/orders/")
clickstream_df = spark.read.parquet(f"s3://{bucket}/silver/clickstream/")

Why read Parquet here instead of CSV?

The Silver layer stores data as Parquet, which preserves the data types and schema from the Bronze-to-Silver cleaning. Reading Parquet is significantly faster than re-parsing CSV because Spark can skip irrelevant columns and read only the data it needs.

  • Add the first Gold table aggregation. This computes daily revenue metrics. Paste this below the Silver read:
# Gold Table 1: Daily Revenue Summary
daily_revenue = (
    orders_df
    .withColumn("year", year(col("order_date")))
    .withColumn("month", month(col("order_date")))
    .withColumn("day", dayofmonth(col("order_date")))
    .groupBy("year", "month", "day")
    .agg(
        _round(_sum("order_amount"), 2).alias("total_revenue"),
        count("order_id").alias("total_orders"),
        countDistinct("customer_id").alias("unique_customers"),
        _round(avg("order_amount"), 2).alias("avg_order_value")
    )
)

What does this aggregation produce?

This groups all orders by date and computes four KPIs per day: total revenue, number of orders, unique customers, and average order value. The year, month, and day columns are extracted first so the output can be partitioned by time for efficient querying.

  • Add the second Gold table. This computes customer lifetime value. Paste below the daily revenue block:
# Gold Table 2: Customer Lifetime Value
customer_ltv = (
    orders_df
    .groupBy("customer_id", "customer_email")
    .agg(
        _round(_sum("order_amount"), 2).alias("lifetime_value"),
        count("order_id").alias("total_orders"),
        _round(avg("order_amount"), 2).alias("avg_order_value")
    )
)

What is customer lifetime value?

Customer lifetime value (LTV) is a business metric that sums all purchases a customer has ever made. This table groups by customer_id and computes how much each customer has spent in total, how many orders they placed, and their average order size.

  • Add the third Gold table and the write operations. This computes traffic conversion metrics and writes all three tables to S3. Paste this at the end of your file:
# Gold Table 3: Traffic Source Conversion
conversions = (
    clickstream_df
    .groupBy("traffic_source")
    .agg(
        count("event_id").alias("total_sessions"),
        countDistinct("user_id").alias("unique_visitors"),
        _round(avg("session_duration_sec"), 1).alias("avg_session_duration_sec")
    )
)

# Write Gold tables as partitioned Parquet
daily_revenue.write.mode("overwrite").partitionBy("year", "month").parquet(f"s3://{bucket}/gold/daily_revenue/")
customer_ltv.write.mode("overwrite").parquet(f"s3://{bucket}/gold/customer_ltv/")
conversions.write.mode("overwrite").parquet(f"s3://{bucket}/gold/traffic_conversions/")

job.commit()

Why is daily_revenue partitioned but the others are not?

The daily revenue table is partitioned by year and month because analysts typically query revenue data for specific time periods. Partitioning lets Athena skip entire folders of irrelevant months when running queries.

The customer LTV and traffic conversion tables are small enough that partitioning provides no benefit. They are written as flat Parquet files.

  • Save silver_to_gold.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before uploading.

ⓧ I'd like to double check the full code

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import (
    col, sum as _sum, count, countDistinct,
    avg, year, month, dayofmonth, round as _round
)
from pyspark.sql.window import Window

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'S3_BUCKET'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

bucket = args['S3_BUCKET']

# Read cleaned data from Silver layer
orders_df = spark.read.parquet(f"s3://{bucket}/silver/orders/")
clickstream_df = spark.read.parquet(f"s3://{bucket}/silver/clickstream/")

# Gold Table 1: Daily Revenue Summary
daily_revenue = (
    orders_df
    .withColumn("year", year(col("order_date")))
    .withColumn("month", month(col("order_date")))
    .withColumn("day", dayofmonth(col("order_date")))
    .groupBy("year", "month", "day")
    .agg(
        _round(_sum("order_amount"), 2).alias("total_revenue"),
        count("order_id").alias("total_orders"),
        countDistinct("customer_id").alias("unique_customers"),
        _round(avg("order_amount"), 2).alias("avg_order_value")
    )
)

# Gold Table 2: Customer Lifetime Value
customer_ltv = (
    orders_df
    .groupBy("customer_id", "customer_email")
    .agg(
        _round(_sum("order_amount"), 2).alias("lifetime_value"),
        count("order_id").alias("total_orders"),
        _round(avg("order_amount"), 2).alias("avg_order_value")
    )
)

# Gold Table 3: Traffic Source Conversion
conversions = (
    clickstream_df
    .groupBy("traffic_source")
    .agg(
        count("event_id").alias("total_sessions"),
        countDistinct("user_id").alias("unique_visitors"),
        _round(avg("session_duration_sec"), 1).alias("avg_session_duration_sec")
    )
)

# Write Gold tables as partitioned Parquet
daily_revenue.write.mode("overwrite").partitionBy("year", "month").parquet(f"s3://{bucket}/gold/daily_revenue/")
customer_ltv.write.mode("overwrite").parquet(f"s3://{bucket}/gold/customer_ltv/")
conversions.write.mode("overwrite").parquet(f"s3://{bucket}/gold/traffic_conversions/")

job.commit()
Upload the script, create the job, and run it

With the script ready, you need to upload it to S3 and register it as a Glue job. This follows the same pattern as the Bronze-to-Silver job you created in the previous step.

  • Upload the script to your data lake bucket by running this command:
aws s3 cp silver_to_gold.py s3://[[S3_BUCKET="medallion-datalake-your-account-id"]]/scripts/silver_to_gold.py
  • Create the Glue ETL job by running this command:
aws glue create-job \
    --name silver-to-gold-etl \
    --role medallion-pipeline-role \
    --command '{"Name":"glueetl","ScriptLocation":"s3://[[S3_BUCKET="medallion-datalake-your-account-id"]]/scripts/silver_to_gold.py","PythonVersion":"3"}' \
    --glue-version "4.0" \
    --number-of-workers 2 \
    --worker-type "G.1X" \
    --default-arguments '{"--job-bookmark-option":"job-bookmark-enable","--S3_BUCKET":"[[S3_BUCKET="medallion-datalake-your-account-id"]]"}'

What do these job settings mean?

  • glueetl tells Glue this is an Apache Spark ETL job.
  • --glue-version "4.0" uses Glue 4.0 which runs Spark 3.3.0 with Python 3.10.
  • G.1X workers provide 4 vCPU and 16 GB memory each (1 DPU per worker).
  • --job-bookmark-option enables job bookmarks so subsequent runs only process new data.
  • --S3_BUCKET passes your bucket name into the script as a runtime parameter.
  • Start the job run by running:
aws glue start-job-run --job-name silver-to-gold-etl

What should I see?

The command returns a JobRunId string. The job takes approximately 3-5 minutes to complete. It reads from the Silver layer, computes aggregations, and writes Parquet to the Gold layer.

  • Check the job status by running:
aws glue get-job-runs --job-name silver-to-gold-etl
  • Wait until JobRunState shows SUCCEEDED. Re-run the command every minute or so until it completes.
  • Verify the Gold layer output by listing the new prefixes:
aws s3 ls s3://[[S3_BUCKET="medallion-datalake-your-account-id"]]/gold/

You should see three prefixes: daily_revenue/, customer_ltv/, and traffic_conversions/.

Job showing FAILED status?

Check that the Silver layer has data by running aws s3 ls s3://your-bucket/silver/orders/. If it is empty, re-run your Bronze-to-Silver job first.

Verify the --S3_BUCKET value in your --default-arguments matches your actual bucket name exactly, with no trailing slash.

help me debug my silver-to-gold Glue job failure

Run a Glue Crawler on the Gold layer

The Gold layer now contains Parquet files, but Athena cannot query them until they are registered in the Data Catalog. A Glue Crawler will scan the Gold prefix, discover the schemas, and create tables in gold_db.

  • Create the Gold layer crawler by running:
aws glue create-crawler \
    --name gold-crawler \
    --role medallion-pipeline-role \
    --database-name gold_db \
    --targets '{"S3Targets":[{"Path":"s3://[[S3_BUCKET="medallion-datalake-your-account-id"]]/gold/"}]}'

What does the crawler do?

The crawler scans all Parquet files under the gold/ prefix. It discovers one table per subfolder (daily_revenue, customer_ltv, traffic_conversions) and registers their column names and types in gold_db.

  • Start the crawler by running:
aws glue start-crawler --name gold-crawler
  • Wait 1-2 minutes for the crawler to finish. Check its status by running:
aws glue get-crawler --name gold-crawler --query "Crawler.State"
  • When the state shows READY, the crawler has finished. Verify the tables were created by running:
aws glue get-tables --database-name gold_db --query "TableList[].Name"

You should see three tables listed: daily_revenue, customer_ltv, and traffic_conversions.

Crawler stuck or no tables created?

If the crawler stays in RUNNING state for more than 5 minutes, check that the gold/ prefix actually contains Parquet files using aws s3 ls s3://your-bucket/gold/ --recursive.

If the crawler completes but get-tables returns an empty list, verify the --database-name in the create-crawler command matches gold_db exactly.

help me debug why my Gold layer Glue Crawler is not finding tables

Your Gold layer is now populated and cataloged. Next up, you will orchestrate this entire pipeline into a single automated workflow using AWS Step Functions.

Orchestrate with AWS Step Functions

You now have two ETL jobs that transform data through the medallion layers. But running each job manually in sequence is fragile and time-consuming.

In this step, you will create an AWS Step Functions state machine that orchestrates the entire pipeline end-to-end. One click triggers the Bronze crawler, then the Bronze-to-Silver job, then the Silver-to-Gold job, then the Gold crawler, all in the correct order with automatic waiting between steps.

In this step, get ready to:
  • Create the Step Functions state machine definition as a JSON file.
  • Deploy and execute the state machine to run the full pipeline.
  • Verify all three S3 layers are populated after the end-to-end run.
Create the state machine definition

A Step Functions state machine is defined in JSON using the Amazon States Language. Each state in the definition represents one task in your pipeline sequence.

  • Create a file called pipeline_state_machine.json in your project directory by running:
touch pipeline_state_machine.json
  • Open pipeline_state_machine.json in your text editor and paste the following definition:
{
  "Comment": "Medallion Architecture Data Pipeline Orchestration",
  "StartAt": "RunBronzeCrawler",
  "States": {
    "RunBronzeCrawler": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startCrawler.sync",
      "Arguments": {
        "Name": "bronze-crawler"
      },
      "Next": "RunBronzeToSilverJob"
    },
    "RunBronzeToSilverJob": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Arguments": {
        "JobName": "bronze-to-silver-etl"
      },
      "Next": "RunSilverToGoldJob"
    },
    "RunSilverToGoldJob": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Arguments": {
        "JobName": "silver-to-gold-etl"
      },
      "Next": "RunGoldCrawler"
    },
    "RunGoldCrawler": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startCrawler.sync",
      "Arguments": {
        "Name": "gold-crawler"
      },
      "End": true
    }
  }
}

What does this definition do?

The state machine defines four sequential tasks that execute your pipeline in order:

  • RunBronzeCrawler discovers schemas from the raw source files in Bronze.
  • RunBronzeToSilverJob cleans and conforms the data into Parquet in the Silver layer.
  • RunSilverToGoldJob aggregates Silver data into analytics-ready Gold tables.
  • RunGoldCrawler registers the new Gold tables in the Data Catalog.

The .sync suffix on each resource ARN tells Step Functions to wait for the Glue job or crawler to finish before moving to the next state.

  • Save pipeline_state_machine.json.
  • Confirm the file exists by running:
ls pipeline_state_machine.json

You should see pipeline_state_machine.json in the output.

Don't see the file?

Make sure you created the file in your current working directory. Run pwd to check which directory you are in and confirm it matches where you intended to save the file.

help me find my pipeline_state_machine.json file

Why Step Functions instead of Glue Workflows?

Step Functions provides broader service integration beyond just Glue components. It can orchestrate any AWS service, offers better error handling with try/catch/retry patterns, and is the AWS-recommended approach for complex multi-service pipelines.

Glue Workflows are limited to orchestrating Glue components only, with a maximum of 100 jobs, crawlers, and triggers per workflow.

Deploy and execute the state machine

With the definition file ready, you will now create the state machine in AWS and run it.

  • Create the Step Functions state machine by running:
aws stepfunctions create-state-machine \
  --name medallion-pipeline \
  --definition file://pipeline_state_machine.json \
  --role-arn arn:aws:iam::[[AWS_ACCOUNT_ID="your 12-digit AWS account ID"]]]:role/medallion-pipeline-role

What does this command do?

This command creates a Standard state machine named medallion-pipeline using your JSON definition file. The --role-arn flag assigns the IAM role that Step Functions will assume when invoking Glue crawlers and jobs.

The file:// prefix tells the AWS CLI to read the definition from your local file instead of passing inline JSON.

You should see a JSON response containing a stateMachineArn field. Copy this ARN value for the next command.

Seeing an access denied error?

Confirm that the medallion-pipeline-role trust policy allows states.amazonaws.com to assume the role. The role was created in Step 1 with this trust already included. If you modified the role since then, re-add the Step Functions service principal.

help me fix my Step Functions create-state-machine access denied error

  • Start an execution of the state machine by running:
aws stepfunctions start-execution \
  --state-machine-arn [[STATE_MACHINE_ARN="arn:aws:states:us-east-1:123456789012:stateMachine:medallion-pipeline"]]

What happens next?

This kicks off the full pipeline. Step Functions will execute each state in sequence, waiting for each Glue operation to complete before proceeding. The entire run takes approximately 10-15 minutes because each Glue job has startup overhead.

You should see a JSON response containing an executionArn field. Copy this ARN to monitor the execution.

  • Check the execution status by running:
aws stepfunctions describe-execution \
  --execution-arn [[EXECUTION_ARN="arn:aws:states:us-east-1:123456789012:execution:medallion-pipeline:execution-id"]]

Run this command periodically until the status field shows SUCCEEDED.

Monitor in the console instead

You can also watch the execution in real time by opening the Step Functions console. Navigate to **State machines**, click **medallion-pipeline**, then click your execution. The visual workflow shows each state turning green as it completes.

Execution status shows FAILED?

Check which state failed in the execution details. Common causes include:

  • The IAM role is missing Glue permissions. Confirm AWSGlueServiceRole is attached to medallion-pipeline-role.
  • A crawler or job name in the definition does not match the actual resource name. Verify bronze-crawler, bronze-to-silver-etl, silver-to-gold-etl, and gold-crawler all exist in Glue.
  • A Glue job ran out of DPU-hours or hit a timeout. Check the Glue job run details for error messages.

help me debug my Step Functions execution failure

Verify the end-to-end pipeline results

The state machine re-ran both ETL jobs and both crawlers. Confirm that all three layers of your data lake are populated with fresh output.

  • Check the Bronze layer by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/bronze/orders/
  • Check the Silver layer by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/silver/orders/
  • Check the Gold layer by running:
aws s3 ls s3://[[DATALAKE_BUCKET="medallion-datalake-your-account-id"]]/gold/daily_revenue/

Each command should list files or prefixes confirming data exists at every layer. The Silver and Gold layers should show Parquet files.

Missing files in Silver or Gold?

If Bronze has files but Silver or Gold are empty, the corresponding ETL job likely failed. Check the Glue job run history in the AWS Glue console for error details. Common issues include incorrect S3 bucket names passed as job parameters.

help me figure out why my Silver or Gold layer is empty after the pipeline ran

✔️ Awesome, I've got everything!

Great. Double check you have saved pipeline_state_machine.json and that your execution completed successfully.

ⓧ I'd like to double check the full code

Here is the complete pipeline_state_machine.json file:

{
  "Comment": "Medallion Architecture Data Pipeline Orchestration",
  "StartAt": "RunBronzeCrawler",
  "States": {
    "RunBronzeCrawler": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startCrawler.sync",
      "Arguments": {
        "Name": "bronze-crawler"
      },
      "Next": "RunBronzeToSilverJob"
    },
    "RunBronzeToSilverJob": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Arguments": {
        "JobName": "bronze-to-silver-etl"
      },
      "Next": "RunSilverToGoldJob"
    },
    "RunSilverToGoldJob": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Arguments": {
        "JobName": "silver-to-gold-etl"
      },
      "Next": "RunGoldCrawler"
    },
    "RunGoldCrawler": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startCrawler.sync",
      "Arguments": {
        "Name": "gold-crawler"
      },
      "End": true
    }
  }
}

Your pipeline is fully automated. Next up, you will query the Gold layer with Amazon Athena to prove the data is analytics-ready.

Query the Gold Layer with Amazon Athena

Your pipeline has done all the heavy lifting. Raw data flowed through Bronze, got cleaned in Silver, and landed as aggregated business metrics in Gold.

Now it is time to prove that the Gold layer is analytics-ready by running real SQL queries against it. Amazon Athena lets you query data directly in S3 using standard SQL, with no servers to manage and no data to load.

In this step, get ready to:
  • Connect the Athena query editor to your Gold layer tables.
  • Run analytics queries for daily revenue, customer lifetime value, and traffic source conversions.
  • Export a query result as CSV to demonstrate the analytics consumption pattern.
Open the Athena console and configure the query results location

Before you can run any queries, Athena needs to know where to store its output files. You already created an S3 bucket for this in an earlier step.

  • In the AWS Management Console, search for Athena in the top search bar and select it.
  • If this is your first time visiting Athena, click Explore the query editor to open the query editor.
  • In the navigation bar at the top of the query editor, click Settings.
  • Click Manage.
  • In the Location of query result box, enter the S3 path to your Athena results bucket:
s3://[[ATHENA_BUCKET="medallion-athena-results-your-account-id"]]/
  • Click Save.
  • Click Editor to switch back to the query editor.
  • In the left panel under Database, select gold_db from the dropdown.

You should see three tables listed in the left panel: daily_revenue, customer_ltv, and traffic_conversions.

Why query S3 directly?

Traditional analytics requires loading data into a separate warehouse before querying. Athena skips that step entirely. It reads Parquet files directly from S3 using schema information from the Glue Data Catalog.

Because your Gold layer is stored in Parquet (a columnar format), Athena only reads the columns your query needs. This makes queries fast and cheap.

Don't see any tables?

Make sure you selected gold_db from the Database dropdown (not bronze_db or silver_db). If gold_db shows no tables, go back and confirm the Gold crawler ran successfully in the previous step.

help me troubleshoot why my gold_db tables are not appearing in Athena

Run analytics queries against the Gold layer

With the Gold layer connected, you can now run SQL queries that answer real business questions. Each query targets one of the three aggregated tables your pipeline created.

  • Clear the query editor and paste the following query to see daily revenue trends:
SELECT year, month, day, total_revenue, total_orders
FROM daily_revenue
ORDER BY year, month, day
  • Click Run.

You should see a results table with rows showing revenue and order counts for each day in your sample data.

What does this query show?

This query reads the daily_revenue table your Silver-to-Gold ETL job created. It contains pre-aggregated totals, so the query returns instantly without needing to re-process raw order data.

  • Clear the editor and paste this query to find your top customers by lifetime value:
SELECT customer_id, customer_email, lifetime_value, total_orders
FROM customer_ltv
ORDER BY lifetime_value DESC
  • Click Run.

You should see customers ranked by total spending, with CUST-101 (alice@example.com) at the top with the highest lifetime value.

  • Clear the editor and paste this query to see conversion rates by traffic source:
SELECT traffic_source, total_sessions, unique_visitors, avg_session_duration_sec
FROM traffic_conversions
ORDER BY total_sessions DESC
  • Click Run.

You should see traffic sources ranked by session count, showing which channels drive the most engagement.

Why pre-aggregate in the Gold layer?

These queries return in under a second because the Gold layer already did the heavy computation (joins, grouping, aggregations) during the ETL job. Analysts querying Gold tables get instant answers without needing to understand the raw data structure.

This is the core value of the medallion architecture: each layer serves a different audience. Bronze for data engineers, Silver for data scientists, Gold for business analysts.

Seeing zero rows returned?

If a query returns no results, the Gold layer Parquet files may be empty. Go to the S3 console and check that files exist under gold/daily_revenue/, gold/customer_ltv/, and gold/traffic_conversions/. If they are missing, re-run the Step Functions state machine from the previous step.

help me debug why my Athena query against the Gold layer returns zero rows

Export a query result as CSV

Analytics teams often need to pull query results into spreadsheets or feed them into downstream tools. Athena makes this easy with a built-in download option.

  • Run the daily revenue query again (or any of the three queries above).
  • In the Results pane below the query editor, click Download results.
  • Save the CSV file when your browser prompts you.

Where do results end up in S3?

Every query you run in Athena automatically saves its results as a CSV file in your Athena results bucket. The Download results button downloads that same file to your local machine. You can also find these CSV files in the S3 console under your medallion-athena-results bucket.

What is the analytics consumption pattern?

In production, teams consume Gold layer data in multiple ways: Athena for ad-hoc SQL, BI tools (QuickSight, Tableau) connected via JDBC, and ML pipelines reading Parquet directly from S3. The exported CSV demonstrates the simplest consumption path.

Your data lake is now fully operational from ingestion to analytics. Next up, you will tackle the Secret Mission to add data quality checks and event-driven automation.

Secret mission

Add Data Quality and Event-Driven Triggers

Your pipeline works end-to-end, but it runs only when you manually trigger it. In production, pipelines need two things: automated data quality checks that catch bad data before it reaches the Gold layer, and event-driven triggers that start the pipeline the moment new data lands. Time to make your pipeline production-ready.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

This project uses serverless AWS services that only charge when actively running. Ongoing costs are minimal (just S3 storage for your sample data) since Glue jobs and Step Functions only charge on execution.

Cost warning

If you leave the S3 buckets in place, you will incur small storage charges for the data at rest. AWS Glue ETL jobs cost $0.44 per DPU-hour when running, and Athena charges $5 per TB scanned. With the sample datasets used in this project, costs are fractions of a cent per query or job run.

If you are not planning to iterate on this pipeline further, choose Pause or Delete to avoid any unexpected charges.

Resources you used:

  • S3 buckets (data lake bucket and Athena results bucket).
  • AWS Glue Data Catalog databases (bronze_db, silver_db, gold_db).
  • AWS Glue Crawlers (bronze-crawler, gold-crawler).
  • AWS Glue ETL jobs (bronze-to-silver-etl, silver-to-gold-etl).
  • AWS Step Functions state machine.
  • IAM role with attached policies.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the pipeline with the Secret Mission.

  • Your S3 buckets will continue to store data at minimal cost (fractions of a cent for the sample datasets).
  • Glue jobs and Step Functions only charge when you run them. They will not incur cost while idle.
  • The Glue Data Catalog databases and tables remain free under the first 1 million objects free tier.

Pause - I'll come back to this later

All the resources in this project are serverless. They do not run unless triggered, so there is nothing to "pause" in the traditional sense.

  • Your S3 storage charges will continue at a negligible rate for the sample data.
  • No Glue jobs or Step Functions executions will run unless you trigger them manually.
  • When you return, everything will be exactly as you left it.

Delete - I don't want to use this again

Remove all project resources and start fresh. Run the following commands in your terminal to delete everything.

  • Delete your data lake S3 bucket and all its contents by running:
  • Replace the bucket name with the one you created in Step 2.
aws s3 rb s3://[[DATA_BUCKET="your-data-lake-bucket-name"]] --force
  • Delete your Athena results bucket by running:
aws s3 rb s3://[[ATHENA_BUCKET="your-athena-results-bucket-name"]] --force
  • Delete the Glue ETL jobs by running:
aws glue delete-job --job-name bronze-to-silver-etl
aws glue delete-job --job-name silver-to-gold-etl
  • Delete the Glue Crawlers by running:
aws glue delete-crawler --name bronze-crawler
aws glue delete-crawler --name gold-crawler
  • Delete the Glue Data Catalog databases by running:
aws glue delete-database --name bronze_db
aws glue delete-database --name silver_db
aws glue delete-database --name gold_db
  • Delete the Step Functions state machine by running:
  • Replace the ARN with your state machine ARN from the Step Functions console.
aws stepfunctions delete-state-machine --state-machine-arn [[STATE_MACHINE_ARN="your-state-machine-arn"]]
  • Delete the IAM role by first detaching its policies, then deleting the role itself. Replace the role name and policy ARNs with the ones you created in Step 1.
aws iam detach-role-policy --role-name [[ROLE_NAME="your-glue-role-name"]] --policy-arn [[POLICY_ARN="arn-of-attached-policy"]]
aws iam delete-role --role-name [[ROLE_NAME="your-glue-role-name"]]

Detach all policies before deleting the role

You must detach every managed policy from the role before you can delete it. If you attached multiple policies, run the detach command once for each policy ARN. You can list attached policies with:

aws iam list-attached-role-policies --role-name [[ROLE_NAME="your-glue-role-name"]]

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-grade serverless data pipeline on AWS that transforms raw e-commerce data into analytics-ready and ML-ready datasets through a medallion architecture.

You've learned how to:

  • Design and implement a medallion architecture data lake on Amazon S3 with Bronze, Silver, and Gold layers that progressively refine raw data into business-ready datasets.
  • Build PySpark ETL jobs in AWS Glue that perform deduplication, type casting, null handling, joins, and business aggregations across multiple data sources.
  • Orchestrate end-to-end pipeline execution with AWS Step Functions and query Gold-layer KPIs with Amazon Athena to prove analytics readiness.
  • Added data quality validation rules and event-driven triggers with EventBridge so the pipeline executes automatically when new data lands in S3.

Ready to quiz yourself?