Build a Serverless Data Lake on AWS

Build an S3 + Glue + Athena pipeline to transform CSV data into Parquet.

Introduction

30 Second Summary

Every online store generates thousands of transactions daily. Without a system to organize, transform, and analyze that data, valuable insights stay buried in messy CSV files.

In this project, you will build a serverless data lake pipeline on AWS that takes raw e-commerce sales data, transforms it into an optimized format, and makes it queryable with SQL.

What You'll Build

A complete serverless data lake using Amazon S3 for storage, AWS Glue for ETL and schema discovery, and Amazon Athena for SQL analytics.

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

  • An S3 data lake with separate raw and curated zones that organize your data by processing stage.
  • An AWS Glue ETL job that automatically transforms CSV files into optimized Parquet format.
  • SQL analytics queries running in Athena directly against your data lake, analyzing revenue, top products, and customer trends.
  • Secret Mission: Add date-based partitioning to your curated layer and compare query performance between partitioned and non-partitioned data.

Are there any prerequisites?

You need an AWS account, Python installed locally, and basic familiarity with SQL. You should also be comfortable navigating the S3 console.

Before We Start

Before We Start...

Set Up Your Environment

Before you can build a data lake, you need the underlying infrastructure in place. Your pipeline will read and write data in Amazon S3, and AWS Glue will handle the ETL processing.

In this step, you will create the S3 bucket that holds your raw and curated data zones, then set up the IAM role that gives Glue permission to access those zones.

In this step, get ready to:
  • Confirm your AWS CLI connection is working.
  • Create an S3 bucket with raw/ and curated/ folder prefixes.
  • Create an IAM role for AWS Glue with the required permissions.
Confirm your AWS CLI connection

Your AWS CLI is already installed and configured from a prior setup. Let's quickly confirm it can reach your AWS account.

  • Verify the CLI is reachable by running this command:
aws sts get-caller-identity

What should I see?

You should see a JSON response with your Account, UserId, and Arn fields. This confirms the CLI can authenticate with your AWS account.

  • Sign into the AWS Management Console at https://console.aws.amazon.com.
  • Keep this browser tab open. You will use the console for Glue and Athena in later steps.

Getting an access denied error?

Make sure your CLI credentials have not expired. If you are using temporary session credentials, regenerate them and reconfigure with aws configure.

help me fix my AWS CLI authentication issue

Create an S3 bucket with data lake folder structure

A data lake separates storage into zones. The raw zone holds unprocessed source data exactly as it arrives. The curated zone holds cleaned, transformed data optimized for analytics.

  • Create your S3 bucket by running this command:
aws s3 mb s3://[[BUCKET_NAME="your-unique-datalake-bucket-name"]]

What does this command do?

The aws s3 mb command creates ("makes") a new S3 bucket. Bucket names must be globally unique across all of AWS, between 3 and 63 characters, and contain only lowercase letters, numbers, and hyphens.

A good pattern is yourname-datalake-2024 to keep it unique and descriptive.

Why use the CLI for S3 operations?

The CLI approach demonstrates automation skills that employers expect from data engineers. In a production environment, you would script bucket creation rather than clicking through the console.

S3 uses a flat structure, but you can simulate folders by creating zero-byte objects with keys ending in /. These appear as folders in the S3 console.

  • Create the raw/ and curated/ zone prefixes by running these commands:
aws s3api put-object --bucket [[BUCKET_NAME="your-unique-datalake-bucket-name"]] --key raw/
aws s3api put-object --bucket [[BUCKET_NAME="your-unique-datalake-bucket-name"]] --key curated/

What do these commands do?

Each aws s3api put-object call creates a zero-byte object whose key ends with /. The S3 console displays these as folders, giving you a clear two-zone data lake structure.

  • Verify your folder structure by running:
aws s3 ls s3://[[BUCKET_NAME="your-unique-datalake-bucket-name"]]/

You should see PRE raw/ and PRE curated/ in the output.

Bucket name already exists?

S3 bucket names are globally unique. If you see a "BucketAlreadyExists" error, choose a different name by adding your initials or a random number.

help me pick a unique S3 bucket name

Create an IAM role for AWS Glue

AWS Glue needs permission to read your S3 data and write results back. You grant these permissions by creating an IAM role that Glue can assume.

  • Create the IAM role with Glue as the trusted service by running this command:
aws iam create-role --role-name GlueDataLakeRole --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"glue.amazonaws.com"},"Action":"sts:AssumeRole"}]}'

What does this command do?

This creates a role named GlueDataLakeRole with a trust policy that allows the glue.amazonaws.com service to assume it. Without this trust relationship, Glue cannot use the role to access your resources.

