Build a Medallion Pipeline on AWS

Transform messy orders into trusted analytics with AWS Glue and Athena.

Introduction

30 Second Summary

Food-delivery orders rarely arrive ready for a report. A duplicate order or malformed price can quietly turn a simple revenue total into a misleading number.

In this project, you will build a batch medallion data pipeline that turns messy Campus Eats orders into trusted restaurant metrics. The pipeline preserves raw evidence in Bronze before publishing validated records in Silver and business-ready metrics in Gold.

What You'll Build

Your final demo traces one messy order file through all three layers with SQL results that prove duplicate records and malformed values no longer distort the final metrics.

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

  • A traceable data journey from the untouched orders.csv file in Amazon S3 to clean Apache Parquet outputs you can inspect.
  • A repeatable quality job in AWS Glue that uses PySpark to safely convert types, filter invalid rows, and remove duplicate orders.
  • A SQL proof of trust in Amazon Athena that shows 12 raw rows becoming eight valid orders plus four Gold metric rows.
  • Secret Mission: Add a rejected-record quarantine with a human-readable reason for every invalid row.

Are there any prerequisites?

You'll need a Mac with TextEdit plus an AWS account with administrator access. Basic Python and SQL knowledge is enough.

Before We Start

Before any AWS work begins, take a moment to commit to the Campus Eats pipeline and the purpose of each layer. Bronze preserves the unchanged messy CSV as raw evidence, Silver contains validated and deduplicated typed orders, and Gold contains analytics-ready restaurant-and-zone metrics.

Set Up the Lakehouse Landing Zone

Your pipeline needs isolated storage before it can protect the raw file or publish trusted outputs. It also needs permission before AWS Glue can move data between layers.

You will create the messy source file in TextEdit. Then you will build four private Amazon S3 buckets in one AWS Region.

IAM will let AWS Glue access those buckets. Amazon Athena will store query results in the assets bucket.

In this step, get ready to:
  • Create the malformed source CSV in plain-text mode.
  • Build the private Amazon S3 landing zone for the pipeline.
  • Connect the pipeline services to the landing zone.
Create the messy source file

The Bronze layer earns its value by keeping source evidence unchanged. This CSV fixture includes the quality problems that later layers must handle.

  • Press Cmd+Space to open Spotlight.
  • Type TextEdit into Spotlight.
  • Press Enter to open TextEdit.
  • Choose Format from the menu bar.
  • Choose Make Plain Text.
  • Replace the blank document with the source data below:
order_id,ordered_at,restaurant,campus_zone,order_total,delivery_minutes,status
O1001,2026-08-20 11:05:00,Green Bowl,North,12.50,22,delivered
O1002,2026-08-20 11:12:00,Pizza Lab,East,$18.00,31,delivered
O1003,2026-08-20 12:01:00,Green Bowl,North,9.75,18,delivered
O1004,2026-08-20 12:25:00,Curry Corner,West,14.25,28,delivered
O1005,2026-08-20 13:10:00,Pizza Lab,East,21.50,35,delivered
O1005,2026-08-20 13:10:00,Pizza Lab,East,21.50,35,delivered
O1006,not-a-date,Green Bowl,South,11.00,20,delivered
O1007,2026-08-20 18:15:00,Curry Corner,West,N/A,40,delivered
O1008,2026-08-20 18:30:00,Sushi Spot,South,24.00,27,delivered
O1009,2026-08-20 19:05:00,Sushi Spot,South,16.50,,delivered
O1010,2026-08-20 19:30:00,Pizza Lab,East,8.50,19,cancelled
O1011,2026-08-20 20:10:00,Curry Corner,West,17.00,26,delivered

What is messy about this file?

  • The order ID O1005 appears twice.
  • The order_total column contains $18.00 and N/A.
  • The ordered_at column contains not-a-date.
  • The delivery_minutes value is missing for O1009.
  • Press Cmd+S to open the save dialog.
  • Enter orders.csv in the Save As field.
  • Select your Desktop as the save location.
  • Choose Save.
  • Choose the option that keeps the .csv extension if TextEdit asks about the file type.
  • Select Desktop in the Finder sidebar.
  • Confirm the file name is exactly orders.csv.

Your first project artifact is ready. The unchanged source file now carries every duplicate and malformed value into Bronze.

Seeing the wrong file extension?

If Finder shows orders.csv.txt, rename the file to orders.csv. Confirm the extension change when macOS asks.

If the rows appear as formatted text, return to TextEdit and choose Make Plain Text before saving again.

Help me fix my CSV file

✔️ Awesome, I've got everything!

Great. Double-check that orders.csv is saved on your Desktop.

ⓧ I'd like to double check the full code

Compare your file with the complete fixture below. Every row must match before you upload it.

order_id,ordered_at,restaurant,campus_zone,order_total,delivery_minutes,status
O1001,2026-08-20 11:05:00,Green Bowl,North,12.50,22,delivered
O1002,2026-08-20 11:12:00,Pizza Lab,East,$18.00,31,delivered
O1003,2026-08-20 12:01:00,Green Bowl,North,9.75,18,delivered
O1004,2026-08-20 12:25:00,Curry Corner,West,14.25,28,delivered
O1005,2026-08-20 13:10:00,Pizza Lab,East,21.50,35,delivered
O1005,2026-08-20 13:10:00,Pizza Lab,East,21.50,35,delivered
O1006,not-a-date,Green Bowl,South,11.00,20,delivered
O1007,2026-08-20 18:15:00,Curry Corner,West,N/A,40,delivered
O1008,2026-08-20 18:30:00,Sushi Spot,South,24.00,27,delivered
O1009,2026-08-20 19:05:00,Sushi Spot,South,16.50,,delivered
O1010,2026-08-20 19:30:00,Pizza Lab,East,8.50,19,cancelled
O1011,2026-08-20 20:10:00,Curry Corner,West,17.00,26,delivered
Build the private S3 landing zone

