Serverless E-Commerce Analytics Dashboard

Build a serverless analytics pipeline from Lambda to a QuickSight dashboard.

Introduction

30 Second Summary

Every online store generates thousands of data points every day. Turning that raw data into clear business insights is what separates guesswork from strategy.

In this project, you will build a serverless analytics pipeline on AWS that generates sample e-commerce orders, stores them for both operational use and analytics, and visualizes the results in an interactive dashboard.

What You'll Build

A complete data pipeline that flows from a Python Lambda function through DynamoDB and S3, into SQL queries with Athena, and out as a published QuickSight dashboard.

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

  • A Python Lambda function that generates 150 sample e-commerce orders and loads them into DynamoDB and S3.
  • A SQL analytics layer using Athena to query your data lake with standard SQL.
  • A published QuickSight dashboard with three visualizations showing revenue trends, top product categories, and regional sales breakdowns.
  • Secret Mission: Add interactive date range filters and KPI summary metrics to your dashboard.

Are there any prerequisites?

You need an AWS account with the Free Tier active, the AWS CLI installed and configured, and Python installed locally.

Some familiarity with DynamoDB and QuickSight is helpful but not required.

Set Up Your Project Resources

Before writing any code, you need three AWS resources in place. Your Lambda function will write data to both DynamoDB and S3. Then you will query that data using QuickSight to build a dashboard.

In this step, you will create all three resources so they are ready when the Lambda function runs in the next step.

In this step, get ready to:
  • Create an S3 bucket for analytics data and Athena query results.
  • Create a DynamoDB table to store e-commerce order records.
  • Activate the QuickSight free trial.
Create your S3 bucket

Your project needs an S3 bucket to store two things: the analytics data file your Lambda will generate, and the query results that Athena produces when you run SQL against that data.

  • Sign in to the AWS Management Console.
  • In the search bar at the top, type S3 and select S3 from the results.
  • Click Create bucket.
  • For Bucket name, enter a globally unique name such as nextwork-analytics-yourname. S3 bucket names must be unique across all AWS accounts, so add your name or a random number to make it unique.
  • For AWS Region, choose a region close to you (e.g. us-east-1). Remember this region because all other resources must use the same one.
  • Leave all other settings as default (Block Public Access enabled, no versioning).
  • Scroll down and click Create bucket.

Why one bucket for both data and query results?

Athena requires an S3 location to write its query output. Using the same bucket with different prefixes (orders/ for data and athena-results/ for output) keeps everything organized in one place and simplifies cleanup later.

  • You should see your new bucket appear in the S3 buckets list with a green success banner.

Bucket name already taken?

S3 bucket names are globally unique across all AWS accounts. If you see a naming error, try adding random numbers or your initials to the name. Help me fix my S3 bucket name error.

Create the DynamoDB table

Your Lambda function will write 150 e-commerce order records into this table. Each order has a unique ID, so order_id will serve as the partition key.

  • In the AWS Console search bar, type DynamoDB and select DynamoDB from the results.
  • In the left navigation pane, click Tables.
  • Click Create table.
  • For Table name, enter ecommerce-orders.
  • For Partition key, enter order_id and leave the type as String.
  • Leave the Sort key field empty.
  • Under Table settings, select Customize settings.
  • Under Read/write capacity settings, select On-demand.
  • Leave all remaining settings as default.
  • Scroll down and click Create table.

What is on-demand capacity mode?

On-demand mode means DynamoDB automatically scales to handle your requests without any capacity planning. You only pay for the reads and writes you actually perform.

For this project, the Lambda will write 150 items in a single batch. On-demand handles this burst easily and costs virtually nothing within the AWS Free Tier.

  • Wait for the table status to change from Creating to Active. This usually takes a few seconds.
  • You should see ecommerce-orders listed with status Active and billing mode On-demand.

Table stuck in Creating status?

DynamoDB tables typically activate within 5-10 seconds. If the status does not change, refresh the page. If it still shows Creating after a minute, check that you are looking at the correct AWS region in the top-right corner of the console. Help me troubleshoot my DynamoDB table creation.

Activate the QuickSight free trial

QuickSight is the visualization service you will use to build your dashboard in the final step. It offers a 30-day free trial for up to 25 users.

  • In the AWS Console search bar, type QuickSight and select QuickSight from the results.
  • If this is your first time opening QuickSight, you will see a signup page. Your AWS account number is displayed for verification.
  • Enter a unique QuickSight account name (e.g. nextwork-analytics).
  • Enter your email address in the Notification email address field.
  • For the SPICE region, choose the same region you used for your S3 bucket and DynamoDB table.
  • For Authentication method, keep the recommended option (Password-based or Single-Sign On).
  • Click Create account.
  • Wait for account creation to complete. This may take a minute.
  • Click Go to Amazon QuickSight to open the service.

Cancel QuickSight before your trial ends

The QuickSight free trial lasts 30 days. After that, it charges $24 per user per month. Set a calendar reminder to delete your QuickSight account before the trial expires. The Before You Go section at the end of this project has deletion steps.

Already have QuickSight?

