Serverless ETL Pipeline with Glue and Athena

Build a serverless ETL pipeline that transforms CSV data into Parquet on AWS.

Introduction

30 Second Summary

Every time you check a sales dashboard, see a product recommendation, or get a shipping estimate, there's a data pipeline behind the scenes transforming raw records into useful answers.

In this project, you will build a serverless ETL pipeline on AWS that lands raw sales data in a data lake, catalogs it automatically, transforms it into an optimized format, and lets you query it with SQL. This is the same pattern data engineers use at companies processing millions of records daily.

What You'll Build

You'll write a SQL query in Amazon Athena asking "which region generated the most revenue?" and get your answer instantly from data that flowed through your own pipeline.

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

A data lake in Amazon S3 loaded with 2000 rows of realistic sales data you generated from a Python script right in the browser.
An auto-discovered schema in the AWS Glue Data Catalog where column names and data types appear without you writing a single table definition.
Optimized Parquet data produced by a PySpark ETL job. You'll re-run the same query and see data scanned drop dramatically.
Secret Mission: Add date-based partitioning to slash query costs and boost performance even further.

Do I need any prior experience?

Basic Python and SQL knowledge is helpful but not required. You don't need an existing AWS account. You'll create one (for free) in Step 1.

Before We Start

Before any hands-on work, take a moment to commit to what you're about to build and why it matters for your data engineering journey. This is the pattern that powers data lakes at companies processing millions of records daily.

Create Your AWS Account and S3 Data Lake

Every data pipeline needs somewhere to land its data. Before you can ingest, transform, or query anything, you need a storage layer that separates raw source files from cleaned, optimized output.

In this step, you will create an AWS account, build an S3 bucket with two distinct zones (raw/ and processed/), and generate 2000 rows of realistic sales data using a Python script in AWS CloudShell. This two-zone structure is how production data lakes keep unprocessed source data separate from cleaned output.

In this step, get ready to:

Create an AWS Free Tier account and sign in to the console.
Set up an S3 bucket with raw and processed zones.
Generate and upload 2000 rows of sales data via CloudShell.

Create your AWS Free Tier account

Open your browser and navigate to https://aws.amazon.com/free/.
Click Create a free account.
Select the Free plan.
Complete the sign-up form with your email address, phone number, and a credit card for identity verification.

Will I be charged?

No. The credit card is for identity verification only. You will not be charged on the Free plan. New AWS customers receive $100 in credits on sign-up and can earn up to $200 total over 6 months.

Once your account is active, sign in to the AWS Management Console.

You should see the AWS Management Console home page with a search bar at the top and a list of AWS services.

Account not activating?

New accounts can take up to 24 hours to fully activate, though most are ready within minutes. Check your email for a confirmation message from AWS.

Why isn't my AWS account activating?

Create your S3 data lake bucket

Your S3 bucket will act as the data lake for this pipeline. You will create two folders inside it to separate raw input from processed output.

In the AWS Console search bar at the top, type S3 and select S3 from the results.
In the left navigation pane, choose General purpose buckets.
Click Create bucket.
For Bucket name, enter a unique name like sales-etl-pipeline-yourname (replace "yourname" with your actual name to make it globally unique).
Leave all other settings at their defaults and scroll to the bottom.
Click Create bucket.

Your new bucket appears in the bucket list. Now create the two-zone folder structure inside it.

Click your bucket name to open it.
Click Create folder.
Enter raw as the folder name.
For the folder encryption setting, choose Disable.
Click Save.
Repeat the same process to create a second folder named processed.

You should now see both raw/ and processed/ listed inside your bucket.

Why two folders?

Production data lakes always separate raw data from processed data. The raw/ zone holds unmodified source files exactly as they arrive. The processed/ zone holds cleaned, transformed, and optimized data ready for analytics.

This separation means you can always reprocess from the original source if your transformation logic changes.

Bucket name already taken?

S3 bucket names must be globally unique across all AWS accounts. Try adding numbers or a date to your name, for example sales-etl-pipeline-jane-2026. Names must be lowercase letters, numbers, and hyphens only.

Help me pick a valid S3 bucket name

Generate and upload sales data with CloudShell

AWS CloudShell is a browser-based terminal built into the AWS Console. It comes with Python 3 and boto3 pre-installed, so you can run scripts that interact with AWS services without installing anything locally.

