Build a Serverless Data Lake on AWS

Build an S3, Glue, and Athena pipeline to ingest and query e-commerce data.

Introduction

30 Second Summary

Every online purchase generates a row of data. But raw data is messy. It arrives with missing values, duplicate records, and no structure. Before anyone can run reports or spot trends, someone needs to build the pipeline that cleans and organizes it all.

In this project, you will build a serverless data lake pipeline on AWS that ingests raw e-commerce order data, cleans it with automated quality checks, and makes it queryable with standard SQL.

What You'll Build

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

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

  • An S3 data lake with raw and processed storage zones holding e-commerce order data.
  • A Glue Crawler and Data Catalog that automatically discovers your data's schema and makes it visible to analytics services.
  • A Python Shell ETL job that removes nulls, deduplicates records, and calculates a derived revenue column.
  • Secret Mission: Convert your processed data to Parquet format and measure Athena scan cost reduction.

Are there any prerequisites?

You should have basic familiarity with SQL and Python. You also need an AWS account with the AWS CLI already configured.

Set Up Your AWS Environment

Before you can build a data pipeline, you need a place for data to live and permissions for services to access it.

In this step, you will create the AWS infrastructure that underpins your data lake: two S3 buckets for storage, an IAM role for service permissions, and sample data with intentional quality issues.

In this step, get ready to:
  • Create S3 buckets for your data lake and Athena query results.
  • Create an IAM role that grants AWS Glue access to your buckets.
  • Generate sample order data and upload it to S3.
Verify your AWS CLI and create S3 buckets

Your AWS CLI needs to be configured so it can talk to your AWS account. You will also create two S3 buckets: one to hold your data lake files and one for Athena query results.

  • Open your terminal.
  • Confirm your AWS CLI is configured by running this command:
aws sts get-caller-identity

✔️ I see my account ID and ARN

You should see JSON output with your Account, UserId, and Arn. This confirms your CLI can authenticate with AWS.

ⓧ I see an error

If you see a credentials or configuration error, run the following command and enter your access key, secret key, region, and output format when prompted:

aws configure

After configuring, re-run aws sts get-caller-identity to confirm it works.

Now you will create two S3 buckets. Bucket names must be globally unique across all of AWS, so include your name or a random string.

  • Create your data lake bucket by running this command:
aws s3 mb s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]
  • Create your Athena results bucket by running this command:
aws s3 mb s3://[[ATHENA_BUCKET="your-athena-results-bucket"]]

Why two buckets?

Your data lake bucket holds the actual data (raw and processed zones). Athena needs a separate location to write query result files. Keeping them separate makes cleanup easier and avoids mixing data with query outputs.

  • Verify both buckets exist by running:
aws s3 ls

You should see both bucket names in the output list.

Bucket creation failed?

If you see a "BucketAlreadyExists" error, someone else in the world already owns that bucket name. Try a different name with more unique characters (e.g., add today's date or your initials).

If you see an access denied error, your IAM user may not have S3 permissions. Help me fix my S3 bucket creation error.

Create an IAM role for AWS Glue

AWS Glue needs permission to read from and write to your S3 buckets. You will create an IAM role with a trust policy that allows the Glue service to assume it, then attach the required permissions.

  • Create a file called trust-policy.json with the following content:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "glue.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

What is a trust policy?

A trust policy defines which AWS services or accounts are allowed to "assume" (use) a role. Here you are telling AWS that the Glue service is permitted to take on this role's permissions.

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

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

  • Attach the AWS-managed Glue service policy by running:
aws iam attach-role-policy \
    --role-name GlueServiceRole \
    --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole

What does AWSGlueServiceRole provide?

This AWS-managed policy grants Glue the baseline permissions it needs to run crawlers and jobs (CloudWatch Logs, EC2 networking, etc). It does not include S3 access, which is why you need an additional inline policy.

  • Create a file called s3-access-policy.json that grants full S3 access to your two buckets:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:*",
      "Resource": [
        "arn:aws:s3:::[[DATALAKE_BUCKET="your-datalake-bucket"]]",
        "arn:aws:s3:::[[DATALAKE_BUCKET="your-datalake-bucket"]]/*",
        "arn:aws:s3:::[[ATHENA_BUCKET="your-athena-results-bucket"]]",
        "arn:aws:s3:::[[ATHENA_BUCKET="your-athena-results-bucket"]]/*"
      ]
    }
  ]
}
  • Attach this inline policy to your role by running:
