ETL Pipeline with Cloud Integration & Automated Reporting

A modular, production-grade Python ETL pipeline that demonstrates end-to-end data engineering — from raw CSV extraction through transformation, SQLite persistence, visualisation, and optional Azure cloud integration and email delivery.

---

✨ Features

Feature	Status
CSV Extraction	✅
Azure SQL Extraction	✅ (optional)
Data Cleaning & Transformation	✅
SQLite Loading	✅
Processed CSV Export	✅
6-Chart Analytics Report (HTML)	✅
Azure Blob Storage Upload	✅ (optional)
Gmail Email Delivery	✅ (optional)
Structured Logging	✅
.env Configuration	✅

---

🚀 Quick Start

1. Clone / navigate to the project

cd etl-project

2. Create and activate a virtual environment

python -m venv venv
source venv/bin/activate        # Linux / macOS
# venv\Scripts\activate         # Windows

3. Install dependencies

pip install -r requirements.txt

4. Configure the pipeline

cp .env.example .env
# Edit .env with your preferred text editor

5. Run the pipeline

python run_etl.py

---

📁 Project Structure

etl-project/
│
├── run_etl.py                  # Main orchestrator
│
├── data/
│   ├── dummy_data.csv          # 200-row sample sales dataset
│   └── etl.db                  # Generated SQLite database (after run)
│
├── reports/
│   ├── report.html             # Generated HTML dashboard (after run)
│   ├── summary.png             # Legacy summary chart (after run)
│   ├── processed_data.csv      # Cleaned data export (after run)
│   └── charts/                 # Individual chart PNGs (after run)
│       ├── distribution.png
│       ├── category_revenue.png
│       ├── category_share.png
│       ├── monthly_trend.png
│       ├── region_revenue.png
│       └── top_reps.png
│
├── etl/
│   ├── __init__.py
│   ├── extractor.py            # CSV reading with encoding detection
│   ├── transformer.py          # 9-step data cleaning pipeline
│   ├── loader.py               # SQLite loading + CSV export
│   ├── viz.py                  # 6 chart types + self-contained HTML report
│   ├── emailer.py              # SMTP email with attachments
│   ├── azure_loader.py         # Azure Blob Storage uploader
│   ├── azure_sql_reader.py     # Azure SQL Server reader
│   └── templates/
│       └── email_template.html
│
├── logs/
│   └── etl.log                 # Detailed debug log (after run)
│
├── requirements.txt
├── .env.example
├── .gitignore
└── README.md

---

⚙️ Configuration Reference

All settings are read from the .env file. Copy .env.example to .env and adjust as needed.

Core Settings

Variable	Default	Description
CSV_PATH	data/dummy_data.csv	Path to the input CSV file
DB_PATH	data/etl.db	SQLite database path
TABLE_NAME	sales_data	SQLite table name
REPORT_DIR	reports	Output directory for report & charts
LOG_LEVEL	INFO	Logging verbosity (DEBUG/INFO/WARNING)

Email (Gmail SMTP)

Variable	Description
SEND_EMAIL	Set to true to enable email delivery
SMTP_USER	Your Gmail address
SMTP_PASS	Gmail App Password (16 characters, not your login password)
TO_ADDRESS	Recipient email address
EMAIL_SUBJECT	Subject line

Getting a Gmail App Password:
Google Account → Security → 2-Step Verification → App Passwords

Azure SQL Server

Variable	Description
READ_FROM_AZURE_SQL	Set to true to extract from Azure SQL instead of CSV
AZURE_SQL_SERVER	e.g. myserver.database.windows.net
AZURE_SQL_DATABASE	Database name
AZURE_SQL_USERNAME	SQL login (e.g. user@myserver)
AZURE_SQL_PASSWORD	SQL login password
AZURE_SQL_TABLE	Table to read
AZURE_SQL_DRIVER	ODBC driver (default: ODBC Driver 17 for SQL Server)

Azure Blob Storage

Variable	Description
UPLOAD_TO_AZURE	Set to true to enable Azure upload
AZURE_STORAGE_CONNECTION_STRING	Found in Azure Portal → Storage Account → Access Keys
AZURE_CONTAINER_NAME	Blob container (default: etl-data)
AZURE_BLOB_NAME	Destination blob filename (default: processed_data.csv)

---

🔄 Pipeline Execution Steps

CSV / Azure SQL
      │
      ▼
 1. Extract          ─── extractor.py / azure_sql_reader.py
      │
      ▼
 2. Transform        ─── transformer.py  (9-step cleaning pipeline)
      │
      ▼
 3. Load → SQLite    ─── loader.py
      │
      ├──────────────────────────────────────┐
      ▼                                      ▼
 4. Report / Charts  ─── viz.py        Azure Blob Upload ─── azure_loader.py
      │                                      (optional)
      ▼
 5. Email Report     ─── emailer.py
      (optional)

---

📊 Transformation Pipeline Details

The transformer.py module applies these steps in order:

1. Column normalisation — all headers converted to snake_case
2. Required column validation — asserts date and amount are present
3. Duplicate removal — exact duplicate rows dropped and logged
4. Numeric type coercion — amount, discount, units_sold cast to float64
5. Date parsing — date column parsed to datetime64
6. String standardisation — strip whitespace + title-case
7. Missing value handling — numeric → median fill; strings → 'Unknown'; invalid dates → row dropped
8. Derived columns — revenue, discounted_amount, year, month, quarter, month_name
9. Index reset — clean zero-based integer index

---

📈 Report Contents

The generated reports/report.html is a self-contained HTML dashboard that includes:

• Key metrics panel — total records, total revenue, average transaction, top category, completion rate, average discount
• Revenue Distribution — histogram of transaction amounts
• Revenue by Category — horizontal bar chart
• Category Share — donut chart
• Monthly Revenue Trend — time-series line chart
• Revenue by Region — vertical bar chart
• Top Sales Representatives — ranked horizontal bar chart
• Descriptive Statistics — numeric summary table

All charts are embedded as base64 PNGs — no internet connection required to view the report.

---

📋 Logs

Every pipeline run writes to:

• Console — at the configured LOG_LEVEL
• logs/etl.log — always at DEBUG level (full trace)

---

🛡️ Error Handling

Error Type	Behaviour
Missing CSV file	Pipeline aborts with clear message
Invalid data types	Coerced to NaN/NaT; logged as warnings
Duplicate records	Silently removed; count logged
Azure connection failure	Logged as error; pipeline continues
SMTP authentication failure	Logged as error; pipeline continues
Missing env variables for optional steps	Logged; step skipped gracefully

---

🧩 Extending the Pipeline

• New data source: Add a reader in etl/ and call it from run_etl.py.
• New transformation: Add a step function in transformer.py and call it from transform().
• New chart: Add a _chart_* function in viz.py and embed it in _HTML_TEMPLATE.
• New output: Add a loader in etl/ and register it in run_etl.py.

---

📦 Dependencies

pandas          ≥ 2.0
numpy           ≥ 1.24
SQLAlchemy      ≥ 2.0
matplotlib      ≥ 3.7
jinja2          ≥ 3.1
python-dotenv   ≥ 1.0
azure-storage-blob ≥ 12.19   (optional)
pyodbc          ≥ 5.0        (optional)