In the top navigation bar of the AWS Console, click the terminal icon (it looks like a command prompt [>_]) to open CloudShell.
Wait for the shell environment to initialize. You will see a command prompt when it is ready.

You will now create a Python script that generates 2000 rows of realistic sales data and uploads it directly to your S3 bucket.

Open the script file in the CloudShell editor by running this command:

edit generate_sales_data.py

What does this command do?

The edit command opens CloudShell's built-in visual editor in a new tab. It supports copy-paste, syntax highlighting, and a familiar save shortcut.

The editor opens as an empty file. You will paste the script in three sections. Copy and paste each section in order.

Paste the first section, which sets up imports, configuration, and the product data pools:

import csv
import random
import io
import boto3
from datetime import date, timedelta

# Configuration - UPDATE THIS with your bucket name
BUCKET_NAME = "[[S3_BUCKET=\"sales-etl-pipeline-yourname\"]]"
S3_KEY = "raw/sales_data.csv"

# Data pools for realistic generation
categories = {
    "Electronics": ["Wireless Mouse", "USB-C Hub", "Mechanical Keyboard", "Webcam HD",
                    "Bluetooth Speaker", "Monitor 27 inch", "Laptop Stand", "Wireless Charger",
                    "Noise Cancelling Headphones", "Portable SSD", "Smart Power Strip",
                    "Drawing Tablet", "Docking Station", "Action Camera", "Webcam 4K",
                    "Wireless Earbuds", "Portable Monitor", "USB Microphone", "Ring Light",
                    "Smart Plug Pack"],
    "Office Supplies": ["Printer Paper", "Ballpoint Pens", "Sticky Notes", "File Folders",
                        "Whiteboard Markers", "Desk Organizer", "Label Maker", "Binder Clips",
                        "Planner Notebook", "Paper Shredder", "Stapler Heavy Duty", "Laminator",
                        "Calendar Planner", "Tape Dispenser", "Envelope Pack"],
    "Furniture": ["Standing Desk", "Ergonomic Chair", "Bookshelf", "Desk Lamp",
                  "Filing Cabinet", "Conference Table", "Monitor Arm", "Sit-Stand Converter",
                  "Cable Management Kit", "Acoustic Panel Set", "Standing Desk Mat",
                  "Desk Shelf Riser", "Office Partition", "Footrest", "Drawer Unit"]
}

What does this code do?

This section imports the libraries you need (csv, random, boto3 for AWS access) and defines the configuration. The BUCKET_NAME variable tells the script where to upload the data.

The categories dictionary holds realistic product names across Electronics, Office Supplies, and Furniture. These are the products that will appear in your generated sales records.

Safe Paste dialog

CloudShell has a Safe Paste feature enabled by default. When you paste multiline text, a dialog appears asking you to verify the content. Review the pasted text and click Paste to confirm.

Directly below the code you just pasted, add the second section with remaining data pools and the generation function:

regions = ["North America", "Europe", "Asia Pacific"]
segments = ["Consumer", "Corporate", "Home Office"]

# Price ranges per category
price_ranges = {
    "Electronics": (14.99, 299.99),
    "Office Supplies": (0.99, 89.99),
    "Furniture": (15.99, 599.99)
}

def generate_sales_data(num_rows=2000):
    rows = []
    start_date = date(2024, 1, 1)
    end_date = date(2024, 12, 31)
    days_range = (end_date - start_date).days

    for i in range(num_rows):
        order_id = 1001 + i
        order_date = start_date + timedelta(days=random.randint(0, days_range))
        category = random.choice(list(categories.keys()))
        product = random.choice(categories[category])
        quantity = random.randint(1, 10)
        low, high = price_ranges[category]
        unit_price = round(random.uniform(low, high), 2)
        revenue = round(quantity * unit_price, 2)
        region = random.choice(regions)
        segment = random.choice(segments)

        rows.append([order_id, order_date.isoformat(), category, product,
                     quantity, unit_price, revenue, region, segment])

    return rows

What does this code do?

The generate_sales_data function creates 2000 randomized sales records. Each record includes an order ID, a random date in 2024, a product from one of three categories, a quantity, a price within the category's range, and the computed revenue.

The regions and segments lists add geographic and customer-type variety so you can write interesting analytics queries later.