aws iam put-role-policy \
    --role-name GlueServiceRole \
    --policy-name S3AccessPolicy \
    --policy-document file://s3-access-policy.json

Your Glue role now has the managed service policy plus your custom S3 access policy attached.

Seeing a MalformedPolicyDocument error?

Double check that your JSON files have matching braces and brackets. A missing comma or extra trailing comma is the most common cause.

Make sure you replaced the bucket names in s3-access-policy.json with the actual names you chose earlier.

Help me fix my IAM policy JSON error.

Generate sample data and upload to S3

Your data lake needs data. You will run a Python script that generates a realistic e-commerce orders dataset with intentional quality problems (null values and duplicate rows). These flaws give your ETL job meaningful cleaning work in the next steps.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a project folder and move into it by running these commands:
mkdir datalake-pipeline
cd datalake-pipeline
  • Open VS Code in this folder by running:
code .
  • Create a new file called generate_data.py and paste the following code:
import csv
import random
from datetime import datetime, timedelta

random.seed(42)

products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Hub", "Desk Lamp"]
statuses = ["completed", "pending", "cancelled", "returned"]

rows = []
for i in range(1, 501):
    order_date = datetime(2026, 1, 1) + timedelta(days=random.randint(0, 165))
    row = {
        "order_id": i,
        "customer_id": random.randint(1000, 1200),
        "product_name": random.choice(products),
        "quantity": random.randint(1, 5),
        "unit_price": round(random.uniform(9.99, 499.99), 2),
        "order_date": order_date.strftime("%Y-%m-%d"),
        "status": random.choice(statuses),
    }
    # Introduce nulls in ~5% of rows
    if random.random() < 0.05:
        row["quantity"] = ""
    if random.random() < 0.05:
        row["customer_id"] = ""
    rows.append(row)

# Introduce ~20 duplicate rows
for _ in range(20):
    rows.append(random.choice(rows[:480]).copy())

random.shuffle(rows)