A medallion architecture separates raw Bronze data from validated Silver data. Gold receives the smaller datasets built for analysis.

A fourth bucket holds scripts and temporary files. It also gives Athena a dedicated location for query results.

Why use separate buckets?

Each bucket gives one layer a clear storage boundary. This makes it easier to protect raw evidence while allowing transformed outputs to be replaced.

The assets bucket keeps operational files away from the datasets you query. Each location has one job.

Every S3 bucket name must be unique. One reusable suffix keeps the four names connected while avoiding a naming collision.

Keep Costs Predictable

This setup creates usage-based AWS resources. Normal completion is estimated at $0.10 to $1.00.

The cleanup section removes the resources when you finish. Avoid unnecessary job reruns later in the project.

Signing in uses your normal AWS credentials. Keep those credentials inside the AWS Management Console.

  • Sign in to the AWS Management Console with your administrator account.
  • Open the Region menu in the top navigation bar.
  • Choose one AWS Region for every resource in this project.
  • Record the selected Region here: your selected AWS Region.
  • Open Amazon S3 from the services menu.
  • Choose General purpose buckets in the left navigation pane.

Use These Four Bucket Names

  • Bronze bucket: campus-eats-bronze-<unique-suffix>.
  • Silver bucket: campus-eats-silver-<unique-suffix>.
  • Gold bucket: campus-eats-gold-<unique-suffix>.
  • Assets bucket: campus-eats-assets-<unique-suffix>.
  • Choose a unique suffix containing lowercase letters or numbers.
  • Record that suffix here: <unique-suffix>.
  • Choose Create bucket on the General purpose buckets page.
  • Enter one complete name from the bucket list in the Bucket name field.
  • Choose your selected AWS Region in the Region field.
  • Keep every Block Public Access setting enabled.
  • Choose Create bucket at the bottom of the page.
  • Repeat this flow until all four bucket names appear on the General purpose buckets page.

That is the storage backbone in place. You now have a private boundary for every layer of the pipeline.

Bucket name already taken?

Choose a longer suffix if Amazon S3 reports that a bucket name is unavailable. Update <unique-suffix> before creating all four buckets with the new value.

Use only lowercase letters and numbers in the suffix. Keep the hyphens already included in each prefix.

Help me fix my S3 bucket names

  • Choose the bucket whose name starts with campus-eats-assets-.
  • Use the folder creation control above the object list to create athena-results/.
  • Use the folder creation control above the object list to create scripts/.
  • Use the folder creation control above the object list to create temporary/.
  • Confirm all three prefixes appear in the assets bucket.

The assets bucket now separates query results from job scripts and temporary files.

  • Return to General purpose buckets.
  • Choose the bucket whose name starts with campus-eats-bronze-.
  • Choose Upload.
  • Choose Add file.
  • Select orders.csv from your Desktop.
  • Choose Upload.
  • Confirm the success message on the Upload: status page.

Amazon S3 has accepted the raw source object. Bronze now holds the exact file you created.

  • Return to the Bronze bucket object list.
  • Confirm orders.csv appears at the bucket root.

Upload missing or unsuccessful?

Confirm that you selected the Bronze bucket before uploading. The bucket name must start with campus-eats-bronze-.

If the object name ends in .txt, fix the local file name before uploading it again.

Help me troubleshoot the S3 upload

Connect AWS Glue and Athena

The AWS Glue job needs a service role before it can read Bronze or write transformed data. The role grants those permissions without placing credentials inside the job script.

Athena also needs an S3 destination before it can run a query. The assets bucket gives every result a predictable home.

  • Switch from Amazon S3 to IAM in the AWS Management Console.
  • Choose Roles in the left navigation pane.
  • Choose Create role.
  • Choose AWS service as the trusted entity type.
  • Choose AWS Glue as the service or use case.
  • Choose Next.

Keep the Permission Temporary

The AmazonS3FullAccess policy grants broad access to Amazon S3. This is an intentional shortcut for the short project.

The cleanup section removes the role after the project. Production roles should limit access to the required buckets.

  • Select AWSGlueServiceRole on the Add permissions page.
  • Select AmazonS3FullAccess.
  • Choose Next.
  • Enter AWSGlueServiceRole-CampusEats in the Role name field.
  • Review the trusted service and attached policies.
  • Choose Create role.

IAM returns you to the roles list after creating the service role.

  • Open AWSGlueServiceRole-CampusEats from the roles list.
  • Confirm the trust relationship names AWS Glue.
  • Confirm AWSGlueServiceRole and AmazonS3FullAccess appear as attached managed policies.

Your service role is ready. AWS Glue now has the permission path it needs for the pipeline.

  • Switch from IAM to Amazon Athena in the AWS Management Console.
  • Open the primary workgroup.
  • Choose Settings.
  • Choose Manage.
  • Enter s3://campus-eats-assets-<unique-suffix>/athena-results/ under Location of query result.
  • Choose Save.

Athena now has somewhere to write query output. This setting unlocks the Bronze queries in the next step.

Athena location not saving?

Confirm that the bucket name matches your assets bucket exactly. Keep the trailing slash after athena-results/.

Confirm that the assets bucket uses the same Region as Athena. Region mismatches can prevent the location from working as expected.

Help me fix the Athena result location

Before you check the landing zone, picture the three signals that prove it is ready.

  • Return to the Bronze bucket in Amazon S3.
  • Confirm orders.csv appears at the bucket root.
  • Return to the primary workgroup settings in Athena.
  • Confirm s3://campus-eats-assets-<unique-suffix>/athena-results/ appears as the query result location.
  • Return to AWSGlueServiceRole-CampusEats in IAM.
  • Confirm the role trusts AWS Glue and has both managed policies attached.

You will see orders.csv in Bronze. Athena will show the assets bucket path under the query result location.