Below the function, paste the final section that generates the data, writes it to CSV, and uploads to S3:

# Generate data
print("Generating 2000 rows of sales data...")
rows = generate_sales_data(2000)

# Write to CSV in memory
output = io.StringIO()
writer = csv.writer(output)
writer.writerow(["order_id", "order_date", "product_category", "product_name",
                 "quantity", "unit_price", "revenue", "region", "customer_segment"])
writer.writerows(rows)

# Upload to S3
print(f"Uploading to s3://{BUCKET_NAME}/{S3_KEY}...")
s3 = boto3.client("s3")
s3.put_object(Bucket=BUCKET_NAME, Key=S3_KEY, Body=output.getvalue())
print("Done! Sales data uploaded successfully.")

What does this code do?

This section calls the generator, writes all 2000 rows into an in-memory CSV (using io.StringIO so nothing touches disk), and uploads the result directly to your S3 bucket's raw/ folder using the boto3 S3 client.

The put_object call streams the CSV content straight from memory to S3 in one request.

Make sure you update the BUCKET_NAME value on line 8 to match the bucket name you created earlier.
Save the file using Cmd+S (macOS) or Ctrl+S (Windows), then close the editor tab to return to the terminal.

✔️ Awesome, I've got everything!

Great. Make sure you saved the file and that your BUCKET_NAME matches your actual bucket name.

ⓧ I'd like to double check the full code

Your file should contain these three sections in order:

Section 1: Imports, BUCKET_NAME, S3_KEY, and the categories dictionary.
Section 2: regions, segments, price_ranges, and the generate_sales_data function.
Section 3: The main execution block that calls generate_sales_data(2000), writes the CSV, and uploads to S3.

Scroll up and confirm each section is present in your file, in order.

Before you run this, what do you expect to see printed in the terminal?

Run the script by entering this command:

python3 generate_sales_data.py

You should see three lines of output:

Generating 2000 rows of sales data...
Uploading to s3://sales-etl-pipeline-yourname/raw/sales_data.csv...
Done! Sales data uploaded successfully.

Confirm the file landed in S3 by going back to the S3 console.
Click your bucket name, then click the raw/ folder.

You should see sales_data.csv listed with a file size around 180 KB.

Seeing an AccessDenied or NoSuchBucket error?

Double check that the BUCKET_NAME value in your script matches your actual bucket name exactly. Bucket names are case-sensitive and must be all lowercase.

Also confirm that CloudShell is running in the same AWS Region where you created your bucket. Check the Region selector in the top-right corner of the console.

Help me debug my CloudShell upload error

Your data lake is live with 2000 rows of sales data ready for analysis. Next up, you will use AWS Glue to automatically discover the schema of this data and register it in a central catalog.

Crawl and Catalog Your Raw Data

Your S3 bucket has 2000 rows of sales data sitting in the raw/ folder. But before you can query or transform that data, you need to know its structure.

Instead of manually defining every column and data type, you will use an AWS Glue Crawler to inspect your CSV and automatically register its schema in the Data Catalog. The Data Catalog is the central metadata repository that all AWS analytics services share.

In this step, get ready to:

Create a Glue database to organize your catalog tables.
Run a Crawler that auto-discovers your CSV schema.
Verify the auto-discovered table in the Data Catalog.

Create a Glue database

A Glue database is a logical container that groups related tables together. You will create one called sales_analytics_db to hold all the tables your pipeline discovers.

In the AWS console, type Glue in the top search bar and select AWS Glue from the results.
In the left-hand menu, under Data catalog, choose Databases.
Click Add database.
Enter sales_analytics_db as the database name.
Leave the Location field empty.
Click Create database.

Your new database now appears in the Databases list.

What is a Glue database?

A Glue database does not store any actual data. It is a namespace in the Data Catalog where table definitions (metadata) live. Think of it like a folder that organizes table schemas so that services like Athena and Glue ETL jobs know where to find them.

Create and run a Glue Crawler

A Crawler scans your data store, identifies file formats, infers column names and types, and registers the result as a table in the Data Catalog. You will point it at your raw/ folder.

In the left-hand menu of the Glue console, choose Crawlers.
Click Create crawler.
In the Crawler name field, enter sales-raw-crawler.
Click Next.

Now you will configure the data source.