with open("orders_raw.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["order_id", "customer_id", "product_name", "quantity", "unit_price", "order_date", "status"])
    writer.writeheader()
    writer.writerows(rows)

print(f"Generated orders_raw.csv with {len(rows)} rows (includes nulls and duplicates)")

What does this script do?

  • Generates 500 e-commerce order rows with fields like order_id, customer_id, product_name, quantity, and unit_price.
  • Randomly blanks out quantity or customer_id in about 5% of rows to simulate missing data.
  • Appends 20 duplicate rows to simulate data ingestion errors.
  • Shuffles the rows and writes them to a CSV file called orders_raw.csv.
  • Save generate_data.py.
  • Run the script by executing this command in your terminal:
python3 generate_data.py

You should see output confirming 520 rows were generated.

  • Upload the CSV to your data lake bucket's raw zone by running:
aws s3 cp orders_raw.csv s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/raw/orders_raw.csv
  • Verify the file is in S3 by running:
aws s3 ls s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/raw/

You should see orders_raw.csv listed with its file size.

Upload failed or file not showing?

Make sure you ran generate_data.py first. The orders_raw.csv file must exist in your current directory before you can upload it.

If you see an access denied error on the aws s3 cp command, your IAM user needs S3 write permissions. This is separate from the Glue role you just created.

Help me troubleshoot my S3 upload issue.

✔️ Awesome, I've got everything!

Great. Double check you have saved generate_data.py and confirmed the upload to S3.

ⓧ I'd like to double check the full code

Here is the complete generate_data.py for comparison:

import csv
import random
from datetime import datetime, timedelta

random.seed(42)

products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Hub", "Desk Lamp"]
statuses = ["completed", "pending", "cancelled", "returned"]

rows = []
for i in range(1, 501):
    order_date = datetime(2026, 1, 1) + timedelta(days=random.randint(0, 165))
    row = {
        "order_id": i,
        "customer_id": random.randint(1000, 1200),
        "product_name": random.choice(products),
        "quantity": random.randint(1, 5),
        "unit_price": round(random.uniform(9.99, 499.99), 2),
        "order_date": order_date.strftime("%Y-%m-%d"),
        "status": random.choice(statuses),
    }
    # Introduce nulls in ~5% of rows
    if random.random() < 0.05:
        row["quantity"] = ""
    if random.random() < 0.05:
        row["customer_id"] = ""
    rows.append(row)

# Introduce ~20 duplicate rows
for _ in range(20):
    rows.append(random.choice(rows[:480]).copy())

random.shuffle(rows)

with open("orders_raw.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["order_id", "customer_id", "product_name", "quantity", "unit_price", "order_date", "status"])
    writer.writeheader()
    writer.writerows(rows)

print(f"Generated orders_raw.csv with {len(rows)} rows (includes nulls and duplicates)")

Cost check

S3 storage is free up to 5 GB for 12 months on new AWS accounts. The data in this project is under 1 MB, so storage costs are negligible.

Your data lake foundation is ready. Next up, you will use a Glue Crawler to automatically discover the schema of your raw data.

Catalog Data with a Glue Crawler

In the previous step, you set up your AWS environment with S3 buckets, an IAM role, and raw order data. Now you have 520 rows of CSV sitting in your data lake bucket, but no service knows what columns or data types that file contains.

Before any analytics service can query your data, it needs a schema. A Glue Crawler automatically scans your S3 data, infers column names and types, and registers the results in the AWS Glue Data Catalog. This step creates that catalog entry so Athena can query your raw data later.

In this step, get ready to:
  • Create a Glue Data Catalog database and crawler.
  • Run the crawler to auto-discover your CSV schema.
  • Verify the inferred table schema in the Data Catalog.
Create the Glue database and crawler

The Data Catalog organizes table metadata into databases. You need a database first, then a crawler that knows where to look for data and where to write the discovered schema.

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

What does this command do?

The --database-input flag accepts a JSON object with the database name. Glue stores the name in lowercase for Hive compatibility. This database acts as the container for all the table metadata your crawler will discover.

  • Create a Glue Crawler named raw-orders-crawler that points at your raw data prefix by running:
aws glue create-crawler \
    --name raw-orders-crawler \
    --role GlueServiceRole \
    --database-name datalake_db \
    --targets '{"S3Targets": [{"Path": "s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/raw/"}]}'

What does this command do?

The --name flag sets the crawler's identifier. The --role points to the IAM role you created in Step 1, which gives the crawler permission to read S3 and write to the Data Catalog.

The --targets flag tells the crawler which S3 path to scan. The --database-name flag tells it where to write the discovered table metadata.

  • Confirm neither command returned an error. A successful create-database and create-crawler produce no output on success.

Seeing an AccessDeniedException?

Make sure the IAM role name in --role matches the role you created in Step 1 exactly. Also verify that your AWS CLI user has the glue:CreateDatabase and glue:CreateCrawler permissions.

Help me troubleshoot my Glue crawler AccessDeniedException

Run the crawler and wait for completion

The crawler is created but hasn't scanned anything yet. You need to start it and wait for it to finish before checking the results.

  • Start the crawler by running:
aws glue start-crawler --name raw-orders-crawler
  • Wait 1-2 minutes for the crawler to complete. Check its status by running:
aws glue get-crawler --name raw-orders-crawler --query 'Crawler.State' --output text
  • Re-run the status check command until it returns READY. This means the crawl completed successfully.

Why does it take a minute or two?

Glue allocates compute resources (2 DPUs minimum) to run the crawler, even for tiny datasets. It scans your CSV file, infers the schema from the header row and data values, then writes the results to the Data Catalog. The 10-minute minimum billing applies regardless of how fast the crawl finishes.

  • Once the crawler shows READY, verify the auto-discovered table by running:
aws glue get-table --database-name datalake_db --name raw

What does this output show?

The get-table command returns the full table definition including column names and inferred data types. The crawler names the table after the S3 prefix (raw), since your data lives under the raw/ prefix.

Look for the StorageDescriptor.Columns array in the JSON output. You should see columns like order_id (bigint), customer_id (string), product_name (string), quantity (string), unit_price (double), order_date (string), and status (string).

Table not found?

If you get an EntityNotFoundException, the crawler may not have finished. Run the get-crawler status command again and wait until it returns READY. Also confirm your bucket name in the --targets path matches the actual bucket you created in Step 1.

Help me figure out why my Glue crawler did not create a table

Understand the discovered schema

The crawler detected column names from your CSV header and inferred data types by sampling the data values. This metadata is now available to any AWS analytics service.

  • Notice that quantity was inferred as string instead of bigint. This is because your raw data contains empty values (nulls) in the quantity column. The crawler sees mixed content and picks the safest type.
  • The order_id column was inferred as bigint because every value in that column is a whole number with no nulls.
  • The unit_price column was inferred as double because it contains decimal values.

What is the Glue Data Catalog?

The Data Catalog is a central metadata repository. It stores table definitions (column names, types, S3 locations) that services like Amazon Athena, EMR, and Redshift Spectrum can all reference. You create the catalog entry once, and multiple services can query the underlying data without needing their own schema definitions.

Your raw data now has a schema registered in the Glue Data Catalog. Next up, you will build an ETL job that reads this raw data, cleans it, and writes a higher-quality version to your processed zone.

Build and Run a Glue Python Shell ETL Job

In the previous step, you cataloged your raw order data so that AWS services can find and understand it automatically. Now it is time to clean that data.

Raw data almost always has quality problems: missing values, duplicate records, and missing derived fields. In this step, you will write a Python ETL script that reads the raw CSV from S3, removes nulls and duplicates, calculates a revenue column, and writes the cleaned output to a processed zone.

In this step, get ready to:
  • Write a Python ETL script that cleans and transforms your raw order data.
  • Deploy the script to AWS Glue as a Python Shell job.
  • Run the job and verify the cleaned output in your processed zone.
Write the ETL script

Your ETL script follows the classic Extract-Transform-Load pattern. It reads raw data from S3, applies data quality checks, and writes clean data back to a different prefix in the same bucket.

Why Python Shell instead of Spark?

Your dataset is only ~520 rows. A Spark job requires a minimum of 2 DPUs ($0.88/hour minimum) and is overkill for this data size.

Python Shell uses 0.0625 DPU (about $0.003 per run) and comes pre-loaded with boto3 and pandas. It teaches the same ETL concepts without the cost or complexity of a distributed cluster.

  • In VS Code, create a new file called transform_orders.py in your project folder.
  • Paste the following imports and configuration block:
import sys
import boto3
import pandas as pd
import io

# Configuration from job arguments
BUCKET = sys.argv[1] if len(sys.argv) > 1 else "your-datalake-bucket"
RAW_KEY = "raw/orders_raw.csv"
PROCESSED_KEY = "processed/orders_cleaned.csv"

s3 = boto3.client("s3")

What does this code do?

  • BUCKET reads the bucket name from a command-line argument, or falls back to a default. Glue Python Shell jobs pass arguments via sys.argv.
  • RAW_KEY and PROCESSED_KEY define the S3 paths for input and output data.
  • s3 = boto3.client("s3") creates an S3 client the script uses to read and write objects.
  • Below the configuration block, add the Extract step that reads the raw CSV from S3:
# Extract: Read raw CSV from S3
response = s3.get_object(Bucket=BUCKET, Key=RAW_KEY)
df = pd.read_csv(io.BytesIO(response["Body"].read()))

print(f"Raw data: {len(df)} rows")

What does this code do?

s3.get_object() fetches the raw CSV file as a streaming response. Wrapping response["Body"].read() in io.BytesIO converts it into something pandas can read as a file.

  • Add the Transform step with data quality checks:
# Transform: Data quality checks
# 1. Remove rows with null values in critical columns
df = df.dropna(subset=["order_id", "customer_id", "quantity"])

# 2. Deduplicate by order_id (keep first occurrence)
df = df.drop_duplicates(subset=["order_id"], keep="first")

# 3. Ensure correct data types
df["order_id"] = df["order_id"].astype(int)
df["customer_id"] = df["customer_id"].astype(int)
df["quantity"] = df["quantity"].astype(int)
df["unit_price"] = df["unit_price"].astype(float)

# 4. Calculate derived column: revenue
df["revenue"] = df["quantity"] * df["unit_price"]
df["revenue"] = df["revenue"].round(2)

print(f"Cleaned data: {len(df)} rows")
print(f"Removed {520 - len(df)} rows (nulls + duplicates)")

What do these transformations do?

  • dropna() removes rows where order_id, customer_id, or quantity is missing. These are critical fields without which a row is meaningless.
  • drop_duplicates() keeps only the first occurrence of each order_id, removing the ~20 duplicate rows that were in the raw data.
  • The type casts ensure numeric columns are proper integers and floats for downstream analytics.
  • The revenue column is a derived field: quantity multiplied by unit price. Analysts can query this directly instead of calculating it each time.
  • Add the Load step that writes the cleaned data back to S3:
# Load: Write cleaned CSV back to S3
buffer = io.StringIO()
df.to_csv(buffer, index=False)
s3.put_object(Bucket=BUCKET, Key=PROCESSED_KEY, Body=buffer.getvalue())

print(f"Wrote cleaned data to s3://{BUCKET}/{PROCESSED_KEY}")

What does this code do?

df.to_csv(buffer, index=False) converts the cleaned DataFrame to CSV format in memory. Then s3.put_object() uploads it to the processed/ prefix in your data lake bucket.

  • Save transform_orders.py.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

import sys
import boto3
import pandas as pd
import io

# Configuration from job arguments
BUCKET = sys.argv[1] if len(sys.argv) > 1 else "your-datalake-bucket"
RAW_KEY = "raw/orders_raw.csv"
PROCESSED_KEY = "processed/orders_cleaned.csv"

s3 = boto3.client("s3")

# Extract: Read raw CSV from S3
response = s3.get_object(Bucket=BUCKET, Key=RAW_KEY)
df = pd.read_csv(io.BytesIO(response["Body"].read()))

print(f"Raw data: {len(df)} rows")

# Transform: Data quality checks
# 1. Remove rows with null values in critical columns
df = df.dropna(subset=["order_id", "customer_id", "quantity"])

# 2. Deduplicate by order_id (keep first occurrence)
df = df.drop_duplicates(subset=["order_id"], keep="first")

# 3. Ensure correct data types
df["order_id"] = df["order_id"].astype(int)
df["customer_id"] = df["customer_id"].astype(int)
df["quantity"] = df["quantity"].astype(int)
df["unit_price"] = df["unit_price"].astype(float)

# 4. Calculate derived column: revenue
df["revenue"] = df["quantity"] * df["unit_price"]
df["revenue"] = df["revenue"].round(2)

print(f"Cleaned data: {len(df)} rows")
print(f"Removed {520 - len(df)} rows (nulls + duplicates)")

# Load: Write cleaned CSV back to S3
buffer = io.StringIO()
df.to_csv(buffer, index=False)
s3.put_object(Bucket=BUCKET, Key=PROCESSED_KEY, Body=buffer.getvalue())

print(f"Wrote cleaned data to s3://{BUCKET}/{PROCESSED_KEY}")
Upload the script and create the Glue job

AWS Glue runs your script from S3, not from your local machine. You need to upload the script to your data lake bucket and then register it as a Glue job.

  • Upload the script to your S3 bucket's scripts/ prefix by running:
aws s3 cp transform_orders.py s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/scripts/transform_orders.py
  • Verify the upload succeeded by running:
aws s3 ls s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/scripts/

You should see transform_orders.py listed in the output.

  • Create the AWS Glue Python Shell job by running:
aws glue create-job \
    --name transform-orders-job \
    --role GlueServiceRole \
    --command '{"Name":"pythonshell","PythonVersion":"3.9","ScriptLocation":"s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/scripts/transform_orders.py"}' \
    --max-capacity 0.0625

What does this command do?

  • --name transform-orders-job gives your job a descriptive name you will use to run it later.
  • --role GlueServiceRole tells Glue to use the IAM role you created in Step 1. This role grants the job permission to read and write to your S3 buckets.
  • --command specifies the job type (pythonshell), Python version, and the S3 location of your script.
  • --max-capacity 0.0625 allocates the minimum DPU (1 GB memory). This is the cheapest option and more than enough for a 520-row CSV.

You should see a JSON response with the job name confirming the job was created.

Seeing an AccessDeniedException?

Make sure the IAM role name matches exactly what you created in Step 1. Check that your AWS CLI user has permission to create Glue jobs (the AWSGlueServiceRole policy on your user or an admin policy).

help me fix an AccessDeniedException when creating a Glue job

Run the job and verify the output

With the job registered, you can trigger a run and watch it complete. Python Shell jobs on small data typically finish in 1-3 minutes.

  • Start the job run by running:
aws glue start-job-run --job-name transform-orders-job

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

  • Check the job status by running (replace the run ID with your actual value):
aws glue get-job-run \
    --job-name transform-orders-job \
    --run-id [[JOB_RUN_ID="your-job-run-id-from-previous-command"]]

What should I see?

Look for "JobRunState": "SUCCEEDED" in the output. If the state is RUNNING, wait 1-2 minutes and run the command again.

  • Verify the cleaned output file exists in your processed zone by running:
aws s3 ls s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/processed/

You should see orders_cleaned.csv listed in the output.

Job status showing FAILED?

Check that your S3 bucket name in the script's default value matches your actual bucket. Also confirm the file raw/orders_raw.csv exists in your bucket (you uploaded it in Step 1).

Verify the GlueServiceRole has S3 access by checking the inline policy you attached in Step 1.

help me debug a FAILED Glue Python Shell job

Cost check

This job run costs approximately $0.003. Python Shell at 0.0625 DPU is billed per second with a 1-minute minimum. For a job that runs 1-3 minutes on 520 rows, the cost is negligible.

Your ETL pipeline is complete. Next up, you will query both the raw and processed data with Amazon Athena to prove your pipeline delivers clean, queryable results.

Query and Validate with Amazon Athena

Your ETL job transformed the raw data into a clean, reliable dataset sitting in the processed/ prefix of your S3 bucket. Now it is time to prove the pipeline works.

In this step, you will catalog the processed data so Athena can find it, then run SQL queries that confirm your data quality checks passed and that your analytics are ready for production use.

In this step, get ready to:
  • Crawl the processed/ prefix to create a new Data Catalog table.
  • Run validation queries confirming no nulls, no duplicates, and correct revenue values.
  • Run analytics queries on your cleaned data and compare results with the raw table.
Crawl the processed data to create a catalog table

Before Athena can query your cleaned data, it needs a table definition in the Glue Data Catalog that describes the schema and location of the processed files. You will create a second crawler that targets the processed/ prefix.

  • Create a new Glue Crawler targeting your processed data by running this command:
aws glue create-crawler \
  --name processed-orders-crawler \
  --role GlueServiceRole \
  --database-name datalake_db \
  --targets '{"S3Targets":[{"Path":"s3://[[DATALAKE_BUCKET="your-datalake-bucket"]]/processed/"}]}'

What does this command do?

This command mirrors the crawler you created for the raw/ prefix in Step 2. It points at the processed/ folder instead, using the same IAM role and writing table metadata into the same datalake_db database.

The crawler will detect the cleaned CSV file and infer column names, data types, and row count automatically.

  • Run the crawler by executing this command:
aws glue start-crawler --name processed-orders-crawler
  • Wait about 1-2 minutes for the crawler to finish, then check its status by running:
aws glue get-crawler --name processed-orders-crawler --query "Crawler.State"
  • You should see "READY" in the output, meaning the crawl has completed.
  • Verify the new table was created in the Data Catalog by running:
aws glue get-table --database-name datalake_db --name processed --query "Table.StorageDescriptor.Columns"
  • You should see a list of columns including order_id, customer_id, product_name, quantity, unit_price, order_date, status, and revenue.

Why does the processed table have a revenue column?

Your ETL script calculated revenue as quantity * unit_price and wrote it as a new column in the output CSV. The crawler detected this extra column and added it to the table schema.

Notice that customer_id is now typed as bigint (not string like in the raw table) because your ETL cast it to an integer.

Crawler still showing RUNNING?

The crawler needs about 2 minutes to complete on small datasets due to Glue's minimum billing period. Wait another minute and re-run the get-crawler command.

If the crawler shows STOPPING or an error, confirm your GlueServiceRole has S3 read permissions on the processed/ prefix.

Help me troubleshoot my Glue Crawler not completing successfully.

Run validation queries in Athena

Amazon Athena lets you run standard SQL queries directly against your Data Catalog tables without managing any servers. You will use it to verify that your ETL job removed all nulls, eliminated duplicates, and calculated revenue correctly.

  • Open the Athena console at https://console.aws.amazon.com/athena/.
  • If this is your first time visiting Athena, click Explore the query editor to open the query editor.
  • Click Edit Settings (or Settings then Manage in the navigation bar).
  • In the Location of query result field, enter your Athena results bucket path:
s3://[[ATHENA_BUCKET="your-athena-results-bucket"]]/
  • Click Save.
  • Click Editor to return to the query editor.
  • In the Database dropdown on the left panel, select datalake_db.

You should see both the raw and processed tables listed under your database.

Why does Athena need a results bucket?

Every query Athena runs produces output files (CSV results and metadata). Athena writes these to an S3 location you specify. This is separate from your data lake bucket to keep query outputs organized.

  • Verify no null values remain in critical columns by running this query in the Athena editor:
-- Check for null values in critical columns
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS null_order_ids,
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_ids,
  SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) AS null_quantities
FROM processed;

What does this query do?

This query counts the total rows in the processed table and checks each critical column for any remaining null values. All three null counts should be 0, proving your ETL's dropna() step worked.

  • You should see 0 for all three null columns.
  • Verify no duplicate order_ids exist by running:
-- Check for duplicate order_ids
SELECT order_id, COUNT(*) AS occurrences
FROM processed
GROUP BY order_id
HAVING COUNT(*) > 1;
  • This query should return zero rows, meaning every order_id is unique.
  • Verify the revenue column is correctly calculated by running:
-- Verify revenue = quantity * unit_price
SELECT order_id, quantity, unit_price, revenue,
  ROUND(CAST(quantity AS DOUBLE) * unit_price, 2) AS expected_revenue
FROM processed
WHERE ROUND(CAST(quantity AS DOUBLE) * unit_price, 2) != revenue
LIMIT 10;
  • This query should also return zero rows, confirming that revenue matches quantity * unit_price for every record.

Seeing an error about the table not found?

Make sure datalake_db is selected in the Database dropdown on the left. Athena only searches within the selected database.

If the processed table does not appear, confirm your crawler completed successfully by running aws glue get-crawler --name processed-orders-crawler in your terminal.

Help me troubleshoot why Athena cannot find my processed table.

Run analytics queries and compare raw vs processed data

With data quality confirmed, you can now run the same analytics queries that a data analyst would use in production. You will also compare results between the raw and processed tables to see how your ETL improved the data.

  • Run a total revenue by product query:
-- Total revenue by product
SELECT product_name,
  ROUND(SUM(revenue), 2) AS total_revenue,
  COUNT(*) AS order_count
FROM processed
GROUP BY product_name
ORDER BY total_revenue DESC;
  • You should see all 8 products listed with their total revenue and order counts.
  • Run an average order value query:
-- Average order value
SELECT ROUND(AVG(revenue), 2) AS avg_order_value,
  ROUND(MIN(revenue), 2) AS min_order_value,
  ROUND(MAX(revenue), 2) AS max_order_value
FROM processed;
  • Run a top customers by spend query:
-- Top 10 customers by total spend
SELECT customer_id,
  ROUND(SUM(revenue), 2) AS total_spend,
  COUNT(*) AS num_orders
FROM processed
GROUP BY customer_id
ORDER BY total_spend DESC
LIMIT 10;

What do these analytics queries show?

These are the kinds of queries a business analyst runs daily. Revenue by product shows which items drive the most income. Average order value is a key e-commerce metric. Top customers by spend identifies high-value accounts.

All of these queries work because your ETL job cleaned the data and added the revenue column. Without your pipeline, analysts would need to filter nulls and calculate revenue every time they write a query.

  • Compare the row counts between raw and processed tables by running:
-- Compare row counts: raw vs processed
SELECT 'raw' AS table_name, COUNT(*) AS row_count FROM raw
UNION ALL
SELECT 'processed' AS table_name, COUNT(*) AS row_count FROM processed;
  • You should see the raw table has 520 rows and the processed table has fewer (the difference is the rows removed by your null filtering and deduplication).

What does this comparison prove?

The row count difference is your ETL's data quality improvement in a single number. Your pipeline removed rows with null critical fields and eliminated duplicate order_ids. This is exactly what you would demonstrate in an interview to show your pipeline produces clean, trustworthy data.

How much did these queries cost?

Athena charges $5.00 per TB of data scanned, with a 10 MB minimum per query. Your dataset is well under 1 MB, so each query is billed at the 10 MB minimum. Running all the queries in this step costs a fraction of a cent.

Queries return unexpected results?

If the raw table shows fewer than 520 rows, your data generation script may not have run correctly. Re-check that you uploaded the complete orders_raw.csv file to the raw/ prefix.

If the processed table returns the same row count as raw, your ETL job may not have run to completion. Verify the job succeeded with aws glue get-job-run and check that processed/orders_cleaned.csv exists in your bucket.

Help me debug why my Athena query results look wrong.

Your serverless data lake pipeline is now fully operational. Raw data flows in, gets discovered by crawlers, transformed by your ETL job, and queried by analysts with standard SQL. Next up, you can take on the Secret Mission to optimize your pipeline with columnar storage.

Secret mission

Optimize with Parquet and Measure Cost Savings

Your pipeline outputs CSV, but production data lakes use columnar formats like Parquet to slash query costs. In this mission, you will modify your ETL script to output Parquet, re-catalog the data, and measure exactly how much less data Athena scans compared to CSV.

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 minimal S3 storage (under 1 MB) and no continuously running services, so ongoing costs are negligible unless you run crawlers or jobs again.

Cost warning

Idle Glue resources (jobs, crawlers, database) do not incur charges. Costs only apply when you run a crawler ($0.15 per run minimum) or a Python Shell job ($0.003 per 5-minute run). If you plan to experiment further, be aware each crawler run has a 10-minute minimum billing at 2 DPUs.

Resources you used:

  • 2 S3 buckets (your-datalake-bucket and your-athena-results-bucket).
  • 1 IAM role (GlueServiceRole) with attached managed and inline policies.
  • 1 Glue database (datalake_db) with 3 tables (raw, processed, processed_parquet).
  • 3 Glue Crawlers (raw-orders-crawler, processed-orders-crawler, processed-parquet-crawler).
  • 2 Glue Python Shell jobs (transform-orders-job, transform-orders-parquet-job).
  • Local project files (generate_data.py, transform_orders.py, transform_orders_parquet.py, orders_raw.csv).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with your data lake pipeline.

  • Your crawlers, jobs, and database remain available for future use.
  • You will only be charged if you manually run crawlers or jobs again.
  • S3 storage for this project is well under 1 MB.
  • Local Python files remain on your Desktop.

Pause - I'll come back to this later

Leave all resources in place but do not run any jobs or crawlers. No charges accrue for idle Glue resources or small S3 storage.

  • Do not run any crawlers or Glue jobs until you are ready to resume.
  • Your Data Catalog tables, S3 data, and IAM role will all remain intact.
  • When you return, you can pick up where you left off without recreating anything.

Delete - I don't want to use this again

Remove all project resources from your AWS account and local machine. Run these commands in your terminal.

  • Delete the Glue jobs by running:
aws glue delete-job --job-name transform-orders-job
aws glue delete-job --job-name transform-orders-parquet-job
  • Delete the Glue Crawlers by running:
aws glue delete-crawler --name raw-orders-crawler
aws glue delete-crawler --name processed-orders-crawler
aws glue delete-crawler --name processed-parquet-crawler
  • Delete the Data Catalog tables by running:
aws glue delete-table --database-name datalake_db --name raw
aws glue delete-table --database-name datalake_db --name processed
aws glue delete-table --database-name datalake_db --name processed_parquet
  • Delete the Glue database by running:
aws glue delete-database --name datalake_db
  • Delete the IAM role. You must detach policies before deleting the role:
aws iam detach-role-policy --role-name GlueServiceRole --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
aws iam delete-role-policy --role-name GlueServiceRole --policy-name S3AccessPolicy
aws iam delete-role --role-name GlueServiceRole

Why three commands for the IAM role?

AWS requires you to remove all attached policies before deleting a role. The first command detaches the managed policy. The second deletes the inline S3 access policy. Only then can the third command delete the role itself.

  • Delete both S3 buckets by running:
aws s3 rb s3://[[DATALAKE_BUCKET="your-datalake-bucket"]] --force
aws s3 rb s3://[[ATHENA_BUCKET="your-athena-results-bucket"]] --force

What does --force do?

The --force flag empties the bucket (deletes all objects) before removing the bucket itself. Without it, the command fails on non-empty buckets.

  • Delete local project files by running:
cd ~/Desktop
rm -rf generate_data.py transform_orders.py transform_orders_parquet.py orders_raw.csv

Nice Work!

Nice Work!

Nice work! You just built a complete serverless data lake pipeline on AWS that ingests raw data, discovers schemas automatically, transforms with quality checks, and delivers clean analytics-ready output.

You've learned how to:

  • Build a serverless data lake on AWS with raw and processed storage zones in S3, using Glue Crawlers to automatically discover schemas and populate the AWS Glue Data Catalog.
  • Write a Python Shell ETL job in AWS Glue that performs data quality checks (null removal, deduplication) and computes derived columns like revenue.
  • Query both raw and processed data with Amazon Athena using standard SQL, validating pipeline correctness by comparing results across zones.
  • Secret Mission: Converted processed data to Parquet format and measured the reduction in Athena data scanned compared to CSV.

Ready to quiz yourself?