If you have previously signed up for QuickSight, you will land directly on the QuickSight home page instead of seeing the signup flow. You can proceed to the next step.

  • You should now see the QuickSight home page with a welcome message or sample analyses.

Getting a permissions error?

QuickSight requires specific IAM permissions to set up. If you see an access denied message, make sure you are signed in as a user with administrator access (or at minimum, permissions to subscribe to QuickSight). Help me fix QuickSight signup permissions.

Your foundational resources are in place. Next up, you will write the Lambda function that generates e-commerce data and loads it into both DynamoDB and S3.

Write and Deploy the Lambda Function

Your data stores are ready. Now you need something to fill them with data.

In this step, you will write a Python Lambda function that generates 150 sample e-commerce orders and loads them into both DynamoDB and S3. Then you will create an IAM role to give the function the permissions it needs, and deploy it using the AWS CLI.

In this step, get ready to:
  • Write the Python Lambda handler that generates and loads e-commerce order data.
  • Create an IAM execution role with the correct permissions.
  • Package and deploy the Lambda function using the AWS CLI.
Create the project folder and write the Lambda handler

Your Lambda function needs a local project folder to hold the Python code and configuration files before you deploy them to AWS.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ecommerce-analytics
cd ecommerce-analytics
  • Confirm the folder exists by running:
ls

The folder will be empty for now. You will add files to it next.

The Lambda function has two jobs. First, it generates 150 fake e-commerce orders with realistic product names, prices, and regions. Second, it writes those orders to DynamoDB and to S3 as a JSON Lines file that Athena can query later.

  • Create a file called lambda_function.py inside your ecommerce-analytics folder. Open it in your text editor and paste the following code:
import json
import boto3
import random
from datetime import datetime, timedelta
from decimal import Decimal


def generate_orders(num_orders=150):
    """Generate sample e-commerce order data."""
    products = [
        {"name": "Wireless Headphones", "category": "Electronics", "price": 49.99},
        {"name": "Running Shoes", "category": "Sportswear", "price": 89.99},
        {"name": "Coffee Maker", "category": "Home & Kitchen", "price": 79.99},
        {"name": "Yoga Mat", "category": "Sportswear", "price": 29.99},
        {"name": "Bluetooth Speaker", "category": "Electronics", "price": 39.99},
        {"name": "Water Bottle", "category": "Sports & Outdoors", "price": 24.99},
        {"name": "Desk Lamp", "category": "Home & Kitchen", "price": 34.99},
        {"name": "Phone Case", "category": "Electronics", "price": 19.99},
        {"name": "Backpack", "category": "Sports & Outdoors", "price": 59.99},
        {"name": "Notebook Set", "category": "Office Supplies", "price": 14.99},
    ]
    regions = ["us-east-1", "us-west-2", "eu-west-1", "ap-southeast-1"]
    statuses = ["completed", "completed", "completed", "processing", "cancelled"]

    orders = []
    start_date = datetime(2024, 1, 1)

    for i in range(num_orders):
        product = random.choice(products)
        quantity = random.randint(1, 5)
        order_date = start_date + timedelta(days=random.randint(0, 180))

        order = {
            "order_id": f"ORD-{i+1:04d}",
            "order_date": order_date.strftime("%Y-%m-%d"),
            "customer_id": f"CUST-{random.randint(1, 50):04d}",
            "product_name": product["name"],
            "category": product["category"],
            "quantity": quantity,
            "unit_price": product["price"],
            "total_amount": round(product["price"] * quantity, 2),
            "region": random.choice(regions),
            "status": random.choice(statuses),
        }
        orders.append(order)

    return orders

What does this code do?

The generate_orders function creates 150 fake but realistic e-commerce orders. Each order gets a unique ID, a random product from the catalog, a random customer, and a date somewhere in the first half of 2024.

The weighted statuses list (three entries for "completed" vs one each for "processing" and "cancelled") means roughly 60% of orders will be marked completed. This gives your analytics queries meaningful data to filter on later.

  • Below the generate_orders function in the same file, paste the main Lambda handler:
def lambda_handler(event, context):
    """
    Main Lambda handler. Loads sample e-commerce orders into
    DynamoDB (operational store) and S3 (analytics layer).
    """
    dynamodb = boto3.resource("dynamodb")
    s3 = boto3.client("s3")

    table_name = "ecommerce-orders"
    bucket_name = event["bucket_name"]

    # Generate sample orders
    orders = generate_orders(150)

    # Write to DynamoDB (operational store)
    # DynamoDB requires Decimal for numeric types, not float
    table = dynamodb.Table(table_name)
    with table.batch_writer() as batch:
        for order in orders:
            dynamo_item = {
                "order_id": order["order_id"],
                "order_date": order["order_date"],
                "customer_id": order["customer_id"],
                "product_name": order["product_name"],
                "category": order["category"],
                "quantity": order["quantity"],
                "unit_price": Decimal(str(order["unit_price"])),
                "total_amount": Decimal(str(order["total_amount"])),
                "region": order["region"],
                "status": order["status"],
            }
            batch.put_item(Item=dynamo_item)

    # Write to S3 as JSON Lines (analytics layer)
    # Each line is one JSON object, which Athena can parse directly
    json_lines = "\n".join(json.dumps(order) for order in orders)
    s3.put_object(
        Bucket=bucket_name,
        Key="orders/orders.json",
        Body=json_lines,
        ContentType="application/json",
    )

    return {
        "statusCode": 200,
        "body": json.dumps(
            {
                "message": f"Successfully loaded {len(orders)} orders",
                "dynamodb_table": table_name,
                "s3_location": f"s3://{bucket_name}/orders/orders.json",
            }
        ),
    }