For Is your data already mapped to AWS Glue tables?, keep the default Not yet selected.
Click Add a data source.
For Data source, choose S3.
For the S3 path, click Browse S3 and navigate to your bucket's raw/ folder. Select it so the path reads s3://sales-etl-pipeline-yourname/raw/.
Click Add an S3 data source to confirm.
Click Next.

Next, configure the IAM role that gives the Crawler permission to read your S3 data.

Choose Create new IAM role.
The role name starts with AWSGlueServiceRole-. In the suffix field, enter sales so the full role name becomes AWSGlueServiceRole-sales.
Click Next.

What does this IAM role do?

The wizard creates an IAM role and attaches the AWS managed policy AWSGlueServiceRole to it. It also adds an inline policy granting read access to your specific S3 path. This means the Crawler has only the permissions it needs to read your data and write metadata to the Data Catalog.

Finally, configure where the Crawler outputs its results.

For Target database, select sales_analytics_db from the dropdown.
Leave Table name prefix empty.
For Crawler schedule, keep the default On demand.
Click Next.
Review your settings and click Create crawler.

Now run the Crawler to scan your data.

On the Crawlers page, select the checkbox next to sales-raw-crawler.
Click Run.
Wait for the Status column to change from Running to Completed. This typically takes 1-2 minutes. Click the refresh icon if the status does not update automatically.

Crawler stuck or failed?

If the crawler shows a Failed status, click on the crawler name and check the error message in the run details. The most common cause is an incorrect S3 path. Confirm the path ends with raw/ (with the trailing slash) and matches your bucket name exactly.

If the status stays at Running for more than 5 minutes, refresh the page. The Crawler may have already completed.

help me troubleshoot my Glue Crawler error

Verify the auto-discovered table

The Crawler read your CSV, inferred column names from the header row, and detected data types for each column. It registered all of this as a table in your database.

Before you check, what columns and types do you think the Crawler inferred from your 9-column CSV?

In the left-hand menu, under Data catalog, choose Databases.
Click on sales_analytics_db.
You should see a table named raw listed under Tables.
Click on the raw table name to view its schema.

You should see 9 columns with the following names and inferred types: order_id (bigint), order_date (string), product_category (string), product_name (string), quantity (bigint), unit_price (double), revenue (double), region (string), customer_segment (string).

Why did the Crawler choose these types?

The Crawler samples rows from your CSV and picks the most appropriate type. Whole numbers like order_id and quantity become bigint. Decimal numbers like unit_price and revenue become double. Everything else defaults to string.

This automatic inference saves you from manually declaring a schema for every dataset you ingest. The schema-on-read pattern means your data sits in S3 as-is, and the catalog defines how to interpret it when queried.

Don't see the raw table?

Make sure the Crawler status shows Completed (not Ready or Running). If you accidentally created the Crawler pointing to the bucket root instead of the raw/ folder, delete the Crawler, recreate it with the correct path, and run it again.

help me figure out why my Glue Crawler did not create a table

Your data is now cataloged and ready to query. Next up, you will use Amazon Athena to run SQL directly against this raw CSV and see firsthand why the format matters for performance.

Query Raw CSV Data in Athena

Your data is cataloged and ready. The Glue Crawler discovered your schema automatically, which means Amazon Athena can already query your sales data using standard SQL.

But there is a catch. Querying raw CSV files is inefficient because Athena must scan every byte of the file, even when you only need one or two columns. In this step, you will see this problem firsthand and understand exactly what the ETL transformation in the next step will fix.

In this step, get ready to:

Configure Athena's query result storage location.
Run SQL queries against your cataloged CSV data.
Observe the "Data scanned" metric to understand CSV's inefficiency.

Configure Athena's query result location

Before Athena can run any query, it needs an S3 location to store query results. This is a one-time setup.

In the AWS Console search bar, type Athena and select the service.
If this is your first time visiting Athena, click Explore the query editor to open the query editor.
Click Edit Settings to set up a query result location.
In the Location of query result box, enter the following path:

s3://[[BUCKETNAME="sales-etl-pipeline-yourname"]]/athena-results/

Click Save.
Click Editor in the top navigation to switch back to the query editor.

Why does Athena need a result location?

Every query Athena runs produces output files (the result rows in CSV format). These files need to live somewhere in S3. The athena-results/ folder inside your existing bucket keeps everything organized in one place.

