Ciaren

Blog

Why a Left Join Inflates Your Order Totals

Trace inflated order totals to repeated customer keys, fix the lookup before the left join, and check that each order still contributes its amount once.

About this post

Published
Reading time
3 min
Author
Ciaren team
joinsdata-qualitytutorial

The orders export totals $330. After a left join adds a customer region, the same orders total $530. The missing customer still has an order in the result. The extra $200 comes from a customer who matched twice.

This example uses a directory with current and archived region assignments. The report asks for the current region of each customer, and every order should contribute its amount once.

Follow the order IDs through the join

The left input has one row per order. Multiple orders for one customer are expected.

order_idcustomer_idamount
1001C12120
1002C1880
1003C9950
1004C1280

The right input has one row per region assignment.

assignment_idcustomer_idregionstate
A1C12Westarchived
A2C12Northcurrent
A3C18Southcurrent

In Ciaren, connect orders to the left handle and the directory to the right handle of a Join / Merge node. Set on to customer_id and how to left. The table below shows the result with just the columns needed to diagnose it. Join settings

order_idcustomer_idamountregion
1001C12120West
1001C12120North
1002C1880South
1003C9950null
1004C1280West
1004C1280North

A left join returns one result for each right-side match. An unmatched order gets one row with a missing region. Here both C12 orders appear twice, so their amounts add an extra $120 + $80. The equal $80 amounts on orders 1002 and 1004 also show why summing distinct amounts would give the wrong answer. Each of those two orders should contribute $80.

Find the lookup keys that match more than once

On a separate branch from the directory input, add Group By Aggregate. Group by customer_id and use count on assignment_id. Each directory row in this example has an assignment ID, which matters because count skips nulls. Then use Filter rows to keep counts greater than 1. The result is C12 with a count of 2. Group by + aggregate · Filter rows

Apply the rule before joining

  1. On the directory branch that feeds the join, use Filter rows to keep state == "current". That selects North for C12 and South for C18. Filter rows
  2. Put Assert unique after the filter. Check customer_id in error mode so a future directory with two current assignments for a customer stops the run. Assert unique
  3. Join the orders to that checked directory, and place another Assert unique on order_id immediately after the join. That checks the result at the same level as the source amounts, before any grouping can hide repeated orders. Assert unique

With the filter in place, the four amounts sum to $330. North has $200, South has $80, and order 1003 still carries $50 with a missing region. If the report needs a region breakdown, use Fill nulls on the region column to give the unmatched order an Unassigned label before grouping. Fill nulls

For a report that needs the region at the time of the order, use an assignment valid at the order timestamp. The current-region filter answers a different question. In either report, each order ID should appear once after enrichment.