L0 β Complete
Data Discovery & Problem Statement
Profile the Olist dataset, identify reconciliation exceptions using SQL, and write a fact-based problem statement. This layer is the foundation β everything in L1βL6 builds on what we find here.
π Dataset: Olist Brazilian E-Commerce
π Tool: DuckDB + DBeaver
π
Completed: Jul 2026
β± ~4 hours
Dataset candidates considered
Three options were evaluated for the O2C reconciliation project:
β Chosen β Olist + synthetic settlement (hybrid)
Olist Brazilian E-Commerce (Kaggle, public domain) β 99,441 real orders, 7 relational tables, multi-payment-type data (credit card, boleto, voucher, debit card), timestamps covering purchase β approval β delivery. Used as the order/billing side.
A synthetic settlement file will be generated in L2 to simulate what a payment processor would return β matching the reality of enterprise O2C where order system and settlement system are always separate.
Why this over fully synthetic: Real data has real dirty data β nulls, timing anomalies, orphan records. That makes the reconciliation exercise meaningful, not just a clean demo.
β Not chosen β UCI Online Retail II
Invoice-level only. No payment or settlement records at all. Would require building both sides from scratch.
β Not chosen β Fully synthetic
Too much generation time. "Synthetic only" also carries less credibility in a portfolio context than real public data.
Download source
π‘
Lesson: In a real BSA engagement, the dataset decision is a requirements conversation β what data does Finance currently export? What does the payment processor settlement file look like? The dataset choice shapes every query you write downstream.
Excel can open CSVs but fails at joining multiple tables and slows down past ~100K rows. SQL gives us reproducibility β every finding is a saved query anyone can re-run to verify.
Install DuckDB
# Terminal (Windows PowerShell or Mac/Linux)
pip install duckdb
# Then install DBeaver Community (free SQL editor UI)
# https://dbeaver.io/download/ β Community Edition
Connect DBeaver to DuckDB
DBeaver β New Connection β search "DuckDB" β set path to a new .duckdb file, e.g. C:/olist/olist.duckdb. DBeaver creates the file on first connect.
Load all CSVs as tables β run one at a time (Ctrl+Enter per block)
CREATE TABLE orders AS SELECT * FROM read_csv_auto('H:/path/olist_orders_dataset.csv');
CREATE TABLE payments AS SELECT * FROM read_csv_auto('H:/path/olist_order_payments_dataset.csv');
CREATE TABLE items AS SELECT * FROM read_csv_auto('H:/path/olist_order_items_dataset.csv');
β Common error: path backslash
Windows paths use \ but SQL needs /. Change C:\folder\file.csv to C:/folder/file.csv.
β Common error: running all queries at once
Highlight only the CREATE TABLE you want, then Ctrl+Enter. If you run all three at once, the second CREATE fires before the first finishes β you get "table does not exist" on the SELECT that follows.
π‘
Why DuckDB instead of MySQL/PostgreSQL: DuckDB reads CSV directly with read_csv_auto() β no import wizard, no schema definition upfront. SQL syntax is standard and transfers to Snowflake, BigQuery, or Redshift with minimal changes.
First instinct is always to jump into analysis. Don't. Spend 10 minutes profiling first. It prevents wrong assumptions and saves hours of debugging later.
What columns exist?
-- Excel equivalent: look at row 1 (header row)
DESCRIBE orders;
Returns column names and data types. Key columns for O2C: order_id, order_status, order_purchase_timestamp, order_approved_at, order_delivered_customer_date, order_estimated_delivery_date.
See real data β first 5 rows
-- Excel equivalent: look at rows 2β6
SELECT * FROM orders LIMIT 5;
Order status distribution
-- Excel equivalent: Pivot Table β Rows=order_status, Values=Count
SELECT
order_status,
COUNT(*) AS total,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
GROUP BY order_status
ORDER BY total DESC;
| order_status | total | pct % |
| delivered | 96,478 | 97.0% |
| shipped | 1,107 | 1.1% |
| canceled | 625 | 0.6% |
| unavailable | 609 | 0.6% |
| invoiced | 314 | 0.3% |
| processing | 301 | 0.3% |
COUNT(column) counts only non-null rows. So COUNT(*) - COUNT(col) = number of nulls. This pattern replaces Excel's =COUNTBLANK() but works across 99K rows instantly.
SELECT
COUNT(*) AS total_rows,
COUNT(order_approved_at) AS has_approved,
COUNT(*) - COUNT(order_approved_at) AS null_approved,
COUNT(order_delivered_customer_date) AS has_delivered,
COUNT(*) - COUNT(order_delivered_customer_date) AS null_delivered
FROM orders;
| metric | value |
| total_rows | 99,441 |
| null_approved | 160 |
| null_delivered | 2,965 |
Drill into null_approved β what status are they?
SELECT order_status, COUNT(*) AS total
FROM orders
WHERE order_approved_at IS NULL
GROUP BY order_status
ORDER BY total DESC;
| order_status | total | interpretation |
| canceled | 141 | Expected β canceled before approval |
| delivered | 14 | β Problem β delivered with no approval record |
| created | 5 | Expected β pending approval |
Investigate β inspect the 14 records directly
Finding 14 is not enough β pull the actual rows before drawing any conclusions. This query retrieves every timeline-relevant column so we can see what the records look like.
-- pull the actual 14 records and inspect their timestamps
SELECT
order_id,
order_status,
order_purchase_timestamp,
order_approved_at, -- this column will be NULL for all 14 rows
order_delivered_customer_date
FROM orders
WHERE order_approved_at IS NULL
AND order_status = 'delivered'
ORDER BY order_purchase_timestamp; -- sort by purchase date to spot patterns
The result is 14 rows. Scanning the order_purchase_timestamp column, all records appear to cluster in JanβFeb 2017. That is an observation at this point, not a confirmed finding β the next query counts it precisely.
Confirm the pattern β count by month
STRFTIME trims a timestamp down to year-month only, then GROUP BY counts how many orders fall in each month. Excel equivalent: =TEXT(A2,"YYYY-MM") followed by a Pivot Table on that column.
-- trim timestamp to year-month, then count per month
SELECT
STRFTIME(order_purchase_timestamp, '%Y-%m') AS purchase_month,
COUNT(*) AS total
FROM orders
WHERE order_approved_at IS NULL
AND order_status = 'delivered'
GROUP BY purchase_month
ORDER BY purchase_month;
| purchase_month | total |
| 2017-01 | 2 |
| 2017-02 | 12 |
Confirmed β all 14 records fall within just 2 months. A random error would scatter across the full 2-year dataset. Two months means a systemic event, not individual mistakes.
π΄ Finding β 14 delivered orders, no approval record
All 14 records cluster in JanβFeb 2017 (2 in January, 12 in February). This is a systemic data gap, not random human error β likely a system outage or migration event in Q1 2017 that prevented approval timestamps from being recorded while fulfillment continued normally.
In a real Finance audit, these would be flagged as: revenue recognized with no corresponding approval event β audit trail incomplete for 14 transactions.
π‘
SQL lesson β IS NULL vs = NULL: In SQL, NULL = NULL evaluates to unknown (not true), so WHERE col = NULL always returns zero rows. Always write IS NULL or IS NOT NULL.
π
Note for later review: These two queries are investigative queries β written to confirm a hypothesis mid-analysis, not to produce a deliverable directly. Come back to these once STRFTIME, AND conditions, and GROUP BY feel fully familiar.
SELECT
payment_type,
COUNT(*) AS total_transactions,
ROUND(SUM(payment_value), 2) AS total_value,
ROUND(AVG(payment_value), 2) AS avg_value
FROM payments
GROUP BY payment_type
ORDER BY total_value DESC;
| payment_type | transactions | total_value R$ | avg R$ |
| credit_card | 76,795 | 12,542,084 | 163.32 |
| boleto | 19,784 | 2,869,361 | 145.03 |
| voucher | 5,775 | 379,437 | 65.70 |
| debit_card | 1,529 | 217,990 | 142.57 |
| not_defined | 3 | 0.00 | 0.00 |
β Finding β not_defined, R$0.00
3 payment records with no payment type and zero value. A payment record that exists but carries no value or type is an orphan record β flag for L2 exception report.
βΉ Observation β voucher avg R$65.70 vs credit_card avg R$163.32
Voucher average is less than half of credit card. This means vouchers are likely partial payment offsets (discount codes), not standalone payment method β important for reconciliation logic: a voucher + credit_card combination on one order must be summed together before comparing to order total.
This is the core O2C reconciliation question: order total (items + freight) vs payment total. We use CTEs (WITH ... AS) to pre-aggregate each table to one row per order before joining β this prevents a critical bug (see Step 07).
WITH order_value AS (
SELECT
order_id,
ROUND(SUM(price + freight_value), 2) AS order_total
FROM items
GROUP BY order_id
),
payment_value AS (
SELECT
order_id,
ROUND(SUM(payment_value), 2) AS payment_total,
MAX(payment_installments) AS installments
FROM payments
GROUP BY order_id
)
SELECT
o.order_id,
ov.order_total,
pv.payment_total,
ROUND(ov.order_total - pv.payment_total, 2) AS diff,
pv.installments,
CASE
WHEN pv.installments > 1
AND pv.payment_total > ov.order_total THEN 'INSTALLMENT_INTEREST'
WHEN pv.payment_total < ov.order_total THEN 'UNDERPAYMENT'
ELSE 'OVERPAYMENT'
END AS exception_type
FROM orders o
JOIN order_value ov ON o.order_id = ov.order_id
JOIN payment_value pv ON o.order_id = pv.order_id
WHERE ABS(ov.order_total - pv.payment_total) > 0.01
ORDER BY ABS(diff) DESC;
| exception_type | orders | total variance R$ | avg variance R$ |
| INSTALLMENT_INTEREST | 257 | 3,063.77 | 11.92 |
| UNDERPAYMENT | 83 | 199.52 | 2.40 |
| OVERPAYMENT | 29 | 6.59 | 0.23 |
β Interpretation
INSTALLMENT_INTEREST (257): Expected business behavior β credit card installment fees in Brazil are charged on top of item price. Not a reconciliation error; exclude from exception report.
UNDERPAYMENT (83, R$199.52): Average R$2.40 per order β likely partial voucher redemption that wasn't fully offset. Flag for Finance review, low priority.
OVERPAYMENT (29, R$6.59 total): Average R$0.23 β floating-point rounding in payment processor. Below materiality threshold. Document and close.
The first version of the amount mismatch query joined items directly to payments without pre-aggregating. The result was wildly inflated numbers β one order showed a payment of R$109,312 against an order total of R$13,664.
π Bug β many-to-many JOIN creates row multiplication
When an order has 3 items AND 2 payment rows, a direct JOIN creates 3Γ2 = 6 rows.
SUM(price) then counts each item price twice (once per payment row).
SUM(payment_value) counts each payment three times (once per item row).
Fix: Use CTEs to reduce each table to one row per order_id before joining. Then the join is always 1-to-1.
π‘
Lesson: Always aggregate before joining when both sides of a JOIN can have multiple rows per key. This is called the "fan-out problem" and it's one of the most common silent data errors in SQL β the query runs without error, the numbers just look wrong. The fix is WITH table AS (SELECT key, SUM(...) ... GROUP BY key).
625 orders have order_status = 'canceled' but still carry active payment records. This means the customer paid, the order was canceled, but there is no refund or write-off in the data.
SELECT
COUNT(DISTINCT o.order_id) AS canceled_with_payment,
ROUND(SUM(p.payment_value), 2) AS total_at_risk,
ROUND(AVG(p.payment_value), 2) AS avg_per_order
FROM orders o
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'canceled';
| metric | value |
| canceled_with_payment | 625 |
| total_at_risk R$ | 143,255.60 |
| avg_per_order R$ | 215.75 |
π΄ Critical β R$143,255 revenue exposure
Every single canceled order (625 of 625) has a payment record. This is the headline finding of the entire L0 layer. In a real Finance context, each of these 625 orders needs one of three dispositions: (1) refund issued, (2) write-off approved, or (3) payment reapplied to a replacement order. None of these are recorded in the current dataset.
| priority | exception | count | financial exposure |
| HIGH | Canceled orders retaining payment | 625 | R$143,255 |
| MED | Underpayment | 83 | R$199.52 |
| MED | Orders with no item records | 775 | unknown |
| MED | Approval gap Q1 2017 | 14 | audit risk |
| LOW | Overpayment (rounding) | 29 | R$6.59 |
| EXP | Installment interest | 257 | expected behavior |
Problem statement (L0 deliverable)
Context
Olist marketplace processes 99,441 orders across four payment types over a 2-year period (2016β2018), totaling R$16,008,872.
Problem
Manual reconciliation between order records and payment settlements cannot detect exceptions at scale. SQL analysis identified R$143,255 of collected payments on 625 canceled orders with no refund or write-off record, 775 orders with missing item records, and a systemic data gap affecting 14 delivered orders in Q1 2017.
Impact
R$143,455 identified in this first exploratory pass (canceled orders holding customer payment, plus early underpayment scan) β not the full picture. Manual reconciliation estimated at 2β3 business days per monthly close cycle. Exceptions discovered late increase refund processing cost and audit risk. Full classification across all 6 exception types in L2 surfaces R$271,891.01 total exposure.
Objective
Design an automated O2C reconciliation system achieving β₯95% match rate on order-to-payment reconciliation, classifying exceptions by type and severity, and flagging material items for Finance review by T+1.
99,441 orders profiled across 7 relational tables
Actual Β· Olist public dataset 2016β2018
R$143,255 revenue exposure identified from canceled orders
Actual Β· SQL query Β· canceled + payment records
5 exception categories classified by severity
Actual Β· DuckDB reconciliation queries
1 bug found and fixed β many-to-many JOIN fan-out
Actual Β· CTE pre-aggregation pattern applied