Now attach two managed policies that give the role the permissions it needs.

  • Attach the S3 full access policy by running:
aws iam attach-role-policy --role-name GlueDataLakeRole --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess
  • Attach the Glue service role policy by running:
aws iam attach-role-policy --role-name GlueDataLakeRole --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole

What do these policies provide?

AmazonS3FullAccess lets the role read and write objects in any S3 bucket in your account. This is necessary because Glue reads raw data from S3 and writes transformed output back to S3.

AWSGlueServiceRole grants the baseline permissions Glue needs to operate. This includes access to CloudWatch Logs for monitoring and EC2 networking for VPC connections.

  • Verify the role was created and the policies are attached by running:
aws iam list-attached-role-policies --role-name GlueDataLakeRole

You should see both AmazonS3FullAccess and AWSGlueServiceRole listed in the output.

Seeing an access denied error?

Your IAM user needs the iam:CreateRole and iam:AttachRolePolicy permissions to run these commands. If you are using a restricted IAM user, ask your account admin to grant these permissions or use an admin-level user for this project.

help me fix IAM permission errors when creating a role

Your environment is ready. Next up, you will generate realistic e-commerce data and upload it to your raw zone for processing.

Generate and Upload Sample Data

Your environment is set up with an S3 bucket and a Glue IAM role ready to go. Now you need actual data for your pipeline to process.

In this step, you'll write a Python script that generates realistic e-commerce sales data, then upload it to your S3 raw zone. This gives your data lake its first dataset to crawl, transform, and query in later steps.

In this step, get ready to:
  • Write a Python script that generates 2000 rows of e-commerce order data.
  • Upload the resulting CSV file to your S3 raw zone.
  • Verify the upload in the S3 console.
Write the data generator script

Your data lake needs a realistic dataset to work with. You'll create a Python script that generates 2000 e-commerce order records with fields like product name, category, price, and region.

  • In VS Code, create a new file by pressing Cmd+N (macOS) or Ctrl+N (Windows).
  • Save the file immediately with Cmd+S (macOS) or Ctrl+S (Windows).
  • Name it generate_data.py and save it to your Desktop.
  • Add the imports and product configuration by pasting this code into generate_data.py:
import csv
import random
from datetime import datetime, timedelta

# Set seed for reproducible data
random.seed(42)

# Product catalog: (name, category, unit_price)
products = [
    ("Wireless Mouse", "Electronics", 29.99),
    ("USB-C Hub", "Electronics", 49.99),
    ("Mechanical Keyboard", "Electronics", 89.99),
    ("Laptop Stand", "Accessories", 39.99),
    ("Webcam HD", "Electronics", 59.99),
    ("Desk Lamp", "Home Office", 34.99),
    ("Monitor Arm", "Accessories", 44.99),
    ("Noise Cancelling Headphones", "Electronics", 149.99),
    ("Ergonomic Chair Cushion", "Home Office", 54.99),
    ("Cable Management Kit", "Accessories", 19.99),
]

# Regions to simulate multi-region orders
regions = ["us-east-1", "us-west-2", "eu-west-1", "ap-southeast-1"]

# Date range for order dates (full year 2024)
start_date = datetime(2024, 1, 1)
end_date = datetime(2024, 12, 31)
date_range = (end_date - start_date).days

What does this code do?

random.seed(42) makes the random data reproducible. Every time you run the script, you get the same 2000 rows. This is useful for testing your pipeline consistently.

The products list defines 10 items across three categories (Electronics, Accessories, Home Office). Each tuple stores the product name, category, and base price.

  • Add the data generation loop below the configuration code:
# Generate 2000 order rows
rows = []
for i in range(1, 2001):
    product_name, category, base_price = random.choice(products)
    quantity = random.randint(1, 5)
    order_date = start_date + timedelta(days=random.randint(0, date_range))
    customer_id = f"CUST-{random.randint(1000, 9999)}"
    region = random.choice(regions)
    amount = round(base_price * quantity, 2)

    rows.append({
        "order_id": f"ORD-{i:05d}",
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": customer_id,
        "product_name": product_name,
        "category": category,
        "quantity": quantity,
        "unit_price": base_price,
        "amount": amount,
        "region": region,
    })

What does this loop produce?

Each iteration creates one order record. The loop runs 2000 times, generating a full year of simulated orders with random products, quantities, customers, and regions.

The amount field multiplies base_price * quantity to give a total order value. This is the kind of derived column your Glue ETL job will validate later.

  • Add the CSV file writing code at the bottom of the script:
# Write all rows to a CSV file
with open("ecommerce_sales.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)

print(f"Generated {len(rows)} rows in ecommerce_sales.csv")

What does this code do?

csv.DictWriter writes each dictionary in rows as a CSV row, using the dictionary keys as column headers. The newline="" argument prevents extra blank lines on Windows.

  • Save generate_data.py with Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Make sure you've saved your file before running it in the next step.

ⓧ I'd like to double check the full code

import csv
import random
from datetime import datetime, timedelta

random.seed(42)

products = [
    ("Wireless Mouse", "Electronics", 29.99),
    ("USB-C Hub", "Electronics", 49.99),
    ("Mechanical Keyboard", "Electronics", 89.99),
    ("Laptop Stand", "Accessories", 39.99),
    ("Webcam HD", "Electronics", 59.99),
    ("Desk Lamp", "Home Office", 34.99),
    ("Monitor Arm", "Accessories", 44.99),
    ("Noise Cancelling Headphones", "Electronics", 149.99),
    ("Ergonomic Chair Cushion", "Home Office", 54.99),
    ("Cable Management Kit", "Accessories", 19.99),
]

regions = ["us-east-1", "us-west-2", "eu-west-1", "ap-southeast-1"]

start_date = datetime(2024, 1, 1)
end_date = datetime(2024, 12, 31)
date_range = (end_date - start_date).days

rows = []
for i in range(1, 2001):
    product_name, category, base_price = random.choice(products)
    quantity = random.randint(1, 5)
    order_date = start_date + timedelta(days=random.randint(0, date_range))
    customer_id = f"CUST-{random.randint(1000, 9999)}"
    region = random.choice(regions)
    amount = round(base_price * quantity, 2)

    rows.append({
        "order_id": f"ORD-{i:05d}",
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": customer_id,
        "product_name": product_name,
        "category": category,
        "quantity": quantity,
        "unit_price": base_price,
        "amount": amount,
        "region": region,
    })

with open("ecommerce_sales.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)

print(f"Generated {len(rows)} rows in ecommerce_sales.csv")
  • Open a terminal in VS Code by pressing Ctrl+` (backtick key, same shortcut on macOS and Windows).
  • Navigate to your Desktop by running this command:
cd ~/Desktop
  • Run the script to generate your dataset:
python3 generate_data.py
  • You should see this output in your terminal:
Generated 2000 rows in ecommerce_sales.csv
  • Confirm the file was created by running:
ls ecommerce_sales.csv

You should see ecommerce_sales.csv listed in the output.

Seeing 'command not found' or 'No such file'?

If python3 is not found, try python instead. Some systems use python as the default command.

If you see 'No such file or directory', make sure your terminal is in the same folder where you saved generate_data.py. Run ls to check what files are in your current directory.

Help me debug why my Python script isn't running

Upload the CSV to your S3 raw zone

Your raw zone is the landing area for unprocessed data. Everything that enters your data lake starts here before any transformation happens.

  • Upload ecommerce_sales.csv to your S3 bucket's raw prefix by running this command:
aws s3 cp ecommerce_sales.csv s3://[[BUCKET_NAME="your-datalake-bucket-name"]]/raw/ecommerce_sales.csv

What does this command do?

aws s3 cp copies a file from your local machine to S3 (or vice versa). The destination path places the file inside the raw/ prefix you created in Step 1, keeping your data lake structure organized.

  • You should see output confirming the upload:
upload: ./ecommerce_sales.csv to s3://your-bucket/raw/ecommerce_sales.csv

Upload failed or access denied?

Double-check your bucket name in the command. It must match the exact name you created in Step 1.

If you see 'Access Denied', verify your AWS CLI is configured with credentials that have S3 write access. Run aws sts get-caller-identity to confirm you're authenticated.

Help me fix my S3 upload error

Verify the upload in the S3 console

A quick check in the AWS console confirms your file landed in the right place. This is the data your Glue Crawler will discover in the next step.

  • Switch back to the AWS Management Console in your browser.
  • In the search bar at the top, type S3 and select S3 from the results.
  • Click on your bucket name (my-datalake-bucket).
  • Click the raw/ folder.
  • You should see ecommerce_sales.csv listed with a file size of approximately 130-140 KB.

Why check the file size?

A file size of ~130 KB for 2000 rows of CSV data confirms the upload completed fully. If the file shows 0 bytes, the upload was interrupted.

Don't see the file in the raw/ folder?

Make sure you're looking inside the raw/ folder, not at the bucket root. Click into raw/ to see its contents.

If the raw/ folder is empty, re-run the upload command and check that your bucket name is correct.

Help me find my uploaded file in S3

Your raw zone now has real data waiting to be processed. Next up, you'll point a Glue Crawler at this CSV file and watch it automatically discover the schema for you.

Discover Your Data with a Glue Crawler

Your raw CSV data is sitting in S3, but no service knows what columns it has or what data types they hold. Before you can run queries or transform data, something needs to read your file and record its structure.

This is what an AWS Glue Crawler does. It scans your S3 data, infers the schema automatically, and registers a table definition in the Glue Data Catalog. The Data Catalog then becomes the single source of truth that other services like Athena and Glue ETL jobs reference when they need to understand your data.

In this step, get ready to:
  • Create a Glue database in the Data Catalog.
  • Create and configure a Glue Crawler to scan your S3 raw zone.
  • Run the crawler and inspect the auto-discovered schema.
Create a Glue database in the Data Catalog

A database in the Glue Data Catalog is a container that organizes table definitions. You need one before the crawler can write its discovered schema anywhere.

  • Return to the AWS Management Console in your browser.
  • In the search bar at the top, type Glue and select AWS Glue from the results.
  • In the left-hand menu under Data catalog, click Databases.
  • Click Add database.
  • In the Name field, enter ecommerce_datalake.
  • Leave the Location field empty.
  • Click Create database.

What is the Glue Data Catalog?

The Glue Data Catalog is a centralized metadata store that holds table definitions, column names, data types, and storage locations. It is Hive Metastore-compatible, which means services like Athena, Redshift Spectrum, and EMR can all read from the same catalog without duplicating metadata.

You should see ecommerce_datalake appear in your list of databases.

Don't see your new database?

Make sure you used only alphanumeric characters and underscores in the name. Glue database names are folded to lowercase, and Athena requires alphanumeric and underscore characters only.

help me troubleshoot why my Glue database was not created

Create and configure a Glue Crawler

Now you will create a crawler that points at your S3 raw zone. The crawler will read the CSV file, figure out the column names and data types, and register a table in the database you just created.

  • In the left-hand menu of the AWS Glue console, click Crawlers.
  • Click Create crawler.
  • In the Name field, enter raw-zone-crawler.
  • Click Next.

You are now on the data sources page.

  • For Is your data already mapped to AWS Glue tables?, leave Not yet selected.
  • Click Add a data source.
  • For Data source, choose S3.
  • For S3 path, click Browse S3 and navigate to your bucket. Select the raw/ folder inside my-datalake-bucket.
  • The S3 path should read s3://my-datalake-bucket/raw/.
  • Click Add an S3 data source.
  • Click Next.

You are now on the security settings page.

  • For Existing IAM role, select GlueDataLakeRole from the dropdown.
  • Click Next.

You are now on the output and scheduling page.

  • For Target database, select ecommerce_datalake from the dropdown.
  • Leave the Table name prefix field empty.
  • For the schedule, leave it set to On demand.
  • Click Next.
  • Review your crawler settings on the summary page.
  • Click Create crawler.

Why does the table get named after the S3 folder?

When a Glue Crawler is pointed at a path like s3://my-datalake-bucket/raw/, it creates a table named after the last folder in the path. In this case, the table will be named raw. This is standard crawler behavior.

You should see a success banner confirming your crawler was created.

Crawler creation failed?

Double-check that your IAM role GlueDataLakeRole has both the AmazonS3FullAccess and AWSGlueServiceRole policies attached. Without these, the crawler cannot read S3 or write to the Data Catalog.

help me fix my Glue Crawler creation error

Run the crawler and inspect the schema

With the crawler created, it is time to run it. The crawler will scan your CSV file, detect the column headers, infer data types, and register a table in the ecommerce_datalake database.

  • On the crawler's detail page, click Run crawler.
  • Wait for the crawler status to change from Running to Ready. This typically takes 1-2 minutes.

What is happening while the crawler runs?

The crawler connects to S3, reads your CSV file, and uses a built-in CSV classifier to detect the schema. It identifies 9 columns from your header row and infers a data type for each one. Then it writes this metadata as a table definition in the Data Catalog.

Crawlers are billed at $0.44 per DPU-hour with a 10-minute minimum. For this small dataset, the cost is negligible.

  • Once the status shows Ready, confirm the Tables added column shows 1.
  • In the left-hand menu under Data catalog, click Tables.
  • Click the table named raw to open its details.
  • Scroll down to the Schema section.

You should see 9 columns listed: order_id, order_date, customer_id, product_name, category, quantity, unit_price, amount, and region. The crawler has automatically assigned data types like string, long, and double to each column.

Why are some columns typed as string?

The crawler infers types based on the data it samples. Text-like columns (order_id, customer_id, product_name) are classified as string. Numeric columns like quantity become long (integer) and decimal columns like unit_price become double. In the next step, the ETL job will cast types more precisely during transformation.

Crawler ran but no table appeared?

Confirm the crawler's target database is set to ecommerce_datalake. If it was left on the default database, check there instead.

Also verify that your S3 path includes the trailing slash: s3://my-datalake-bucket/raw/. Without it, the crawler may not find the data.

help me figure out why my Glue Crawler didn't create a table

Your data lake now has a registered schema. Next up, you will write a Glue ETL job that reads this table, transforms the data, and writes optimized Parquet files to the curated zone.

Transform Data with a Glue ETL Job

Your Glue Crawler just discovered the schema of your raw CSV data and registered it in the Data Catalog. The next step in any data lake pipeline is transforming that raw data into an optimized format.

In this step, you will write a PySpark ETL script and run it as an AWS Glue job. The script reads your CSV data from the Data Catalog, casts data types, adds derived columns, filters invalid rows, and writes the result as Parquet to your curated zone.

In this step, get ready to:
  • Create a new Glue ETL job using the Spark script editor.
  • Write a PySpark script that transforms CSV data into Parquet.
  • Run the job and verify Parquet output in your curated zone.
Create a new Glue ETL job in Glue Studio

AWS Glue Studio provides a script editor where you can write PySpark code and run it on a fully managed Spark cluster. You will create a new Spark job and paste your transformation script into it.

  • Return to the AWS Glue console in your browser.
  • In the left navigation pane, click Jobs.
  • In the Create job section, select Spark script editor.
  • Under Options, choose Create a new script with boilerplate code.
  • Click Create.

The script editor opens with some boilerplate Python code. You will replace this entirely with your own script in the next substep.

Why the Spark script editor?

Glue Studio offers a visual drag-and-drop editor and a script editor. The script editor gives you full control over PySpark transformations and teaches transferable Spark skills that employers look for in data engineering roles.

Write the PySpark ETL script

Your ETL script will read from the Data Catalog table, cast columns to proper types, add year and month columns, filter out invalid rows, and write optimized Parquet files to your curated zone.

  • Switch back to VS Code.
  • Create a new file called glue_etl_job.py in the same folder as generate_data.py.
  • Add the imports and job 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 pyspark.sql.functions import col, year, month, when

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

What does this code do?

This boilerplate initializes the Glue job environment. Every Glue PySpark script follows this pattern.

  • `getResolvedOptions` reads job parameters passed from the Glue console (the job name and your custom `output_path`).
  • `GlueContext` wraps the Spark session with Glue-specific methods like reading from the Data Catalog.
  • `job.init()` starts tracking the job lifecycle for features like bookmarks and metrics.
  • Below the initialization code, add the section that reads data from your Data Catalog:
dynamic_frame = glueContext.create_dynamic_frame.from_catalog(
    database="ecommerce_datalake",
    table_name="raw",
)

df = dynamic_frame.toDF()

What does this code do?

`create_dynamic_frame.from_catalog` reads data using the schema that your crawler discovered. It references the ecommerce_datalake database and the raw table you saw in the Data Catalog.

`toDF()` converts the Glue DynamicFrame into a standard Spark DataFrame so you can use familiar PySpark transformations.

  • Next, add the transformation logic that casts types, adds derived columns, and filters invalid data:
df_transformed = df \
    .withColumn("quantity", col("quantity").cast("integer")) \
    .withColumn("unit_price", col("unit_price").cast("double")) \
    .withColumn("amount", col("amount").cast("double")) \
    .withColumn("order_year", year(col("order_date"))) \
    .withColumn("order_month", month(col("order_date"))) \
    .filter(col("amount").isNotNull() & (col("amount") > 0))

What does this code do?

  • `cast("integer")` and `cast("double")` enforce correct numeric types. The crawler may infer them as strings or longs depending on the data.
  • `year(col("order_date"))` and `month(col("order_date"))` extract year and month as new columns. These are useful for partitioning and analytics later.
  • `filter(col("amount").isNotNull() & (col("amount") > 0))` removes any rows with null or zero-value amounts.
  • Finally, add the code that writes the output as Parquet and commits the job:
df_transformed.write \
    .mode("overwrite") \
    .parquet(args["output_path"])

job.commit()

What does this code do?

`df_transformed.write.parquet(args["output_path"])` writes the cleaned DataFrame as Parquet files to the S3 path passed as a job parameter. Parquet is a columnar format that compresses well and lets Athena read only the columns it needs.

`job.commit()` signals to Glue that the job completed successfully. This finalizes bookmarks and metrics.

  • Save glue_etl_job.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before moving on.

ⓧ I'd like to double check the full code

Here is the complete glue_etl_job.py file:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import col, year, month, when

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

dynamic_frame = glueContext.create_dynamic_frame.from_catalog(
    database="ecommerce_datalake",
    table_name="raw",
)

df = dynamic_frame.toDF()

df_transformed = df \
    .withColumn("quantity", col("quantity").cast("integer")) \
    .withColumn("unit_price", col("unit_price").cast("double")) \
    .withColumn("amount", col("amount").cast("double")) \
    .withColumn("order_year", year(col("order_date"))) \
    .withColumn("order_month", month(col("order_date"))) \
    .filter(col("amount").isNotNull() & (col("amount") > 0))

df_transformed.write \
    .mode("overwrite") \
    .parquet(args["output_path"])

job.commit()

Now you need to paste this script into the Glue Studio editor you created earlier.

  • Select all the code in glue_etl_job.py and copy it.
  • Switch back to the Glue Studio script editor in your browser.
  • Select all the existing boilerplate code in the Script tab and delete it.
  • Paste your full glue_etl_job.py code into the editor.
Run the job and verify Parquet output in S3

Before you can run the job, you need to configure its name, IAM role, Glue version, and the output path parameter your script reads.

  • Click Untitled job on the title bar at the top of the page.
  • Replace it with ecommerce-etl-job.
  • Click the Job details tab.
  • In the IAM Role dropdown, select GlueDataLakeRole.
  • Set Glue version to Glue 3.0.
  • Set Requested number of workers to 2 (the minimum).
  • Scroll down and expand Advanced properties.
  • Scroll to the Job parameters section.
  • Click Add new parameter.
  • In the Key field, enter:
--output_path
  • In the Value field, enter:
s3://[[DATALAKE_BUCKET="your-datalake-bucket-name"]]/curated/
  • Click Save in the top right corner.
  • Click Run.

How long does the job take?

The job typically takes 2-5 minutes to complete. Most of that time is spent provisioning the Spark cluster. The actual data transformation for 2000 rows is almost instant.

Glue bills at $0.44 per DPU-hour with a 1-minute minimum. With 2 workers running for a few minutes, this job costs a few cents.

  • Click the Runs tab to monitor progress.
  • Wait until the Run status shows Succeeded.

Job failed or stuck in Running?

  • Check that the IAM role GlueDataLakeRole has both AmazonS3FullAccess and AWSGlueServiceRole policies attached.
  • Verify the --output_path value ends with a trailing slash (/curated/).
  • Click the error link in the Runs tab to view CloudWatch logs for the specific error message.

Still stuck? Help me debug my Glue ETL job failure.

Now verify the Parquet files appeared in your curated zone.

  • Open the S3 console and navigate to your bucket.
  • Click into the curated/ folder.
  • You should see one or more .parquet files listed.

Why Parquet instead of CSV?

Parquet stores data in columns rather than rows. When Athena queries only specific columns (like category and amount), it skips all other columns entirely.

Athena charges $5 per TB of data scanned. Columnar Parquet with compression can reduce scanned data by 10x or more compared to raw CSV. Less data scanned means cheaper and faster queries.

Your raw CSV data has been transformed into optimized Parquet in your curated zone. Next up, you will query this data with Athena to run SQL analytics directly on S3.

Query Your Data Lake with Athena

Your data lake now has a raw zone with CSV and a curated zone with optimized Parquet. The missing piece is a way to actually ask questions of this data.

In this step, you will use Amazon Athena to run SQL queries directly against your S3 data. Athena is serverless, so there is no infrastructure to manage. You pay only for the data each query scans.

In this step, get ready to:
  • Configure Athena's query results location and catalog the curated zone.
  • Run analytics queries for revenue, top products, and monthly trends.
  • Compare query performance between raw CSV and curated Parquet tables.
Set up Athena and catalog the curated zone

Before Athena can run a query, it needs a place to store its results. You also need a Glue Crawler on the curated zone so Athena knows the Parquet schema.

  • Open the Athena console at https://console.aws.amazon.com/athena/.
  • If this is your first time visiting the Athena console in this region, click Explore the query editor to open the query editor.
  • In the navigation bar at the top, click Settings.
  • Click Manage.
  • In the Location of query result box, enter the following path:
s3://[[BUCKET_NAME="your-datalake-bucket-name"]]/athena-results/
  • Click Save.
  • Click Editor to return to the query editor.

Why does Athena need a results location?

Every query Athena runs produces output files (CSV results plus metadata). Athena stores these in the S3 path you just configured. This keeps your query history accessible and downloadable from the console.

Now you need to catalog the Parquet data in the curated zone so Athena can query it.

  • Switch back to the AWS Glue console at https://console.aws.amazon.com/glue/.
  • In the left navigation panel, click Crawlers.
  • Click Create crawler.
  • Enter curated-zone-crawler as the crawler name.
  • Click Next.
  • Under Data Sources, click Add a data source.
  • Choose S3 as the data source.
  • For the S3 path, enter:
s3://[[BUCKET_NAME="your-datalake-bucket-name"]]/curated/
  • Click Add an S3 data source.
  • Click Next.
  • For the IAM role, select GlueDataLakeRole from the existing roles list.
  • Click Next.
  • For the target database, select ecommerce_datalake from the dropdown.
  • Click Next.
  • Review the crawler configuration and click Create crawler.
  • Select the curated-zone-crawler checkbox and click Run.
  • Wait for the crawler status to show Completed. This takes about 1-2 minutes.

What did the crawler create?

The crawler scanned the Parquet files in s3://my-datalake-bucket/curated/ and created a new table called curated in the ecommerce_datalake database. This table includes the order_year and order_month columns that your ETL job added.

  • In the left navigation panel, click Databases.
  • Click ecommerce_datalake to view its tables.
  • Confirm you now see two tables: raw and curated.

Don't see the curated table?

Make sure the crawler's S3 path points to s3://my-datalake-bucket/curated/ (with the trailing slash). Also verify that your Glue ETL job from the previous step completed successfully and wrote Parquet files to that location.

help me troubleshoot why my curated-zone-crawler did not create a table in the Glue Data Catalog

Run analytics queries on your curated data

With both tables cataloged, you can now run SQL directly against your S3 data from the Athena query editor.

  • Return to the Athena console at https://console.aws.amazon.com/athena/.
  • In the Database dropdown on the left side of the query editor, select ecommerce_datalake.
  • Run the following query to see total revenue by category:
-- Total revenue by product category
SELECT
    category,
    COUNT(*) as total_orders,
    ROUND(SUM(amount), 2) as total_revenue,
    ROUND(AVG(amount), 2) as avg_order_value
FROM curated
GROUP BY category
ORDER BY total_revenue DESC;
  • Click Run or press Ctrl+Enter to execute the query.

What does this query do?

This query groups all orders by their product category, then calculates the total number of orders, total revenue, and average order value for each category. The ORDER BY total_revenue DESC sorts the results so the highest-earning category appears first.

You should see a results table with three categories (Electronics, Accessories, Home Office) and their revenue totals.

  • Clear the query editor and run this query to find top products by sales:
-- Top 5 products by total sales
SELECT
    product_name,
    category,
    COUNT(*) as units_sold,
    ROUND(SUM(amount), 2) as total_sales
FROM curated
GROUP BY product_name, category
ORDER BY total_sales DESC
LIMIT 5;
  • Click Run.

You should see the top five products ranked by total sales revenue.

  • Clear the query editor and run this query to see monthly revenue trends:
-- Monthly revenue trends for 2024
SELECT
    order_year,
    order_month,
    COUNT(*) as total_orders,
    ROUND(SUM(amount), 2) as monthly_revenue
FROM curated
GROUP BY order_year, order_month
ORDER BY order_year, order_month;
  • Click Run.

What does this query do?

This query uses the order_year and order_month columns that your Glue ETL job derived from the raw order_date string. Grouping by these columns shows you how revenue changes month to month throughout 2024.

You should see 12 rows showing each month's order count and revenue total.

Query returning zero results?

Make sure you selected ecommerce_datalake in the Database dropdown. If you see an error about the table not existing, verify that both crawlers completed successfully in the Glue console.

help me debug why my Athena query against the curated table returns no results

Compare query performance between CSV and Parquet

One of the biggest reasons to transform raw CSV into Parquet is query cost. Athena charges $5 per TB of data scanned. Parquet's columnar format means Athena reads only the columns your query needs, dramatically reducing the amount of data scanned.

  • Clear the query editor and run this query against the raw table (CSV format):
-- Query against raw CSV table
SELECT
    category,
    ROUND(SUM(CAST(amount AS double)), 2) as total_revenue
FROM raw
GROUP BY category
ORDER BY total_revenue DESC;
  • Click Run.
  • After the query completes, note the Data scanned value shown below the results panel.
  • Now run the same query against the curated table (Parquet format):
-- Same query against curated Parquet table
SELECT
    category,
    ROUND(SUM(amount), 2) as total_revenue
FROM curated
GROUP BY category
ORDER BY total_revenue DESC;
  • Click Run.
  • Compare the Data scanned value for this query against the CSV query result.

Why is Parquet so much cheaper to query?

CSV is a row-based format. Even if your query only needs the category and amount columns, Athena must scan every byte of every row. Parquet stores data in columns, so Athena reads only the two columns it needs.

Parquet also compresses data internally. The combination of columnar storage and compression typically reduces data scanned by 10x or more. Since Athena charges $5 per TB scanned, this translates directly into lower costs.

You should see that the Parquet query scanned significantly less data than the CSV query. This difference grows larger with bigger datasets.

Both queries show the same data scanned?

Make sure the first query runs against the raw table and the second against curated. Also verify that the curated zone actually contains Parquet files (check in S3 that curated/ has .parquet files, not CSV).

help me understand why my Athena queries on raw and curated tables show the same data scanned amount

You now have a fully working analytics layer on your data lake. You can run SQL against S3 data without provisioning any servers, and you have seen firsthand why Parquet is the industry standard for data lake storage. Next up, a secret mission to make your pipeline even more production-ready with partitioning.

Secret mission

Add Partitioning for Production Performance

Your data lake works, but it reads every single file on every query. In production, that wastes money. Add date-based partitioning so Athena only scans the months it needs, then measure exactly how much data (and cost) you save.

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 AWS cloud resources that incur small charges if left running (Glue ETL runs cost $0.44 per DPU-hour, and Athena charges $5 per TB scanned).

Cost warning

Your S3 storage costs are negligible for this dataset size, and the Glue Data Catalog is Always Free for the first 1 million objects and 1 million requests per month. However, if you re-run your Glue ETL jobs or Athena queries, each run incurs additional charges. Delete your resources if you do not plan to return.

Resources you used:

  • S3 bucket my-datalake-bucket (stores raw CSV, curated Parquet, partitioned Parquet, and Athena query results).
  • IAM role GlueDataLakeRole (with AmazonS3FullAccess and AWSGlueServiceRole policies attached).
  • Glue Data Catalog database ecommerce_datalake (with tables raw, curated, and curated_partitioned).
  • Glue ETL jobs ecommerce-etl-job and ecommerce-etl-job-partitioned.
  • Glue Crawlers raw-zone-crawler, curated-zone-crawler, and curated-zone-partitioned-crawler.
  • Local files generate_data.py, glue_etl_job.py, glue_etl_job_partitioned.py, and ecommerce_sales.csv.

Keep everything running

No action needed. Choose this if you are still actively building on your data lake or plan to add more data sources.

  • Your S3 storage costs are negligible for this small dataset (a few KB of Parquet and CSV).
  • The Glue Data Catalog tables and database incur no charges under the Always Free tier.
  • You will only be charged again if you manually re-run a Glue ETL job or execute more Athena queries.

Pause - I'll come back to this later

There is nothing to pause in this project. All services are serverless and pay-per-use. You only pay when you actively run jobs or queries.

  • Leave everything as-is. No processes are running in the background.
  • When you return, you can run new Glue jobs or Athena queries without any setup.

Delete - I don't want to use this again

Remove all project resources to stop any potential future charges. Run the following commands in your terminal.

Delete the Glue crawlers by running:

aws glue delete-crawler --name raw-zone-crawler
aws glue delete-crawler --name curated-zone-crawler
aws glue delete-crawler --name curated-zone-partitioned-crawler

Delete the Glue ETL jobs by running:

aws glue delete-job --job-name ecommerce-etl-job
aws glue delete-job --job-name ecommerce-etl-job-partitioned

Delete the Glue Data Catalog database (this also removes all tables within it) by running:

aws glue delete-database --name ecommerce_datalake

Empty and delete the S3 bucket by running:

aws s3 rb s3://my-datalake-bucket --force

Delete the IAM role. You must detach the policies first, then delete the role:

aws iam detach-role-policy --role-name GlueDataLakeRole --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess
aws iam detach-role-policy --role-name GlueDataLakeRole --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
aws iam delete-role --role-name GlueDataLakeRole

Delete local project files by running:

rm generate_data.py glue_etl_job.py glue_etl_job_partitioned.py ecommerce_sales.csv

Nice Work!

Nice Work!

You just built a production-style serverless data lake on AWS. From raw CSV data to optimized Parquet analytics, you now have hands-on experience with the most common data engineering pattern hiring managers look for.

You've learned how to:

  • Build a data lake with raw and curated zones in S3, and use AWS Glue Crawlers to auto-discover schemas into the Data Catalog.
  • Write a PySpark ETL job in AWS Glue that cleans, transforms, and converts CSV into optimized Parquet format.
  • Run SQL analytics in Amazon Athena directly on S3 data, and understand why columnar formats dramatically reduce query costs.
  • Secret Mission: Added date-based partitioning to the curated layer and measured the query cost savings with Athena's Data scanned metrics.

Ready to quiz yourself?