Medallion Data Lake with AWS Glue

Build a Bronze/Silver/Gold data lake on S3 with PySpark ETL and Athena.

Introduction

30 Second Summary

Every company collects mountains of data, but raw data is messy. Duplicates, missing values, and inconsistent formats make it impossible to trust any report built on top of it.

In this project, you will build a serverless medallion data lake on AWS. Starting with messy e-commerce order data, you will write two PySpark transformation jobs that clean and aggregate it into analytics you can query instantly with Amazon Athena.

What You'll Build

When your pipeline is complete, you can open Athena, run a single SQL query, and instantly see revenue by product category and top customers by lifetime value from data that started as a pile of messy JSON.

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

  • A three-layer data lake on S3 where raw orders flow from Bronze through Silver to Gold, with each layer visibly cleaner and more useful when you query it.
  • Two automated ETL jobs on AWS Glue that clean duplicates, fix inconsistent formats, and aggregate orders into business-ready metrics you can query on the spot.
  • A working Athena query layer that proves the pipeline delivers correct, fast results on partitioned Parquet data, with measurable performance gains over querying raw files.
  • Secret Mission: Orchestrate the full pipeline with a Glue Workflow that chains every job and crawler into a single automated sequence.

Are there any prerequisites?

This project assumes you have an AWS account with billing enabled, the AWS CLI installed and configured, Python installed, and VS Code ready to go. If you need to set up any of these, complete that before starting Step 1.

Before We Start

Before we dive into creating resources, let's lock in what you're about to build and why it matters.

Set Up S3 Data Lake and IAM Role

Every AWS Glue job needs two things before it can run. It needs an S3 bucket to store raw data and processed output. It also needs an IAM role that grants permission to read and write S3 and access the Glue Data Catalog.

In this step, you will create an S3 bucket with the medallion architecture's three layers (Bronze, Silver, Gold), set up the IAM role, and generate the messy raw dataset that your ETL pipeline will clean up.

In this step, get ready to:
  • Create an S3 bucket with bronze/, silver/, gold/, and scripts/ prefixes.
  • Create an IAM role with the right permissions for AWS Glue.
  • Generate synthetic order data with intentional quality problems and upload it to S3.
Create the S3 bucket with medallion layers

Each layer of the medallion architecture maps to a prefix inside a single S3 bucket. The bronze/ prefix holds raw data as it arrived. The silver/ and gold/ prefixes hold progressively cleaner output.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir medallion-datalake
cd medallion-datalake
  • Look up your AWS account ID by running this command:
aws sts get-caller-identity --query Account --output text

You should see a 12-digit number printed to the terminal. Copy this number. You will use it to create a globally unique bucket name.

Seeing an error instead of your account ID?

Make sure your AWS CLI is configured with valid credentials. Run aws configure and enter your access key ID, secret access key, and set the default region to us-east-1.

Help me configure my AWS CLI credentials.

  • Create the S3 bucket by running this command, replacing the placeholder with your 12-digit account ID:
aws s3 mb s3://medallion-datalake-[[ACCOUNT_ID="your-12-digit-account-id"]] --region us-east-1

Why include the account ID in the bucket name?

S3 bucket names are globally unique across all AWS accounts. Including your account ID prevents naming collisions with other learners running the same project.

You should see a make_bucket confirmation message.

  • Create the four folder prefixes inside your bucket by running these commands:
aws s3api put-object --bucket [[BUCKET_NAME="medallion-datalake-your-account-id"]] --key bronze/
aws s3api put-object --bucket [[BUCKET_NAME="medallion-datalake-your-account-id"]] --key silver/
aws s3api put-object --bucket [[BUCKET_NAME="medallion-datalake-your-account-id"]] --key gold/
aws s3api put-object --bucket [[BUCKET_NAME="medallion-datalake-your-account-id"]] --key scripts/

What do these commands do?

Each command creates a zero-byte object whose key ends with a /. S3 treats these as folder markers, so they appear as folders in the S3 console. Each command prints an ETag response confirming the object was created.

  • Confirm your bucket structure by running:
aws s3 ls s3://[[BUCKET_NAME="medallion-datalake-your-account-id"]]/

You should see four entries: bronze/, gold/, scripts/, and silver/ listed as prefixes.

Not seeing all four prefixes?

Make sure you included the trailing / in each --key value (e.g. bronze/ not bronze). Also check that the bucket name in your ls command matches the one you created.

Help me list the contents of my S3 bucket.

Create the IAM role for AWS Glue

AWS Glue needs an IAM role to act on your behalf. The managed AWSGlueServiceRole policy covers Glue Data Catalog access, but it only grants S3 permissions to paths starting with aws-glue-*. Your bucket has a custom name, so you also need an inline policy that targets your specific bucket.

  • Create a trust policy file that allows the Glue service to assume this role. In VS Code, create a file called trust-policy.json inside your medallion-datalake folder and paste this content:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "glue.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

What is a trust policy?

A trust policy defines which AWS service is allowed to "assume" (use) this role. This policy says only glue.amazonaws.com can assume the role, so no other service or user can use these permissions.

  • Create the S3 access policy file. In VS Code, create a second file called s3-policy.json in the same folder and paste this content, replacing the bucket name placeholder with your actual bucket name:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:*",
      "Resource": [
        "arn:aws:s3:::[[BUCKET_NAME=\"medallion-datalake-your-account-id\"]]",
        "arn:aws:s3:::[[BUCKET_NAME=\"medallion-datalake-your-account-id\"]]/*"
      ]
    }
  ]
}

What does this policy do?

