Data Science Track Capstone: AI-Augmented Analytics Case Study
Deliver an end-to-end data science case study using AI tools responsibly. Clean data, run SQL audits, design dashboards, and document verification steps for your portfolio.
Domain / Environment
Retail Analytics / Conda VM
Difficulty
Advanced (4/5)
Track Outcome
Data Science Track Capstone
Deliverables
GitHub Portfolio Case Study & Verified Source Code
1. System Architecture & AI Verification Loop
The diagram below displays the AI-augmented development verification loop. AI-generated code must go through a manual audit (reviewing database schemas, testing edge cases, and checking query plans) before being merged into the analytics pipeline.
2. Part 1: Step-by-Step Action Items & Key Execution Steps
STEP 1
Activate Python Virtual Environment
Point the terminal execution environment to the course conda sandbox environment.
$ conda activate ds_ai_ml
This targets active python libraries to the isolated virtual sandbox.
STEP 2
Create Project Folders inside Linux VM
Create a dedicated folder for the project files inside your guest VM home folder directory.
$ mkdir -p ~/Projects/capstone_ds && cd ~/Projects/capstone_ds
This sets up the working directory layout for the capstone code.
STEP 3
Initialize Git Repository
Create a git repository to version control and publish your capstone project.
$ git init
This initializes an empty git repository in the workspace.
STEP 4
Create data wrangling script file in VS Code
Launch VS Code and create the data cleaning script file.
Launch VS Code via terminal "code ." -> New File -> Type: clean_data.py -> Paste Python code -> Save file
This registers the data cleaning script in `clean_data.py` to prepare raw transaction logs.
STEP 5
Run and Verify AI-Generated Code
Execute the script and verify that the output data matches expectations.
$ python clean_data.py
This runs the wrangling script, prints output rows, and verifies there are no missing values or incorrect data types.
STEP 6
Write Portfolio README
Write a comprehensive project README that documents the business problem, data model, and AI verification steps.
New File -> Type: README.md -> Paste project write-up -> Save file
A well-documented `README.md` is critical to display your capstone project in your portfolio.
STEP 7
Commit files to Git repository
Stage and commit the project files to your git repository history logs.
$ git add . && git commit -m "Initialize capstone data science project and verification docs"
This saves the files and registers a commit in your git history.
3. Capstone Operational Pipeline Flow
The flowchart below outlines the capstone analytics pipeline. It details the steps from raw data ingestion and SQLite storage to SQL audits, verification checks, and dashboard visual layouts.
4. Part 2: Complete Deliverable Assets & Production Templates
To run the capstone project, we need the raw customer dataset, the Python cleaning script, and the portfolio README file. Below are the consolidated templates.
Production templates
1. Sample Dataset (Save as ~/Projects/capstone_ds/customer_sales.csv):
2. Data Cleaning and SQL Load Script (Save as ~/Projects/capstone_ds/clean_data.py):
import pandas as pd
import sqlite3
defmain():
print("=== Loading and Cleaning Raw Transaction Logs ===")
df = pd.read_csv("customer_sales.csv")
# Clean columns and handle missing values
df["Purchase_Date"] = pd.to_datetime(df["Purchase_Date"])
df["Amount"] = df["Amount"].fillna(0.0)
# Check data types
print(df.dtypes)
# Load data to SQLite database
print("\n=== Loading cleaned records to SQLite DB ===")
conn = sqlite3.connect("retail_sales.db")
df.to_sql("sales", conn, if_exists="replace", index=False)
# Verify row count
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM sales")
count = cursor.fetchone()[0]
print(f"Verification check: loaded {count} rows successfully.")
conn.close()
print("=== Data loading and verification complete! ===")
if __name__ == "__main__":
main()
3. SQL Queries Script (Save as ~/Projects/capstone_ds/queries.sql):
-- Calculate Total Sales and Average Rating by Product Category
SELECT
Product_Category,
COUNT(Transaction_ID) as Total_Orders,
SUM(Amount) as Total_Revenue,
ROUND(AVG(Rating), 2) as Avg_Rating
FROM
sales
GROUP BY
Product_Category
ORDER BY
Total_Revenue DESC;
4. Portfolio README (Save as ~/Projects/capstone_ds/README.md):
# AI-Augmented Retail Analytics Case Study
## Project Overview
This capstone case study analyzes customer transaction trends across multiple product categories to optimize marketing campaigns.
## Key Features
- **Data Ingestion**: Cleaned raw transactions CSV file using Pandas.
- **SQL Aggregations**: Audited and summarized sales using SQLite.
- **Explainable Analytics**: Explored trend components and seasonal variance.
## AI Usage and Manual Verification Docs
To build this pipeline, coding assistants were used to generate the initial data wrangling scripts and SQL query definitions.
The output was verified using the following audit steps:
1. **Row Count Validation**: Confirmed matching row counts (10 entries) between raw files and SQL tables.
2. **Schema Audit**: Verified correct column data types (dates parsed as datetime and monetary values as floats).
3. **Query Audits**: Run query plans to ensure no slow queries or incorrect grouping filters were introduced.
5. Deliverables Summary
Verify that the following configurations and outputs exist inside your project workspace.
Created Files / Templates
~/Projects/capstone_ds/customer_sales.csv - Raw transaction logs dataset.