CSV Data Pipeline on AWS

Build a serverless pipeline that auto-cleans and visualizes CSV data.

Introduction

30 Second Summary

Every business generates messy data. Spreadsheets full of duplicate rows, inconsistent dates, and random whitespace get passed around before anyone can run reports on them.

In this project, you will build a serverless data pipeline on AWS that automatically cleans, enriches, and visualizes CSV data the moment it lands in storage.

What You'll Build

Drop a messy sales CSV into an S3 bucket and watch it flow through three automated stages: Lambda cleans it, AWS Glue catalogs and enriches it, and QuickSight displays it as an interactive dashboard you can share.

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

  • Upload a CSV full of duplicates, inconsistent dates, and whitespace, then watch it get cleaned automatically within seconds.
  • See AWS Glue infer your data's schema and add a calculated total_amount column through a visual ETL job.
  • View a live QuickSight dashboard showing sales broken down by product category.
  • Secret Mission: Add a time-series visualization and an interactive filter to your dashboard.

Are there any prerequisites?

You'll need an AWS account with the AWS CLI installed and configured. Basic familiarity with Python helps, but you don't need to be an expert.

Before We Start

Before We Start...

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

Set Up S3 Buckets and IAM Role

Before your pipeline can process any data, it needs dedicated storage for each stage of quality.

In this step, you'll create three separate S3 buckets (raw, processed, transformed) and an IAM role that gives Lambda permission to move data between them. You'll also upload a sample CSV file loaded with messy data to test later.

In this step, get ready to:
  • Create three S3 buckets for raw, processed, and transformed data.
  • Create an IAM role with permissions for Lambda to access all three buckets.
  • Upload a sample CSV with data quality issues to the raw bucket.
Create three S3 buckets

S3 bucket names must be globally unique across all AWS accounts. A good pattern is to prefix each name with your initials and a descriptor like jd-raw-bucket.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a project folder and move into it by running these commands:
mkdir csv-pipeline
cd csv-pipeline
  • Create your three S3 buckets by running these commands:
aws s3 mb s3://[[RAW_BUCKET="jd-raw-bucket"]]
aws s3 mb s3://[[PROCESSED_BUCKET="jd-processed-bucket"]]
aws s3 mb s3://[[TRANSFORMED_BUCKET="jd-transformed-bucket"]]

What do these commands do?

Each aws s3 mb command creates ("makes") a new bucket. The three buckets represent your pipeline's stages:

  • Raw holds the original, messy data as it arrives.
  • Processed holds data after Lambda cleans it.
  • Transformed holds the final enriched data ready for analytics.

You should see make_bucket confirmation messages for each bucket.

Seeing a BucketAlreadyExists error?

S3 bucket names are globally unique. If someone else already has the name you chose, pick a more specific name by adding numbers or extra initials (e.g. jd2025-raw-bucket).

If you see a credentials error, confirm your AWS CLI is configured by running aws sts get-caller-identity.

Help me fix my S3 bucket creation error.

Create the Lambda execution role

Lambda needs an IAM role to run and access your S3 buckets. You'll create a role with a trust policy that allows Lambda to assume it, then attach permissions.

  • Create the trust policy file by running this command:
cat > trust-policy.json << 'EOF'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
EOF

What is a trust policy?

A trust policy defines which AWS service is allowed to "assume" this role. By setting the principal to lambda.amazonaws.com, you're saying only Lambda functions can use this role's permissions.

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

You should see JSON output showing your new role's details, including its ARN.

  • Attach the managed logging policy by running this command:
aws iam attach-role-policy --role-name LambdaS3PipelineRole --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

What does AWSLambdaBasicExecutionRole do?

This managed policy gives Lambda permission to write logs to CloudWatch. Without it, your function would run but you'd never see any log output for debugging.

  • Create the S3 access policy file by running this command:
cat > s3-policy.json << 'EOF'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::[[RAW_BUCKET="jd-raw-bucket"]]",
        "arn:aws:s3:::[[RAW_BUCKET="jd-raw-bucket"]]/*",
        "arn:aws:s3:::[[PROCESSED_BUCKET="jd-processed-bucket"]]",
        "arn:aws:s3:::[[PROCESSED_BUCKET="jd-processed-bucket"]]/*",
        "arn:aws:s3:::[[TRANSFORMED_BUCKET="jd-transformed-bucket"]]",
        "arn:aws:s3:::[[TRANSFORMED_BUCKET="jd-transformed-bucket"]]/*"
      ]
    }
  ]
}
EOF

Why six Resource entries?

