The task and original case
Build a revenue reconciliation that survives two refunds against one order. The useful evidence is the grain of each table, the join you chose and the exception check—not a large dashboard. This exercise asks why a perfectly valid-looking merge can change gross revenue before you subtract anything.
| Case element | Value or requirement |
|---|---|
| Inputs | Two orders: A gross 100 and B gross 50. Refund rows: A 5 and A 5. |
| Build | A revenue reconciliation with row-grain notes and an unmatched-order check. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Start with one row per order in orders.csv and one row per refund in refunds.csv. Check that order IDs are unique on the order side and refund IDs are unique on the refund side. Both refunds belong to A, so the refund table legitimately has repeated order IDs. Write these two grain definitions above the calculation.
Group refunds by order ID and sum their amounts. The resulting refund table has one row for A with a value of 10. Left-join that summary onto orders; replace a missing refund with zero only after confirming it means no matching refund in this complete invented ledger. Calculate net separately for each order, then sum. pandas documents join types and merge validation; use a one-to-one check for this summarized join. Retain unmatched refund keys in a separate audit rather than dropping them.
Sources[1]
Check the worked result
A has gross 100, refund 10 and net 90. B has gross 50, refund 0 and net 50. Gross totals 150, refunds total 10 and net totals 140. The joined output should still contain two order rows.
A direct left join to the two raw refunds produces A twice and B once. Summing the repeated gross column yields 250. Subtracting refunds then yields 240, an incorrect result even though every refund appears exactly once. Explain that the duplicated measure belongs to the order grain.
Make the failure visible
Add a third refund for an unknown order C. Your unmatched-key report must show it; the two known orders must not disappear or acquire C's refund. Also duplicate an order row intentionally. A uniqueness or join-cardinality check should stop the run before it quietly creates another inflated result. Never use DISTINCT on gross amounts to repair row multiplication: unrelated orders can have the same price.
Revise it and ask someone to reproduce it
Keep the original reconciliation as revision one. For revision two, split A's refund into three rows totaling the same 10. The order-level net should remain 90. Give a peer only the inputs and method, and ask them to explain why gross remains 150. Record their first unanswered grain question and improve the note.
- Two output order rows and three independently reconciled totals are visible.
- The refund summary and unmatched-key register can be inspected.
- A split-refund revision changes row count without changing total net.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Reconciled an invented two-order ledger by aggregating split refunds before joining; preserved gross revenue and reported unmatched keys.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.