Olist Brazilian E-Commerce — Multi-Platform Big Data Integration Pipeline

Hands-on guide: environment setup + how to rerun the full pipeline end to end, following the exact order it was actually built (mirrors Report/OLIST_PROJECT_REPORT.md). Use this file to explore the project, rebuild it yourself, or keep extending it.

Course: Big Data Advanced | Group: Group 4 (Andy Phan, Innocent, Ameer, To, …) Dataset: Brazilian E-Commerce Public Dataset (Olist) — 9 original CSV files.

---

1. What this project does

The assignment requires integrating one dataset across 4 different big-data tools (Snowflake, Azure SQL, MongoDB Atlas, Hadoop HDFS), converging into PySpark to join everything into one flat table (Flat Tile), then using an LLM to generate business insights in an executive-briefing format.

Source (9 CSV) ──┬──────────────────────────────────────────────┐
                  ├──→ Snowflake (raw landing, all 9 files)      │
                  ├──→ Azure SQL (4 tables direct + 2 tables via │
                  │      Snowflake → Alteryx-equivalent)         │
                  ├──→ MongoDB Atlas (2 tables, JSON denormalized)│
                  └──→ Hadoop HDFS (geolocation, 2 partitions)   │
                                                                  ▼
                              Jupyter (PySpark/SparkSQL)  ←── Enrichment (Weather, Census)
                                        │
                                        ▼
                                   Flat Tile (121,532 rows, 23 columns)
                                        │
                                        ▼
                          Google Colab (LLM/Groq) → Insight + Slide deck

Original assignment diagram: Content/Screenshot 2026-06-19 204312.jpg. Full explanation of each branch, the red/green flow, ETL vs ELT: see Report/OLIST_PROJECT_REPORT.md section 2.

---

2. Directory structure

Big_data_2_Project/
├── Content/
│   ├── archive/                  ← 9 original CSV files (Olist dataset)
│   ├── Screenshot 2026-06-19 204312.jpg   ← original assignment diagram
│   └── ...                       ← other evidence screenshots
├── etl/
│   ├── snowflake_to_azuresql.py  ← Branch: Snowflake/CSV → Azure SQL (6 tables)
│   ├── snowflake_to_mongodb.py   ← Branch: CSV → MongoDB Atlas (2 tables, JSON)
│   ├── fetch_enrichment_data.py  ← Fetch Weather (meteostat) + Census (IBGE)
│   ├── consolidate_weather.py    ← Merge 12 station_*.csv files into weather_by_city.csv
│   ├── build_notebook.py         ← Regenerates etl/olist_flat_tile.ipynb (PySpark)
│   ├── build_llm_notebook.py     ← Regenerates etl/olist_llm_analysis.ipynb (Colab/LLM)
│   ├── build_report_docx.py      ← Regenerates Report/OLIST_PROJECT_REPORT.docx
│   ├── olist_flat_tile.ipynb     ← PySpark notebook (runs on WSL Jupyter Lab)
│   ├── olist_llm_analysis.ipynb  ← LLM notebook (runs on Google Colab)
│   ├── flat_tile_output.csv      ← Output of olist_flat_tile.ipynb, input for Colab
│   ├── enrichment/                ← weather_by_city.csv, census_by_city.csv
│   └── mongo_export/              ← reviews.json, products.json (for importing into Mongo Atlas)
├── Report/
│   ├── OLIST_PROJECT_REPORT.md   ← Main report (English, for reading/submission)
│   └── OLIST_PROJECT_REPORT.docx
├── SESSION_RECOVERY_LOG.md       ← Log of decisions/incidents encountered along the way
├── MEDALLION_REDESIGN_PROPOSAL.md ← Alternative architecture proposal (not applied)
├── venv/, venv311/                ← Python virtualenvs (not committed — see .gitignore)
└── README.md                      ← This file

---

3. Prerequisites (accounts + environment)