This inline policy grants full S3 access (s3:*) but only to your specific bucket. The first resource line covers the bucket itself. The second line (with /*) covers every object inside it.

  • Save both trust-policy.json and s3-policy.json.
  • Confirm the files exist by running:
ls

You should see trust-policy.json and s3-policy.json listed in the output.

  • Create the IAM role by running this command:
aws iam create-role --role-name AWSGlueServiceRole-MedallionETL --assume-role-policy-document file://trust-policy.json

You should see a JSON response containing the role's ARN, name, and creation date.

Why does the role name start with AWSGlueServiceRole?

Naming your role with the AWSGlueServiceRole prefix lets it work in Glue Studio without extra iam:PassRole configuration.

  • Attach the managed Glue service policy by running this command:
aws iam attach-role-policy --role-name AWSGlueServiceRole-MedallionETL --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
  • Apply the inline S3 policy by running this command:
aws iam put-role-policy --role-name AWSGlueServiceRole-MedallionETL --policy-name S3AccessPolicy --policy-document file://s3-policy.json

Both commands succeed silently with no output. That is the expected behavior.

  • Verify the role and its attached policies by running:
aws iam get-role --role-name AWSGlueServiceRole-MedallionETL

You should see a JSON response with your role's details, including the ARN and the trust policy you defined.

Seeing a NoSuchEntity error?

This means the create-role command did not succeed. Scroll up in your terminal and look for an error on that line. A common cause is a malformed trust-policy.json file. Double-check that all braces and brackets are properly closed.

Help me create the IAM role for my Glue project.

Generate synthetic order data and upload it to S3

Instead of downloading a public dataset, you will generate synthetic e-commerce orders with controlled data quality problems. The script creates 500 base orders with deliberate issues: null amounts, mixed-case categories, and inconsistent date formats. It also adds 50 duplicate entries.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Select the medallion-datalake folder on your Desktop, then click Open.
  • Create a new file called generate_data.py by pressing Cmd+N (macOS) or Ctrl+N (Windows), then save with Cmd+S (macOS) or Ctrl+S (Windows) and name it generate_data.py.
  • Start building the data generator by pasting this code into generate_data.py:
import json
import random
import os
from datetime import datetime, timedelta

random.seed(42)

categories = ["Electronics", "electronics", "ELECTRONICS", "Clothing", "clothing", "Home & Garden", "home & garden", "Books", "books", "Sports"]
customers = [f"CUST-{str(i).zfill(4)}" for i in range(1, 51)]
statuses = ["completed", "completed", "completed", "pending", "cancelled"]

orders = []
order_id_counter = 1000

# Generate 500 base orders with deliberate quality issues
for i in range(500):
    order_date = datetime(2024, 1, 1) + timedelta(days=random.randint(0, 180))
    amount = round(random.uniform(5.0, 500.0), 2)

    order = {
        "order_id": f"ORD-{order_id_counter}",
        "customer_id": random.choice(customers),
        "category": random.choice(categories),
        "amount": amount if random.random() > 0.05 else None,
        "order_date": order_date.strftime("%Y-%m-%d") if random.random() > 0.03 else order_date.strftime("%m/%d/%Y"),
        "status": random.choice(statuses),
        "quantity": random.randint(1, 10)
    }
    orders.append(order)
    order_id_counter += 1

What does this code do?

random.seed(42) makes the output reproducible. Every learner who runs this script gets the same 500 orders.

The categories list deliberately mixes cases ("Electronics", "electronics", "ELECTRONICS"). About 5% of amount values are set to None, and about 3% of dates use MM/DD/YYYY format instead of YYYY-MM-DD. These are the exact problems your ETL pipeline will fix.

  • Add the remaining code below the existing code in generate_data.py. This section adds duplicates and writes the output file:
# Add deliberate duplicates (10%)
for i in range(50):
    orders.append(random.choice(orders[:500]))

random.shuffle(orders)

os.makedirs("data", exist_ok=True)

# Write as JSON lines (one JSON object per line)
with open("data/orders.json", "w") as f:
    for order in orders:
        f.write(json.dumps(order) + "\n")

print(f"Generated {len(orders)} orders (including ~50 duplicates) to data/orders.json")

What does this code do?

The duplicate loop picks 50 random orders from the original 500 and appends them again. This gives the dataset a realistic problem that your Silver layer job will need to handle through deduplication.

The file is written as JSON lines (one JSON object per line), which is the format AWS Glue expects when reading JSON from S3.

  • Save generate_data.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved generate_data.py with both code chunks, then move on.

ⓧ I'd like to double check the full code

Scroll up and confirm both chunks are present in order. Chunk 1 starts with the import statements and ends with order_id_counter += 1. Chunk 2 starts with the # Add deliberate duplicates comment and ends with the print() line. Make sure there is no blank line between the two chunks. Chunk 2 should follow directly after order_id_counter += 1 with one blank line separating them.

  • Run the script in your terminal by running this command:
python3 generate_data.py

You should see: Generated 550 orders (including ~50 duplicates) to data/orders.json.

Seeing a ModuleNotFoundError or command not found?

If you see command not found: python3, try python generate_data.py instead. On some systems, the command is just python. Make sure you are running the command from inside the medallion-datalake folder.

Help me run my Python data generator script.

  • Upload the generated data to your S3 bucket's Bronze layer by running this command:
aws s3 cp data/orders.json s3://[[BUCKET_NAME="medallion-datalake-your-account-id"]]/bronze/orders.json

You should see an upload confirmation message with the S3 destination path.

Before you verify, what do you think you will see when you list the contents of your bronze/ prefix?

  • Confirm the upload by running:
aws s3 ls s3://[[BUCKET_NAME="medallion-datalake-your-account-id"]]/bronze/

You should see orders.json listed with its file size.

Your data lake foundation is in place. You have an S3 bucket with the medallion layers, an IAM role with the right permissions, and 550 messy order records sitting in the Bronze layer. Next up, you will point AWS Glue at that raw data and try querying it directly to see just how messy it really is.

Crawl Bronze Data and Attempt Athena Queries

Your S3 bucket is loaded with raw order data and your IAM role is ready. Before building any ETL pipeline, you need to find out whether the raw data is already usable for analytics.

In this step, you will register your bronze data with the Glue Data Catalog and attempt to query it directly with Amazon Athena. The results will reveal exactly why raw data needs transformation before it can drive business decisions.

In this step, get ready to:
  • Create a Glue database and Crawler that scans the bronze layer.
  • Run the Crawler and verify it registers a table in the Data Catalog.
  • Query the bronze table in Athena and observe data quality problems firsthand.
Create the Glue database and Crawler

A Glue Crawler scans a data source and automatically infers its schema. It then writes that schema as a table definition in the Glue Data Catalog, which makes the data queryable by Athena.

  • Create a Glue database called medallion_db by running this command:
aws glue create-database \
  --database-input '{"Name":"medallion_db"}'

What does this command do?

This creates an empty database in the Glue Data Catalog. The database is a logical container where Crawlers will register table metadata.

  • Verify the database exists by running:
aws glue get-database --name medallion_db

You should see a JSON response that includes "Name": "medallion_db" in the output.

  • Create the Crawler pointing at your bronze S3 path by running this command:
aws glue create-crawler \
  --name bronze-crawler \
  --role AWSGlueServiceRole-MedallionETL \
  --database-name medallion_db \
  --targets '{"S3Targets":[{"Path":"s3://[[BUCKET_NAME="your-bucket-name"]]/bronze/"}]}'

What does this command do?

The --name flag sets the crawler name to bronze-crawler. The --role flag tells it which IAM role to assume for S3 access.

The --targets flag points the Crawler at the bronze/ prefix of your bucket. The --database-name flag tells the Crawler where to register any tables it discovers.

Seeing an error about the role?

Make sure the role name matches exactly: AWSGlueServiceRole-MedallionETL. This is the role you created in Step 1.

help me debug my Glue Crawler creation error

Run the Crawler and verify the table

The Crawler is created but has not scanned your data yet. You need to start it manually and wait for it to finish.

  • Start the Crawler by running:
aws glue start-crawler --name bronze-crawler

How long does this take?

Crawlers have a 10-minute minimum billing duration. For a small dataset like yours, the actual scan finishes in 1-2 minutes, but the Crawler remains in RUNNING state until the billing window closes. You can check on its state while you wait.

  • Check whether the Crawler is still running by running:
aws glue get-crawler --name bronze-crawler \
  --query 'Crawler.State' --output text

If the output says RUNNING, wait about 30 seconds and re-run the command. You are waiting for it to say READY.

  • Once the state is READY, confirm that the Crawler created a table by running:
aws glue get-tables --database-name medallion_db

You should see a TableList containing a table named bronze (or similar, inferred from the S3 prefix). The StorageDescriptor section shows the inferred schema with columns like order_id, customer_id, category, amount, and order_date.

Table list is empty?

Confirm the Crawler completed successfully by running aws glue get-crawler --name bronze-crawler and checking LastCrawl.Status. If it shows FAILED, check that your S3 bucket path and IAM role permissions are correct.

help me debug why my Glue Crawler found no tables

Query the bronze table in Athena

The Crawler has registered your raw data as a table. Now you can query it with SQL using Amazon Athena. Before you run any queries, Athena needs a place to store query results.

  • Open the AWS Management Console in your browser.
  • Search for Athena in the top search bar and open the Athena service.

Athena requires an S3 output location for query results. You will configure this before running your first query.

  • If you see a banner about setting up a query result location, click Settings in the top navigation.
  • Click Manage.
  • In the Location of query result field, enter your bucket's Athena results path:
s3://[[BUCKET_NAME="your-bucket-name"]]/athena-results/
  • Click Save.

Now you are ready to run queries. This is the moment you find out whether the raw bronze data can answer a basic business question.

  • Click Editor in the left navigation to return to the query editor.
  • In the Database dropdown on the left panel, select medallion_db.
  • You should see the table the Crawler created listed under Tables.

Before you run this, do you think querying 550 raw records will give you an accurate order count? Think about the data you generated. It had 500 base orders plus 50 duplicates.

  • Paste the following query into the query editor and click Run:
SELECT COUNT(*) AS total_orders
FROM bronze;

The result is 550. If your company had 500 real orders, this count is inflated by 50 duplicate rows. Any report built on this number would be wrong.

  • Now try a revenue-by-category breakdown. Paste this query and click Run:
SELECT category, SUM(amount) AS total_revenue
FROM bronze
GROUP BY category
ORDER BY total_revenue DESC;

Look at the results closely. You will see Electronics, electronics, and ELECTRONICS appearing as three separate rows. They are the same category, but inconsistent casing splits them apart. The revenue totals are also corrupted by null amounts.

Why are there three rows for Electronics?

The original data contained mixed-case category names. SQL treats Electronics and electronics as different strings in a GROUP BY clause. A real analytics report built on this data would show fragmented categories and inaccurate totals.

This is exactly the problem that ETL pipelines solve. In Step 3, you will build a PySpark job that normalizes categories, removes duplicates, and drops null values.

You have just experienced the core problem this entire pipeline exists to solve. The raw data is full of duplicates, nulls, and inconsistent formatting. Athena can read it, but the results are unreliable. In the next step, you will build a PySpark Glue job that cleans, deduplicates, and standardizes this data into a trustworthy Silver layer.

Build the Bronze-to-Silver PySpark Glue Job

In the previous step, you queried the Bronze layer directly with Athena and saw the problems firsthand. Duplicates inflated your row count to 550. Null amounts corrupted aggregations. Mixed-case categories like Electronics, electronics, and ELECTRONICS split into separate rows.

Now you will fix all of that. In this step, you will write a PySpark ETL script that reads the raw Bronze data, cleans it, deduplicates it, and writes the result as partitioned Parquet into the Silver layer on S3. This is the core data engineering skill: transforming unreliable raw data into a trustworthy intermediate layer.

In this step, get ready to:
  • Write the Bronze-to-Silver PySpark ETL script in VS Code.
  • Upload the script to S3 and create the Glue job via the AWS CLI.
  • Run the job, crawl the Silver output, and verify clean data with Athena.
Write the Bronze-to-Silver PySpark script

AWS Glue runs your ETL logic as a PySpark script on a managed Spark cluster. You will write this script locally in VS Code, then upload it to S3 so Glue can find it.

  • Switch back to VS Code.
  • Create a new file called bronze_to_silver.py in your project folder (the same folder that contains generate_data.py).

The script has three parts: initialization, transformations, and output. You will build each part as a separate chunk.

  • Add the imports and GlueContext initialization to bronze_to_silver.py by pasting the following 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 awsglue.dynamicframe import DynamicFrame
from pyspark.sql.functions import col, lower, to_date, coalesce, regexp_replace, trim
from pyspark.sql.types import DecimalType

# Parse job arguments (JOB_NAME and custom S3_BUCKET)
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)

# Get the bucket name passed as a job argument
bucket = args["S3_BUCKET"]

What does this code do?

The imports pull in Glue-specific libraries (GlueContext, DynamicFrame, Job) alongside standard PySpark functions for data transformations.

The getResolvedOptions call reads command-line arguments that Glue passes to your script at runtime, including the job name and a custom S3_BUCKET parameter so the script knows which bucket to read from and write to.

  • Next, add the data read and transformation logic below the initialization code you just pasted:
# Read raw JSON from Bronze
bronze_dyf = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={"paths": [f"s3://{bucket}/bronze/"]},
    format="json",
    transformation_ctx="bronze_dyf"
)

# Convert to DataFrame for transformations
df = bronze_dyf.toDF()

# Drop rows with null order_id or null amount
df = df.filter(col("order_id").isNotNull() & col("amount").isNotNull())

# Deduplicate on order_id (keep first occurrence)
df = df.dropDuplicates(["order_id"])

# Normalize category to lowercase and trim whitespace
df = df.withColumn("category", lower(trim(col("category"))))

# Cast amount to decimal
df = df.withColumn("amount", col("amount").cast(DecimalType(10, 2)))

# Normalize order_date to consistent format
df = df.withColumn(
    "order_date",
    coalesce(
        to_date(col("order_date"), "yyyy-MM-dd"),
        to_date(col("order_date"), "MM/dd/yyyy")
    )
)

# Filter out rows where date parsing failed
df = df.filter(col("order_date").isNotNull())

What does this code do?

This is the heart of the ETL job. It reads the raw JSON from the Bronze S3 prefix into a DynamicFrame, then converts it to a standard Spark DataFrame so you can use PySpark transformations.

  • Rows with null order_id or null amount are dropped since they cannot contribute to meaningful analytics.
  • Duplicates are removed by keeping only one row per order_id.
  • Categories are normalized to lowercase so Electronics, electronics, and ELECTRONICS merge into a single group.
  • The amount column is cast to a precise DecimalType(10, 2) to avoid floating-point rounding in aggregations.
  • The order_date column handles both date formats in the raw data (yyyy-MM-dd and MM/dd/yyyy) using coalesce.
  • Finally, add the output section that writes the clean data to Silver as partitioned Parquet:
# Convert back to DynamicFrame and write as partitioned Parquet
silver_dyf = DynamicFrame.fromDF(df, glueContext, "silver_dyf")

glueContext.write_dynamic_frame.from_options(
    frame=silver_dyf,
    connection_type="s3",
    connection_options={
        "path": f"s3://{bucket}/silver/",
        "partitionKeys": ["order_date"]
    },
    format="parquet",
    transformation_ctx="silver_output"
)

job.commit()

What does this code do?

The cleaned DataFrame is converted back to a DynamicFrame (Glue's native format for I/O), then written as Parquet files partitioned by order_date. Partitioning means Athena only scans the date folders it needs instead of the entire dataset.

The job.commit() call tells Glue the job finished successfully and enables job bookmarking for incremental processing in the future.

  • Save bronze_to_silver.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved bronze_to_silver.py before moving on.

ⓧ I'd like to double check the full code

Your bronze_to_silver.py has three sections you built above. Scroll up and confirm each one is present in this order:

  • Imports and GlueContext initialization (ending with bucket = args["S3_BUCKET"]).
  • Data read and transformations (ending with df = df.filter(col("order_date").isNotNull())).
  • Output section (ending with job.commit()).

Why DynamicFrame for I/O and DataFrame for transforms?

DynamicFrames are Glue's native abstraction. They handle schema inconsistencies gracefully and integrate directly with the Glue Data Catalog. DataFrames give you access to the full PySpark transformation API.

Using DynamicFrame for reading and writing, and DataFrame for the transformations in between, is the production best practice for Glue ETL jobs.

Upload the script and create the Glue job

With the script written, you need to get it onto S3 where Glue can access it, then create a Glue job definition that tells Glue where to find the script, which IAM role to use, and how much compute to allocate.

  • Upload bronze_to_silver.py to the scripts/ prefix in your S3 bucket by running this command in your terminal:
aws s3 cp bronze_to_silver.py s3://[[BUCKET_NAME="your-bucket-name"]]/scripts/bronze_to_silver.py
  • Confirm the upload succeeded by running:
aws s3 ls s3://[[BUCKET_NAME="your-bucket-name"]]/scripts/

You should see bronze_to_silver.py listed in the output.

Don't see the file?

  • Make sure your terminal is in the same directory as bronze_to_silver.py. Use ls to confirm the file exists locally before uploading.
  • Check that your bucket name matches the one you created in Step 1.

Help me troubleshoot my S3 upload for the bronze_to_silver.py script.

  • Create the Glue job by running this command:
aws glue create-job \
    --name bronze-to-silver \
    --role AWSGlueServiceRole-MedallionETL \
    --command '{"Name":"glueetl","ScriptLocation":"s3://'[[BUCKET_NAME="your-bucket-name"]]'/scripts/bronze_to_silver.py","PythonVersion":"3"}' \
    --default-arguments '{"--S3_BUCKET":"'[[BUCKET_NAME="your-bucket-name"]]'"}' \
    --glue-version "4.0" \
    --number-of-workers 2 \
    --worker-type "G.1X"

What does this command do?

  • The --command flag tells Glue this is a glueetl job (standard ETL) and where the Python script lives on S3.
  • The --default-arguments flag passes your bucket name as the --S3_BUCKET parameter that getResolvedOptions reads in your script.
  • The --glue-version "4.0" selects Spark 3.3 and Python 3.10 for the runtime.
  • Using 2 G.1X workers keeps costs low at $0.44 per DPU-hour (2 DPUs total). The default of 10 would be wasteful for this small dataset.

You should see a JSON response containing the job name and configuration details.

Seeing an AccessDeniedException?

  • Make sure the role name is exactly AWSGlueServiceRole-MedallionETL with no typos. The role must start with AWSGlueServiceRole for Glue to use it without additional iam:PassRole configuration.
  • Verify the role exists by running aws iam get-role --role-name AWSGlueServiceRole-MedallionETL.

Help me fix my Glue create-job AccessDeniedException.

Run the job and verify clean Silver data

The job is defined. Now you will start it, wait for it to finish, then run a Crawler on the Silver output and query it in Athena to prove the data is clean.

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

The command returns a JobRunId. Copy this value. You will use it to check the job status.

How long does the job take?

Glue jobs have startup overhead while spinning up the Spark cluster. Expect the job to take 2-5 minutes for this small dataset. Most of that time is cluster startup, not the actual processing.

  • Check the job status by running this command, replacing the run ID with the value you copied:
aws glue get-job-run \
    --job-name bronze-to-silver \
    --run-id [[JOB_RUN_ID="your-job-run-id"]]
  • Look for the JobRunState field in the output. If it shows RUNNING, wait a minute and run the command again.
  • Repeat until the status shows SUCCEEDED.

Job status shows FAILED?

  • Check the ErrorMessage field in the get-job-run output for details.
  • A common cause is the IAM role missing S3 permissions for your specific bucket. The managed AWSGlueServiceRole policy only covers S3 paths prefixed with aws-glue-*. Confirm the inline policy on your role grants access to your bucket.
  • Another common cause is a typo in the script. Double check your bronze_to_silver.py matches the code above exactly.

Help me debug my failed Glue job.

  • Confirm the Silver data exists on S3 by running:
aws s3 ls s3://[[BUCKET_NAME="your-bucket-name"]]/silver/

You should see folders named like order_date=2024-01-05/. Each folder contains Parquet files for that date partition.

  • Now create a Crawler to register the Silver data in the Glue Data Catalog by running:
aws glue create-crawler \
    --name silver-crawler \
    --role AWSGlueServiceRole-MedallionETL \
    --database-name medallion_db \
    --targets '{"S3Targets":[{"Path":"s3://'[[BUCKET_NAME="your-bucket-name"]]'/silver/"}]}'
  • Start the crawler by running:
aws glue start-crawler --name silver-crawler
  • Check the crawler status by running:
aws glue get-crawler --name silver-crawler --query "Crawler.State"
  • If the output shows RUNNING, wait 30 seconds and run it again. Repeat until the state shows READY.

Before you run the final Athena query, think about this: the Bronze table had 550 rows. Do you expect the Silver table to have more rows, fewer rows, or the same number?

  • Return to the Athena query editor in the AWS Management Console and run this query:
SELECT COUNT(*) FROM silver_orders;

The Silver count is lower than 550. Your script removed the duplicate rows and dropped rows with null values. The exact number depends on how many nulls and duplicates existed in the generated data.

  • Run one more query to confirm the category normalization worked:
SELECT DISTINCT category FROM silver_orders ORDER BY category;

You should see only lowercase categories: books, clothing, electronics, home & garden, sports. No more duplicate category rows from mixed casing.

Still seeing mixed-case categories?

  • Make sure you are querying silver_orders, not bronze_orders. The Bronze table still has the original messy data.
  • If the table silver_orders does not exist, the Silver Crawler may not have finished. Check its state with aws glue get-crawler --name silver-crawler.

Help me troubleshoot my Silver Athena query results.

Your Bronze-to-Silver pipeline is working. Messy JSON goes in, clean partitioned Parquet comes out. Next up, you will build the Silver-to-Gold job that aggregates this clean data into business-ready metrics.

Build the Silver-to-Gold PySpark Glue Job

Your Silver layer proved the pipeline works. Messy Bronze data went in, and clean, deduplicated Parquet came out.

But Silver data is still transactional. Each row is a single order. Business stakeholders do not want to scroll through thousands of orders to answer "which category drives the most revenue?" or "who are our highest-value customers?" The Gold layer solves this by pre-aggregating Silver data into summary tables optimized for fast Athena queries.

In this step, get ready to:
  • Write a PySpark script that computes revenue-by-category and customer lifetime value aggregations.
  • Upload the script, create a Gold Glue job, and run it.
  • Crawl the Gold data and validate results with Athena queries.
Write the Silver-to-Gold PySpark script

This script follows the same structure as bronze_to_silver.py. It initializes a GlueContext, reads from Silver, and writes aggregated Parquet to the Gold prefix.

  • In VS Code, create a new file called silver_to_gold.py in the same folder as bronze_to_silver.py.
  • Add the imports and GlueContext initialization by pasting this 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 awsglue.dynamicframe import DynamicFrame
from pyspark.sql.functions import col, sum as spark_sum, count, avg

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 code do?

This initialization block is almost identical to the Bronze-to-Silver script. The key difference is the imports: sum as spark_sum, count, and avg are Spark aggregation functions you will use to compute Gold metrics.

The sum function is imported as spark_sum to avoid conflicting with Python's built-in sum() function.

  • Below the initialization block, add the code that reads Silver data and computes both aggregations:
# Read clean data from Silver
silver_dyf = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={"paths": [f"s3://{bucket}/silver/"]},
    format="parquet",
    transformation_ctx="silver_dyf"
)

df = silver_dyf.toDF()

# Filter to completed orders only for revenue metrics
completed_df = df.filter(col("status") == "completed")

# Gold Aggregation 1: Revenue by category
revenue_by_category = completed_df.groupBy("category").agg(
    spark_sum("amount").alias("total_revenue"),
    count("order_id").alias("order_count"),
    avg("amount").alias("avg_order_value")
)

# Gold Aggregation 2: Customer lifetime value
customer_ltv = completed_df.groupBy("customer_id").agg(
    spark_sum("amount").alias("total_spend"),
    count("order_id").alias("total_orders"),
    avg("amount").alias("avg_order_value")
)

What does this code do?

  • The script reads Silver Parquet instead of Bronze JSON. The Silver data is already clean and deduplicated.
  • It filters to only completed orders so that pending and cancelled orders do not inflate revenue numbers.
  • The first aggregation groups by category to calculate total revenue, order count, and average order value per category.
  • The second aggregation groups by customer_id to calculate each customer's total spend, total orders, and average order value. This is their lifetime value.
  • Finally, add the code that writes both aggregations to the Gold prefix and commits the job:
# Write revenue_by_category to Gold
revenue_dyf = DynamicFrame.fromDF(revenue_by_category, glueContext, "revenue_dyf")
glueContext.write_dynamic_frame.from_options(
    frame=revenue_dyf,
    connection_type="s3",
    connection_options={"path": f"s3://{bucket}/gold/revenue_by_category/"},
    format="parquet",
    transformation_ctx="gold_revenue_output"
)

# Write customer_ltv to Gold
ltv_dyf = DynamicFrame.fromDF(customer_ltv, glueContext, "ltv_dyf")
glueContext.write_dynamic_frame.from_options(
    frame=ltv_dyf,
    connection_type="s3",
    connection_options={"path": f"s3://{bucket}/gold/customer_ltv/"},
    format="parquet",
    transformation_ctx="gold_ltv_output"
)

job.commit()

Why two separate outputs?

Each aggregation writes to a different S3 path: gold/revenue_by_category/ and gold/customer_ltv/. This creates two separate tables in the Glue Data Catalog when you crawl them. Business users can query each table independently.

Notice this Gold job does not use partitionKeys. The aggregated results are small summary tables, not transactional data. Partitioning would add overhead with no benefit.

  • Save silver_to_gold.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved silver_to_gold.py and move on to the next substep.

ⓧ I'd like to double check the full code

Your silver_to_gold.py has three sections. Scroll through your file and confirm each is present in order:

  • Imports and GlueContext initialization (lines 1-17).
  • Silver read, filter to completed orders, and two groupBy aggregations (revenue_by_category, customer_ltv).
  • Two write_dynamic_frame calls writing to gold/revenue_by_category/ and gold/customer_ltv/, followed by job.commit().
Upload the script and run the Gold Glue job

With the script written, you need to upload it to S3, create the Glue job, and run it. This follows the same pattern you used for the Bronze-to-Silver job.

  • Upload silver_to_gold.py to your S3 bucket by running this command:
aws s3 cp silver_to_gold.py s3://[[S3_BUCKET="your-bucket-name"]]/scripts/silver_to_gold.py
  • Confirm the upload succeeded by running:
aws s3 ls s3://[[S3_BUCKET="your-bucket-name"]]/scripts/

You should see both bronze_to_silver.py and silver_to_gold.py listed.

  • Create the Silver-to-Gold Glue job by running this command:
aws glue create-job \
    --name silver-to-gold \
    --role AWSGlueServiceRole-MedallionETL \
    --command '{"Name":"glueetl","ScriptLocation":"s3://[[S3_BUCKET="your-bucket-name"]]/scripts/silver_to_gold.py","PythonVersion":"3"}' \
    --default-arguments '{"--S3_BUCKET":"[[S3_BUCKET="your-bucket-name"]]"}' \
    --worker-type G.1X \
    --number-of-workers 2 \
    --glue-version "4.0"

What does this command do?

  • --name silver-to-gold names the job so you can reference it later.
  • --command tells Glue this is a glueetl (PySpark ETL) job and points to your script on S3.
  • --default-arguments passes your bucket name to the script as the --S3_BUCKET parameter.
  • --worker-type G.1X --number-of-workers 2 keeps costs low at 2 DPUs ($0.44/DPU-hour).
  • Start the job by running:
aws glue start-job-run --job-name silver-to-gold

The command returns a JobRunId. Copy this value for the next command.

  • Check the job status by running this command with your run ID:
aws glue get-job-run \
    --job-name silver-to-gold \
    --run-id [[JOB_RUN_ID="your-job-run-id"]]

How long does this take?

Glue jobs take a few minutes to provision workers and execute. Re-run the get-job-run command every 30-60 seconds until JobRunState shows SUCCEEDED.

Seeing FAILED instead of SUCCEEDED?

  • Check that silver_to_gold.py was uploaded to the correct path: s3://your-bucket/scripts/silver_to_gold.py.
  • Confirm Silver Parquet files exist at s3://your-bucket/silver/ from the previous step. If they are missing, re-run the Bronze-to-Silver job first.
  • Verify the IAM role AWSGlueServiceRole-MedallionETL has S3 permissions for your bucket.

Why is my silver-to-gold Glue job failing?

Crawl Gold data and validate with Athena queries

The Gold Parquet files are now on S3, but Athena cannot query them until they are registered in the Glue Data Catalog. A Crawler does this automatically.

  • Create a Gold Crawler by running:
aws glue create-crawler \
    --name gold-crawler \
    --role AWSGlueServiceRole-MedallionETL \
    --database-name medallion_db \
    --targets '{"S3Targets":[{"Path":"s3://[[S3_BUCKET="your-bucket-name"]]/gold/"}]}'
  • Start the Gold Crawler by running:
aws glue start-crawler --name gold-crawler
  • Wait about a minute, then check the crawler status by running:
aws glue get-crawler --name gold-crawler --query 'Crawler.State'

Re-run this command until you see READY. This means the Crawler has finished and registered the Gold tables.

What did the Crawler create?

The Crawler scanned gold/revenue_by_category/ and gold/customer_ltv/ and registered two new tables in medallion_db: revenue_by_category and customer_ltv. You can now query both with Athena SQL.

Before you run the validation query, what do you expect the results to look like? Will the categories show up in lowercase or mixed case?

  • Open the Athena console in your browser.
  • Make sure medallion_db is selected as your database.
  • Run this query to validate the revenue-by-category aggregation:
SELECT category, total_revenue, order_count
FROM gold_revenue_by_category
ORDER BY total_revenue DESC

You should see a clean, aggregated table with each category in lowercase. No duplicate categories, no null values, and correct totals.

  • Now run this query to validate the customer lifetime value table:
SELECT customer_id, total_spend, total_orders
FROM gold_customer_ltv
ORDER BY total_spend DESC
LIMIT 10

You should see the top 10 customers ranked by total spend. Each customer appears exactly once with their aggregated order metrics.

Not seeing the Gold tables in Athena?

  • Check that the Gold Crawler status is READY (not RUNNINGor STOPPING). Tables only appear after the Crawler finishes.
  • Confirm that medallion_db is selected as the database in the Athena query editor.
  • Verify that Parquet files exist under s3://your-bucket/gold/ by running aws s3 ls s3://your-bucket/gold/ --recursive.

Why are my Gold tables not showing in Athena after running the Crawler?

Your full medallion pipeline is now operational. Raw data enters Bronze, gets cleaned in Silver, and is aggregated into analytics-ready Gold tables. Next up, you will prove the pipeline's value by comparing Bronze and Gold results side by side.

Validate the Full Pipeline and Compare Results

In the last step, you built the Silver-to-Gold aggregation job and registered two new Gold tables in the Glue Data Catalog. Your pipeline now has all three medallion layers populated and queryable.

But how do you know the pipeline actually delivered value? In this step, you will run the same business question against both the raw Bronze data and the refined Gold data. The difference in accuracy and scan efficiency proves that every transformation you wrote was worth it.

In this step, get ready to:
  • Compare total revenue from Bronze vs Gold to see how duplicates inflate raw results.
  • Check Athena query statistics to measure the performance gap between raw JSON and partitioned Parquet.
  • Run a business-ready top-5 customer lifetime value query on Gold data.
Run the Bronze revenue query

Back in Step 2, you saw messy results when querying Bronze directly. Now you have clean Gold data to compare against. The most convincing proof is asking the exact same question of both layers and watching the numbers diverge.

Before you run this query, think about it: do you expect the Bronze total revenue to be higher, lower, or equal to the Gold total? Why?

  • Return to the Athena query editor in the AWS Management Console.
  • Make sure medallion_db is selected as the database in the left panel.
  • Query total revenue from the raw Bronze table by running this SQL:
-- Total revenue from raw Bronze data (completed orders only)
SELECT SUM(amount) AS total_revenue
FROM bronze_orders
WHERE status = 'completed'
  • Click Run.
  • Note the total_revenue value in the results. Write it down or keep this tab open.

Why filter to completed orders?

Your Gold revenue_by_category table only contains completed orders because the silver_to_gold.py script filtered on status = 'completed'. Adding the same WHERE clause to Bronze makes this an apples-to-apples comparison.

Run the Gold revenue query and compare
  • Open a new query tab by clicking the + icon next to your existing query tab.
  • Query total revenue from the Gold layer by running this SQL:
-- Total revenue from clean, deduplicated Gold data
SELECT SUM(total_revenue) AS total_revenue
FROM revenue_by_category
  • Click Run.

Compare the two results. The Bronze total_revenue is noticeably higher than the Gold total_revenue. That inflation comes from duplicate orders being summed multiple times in Bronze.

Why does Bronze over-report revenue?

Your raw data has ~50 duplicate orders. When you SUM(amount) on Bronze, every duplicate adds its amount again. The Gold table was built from Silver, which deduplicated on order_id. Gold's number is correct.

In a real business, this difference would mean reporting inflated revenue to stakeholders. That is exactly the kind of silent data quality bug that a medallion architecture prevents.

Check Athena query statistics for performance comparison

Data quality is only half the story. The other benefit of your pipeline is performance. Athena charges $5 per TB of data scanned, so reducing scan size saves real money at scale.

  • Switch back to the tab where you ran the Bronze query.
  • Look at the Data scanned value shown in the results bar below the query results. Note the number (it will be in KB).
  • Switch to the tab where you ran the Gold query.
  • Check the Data scanned value for this query.

The Gold query scans significantly less data than the Bronze query. Bronze forces Athena to read through the entire raw JSON file. Gold reads a compact, pre-aggregated Parquet file with only the columns it needs.

Why does this matter at scale?

With a few hundred records, the cost difference is fractions of a cent. But imagine this dataset at millions or billions of rows. A 10x reduction in data scanned means a 10x reduction in Athena cost.

Partitioned Parquet also scans faster because Athena can skip entire partitions that do not match the query filter. This is why production data teams invest in the Bronze-to-Silver-to-Gold pipeline you just built.

Data scanned values look the same?

With a small dataset, both values may appear as 0 KB because Athena rounds down. The absolute numbers matter less than the pattern. At production scale, the difference would be dramatic.

If your Gold query scans more data than expected, make sure you are querying revenue_by_category (the aggregated Gold table), not silver_orders.

Need help interpreting the results?

Run a business-ready top 5 customer lifetime value query

The revenue comparison proved accuracy and performance. Now run a query that a business stakeholder would actually ask for: who are the top 5 highest-value customers?

Before you run it, think about what you expect. Your Gold customer_ltv table has pre-computed total_spend, total_orders, and avg_order_value for each customer. Do you think the results will be clean and ready to present, or will they need more processing?

  • Open a new query tab in Athena.
  • Run this query to find the top 5 customers by lifetime value:
-- Top 5 customers by total lifetime spend
SELECT customer_id,
       total_spend,
       total_orders,
       avg_order_value
FROM customer_ltv
ORDER BY total_spend DESC
LIMIT 5
  • Click Run.

You see five rows with clean customer IDs, precise spending totals, order counts, and average order values. No duplicates, no nulls, no formatting issues. This is a result you could hand directly to a stakeholder.

What does this prove?

Running this query on the raw bronze_orders table would require writing the aggregation logic in SQL every time, and the results would be wrong because of duplicates. Your Gold layer pre-computes these metrics once during the ETL pipeline.

Analysts can now write simple SELECT queries against Gold tables and trust the numbers immediately. That is the payoff of a medallion architecture.

Seeing zero rows or an error?

Make sure medallion_db is still selected as your database. If you switched databases or sessions, Athena will not find the customer_ltv table.

If the table does not appear in the left panel, your Gold Crawler may not have finished. Return to the AWS Glue console and confirm the gold-crawler status is Ready.

Need help debugging?

Secret mission

Orchestrate with a Glue Workflow

Your pipeline works, but you ran every job and crawler by hand. In production, nobody kicks off four separate steps manually. Can you wire the entire Bronze-to-Gold pipeline into a single Glue Workflow that runs end-to-end with one trigger?

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 jobs or queries.

Cost warning

Your Glue jobs cost $0.44 per DPU-hour while running. You ran several short jobs during this project, which likely totaled $2-4. No further charges accrue unless you run additional jobs or queries. Glue Crawlers also charge $0.44 per DPU-hour with a 10-minute minimum per run.

If you leave everything in place but do not run any jobs, the only potential ongoing cost is negligible S3 storage for your small dataset.

Resources you used:

  • S3 bucket with Bronze, Silver, and Gold data layers.
  • Two AWS Glue ETL jobs (bronze-to-silver and silver-to-gold).
  • Three Glue Crawlers (bronze-crawler, silver-crawler, gold-crawler).
  • Glue Data Catalog database medallion_db with four tables.
  • IAM role AWSGlueServiceRole-MedallionETL with attached policies.
  • Glue Workflow medallion-pipeline with four triggers (Secret Mission).
  • Local project files (generate_data.py, bronze_to_silver.py, silver_to_gold.py).
  • Athena query results stored in the athena-results/ prefix.

Keep everything running

No action needed. Choose this if you want to keep your data lake available for portfolio demos or further experimentation.

  • All services used in this project are serverless. You only incur charges when you actively run Glue jobs, Crawlers, or Athena queries.
  • S3 storage for your small dataset costs fractions of a cent per month.
  • The Glue Data Catalog free tier covers up to 1 million objects and 1 million requests per month, so your tables cost nothing to keep registered.

Pause - I'll come back to this later

Since all resources in this project are serverless, there is nothing to pause. Charges only occur when you run jobs or queries.

  • Your S3 data, Glue jobs, Crawlers, and Data Catalog tables remain in place at negligible cost.
  • You can re-run your Glue jobs or Athena queries at any time without reconfiguring anything.

Delete - I don't want to use this again

Remove all project resources to ensure no future charges. Run the following commands in your terminal.

Delete the Glue Workflow and its triggers
  • Delete all four triggers and the workflow by running these commands:
aws glue delete-trigger --name start-bronze-to-silver
aws glue delete-trigger --name after-bronze-to-silver
aws glue delete-trigger --name after-silver-crawler
aws glue delete-trigger --name after-silver-to-gold
aws glue delete-workflow --name medallion-pipeline
Delete the Glue jobs
  • Delete both ETL jobs by running:
aws glue delete-job --job-name bronze-to-silver
aws glue delete-job --job-name silver-to-gold
Delete the Glue Crawlers
  • Delete all three Crawlers by running:
aws glue delete-crawler --name bronze-crawler
aws glue delete-crawler --name silver-crawler
aws glue delete-crawler --name gold-crawler
Delete the Glue database and its tables
  • Delete the database by running the following command. This also removes all tables registered in the database.
aws glue delete-database --name medallion_db
Delete the S3 bucket
  • Delete the bucket and all its contents by running:
aws s3 rb s3://[[BUCKET_NAME="medallion-datalake-your-account-id"]] --force
Delete the IAM role

Before you can delete an IAM role, you must remove all attached policies first.

  • List the inline policies on your role by running:
aws iam list-role-policies --role-name AWSGlueServiceRole-MedallionETL
  • Delete each inline policy that appears in the output. Replace the policy name with the one shown in your results:
aws iam delete-role-policy --role-name AWSGlueServiceRole-MedallionETL --policy-name [[INLINE_POLICY_NAME="your-inline-policy-name"]]
  • Detach the managed policy by running:
aws iam detach-role-policy --role-name AWSGlueServiceRole-MedallionETL --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
  • Delete the role by running:
aws iam delete-role --role-name AWSGlueServiceRole-MedallionETL
Delete local project files
  • Remove the local project directory from your Desktop by running:
rm -rf ~/Desktop/medallion-datalake

Nice Work!

Nice Work!

You just built a production-style serverless data lake on AWS. Messy raw data went in, and clean, partitioned, business-ready analytics came out the other side.

You've learned how to:

  • Build a three-layer medallion data lake (Bronze/Silver/Gold) on Amazon S3 and write PySpark ETL jobs with AWS Glue DynamicFrames for data cleaning, deduplication, and aggregation.
  • Configure Glue Crawlers to automatically discover schemas and register tables in the Glue Data Catalog.
  • Query analytics-ready Gold data with Amazon Athena SQL and prove the quality and performance improvement over raw Bronze data.
  • Orchestrate the full Bronze-to-Gold pipeline with a Glue Workflow that chains jobs and crawlers into a single automated run (Secret Mission).

Ready to quiz yourself?