A comprehensive retail intelligence system that simulates modern enterprise ERP capabilities. This project demonstrates dimensional modeling, relational database design, ETL processing, and interactive business analytics.
This pipeline integrates transactional data from three channels:
- Brick-and-Mortar POS: In-store sales transactions
- Shopify: E-commerce web sales
- Amazon: Marketplace sales
The system standardizes multi-source data, loads it into an optimized SQL database, and provides real-time operational insights through an interactive dashboard.
- Realistic mock data across 3 retail channels
- Intentional anomalies (mixed date formats, currencies, duplicates) to simulate real-world scenarios
- ~5,000+ transactions across 90 days
- Multi-source data extraction
- Data standardization and normalization
- Quality validation and deduplication
- Unified fact table creation
- Star schema with optimized dimensional modeling
- Fact table:
sales_transactions - Dimension tables:
products,dates,stores,channels - Strategic indexing for fast query performance
- Real-time operational KPIs
- Channel performance analytics
- Revenue trends and forecasting
- Inventory alerts (configurable threshold)
- Interactive filters and drill-downs
- Python 3.8+ — Data processing with Pandas
- PostgreSQL/SQLite — Relational database
- SQLAlchemy — ORM for database operations
- Streamlit — Interactive dashboard framework
- Faker — Realistic mock data generation
├── data/
│ ├── raw/ # Generated mock data (CSVs)
│ ├── processed/ # Transformed data
│ └── schemas/ # SQL schema definitions
├── scripts/
│ ├── generate_mock_data.py # Data generation
│ ├── etl_pipeline.py # ETL logic
│ └── db_setup.py # Database initialization
├── dashboard/
│ └── app.py # Streamlit dashboard
├── config.py # Configuration settings
├── requirements.txt # Python dependencies
├── .env.example # Environment variables template
└── README.md # This file
- Python 3.8 or higher
- pip or conda
- Clone the repository
git clone https://github.com/yourusername/Retail-intelligence-pipeline.git
cd Retail-intelligence-pipeline- Create a virtual environment
python -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate- Install dependencies
pip install -r requirements.txt- Configure environment
cp .env.example .env
# Edit .env to configure database settings (optional for SQLite)python scripts/generate_mock_data.pyThis creates three CSV files in data/raw/:
pos_raw.csvshopify_raw.csvamazon_raw.csv
python scripts/db_setup.pyCreates optimized tables with proper indexing in your configured database.
python scripts/etl_pipeline.pyTransforms raw data and loads it into the database.
streamlit run dashboard/app.pyOpens interactive dashboard at http://localhost:8501
| Column | Type | Description |
|---|---|---|
| transaction_id | INTEGER | Primary Key |
| channel_id | INTEGER | Foreign Key → dim_channels |
| product_id | INTEGER | Foreign Key → dim_products |
| date_id | INTEGER | Foreign Key → dim_dates |
| store_id | INTEGER | Foreign Key → dim_stores |
| quantity | INTEGER | Units sold |
| unit_price | DECIMAL | Price per unit |
| total_amount | DECIMAL | Total transaction value |
| order_status | VARCHAR | Completed/Cancelled/Refunded |
- dim_channels: channel_id, channel_name
- dim_products: product_id, sku, product_name, category
- dim_dates: date_id, date, year, month, quarter
- dim_stores: store_id, store_name, location, channel_id
- Composite index on
(channel_id, date_id)for fast aggregations - Index on
product_idfor inventory lookups - Index on
order_statusfor filtering
- Total Revenue (by channel)
- Transaction Count
- Average Order Value
- Inventory Alerts Count
- Channel Mix: Revenue distribution pie chart
- Daily Trend: Line chart of revenue over time
- Cancellation Rate: Bar chart by channel
- Top Products: Table of best sellers
- Inventory Alerts: Products below threshold
- Date range selector
- Channel filter
- Adjustable inventory threshold
RAW DATA (3 Channels)
↓
EXTRACT: Load CSV files
↓
TRANSFORM:
• Standardize date formats (YYYY-MM-DD)
• Normalize product IDs (all → SKU format)
• Convert currencies (→ base currency)
• Handle nulls and duplicates
• Validate data types
↓
LOAD: Insert into SQL database
↓
UNIFIED DATA SOURCE
↓
DASHBOARD: Real-time analytics
✅ Dimensional Modeling — Properly designed star schema
✅ Relational Database Design — Normalized tables with referential integrity
✅ ETL Best Practices — Robust data pipeline with validation
✅ Data Consistency — Multi-source standardization
✅ Responsive Frontend — Interactive Streamlit dashboard
✅ Enterprise Architecture — Scalable, maintainable codebase
Edit config.py to customize:
# Mock Data
NUM_TRANSACTIONS_POS = 2000
NUM_TRANSACTIONS_SHOPIFY = 1500
NUM_TRANSACTIONS_AMAZON = 1800
DATE_RANGE_DAYS = 90
# Inventory Settings
INVENTORY_THRESHOLD = 50
# Database
DATABASE_URL = "sqlite:///./data/retail_intelligence.db"No configuration needed. Database file stored at data/retail_intelligence.db
Update .env:
DB_TYPE=postgresql
DB_USER=postgres
DB_PASSWORD=your_password
DB_HOST=localhost
DB_PORT=5432
DB_NAME=retail_intelligence
Error: Module not found
pip install -r requirements.txtError: Database connection failed
- Verify PostgreSQL is running (if using PostgreSQL)
- Check
.envcredentials - For SQLite, ensure
data/directory exists
Dashboard not loading
streamlit run dashboard/app.py --logger.level=debug- Real-time data ingestion from live APIs
- Predictive analytics (demand forecasting)
- Automated data quality reporting
- Multi-warehouse support
- Cloud deployment (AWS/Azure)
- Advanced filtering and drill-down capabilities
- Export functionality (PDF, Excel)
See LICENSE file for details.
Your Name — jdatjedd
For issues or questions, please open a GitHub issue or contact the maintainer.
Last Updated: July 2026
Status: Development (Phase 1.1 Complete)