ecommerce-data-pipeline

Processing 100k+ e-commerce transactions through an end-to-end ETL pipeline. Built with Pandas for data transformation, MySQL for relational modeling, and Power BI for dynamic BI dashboards.

Project Architecture & Overview

This repository documents the end-to-end architecture of a batch-processing ETL pipeline and business intelligence solution. Processing over 100,000 highly relational, anonymized e-commerce transactions from the Olist dataset, this project transforms fragmented raw flat files into a structured, query-optimized relational database designed specifically for downstream BI consumption and executive reporting.

Technical Implementation by Phase

Phase 1: Data Ingestion & Transformation (Python / Pandas)

• Programmatic ETL: Developed a robust Python script to ingest multiple raw CSV datasets into Pandas DataFrames, handling memory optimization and schema validation.
• Temporal Engineering: Executed complex datatype casting, specifically parsing string-based order timestamps into standard datetime64 objects to enable accurate time-series analysis and SLA tracking.
• Data Cleansing & Normalization: Engineered logic to handle missing variables (imputing or isolating null delivery dates) and standardized categorical dimensions by dynamically mapping native Portuguese product taxonomies into English.

Phase 2: Relational Data Modeling & Storage (MySQL)

• Schema Design (3NF): Engineered a strictly typed, normalized relational schema (Third Normal Form) deployed on a MySQL engine.
• Fact & Dimension Modeling: Segregated data into localized fact tables (orders, order_items, payments) and descriptive dimension tables (customers, products).
• Automated Loading: Leveraged Python's SQLAlchemy ORM and pymysql driver to automate the bulk insertion of cleansed DataFrames into the database, enforcing strict referential integrity via Primary Key (PK) and Foreign Key (FK) constraints.

Phase 3: Analytical Querying & Business Logic (SQL)

• Advanced Aggregations: Developed highly optimized SQL queries to extract actionable business intelligence, utilizing Common Table Expressions (CTEs) to simplify complex, multi-layered JOIN operations.
• Window Functions: Implemented partition-based window functions (e.g., LAG(), OVER()) to compute temporal metrics such as Month-over-Month (MoM) revenue growth without relying on BI-layer processing.
• Logistics Tracking: Engineered conditional logic (CASE WHEN) to calculate supply chain efficiency, measuring actual delivery times against estimated delivery Service Level Agreements (SLAs).

Phase 4: Semantic Layer & Visualization (Power BI)

• Star-Schema Modeling: Constructed a high-performance semantic data model within Power BI by establishing active 1-to-many relationships between the central fact tables and surrounding dimensions.
• DAX Engineering: Authored custom Data Analysis Expressions (DAX) measures to compute dynamic, cross-filterable KPIs including Gross Merchandise Value (GMV), Average Order Value (AOV), and total transaction volume.
• UI/UX Optimized Dashboard: Designed a dark-themed, high-contrast executive dashboard utilizing spatial mapping for geographic revenue distribution, clustered bar charts for categorical performance, and time-series line charts featuring dynamic hierarchy drill-downs (Year $\rightarrow$ Quarter $\rightarrow$ Month).

Executive Dashboard