🛒 Olist E-Commerce Data Warehouse & Analytics Platform

This repository contains a modern data warehouse (DWH) and analytics platform designed for the Olist Brazilian E-Commerce Dataset (over 1M+ rows of customers, items, reviews, and geo coordinates).

It implements a complete end-to-end ELT pipeline using DuckDB as the local OLAP database, dbt Core for Kimball dimensional transformations, Astronomer Cosmos & Apache Airflow for orchestrating task groups, Bruin CLI for connection and lineage testing, and Evidence.dev for BI dashboarding.

---

🏗️ The Technology Stack

• Storage & Query Engine: DuckDB (ecommerce.db) - extremely fast vectorized local columnar database.
• Transformation: dbt Core (dbt-duckdb adapter) - modular SQL compiling, dependency graphs, and documentation.
• Orchestration: Apache Airflow & Astronomer Cosmos - schedules the python ingestion and compiles dbt projects dynamically into Airflow tasks.
• Validation & Lineage: Bruin CLI - fast validation of sql syntax, query rendering, and local connections.
• Reporting (BI): Evidence.dev - markdown-based static BI dashboard querying DuckDB natively.
• Formatting & Quality: SQLFluff - SQL linter to enforce style guides.
• Package Management: uv - Astral's lightning-fast Rust-based Python packaging tool.

---

🌐 Web Interfaces & Local Services

When running the platform locally, the following interfaces are available:

1. Evidence BI Dashboard

🌐 URL: http://localhost:3000 - Dynamic cohorts, sales growth, and product rankings.

2. Apache Airflow Orchestrator

🌐 URL: http://localhost:8080 - Task execution DAG graphs, schedule runs, and execution logs.

DAG Graph (Astronomer Cosmos Task Group compile)

Successful Pipeline DAG Run

---

📁 Project Structure

ecommerce-data-modeling/
├── .env                        # Local environment variables (paths)
├── .env.example                # Environment variables template
├── .sqlfluff                   # Linter formatting configuration
├── .gitignore                  # Git exclusions for db, target, logs
├── requirements.txt            # Python dependencies (dbt, duckdb, airflow, cosmos)
├── README.md                   # Project overview & documentation
├── dataset/                    # Raw Olist CSV files
│   ├── olist_customers_dataset.csv
│   └── ...
├── scripts/
│   └── ingest.py               # Vectorized Python script to load CSVs into DuckDB
├── dbt_project/                # dbt Project root
│   ├── dbt_project.yml         # dbt settings (staging, intermediate, marts schemas)
│   ├── profiles.yml            # DuckDB adapter configurations
│   ├── packages.yml            # dbt dependencies (dbt_utils)
│   └── models/
│       ├── staging/            # Staging views (1:1 maps, type casting, renaming)
│       ├── intermediate/       # Intermediate views (payment and review rollups, joins)
│       └── marts/              # Star Schema analytics tables (dim_ & fct_)
├── airflow/                    # Airflow configurations
│   └── dags/
│       └── run_lakehouse.py     # Cosmos orchestrator DAG
└── dashboard/                  # Evidence.dev Dashboard
    └── pages/
        └── index.md            # Markdown-based dashboard reports

---

⚙️ Environment & Dependency Management

We use uv to manage virtual environments (.venv) and Python packages cleanly and at maximum speed.

1. Install uv (if not installed)

Install uv globally on your Linux system:

curl -LsSf https://astral.sh/uv/install.sh | sh

2. Create and Activate the Virtual Environment (.venv)

Create a localized virtual environment inside the project directory:

# Create the .venv folder
uv venv

# Activate the virtual environment
source .venv/bin/activate

3. Install Python Dependencies

Install all required libraries (dbt, duckdb, cosmos, airflow, sqlfluff) specified in requirements.txt:

uv pip install -r requirements.txt

4. Manage Environment Variables (.env)

The pipeline uses environment variables to resolve absolute paths dynamically (preventing relative path breaks inside Airflow).

1. Copy the template:

   cp .env.example .env

2. Open .env and verify the paths align with your workspace:

   PROJECT_ROOT=/home/tunguyenn99/my-project/ecommerce-data-modeling
   AIRFLOW_HOME=/home/tunguyenn99/my-project/ecommerce-data-modeling
   DBT_PROFILES_DIR=/home/tunguyenn99/my-project/ecommerce-data-modeling/dbt_project
   DB_PATH=/home/tunguyenn99/my-project/ecommerce-data-modeling/ecommerce.db

---

🚀 Execution Guide

Step 1: Ingest Raw CSVs into DuckDB

Run the ingestion script to bulk copy the raw CSVs into DuckDB's raw schema:

# Ensure virtual env is active
uv run python scripts/ingest.py

This creates the ecommerce.db file and loads over 1.5 million records across 9 tables in less than 1.0 second.

Step 2: Install dbt Package Dependencies

Navigate into the dbt project folder and download required macro libraries:

cd dbt_project
dbt deps

Step 3: Run and Test dbt Transformations

Compile staging models, build aggregated intermediate layers, materialize star-schema fact/dimensions tables, and execute testing blocks:

# Test connection compatibility
dbt debug --profiles-dir .

# Materialize views and tables (creates schemas: main_staging, main_intermediate, main_marts)
dbt run --profiles-dir .

# Run all unique, null, and relationship constraint tests (32 tests total)
dbt test --profiles-dir .

Step 4: Use Bruin CLI for Ad-Hoc Analytics & Lineage

Bruin CLI is pre-configured via .bruin.yml to communicate with the DuckDB database.

# Navigate back to project root
cd ..

# Verify connections list
bruin connections list

# Query fact tables directly via Bruin CLI
bruin query --connection duckdb-default --query "select * from main_marts.fct_monthly_kpis limit 5" --description "Verify monthly kpis"

Step 5: Start the BI Dashboard (Evidence.dev)

Connect the visual frontend to DuckDB and launch the interactive analytics server:

cd dashboard
npm install
npm run dev

Open http://localhost:3000 in your browser to view cohort maps, revenue growth metrics, and product category rankings.

---

📊 Analytical Dimensional Model

The pipeline maps the Olist database to a clean Star Schema:

• dim_customers: Deduplicated geographic demographics (State, City, coordinates) mapped to customer accounts.
• dim_products: Product dimensions translated to English category labels.
• dim_sellers: Seller records supplemented with geolocation coordinates.
• fct_orders: Grain at the order level, summing totals, payment divisions, review rankings, and shipping delays.
• fct_order_items: Grain at the order line-item level.
• fct_monthly_kpis: Monthly financial, traffic, and rating aggregation matrix.

---

🤝 Connect & Community

• Author: Tu Nguyen
• Community: Join the Xom Data community for more data analytics engineering insights!

Built with ❤️ for the Xom Data community.