Analyzing 100,000+ Orders: My Olist E-Commerce SQL & OLAP Project

In my previous SQL study notes, almost every example used clean 4-row or 5-row toy tables. Those are useful for understanding what an INNER JOIN or GROUP BY does in theory, but they don’t prepare you for working with actual data systems. Real data is messy, has missing attributes, contains timestamps that defy logic, and spans hundreds of thousands of rows across related tables.

To test my skills on real-world data, I built an end-to-end SQL project using the Olist Brazilian E-Commerce dataset from Kaggle, analyzing over 100,000 customer transactions from 2016 to 2018.

The full repository and all 50 questions across 5 phases are available on GitHub: Ecommerce_Data_Modeling_SQL.

Here is an overview of what the project is, how I approached it, the technical hurdles I ran into, and what the experience taught me.

---

The Setup: Containerized MySQL in Docker

Instead of installing MySQL directly on my Mac, I ran MySQL 8.0 inside a Docker container on port 3307. This gave me an isolated, reproducible database environment that I can start, stop, or recreate without cluttering my local machine:

docker run --name ecommerce_mysql \
  -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=my_password \
  -d mysql:latest

Inside the database (ecommerce_db), I loaded nine relational tables spanning orders, line items, customers, products, payments, sellers, reviews, geolocation, and product category translations.

---

What I Built: The 5-Phase Architecture

Rather than jumping straight to complex queries, I structured the project into five logical engineering phases:

1. Catalog & Data Exploration: Auditing total record counts across all nine tables, inspecting order status distributions (delivered, canceled, shipped), customer geographic spread across Brazilian states, and product catalog weights.
2. Data Quality & Hygiene Suite: Writing automated sanity checks to catch data defects: duplicate primary keys (HAVING COUNT(*) > 1), invalid order timelines where delivery preceded purchase, zero or negative payment amounts, and missing address data.
3. Business Performance KPIs: Joining transactions with orders, products, and sellers to calculate commercial metrics: total delivered merchandise revenue, Average Order Value (AOV) using modular CTEs, delivery turnaround latency, and carrier on-time SLA compliance.
4. Advanced Analytical SQL: Utilizing analytical window functions (ROW_NUMBER(), RANK(), DENSE_RANK()) to compare category rankings, calculating monthly running totals, month-over-month growth rates, moving averages, and measuring days between repeat purchases using LAG().
5. OLAP Dimensional Modeling: Transforming the relational schema into an analytical star schema centered on delivered order items (fact_sales) connected to four dimension tables (dim_customer, dim_product, dim_seller, and dim_date).

                  +-------------------------+
                  |       dim_customer      |
                  +-------------------------+
                  | PK  customer_id         |
                  |     customer_city       |
                  |     customer_state      |
                  +------------+------------+
                               |
                               | (1:N)
                               v
+-------------------------+        +--------------------------+
|       dim_product       |        |        fact_sales        |
+-------------------------+        +--------------------------+
| PK  product_id          |<---+   | PK,FK1  order_id         |
|     product_category    |    |   | PK      order_item_id    |
|     product_weight_g    |    +---| FK2     customer_id      |
|     product_dimensions  |        | FK3     product_id       |
+-------------------------+    |   | FK4     seller_id        |
                               +---| FK5     date_key         |
                               |   |         price            |
                               |   |         freight_value    |
                               |   +--------------------------+
                               |               |
                               | (1:N)         | (1:N)
                               v               v
                  +-------------------------+  +--------------------------+
                  |        dim_seller       |  |         dim_date         |
                  +-------------------------+  +--------------------------+
                  | PK  seller_id           |  | PK  date_key (YYYYMMDD)  |
                  |     seller_city         |  |     full_purchase_date   |
                  |     seller_state        |  |     quarter, month, year |
                  +-------------------------+  +--------------------------+

---

Real Data Challenges & How I Solved Them

Building the star schema wasn’t just about writing CREATE TABLE AS SELECT. Working with raw, uncurated data forced me to troubleshoot several real-world edge cases:

1. Preserving Revenue on Missing Product Categories

While building dim_product, I found 611 product records in the source dataset that had missing or null category attributes. If I had simply filtered them out or allowed an INNER JOIN to drop them, over $170,000 in delivered sales items would have vanished from the fact table.

Following Kimball dimensional modeling standards, I preserved every product identifier and modeled missing categories as 'Unknown'. This ensured 100% financial reconciliation with raw source data down to the cent ($13,591,643.70 delivered revenue) while maintaining clean categorization for reporting.

2. Seller Ingestion & Operating System Buffer Limits

When auditing seller joins, I discovered an initial staging discrepancy where only 289 sellers were imported instead of all 3,095 from the source CSV. Joining against an incomplete dimension orphaned over 90% of sales items on queries.

When attempting to batch insert the remaining 2,800+ records in a single command, the massive query string exceeded the operating system’s process argument buffer limit (argument list too long). I solved this by piping the data stream directly through standard input (stdin) in 500-row batches, successfully restoring all 3,095 sellers and bringing unmatched seller keys in fact_sales to exactly zero.

3. Column Widths for International Locations

During schema migration, Brazilian municipality names (e.g., sao jose do vale do rio preto) exceeded an initial VARCHAR(25) constraint. Profiling column lengths with MAX(CHAR_LENGTH(...)) helped me properly size seller_city to VARCHAR(50), avoiding silent truncation errors.

---

What Working on Real Data Taught Me

1. Clean queries start with dirty data: You cannot write analytical queries based on assumptions. You must write data quality tests first to verify whether your assumptions actually hold true in the dataset.
2. The grain defines the warehouse: Before designing any fact table or running aggregations, you have to be precise about what a single row represents. In fact_sales, the grain is one row per delivered line item, not the entire order.
3. CTEs make business logic maintainable: Breaking down calculations (like Average Order Value, moving averages, or latency intervals) into modular WITH blocks makes queries readable and far easier to debug.
4. Data engineering is about integrity: An analytics model is only valuable if stakeholders can trust the numbers. Verifying composite primary keys, enforcing foreign key constraints, and reconciling fact revenue against source totals are non-negotiable steps.

---

Project Repository

The full 50-question project, table creation statements, data quality tests, and star schema queries are documented in my repository on GitHub:

→ github.com/nirajbasnet009/Ecommerce_Data_Modeling_SQL