A tested dbt + DuckDB warehouse over 99k orders from Olist, a Brazilian marketplace. Seven messy raw tables become documented marts with 59 data tests on every push. Then one question: what drives bad reviews, and what should operations fix first?
CI on every pushdbt · DuckDB · SQL · Streamlit · GitHub Actions
DataBrazilian E-Commerce Public Dataset by Olist: about 99k orders, Sep 2016 to Oct 2018, seven CSVs. Partial months are left out of the charts.
62%Late orders with a 1–2 star review
9%On-time orders with one
59Data tests, run in CI
6.8%Of delivered orders arrived late
Data flow · how a record moves
Seven CSVs to one tested warehouse
Each dot is a record. Staging fans out into one view per source table. Duplicate reviews are dropped so each order keeps one. Every layer is tested before the dashboard reads it. Click a stage to read what happens there.
01 · The finding
The cliff starts at day one
Share of 1–2 star reviews by when the parcel arrived compared with the promised date. Early or on time, it stays near one in ten. One to three days late, it triples. Eight days late, four in five reviews are bad.
Average score4.32 for parcels 10+ days early; 1.70 for parcels 8+ days late. Only 6.8% of the 96,478 delivered orders were late, yet they hold most of the angry reviews.
02 · Bad months
Delivery failures, not demand
Two periods drove most late deliveries. Order volume was flat to rising across both, so each dip in reviews lines up with a delivery spike, not a change in demand.
Black Friday, Nov 20170%late share
Feb–Mar 20180%late share, the worst
Average review, Feb–Mar 20180lowest in the data
Customers who came back0%of 96,096
Top categorieshealth_beauty (R$1.23M) and watches_gifts (R$1.17M). Total lifetime value across customers: R$15.42M.
03 · Distance
São Paulo speed is not national speed
Average days from purchase to delivery. São Paulo receives 42% of orders and gets them in under nine days. The North and Northeast wait two to three times as long.
SuggestionPad estimates for the North and Northeast. Early parcels earn the best reviews, so a longer, honest promise costs little.
04 · Tests
75 checks, one honest warning
The latest dbt build: 74 passes and one warning. The warning is real: 247 orders (0.26%) have payments that don't match items plus freight within R$1, mostly voucher top-ups. The test warns on any mismatch and fails above 500.