Olist Big Data Pipeline

Project Overview

The big-data case study of the Huawei Yapay Zeka Uzmanlık Programı (AI Expertise Program): take the Olist Brazilian e-commerce dataset — 9 CSV tables, 1,550,922 rows of real marketplace data — and grow it, over three assignment phases, into a production-style data platform that answers seven business questions every day without a human in the loop.

The final pipeline: Apache Spark ingests the CSVs as Parquet into a MinIO data lake (bronze, immutable), a pyarrow loader copies bronze into Postgres (raw), dbt builds the cleaned silver and star-schema gold layers with 46 schema tests as quality gates, and Apache Airflow runs the whole DAG daily. Superset dashboards answer the business questions; Grafana watches the pipeline's health. End-to-end: ~1.8 minutes, verified idempotent with two consecutive fully green runs.

Data quality, measured — not assumed

The first real lesson of the project: data lies, so profile before you trust. Measured findings that shaped the silver layer:

• 26.2% of the geolocation table is exact duplicates (261,831 of 1,000,163 rows) — any naive join through it inflates every downstream number. Deduplicated to 19,015 rows (one per ZIP prefix), enforced by a unique test forever.
• 789 review keys attach to multiple orders; 547 orders have multiple reviews — the "latest review per order" rule is tested, not tribal knowledge.
• 99,441 order-scoped customer ids hide 96,096 real people — "customer count" is wrong by 3,345 unless you know which id to count.
• Referential integrity: zero violations. The dirt lives in content, not structure.

The star schema

Every business question has the same shape — aggregate one measure, cut by one or two attributes — so gold is a star schema: three fact tables at three grains (order item, order, payment record) and four dimensions (customers, products, sellers, calendar), plus one thin, tested reporting view per question. The grain distinction is load-bearing: one order can mix credit card and voucher, which is exactly why payments get their own fact table.

Decisions I'd defend

• ELT over ETL. Spark only moves and types data; every transformation lives in dbt inside the warehouse, where it is versioned, testable SQL. Bronze is immutable, so a bad rule means rebuilding from bronze — never re-extracting from source.
• Tests as the pipeline's brake. The 46 dbt tests run inside dbt build in dependency order; during development a wrong deduplication rule tripped a unique test and stopped the build before any dashboard showed a wrong number. That single save justified the whole approach.
• Task boundaries = failure domains. The Airflow DAG's four tasks pass no data to each other — each reads from the lake or warehouse — so any task can be re-run alone, and a transform failure never re-reads a CSV.
• Dashboards as code. Superset's datasets, charts and dashboards are created entirely through its REST API; Grafana is fully provisioned from files. No UI clicking survives a rebuild.

Headline answers: R$ 13.49M revenue, 96,096 unique customers, November 2017 Black Friday spike, São Paulo alone at 38% of revenue, a 3.3× delivery-time gap between São Paulo (8.8 days) and Roraima (29.4 days), credit cards carrying 78% of payment value.

What I Learned

• The hardest engineering decision wasn't a tool — it was drawing the line for where Spark's job ends. "Let Spark do everything" makes business logic untestable; Spark moves data, dbt owns logic.
• Version pinning is not pedantry: Spark↔MinIO S3A jars must match the image's Hadoop exactly, and dbt + Airflow literally cannot share a Python environment (pip gives up). Isolate and pin, in scripts.
• Idempotency is a feature you prove, not claim — the second green run is the evidence.