What does the handler do?

The lambda_handler function is the entry point AWS Lambda calls when the function runs. It reads the bucket_name from the incoming event so you can tell it which S3 bucket to write to.

It writes to two destinations. DynamoDB gets each order as a table item (converting floats to Decimal because DynamoDB requires it). S3 gets a single JSON Lines file where each line is one order, which Athena can parse directly.

Why write to both DynamoDB and S3?

This is called the dual-write pattern. DynamoDB serves as the fast operational store for looking up individual orders. S3 serves as the analytics layer where Athena can scan all orders cheaply with SQL.

Querying DynamoDB directly for analytics would require expensive full-table scans that consume read capacity. S3 is optimized for bulk reads, and Athena charges only $5 per TB scanned.

  • Save lambda_function.py.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

import json
import boto3
import random
from datetime import datetime, timedelta
from decimal import Decimal


def generate_orders(num_orders=150):
    """Generate sample e-commerce order data."""
    products = [
        {"name": "Wireless Headphones", "category": "Electronics", "price": 49.99},
        {"name": "Running Shoes", "category": "Sportswear", "price": 89.99},
        {"name": "Coffee Maker", "category": "Home & Kitchen", "price": 79.99},
        {"name": "Yoga Mat", "category": "Sportswear", "price": 29.99},
        {"name": "Bluetooth Speaker", "category": "Electronics", "price": 39.99},
        {"name": "Water Bottle", "category": "Sports & Outdoors", "price": 24.99},
        {"name": "Desk Lamp", "category": "Home & Kitchen", "price": 34.99},
        {"name": "Phone Case", "category": "Electronics", "price": 19.99},
        {"name": "Backpack", "category": "Sports & Outdoors", "price": 59.99},
        {"name": "Notebook Set", "category": "Office Supplies", "price": 14.99},
    ]
    regions = ["us-east-1", "us-west-2", "eu-west-1", "ap-southeast-1"]
    statuses = ["completed", "completed", "completed", "processing", "cancelled"]

    orders = []
    start_date = datetime(2024, 1, 1)

    for i in range(num_orders):
        product = random.choice(products)
        quantity = random.randint(1, 5)
        order_date = start_date + timedelta(days=random.randint(0, 180))

        order = {
            "order_id": f"ORD-{i+1:04d}",
            "order_date": order_date.strftime("%Y-%m-%d"),
            "customer_id": f"CUST-{random.randint(1, 50):04d}",
            "product_name": product["name"],
            "category": product["category"],
            "quantity": quantity,
            "unit_price": product["price"],
            "total_amount": round(product["price"] * quantity, 2),
            "region": random.choice(regions),
            "status": random.choice(statuses),
        }
        orders.append(order)

    return orders


def lambda_handler(event, context):
    """
    Main Lambda handler. Loads sample e-commerce orders into
    DynamoDB (operational store) and S3 (analytics layer).
    """
    dynamodb = boto3.resource("dynamodb")
    s3 = boto3.client("s3")

    table_name = "ecommerce-orders"
    bucket_name = event["bucket_name"]

    # Generate sample orders
    orders = generate_orders(150)

    # Write to DynamoDB (operational store)
    # DynamoDB requires Decimal for numeric types, not float
    table = dynamodb.Table(table_name)
    with table.batch_writer() as batch:
        for order in orders:
            dynamo_item = {
                "order_id": order["order_id"],
                "order_date": order["order_date"],
                "customer_id": order["customer_id"],
                "product_name": order["product_name"],
                "category": order["category"],
                "quantity": order["quantity"],
                "unit_price": Decimal(str(order["unit_price"])),
                "total_amount": Decimal(str(order["total_amount"])),
                "region": order["region"],
                "status": order["status"],
            }
            batch.put_item(Item=dynamo_item)

    # Write to S3 as JSON Lines (analytics layer)
    # Each line is one JSON object, which Athena can parse directly
    json_lines = "\n".join(json.dumps(order) for order in orders)
    s3.put_object(
        Bucket=bucket_name,
        Key="orders/orders.json",
        Body=json_lines,
        ContentType="application/json",
    )

    return {
        "statusCode": 200,
        "body": json.dumps(
            {
                "message": f"Successfully loaded {len(orders)} orders",
                "dynamodb_table": table_name,
                "s3_location": f"s3://{bucket_name}/orders/orders.json",
            }
        ),
    }
Create the IAM execution role

Lambda functions need an IAM role that grants them permission to access other AWS services. Your function needs to write to DynamoDB, upload to S3, and create CloudWatch Logs for debugging.

