← Back to portfolio
O2C Reconciliation Modernization
L2 β€” Complete
Data + SQL β€” Reconciliation Engine
Turn L1's To-Be process design (match β†’ classify β†’ flag) into working SQL. Three chained queries automate what a Finance analyst would otherwise do manually every close cycle.
πŸ“‚ Dataset: Olist Brazilian E-Commerce πŸ›  Tool: DuckDB + DBeaver πŸ“… Completed: Jul 2026 🎯 Target: β‰₯95% match rate
Contents
01
Query 1 β€” Auto match
Does what the customer paid match what the order system says they owe?
Pre-aggregate items and payments to one row per order_id using the CTE pattern from L0 (prevents the fan-out bug found there), then LEFT JOIN from orders so every order survives β€” even ones missing items or payments entirely.
CREATE OR REPLACE TABLE l2_auto_match AS
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, o.order_status, o.order_approved_at,
    ov.order_total, pv.payment_total, pv.installments,
    ROUND(COALESCE(pv.payment_total, 0) - COALESCE(ov.order_total, 0), 2) AS diff,
    CASE WHEN ABS(COALESCE(pv.payment_total, 0) - COALESCE(ov.order_total, 0)) <= 0.01
         THEN 'MATCH' ELSE 'MISMATCH' END AS match_status
FROM orders o
LEFT JOIN order_value   ov ON o.order_id = ov.order_id
LEFT JOIN payment_value pv ON o.order_id = pv.order_id;
match_statusorders
MATCH98,299
MISMATCH1,142
Why LEFT JOIN, not JOIN
JOIN (inner) silently drops any order with no matching item or payment row β€” the exact kind of gap that matters most in reconciliation. LEFT JOIN + COALESCE(x, 0) keeps every one of the 99,441 orders and turns "no data" into a real, calculable exception instead of a missing row.
πŸ’‘
Trick: LEFT JOIN keeps every row from the main table even when the other side is empty. COALESCE fills that empty side with 0 so the math still works. The two always travel together in a reconciliation query.
02
Query 2 β€” Auto classify
Sort the 1,142 mismatches into the exception types Finance actually needs to act on
Built on top of l2_auto_match. A single CASE WHEN assigns exactly one exception_type per order across 6 categories plus a normal "OK" state.
CREATE OR REPLACE TABLE l2_exception_classified AS
SELECT *,
    CASE
        WHEN order_status = 'canceled' AND payment_total > 0 THEN 'CANCELED_WITH_PAYMENT'
        WHEN order_approved_at IS NULL AND order_status = 'delivered' THEN 'APPROVAL_GAP'
        WHEN order_total IS NULL THEN 'MISSING_ITEMS'
        WHEN match_status = 'MATCH' THEN 'OK'
        WHEN installments > 1 AND payment_total > order_total THEN 'INSTALLMENT_INTEREST'
        WHEN payment_total < order_total THEN 'UNDERPAYMENT'
        ELSE 'OVERPAYMENT'
    END AS exception_type
FROM l2_auto_match;
First attempt returned only 2 orders for CANCELED_WITH_PAYMENT β€” L0 had documented 625. See Step 03 for what went wrong and why.
03
Bug found: CASE WHEN priority order
CASE WHEN behaves like if/elif, not a set of independent ifs
Most canceled orders also have no item records at all β€” order_total is NULL. Checking MISSING_ITEMS before CANCELED_WITH_PAYMENT meant those orders were claimed by the first branch and never reached the canceled-order check.
πŸ› Bug β€” wrong branch order in CASE WHEN
CASE WHEN stops at the first condition that evaluates true β€” it does not check every condition. Once a row matches order_total IS NULL, it is locked into MISSING_ITEMS even if it also satisfies order_status = 'canceled'.

Fix: reorder branches so business-status flags (canceled, approval gap) β€” which matter regardless of dollar amounts β€” are checked before amount-based flags. Result jumped from 2 β†’ 622 orders correctly classified as CANCELED_WITH_PAYMENT.
πŸ’‘
Lesson: CASE WHEN = if/elif, never if/if. Always order branches from most specific/business-critical to most general β€” the most important flag has to win the tie, not whichever condition happens to sit first in the pipeline logically.
04
Bug found: reusing "diff" for the wrong question
Not every exception type is answering the same question
Once CANCELED_WITH_PAYMENT was correctly classified, its reported exposure showed R$37,369.88 β€” far short of L0's R$143,255.60.
πŸ› Bug β€” diff answers "reconciliation," not "risk exposure"
diff = payment_total βˆ’ order_total, built to answer "does the amount match?" But for a canceled order, the real question is "how much money did we collect that we now need to refund or write off?" β€” that's payment_total on its own, regardless of whether it happens to equal order_total.

