Retail & Supply Chain Intelligence Pipeline
Local-First Automated ETL, Relational Star Schema & SQL Audit Engine
Data Integrity
100% Passed
Orders Analyzed
100,000+
Star Schema Tables
7 Tables
Audit Latency
< 50ms
1. Problem Statement & Business Objective
Olist operates the largest department store marketplace in Brazil, connecting tens of thousands of independent merchants with millions of customers across all 27 federative units. Transactional data was siloed across disparate flat files with unstandardized UTC timestamps, lost leading zeros in zip codes, and unpartitioned delivery statuses. Mounting logistics delays in remote states and unpredictable shipping freight costs suppressed customer satisfaction and eroded unit margins without an automated, auditable relational source of truth.
End-to-End Pipeline & System Architecture
Automated Data Surgery
Pandas / Regex
Normalizes UTC timestamps, enforces 5-digit zip strings via zfill(5), and quarantines non-positive prices.
Star Schema Modeling
SQLAlchemy ORM
Defines relational star schema with explicit PK/FK constraints across 3 dimension tables and 4 fact tables.
Multi-Engine Loading
SQLite / DuckDB / MySQL
Enforces idempotent batch ingestion supporting local SQLite/DuckDB or production MySQL.
Automated Integrity Audit
Python / SQL Engine
Programmatically reconciles DataFrame row/key counts against database SELECT COUNT(DISTINCT id) queries.
Strategic SQL Intelligence
Analytical SQL / CTEs
Executes 4-phase SQL audit diagnosing northern Brazil transit bottlenecks, category revenue, and CLV power users.
Phase-by-Phase Engineering Lifecycle
Phase 1: Automated Data Surgery & Cleansing
Objective: Sanitize raw CSV transactional dumps, enforce leading zero integrity, and quarantine non-positive amounts.
Key Deliverables & Implementations
- Preserved 5-digit Brazilian postal codes using regex zfill(5) normalization, eliminating geographic routing errors.
- Standardized heterogeneous timestamp strings into ISO-8601 UTC DATETIME values.
- Quarantined and filtered zero/negative pricing anomalies before database loading.
Phase 2: Relational Star Schema ETL & Two-Way Integrity Verification
Objective: Design Kimball-style relational star schema and automate DataFrame-to-SQL reconciliation.
Key Deliverables & Implementations
- Defined normalized Star Schema spanning 3 dimension tables and 4 transactional fact tables.
- Engineered automated audit comparing DataFrame len/nunique against SQL COUNT and COUNT(DISTINCT).
- Achieved 100% data integrity pass rate across all tables during CI pipeline verification.
Phase 3: Advanced SQL Business Audit Findings
Objective: Execute high-impact analytical queries answering executive logistics, margin, and CLV questions.
Key Deliverables & Implementations
- Diagnosed critical transit bottlenecks in Northern Brazil (Amapá averaging +12.64 days delay vs SLA).
- Identified top 5 revenue-generating sellers per category via correlated subqueries and DENSE_RANK.
- Analyzed payment mix economics (Credit Card 74.38% vs Boleto Bancário 18.40% with 48h settlement lag).
- Segmented CLV power users generating +$15,551+ spend above the $1,415 population benchmark.
Dimensional Schema & Table Definitions
Grain: One row per customer order transaction
| Column | Type | Key | Description |
|---|---|---|---|
| order_id | VARCHAR(32) | PK | Unique order identifier |
| customer_id | VARCHAR(32) | FK | Foreign key referencing dim_customers |
| order_status | VARCHAR(16) | Delivery status (delivered, shipped, canceled) | |
| order_purchase_timestamp | DATETIME | Normalized UTC order timestamp | |
| order_delivered_customer_date | DATETIME | Timestamp order reached customer | |
| order_estimated_delivery_date | DATETIME | SLA committed delivery date |
Grain: One row per item within an order line
| Column | Type | Key | Description |
|---|---|---|---|
| order_id | VARCHAR(32) | FK | Parent order identifier |
| order_item_id | INT | PK | Sequential order line item number |
| product_id | VARCHAR(32) | FK | Foreign key referencing dim_products |
| seller_id | VARCHAR(32) | FK | Foreign key referencing dim_sellers |
| price | DECIMAL(10,2) | Item selling price (strictly > 0) | |
| freight_value | DECIMAL(10,2) | Logistics freight shipping charge |
Grain: One row per payment installment or transaction
| Column | Type | Key | Description |
|---|---|---|---|
| order_id | VARCHAR(32) | FK | Parent order identifier |
| payment_sequential | INT | PK | Sequential installment number |
| payment_type | VARCHAR(16) | Payment method (credit_card, boleto, voucher, debit) | |
| payment_installments | INT | Number of credit installments | |
| payment_value | DECIMAL(10,2) | Monetary amount paid |
Grain: One row per unique customer session
| Column | Type | Key | Description |
|---|---|---|---|
| customer_id | VARCHAR(32) | PK | Unique customer session key |
| customer_unique_id | VARCHAR(32) | Persistent customer identity across repeat orders | |
| customer_zip_code_prefix | VARCHAR(5) | Standardized 5-digit Brazilian postal code | |
| customer_city | VARCHAR(64) | Customer municipality name | |
| customer_state | VARCHAR(2) | Federative unit 2-letter state code |
Grain: One row per catalog product SKU
| Column | Type | Key | Description |
|---|---|---|---|
| product_id | VARCHAR(32) | PK | Unique product catalog SKU hash |
| product_category_name | VARCHAR(64) | Cleaned product category taxonomy | |
| product_weight_g | FLOAT | Physical shipping weight in grams | |
| product_length_cm | FLOAT | Package dimension length in centimeters |
Northern Brazil Logistics Transit Bottleneck Audit (SQL)
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 5;Quantifiable Impact & Verified Outcomes
- 100% verified relational data integrity between Python extraction layer and relational database.
- Diagnosed logistics transit bottlenecks in Northern Brazil (Amapá averaging +12.64 days delay beyond SLA).
- Identified VIP power users ($16,966 spend across 42 orders) generating +$15,551+ surplus revenue above benchmark.