You will create two policy files. The trust policy tells AWS that Lambda is allowed to assume this role. The permissions policy lists the specific actions your function can perform.

  • In your ecommerce-analytics folder, create a file called trust-policy.json and paste the following:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

What is a trust policy?

A trust policy defines WHO can use this role. In this case, it says the Lambda service (lambda.amazonaws.com) is allowed to assume the role. Without this, your Lambda function cannot take on the role's permissions.

  • Save trust-policy.json.
  • Create another file called permissions-policy.json in the same folder and paste the following:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "dynamodb:PutItem",
        "dynamodb:BatchWriteItem"
      ],
      "Resource": "arn:aws:dynamodb:*:*:table/ecommerce-orders"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject"
      ],
      "Resource": "arn:aws:s3:::[[BUCKET_NAME=\"your-analytics-bucket-name\"]]/*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "logs:CreateLogGroup",
        "logs:CreateLogStream",
        "logs:PutLogEvents"
      ],
      "Resource": "arn:aws:logs:*:*:*"
    }
  ]
}

What does this permissions policy grant?

  • dynamodb:PutItem and dynamodb:BatchWriteItem let the function write order records to your ecommerce-orders table.
  • s3:PutObject lets the function upload the JSON Lines file to your S3 bucket.
  • The logs permissions let the function write debugging logs to CloudWatch.
  • Save permissions-policy.json.

Now use the AWS CLI to create the role and attach the permissions.

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

What does this command do?

This creates an IAM role named ecommerce-lambda-role and attaches the trust policy from your JSON file. The file:// prefix tells the CLI to read the policy from a local file.

You should see a JSON response containing the role details, including an Arn field. Copy the full ARN value (it looks like arn:aws:iam::123456789012:role/ecommerce-lambda-role). You will need it when you deploy the function.

  • Attach the permissions policy to the role by running:
aws iam put-role-policy --role-name ecommerce-lambda-role --policy-name ecommerce-lambda-permissions --policy-document file://permissions-policy.json

What does this command do?

This embeds your permissions policy directly into the role as an inline policy named ecommerce-lambda-permissions. The role now knows both WHO can use it (Lambda) and WHAT it can do (write to DynamoDB, S3, and CloudWatch Logs).

Seeing an access denied error?

Make sure your AWS CLI is configured with credentials that have permission to create IAM roles. Run aws sts get-caller-identity to confirm your CLI is authenticated.

Also check that you are running the commands from inside your ecommerce-analytics folder so the CLI can find trust-policy.json and permissions-policy.json.

Help me fix an access denied error when creating an IAM role with the AWS CLI.

Package and deploy the Lambda function

AWS Lambda expects your code as a zip file. You will also create a test event file so you can invoke the function with your bucket name later.

  • Create a file called test-event.json in your ecommerce-analytics folder and paste the following:
{
  "bucket_name": "[[BUCKET_NAME=\"your-analytics-bucket-name\"]]"
}

What is this file for?

When you invoke the Lambda function, you pass it an event payload. This JSON file provides the bucket_name that the handler reads with event["bucket_name"]. Replace the placeholder with the actual S3 bucket name you created in Step 1.

  • Save test-event.json.
  • Zip your Lambda function code by running:

macOS

zip function.zip lambda_function.py

Windows

Compress-Archive -Path lambda_function.py -DestinationPath function.zip
  • Confirm the zip file was created by running:
ls

You should see function.zip listed alongside your other files (lambda_function.py, trust-policy.json, permissions-policy.json, test-event.json).

  • Deploy the Lambda function to AWS by running this command (replace the role ARN with the one you copied earlier):
aws lambda create-function --function-name ecommerce-data-loader --zip-file fileb://function.zip --handler lambda_function.lambda_handler --runtime python3.12 --role [[ROLE_ARN="arn:aws:iam::123456789012:role/ecommerce-lambda-role"]] --timeout 60

What does this command do?

  • --function-name ecommerce-data-loader names the function in AWS.
  • --zip-file fileb://function.zip uploads your zipped code. The fileb:// prefix tells the CLI to read a binary file from disk.
  • --handler lambda_function.lambda_handler tells Lambda which file and function to call (file lambda_function.py, function lambda_handler).
  • --runtime python3.12 sets the Python version.
  • --timeout 60 gives the function 60 seconds to finish writing 150 orders to two services.

You should see a JSON response with details about your new function, including a FunctionArn and a State of Pending or Active.

Seeing a role not found error?

IAM roles take a few seconds to propagate after creation. Wait 10 seconds and try the command again.

If you see a message about the role ARN being invalid, double check that you copied the full ARN from the aws iam create-role output. It should start with arn:aws:iam:: and end with :role/ecommerce-lambda-role.

Help me fix a role not found error when deploying my Lambda function.

Your Lambda function is deployed and ready to run. Next up, you will invoke it and verify that your data lands in both DynamoDB and S3.

Test and Verify the Data Pipeline

Your Lambda function is deployed and ready to generate data. The next step is to trigger it and confirm that order records land in both DynamoDB and S3.

Running the function once will generate 150 sample orders. You will verify the data in two places: the DynamoDB console (the operational store) and the S3 console (the analytics layer).

