Modeling 100,000 Brazilian e-commerce orders into a Snowflake star schema — and the three questions it answers.
Olist — Brazil's largest department-store marketplace — released ~2 years of real transactions across 8 normalized tables. The raw tables can't answer a single strategic question on their own.
Where is the revenue actually coming from, are sellers staying, and is late delivery a logistics problem or a geography problem?
The grain is line item, not order — so revenue stays additive and the per-seller story survives.
dim_customer is SCD Type 2, keyed on customer_unique_id because people relocate between orders. dim_geography is conformed across customer and seller.
65% comes from one region — the São Paulo–Rio–Minas axis. The Norte, geographically the largest, is 2.6%. Even regional effort allocation spends four-fifths of effort on one-fifth of opportunity.
I expected a churn cliff; the data pushed back. ~50% of every cohort is still active at month 6. The risk isn't collapse — it's the steady ~40% who drift off in the first quarter, which acquisition has to keep refilling.
Inside Sudeste it's ~7% regardless of category; outside it climbs 1.3–1.7×, widest for fashion. The lever is the freight network by region, not supplier SLAs by product — and the same dim_geography from Finding 01 surfaces it in one join.
One conformed dimension answered two of the three questions.
65% is one region. Effort allocation should say so out loud, not spread evenly.
No cliff — I was wrong about that. The job is refilling first-quarter drift, not stopping collapse.
Fix the freight network outside Sudeste — it's geography, not product SLAs.
unique · not_null · relationships · accepted_values. fact_orders is clustered on purchase_date_key — every dashboard filters by date first. The retention cohorts use LAG() windows over a cohort × period cross-join — no triangular subquery.
Eight flat tables in. Three decisions out.