SQL + Python Data Automation

Portfolio project focused on Application Support, SQL troubleshooting and report automation. It validates transaction data from CSV, loads it into SQLite, runs diagnostic SQL queries and exports operational reports.

What this project demonstrates

• Data validation and error handling
• ETL automation with Python and Pandas
• SQL queries for operational analysis
• SQLite database creation and indexing
• Logging for troubleshooting and traceability
• Incident-oriented reports for failed and pending transactions
• Clear execution and support documentation

Workflow

CSV input
   |
   v
Python validation and ETL
   |
   v
SQLite database
   |
   v
SQL diagnostic queries
   |
   v
CSV operational reports + logs

Project structure

sql-python-data-automation/
├── data/
│   └── raw_transactions.csv
├── sql/
│   └── analysis.sql
├── python/
│   ├── etl.py
│   └── report.py
├── output/
│   └── .gitkeep
├── logs/
│   └── .gitkeep
├── .gitignore
├── requirements.txt
└── README.md

The SQLite database, reports and logs are generated locally and excluded from Git.

Requirements

• Python 3.10 or newer
• pip

Installation

Windows PowerShell

git clone https://github.com/SEBAZUMELZU/sql-python-data-automation.git
cd sql-python-data-automation

python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

If PowerShell blocks activation, use:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\.venv\Scripts\Activate.ps1

macOS or Linux

git clone https://github.com/SEBAZUMELZU/sql-python-data-automation.git
cd sql-python-data-automation

python3 -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt

Usage

Run the ETL process:

python python/etl.py

Generate reports:

python python/report.py

Generated files:

data/transactions.db
output/user_summary.csv
output/category_summary.csv
output/incident_candidates.csv
output/daily_completed_totals.csv
logs/etl.log
logs/report.log

SQL analysis

The project produces four reports:

Report	Purpose
user_summary.csv	Transaction totals and completed amount by user
category_summary.csv	Volume and amount grouped by category
incident_candidates.csv	Failed and pending transactions requiring support review
daily_completed_totals.csv	Daily completed transaction totals

Example diagnostic query:

SELECT
    transaction_id,
    user_name,
    status,
    CASE
        WHEN status = 'failed' THEN 'P2 - investigate failed transaction'
        WHEN status = 'pending' THEN 'P3 - verify pending transaction'
    END AS support_action
FROM transactions
WHERE status IN ('failed', 'pending');

Validation and error handling

The ETL verifies:

• Required columns
• Numeric transaction and user identifiers
• Valid dates and amounts
• Duplicate transaction identifiers
• Negative amounts
• Empty user or category values
• Allowed statuses: completed, pending, failed

Errors are displayed in the terminal and written to logs/etl.log.

Troubleshooting

ModuleNotFoundError: No module named 'pandas'

Activate the virtual environment and install dependencies:

python -m pip install -r requirements.txt

Input file not found

Confirm this file exists:

data/raw_transactions.csv

You can also provide another path:

python python/etl.py --input path/to/file.csv

Database not found

Run the ETL before generating reports:

python python/etl.py
python python/report.py

A CSV validation error appears

Review logs/etl.log. Check column names, duplicate IDs, invalid statuses, dates and negative amounts.

Reports are not generated

Review logs/report.log and confirm:

• data/transactions.db exists
• the transactions table was created
• sql/analysis.sql contains named query blocks

Application Support scenario

A support analyst receives a report that some transactions have not completed. This project provides a repeatable diagnostic process:

1. Validate the source data.
2. Load a consistent local database.
3. Identify failed and pending records with SQL.
4. Assign an initial support priority.
5. Export evidence for investigation or escalation.
6. Preserve execution logs for traceability.

Possible improvements

• Unit tests with pytest
• Scheduled execution
• Dashboard visualization
• Email notification for failed transactions
• Database connection to PostgreSQL or MySQL
• Ticket creation through an API

Author

Sebastián Zumelzu
Application Support / IT Operations profile with a technical background in programming, SQL, Linux and enterprise systems.