Building a Serverless Sales Analytics Pipeline Using S3, Glue, Athena and Lambda
Introduction
Analysing sales data manually can take a lot of time, especially for retail businesses with many sales transactions and across different branches. Business owners need to know how much they are selling, which products sell best, and how each branch is performing. However, finding these insights from raw sales files can involve a lot of manual work.
For this project, I wanted to explore how AWS could be used to automate this process. I built a serverless retail sales analytics platform using Amazon S3, AWS Glue, Amazon Athena, and AWS Lambda.
The solution stores sales data in Amazon S3, uses AWS Glue to organise and catalogue it, and Amazon Athena to run SQL queries for analysing sales performance. I then used AWS Lambda to automate the process so that whenever a new CSV file is uploaded, the analytics queries are triggered automatically, and the results are saved back to S3.
In this blog, I’ll walk through how I built the solution and how these AWS services work together to automate sales data analysis.
Architecture Overview
The solution uses a serverless, event-driven architecture built using five main AWS services: Amazon S3, AWS Glue, Amazon Athena, IAM, and AWS Lambda.
The automation starts when a sales CSV file is uploaded to an S3 bucket. An S3 event notification detects the new file and triggers a Lambda function. The function submits predefined SQL queries to Amazon Athena.
Athena uses the table metadata stored in the AWS Glue Data Catalog to understand the structure and location of the sales data. It then reads the data from S3, runs the queries, and saves the results in a separate folder in the same S3 bucket.
This means that whenever a new sales CSV file is uploaded to the specified folder, the analysis process can run automatically without me having to submit each query manually.
AWS Services Used
I used five AWS services to build the sales analytics pipeline. Each service had a specific role in storing, organising, analysing, and automating the sales data.
1. Amazon S3
Amazon S3 is a fully managed object storage service. I used it to store the raw retail sales CSV files and the results generated by Athena.
2. AWS Glue
AWS Glue is a serverless data integration service. I used it’s crawler to identify the schema of the sales data and create metadata in the Glue Data Catalog.
3. Amazon Athena
Amazon Athena is a serverless query service that allows users to analyse data directly in Amazon S3 using SQL. I used it to run queries and generate sales insights.
4. AWS Lambda
AWS Lambda is a serverless compute service that runs code in response to events without requiring users to manage servers. I used it to automatically submit Athena queries whenever a new sales CSV file was uploaded to S3.
5. AWS IAM
AWS Identity and Access Management (IAM) is used to manage access to AWS resources. I used IAM roles and policies to give the Lambda function the permissions it needed to interact with other AWS services.
Project Implementation
- Creating the S3 Bucket
I started by creating an S3 bucket named sales-analytics-app-2026. Inside the bucket, I created two folders to organise the project files:
- raw-sales-data/ — to store the uploaded retail sales CSV files.
- athena-results/ — to store the results generated by Amazon Athena.
I then uploaded the retail sales dataset into the raw-sales-data/ folder.
2. Setting Up AWS Glue
Next, I set up AWS Glue to identify the structure of the sales dataset. I created a database named sales_analytics_db and a crawler named sales-analytics-crawler.
I then configured the crawler to scan the raw-sales-data/ folder in my S3 bucket. After running it, AWS Glue identified the columns and data types in the CSV file and created a table named raw_sales_data in the Glue Data Catalog. This made the sales data available for querying in Amazon Athena.
3. Running SQL Queries in Amazon Athena
After AWS Glue created the Data Catalog table, I moved to Amazon Athena to analyse the sales data.
I selected the sales_analytics_db database and used the raw_sales_data table to write and run five SQL queries. I ran the queries manually to test the data and confirm that Athena could return the expected results.
Get Kevin Kiruri’s stories in your inbox
Join Medium for free to get updates from this writer.
The queries focused on answering common retail business questions:
- Sales Summary: What was the total revenue, number of transactions, units sold and average transactions?
- Revenue by Branch: Which branches generated the most revenue?
- Revenue by Category: Which product categories contributed the most to sales?
- Monthly Sales Trend: How did sales performance change from month to month?
- Top Five Products: Which products generated the highest revenue?
All five queries executed successfully, and Athena saved their results in the athena-results/ folder in my S3 bucket.
This led me to the next part of the project: using AWS Lambda to automate the process. The idea was to allow the business owner to upload a new sales CSV file and have the queries run automatically without needing to interact with SQL.
4. Setting Up IAM Permissions
Before creating the Lambda function, I needed to make sure it had the right permissions to run the analytics queries. I created an IAM execution role named LambdaSalesAnalyticsExecutionRole and attached the AWSLambdaBasicExecutionRole policy to allow the function to write logs to Amazon CloudWatch.
I also added a custom policy called SalesAnalyticsLambdaAccessPolicy. This provided the permissions needed to submit queries to Athena, access the Glue Data Catalog and write query results to the S3 bucket.
With the execution role ready, the Lambda function had the access it needed to submit queries and save their results.
5. Creating the Lambda Function
After setting up the IAM permissions, I created a Lambda function named SalesAnalyticsAutomationFunction.
I added environment variables to provide the function with the details it needed to run the queries:
- DATABASE_NAME: sales_analytics_db
- ATHENA_WORKGROUP: primary
- ATHENA_OUTPUT: s3://sales-analytics-app-2026/athena-results/
The function purpose was to submit five predefined SQL queries to Amazon Athena. These queries covered the sales summary, revenue by branch, revenue by category, monthly sales trend and top five products.
import json
import os
import boto3
import urllib.parse
# AWS clients
athena = boto3.client("athena")
# Environment variables
DATABASE_NAME = os.environ["DATABASE_NAME"]
ATHENA_WORKGROUP = os.environ["ATHENA_WORKGROUP"]
ATHENA_OUTPUT = os.environ["ATHENA_OUTPUT"]
# Five sales analytics queries
QUERIES = {
"sales_summary": """
SELECT
SUM(total_amount) AS total_revenue,
COUNT(DISTINCT transaction_id) AS total_transactions,
SUM(quantity) AS total_units_sold,
AVG(total_amount) AS average_transaction_value
FROM raw_sales_data
""",
"revenue_by_branch": """
SELECT
branch,
SUM(total_amount) AS total_revenue,
COUNT(DISTINCT transaction_id) AS total_transactions,
SUM(quantity) AS total_units_sold
FROM raw_sales_data
GROUP BY branch
ORDER BY total_revenue DESC
""",
"revenue_by_category": """
SELECT
category,
SUM(total_amount) AS total_revenue,
SUM(quantity) AS total_units_sold,
COUNT(DISTINCT transaction_id) AS total_transactions
FROM raw_sales_data
GROUP BY category
ORDER BY total_revenue DESC
""",
"monthly_sales_trend": """
SELECT
date_format(
date_parse(transaction_date, '%Y-%m-%d'),
'%Y-%m'
) AS sales_month,
SUM(total_amount) AS total_revenue,
COUNT(DISTINCT transaction_id) AS total_transactions,
SUM(quantity) AS total_units_sold
FROM raw_sales_data
GROUP BY
date_format(
date_parse(transaction_date, '%Y-%m-%d'),
'%Y-%m'
)
ORDER BY sales_month
""",
"top_five_products": """
SELECT
product_name,
SUM(total_amount) AS total_revenue,
SUM(quantity) AS total_units_sold,
COUNT(DISTINCT transaction_id) AS total_transactions
FROM raw_sales_data
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 5
"""
}
def lambda_handler(event, context):
submitted_queries = []
try:
# Process the S3 event
for record in event.get("Records", []):
bucket = record["s3"]["bucket"]["name"]
key = urllib.parse.unquote_plus(
record["s3"]["object"]["key"]
)
# Only process CSV files in the raw-sales-data folder
if not key.startswith("raw-sales-data/") or not key.endswith(".csv"):
print(f"Skipping file: {key}")
continue
print(f"Processing uploaded file: s3://{bucket}/{key}")
# Submit all five queries to Athena
for query_name, query_string in QUERIES.items():
response = athena.start_query_execution(
QueryString=query_string,
QueryExecutionContext={
"Database": DATABASE_NAME
},
WorkGroup=ATHENA_WORKGROUP,
ResultConfiguration={
"OutputLocation": ATHENA_OUTPUT
}
)
query_id = response["QueryExecutionId"]
submitted_queries.append({
"query_name": query_name,
"query_execution_id": query_id
})
print(f"Submitted {query_name}: {query_id}")
print("All queries submitted successfully.")
return {
"statusCode": 200,
"body": json.dumps({
"status": "SUCCESS",
"submitted_query_count": len(submitted_queries),
"queries": submitted_queries
})
}
except Exception as error:
print(f"Error processing sales data: {str(error)}")
raise errorI tested the function manually before connecting it to S3. The test completed successfully, and Lambda submitted all five queries to Athena. This confirmed that the function and its permissions were working as expected.
6. Configuring the S3 Event Notification
After setting up the Lambda function, I connected it to Amazon S3 so that the analysis could run automatically whenever a new sales file was uploaded.
In the S3 bucket, I created an event notification named SalesAnalyticsCSVUploadTrigger. I set it up to listen for object-created events, with the following filters:
- Prefix: raw-sales-data/
- Suffix: .csv
I then selected SalesAnalyticsAutomationFunction as the destination.
With this configuration, uploading a new CSV file to the raw-sales-data/ folder triggers the Lambda function. The function then submits SQL queries to Athena, which processes the sales data and saves the results in the athena-results/ folder.
This completed the automation setup. The next step was to upload a new test file and confirm that the entire workflow worked as expected.
7. Testing and Results
I uploaded a small test CSV file named retail_sales_test.csv into the raw-sales-data/ folder in my S3 bucket. The upload triggered the SalesAnalyticsAutomationFunction Lambda function automatically.
I then checked the function’s execution logs in Amazon CloudWatch. The logs showed that Lambda had detected the uploaded file and submitted all queries to Amazon Athena.
Next, I opened Athena and checked the query execution history. All five queries had completed successfully. I also checked the athena-results/ folder in S3 and confirmed that the query result files had been saved there.
Limitations and Future Improvements
Although the automation worked as expected, there are a few things I would improve in a future version.
The project used a sample dataset with a relatively small number of records. A production system would need to handle larger datasets and more frequent uploads.
A future improvement would be to build a dashboard using Amazon QuickSight or another visualisation tool. This would allow a business owner to view sales trends, compare and identify top-selling products without opening Athena or checking the saved query results in S3.
These improvements would make the solution more useful for a real retail business and better suited to a larger amount of sales data.
Conclusion
Retail businesses need a simple way to understand their sales performance without having to analyse CSV files manually or write SQL queries every time new data becomes available.
In this project, I built a serverless sales analytics pipeline that addresses this problem by bringing together Amazon S3, AWS Glue, Amazon Athena and AWS Lambda.
The result is a solution that reduces repetitive work and makes it easier to generate sales reports from newly uploaded data. Although this project used a sample dataset and does not yet include a visual dashboard, it provides a foundation that could be extended into a more complete retail analytics platform.
Happy building!
Follow me on LinkedIn for more demos and networking Kevin Kiruri