Don't see Edit Settings?

If Athena opens directly into the query editor without prompting you, click Settings in the top navigation bar, then click Manage to access the same settings page.

Help me find the Athena query result settings

Run your first query on the raw data

Your Glue Crawler already registered the raw table in the sales_analytics_db database. Athena can query it immediately using standard SQL.

In the query editor, find the Database dropdown on the left side.
Select sales_analytics_db from the dropdown.
Paste the following query into the query editor and click Run:

SELECT * FROM raw LIMIT 10;

You should see 10 rows of your sales data returned in the results panel below the editor, with columns like order_id, order_date, product_category, and revenue.

Look below the results table for the Data scanned metric. Note the value displayed (it should be around 180 KB).

What does Data scanned mean?

Athena charges you based on the amount of data it reads from S3 to answer your query. The "Data scanned" value tells you exactly how many bytes Athena had to read.

For production data lakes with terabytes of data, this number directly affects both cost and query speed.

Observe the full-file scan problem

Your first query selected all columns, so it makes sense that Athena scanned the whole file. But what happens when you only need two columns out of nine?

Before you run the next query, predict: do you think Athena will scan less data when you only select region and revenue instead of all nine columns?
Clear the query editor and paste this query, then click Run:

SELECT region, SUM(revenue) as total_revenue
FROM raw
GROUP BY region
ORDER BY total_revenue DESC;

Check the "Data scanned" metric again. It is still around 180 KB, the exact same amount as your first query that selected all nine columns.

Why is the data scanned the same?

CSV is a row-based format. Every row contains all columns packed together in a single line of text. To read just region and revenue, Athena has to read every byte of every row because the columns are not stored separately.

This means Athena always scans the entire file regardless of which columns you select. For a 180 KB file this is fine. For a 180 GB production dataset, you would be paying to scan gigabytes of data you do not need. This is the exact problem you will fix in the next step with a columnar format called Parquet.

Query failed or no results?

Make sure sales_analytics_db is selected in the Database dropdown. If you see an error about no output location, return to Settings and confirm your S3 path starts with s3:// and ends with a trailing slash.

Help me troubleshoot my Athena query

Now you have seen the problem firsthand. CSV forces Athena to scan the entire file no matter how few columns you actually need. Next up, you will write a Glue ETL job that transforms this CSV into Parquet, a columnar format that lets Athena read only the columns each query needs.

Transform CSV to Parquet with a Glue ETL Job

In the previous step, you saw firsthand how querying raw CSV forces Athena to scan every byte of your file.

Now you will fix that problem. A Glue ETL job reads your raw data from the Data Catalog, transforms it, and writes it in Parquet format. Parquet is a columnar storage format that lets Athena read only the columns each query needs, dramatically reducing data scanned and query cost.

In this step, get ready to:

Create a Glue ETL job using the Spark script editor.
Write a PySpark script that transforms CSV to optimized Parquet.
Run the job and confirm Parquet files appear in your S3 processed zone.

Create a new Glue ETL job

You need a Spark job in AWS Glue to run your transformation script. The Spark script editor gives you a code editor where you can write PySpark directly.

Return to the AWS Glue console.
In the left navigation pane, expand ETL and select Jobs.
In the Create job panel, select Spark script editor.
Click Create.

The browser navigates to the script editor with some default boilerplate code. You will replace this with your own ETL script.

Write the PySpark transformation script

Your ETL script will read the raw CSV data from the Data Catalog, cast columns to proper types, add computed date columns, and write the result as Parquet.

Select all the default code in the Script pane and delete it.
Paste the following imports and job initialization 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, month, year

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

What does this code do?

The imports bring in the AWS Glue libraries (GlueContext, Job) and PySpark functions (col, month, year) needed for transformations.
getResolvedOptions reads job parameters passed by the Glue runtime.
GlueContext wraps the Spark context and exposes methods for reading from the Data Catalog.
job.init() tells Glue this job has started, enabling features like job bookmarks.

Directly below the initialization code, paste the transformation and write logic:

# Read raw data from the Glue Data Catalog
datasource = glueContext.create_dynamic_frame.from_catalog(
    database="sales_analytics_db",
    table_name="raw",
    transformation_ctx="datasource"
)

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

