Configurable Python ETL Pipeline with Audit Logging for Multi-Channel E-commerce Analytics
Configurable Python ETL Pipeline with Audit Logging for Multi-Channel E-commerce Analytics
Embedded Files
Built an ETL Pipeline using Python to clean messy and inconsistent CSV Dataset then export to Power BI for Dashboard Analysis
Built an ETL Pipeline using Python to clean messy and inconsistent CSV Dataset then export to Power BI for Dashboard Analysis
📖 PROJECT INFORMATION
📖 PROJECT INFORMATION
Project Overview
Project Overview
This project demonstrates the design and implementation of a configurable ETL (Extract, Transform, Load) pipeline that automates the cleaning, validation, and preparation of raw e-commerce datasets for business reporting and analytics.
The solution processes multiple related CSV datasets representing customers, products, orders, inventory, and warehouses. Using configurable JSON schemas and business rules, the pipeline standardizes data formats, validates records, detects data quality issues, applies fuzzy matching, and generates comprehensive audit reports before producing clean datasets for analysis.
The processed data is then modeled in Power BI to create interactive dashboards that provide insights into sales performance, inventory management, and ETL audit metrics.
Business Scenario
Business Scenario
A growing retail company operates across multiple sales channels, including Shopify, Shopee, Lazada, Amazon, TikTok Shop, and physical retail stores (POS). Because each platform exports data using different formats and standards, the organization receives inconsistent datasets containing missing values, duplicate records, formatting inconsistencies, invalid identifiers, and misspelled values.
To improve reporting accuracy and reduce manual data preparation, the company requires an automated ETL pipeline capable of cleaning, validating, and standardizing incoming datasets while generating audit logs that provide transparency into every transformation performed during the ETL process.
Project Objectives
Project Objectives
This project aims to:
Develop a reusable and configurable ETL pipeline for processing multiple datasets.
Automate common data cleaning and validation tasks using configurable business rules.
Improve data quality by detecting and correcting inconsistencies.
Generate audit logs and ETL summaries to support data governance and troubleshooting.
Produce clean, analysis-ready datasets for business intelligence reporting.
Demonstrate practical ETL development techniques commonly used in real-world data engineering and analytics projects.
⚙️ TECHNICAL DETAILS
⚙️ TECHNICAL DETAILS
ETL Workflow
ETL Workflow
Raw CSV Files
↓
Raw CSV Files
↓
Extract
↓
Validation
↓
Cleaning
↓
Transformation
↓
Audit
↓
Output
↓
Power BI Dashboard
1. Extract
1. Extract
Purpose
Load all raw CSV datasets from the configured source directory.
Activities
Read raw CSV files.
Verify file availability.
Prepare datasets for transformation.
2. Validation
2. Validation
Purpose
Ensure each dataset matches the expected schema before processing.
Checks
Required columns
Primary key format
Missing columns
Invalid identifiers
3. Cleaning
3. Cleaning
Purpose
Standardize raw data into consistent formats.
Operations
Trim whitespace
Normalize null values
Standardize text
Format dates
Clean numeric values
Standardize email addresses
4. Transformation
4. Transformation
Purpose
Improve data quality using business rules.
Operations
Fuzzy matching
Duplicate removal
Business rule validation
5. Audit
5. Audit
Purpose
Track every transformation performed by the ETL pipeline.
Outputs
ETL Summary
Transformation Log
Rejected Records
6. Output
6. Output
Purpose
Produce clean datasets ready for reporting and analysis.
Outputs
Clean CSV files
Power BI dashboards
Key Features
Key Features
Configurable JSON-based ETL workflow
Automated processing of multiple related CSV files
Dynamic schema validation
Primary key validation
Duplicate detection and removal
Null value normalization
Text, numeric, currency, and email standardization
Date parsing and formatting
Fuzzy matching for inconsistent values
Invalid record rejection
Transformation logging
ETL summary generation
Power BI dashboard integration
Technologies Used
Technologies Used
Python
Core ETL pipeline development
Pandas
Data extraction, transformation, and loading
RapidFuzz
Fuzzy matching and value correction
JSON
Configurable schemas and validation rules
Power BI
Data modeling and dashboard visualization
Git & GitHub
Version control and project management
🖼️IMPLEMENTATION GALLERY
🖼️IMPLEMENTATION GALLERY
📊 Sales Dashboard Datasets
📊 Sales Dashboard Datasets
These datasets are used to build the Sales Dashboard in Power BI.
The ETL pipeline processes five related datasets representing customers, products, orders, inventory, and warehouses. These datasets are cleaned, validated, and transformed before being loaded into Power BI for sales and inventory analysis.
The ETL pipeline processes five related datasets representing customers, products, orders, inventory, and warehouses. These datasets are cleaned, validated, and transformed before being loaded into Power BI for sales and inventory analysis.
📁 customers.csv
📁 customers.csv
Purpose:
Stores customer information used for sales analysis and order processing.
📦 products.csv
📦 products.csv
Purpose:
Stores product information including categories and pricing used for sales reporting and inventory management.
📦 inventory.csv
📦 inventory.csv
Purpose:
Tracks inventory levels for each product across warehouses to support stock monitoring and replenishment analysis.
🏢 warehouses.csv
🏢 warehouses.csv
Purpose:
Stores warehouse information used for inventory allocation and order fulfillment.
🛒 orders.csv
🛒 orders.csv
Purpose:
Stores customer purchase transactions across multiple sales channels and links customers, products, and warehouses for sales reporting and analysis.
📸 RELATIONSHIP VIEW
📸 RELATIONSHIP VIEW
Purpose:
Illustrates the relationships between the Customers, Orders, Products, Inventory, and Warehouses datasets, enabling accurate filtering, aggregations, and cross-table analysis within Power BI. .
📊 MULTI-CHANNEL SALES & INVENTORY DASHBOARD
📊 MULTI-CHANNEL SALES & INVENTORY DASHBOARD
Purpose:
Provides an interactive view of sales performance, inventory status, and key business metrics for data-driven decision making.
📊 Audit Dashboard Datasets
📊 Audit Dashboard Datasets
These datasets are used to build the Sales Dashboard in Power BI.
The ETL pipeline generates audit datasets that record processing statistics, data transformations, and rejected records. These outputs improve transparency by allowing users to review data quality issues, validation results, and every transformation performed during the ETL process.
📄 etl_summary.csv
📄 etl_summary.csv
Purpose:
Provides a summary of ETL processing results for each dataset, including validation statistics and data quality metrics.
📝 transformation_log.csv
📝 transformation_log.csv
Purpose:
Records every transformation performed during the ETL process to provide a complete audit trail of data modifications.
Examples of Action:
Trim Spaces
Proper Case
Lowercase Email
Currency Formatting
Date Standardization
Fuzzy Match
Null Normalization
🚫rejected_records.csv
🚫rejected_records.csv
Purpose:
Stores records that failed validation checks and were excluded from the cleaned datasets, along with the reason for rejection.
Examples of Reason:
Invalid Primary Key
Duplicate Primary Key
Invalid Date
Required Value Missing
Invalid Foreign Key
Invalid Numeric Value
🚩 duplicates.csv
🚩 duplicates.csv
Purpose:
Records duplicate-related validation results, including exact duplicate removal and primary key violation detection, to support data quality monitoring and auditing.
Examples of Reason:
Invalid Primary Key
Duplicate Primary Key
Invalid Date
Required Value Missing
Invalid Foreign Key
Invalid Numeric Value
📊 ETL AUDIT & DATA QUALITY DASHBOARD
📊 ETL AUDIT & DATA QUALITY DASHBOARD
Purpose:
Monitors ETL execution by summarizing processing statistics, transformation activities, validation results, duplicate handling, and rejected records to support data quality monitoring and troubleshooting.
📸 RELATIONSHIP VIEW
📸 RELATIONSHIP VIEW
Purpose:
Illustrates the relationships between the ETL audit datasets, enabling cross-analysis of processing statistics, transformation activities, duplicate handling, and rejected records within the Audit Dashboard.
📁 Project Structure
📁 Project Structure
These are the folder structures and their purposes.
📁 config
📁 config
Purpose:
Stores JSON configuration files that define dataset schemas, validation rules, and fuzzy matching values used throughout the ETL pipeline.
Contents:
customers.json
products.json
orders.json
inventory.json
warehouses.json
fuzzy_values.json
📁 data
📁 data
Purpose:
Stores all datasets used throughout the ETL process, including raw input files, cleaned outputs, and audit reports.
Subfolders:
raw/
cleaned/
audit/
📁 etl
📁 etl
Purpose:
Contains the core ETL modules responsible for extracting, transforming, validating, auditing, and loading data.
Core Modules:
extract.py
transform.py
duplicate.py
audit.py
load.py
📁 tools
📁 tools
Purpose:
Contains helper utilities used for fuzzy matching, debugging, and inspecting dataset values during development and testing.
Utilities:
fuzzyMatch.py
inspect_column.py
print_inspect_column.py
📄 main.py
📄 main.py
Purpose:
Serves as the main entry point of the ETL pipeline by coordinating the extraction, transformation, validation, auditing, and loading processes.
📊 RESULTS & OUTCOMES
📊 RESULTS & OUTCOMES
Project Outcome
Project Outcome
The project successfully automated the extraction, validation, cleaning, and auditing of multiple e-commerce datasets using a configurable ETL pipeline. The solution produced standardized datasets for Power BI reporting while generating detailed audit logs to improve data quality monitoring, transparency, and troubleshooting.
Key Learnings
Key Learnings
Designing configurable ETL workflows
Implementing JSON-driven validation rules
Building reusable data cleaning functions
Managing relational datasets in Power BI
Creating audit logging for data quality monitoring
Applying fuzzy matching to improve data consistency
Future Improvements
Future Improvements
SQL Database integration
Incremental ETL
Cloud Storage
Docker
Airflow
Automated Scheduling
Unit Testing
📥 DOWNLOADS
📥 DOWNLOADS
📂 ETL Pipeline Project (ZIP)
📂 ETL Pipeline Project (ZIP)
Download the complete ETL Pipeline project, including configuration files, Python modules, datasets, and utilities.
💻 View Source Code (GitHub)
💻 View Source Code (GitHub)
View the complete source code on GitHub without downloading the project files.
📊 Power BI Dashboard
📊 Power BI Dashboard
Download the Power BI report (.pbix) containing both the Sales Dashboard and Audit Dashboard.
📁 Sample Datasets
📁 Sample Datasets
Download the raw input datasets, cleaned outputs, and audit reports generated during the ETL process.
All downloadable resources are provided for portfolio and educational purposes.
This project started as a simple ETL pipeline to clean five CSV files before importing them into Power BI. As I continued developing it, I expanded the scope by introducing configurable JSON validation rules, fuzzy matching with RapidFuzz, audit logging, duplicate handling, and separate Sales and Audit dashboards. It became much more than a data cleaning project, it evolved into a configurable ETL pipeline that demonstrates data validation, automation, reporting, and data quality monitoring.
Page updated
Google Sites
Report abuse