Each bucket needs two ARN patterns. The bare ARN (arn:aws:s3:::bucket-name) grants s3:ListBucket at the bucket level. The wildcard ARN (arn:aws:s3:::bucket-name/*) grants s3:GetObject and s3:PutObject on objects inside the bucket.

  • Attach the inline S3 policy to your role by running this command:
aws iam put-role-policy --role-name LambdaS3PipelineRole --policy-name S3AccessPolicy --policy-document file://s3-policy.json
  • Confirm the inline policy is attached by running:
aws iam list-role-policies --role-name LambdaS3PipelineRole

You should see S3AccessPolicy listed in the output.

Seeing NoSuchEntity or AccessDenied?

If you see NoSuchEntity, the role name might be mistyped. Double check you used LambdaS3PipelineRole exactly (case-sensitive) in all commands.

If you see AccessDenied, your AWS CLI user may lack IAM permissions. Confirm your user has the IAMFullAccess policy or equivalent attached in the AWS Console.

Help me fix my IAM role creation error.

Create and upload the sample CSV

Your pipeline needs messy data to clean. This sample CSV has intentional quality issues: duplicate rows, inconsistent date formats, empty fields, and extra whitespace.

  • Create the sample data file by running this command:
cat > sample_sales_data.csv << 'EOF'
order_id,date,product,category,quantity,price,customer_region
1,2024-01-15,Widget A,Electronics,2,29.99,Northeast
2,01/16/2024,Gadget B,Electronics,1,49.99,West
3,2024-01-17, Mega Speaker ,Audio,3,89.99,Southeast
4,2024-01-18,USB Cable,Electronics,5,9.99,West
,2024-01-19,Mystery Item,Unknown,1,19.99,Northeast
5,2024-01-20,Bluetooth Headphones,Audio,2,59.99,Midwest
6,15-01-2024,Smart Watch,Electronics,1,199.99,Northeast
7,2024/01/22,Desk Lamp,Home,4,34.99,Southeast
3,2024-01-17,Mega Speaker,Audio,3,89.99,Southeast
8,2024-01-23,Phone Case,Accessories,10,14.99,West
9,2024-01-24,Wireless Mouse,Electronics,3,24.99,Midwest
10,,Keyboard,Electronics,2,44.99,Northeast
11,2024-01-26,  Monitor Stand  ,Home,1,79.99,Southeast
12,2024-01-27,HDMI Cable,Electronics,6,12.99,West
13,2024-01-28,Webcam,Electronics,2,69.99,Midwest
14,2024-01-29,Power Bank,Electronics,4,39.99,Northeast
15,2024-01-30,Notebook,Office,12,4.99,Southeast
EOF

What's wrong with this data?

This CSV has four types of problems your Lambda function will need to fix:

  • Inconsistent dates: 2024-01-15, 01/16/2024, 15-01-2024, 2024/01/22 are all different formats.
  • Duplicate rows: order_id 3 appears twice.
  • Empty fields: row 5 has no order_id, row 10 has no date.
  • Extra whitespace: Mega Speaker and Monitor Stand have leading/trailing spaces.
  • Upload the CSV to your raw bucket by running this command:
aws s3 cp sample_sales_data.csv s3://[[RAW_BUCKET="jd-raw-bucket"]]/sample_sales_data.csv

You should see an upload: confirmation message.

  • Confirm the file is in your raw bucket by running:
aws s3 ls s3://[[RAW_BUCKET="jd-raw-bucket"]]/

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

Before you run the final check, what do you expect to see when you list all your S3 buckets?

  • Verify all three buckets exist by running:
aws s3 ls

You should see all three of your buckets listed (raw, processed, and transformed) along with their creation dates.

Missing a bucket in the output?

If you see fewer than three buckets, one of the earlier aws s3 mb commands may have failed silently. Scroll up in your terminal to check for error messages, then re-run the failed command with a unique name.

If the list includes buckets from other projects, that's normal. Look specifically for the three bucket names you chose.

Help me verify my S3 buckets are set up correctly.

Your storage is ready and your Lambda role has permission to read and write across all three stages. Next up, you'll write the Lambda function that actually cleans the messy CSV data.

Build and Test the Lambda Function

In Step 1, you set up the foundation: three S3 buckets and an IAM role that ties them together. Now it is time to write the brains of your pipeline.

Your raw CSV has real-world data problems: duplicated rows, dates in four different formats, whitespace cluttering field values, and rows missing critical data. You need a function that reads the messy file, cleans it, and writes a polished version to your processed bucket. In this step, you will build that function in AWS Lambda and test it manually using a simulated S3 event.

In this step, get ready to:
  • Create a Lambda function with Python 3.13 and deploy your cleaning code.
  • Configure a test event that simulates an S3 file upload.
  • Verify the cleaned CSV appears in the processed bucket with fewer rows.
Create the Lambda function

You will create a new function in the Lambda console and assign it the IAM role from Step 1. This role gives the function permission to read from your raw bucket and write to your processed bucket.

  • In the AWS Console, search for Lambda in the top search bar and select it.
  • Click Create function.
  • Select Author from scratch.
  • For Function name, enter CSVCleaningFunction.
  • For Runtime, choose Python 3.13.
  • Expand Change default execution role.
  • Select Use an existing role.
  • From the Existing role dropdown, choose LambdaS3PipelineRole.
  • Click Create function.

Lambda creates the function and opens the code editor with a default lambda_function.py file.

Why Python 3.13?

The python3.13 runtime includes boto3 (the AWS SDK for Python) pre-installed. Your cleaning code uses only standard library modules plus boto3, so no external packages or Lambda layers are needed.

Deploy the data-cleaning code

The default code in the editor is a placeholder. You will replace it with your CSV-cleaning logic, split across four sections.

  • In the Code tab, select all the existing code in lambda_function.py and delete it.
  • Paste the following imports and configuration at the top of the file:
import json
import csv
import boto3
import io
from datetime import datetime

# Create an S3 client to read and write files
s3_client = boto3.client('s3')

# Destination bucket for cleaned output
PROCESSED_BUCKET = '[[PROCESSED_BUCKET_NAME="your-processed-bucket-name"]]'

What does this code do?

  • The imports bring in standard library modules for JSON, CSV parsing, and date handling.
  • boto3.client('s3') creates an S3 client your function uses to download and upload files.
  • PROCESSED_BUCKET tells the function where to write the cleaned output. Replace the placeholder with your actual processed bucket name from Step 1.
  • Below the configuration, paste the handler function that reads the source file from S3:

def lambda_handler(event, context):
    # Extract bucket and file info from the S3 event
    source_bucket = event['Records'][0]['s3']['bucket']['name']
    source_key = event['Records'][0]['s3']['object']['key']

    # Read the CSV file from S3
    response = s3_client.get_object(Bucket=source_bucket, Key=source_key)
    content = response['Body'].read().decode('utf-8')

    # Set up the CSV reader and tracking variables
    reader = csv.DictReader(io.StringIO(content))
    cleaned_rows = []
    seen = set()

What does this code do?

  • lambda_handler is the entry point Lambda calls when the function is invoked.
  • It reads the bucket name and object key from the incoming S3 event payload.
  • get_object downloads the CSV content into memory.
  • cleaned_rows collects rows that pass all quality checks. seen tracks order IDs to catch duplicates.
  • Next, paste the cleaning loop that processes each row:
    for row in reader:
        # Strip whitespace from all fields
        cleaned_row = {key.strip(): value.strip() for key, value in row.items()}

        # Skip rows missing order_id or date
        if not cleaned_row.get('order_id') or not cleaned_row.get('date'):
            continue

        try:
            date_value = cleaned_row['date']
            parsed_date = None
            # Try each date format until one works
            for fmt in ('%Y-%m-%d', '%m/%d/%Y', '%d-%m-%Y', '%Y/%m/%d'):
                try:
                    parsed_date = datetime.strptime(date_value, fmt)
                    break
                except ValueError:
                    continue
            if parsed_date is None:
                continue
            cleaned_row['date'] = parsed_date.strftime('%Y-%m-%d')
        except Exception:
            continue

        # Skip duplicate order IDs
        if cleaned_row['order_id'] in seen:
            continue
        seen.add(cleaned_row['order_id'])

        cleaned_rows.append(cleaned_row)

What does this cleaning loop do?

Each row goes through four quality gates before it is accepted:

  • Whitespace stripping removes leading and trailing spaces from every field (fixing entries like Monitor Stand ).
  • Required-field check drops rows where order_id or date is empty.
  • Date normalization tries four common formats and converts them all to YYYY-MM-DD. Rows with unparseable dates are dropped.
  • Deduplication skips any row whose order_id was already seen.
  • Finally, paste the output section that writes the cleaned CSV and returns a response:
    # Build the output filename
    output_key = f"cleaned_{source_key.split('/')[-1]}"

    if cleaned_rows:
        # Write cleaned data to a new CSV in memory
        output = io.StringIO()
        writer = csv.DictWriter(output, fieldnames=cleaned_rows[0].keys())
        writer.writeheader()
        writer.writerows(cleaned_rows)

        # Upload the cleaned CSV to the processed bucket
        s3_client.put_object(
            Bucket=PROCESSED_BUCKET,
            Key=output_key,
            Body=output.getvalue().encode('utf-8'),
            ContentType='text/csv'
        )

    return {
        'statusCode': 200,
        'body': json.dumps({
            'message': 'CSV cleaned successfully',
            'rows_processed': len(cleaned_rows),
            'output_bucket': PROCESSED_BUCKET,
            'output_key': output_key if cleaned_rows else None
        })
    }

What does this output section do?

  • output_key prefixes the original filename with cleaned_ so you can easily identify it.
  • csv.DictWriter rebuilds the CSV in memory with only the rows that passed all checks.
  • put_object uploads the cleaned file to your processed bucket.
  • The return body includes a summary (rows processed, output location) useful for debugging.
  • Click Deploy in the code editor toolbar to save your changes.

You should see a green banner confirming the function was updated successfully.

✔️ Awesome, I've got everything!

Great. Confirm you clicked Deploy and saw the success banner.

ⓧ I'd like to double check the full code

Scroll up through your code editor and confirm each section is present in this order:

  • Imports and PROCESSED_BUCKET configuration (your actual bucket name, not the placeholder).
  • lambda_handler function definition with S3 event parsing and CSV reader setup.
  • The for row in reader loop with whitespace stripping, required-field check, date normalization, and deduplication.
  • The output section with put_object and the return statement.

Seeing an error after clicking Deploy?

  • Check for inconsistent indentation. Python requires consistent spacing. Every line inside lambda_handler must be indented with four spaces.
  • Make sure you replaced the PROCESSED_BUCKET placeholder with your actual bucket name from Step 1 (in quotes).
  • Confirm no leftover code from the default template remains at the top of the file.

Help me debug my Lambda function deploy error.

Configure a test event and invoke the function

Your function is deployed, but you need a way to simulate an S3 file upload without actually configuring a trigger yet. Lambda's test event feature lets you pass a fake S3 event to your function manually.

  • Click the Test tab at the top of your function page.
  • Under Test event action, select Create new event.
  • For Event name, enter TestS3Upload.
  • For Template, select S3 Put.

The template populates a JSON event structure. You need to update three placeholder values so the event points to your real raw bucket and file.

  • In the event JSON, find the awsRegion field and replace us-east-1 with the region your buckets are in (if different).
  • Find "name": "amzn-s3-demo-bucket" under the bucket section and replace amzn-s3-demo-bucket with your raw bucket name.
  • Find "key": "test%2Fkey" under the object section and replace test%2Fkey with sample_sales_data.csv.
  • Click Save.

What is this test event simulating?

In production, S3 sends this exact JSON structure to your Lambda whenever a file is uploaded. The event tells your function which bucket and which file triggered it. By creating a test event manually, you can invoke the function without wiring up the actual S3 trigger first.

Before you run the test, predict what you expect to happen. Your raw CSV has 16 data rows with intentional problems. How many rows do you think will survive the cleaning?

  • Click Test to invoke your function.

Under Execution result, expand Details. You should see a response body showing "message": "CSV cleaned successfully" and a rows_processed count lower than 16.

Function returned an error?

  • If you see an AccessDenied error, the LambdaS3PipelineRole may not have the right S3 permissions. Return to IAM and confirm the inline policy grants s3:GetObject on your raw bucket.
  • If you see a NoSuchKey error, the object key in your test event does not match the file in your raw bucket. Confirm it says sample_sales_data.csv exactly.
  • If you see a NoSuchBucket error, check that the bucket name in your test event matches your raw bucket name character for character.

Help me fix my Lambda test event error.

Verify the cleaned output in S3

A successful test means your function wrote a cleaned file to the processed bucket. Let's confirm the output is there and inspect the results.

  • In the AWS Console, navigate to S3.
  • Open your processed bucket.
  • You should see a file named cleaned_sample_sales_data.csv.
  • Select the file and click Download.
  • Open the downloaded file in a text editor or spreadsheet application.

Compare the cleaned file against the original. You should see that:

  • The file has fewer rows than the original 16 (rows with empty order_id, empty date, and the duplicate order_id 3 are gone).
  • All dates are in YYYY-MM-DD format (no more 01/16/2024 or 15-01-2024 variations).
  • Product names are trimmed (Mega Speaker instead of Mega Speaker ).

What rows were removed?

Three categories of rows fail the quality checks:

  • Row 5 (the Mystery Item) has an empty order_id.
  • Row 10 (Keyboard) has an empty date.
  • The second occurrence of order_id 3 (Mega Speaker duplicate) is dropped by deduplication.

Your Lambda function can clean messy CSV data on demand. Next up, you will wire it to an S3 trigger so the cleaning happens automatically the moment a file lands in the raw bucket.

Automate with an S3 Trigger

In the last step, you proved that your Lambda function can clean messy CSV data. But you had to manually click Test every time you wanted it to run.

In a real data pipeline, files arrive at unpredictable times. You need the cleaning to happen automatically the moment new data lands. In this step, you will connect an S3 event trigger to your Lambda function so it fires on its own whenever a file is uploaded to your raw bucket.

In this step, get ready to:
  • Add an S3 event trigger to your Lambda function.
  • Upload a new test CSV and confirm the pipeline fires automatically.
Add an S3 trigger to the Lambda function

An S3 event notification tells AWS to invoke your function whenever a specific action occurs in a bucket. You will configure the raw bucket to send a notification every time a new object is created.

  • Return to the Lambda console and select your CSVCleaningFunction.
  • In the Function overview section at the top, click Add trigger.
  • In the trigger configuration dropdown, select S3.
  • Under Bucket, choose your raw bucket (the one containing sample_sales_data.csv).
  • For Event type, select All object create events.
  • You will see a recursive invocation warning checkbox at the bottom. Check the box to acknowledge it, then click Add.

What is the recursive invocation warning?

If your Lambda wrote its output back to the same bucket that triggers it, each write would trigger another invocation, creating an infinite loop. AWS shows this warning to make sure you understand the risk.

Your pipeline is safe because you use separate buckets. The raw bucket triggers Lambda, but the output goes to the processed bucket. No loop is possible.

  • After clicking Add, confirm the trigger appears in the Function overview diagram. You should see an S3 icon connected to your function.

Don't see the S3 trigger in the diagram?

Make sure you selected the correct bucket (the raw bucket, not the processed or transformed bucket). If the trigger failed to add, check that the Lambda execution role has s3:GetObject permission on the raw bucket.

Help me troubleshoot why my S3 trigger is not appearing on my Lambda function.

Upload a new CSV and verify automatic processing

The trigger is live. Now you will upload a fresh CSV file to prove the pipeline runs hands-free, without you clicking Test.

  • Create a small test CSV file called test_sales_data.csv on your local machine with the following content:
order_id,date,product,category,quantity,price,customer_region
101,2024-02-01,Tablet Stand,Home,2,24.99,Northeast
102,02/03/2024,Screen Protector,Accessories,5,8.99,West
103,2024-02-05, Charging Cable ,Electronics,3,12.99,Midwest
101,2024-02-01,Tablet Stand,Home,2,24.99,Northeast
,2024-02-07,Unknown Item,Misc,1,5.99,Southeast
104,2024-02-08,Laptop Sleeve,Accessories,1,29.99,West

What is wrong with this test data?

  • Order 101 is duplicated (appears twice).
  • Order 102 uses the MM/DD/YYYY date format instead of YYYY-MM-DD.
  • Order 103 has leading and trailing whitespace in the product name.
  • One row has an empty order_id, which should be filtered out.
  • After cleaning, you should end up with 4 valid rows (orders 101, 102, 103, 104).
  • Upload the file to your raw bucket by running this command:
aws s3 cp test_sales_data.csv s3://[[RAW_BUCKET="your-raw-bucket-name"]]/test_sales_data.csv

Before you check the processed bucket, predict what you expect to happen. Do you think a cleaned file will appear automatically without you clicking Test in the Lambda console?

  • Wait about 10 seconds, then check your processed bucket by running:
aws s3 ls s3://[[PROCESSED_BUCKET="your-processed-bucket-name"]]/

You should see two files listed: cleaned_sample_sales_data.csv (from your earlier manual test) and cleaned_test_sales_data.csv (from the automatic trigger). The second file appeared without you doing anything in the Lambda console.

Only seeing one file in the processed bucket?

Wait 15-30 seconds and run the aws s3 ls command again. Lambda can take a few seconds to pick up the event. If the file still does not appear, check that your trigger is configured on the correct raw bucket and that the event type is set to All object create events.

Help me figure out why my S3 trigger is not automatically invoking my Lambda function.

  • Now confirm the invocation in CloudWatch Logs. In the Lambda console, select your CSVCleaningFunction and click the Monitor tab.
  • Click View CloudWatch logs to open the CloudWatch console.
  • Select the most recent log stream. You should see a REPORT line showing the function was invoked, along with its duration and memory used.

Why check CloudWatch Logs?

CloudWatch Logs records every Lambda invocation automatically. The log stream proves your function was triggered by the S3 event (not by you clicking Test). This is how you would monitor a production pipeline for errors or performance issues.

Your pipeline now runs completely hands-free. Any CSV that lands in the raw bucket gets cleaned within seconds. Next up, you will use AWS Glue to catalog the cleaned data and enrich it with a calculated column.

Catalog and Transform with AWS Glue

Your pipeline now fires automatically whenever a CSV lands in the raw bucket. The processed bucket holds cleanly formatted data. But those CSV files are flat text with no structural metadata attached.

AWS Glue solves this by crawling your data to infer its schema, then transforming it with calculated fields. In this step, you'll use AWS Glue to catalog your cleaned data and enrich it with a total_amount column.

In this step, get ready to:
  • Create a Glue service role, database, and crawler that discovers your data's structure.
  • Build a visual ETL job that adds a calculated total_amount column.
  • Run the job and confirm enriched output files in your transformed bucket.
Create the Glue service role, database, and crawler

Glue needs its own IAM role to access your S3 buckets and run crawlers. You'll create this role first, then set up the database and crawler in the Glue console.

  • Search for IAM in the AWS Console search bar and open the IAM console.
  • Click Roles in the left sidebar.
  • Click Create role.
  • Select AWS service as the trusted entity type.
  • Under Use case, search for and select Glue.
  • Click Next.
  • Search for AWSGlueServiceRole and check the box to select it.
  • Search for AmazonS3FullAccess and check the box to select it.

Why two policies?

AWSGlueServiceRole grants Glue permission to manage Data Catalog resources, write logs, and access other Glue components. AmazonS3FullAccess lets Glue read from your processed bucket and write to your transformed bucket.

In production, you would scope S3 access to specific buckets. For learning purposes, full access simplifies the setup.

  • Click Next.
  • Enter GlueServiceRole as the role name.
  • Click Create role.

You should see a success banner confirming the role was created.

Now navigate to the Glue console to create your database and crawler.

  • Search for Glue in the AWS Console search bar and open the AWS Glue console.
  • In the left navigation pane, under Data catalog, click Databases.
  • Click Add database.
  • Enter sales_pipeline_db as the database name.
  • Click Create database.

Your new database appears in the Databases list. This database will hold the metadata tables that the crawler creates.

  • In the left navigation pane, click Crawlers.
  • Click Create crawler.
  • Enter processed-data-crawler as the crawler name.
  • Click Next.
  • For the data source, choose S3 and enter the path to your processed bucket (e.g. s3://your-processed-bucket-name/).
  • Click Next.
  • For IAM role, select the GlueServiceRole you just created.
  • Click Next.
  • For the target database, select sales_pipeline_db from the dropdown.
  • For Frequency, choose On demand.
  • Click Next.
  • Review the crawler settings, then click Create crawler.

The crawler is ready to run. It will scan your processed bucket and infer the CSV schema automatically.

  • Select the processed-data-crawler checkbox.
  • Click Run.

What does the crawler do?

The crawler reads your CSV files in the processed bucket, identifies columns and data types, and writes that schema information into the Data Catalog. This metadata is what Glue uses to read your data in ETL jobs.

The crawler typically takes 1-2 minutes to complete for small datasets.

  • Wait for the crawler status to show Completed (refresh the page if needed).
  • In the left navigation pane, under Data catalog, click Tables.
  • Click on the table name that the crawler created.
  • Scroll down to the Schema section to view the inferred columns.

You should see columns for order_id, date, product, category, quantity, price, and customer_region. Glue discovered the full structure of your CSV without you defining anything manually.

Don't see a table after the crawler ran?

  • Make sure the crawler status shows Completed and not Running. Refresh the Crawlers page to check.
  • Confirm the S3 path you entered during crawler setup points to a bucket that contains CSV files (your processed bucket with the cleaned output).
  • Check that the GlueServiceRole has the AmazonS3FullAccess policy attached. Without it, the crawler cannot read from S3.

Still stuck? Help me troubleshoot why my Glue crawler didn't create a table

How much does this cost?

The Glue Data Catalog is free for up to 1 million objects. The crawler charges $0.44 per DPU-hour with a 10-minute minimum. For this small CSV, expect the crawler to cost around $0.07-0.15.

Create a visual ETL job with a Derived Column transform

Now that Glue knows the data schema, you can create an ETL job that reads from the Data Catalog, adds a calculated column, and writes enriched data to your transformed bucket.

  • In the left navigation pane, under Data Integration and ETL, click ETL jobs.
  • In the Create job section, select Visual ETL.

The visual editor opens with a blank canvas. You'll add three nodes: a data source, a transform, and a data target.

  • On the visual canvas, click the source node that appears by default (or click the + icon to open the Resource panel).
  • Under the Data source properties tab on the right panel, select Data Catalog as the source type.
  • For Database, select sales_pipeline_db.
  • For Table, select the table that the crawler created.

What is the source node?

The source node tells Glue where to read data from. By pointing at the Data Catalog table, Glue automatically knows the S3 location, file format, and column schema of your processed CSV.

  • With the source node selected, click the + icon to open the Resource panel.
  • In the Transforms tab, search for and select Derived Column.
  • In the Transform tab on the right panel, enter total_amount as the new column name.
  • For the expression, enter quantity * price.

What does the Derived Column transform do?

The Derived Column transform creates a new column based on a math formula or SQL expression. Here, quantity * price multiplies the two existing columns to calculate the total sale amount for each order.

  • With the Derived Column node selected, click the + icon to open the Resource panel again.
  • In the Data tab, under Targets, select Amazon S3.
  • In the Data target properties tab on the right panel, set the format to CSV.
  • For S3 Target Location, browse to and select your transformed bucket.

Your visual canvas now shows three connected nodes: source, transform, and target.

  • Click the Job details tab at the top of the editor.
  • Enter a name for your job (e.g. sales-enrichment-job).
  • For IAM Role, select GlueServiceRole.
  • Leave all other settings at their defaults.
  • Click Save in the top right corner.
  • Click Run.

How long does the ETL job take?

A Glue ETL job spins up a Spark cluster, processes your data, and writes the output. For this small dataset, expect the job to take 2-5 minutes. The job charges $0.44 per DPU-hour with a 1-minute minimum.

  • Click the Runs tab to monitor the job status.
  • Wait for the run status to show Succeeded.

Job failed or stuck in Running?

  • Check that the GlueServiceRole is assigned in the Job details tab. Without a role, the job cannot start.
  • Confirm your Data Catalog table points to a bucket with actual CSV files. If the processed bucket is empty, the job has nothing to read.
  • Check the error message in the Runs tab. Click on the run ID to see detailed error logs.

Need more help? Help me troubleshoot my failing Glue ETL job

Inspect the transformed output

The ETL job has written enriched CSV files to your transformed bucket. Before you move on, confirm the output exists and contains the new total_amount column.

Before you check, what do you expect to find in the transformed bucket? Think about what the Derived Column transform added to each row.

  • Search for S3 in the AWS Console search bar and open the S3 console.
  • Click on your transformed bucket.
  • Navigate into the output folder that Glue created.

You should see one or more CSV files. Glue may output multiple part files depending on how it partitions the data.

  • Click on one of the CSV files.
  • Click Download to download the file to your computer.
  • Open the downloaded file in a text editor or spreadsheet application.

You should see all the original columns (order_id, date, product, category, quantity, price, customer_region) plus the new total_amount column. Each row's total_amount equals the product of quantity and price.

What just happened across all three stages?

Your pipeline now spans three stages. Raw data lands in the first bucket. Lambda cleans it and writes to the second. Glue reads from the second, infers the schema, adds total_amount, and writes the enriched output to the third.

Each stage has progressively higher data quality. This separation is a core pattern in data engineering called a data lake architecture.

Transformed bucket is empty?

  • Go back to the Glue ETL job Runs tab and confirm the status is Succeeded. A failed or running job does not write output.
  • Check that the S3 Target Location in the job's target node points to your transformed bucket (not the processed bucket).
  • Look inside subfolders. Glue may write output into a path like run-xxxxx/ inside the bucket.

Still stuck? Help me find my Glue ETL job output in S3

Your data is now cataloged, enriched, and ready for analytics. In the next step, you'll connect it to Amazon QuickSight to build a live dashboard that visualizes sales by category.

Visualize with Amazon QuickSight

Your transformed data is sitting in S3 with a clean schema and a calculated total_amount column. It is analytics-ready. But staring at raw CSV rows does not reveal which product categories drive the most revenue.

Amazon QuickSight connects directly to your S3 data and turns it into interactive charts. In this step, you will sign up for the free trial, point QuickSight at your transformed bucket, and publish a live dashboard showing sales broken down by product category.

In this step, get ready to:
  • Sign up for the QuickSight free trial and grant it access to your transformed bucket.
  • Create a manifest file and use it to build a QuickSight dataset.
  • Build a bar chart analysis and publish it as a dashboard.
Sign up for QuickSight and configure S3 access

QuickSight is a separate service inside your AWS account that needs its own sign-up. It offers a 30-day free trial for up to 25 users.

  • Return to the AWS Management Console.
  • In the search bar at the top, type QuickSight and select the QuickSight service.
  • You will see a sign-up page. Enter a unique account name (e.g. your name or initials) in the QuickSight account name field.
  • Enter your email address in the Notification email address field.
  • Choose the same AWS Region you have been using for your S3 buckets as the SPICE storage region.
  • For authentication method, select the recommended Password-based option.
  • Click Create account.
  • Wait for account creation to complete, then click Go to Quick to open the QuickSight console.

QuickSight charges after 30 days

The free trial lasts 30 days. After that, QuickSight Standard Edition costs $9/user/month. Make sure to cancel before the trial ends if you do not want to be charged. The Before You Go section of this project shows you exactly how.

You should now see the QuickSight home page. Next, grant QuickSight permission to read from your transformed S3 bucket.

  • Click your account name icon in the top-right corner of the QuickSight console.
  • Select Manage QuickSight.
  • In the left sidebar, click Security and Permissions.
  • Click Manage next to the S3 access section.
  • Select your transformed S3 bucket from the list, then confirm your choice.

Why does QuickSight need S3 access?

QuickSight runs in its own service boundary within AWS. Even though the S3 buckets live in the same account, QuickSight cannot read them unless you explicitly grant permission through this Security and Permissions panel.

Don't see your bucket in the list?

Make sure you are in the same AWS Region as your S3 buckets. Check the region selector in the top-right corner of the QuickSight console.

help me fix QuickSight S3 bucket permissions

Create the manifest and QuickSight dataset

QuickSight reads S3 data through a manifest file. This small JSON file tells QuickSight where to find your CSV files and how to parse them.

  • On your local machine, create a file called manifest.json with the following content:
{
    "fileLocations": [
        {
            "URIPrefixes": [
                "s3://[[TRANSFORMED_BUCKET=\"your-transformed-bucket-name\"]]/"
            ]
        }
    ],
    "globalUploadSettings": {
        "format": "CSV",
        "delimiter": ",",
        "textqualifier": "\"",
        "containsHeader": "true"
    }
}

What does this manifest do?

The URIPrefixes field tells QuickSight to import every CSV file found at that S3 path. The globalUploadSettings section describes the file format so QuickSight can parse columns correctly.

  • Replace the bucket name placeholder with your actual transformed bucket name.
  • Save the file.
  • Upload manifest.json to your transformed bucket by running this command:
aws s3 cp manifest.json s3://[[TRANSFORMED_BUCKET="your-transformed-bucket-name"]]/manifest.json
  • Confirm the upload succeeded by listing the bucket contents:
aws s3 ls s3://[[TRANSFORMED_BUCKET="your-transformed-bucket-name"]]/

You should see manifest.json listed alongside the Glue ETL output files.

Upload failed or file not showing?

Double-check that you replaced the placeholder with your actual bucket name. Make sure your AWS CLI is configured for the same region as your buckets.

help me fix my S3 upload command

Now create a QuickSight dataset that reads from this manifest.

  • Return to the QuickSight console home page.
  • Click Datasets in the left navigation.
  • Click New dataset in the top-right corner.
  • Select the S3 icon from the list of data sources.
  • Enter a data source name such as sales-pipeline-data.
  • For the manifest file, select the URL option and enter the S3 URI of your manifest:
s3://[[TRANSFORMED_BUCKET="your-transformed-bucket-name"]]/manifest.json
  • Click Connect.
  • On the next screen, click Visualize to import the data into SPICE and go straight into an analysis.

What is SPICE?

SPICE is QuickSight's in-memory calculation engine. It imports your data from S3 into a fast cache so that charts render instantly without querying S3 each time.

QuickSight opens a new analysis with your dataset loaded. You should see your column names (order_id, date, product, category, quantity, price, customer_region, total_amount) in the Fields list pane on the left.

Seeing zero rows or missing columns?

Open your manifest.json and confirm the URIPrefixes path ends with a trailing slash. Also verify that your Glue ETL output files include a header row by downloading one from the transformed bucket and checking its first line.

help me debug my QuickSight S3 dataset

Build a bar chart and publish the dashboard

With your data loaded, you can now build a visualization that shows total sales by product category.

  • On the analysis page, choose Visualize on the toolbar at left (if not already open).
  • On the application bar at upper left, choose Add, and then choose Add visual.
  • In the Visual types pane, choose the Vertical bar chart icon.
  • From the Fields list pane, drag category to the X axis field well.
  • From the Fields list pane, drag total_amount to the Value field well.

A bar chart appears showing each product category on the X axis and the sum of total_amount on the Y axis. QuickSight automatically aggregates with Sum by default for numeric measures.

What should I see?

You should see bars for categories like Electronics, Audio, Home, Accessories, and Office. Electronics should have the tallest bar because it has the most orders and highest combined total_amount in the sample data.

Now publish this analysis as a shareable dashboard.

  • In the top-right of the analysis page, click Publish.
  • Select Publish dashboard.
  • Enter Sales Pipeline Dashboard as the dashboard name.
  • Click Publish.

Before you check the result, take a moment to predict: what do you expect the dashboard to show?

  • Open the published dashboard by clicking Dashboards in the left navigation, then selecting Sales Pipeline Dashboard.

You should see your bar chart showing total sales amount by product category. The data matches the enriched CSV files in your transformed bucket. Your entire pipeline is now visible: from messy CSV to clean, cataloged, enriched data to a live interactive chart.

Dashboard not showing data?

Go back to Datasets and check that your dataset imported rows successfully. If SPICE shows 0 rows, re-check your manifest.json S3 URI and ensure QuickSight has permission to read from the transformed bucket.

help me fix my QuickSight dashboard

Your pipeline is complete. Data flows from a messy CSV through Lambda cleaning, Glue enrichment, and into a live QuickSight dashboard without any manual intervention between stages. Next up: enhance this dashboard with time-series analysis and filtering in the Secret Mission.

Secret mission

Enhance Your Dashboard

A single chart is a report, not a dashboard. Can you add a time-series view and an interactive filter that transforms your Sales Pipeline Dashboard into a tool someone would actually use for decision-making?

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. Most resources in this project cost very little when idle, but your QuickSight account will start charging after the 30-day free trial ends.

Cost warning

Your QuickSight 30-day free trial will convert to a paid subscription at $9/user/month (annual) or $12/user/month (monthly) if you do not cancel before it expires. If you are not planning to use QuickSight beyond this project, choose Pause or Delete below to avoid charges.

Resources you used:

  • 3 S3 buckets (raw, processed, transformed) storing CSV files.
  • 1 Lambda function (CSVCleaningFunction) with an S3 event trigger.
  • 2 IAM roles (LambdaS3PipelineRole and GlueServiceRole).
  • 1 Glue database (sales_pipeline_db), 1 Glue crawler (processed-data-crawler), and 1 Glue ETL job.
  • 1 QuickSight account (30-day free trial).

Keep everything running

No action needed. Choose this if you are still actively building on this pipeline or experimenting with QuickSight dashboards.

  • S3 storage costs pennies per month for the small CSV files in this project.
  • Lambda costs nothing when idle (you only pay per invocation).
  • The Glue Data Catalog is free. Crawlers and ETL jobs only cost money while actively running.
  • IAM roles have no cost.
  • QuickSight will charge $9+/month after your 30-day trial ends. Set a calendar reminder to cancel before then if you do not plan to keep using it.

Pause - I'll come back to this later

Shut down QuickSight to avoid charges after the trial. Keep S3, Lambda, and Glue resources for future experimentation at near-zero cost.

  • Unsubscribe from QuickSight to avoid post-trial charges. In the QuickSight console, choose your account name in the top right corner.
  • Choose Manage QuickSight.
  • Choose Account termination and follow the prompts to delete your QuickSight account.
  • Leave your S3 buckets, Lambda function, Glue resources, and IAM roles in place. They cost nothing (or near-nothing) while idle.

Delete - I don't want to use this again

Remove all project resources to return your AWS account to zero cost from this project. Run these commands in your terminal.

First, empty and delete all three S3 buckets:

aws s3 rb s3://[[RAW_BUCKET="your-raw-bucket-name"]] --force
aws s3 rb s3://[[PROCESSED_BUCKET="your-processed-bucket-name"]] --force
aws s3 rb s3://[[TRANSFORMED_BUCKET="your-transformed-bucket-name"]] --force

Delete the Lambda function:

aws lambda delete-function --function-name CSVCleaningFunction

Delete the Glue resources:

aws glue delete-crawler --name processed-data-crawler
aws glue delete-job --name [[GLUE_JOB_NAME="your-glue-etl-job-name"]]
aws glue delete-database --name sales_pipeline_db

Delete the Lambda IAM role:

aws iam detach-role-policy --role-name LambdaS3PipelineRole --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
aws iam delete-role-policy --role-name LambdaS3PipelineRole --policy-name [[INLINE_POLICY_NAME="your-inline-s3-policy-name"]]
aws iam delete-role --role-name LambdaS3PipelineRole

Delete the Glue IAM role:

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

Finally, unsubscribe from QuickSight:

  • In the QuickSight console, choose your account name in the top right corner.
  • Choose Manage QuickSight.
  • Choose Account termination and follow the prompts to delete your QuickSight account.

That's a Wrap!

That's a Wrap!

Nice work! You just built a fully automated, serverless data pipeline on AWS that takes messy CSV data and transforms it into a polished, interactive dashboard.

You've learned how to:

  • Built a three-stage data pipeline with separate S3 buckets for raw, processed, and transformed data, and wrote a Lambda function that automatically cleans CSV files (deduplication, date standardization, whitespace removal) the moment they land.
  • Configured event-driven automation with an S3 trigger so the entire pipeline runs hands-free, then used AWS Glue to catalog your data schema and enrich the dataset with a calculated total_amount column via visual ETL.
  • Created a QuickSight dashboard that visualizes your pipeline output as interactive charts, proving the full loop from messy data to actionable insight.
  • Secret Mission: Enhanced the dashboard with time-series analysis and interactive filtering so viewers can explore sales trends by region.

Ready to quiz yourself?