In this step, get ready to:
  • Invoke the Lambda function with a test event.
  • Verify order records appear in DynamoDB.
  • Verify the JSON Lines file exists in your S3 bucket.
Invoke the Lambda function

Your test-event.json file contains the bucket name your function needs to know where to write analytics data. The AWS CLI can invoke your function synchronously and write the response to a local file.

  • Make sure your terminal is inside the ecommerce-analytics folder (the same folder that contains test-event.json).
  • Invoke the Lambda function by running this command:
aws lambda invoke \
  --function-name ecommerce-data-loader \
  --cli-binary-format raw-in-base64-out \
  --payload fileb://test-event.json \
  output.json

What does this command do?

  • --function-name ecommerce-data-loader tells the CLI which Lambda function to invoke.
  • --cli-binary-format raw-in-base64-out ensures the CLI sends the file contents as-is (required for AWS CLI version 2).
  • --payload fileb://test-event.json passes your test event JSON as the function's input.
  • output.json is the local file where the CLI writes the function's response.
  • After the command finishes, the terminal prints a short JSON summary. You should see StatusCode with a value of 200.
  • Open output.json to confirm the response body says Successfully loaded 150 orders.

Seeing an error instead of StatusCode 200?

  • If you see an AccessDeniedException, the IAM role may be missing the required permissions. Double check that your inline policy grants dynamodb:PutItem, dynamodb:BatchWriteItem, and s3:PutObject.
  • If you see a ResourceNotFoundException, confirm the DynamoDB table name is exactly ecommerce-orders and that the table is in the same region as your Lambda function.
  • If the function times out, confirm you set the timeout to 60 seconds when you deployed the function.

Help me debug my Lambda invocation error.

Verify the order records in DynamoDB

The function wrote 150 orders to your DynamoDB table as the operational data store. You can browse these items directly in the AWS console.

  • Open the AWS Management Console and navigate to the DynamoDB service.
  • In the left sidebar, click Tables.
  • Click the ecommerce-orders table name.
  • Click Explore table items (the orange button near the top).

You should see a list of items with order_id values like ORD-0001, ORD-0002, and so on. The table holds 150 items total.

What should I see?

Each item has fields like order_date, customer_id, product_name, category, quantity, unit_price, total_amount, region, and status. The numeric fields (unit_price and total_amount) show as DynamoDB Number types because the function converted them to Decimal.

Don't see any items?

  • Make sure you are viewing the correct AWS region. The region dropdown is in the top-right corner of the console.
  • Check that output.json contains Successfully loaded 150 orders. If it shows an error, re-run the invoke command after fixing the issue.

Help me find my DynamoDB items.

Verify the JSON Lines file in S3

The same function also wrote all 150 orders to S3 as a single JSON Lines file. This is the analytics layer that Athena will query in the next step.

  • In the AWS console, navigate to the S3 service.
  • Click your bucket name (the one you created in Step 1).
  • Click the orders/ folder.
  • You should see a file named orders.json.
  • Click orders.json, then click Download or use the Object actions dropdown to select Query with S3 Select to preview the contents.

The file contains 150 lines. Each line is one complete JSON object representing a single order.

Why JSON Lines instead of a JSON array?

JSON Lines format stores one JSON object per line with no wrapping array. This is the format Athena's JsonSerDe expects. Each line is independently parseable, which makes it efficient for large-scale queries.

