Automated Olist Brazilian E-Commerce ETL, Relational Star Schema & Advanced SQL Business Audit
AP +12.6d, Roraima RR +7.1d, Amazonas AM +6.1d) experiences severe SLA non-compliance due to Amazon basin riverway reliance. Action item: Extend published delivery promises by 10 business days for northern zip prefixes to curb refund chargebacks.
Modeled to isolate dimensional master records (Customers, Sellers, Products) from transactional fulfillment, payments, and review events.
Calculates average delay (Actual Delivery - Estimated Delivery) in days for delivered orders.
SELECT
c.customer_state,
COUNT(o.order_id) AS delivered_orders,
ROUND(AVG(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date)), 2) AS avg_delay_days,
ROUND(MAX(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date)), 2) AS max_delay_days
FROM fact_orders o
INNER JOIN dim_customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY c.customer_state
ORDER BY avg_delay_days DESC
LIMIT 3;
| Customer State | Delivered Orders | Avg Delay (Days) | Max Delay (Days) | Status |
|---|---|---|---|---|
| AP (Amapá) | 11 | +12.64 | 37 | Severe Bottleneck |
| RR (Roraima) | 22 | +7.05 | 35 | Severe Bottleneck |
| AM (Amazonas) | 58 | +6.05 | 34 | Riverway Delay |
Finds the top 5 sellers by revenue in each category without window functions.
WITH seller_category_revenue AS (
SELECT
p.product_category_name,
oi.seller_id,
ROUND(SUM(oi.price), 2) AS total_revenue,
COUNT(oi.order_item_id) AS total_units_sold
FROM fact_order_items oi
INNER JOIN dim_products p ON oi.product_id = p.product_id
GROUP BY p.product_category_name, oi.seller_id
)
SELECT t1.product_category_name, t1.seller_id, t1.total_revenue, t1.total_units_sold
FROM seller_category_revenue t1
WHERE (
SELECT COUNT(*)
FROM seller_category_revenue t2
WHERE t2.product_category_name = t1.product_category_name
AND t2.total_revenue > t1.total_revenue
) < 5
ORDER BY t1.product_category_name ASC, t1.total_revenue DESC;
| Clean Payment Method | Transactions | Total Volume ($) | Avg Order Value ($) | Transaction Share % | Revenue Share % |
|---|---|---|---|---|---|
| Credit Card | 2,975 | $1,047,760 | $352.19 | 74.38% | 74.10% |
| Boleto | 736 | $261,179 | $354.86 | 18.40% | 18.47% |
| Other (Voucher / Debit) | 289 | $105,022 | $363.40 | 7.22% | 7.43% |
Isolates customers with ≥ 3 orders whose spend exceeds the benchmark average ($1,415.23).
| Customer Unique ID | State | Orders | Total Spend ($) | Avg Spend / Order ($) | Benchmark Average ($) | Spend Above Benchmark ($) |
|---|---|---|---|---|---|---|
| uniq_cust_1621 | SP | 42 | $16,966.20 | $403.96 | $1,415.23 | +$15,551.00 |
| uniq_cust_0327 | SP | 38 | $15,756.90 | $414.65 | $1,415.23 | +$14,341.60 |
| uniq_cust_0796 | BA | 45 | $15,638.30 | $347.52 | $1,415.23 | +$14,223.00 |
| uniq_cust_1595 | SP | 39 | $14,754.90 | $378.33 | $1,415.23 | +$13,339.70 |
| uniq_cust_1634 | RJ | 42 | $14,315.80 | $340.85 | $1,415.23 | +$12,900.60 |
| Field / Table | Anomaly Observed | Technical Fix | Status |
|---|---|---|---|
| customer_zip_code_prefix | Leading zeros dropped by numeric parser (e.g. 01001 -> 1001) | String conversion + regex zfill(5) |
Fixed (100%) |
| order_delivered_customer_date | Null values on 219 rows | Differentiated canceled (80) vs active in-transit (139) |
Partitioned |
| order_items.price | Risk of $0 promotional items skewing margin calculations | Hard filter price > 0 with data quality logging |
Passed |
| Timestamps (5 columns) | Inconsistent string formats | Coerced to unified ISO-8601 DATETIME objects | Normalized |
"In Challenge 2, the specification mandated extracting the top 5 sellers per category without window functions. I implemented this using a correlated subquery counting how many sellers within the same partition had higher revenue. From an algorithmic standpoint, this incurs an O(N²) execution penalty because the inner query executes for every row. In production dbt pipelines, I replace this with DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) which computes in O(N log N), allowing millions of rows to be ranked in seconds."
"Our pipeline supports both MySQL and DuckDB/SQLite. MySQL is optimal for transactional mutations—such as order status updates and payments requiring ACID guarantees. However, for analytical scans across millions of order items, a columnar engine like DuckDB avoids scanning unnecessary columns like product dimensions or seller addresses, reducing memory bandwidth by over 80%."
"Rather than relying on silent to_sql() execution, Phase 3 incorporates an automated verification harness that queries the live database using SELECT COUNT(DISTINCT pk) and asserts equality against len(df['pk'].unique()). This immediately catches schema truncation, silent primary key collisions, or silent network dropouts."