IAM will show the AWS Glue trust relationship with both required policies. That completes the landing zone your pipeline depends on.

Your source file, storage layers, query output path, and service role are ready. Next up, you will query Bronze and expose the data quality problem hiding inside the raw strings.

Query Bronze Data Quality

Your landing zone is ready with the raw order file safely stored in Amazon S3. You can now test whether those source records support a trustworthy revenue total.

Amazon Athena can query the Bronze layer through a table registered in the AWS Glue Data Catalog. Bronze preserves source fidelity, so its duplicate records and raw strings reveal the risks of using unvalidated data for analytics.

In this step, get ready to:
  • Register the Bronze CSV as an external table with string-typed columns.
  • Count the raw rows before inspecting duplicate records and malformed values.
  • Test a naive revenue query to expose the limitation of the Bronze schema.
Register the Bronze table

A schema-on-read table gives Athena instructions for interpreting files stored in S3. Every source column stays typed as a string so the catalog reflects the unchanged Bronze data.

  • Return to the Athena query editor from earlier.
  • Confirm that the query editor uses the primary workgroup.
  • Create a new query tab for 01_bronze.sql.
  • Add the catalog database statement by pasting this code:
CREATE DATABASE IF NOT EXISTS campus_eats;

What Does This Statement Do?

The statement creates the campus_eats database for the pipeline's table definitions. The existing database remains unchanged if you run the statement again.

  • Select the database statement in the query editor.
  • Click Run to create the database.

You should see a successful query status. The campus_eats database is now ready to hold the Bronze table definition.

  • Add the external table statement below the database statement by pasting this code:
CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.bronze_orders (
    order_id string,
    ordered_at string,
    restaurant string,
    campus_zone string,
    order_total string,
    delivery_minutes string,
    status string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '"',
    'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://campus-eats-bronze-<unique-suffix>/'
TBLPROPERTIES (
    'skip.header.line.count' = '1',
    'classification' = 'csv'
);

How Does This Table Preserve Bronze?

  • The seven columns use the string type so malformed values remain visible during inspection.
  • The CSV SerDe defines how Athena reads separators, quotes, and escape characters from orders.csv.
  • The LOCATION points to the Bronze bucket that already contains the source file.
  • The table properties skip the CSV header row while preserving all 12 data rows.
  • Replace <unique-suffix> in the LOCATION with the suffix from your Bronze bucket.
  • Select the external table statement in the query editor.
  • Click Run to register the table.

You should see a successful query status. The table definition now points to the unchanged orders.csv object in your Bronze bucket.

  • Expand the campus_eats database in the query editor's table list.
  • Confirm that bronze_orders appears with seven columns.

That catalog entry connects Athena to your raw CSV without changing the source file. Your Bronze evidence remains intact.

Bronze Table Missing?

Confirm that the query editor is using the same AWS Region as your Bronze bucket. Check that the table's LOCATION contains your real bucket suffix.

If the statement fails, verify that its S3 location ends with a forward slash. The location must point to the bucket path containing orders.csv.

Help me troubleshoot my Bronze table

Inspect the raw order records

Row counts reveal whether the table exposes the complete source file. Comparing the total row count with distinct order IDs also surfaces duplicated business records.

Before you run the count, do you expect every source row to have a unique order ID?

  • Add the Bronze count query below the table statement by pasting this code:
SELECT
    COUNT(*) AS raw_rows,
    COUNT(DISTINCT order_id) AS unique_orders
FROM campus_eats.bronze_orders;

What Does This Query Measure?

  • The raw_rows result counts every record exposed by the Bronze table.
  • The unique_orders result counts distinct values in order_id.
  • A difference between the two counts proves that at least one order ID appears more than once.
  • Select the count query in the query editor.
  • Click Run to count the Bronze records.

You should see 12 under raw_rows. You should see 11 under unique_orders.

There is the first quality signal. Bronze has preserved every source row, including a duplicated order ID.

Count Results Look Different?

Confirm that orders.csv sits at the root of the Bronze bucket. Check that the table location points to that bucket.

A count of 13 can mean the CSV header was read as data. Verify that the table properties skip one header row.

Help me fix my Bronze row count

The count proves that duplication exists. Inspecting the rows identifies the duplicate and shows which raw values need validation before analysis.

  • Add the row inspection query below the count query by pasting this code:
SELECT *
FROM campus_eats.bronze_orders
ORDER BY order_id;

What Does This Query Expose?

The query returns every Bronze row in order ID sequence. This makes repeated IDs and malformed source values easier to compare.

  • Select the row inspection query in the query editor.
  • Click Run to inspect the raw records.

You should see O1005 twice. The raw values also include $18.00, N/A, not-a-date, and an empty delivery time.

Rows Missing or Out of Order?

Confirm that you selected the entire query through ORDER BY order_id before running it. Verify that the table still points to the Bronze bucket containing the supplied orders.csv file.

Help me inspect the Bronze rows

Test the naive revenue query

The final query asks Athena to calculate revenue directly from the raw order_total column. This tests whether the Bronze table can support a numeric business metric in its current form.

Before you run this query, do you think Athena can add values from a string-typed column?

  • Add the naive revenue query below the row inspection query by pasting this code:
SELECT SUM(order_total) AS naive_revenue
FROM campus_eats.bronze_orders;

What Does This Query Test?

The query passes the raw order_total column to SUM. A successful result would require values that Athena can treat as numbers.

  • Select the naive revenue query in the query editor.
  • Click Run to test the raw column.

You should see the query fail because Athena cannot aggregate the string-typed order_total column. This is the intended shortfall.

This Failure Is Evidence

Bronze preserves values such as $18.00 and N/A as source evidence. A reliable revenue total needs safe type conversion, validation, and deduplication in the Silver layer.

  • Save the query tab as 01_bronze.sql using the save control above the query editor.
  • Confirm that 01_bronze.sql appears in your saved queries.

✔️ Awesome, I've got everything!

Your saved 01_bronze.sql query now contains the database, Bronze table, inspection queries, and expected failing revenue test.

ⓧ I'd like to double check the full code

Compare your saved query with this complete script. Keep your real bucket suffix in the LOCATION before running the table statement.

CREATE DATABASE IF NOT EXISTS campus_eats;

CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.bronze_orders (
    order_id string,
    ordered_at string,
    restaurant string,
    campus_zone string,
    order_total string,
    delivery_minutes string,
    status string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '"',
    'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://campus-eats-bronze-<unique-suffix>/'
TBLPROPERTIES (
    'skip.header.line.count' = '1',
    'classification' = 'csv'
);

SELECT
    COUNT(*) AS raw_rows,
    COUNT(DISTINCT order_id) AS unique_orders
FROM campus_eats.bronze_orders;

SELECT *
FROM campus_eats.bronze_orders
ORDER BY order_id;

SELECT SUM(order_total) AS naive_revenue
FROM campus_eats.bronze_orders;
  • Return to the Bronze count query in 01_bronze.sql.

Before you run the final check, what difference do you expect between the raw row count and the unique order count?

  • Click Run to verify the Bronze counts again.

You should see 12 raw rows and 11 unique order IDs. Athena now exposes the duplicate and malformed source values while the original CSV remains unchanged.

Your Bronze data is cataloged, inspected, and proven unsuitable for direct revenue analysis. Next up, you'll build the Silver layer that safely converts types and removes invalid duplicate records.

Clean and Validate Silver Data

The Bronze layer preserved all 12 source rows. Amazon Athena exposed the consequence because revenue could not be summed from a string column.

The Silver layer turns that raw text into typed records. AWS Glue applies safe conversions, validation rules, and order-level deduplication before it writes Apache Parquet.

In this step, get ready to:
  • Create an AWS Glue 6.0 Spark job for the Silver transformation.
  • Convert raw strings into typed values before filtering invalid records.
  • Catalog the Silver dataset and verify its row counts in Athena.
Create the AWS Glue job

An AWS Glue Spark job provides the processing engine for the pipeline. It reads the unchanged Bronze file before writing validated rows to the Silver bucket.

Why AWS Glue instead of Athena-only SQL?

Athena can create curated output with SQL. AWS Glue makes the distributed processing stage explicit.

You can express safe parsing and deduplication directly in PySpark. The same job can grow as the pipeline gains more transformation rules.

  • Return to the AWS Management Console from earlier.
  • Confirm that the selected AWS Region matches the Region containing your four buckets.
  • Enter AWS Glue in the console search bar.
  • Select AWS Glue from the search results.
  • Choose Jobs.
  • Choose Spark script editor.
  • Choose Create.

You should see a new Spark job editor with a generated script and configurable job details.

  • Enter campus-eats-medallion as the job name.
  • Select AWSGlueServiceRole-CampusEats as the IAM role.

The job name and service role should now appear in the configuration panel.

  • Choose Glue 6.0 - Supports Spark 4.1.1, Scala 2, Python 3 for Glue version.
  • Set the script language to Python.

The job now uses the required AWS Glue runtime with a Python Spark script.

  • Set the worker type to G.1X.
  • Set the worker count to 2.

The capacity settings should show two G.1X workers.

  • Set Script path to s3://campus-eats-assets-<unique-suffix>/scripts/.
  • Set Temporary path to s3://campus-eats-assets-<unique-suffix>/temporary/.

Both paths should point to the assets bucket from earlier. Each path should end with a forward slash.

Why does the job need two paths?

The script path stores the job code in Amazon S3. The temporary path gives Spark a working area for intermediate job data.

  • Add --BRONZE_PATH as a job parameter key.
  • Set its value to s3://campus-eats-bronze-<unique-suffix>/.

The first parameter now points the job at the unchanged Bronze CSV.

  • Add --SILVER_PATH as another job parameter key.
  • Set its value to s3://campus-eats-silver-<unique-suffix>/orders/.

The second parameter now points the job at the Silver output prefix.

  • Choose Save.

The editor should retain the job name after saving. Your Spark job now has the role, runtime, capacity, paths, and parameters needed by the script.

Job configuration not saving?

Check that both S3 paths end with a forward slash. Confirm that the selected role is AWSGlueServiceRole-CampusEats.

Make sure both parameter keys begin with two hyphens. A missing hyphen prevents the script from resolving that argument.

Help me troubleshoot the AWS Glue job setup.

Build the Silver transformation

The script first initializes the AWS Glue job before reading the Bronze CSV without schema inference. Keeping the source values as strings gives your transformation full control over malformed data.

  • Replace the generated script with this initialization and Bronze-reading block:
import sys

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

args = getResolvedOptions(
    sys.argv,
    ["JOB_NAME", "BRONZE_PATH", "SILVER_PATH"],
)

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

raw_df = spark.read.csv(
    args["BRONZE_PATH"],
    header=True,
    inferSchema=False,
)

What does this initialization do?

  • The imports provide AWS Glue job utilities and PySpark DataFrame functions.
  • getResolvedOptions loads the job name plus both S3 path parameters.
  • raw_df reads the CSV header while preserving every source column as text.
  • Choose Save.

The editor should show the script as saved. The job now has a valid initialization path to the Bronze source.

Seeing editor warnings?

Compare the imports and indentation with the code block. Check that the three argument names use underscores without leading hyphens inside getResolvedOptions.

Help me check the AWS Glue initialization code.

Safe conversions turn malformed dates or numbers into null values. Those nulls can be filtered without terminating the job.

  • Append this normalization block directly below the raw_df block:
normalized_df = raw_df.select(
    F.trim(F.col("order_id")).alias("order_id"),
    F.try_to_timestamp(
        F.col("ordered_at"),
        F.lit("yyyy-MM-dd HH:mm:ss"),
    ).alias("ordered_at"),
    F.trim(F.col("restaurant")).alias("restaurant"),
    F.trim(F.col("campus_zone")).alias("campus_zone"),
    F.regexp_replace(F.col("order_total"), r"[$,]", "")
    .try_cast("double")
    .alias("order_total"),
    F.col("delivery_minutes").try_cast("int").alias("delivery_minutes"),
    F.lower(F.trim(F.col("status"))).alias("status"),
)

How does normalization protect the job?

  • try_to_timestamp parses valid timestamps while converting malformed dates to null.
  • regexp_replace removes currency punctuation before try_cast converts the total to a number.
  • try_cast converts delivery times to integers while turning invalid values into null.
  • trim and lower standardize text before validation.
  • Choose Save.

Before you run the checkpoint

The next click starts billable AWS Glue compute. The workers stop when the script finishes.

Expect the run to spend a few minutes provisioning its workers. A short wait at the beginning is normal.

Before this checkpoint runs, do you expect the malformed source values to stop the script or become nulls?

  • Start the checkpoint by choosing Run.
  • Switch to the Runs tab.

You should see the checkpoint reach SUCCEEDED. Good progress. The job can initialize and build the normalization plan without a script error.

Checkpoint run did not succeed?

Check that the Bronze path points to the bucket containing orders.csv. Confirm that both job parameters are present in the saved configuration.

Compare every parenthesis and comma in normalized_df with the code block above.

Help me troubleshoot the normalization checkpoint.

Validation now decides which normalized rows can enter Silver. Deduplication then keeps one row for each order_id.

  • Append this validation, write, and row-count block below normalized_df:
valid_condition = (
    F.col("order_id").isNotNull()
    & (F.col("order_id") != "")
    & F.col("ordered_at").isNotNull()
    & F.col("restaurant").isNotNull()
    & (F.col("restaurant") != "")
    & F.col("campus_zone").isNotNull()
    & (F.col("campus_zone") != "")
    & F.col("order_total").isNotNull()
    & (F.col("order_total") > 0)
    & F.col("delivery_minutes").isNotNull()
    & (F.col("delivery_minutes") > 0)
    & F.col("status").isin("delivered", "cancelled")
)

silver_df = normalized_df.filter(valid_condition).dropDuplicates(["order_id"])
silver_df.write.parquet(args["SILVER_PATH"], mode="overwrite")

print(f"Raw rows: {raw_df.count()}")
print(f"Silver rows: {silver_df.count()}")

job.commit()

How does the Silver filter work?

  • valid_condition requires identifiers, timestamps, restaurant names, campus zones, positive totals, and positive delivery times.
  • isin accepts only the two expected order statuses.
  • dropDuplicates removes the repeated O1005 record using order_id as the comparison key.
  • mode="overwrite" replaces the previous Silver output when the job runs again.
  • job.commit() completes the initialized AWS Glue job lifecycle.
  • Choose Save.

Before you run the complete transformation, how many rows do you expect after malformed records and the duplicate order are removed?

Worker provisioning can take a few minutes again. Let this run finish before starting another one.

  • Start the complete transformation by choosing Run.
  • Return to the Runs tab.
  • Select the completed run.
  • Open its output log from the run details.

You should see SUCCEEDED for the run. The output log should include Raw rows: 12 and Silver rows: 8.

That is the transformation working: all 12 source rows remain in Bronze while eight valid order IDs reach Silver.

Missing the expected row counts?

Confirm that --BRONZE_PATH ends at the Bronze bucket root. Confirm that --SILVER_PATH ends at the Silver orders/ prefix.

If the run fails during parsing, compare the normalization expressions with the full script below. If the counts differ, check the validation conditions and the order_id deduplication key.

Help me troubleshoot the Silver job output.

✔️ Awesome, I've got everything!

Your saved medallion_pipeline.py initializes the job, normalizes the CSV, validates rows, removes duplicate order IDs, writes Parquet, and prints both row counts.

ⓧ I'd like to double check the full code

Compare your saved AWS Glue script with this complete Silver version.

import sys

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

args = getResolvedOptions(
    sys.argv,
    ["JOB_NAME", "BRONZE_PATH", "SILVER_PATH"],
)

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

raw_df = spark.read.csv(
    args["BRONZE_PATH"],
    header=True,
    inferSchema=False,
)

normalized_df = raw_df.select(
    F.trim(F.col("order_id")).alias("order_id"),
    F.try_to_timestamp(
        F.col("ordered_at"),
        F.lit("yyyy-MM-dd HH:mm:ss"),
    ).alias("ordered_at"),
    F.trim(F.col("restaurant")).alias("restaurant"),
    F.trim(F.col("campus_zone")).alias("campus_zone"),
    F.regexp_replace(F.col("order_total"), r"[$,]", "")
    .try_cast("double")
    .alias("order_total"),
    F.col("delivery_minutes").try_cast("int").alias("delivery_minutes"),
    F.lower(F.trim(F.col("status"))).alias("status"),
)

valid_condition = (
    F.col("order_id").isNotNull()
    & (F.col("order_id") != "")
    & F.col("ordered_at").isNotNull()
    & F.col("restaurant").isNotNull()
    & (F.col("restaurant") != "")
    & F.col("campus_zone").isNotNull()
    & (F.col("campus_zone") != "")
    & F.col("order_total").isNotNull()
    & (F.col("order_total") > 0)
    & F.col("delivery_minutes").isNotNull()
    & (F.col("delivery_minutes") > 0)
    & F.col("status").isin("delivered", "cancelled")
)

silver_df = normalized_df.filter(valid_condition).dropDuplicates(["order_id"])
silver_df.write.parquet(args["SILVER_PATH"], mode="overwrite")

print(f"Raw rows: {raw_df.count()}")
print(f"Silver rows: {silver_df.count()}")

job.commit()

What should match?

This reference is the complete cumulative script for the Silver job. The argument list ends with SILVER_PATH because Gold processing belongs to the next step.

Catalog and verify Silver

The Parquet files now exist in the Silver bucket. An external table in AWS Glue Data Catalog gives Athena the schema and S3 location needed to query them.

  • Switch back to the Athena query editor from earlier.
  • Create a new query editor tab.
  • Build the Silver section of 02_curated_tables.sql by pasting this statement:
CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.silver_orders (
    order_id string,
    ordered_at timestamp,
    restaurant string,
    campus_zone string,
    order_total double,
    delivery_minutes int,
    status string
)
STORED AS PARQUET
LOCATION 's3://campus-eats-silver-<unique-suffix>/orders/';

What does this table definition provide?

The schema exposes ordered_at as a timestamp. It exposes order_total as a double and delivery_minutes as an integer.

STORED AS PARQUET tells Athena how to read the files. The LOCATION points to the exact prefix written by the job.

  • Replace <unique-suffix> with the suffix used by your Silver bucket.
  • Run the external-table statement.
  • Refresh the table list for the campus_eats database.

You should see silver_orders in the table list. This confirms that the Silver Parquet schema is registered in the catalog.

Silver table missing or unreadable?

Check that the table location ends with /orders/ and matches the --SILVER_PATH job parameter. Confirm that the AWS Glue run reached SUCCEEDED before creating the table.

Help me troubleshoot the Silver catalog table.

Before you query Silver, what relationship do you expect between its total row count and distinct order count?

  • Verify the Silver row counts by running this query:
SELECT
    COUNT(*) AS silver_rows,
    COUNT(DISTINCT order_id) AS unique_orders
FROM campus_eats.silver_orders;

What does this query prove?

silver_rows counts every validated record. unique_orders checks whether duplicate order IDs remain.

Matching counts prove that every Silver row has a unique order identifier.

You should see 8 for silver_rows and 8 for unique_orders.

Counts do not show eight and eight?

If both counts are higher, confirm that valid_condition checks every required field. Check that dropDuplicates(["order_id"]) remains in the script.

If Athena returns no rows, verify that the table location matches the Silver output prefix. Run the AWS Glue job again after correcting the path.

Help me explain the unexpected Silver counts.

✔️ Awesome, I've got everything!

Your 02_curated_tables.sql now defines the Silver table and verifies its total and distinct order counts.

ⓧ I'd like to double check the full code

Compare your Silver SQL with this complete file.

CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.silver_orders (
    order_id string,
    ordered_at timestamp,
    restaurant string,
    campus_zone string,
    order_total double,
    delivery_minutes int,
    status string
)
STORED AS PARQUET
LOCATION 's3://campus-eats-silver-<unique-suffix>/orders/';

SELECT
    COUNT(*) AS silver_rows,
    COUNT(DISTINCT order_id) AS unique_orders
FROM campus_eats.silver_orders;

What should this file contain?

The first statement registers the Parquet schema and location. The second statement checks that Silver contains only unique validated orders.

Your Silver layer now converts messy strings into eight typed and uniquely identified orders. Next up, you will aggregate those records into restaurant metrics for the Gold layer.

Publish Gold Restaurant Metrics

Your Silver layer now contains eight valid orders with eight unique IDs. That cleanup gives engineers trustworthy row-level data.

Business consumers still need a smaller table with reusable measures. In this step, AWS Glue turns delivered Silver orders into a Gold layer that you can query with Amazon Athena.

In this step, get ready to:
  • Add a Gold output path to the existing AWS Glue job.
  • Build restaurant metrics from delivered Silver orders with PySpark.
  • Catalog the Gold dataset and prove its business metrics with SQL.
Add the Gold output and aggregation

The Gold dataset groups clean Silver rows by restaurant plus campus zone. Each output row contains an order count, revenue total, and average delivery time.

  • Switch back to the existing campus-eats-medallion job in AWS Glue.
  • Go to the configuration area containing the existing job parameters.
  • Add a job parameter with the key --GOLD_PATH.
  • Enter s3://campus-eats-gold-<unique-suffix>/restaurant_metrics/ as the parameter value.
  • Choose Save.

Good. The job now has a dedicated destination for its business-ready metrics.

  • Return to the Spark script editor for campus-eats-medallion.
  • Find the args = getResolvedOptions( block near the top of medallion_pipeline.py.
  • Replace that block with this updated version:
args = getResolvedOptions(
    sys.argv,
    ["JOB_NAME", "BRONZE_PATH", "SILVER_PATH", "GOLD_PATH"],
)

What does this change do?

  • The argument list tells AWS Glue which configured values the script requires.
  • The script reads GOLD_PATH without hyphens after the job configuration supplies --GOLD_PATH.
  • Choose Save in the script editor.

The job configuration and script now use the same Gold path argument.

Gold path missing from the script?

Check that the job parameter starts with --GOLD_PATH. Check that the Python argument list contains GOLD_PATH without the hyphens.

Confirm that the Gold path ends with /restaurant_metrics/.

Help me check the Gold path configuration

  • Find the line silver_df.write.parquet(args["SILVER_PATH"], mode="overwrite").
  • Select everything from that line through job.commit().
  • Replace the selected section with this completed pipeline ending:
silver_df.write.parquet(args["SILVER_PATH"], mode="overwrite")
silver_df.createOrReplaceTempView("silver_orders")
gold_df = spark.sql("""
    SELECT restaurant, campus_zone, COUNT(*) AS order_count,
           ROUND(SUM(order_total), 2) AS revenue,
           ROUND(AVG(delivery_minutes), 1) AS avg_delivery_minutes
    FROM silver_orders
    WHERE status = 'delivered'
    GROUP BY restaurant, campus_zone
""")
gold_df.write.parquet(args["GOLD_PATH"], mode="overwrite")

print(f"Raw rows: {raw_df.count()}")
print(f"Silver rows: {silver_df.count()}")
print(f"Gold rows: {gold_df.count()}")

job.commit()

How does the Gold aggregation work?

  • The silver_orders temporary view makes the clean DataFrame available to Spark SQL.
  • The WHERE status = 'delivered' condition excludes cancelled orders from business metrics.
  • The grouping creates one metric row for each restaurant and campus-zone combination.
  • The Gold write publishes the four aggregated rows as Parquet.
  • The final print statement exposes the Gold row count in the job logs.
  • Choose Save in the script editor.

✔️ Awesome, I've got everything!

Your script is ready for its Gold run.

ⓧ I'd like to double check the full code

  • Compare your saved medallion_pipeline.py with this complete file:
import sys

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

args = getResolvedOptions(
    sys.argv,
    ["JOB_NAME", "BRONZE_PATH", "SILVER_PATH", "GOLD_PATH"],
)

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

raw_df = spark.read.csv(
    args["BRONZE_PATH"],
    header=True,
    inferSchema=False,
)

normalized_df = raw_df.select(
    F.trim(F.col("order_id")).alias("order_id"),
    F.try_to_timestamp(F.col("ordered_at"), F.lit("yyyy-MM-dd HH:mm:ss")).alias("ordered_at"),
    F.trim(F.col("restaurant")).alias("restaurant"),
    F.trim(F.col("campus_zone")).alias("campus_zone"),
    F.regexp_replace(F.col("order_total"), r"[$,]", "").try_cast("double").alias("order_total"),
    F.col("delivery_minutes").try_cast("int").alias("delivery_minutes"),
    F.lower(F.trim(F.col("status"))).alias("status"),
)

valid_condition = (
    F.col("order_id").isNotNull() & (F.col("order_id") != "") & F.col("ordered_at").isNotNull()
    & F.col("restaurant").isNotNull() & (F.col("restaurant") != "")
    & F.col("campus_zone").isNotNull() & (F.col("campus_zone") != "")
    & F.col("order_total").isNotNull() & (F.col("order_total") > 0)
    & F.col("delivery_minutes").isNotNull() & (F.col("delivery_minutes") > 0)
    & F.col("status").isin("delivered", "cancelled")
)

silver_df = normalized_df.filter(valid_condition).dropDuplicates(["order_id"])
silver_df.write.parquet(args["SILVER_PATH"], mode="overwrite")
silver_df.createOrReplaceTempView("silver_orders")
gold_df = spark.sql("""
    SELECT restaurant, campus_zone, COUNT(*) AS order_count,
           ROUND(SUM(order_total), 2) AS revenue,
           ROUND(AVG(delivery_minutes), 1) AS avg_delivery_minutes
    FROM silver_orders
    WHERE status = 'delivered'
    GROUP BY restaurant, campus_zone
""")
gold_df.write.parquet(args["GOLD_PATH"], mode="overwrite")

print(f"Raw rows: {raw_df.count()}")
print(f"Silver rows: {silver_df.count()}")
print(f"Gold rows: {gold_df.count()}")

job.commit()

What should the complete file contain?

The file preserves the existing Bronze read plus Silver validation. Its new ending creates the Gold aggregation and writes it to the configured Gold path.

Run the completed Glue job

This run uses billable AWS Glue compute. You only need one successful run to publish the Gold dataset.

Allow a few minutes for the Spark workers to start. The wait is expected while AWS Glue prepares the job.

Before you run the job, do you expect Gold to contain the same number of rows as Silver?

  • Choose Run in the AWS Glue script editor.
  • Select the Runs tab.
  • Wait for the latest run to report SUCCEEDED.
  • Open the logs for the successful run.

The logs show Raw rows: 12. They also show Silver rows: 8.

Finally, you see Gold rows: 4.

That is the aggregation working. Your eight clean orders now produce four reusable restaurant-and-zone metric rows.

Job did not reach Gold rows?

Confirm that --GOLD_PATH points to your existing Gold bucket. Check that the path ends with /restaurant_metrics/.

If the run stops before the Gold count, compare the temporary view plus SQL aggregation with the completed file above.

Help me troubleshoot the Gold job run

Catalog and query the Gold metrics

The files in Apache Parquet need a table definition before Athena can query them. The AWS Glue Data Catalog stores that schema plus its Gold location.

  • Return to 02_curated_tables.sql in the Athena query editor from earlier.
  • Append the Gold table definition plus verification query below the Silver query by pasting this code:
CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.gold_restaurant_metrics (
    restaurant string,
    campus_zone string,
    order_count bigint,
    revenue double,
    avg_delivery_minutes double
)
STORED AS PARQUET
LOCATION 's3://campus-eats-gold-<unique-suffix>/restaurant_metrics/';

SELECT *
FROM campus_eats.gold_restaurant_metrics
ORDER BY revenue DESC;

What does this SQL publish?

  • The external table maps five typed columns to the Gold Parquet output.
  • The LOCATION points Athena at the restaurant metrics prefix.
  • The final query sorts the business metrics from highest revenue to lowest revenue.
  • Replace <unique-suffix> with the suffix already used by your Gold bucket.

✔️ Awesome, I've got everything!

Your curated tables file now contains both the Silver and Gold definitions.

ⓧ I'd like to double check the full code

  • Compare your updated 02_curated_tables.sql with this complete file:
CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.silver_orders (
    order_id string,
    ordered_at timestamp,
    restaurant string,
    campus_zone string,
    order_total double,
    delivery_minutes int,
    status string
)
STORED AS PARQUET
LOCATION 's3://campus-eats-silver-<unique-suffix>/orders/';

SELECT
    COUNT(*) AS silver_rows,
    COUNT(DISTINCT order_id) AS unique_orders
FROM campus_eats.silver_orders;

CREATE EXTERNAL TABLE IF NOT EXISTS campus_eats.gold_restaurant_metrics (
    restaurant string,
    campus_zone string,
    order_count bigint,
    revenue double,
    avg_delivery_minutes double
)
STORED AS PARQUET
LOCATION 's3://campus-eats-gold-<unique-suffix>/restaurant_metrics/';

SELECT *
FROM campus_eats.gold_restaurant_metrics
ORDER BY revenue DESC;

How is the file organized?

The first half preserves the Silver table plus its row-count query. The second half registers the Gold table plus its revenue-ranked query.

  • Select only the Gold table creation statement.
  • Choose Run.

Athena completes the statement and registers campus_eats.gold_restaurant_metrics in the Data Catalog.

Before you run the final query, which restaurant do you expect to lead the revenue results?

  • Select the final SELECT query.
  • Choose Run.

You'll see four restaurant-and-zone rows ordered by revenue. Pizza Lab appears first with revenue 39.5.

Pizza Lab's total comes from delivered orders O1002 plus O1005. Cancelled order O1010 contributes nothing to delivered-order revenue.

Gold table empty or missing?

Confirm that the table LOCATION matches the Gold path configured on the successful Glue job.

Check that you ran the table creation statement before the final query.

Help me troubleshoot the Gold Athena results

  • Save 02_curated_tables.sql.

You now have a complete Bronze-Silver-Gold pipeline. Its final table turns validated orders into metrics that business consumers can query directly.

Secret mission

Quarantine Rejected Records

Three malformed orders currently disappear when the pipeline builds Silver. Extend the job with a quarantine path that preserves each rejected row with a reason, then use Athena to report the data-quality problems.

Clean Up Your Resources

Clean Up Your Resources

Your Campus Eats resources can still incur charges through Amazon S3 storage plus requests. Decide whether to keep them available, pause further activity, or delete them entirely.

Cost warning

New AWS Glue job runs can incur compute charges. New Amazon Athena queries can incur scan charges.

The Glue job is unscheduled, so it stops consuming compute after each run finishes. Choose Pause to avoid new activity or Delete to remove ongoing project storage.

Resources you used:

  • A private Bronze layer bucket containing orders.csv.
  • A private Silver layer bucket containing cleaned Apache Parquet data plus rejected records.
  • A private Gold layer bucket containing restaurant metrics.
  • A private assets bucket containing the Glue script, temporary files, and Athena query results.
  • An AWS Glue Spark job named campus-eats-medallion.
  • An AWS Identity and Access Management (IAM) role named AWSGlueServiceRole-CampusEats. It has AWSGlueServiceRole plus AmazonS3FullAccess attached.
  • An AWS Glue Data Catalog database named campus_eats. It contains the Bronze, Silver, Gold, and rejected table definitions.

Keep everything running

No cleanup action is needed. Choose this if you plan to rerun the pipeline or revisit its Athena results.

  • Keep all four Campus Eats buckets in the selected AWS Region.
  • Keep campus-eats-medallion available for future runs.
  • Keep the campus_eats database available for future queries.
  • Keep AWSGlueServiceRole-CampusEats only while the project still needs its broad S3 access.
  • Expect Amazon S3 storage plus requests to remain billable.

Pause - I'll come back to this later

Pause means leaving durable resources in place while avoiding new compute or query activity. The job has no schedule, so no Glue compute continues after a run finishes.

  • Leave campus-eats-medallion idle.
  • Avoid running further Athena queries.
  • Retain the four Campus Eats buckets for your next session.
  • Retain the campus_eats catalog definitions for later.

Amazon S3 storage remains billable. Requests can also incur charges.

Delete - I don't want to use this again

Deletion permanently removes the data lake plus its catalog definitions. Use this option only when you have finished with the project.

Remove the processing job first so the deleted storage cannot receive another project run.

  • Return to the AWS Glue Jobs page from earlier.
  • Select campus-eats-medallion.
  • Delete the selected job with the page's delete action.
  • Confirm that campus-eats-medallion is absent from the job list.

With compute removed, clear the catalog definitions that point to the project data.

  • Return to the database list in AWS Glue Data Catalog.
  • Delete the campus_eats database.
  • Confirm that campus_eats is absent from the database list.

Deleting the database removes its Bronze, Silver, Gold, and rejected external table definitions.

The remaining objects live across the four project buckets. Emptying them first allows each bucket to be deleted.

  • Return to Amazon S3 in the selected AWS Region.
  • Choose General purpose buckets.
  • Use the bucket page's Empty action for each of the four Campus Eats buckets.
  • Use the bucket page's Delete action for each empty Campus Eats bucket.
  • Confirm that none of the four project buckets remains under General purpose buckets.

Finish by removing the service role that carries the project's broad S3 permission.

  • Return to the IAM role list from earlier.
  • Select AWSGlueServiceRole-CampusEats.
  • Delete the selected role with the page's delete action.
  • Confirm that AWSGlueServiceRole-CampusEats is absent from the role list.

That's the cleanup done. The project's reusable AWS resources plus billable storage are removed.

Nice Work!

Nice Work!

You did it! Your Campus Eats pipeline now turns a messy CSV in Amazon S3 into trustworthy analytics through AWS Glue processing plus Amazon Athena queries.

You've learned how to:

  • Build a Bronze-Silver-Gold architecture on Amazon S3 for progressively more trustworthy order data.
  • Process the 12-row batch in AWS Glue 6.0 with PySpark. Safe type conversion handles malformed values. Validation plus deduplication produces eight clean Silver rows.
  • Publish four Gold restaurant metrics as Apache Parquet. Register the curated outputs in AWS Glue Data Catalog for SQL analysis in Amazon Athena.
  • Secret Mission: Preserve three invalid records in a rejected-record quarantine. Give every rejected row a human-readable reason for investigation.

Ready to quiz yourself?