Don't see the orders/ folder?

  • Confirm you are looking at the correct bucket. The bucket name must match what you put in test-event.json.
  • Check that your permissions-policy.json grants s3:PutObject on the correct bucket ARN. The resource must end with /* to allow writing objects inside the bucket.

Help me find my S3 analytics file.

Your data pipeline is working. Orders flow into DynamoDB for operational access and into S3 for analytics. Next up, you will query that S3 data using SQL with Athena.

Query Your Data with Athena

Your order data is sitting in S3 as a JSON Lines file. That format is great for storage, but you cannot run business questions against a raw file.

Amazon Athena lets you query data directly in S3 using standard SQL. It uses the AWS Glue Data Catalog to understand the structure of your data. In this step, you will set up Athena, define a table schema over your JSON file, and run analytics queries to surface revenue trends and customer insights.

In this step, get ready to:
  • Configure Athena's query results location in your S3 bucket.
  • Create a Glue database and external table over your S3 data.
  • Run SQL analytics queries to uncover revenue, trend, and regional insights.
Configure the Athena query results location

Before you can run any query, Athena needs a place in S3 to store the output. You will point it at a folder inside your existing analytics bucket.

  • In the AWS Management Console, search for Athena and open the service.
  • If this is your first time visiting the Athena console, click Explore the query editor to open the query editor.
  • In the navigation bar at the top, click Settings.
  • Click Manage.
  • In the Location of query result box, enter the path to a subfolder in your analytics bucket:
s3://[[ANALYTICS_BUCKET="your-analytics-bucket-name"]]/athena-results/
  • Click Save.
  • Click Editor to switch back to the query editor.

Why a separate folder for results?

Athena writes a CSV file for every query you run. Keeping these in a dedicated athena-results/ prefix keeps your raw data in orders/ clean and separate from query output.

Don't see the Settings option?

Make sure you are in the Athena query editor view, not the Athena landing page. Look for the Settings tab in the top navigation bar next to Editor.

help me find the Athena settings to configure query results

Create a Glue database and define an external table

Athena uses the Glue Data Catalog to store metadata about your data. You will create a database and then define an external table that tells Athena the shape of your JSON Lines file.

  • In the Athena query editor, clear any existing text, paste the following SQL, and click Run:
CREATE DATABASE ecommerce_analytics;

What does this query do?

This creates a new database called ecommerce_analytics in the Glue Data Catalog. It acts as a logical container for the tables you will create next.

  • You should see the message Query successful in the results pane.
  • From the Database dropdown on the left, select ecommerce_analytics to set it as your current database.
  • Clear the editor, paste the following SQL, replace the bucket name with your own, and click Run:
CREATE EXTERNAL TABLE ecommerce_analytics.orders (
  order_id STRING,
  order_date STRING,
  customer_id STRING,
  product_name STRING,
  category STRING,
  quantity INT,
  unit_price DOUBLE,
  total_amount DOUBLE,
  region STRING,
  status STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://[[ANALYTICS_BUCKET="your-analytics-bucket-name"]]/orders/';

What does this query do?

This creates an external table that maps your JSON Lines file into a structured schema. Athena does not copy or move your data. It reads the file in place from S3 every time you query it.

The ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' line tells Athena to parse each line of the file as a JSON object. The LOCATION points to the orders/ prefix where your Lambda stored the data.

  • You should see Query successful in the results pane.
  • In the left panel under Tables, confirm that orders now appears.

Getting a HIVE_METASTORE_ERROR?

Double-check that you selected ecommerce_analytics from the Database dropdown before running the CREATE EXTERNAL TABLE statement. Also confirm the S3 location matches the exact bucket name you used earlier (check for typos or extra slashes).

help me fix a HIVE_METASTORE_ERROR in Athena

Save these SQL statements locally for reference. Create a file called athena-setup.sql in your ecommerce-analytics folder and paste the following:

-- Create the analytics database
CREATE DATABASE ecommerce_analytics;

-- Create external table pointing to S3 JSON Lines data
CREATE EXTERNAL TABLE ecommerce_analytics.orders (
  order_id STRING,
  order_date STRING,
  customer_id STRING,
  product_name STRING,
  category STRING,
  quantity INT,
  unit_price DOUBLE,
  total_amount DOUBLE,
  region STRING,
  status STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://[[ANALYTICS_BUCKET="your-analytics-bucket-name"]]/orders/';
  • Save athena-setup.sql.
Run SQL analytics queries

Now that your table is defined, you can query it using standard SQL. You will run four analytics queries that answer real business questions about your e-commerce data.

  • Clear the Athena editor, paste the following query, and click Run:
-- Revenue by product category
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY category
ORDER BY total_revenue DESC;

What does this query do?

This groups all completed orders by product category and calculates the total revenue, order count, and average order value for each. The ORDER BY total_revenue DESC sorts results from highest to lowest revenue.

  • You should see a results table with categories like Electronics, Sportswear, Home & Kitchen, and their revenue totals.
  • Clear the editor, paste the monthly trends query, and click Run:
-- Monthly order trends
SELECT
  SUBSTR(order_date, 1, 7) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY SUBSTR(order_date, 1, 7)
ORDER BY order_month;
  • You should see monthly rows from 2024-01 through 2024-06 with order counts and revenue totals.
  • Clear the editor, paste the regional breakdown query, and click Run:
-- Sales by region
SELECT
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY region
ORDER BY total_revenue DESC;
  • You should see four regions (us-east-1, us-west-2, eu-west-1, ap-southeast-1) with their revenue totals.
  • Clear the editor, paste the top customers query, and click Run:
-- Top 10 customers by spend
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

What do these queries have in common?

Each query filters on WHERE status = 'completed' to exclude orders that are still processing or cancelled. This ensures your revenue figures only reflect actual completed sales.

Athena charges $5 per TB of data scanned, with a 10 MB minimum per query. Your file is only about 50 KB, so each query costs a fraction of a cent.

  • You should see a list of 10 customer IDs ranked by their total spending.

Save all four queries locally for reference. Create a file called athena-queries.sql in your ecommerce-analytics folder and paste the following:

-- Revenue by product category
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY category
ORDER BY total_revenue DESC;

-- Monthly order trends
SELECT
  SUBSTR(order_date, 1, 7) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY SUBSTR(order_date, 1, 7)
ORDER BY order_month;

-- Sales by region
SELECT
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY region
ORDER BY total_revenue DESC;

-- Top 10 customers by spend
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;
  • Save athena-queries.sql.

Queries returning zero rows?

Make sure the LOCATION in your CREATE EXTERNAL TABLE statement matches exactly where your Lambda put the file. Check your S3 bucket for the orders/orders.json path. The LOCATION should point to the folder (orders/), not the file itself.

help me debug why my Athena query returns zero rows

✔️ Awesome, I've got everything!

Great. Make sure you have saved both athena-setup.sql and athena-queries.sql in your local project folder.

ⓧ I'd like to double check the full code

Here is the complete athena-setup.sql:

-- Create the analytics database
CREATE DATABASE ecommerce_analytics;

-- Create external table pointing to S3 JSON Lines data
CREATE EXTERNAL TABLE ecommerce_analytics.orders (
  order_id STRING,
  order_date STRING,
  customer_id STRING,
  product_name STRING,
  category STRING,
  quantity INT,
  unit_price DOUBLE,
  total_amount DOUBLE,
  region STRING,
  status STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-analytics-bucket-name/orders/';

Here is the complete athena-queries.sql:

-- Revenue by product category
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY category
ORDER BY total_revenue DESC;

-- Monthly order trends
SELECT
  SUBSTR(order_date, 1, 7) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY SUBSTR(order_date, 1, 7)
ORDER BY order_month;

-- Sales by region
SELECT
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY region
ORDER BY total_revenue DESC;

-- Top 10 customers by spend
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM ecommerce_analytics.orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

Your data lake is fully queryable. Next up, you will connect Athena to QuickSight and turn these query results into an interactive visual dashboard.

Build the QuickSight Dashboard

You have 150 order records in your S3 analytics layer and a set of SQL queries that reveal patterns in the data. Now it is time to turn those query results into something visual.

In this step, you will connect QuickSight to your Athena data, import the orders dataset into SPICE (QuickSight's in-memory engine), and build three visualizations that answer real business questions.

In this step, get ready to:
  • Create an Athena data source and import the orders dataset into SPICE.
  • Build three visualizations: revenue by category, orders over time, and sales by region.
  • Publish your analysis as a shareable dashboard.
Create an Athena data source and import the orders dataset

QuickSight needs a data source connection to know where your data lives. You will point it at your Athena workgroup, then select the orders table from your ecommerce_analytics database.

  • Open the QuickSight console (search for QuickSight in the AWS console search bar).
  • In the left navigation pane, choose Datasets.
  • Click New dataset in the top right.
  • On the data source cards screen, choose the Athena card.
  • For Data source name, enter ecommerce-athena.
  • For Athena workgroup, leave the default (primary).
  • Click Validate connection to confirm QuickSight can reach Athena.
  • Click Create data source.

You should see the Choose your table screen appear.

  • For Catalog, choose AwsDataCatalog.
  • For Database, choose ecommerce_analytics.
  • In the Tables list, select orders.
  • Click Edit/Preview data.

The data preparation page opens with a preview of your 150 order records.

  • In the dataset name field at the top, replace the default name with ecommerce-orders-dataset.
  • Confirm the SPICE radio button is selected (not Direct query). SPICE imports the data into QuickSight's in-memory engine for faster dashboard performance.
  • Click Save & visualize at the top of the page.

QuickSight imports your 150 rows into SPICE and opens a new analysis with a blank canvas.

What is SPICE?

SPICE (Super-fast, Parallel, In-memory Calculation Engine) is QuickSight's columnar data store. Instead of querying Athena every time you interact with a chart, SPICE caches the data locally for sub-second response times.

Your QuickSight trial includes 1 GB of SPICE capacity. This dataset is only a few KB, so you are well within limits.

Don't see your database listed?

Make sure you are in the same AWS region where you created your Glue database. QuickSight and Athena must be in the same region to discover the catalog.

If you see a permissions error, QuickSight may not have access to your S3 bucket. Go to the QuickSight account settings, choose Security & permissions, and ensure your S3 bucket is selected under QuickSight access to AWS services.

help me connect QuickSight to my Athena database

Build three visualizations

Each visualization answers a different business question. You will build a bar chart for revenue by category, a line chart for order trends over time, and a pie chart for regional sales breakdown.

Your analysis should already be open with a blank visual selected. If not, open it from the Analyses section of QuickSight.

  • In the Visual types pane at the bottom left, choose the Horizontal bar chart icon.
  • From the Fields list pane, drag category to the Y axis field well.
  • Drag total_amount to the Value field well.

You should see a horizontal bar chart showing total revenue broken down by product category.

What do the field wells do?

Field wells are drop zones that tell QuickSight how to map your data columns to chart axes. For a horizontal bar chart, the Y axis holds the dimension (category labels) and Value holds the measure (numeric values to aggregate).

QuickSight automatically sums total_amount for each category. This is the same aggregation you wrote in SQL earlier.

Now add a second visual for order trends over time.

  • Click Add in the top menu bar, then choose Add visual.
  • In the Visual types pane, choose the Line chart icon.
  • Drag order_date to the X axis field well.
  • Drag total_amount to the Value field well.

You should see a line chart showing revenue aggregated over time. QuickSight groups the dates automatically by month.

Finally, add a pie chart for the regional breakdown.

  • Click Add in the top menu bar, then choose Add visual.
  • In the Visual types pane, choose the Pie chart icon.
  • Drag region to the Group/Color field well.
  • Drag total_amount to the Value field well.

You should see a pie chart with slices representing each AWS region's share of total revenue.

Resize your visuals

You can drag the corner of any visual to resize it. You can also drag visuals around the canvas to rearrange the layout so all three charts are visible at once.

Seeing an empty chart?

Check the Fields list pane to confirm you dragged fields to the correct field wells. Dimension fields (like category) have blue icons. Measure fields (like total_amount) have orange icons.

If your SPICE dataset shows 0 rows, go back to Datasets, open ecommerce-orders-dataset, and click Refresh SPICE to re-import the data.

help me fix my empty QuickSight visualization

Publish the analysis as a dashboard

An analysis is your workspace for building visuals. A dashboard is the published, shareable version that others can view without editing.

  • In your analysis, click Publish in the top right of the application bar.
  • Choose Publish dashboard.
  • Select Publish as new dashboard.
  • For the dashboard name, enter E-Commerce Analytics Dashboard.
  • Leave the default settings (all sheets selected).
  • Click Publish.

QuickSight publishes your dashboard. A share dialog may appear. You can close it by clicking the X icon.

Your dashboard is now live. You can find it anytime under Dashboards in the QuickSight navigation pane.

What is the difference between an analysis and a dashboard?

An analysis is your private editing workspace. You can add, remove, and modify visuals freely.

A dashboard is a read-only snapshot you share with others. Viewers can interact with filters and drill-downs, but they cannot change the underlying visuals or data.

Don't see the Publish button?

You must have at least one visual with data on your canvas to publish. If your visuals are empty, go back and verify the field wells are populated correctly.

help me publish my QuickSight analysis as a dashboard

Secret mission

Add Interactive Filters and KPI Metrics

Your dashboard looks great, but stakeholders want to slice data by date range and see headline numbers at a glance. Add a date range filter control and a row of KPI visuals showing total revenue, total orders, and average order value.

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. Lambda, DynamoDB, and S3 all stay within the AWS Free Tier for this project, so there are no ongoing costs from those services.

Cost warning

QuickSight charges $24 per user per month after the 30-day free trial ends. If you do not plan to keep using QuickSight, delete the account before the trial expires to avoid charges.

Resources you used:

  • 1 Lambda function (free tier, no idle cost).
  • 1 DynamoDB table (free tier, minimal storage).
  • 1 S3 bucket (minimal storage, a few KB).
  • 1 IAM role with attached inline policy.
  • 1 Glue database and external table.
  • 1 QuickSight account (30-day free trial).

Keep everything running

No action needed. Choose this if you are still actively building or exploring the dashboard.

  • Lambda, DynamoDB, and S3 cost nothing within the AWS Free Tier for this usage level.
  • QuickSight will charge $24/month after the 30-day trial ends. Set a calendar reminder to cancel before that date if you do not plan to continue.
  • The IAM role and Glue database have no ongoing cost.

Pause - I'll come back to this later

Shut down the paid resource to avoid charges, but keep the free-tier resources intact so you can pick up where you left off.

  • Delete your QuickSight account before the 30-day trial ends (see steps in the Delete tab).
  • Keep your Lambda function, DynamoDB table, S3 bucket, IAM role, and Glue database. They stay within the Free Tier.
  • When you return, you can re-activate QuickSight and reconnect to your Athena data source.

Delete - I don't want to use this again

Remove all project resources and start fresh. After these steps, total cost is $0.

Delete the QuickSight account
  • Open the QuickSight console.
  • Click your username in the top right corner.
  • Select Manage QuickSight.
  • Click Account Settings.
  • Select Delete account and confirm.
Delete the Glue database
  • Delete the Glue database and its tables by running:
aws glue delete-database --name ecommerce_analytics
Delete the Lambda function
  • Delete the Lambda function by running:
aws lambda delete-function --function-name ecommerce-data-loader
Delete the DynamoDB table
  • Delete the DynamoDB table by running:
aws dynamodb delete-table --table-name ecommerce-orders
Delete the S3 bucket
  • Delete your S3 bucket and all objects inside it by running:
aws s3 rb s3://[[BUCKET_NAME="your-analytics-bucket-name"]] --force
Delete the IAM role
  • Before deleting an IAM role, you must remove its inline policy first. Delete the inline policy by running:
aws iam delete-role-policy --role-name lambda-ecommerce-role --policy-name lambda-ecommerce-policy
  • Now delete the role itself by running:
aws iam delete-role --role-name lambda-ecommerce-role
Delete local project files
  • Remove your local project folder. On macOS or Linux, run:
rm -rf ~/Desktop/ecommerce-analytics
  • On Windows, run:
Remove-Item -Recurse -Force ~\Desktop\ecommerce-analytics

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete serverless analytics pipeline that transforms raw e-commerce data into interactive business dashboards.

You've learned how to:

  • Deploy a Python Lambda function that generates sample data and loads it into both DynamoDB and S3 simultaneously.
  • Query a data lake using standard SQL with Athena and the Glue Data Catalog to extract revenue trends, customer insights, and regional breakdowns.
  • Build and publish an interactive QuickSight dashboard with multiple visualizations connected to your Athena data source.
  • Added interactive date filtering and KPI metrics to your dashboard for dynamic data exploration.

Ready to quiz yourself?