# Cast data types and add computed columns
df_transformed = df.select(
    col("order_id").cast("int"),
    col("order_date").cast("string"),
    col("product_category"),
    col("product_name"),
    col("quantity").cast("int"),
    col("unit_price").cast("double"),
    col("revenue").cast("double"),
    col("region"),
    col("customer_segment"),
    month(col("order_date")).alias("order_month"),
    year(col("order_date")).alias("order_year")
)

# Write as Parquet to the processed zone
df_transformed.write.mode("overwrite").parquet("s3://[[BUCKET_NAME="sales-etl-pipeline-yourname"]]/processed/")

job.commit()

What does this transformation do?

create_dynamic_frame.from_catalog reads data directly from the sales_analytics_db table without needing to specify S3 paths manually.
toDF() converts the Glue DynamicFrame into a standard PySpark DataFrame, giving access to the full Spark transformation API.
The select() call casts columns to proper types (e.g., order_id becomes an integer instead of a string) and adds two new computed columns: order_month and order_year.
The final .write.mode("overwrite").parquet(...) writes the transformed data as Parquet files to your processed/ folder.
job.commit() signals to Glue that the job completed successfully.

Why Parquet over CSV?

CSV is row-based. To read a single column, Athena must scan the entire row for every record. Parquet stores data by column, so Athena can skip columns it does not need.

Parquet also compresses each column independently, making file sizes smaller. The combination of column pruning and compression can reduce data scanned by 70-90%.

✔️ Awesome, I've got everything!

Great. Make sure the full script is in the editor. The imports and initialization should be at the top, followed by the read, transform, write, and commit sections below.

ⓧ I'd like to double check the full code

Scroll through your script editor and confirm these sections appear in order:

Imports and job initialization (7 import lines, then args, sc, glueContext, spark, job setup).
Reading from the catalog with create_dynamic_frame.from_catalog.
Converting to DataFrame with toDF().
The df.select(...) block casting types and adding order_month and order_year.
Writing Parquet to s3://your-bucket/processed/.
job.commit() at the very end.

Configure job details and run the job

Before running, you need to give the job a name, assign the IAM role, and set the worker count to keep costs low.

Click the Job details tab at the top of the editor.
In the Name field, enter csv-to-parquet-sales.
In the IAM Role dropdown, select AWSGlueServiceRole-sales (the role created in an earlier step).
Set Requested number of workers to 2 (the minimum for Spark jobs).
Leave Worker type as G.1X.
Click Save in the top bar.
Click Run.

The job is now queued and will begin executing your PySpark script on a serverless Spark cluster.

Click the Runs tab to monitor your job's progress.

Before the status updates, what do you expect will happen? The script reads CSV, transforms it, and writes Parquet. Do you think it will succeed on the first try?

Wait 2-3 minutes for the status to change to Succeeded. The page refreshes automatically.

What should I see?

On the Runs tab, you will see a card for your job run showing its Run status as Succeeded, along with the start time, end time, and runtime (typically around 1-2 minutes of execution time).

Confirm the output by navigating to Amazon S3 in the AWS console.
Open your bucket and navigate into the processed/ folder.

You should see one or more .parquet files in the folder. These are the transformed, columnar versions of your sales data.

Job status shows Failed?

Check that your S3 bucket name in the script exactly matches your actual bucket name (including any typos in the yourname portion).
Verify the IAM role AWSGlueServiceRole-sales has permission to read from and write to your S3 bucket. The most common failure is an Access Denied error.
Confirm the database name is sales_analytics_db and the table name is raw in your script. These must match exactly what your crawler created.

Why is my Glue ETL job failing with an Access Denied or table-not-found error?

Your raw CSV is now transformed into optimized Parquet. Next up, you will catalog this new data and run the same queries to see just how much faster columnar storage makes things.

Query Optimized Data and Answer Business Questions

In the last step, your Glue ETL job transformed raw CSV into Parquet, a columnar storage format.

Now it is time to prove the payoff. Amazon Athena can use columnar pruning to scan only the columns each query needs. You will re-run the same revenue query and watch the "Data scanned" metric drop dramatically, then write SQL analytics that answer real business questions from your optimized data.

In this step, get ready to:

Crawl the Parquet data so Athena can query it.
Compare data scanned between CSV and Parquet queries.
Answer four business questions using SQL analytics.

Make your Parquet data queryable with a Glue Crawler

