This project implements an end-to-end ETL (Extract, Transform, Load) data pipeline for e-commerce order data.
The pipeline extracts raw order data from CSV files, performs data cleaning and transformation using Python and Pandas, validates data quality, loads the processed data into PostgreSQL, and performs SQL-based business analysis.
Automated tests are implemented using Pytest to validate transformation and data-quality logic.
Raw CSV Data ↓ Python Extraction ↓ Pandas Transformation ↓ Data Quality Validation ↓ Processed CSV ↓ PostgreSQL Database ↓ SQL Analytics
- Python
- Pandas
- PostgreSQL
- SQL
- Psycopg2
- Pytest
- Git
- GitHub
- VS Code
ecommerce-data-pipeline/
- data/raw/ - Raw source datasets
- data/processed/ - Cleaned datasets
- src/extract.py - Data extraction
- src/transform.py - Data transformation
- src/quality_checks.py - Data validation
- src/load.py - PostgreSQL loading
- src/pipeline.py - ETL orchestration
- sql/analysis.sql - Business analytics queries
- tests/ - Automated tests
- requirements.txt - Python dependencies
The extraction layer reads raw e-commerce order data from CSV files into a Pandas DataFrame.
The transformation layer:
- Removes duplicate order IDs
- Converts order dates to datetime values
- Converts quantity and unit price to numeric values
- Handles invalid records
- Calculates total order amount
- Produces a cleaned dataset
The pipeline validates:
- Empty datasets
- Missing order IDs
- Missing customer IDs
- Duplicate order IDs
- Invalid quantities
- Invalid unit prices
- Invalid total amounts
Validated records are loaded into a PostgreSQL database for downstream analytics.
The project includes SQL queries for:
- Total orders
- Total revenue
- Revenue by country
- Revenue by payment method
- Product sales performance
- Daily sales performance
Pytest is used to validate transformation and data-quality logic.
Run tests with:
python -m pytest
Expected result:
3 passed
Install dependencies:
python -m pip install -r requirements.txt
Run the ETL pipeline:
python -m src.pipeline
Run automated tests:
python -m pytest
Run SQL analytics:
psql ecommerce_db -f sql/analysis.sql
- ETL pipeline development
- Python data engineering
- Pandas data transformation
- Data quality validation
- PostgreSQL database integration
- SQL analytics
- Automated testing
- Modular software design
Future versions of the project could include:
- Apache Airflow orchestration
- Docker containerization
- Cloud deployment
- Larger datasets
- Logging and monitoring
- CI/CD using GitHub Actions
- Incremental data loading