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_id | customer_id | amount |
|---|---|---|
| 1001 | C12 | 120 |
| 1002 | C18 | 80 |
| 1003 | C99 | 50 |
| 1004 | C12 | 80 |
The right input has one row per region assignment.
| assignment_id | customer_id | region | state |
|---|---|---|---|
| A1 | C12 | West | archived |
| A2 | C12 | North | current |
| A3 | C18 | South | current |
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_id | customer_id | amount | region |
|---|---|---|---|
| 1001 | C12 | 120 | West |
| 1001 | C12 | 120 | North |
| 1002 | C18 | 80 | South |
| 1003 | C99 | 50 | null |
| 1004 | C12 | 80 | West |
| 1004 | C12 | 80 | North |
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
- 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 - Put Assert unique after the filter. Check
customer_idin error mode so a future directory with two current assignments for a customer stops the run. Assert unique - Join the orders to that checked directory, and place another Assert unique on
order_idimmediately 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.