Your processed/ folder contains Parquet files, but Athena does not know they exist yet. You need to run a Glue Crawler on that folder so it registers as a new table in the Data Catalog.

In the AWS Glue console, click Crawlers in the left navigation pane.
Click the link to your existing crawler (the one you created in Step 2).
Click Edit.
On Step 2: Choose data sources and classifiers, click Edit.
Under Data sources, click Add a data source.
For S3 path, click Browse S3 and select your bucket's processed/ folder.
Click Add an S3 data source.
Click Next through the remaining steps (security settings, output, scheduling) without changing anything.
On the review page, click Update.

Back on the Crawlers page, select your crawler and click Run.
Wait 1-2 minutes for the status to show Completed.

In the left navigation, click Databases, then click sales_analytics_db.
Confirm you now see a table named processed alongside your existing raw table.

What did the crawler discover?

The crawler inspected your Parquet files and automatically registered the schema in the Data Catalog. The processed table includes all original columns plus the two computed columns your ETL job added: order_month and order_year.

Because Parquet embeds its own schema, the crawler can detect precise data types (int, double, string) without guessing from text patterns like it did with CSV.

Don't see the processed table?

Make sure your crawler's S3 path points to the processed/ folder with a trailing slash. If the path is missing the slash, the crawler may not find the Parquet files.

Also confirm the crawler run status shows Completed and the Tables changes column shows at least 1 table added.

Help me troubleshoot my Glue Crawler not discovering my Parquet table.

Compare data scanned between CSV and Parquet

In Step 3, you ran a region revenue query against raw CSV and noted how much data Athena scanned. Now you will run the identical query against Parquet and compare.

Open the Amazon Athena console.
Confirm sales_analytics_db is selected as your database in the left panel.

Before you run the next query, think about what you expect. The Parquet table has the same data as the CSV. Do you think Athena will scan the same amount of data, more, or less?

Run the region revenue query against your Parquet table by pasting this into the query editor:

SELECT region, SUM(revenue) as total_revenue
FROM processed
GROUP BY region
ORDER BY total_revenue DESC;

After the query completes, look at the Data scanned value shown below the results.

You should see a significant reduction compared to the CSV query. Parquet typically reduces data scanned by 70-90% because Athena only reads the two columns it needs (region and revenue) instead of scanning the entire file.

Why is data scanned so much lower?

CSV stores data row by row. To read the revenue column, Athena must scan every row of every column. Parquet stores data column by column, so Athena skips columns it does not need entirely.

This matters for cost too. Athena charges per byte scanned. Less scanning means lower bills at scale.

Data scanned looks the same as CSV?

Make sure your query references the processed table, not raw. If you accidentally queried the raw table again, you will see the same high scan amount.

Help me understand why my Parquet query is not showing reduced data scanned in Athena.

Answer business questions with SQL analytics

Your pipeline is complete and your data is optimized. Now put it to work by answering real business questions that a data analyst would ask.

Find the top 5 product categories by total revenue by running this query:

SELECT product_category, SUM(revenue) as total_revenue
FROM processed
GROUP BY product_category
ORDER BY total_revenue DESC
LIMIT 5;

What does this query do?

This groups all orders by their product category, sums the revenue within each group, sorts from highest to lowest, and returns only the top 5. You can see which category drives the most business.

Find the monthly revenue trend to see which months performed best by running this query:

SELECT order_month, order_year,
       SUM(revenue) as monthly_revenue
FROM processed
GROUP BY order_month, order_year
ORDER BY order_year, order_month;

You should see 12 rows representing each month of 2024. Look for which months had the highest and lowest revenue.

Find the average order value by customer segment by running this query:

SELECT customer_segment,
       ROUND(AVG(revenue), 2) as avg_order_value
FROM processed
GROUP BY customer_segment
ORDER BY avg_order_value DESC;

This reveals which customer segment (Consumer, Corporate, or Home Office) places the highest-value orders on average.

Find the best-selling product in each region using a window function by running this query:

SELECT region, product_name, total_quantity
FROM (
  SELECT region, product_name,
         SUM(quantity) as total_quantity,
         ROW_NUMBER() OVER (
           PARTITION BY region
           ORDER BY SUM(quantity) DESC
         ) as rn
  FROM processed
  GROUP BY region, product_name
) WHERE rn = 1
ORDER BY total_quantity DESC;