Fix: report SUM(payment_total) for CANCELED_WITH_PAYMENT instead of SUM(diff). Result matched L0 exactly: R$143,255.60.
πŸ’‘
Lesson: The same column can't always answer two different business questions. Reconciliation exceptions ask "how far apart are the two numbers?" Risk-exposure flags ask "how much money is actually at stake?" Match the aggregate to the question being asked, not to whichever column is already sitting on the table.
05
Query 3 β€” Exception summary + severity
Roll every exception type up to HIGH / MED / LOW / EXP for Finance triage
Severity tier reuses L0's original priority table (Layer 0, Section 09). Financial exposure switches its aggregate per exception type per Step 04's fix.
CREATE OR REPLACE TABLE l2_exception_summary AS
SELECT
    exception_type,
    CASE exception_type
        WHEN 'CANCELED_WITH_PAYMENT' THEN 'HIGH'
        WHEN 'UNDERPAYMENT'          THEN 'MED'
        WHEN 'MISSING_ITEMS'         THEN 'MED'
        WHEN 'APPROVAL_GAP'          THEN 'MED'
        WHEN 'OVERPAYMENT'           THEN 'LOW'
        WHEN 'INSTALLMENT_INTEREST'  THEN 'EXP'
    END AS severity,
    COUNT(*) AS orders,
    ROUND(SUM(
        CASE WHEN exception_type = 'CANCELED_WITH_PAYMENT'
             THEN payment_total ELSE ABS(diff) END
    ), 2) AS financial_exposure
FROM l2_exception_classified
WHERE exception_type != 'OK'
GROUP BY exception_type;
severityexceptionordersfinancial exposure
HIGHCanceled with payment622R$143,255.60
MEDMissing items614R$125,254.08
MEDUnderpayment83R$199.52
MEDApproval gap (all 14 cluster in Jan–Feb 2017, no date filter in query)14audit risk
LOWOverpayment (rounding)30R$150.05
EXPInstallment interest255R$3,031.76 (expected)
06
Reconciling L2 against L0's original numbers
The counts shifted slightly β€” and that's a feature, not a discrepancy
L0's exception counts came from separate, independent ad-hoc queries β€” not a single unified classification. L2's job was to make every order resolve to exactly one exception type, which surfaced two refinements:
βœ“ CANCELED_WITH_PAYMENT: 622 vs 625
3 of L0's original 625 had a payment record present but valued at R$0.00 β€” no money actually changed hands. L2 filters on payment_total > 0, so those 3 are correctly excluded from the financial-risk count.
β„Ή MISSING_ITEMS: 614 vs 775 Β· OVERPAYMENT: 30 vs 29
L0's separate queries weren't mutually exclusive β€” an order could be counted in more than one ad-hoc query at once. L2's single CASE WHEN tree assigns each order to exactly one bucket, so some orders that were double-counted in L0 now sit in a single, correct category. The full set still sums to exactly 99,441.
πŸ’‘
Lesson: Independent exploratory queries (L0) are for finding patterns. A production classification (L2) has to be mutually exclusive and collectively exhaustive β€” every row gets exactly one label, and the labels sum to the total. Reconciling the two is itself part of the audit trail, not a sign something broke.
07
Results + handoff to L3
Three chained tables now sit ready for a Power BI dashboard
98.37% exception-free rate
Target β‰₯95% Β· 97,823 / 99,441 orders with zero exceptions (distinct from Step 1's 98.85% amount match rate β€” 476 orders match on amount but still fail a business rule)
3 tables chained in DuckDB
l2_auto_match β†’ l2_exception_classified β†’ l2_exception_summary
2 bugs found and fixed
CASE WHEN branch order Β· column reuse across questions
R$271,891.01 total exception exposure
Includes expected installment interest (R$3,031.76) alongside true anomalies
L3 connects l2_exception_summary and l2_exception_classified to Power BI for a severity-tiered dashboard with order-level drill-down.