What you need	Notes
Windows 11	Runs the Python venv, ODBC driver, cloud service connections
WSL2 Ubuntu	Runs Hadoop 3.3.6 + Spark 3.5.1 + Jupyter Lab (Java 11 or 17)
Python 3.11 (venv in repo: venv311/)	pandas, snowflake-connector-python, pymongo, SQLAlchemy, pyodbc
ODBC Driver 18 for SQL Server	Required so pandas can write to Azure SQL via pyodbc/SQLAlchemy
Snowflake account (free trial)	Database OLIST_DB, Schema PUBLIC, Warehouse COMPUTE_WH (X-Small)
Azure SQL account (free tier)	Database olist_dw, SQL Authentication (not Windows Auth)
MongoDB Atlas account (free M0 cluster)	Database olist_mongo, 2 collections: reviews, products
Groq API key (free, console.groq.com/keys)	Used in the LLM step on Colab — entered manually via getpass, never hardcoded

⚠️ No Alteryx required — the original assignment uses Alteryx to transform 2 tables (SELLERS, CATEGORY_NAME_TRANSLATION), but Alteryx's email verification failed, so it was replaced with an equivalent Python script (snowflake_to_azuresql.py, function transform()) doing the same 3 steps: pull–clean–load.

⚠️ Must-do before running — old hardcoded paths

The scripts etl/snowflake_to_azuresql.py, etl/snowflake_to_mongodb.py, and etl/fetch_enrichment_data.py still hardcode the absolute path D:\Big_data_2\... (the OLD folder name, before it was renamed to Big_data_2_Project). Before rerunning, update these variables to match the real path:

• snowflake_to_azuresql.py: variable SOURCE_DIR
• snowflake_to_mongodb.py: variable SOURCE_DIR
• fetch_enrichment_data.py: variables BASE, OUT_DIR

---

4. Rerunning each step — in the exact order it was actually built

Stage 1 — EDA (optional, to understand why each file went where)

Full EDA code + real results are in Report/OLIST_PROJECT_REPORT.md section 3. Conclusion: order_reviews (real free text + high null rate) → MongoDB, geolocation (largest, flat structure) → Hadoop, sellers/category_translation (small, need cleaning) → Snowflake→Azure, the rest (customers, orders, order_items, order_payments) → Azure SQL directly.

Stage 2 — Load the 9 files into Snowflake (landing)

Via Snowsight (web UI): Data → Create → Table → From File, drag-and-drop each file, automatic schema inference — no manual CREATE TABLE needed. Then verify:

DROP DATABASE IF EXISTS OLIST_DB;
CREATE DATABASE OLIST_DB;
USE DATABASE OLIST_DB; USE SCHEMA PUBLIC;
SHOW TABLES IN OLIST_DB.PUBLIC;
-- Verify each table's row count matches the source CSV (see report section 4 for all 9 SELECT statements)
DELETE FROM OLIST_DB.PUBLIC.PRODUCT_CATEGORY_NAME_TRANSLATION WHERE C1 = 'product_category_name';  -- fixes the missed "Skip header" issue

Stage 3 — Azure SQL (6 tables: 4 direct + 2 via Snowflake)

$env:SNOWFLAKE_PASSWORD = "..."
$env:AZURE_SQL_PASSWORD = "..."
& "venv311\Scripts\python.exe" "etl\snowflake_to_azuresql.py"

• 4 tables (CUSTOMERS, ORDERS, ORDER_ITEMS, ORDER_PAYMENTS): read directly from the source CSVs → Azure SQL, no transform, only column names uppercased.
• 2 tables (SELLERS, CATEGORY_NAME_TRANSLATION): read from Snowflake → transform (strip/title-case city, upper-case state, drop duplicates) → Azure SQL.

Stage 4 — MongoDB Atlas (2 collections: reviews, products)

$env:MONGODB_PASSWORD = "..."
& "venv\Scripts\python.exe" "etl\snowflake_to_mongodb.py"

The script first tries to connect directly via pymongo — if the machine hits a TLS error (TLSV1_ALERT_INTERNAL_ERROR, encountered on the original machine and confirmed to be an OS-level issue, not code/config), use the fallback: the script still writes etl/mongo_export/reviews.json and products.json — open MongoDB Atlas Data Explorer (web UI) → Insert Document → paste the JSON content directly.

