Olist E-commerce Data Platform with Airflow, Spark, PostgreSQL, and Metabase

Objective

Build a comprehensive, modern Data Platform to collect, process, transform, and visualize large-scale e-commerce data. This project simulates an End-to-End Data Engineering and Business Intelligence workflow for a major e-commerce platform (Olist). It addresses the problem of querying highly normalized, fragmented operational data by constructing an optimized Data Warehouse (Star Schema).

System integrates:

• Apache Airflow – for ETL orchestration, scheduling, and dependency management.
• Apache Spark – for scalable and distributed data processing, cleaning, and transforming raw data into dimensional models.
• PostgreSQL – acting as the Data Warehouse (Star Schema under the dwh schema).
• Metabase – for Business Intelligence (BI), querying the Data Warehouse, and building interactive Executive Dashboards.
• Docker Compose – for containerization and seamless deployment.

---

System Architecture

The entire Extract, Transform, Load (ETL) process is managed via Apache Airflow DAGs. The pipeline separates the three stages cleanly: data is read from CSV, transformed in Spark, persisted to Parquet as an intermediate medium, and finally loaded into the data warehouse. Raw data is never written to the database.

[Kaggle Dataset (CSV)]
           |
           v
[Apache Airflow: Extract task] --> CSV files on local disk (data/raw)
           |
           v
[Apache Airflow: Transform tasks (9 Spark jobs in parallel)]
           |
           v
[Parquet files on local disk (data/processed)] 
           |
           v
[Apache Airflow: Load tasks (9 jobs in parallel)]
           |
           v
[PostgreSQL (DWH Schema - SCD Type 1)]
           |
           v
[Metabase (Executive Dashboard)]

Pipeline Stages

1. Extract — downloads the Olist dataset from Kaggle and stores the raw CSV files in /opt/airflow/data/raw. The database is not touched at this stage.
2. Transform — PySpark jobs run in parallel. They read CSVs directly, clean data, handle missing values, calculate metrics (e.g. delivery_delay_days), and reshape the normalized structure into a star schema. The transformed data is written to Parquet files in /opt/airflow/data/processed.
3. Load — a separate set of tasks reads each Parquet file produced by Transform and loads it into the dwh schema of PostgreSQL using SCD Type 1 (overwrite). Indexes are created on the larger fact tables to keep BI queries fast.

---

Datasets & Database Schema

Download from: Kaggle - Brazilian E-Commerce Public Dataset by Olist

1. Original Database Schema (Raw Data)

The raw dataset comes in 9 fragmented CSV files representing a highly normalized OLTP system.

erDiagram
    olist_customers_dataset ||--o{ olist_orders_dataset : "places"
    olist_orders_dataset ||--|{ olist_order_items_dataset : "contains"
    olist_orders_dataset ||--|{ olist_order_payments_dataset : "paid via"
    olist_orders_dataset ||--o{ olist_order_reviews_dataset : "receives"
    
    olist_products_dataset ||--o{ olist_order_items_dataset : "is bought as"
    olist_sellers_dataset ||--o{ olist_order_items_dataset : "fulfills"
    
    olist_geolocation_dataset ||--o{ olist_customers_dataset : "locates"
    olist_geolocation_dataset ||--o{ olist_sellers_dataset : "locates"
    
    olist_products_dataset }o--|| product_category_name_translation : "translates to"

    olist_customers_dataset {
        string customer_id PK
        string customer_unique_id
        bigint customer_zip_code_prefix FK
        string customer_city
        string customer_state
    }
    
    olist_geolocation_dataset {
        bigint geolocation_zip_code_prefix PK
        float geolocation_lat
        float geolocation_lng
        string geolocation_city
        string geolocation_state
    }

    olist_order_items_dataset {
        string order_id PK, FK
        bigint order_item_id PK
        string product_id FK
        string seller_id FK
        string shipping_limit_date
        float price
        float freight_value
    }

    olist_order_reviews_dataset {
        string review_id PK
        string order_id FK
        bigint review_score
        string review_comment_title
        string review_comment_message
        string review_creation_date
        string review_answer_timestamp
    }

    olist_orders_dataset {
        string order_id PK
        string customer_id FK
        string order_status
        string order_purchase_timestamp
        string order_approved_at
        string order_delivered_carrier_date
        string order_delivered_customer_date
        string order_estimated_delivery_date
    }

    olist_order_payments_dataset {
        string order_id PK, FK
        bigint payment_sequential PK
        string payment_type
        bigint payment_installments
        float payment_value
    }

    product_category_name_translation {
        string product_category_name PK
        string product_category_name_english
    }

    olist_products_dataset {
        string product_id PK
        string product_category_name FK
        float product_name_lenght
        float product_description_lenght
        float product_photos_qty
        float product_weight_g
        float product_length_cm
        float product_height_cm
        float product_width_cm
    }

    olist_sellers_dataset {
        string seller_id PK
        bigint seller_zip_code_prefix FK
        string seller_city
        string seller_state
    }

Loading

Challenges with the Raw Schema:

• To calculate total revenue by product category, an analyst would need to join order_items, products, and product_category_name_translation.
• To map customer reviews with payment methods, massive cross-joins are required, slowing down dashboard performance.

2. Data Warehouse Schema (Star Schema)

To resolve the querying bottlenecks, Spark transforms the data into an OLAP-optimized Star Schema.

Dimension Tables:

• dim_customers: Customer demographics and geographic keys.
• dim_products: Merged product attributes and English translations.
• dim_sellers: Seller metadata.
• dim_dates: Date dimension for time-series aggregation.
• dim_geolocation: Lat/Lng coordinates for mapping.

Fact Tables:

• fact_orders: Accumulating Snapshot Fact storing order lifecycle timestamps, tracking total orders, and computing delivery delays.
• fact_order_items: Transaction Fact tracking product-level sales and shipping costs.
• fact_payments: Financial Fact tracking exact customer payments.
• fact_order_reviews: Customer Experience Fact tracking satisfaction scores.

How to Run

1. Clone the repository and navigate to the project directory.
2. Initialize the environment using Docker Compose:

   docker-compose up -d

3. Access Apache Airflow at http://localhost:8080.
   • Login credentials: admin / admin.
   • Unpause and trigger the etl_pipeline DAG. The pipeline has three stages: Extract (one task), Transform (nine parallel Spark jobs), and Load (nine parallel jobs).
4. Wait for the Load tasks to finish. Intermediate Parquet files are written to /opt/airflow/data/processed.
5. Access Metabase at http://localhost:3000.
   • Complete the initial account setup.
   • Connect Metabase to PostgreSQL (Host: postgres, DB: airflow, User: airflow, Schema: dwh).

---

Metabase Executive Dashboard

The end result of the Data Platform is a centralized BI Dashboard providing actionable insights into sales trends, customer distribution, and top-selling products.