What does this window function do?

The inner query groups sales by region and product, then ranks products within each region by total quantity sold. ROW_NUMBER() assigns rank 1 to the top seller in each region.

The outer query filters to only keep rn = 1 rows, giving you exactly one winner per region.

You should see three rows, one for each region (North America, Europe, Asia Pacific), each showing that region's top-selling product by quantity.

Getting a syntax error on the window function query?

Athena requires the subquery to be wrapped in parentheses. Make sure you have the opening ( after FROM and the closing ) before WHERE.

Help me fix my Athena SQL window function query that is returning an error.

You just answered four real business questions using SQL against your optimized data lake. Every query benefited from the Parquet transformation you built in Step 4.

Your serverless ETL pipeline is fully operational. You have raw data landing in S3, a Glue Crawler discovering schemas automatically, an ETL job transforming CSV to Parquet, and Athena answering business questions at a fraction of the scan cost. Next up, the Secret Mission challenges you to take this even further with date-based partitioning.

Secret mission

Add Date-Based Partitioning

Your Parquet transformation already slashed data scanned dramatically. But production data lakes handle terabytes, not kilobytes. In this secret mission, you'll add date-based partitioning to your pipeline so Athena can skip entire folders of irrelevant data, cutting costs even further.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. All usage in this project is covered by your $200 Free Tier credits. The Glue Data Catalog is always free for the first 1 million objects, and Glue ETL jobs only run on demand, so idle resources do not accumulate charges.

Resources you used:

S3 bucket (sales-etl-pipeline-yourname) with raw CSV, processed Parquet, partitioned Parquet, and Athena query results.
AWS Glue Data Catalog database (sales_analytics_db) and tables (raw, processed, processed_partitioned).
AWS Glue Crawler.
AWS Glue ETL Job (csv-to-parquet-sales).
IAM Role (AWSGlueServiceRole-sales).

Keep everything running

No action needed. Choose this if you are still actively experimenting with your pipeline or want to try new queries.

Your S3 bucket stores a small amount of data (under 1 MB). Storage costs are negligible within the Free Tier.
The Glue Data Catalog, crawlers, and job definition remain available at no cost while idle.
Athena charges only when you run queries. No queries means no charges.

Pause - I'll come back to this later

Shut down running processes to free up resources, but keep your data and definitions in place so you can resume later.

The Glue ETL job only runs on demand. It is already paused unless you have scheduled it.
Leave your S3 data, Glue database, and crawler in place. They incur no charges while idle.
When you return, you can re-run the crawler or job without recreating anything.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

Delete the Glue ETL Job: Open the AWS Glue console. Go to the Jobs page. Select csv-to-parquet-sales. Open the Actions menu and choose Delete job. Type delete to confirm.
Delete the Glue Crawler: In the Glue console, go to Crawlers. Select your crawler, then choose Delete and confirm.
Delete the Glue Database (this also deletes all tables): In the Glue console, go to Databases. Select the checkbox next to sales_analytics_db and choose Remove.
Empty and delete the S3 bucket: Open the S3 console. Select your bucket (sales-etl-pipeline-yourname) and choose Empty. Type permanently delete to confirm. After emptying completes, select the bucket again and choose Delete. Type the bucket name to confirm, then choose Delete bucket.
Delete the IAM Role: Open the IAM console. In the navigation pane, choose Roles. Select the checkbox next to AWSGlueServiceRole-sales. Choose Delete at the top of the page. Enter the role name to confirm, then choose Delete. The console automatically detaches managed policies.

Nice Work!

Nice work! You just built a complete serverless ETL pipeline on AWS that ingests raw sales data, catalogs it automatically, transforms it into query-optimized Parquet, and answers real business questions with SQL.

You've learned how to:

Build a production-style S3 data lake with raw and processed zones, generate realistic data with Python in AWS CloudShell, and use Glue Crawlers to auto-discover schemas into the Data Catalog.
Write and run a PySpark ETL job in AWS Glue that transforms CSV into columnar Parquet format, dramatically reducing data scanned by downstream queries.
Query data in Amazon Athena with SQL to answer real business questions like top revenue categories, monthly trends, and segment analysis.
Secret Mission: Added date-based partitioning to further reduce query costs and boost performance by letting Athena skip entire folders of irrelevant data.

Ready to quiz yourself?