Stage 5 — Hadoop HDFS (geolocation, 2 partitions)

In WSL2 Ubuntu (Hadoop 3.3.6 installed, running pseudo-distributed):

tail -n +2 Content/archive/olist_geolocation_dataset.csv > geo_nohdr.csv
split -d -n l/2 geo_nohdr.csv geo_part_
hdfs dfs -put geolocation_part1.csv geolocation_part2.csv /olist_ecommerce/geolocation/
jps                       # confirm NameNode/DataNode/SecondaryNameNode are running
hdfs dfs -ls /olist_ecommerce/geolocation/

Stage 6 — Enrichment (Weather + Census)

& "venv311\Scripts\python.exe" "etl\fetch_enrichment_data.py"      # fetch census (IBGE) + weather (meteostat)
& "venv311\Scripts\python.exe" "etl\consolidate_weather.py"        # merge 12 station_*.csv files → weather_by_city.csv

If meteostat fails (this happened with both the old and new versions of the library), use the static Kaggle dataset "Temperature Time-Series for some Brazilian cities" directly — copy the 12 station_*.csv files into etl/enrichment/ then run consolidate_weather.py.

Stage 7 — PySpark Flat Tile (WSL Jupyter Lab)

# In WSL, /mnt/d/... is already mounted to the Windows D: drive
& "venv311\Scripts\python.exe" "etl\build_notebook.py"   # regenerates etl/olist_flat_tile.ipynb

Open etl/olist_flat_tile.ipynb in Jupyter Lab (PySpark kernel, Spark 3.5.1, JDBC driver mssql-jdbc:12.8.1.jre11) → Run All. The notebook reads in parallel from Azure SQL (JDBC), MongoDB (JSON export), and Hadoop HDFS, joining in this order: ORDER_ITEMS → ORDERS → CUSTOMERS → SELLERS → order_payments (agg) → reviews (Mongo) → products (Mongo) → geolocation (HDFS) → weather → census. Result: etl/flat_tile_output.csv (121,532 rows, 23 columns).

⚠️ If reopening the notebook in VS Code shows old content (this has happened before): VS Code keeps the old version in memory and overwrites the newer file every time you save/run a cell. Fix: fully close the tab (choose "Don't Save" if prompted), then reopen it with Ctrl+P.

Stage 8 — LLM Analysis (Google Colab)

& "venv311\Scripts\python.exe" "etl\build_llm_notebook.py"   # regenerates etl/olist_llm_analysis.ipynb

Upload etl/olist_llm_analysis.ipynb + etl/flat_tile_output.csv to Google Colab → Run All. When prompted, enter GROQ_API_KEY (get one free at console.groq.com/keys, never hardcoded). The notebook precomputes Pareto 80/20, customer growth, and Pearson correlations before feeding them into the prompt (the LLM only interprets numbers already computed, it does not estimate them). Model: openai/gpt-oss-120b (falls back to llama-3.3-70b-versatile) via the Groq API. Downloaded output: olist_insights_presentation.md + olist_charts.png.

---

5. Further reading

File	Content
Report/OLIST_PROJECT_REPORT.md	Full report, 13 sections: EDA, PK/FK, detailed stage-by-stage walkthrough, incident log, conclusion
SESSION_RECOVERY_LOG.md	Log of every decision/incident encountered while building the pipeline (read this if you need the "why" behind a choice)
MEDALLION_REDESIGN_PROPOSAL.md	Alternative architecture proposal (Bronze/Silver/Gold) — not applied to the current version, kept for reference if building a separate version

6. Current status / what's left before submission

Full checklist in SESSION_RECOVERY_LOG.md section 5 — summary: the pipeline runs end to end; still missing 2 team member names and a few evidence screenshots (Snowsight, Azure Portal/SSMS, MongoDB Atlas, Hadoop jps, Jupyter output, Colab insight text).