← Back to portfolio
O2C Reconciliation Modernization
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
Contents
01
Why this dataset β€” decision rationale
Before writing a single line of SQL, pick the right data foundation
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.
kaggle.com/datasets/olistbr/brazilian-ecommerce β€” free account required. Download all 8 CSV files. The olist_geolocation_dataset.csv (~1M rows) is not needed for reconciliation work β€” skip it or open in Python only, never Excel.
πŸ’‘
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.
02
Setup: DuckDB + DBeaver
Why not Excel, and how to get a SQL environment running in 10 minutes
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.
# Terminal (Windows PowerShell or Mac/Linux)
pip install duckdb

# Then install DBeaver Community (free SQL editor UI)
# https://dbeaver.io/download/ β†’ Community Edition
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.
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.
03
Profile each table
Before any analysis β€” understand what you're working with
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.
-- 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.
-- Excel equivalent: look at rows 2–6
SELECT * FROM orders LIMIT 5;
-- 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_statustotalpct %
delivered96,47897.0%
shipped1,1071.1%
canceled6250.6%
unavailable6090.6%
invoiced3140.3%
processing3010.3%
04
Null analysis β€” orders table
Nulls in the wrong columns = data integrity exceptions
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;
metricvalue
total_rows99,441
null_approved160
null_delivered2,965
SELECT order_status, COUNT(*) AS total
FROM orders
WHERE order_approved_at IS NULL
GROUP BY order_status
ORDER BY total DESC;
order_statustotalinterpretation
canceled141Expected β€” canceled before approval
delivered14⚠ Problem β€” delivered with no approval record
created5Expected β€” pending approval
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.
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_monthtotal
2017-012
2017-0212
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.
05
Payment type breakdown
Understand how customers pay before reconciling payment amounts
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_typetransactionstotal_value R$avg R$
credit_card76,79512,542,084163.32
boleto19,7842,869,361145.03
voucher5,775379,43765.70
debit_card1,529217,990142.57
not_defined30.000.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.
06
Amount mismatch β€” core reconciliation query
Do what customers paid match what the order system says they owe?
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_typeorderstotal variance R$avg variance R$
INSTALLMENT_INTEREST2573,063.7711.92
UNDERPAYMENT83199.522.40
OVERPAYMENT296.590.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.
07
Bug found: many-to-many JOIN fan-out
What went wrong the first time, and why CTEs fix it
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).
08
Canceled orders with payment β€” R$143,255
The biggest financial exposure in the dataset
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';
metricvalue
canceled_with_payment625
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.
09
Exception summary + problem statement
Translate findings into business language
priorityexceptioncountfinancial exposure
HIGHCanceled orders retaining payment625R$143,255
MEDUnderpayment83R$199.52
MEDOrders with no item records775unknown
MEDApproval gap Q1 201714audit risk
LOWOverpayment (rounding)29R$6.59
EXPInstallment interest257expected behavior
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