End-to-end data engineering pipeline that simulates e-commerce data, transforms it, and loads it into an analytics-ready PostgreSQL data warehouse. Demonstrates skills in:

• Data generation & simulation (Python, Faker)
• SQL & database design (PostgreSQL, data modeling)
• ETL development (Python, Pandas, SQLAlchemy)
• Data warehousing & analytics (Star schema, fact/dimension tables)
• Analytical queries for business insights

flowchart TD

    subgraph Bronze["Raw Data"]
        A["Fake Data Generator (scripts/generate_data.py)"]
    end

    subgraph Silver["Transformation / ETL"]
        B["Staging Tables (PostgreSQL)"]
        C["ETL Pipeline (pipeline.py)"]
    end

    subgraph Gold["Data Warehouse"]
        D["Fact Tables"]
        E["Dimension Tables"]
    end

    subgraph Analytics["Analytics"]
        F["SQL Queries"]
        G["Reports / Dashboards"]
    end

    A --> B
    B --> C
    C --> D
    C --> E
    D --> F
    E --> G

1. Create Database

CREATE DATABASE ecommerce_dw;

2. Create Tables

psql -d ecommerce_dw -f sql/create_tables.sql

3. Configure Project

mv config_template.py config.py

Update database credentials in config.py.

4. Generate Data

python scripts/generate_data.py

5. Run ETL Pipeline

python pipeline.py

6. Run Analytics Query fact and dimension tables for insights (sales trends, customer behavior, product performance).

Figure: SQL query result showing top 10 customers.

• Data Generation: Faker library, Python scripting
• SQL & Data Modeling: PostgreSQL, star schema, fact/dimension tables
• ETL & Pipeline Development: Python, Pandas, SQLAlchemy
• Data Warehousing: Analytical-ready tables, structured modeling
• Analytics & Reporting: SQL aggregation, business insights

• Cloud Deployment: AWS RDS / Redshift or GCP BigQuery
• Pipeline Orchestration: Airflow / Prefect for automated ETL
• Dashboarding: Tableau / Power BI integration