Alteryx Designer Core: The Join That Multiplied the Orders

The Join That Multiplied the Orders

An order fact of 1,200 rows is inner-joined to a customer dimension on customer_id. After the join the stream has 3,100 rows and the revenue total has risen from $4.2M to $10.9M. The customer table came from a CRM migration that left two or three rows per key for about 400 customers, each differing in attribute values and a last_synced timestamp. Separately, finance has flagged 40 orders that vanish from the joined output - those orders have customer_ids that do not exist in the dimension at all, and they must be kept in the reporting extract.

1. How do you prove where the duplication comes from before you change anything? Name the specific checks.

Read the worked answer

Assume nothing; measure each side.

Right side. Group By customer_id with Count on the dimension, filter Count > 1. That gives you the number of duplicated keys and the number of surplus rows - the number that will cause multiplication.
Left side. The same check on the order fact. 1,200 rows is only plausible as an order count if order_id is unique; if the fact itself has duplicate order_id, the join is innocent and you have a different problem entirely.
The L / J / R anchors. The Join tool separates matched (J), unmatched-left (L) and unmatched-right (R). If J has 3,100 rows against a 1,200-row fact, the inflation is inside the match, confirming many-to-many. L should hold the 40 missing customers - that is a direct test of finance's claim, not an inference from it.
Reconcile the arithmetic. Sum revenue on the fact, on J and on the final stream. $4.2M to $10.9M is a factor of about 2.6, which fits keys averaging 2.6 rows apiece. If the multiplication factor and the surplus-row count disagree, there is a second cause hiding underneath.

Why this order matters. Deduping before you have located the duplication is a guess. If the duplicates turn out to be on the fact side, deduping the dimension changes nothing and you have burned a day. The check costs one Summarize and settles it.

2. What is the correct order of operations, and where does 'first record' logic bite you?

Read the worked answer

Dedupe the dimension, then join. Never the reverse.

Why not dedupe after the join. After the inflated join, each order appears two or three times with identical measures and different customer attributes. A Unique on order_id at that point keeps whichever copy arrived first - so the customer attributes attached to the order become a coin flip, and if you had instead aggregated you would have summed the revenue two or three times. The join has already created the harm; a downstream tool cannot know which of the copies was the real one.

The dedupe itself. Sort by customer_id ascending then last_synced descending, then Unique on customer_id. 'Most recently synced record wins' is now an explicit, documented rule rather than an accident of file order. That rule is a business decision - if the CRM's latest row is the one with a cleared region field, 'most recent' may not be what the business wants; get it in writing.

Beware the Summarize shortcut. Group By customer_id with Max on each attribute independently is order-independent and reproducible, but it can fabricate a customer assembled from parts of three different rows. Prefer a whole-row selection.

Then verify. Join count on J must be ≤ 1,200. Revenue on the joined stream must equal revenue on the fact - if it does not, duplicates remain. And check the dedupe's duplicate output count against the 400 known-bad keys; a mismatch means the migration left more damage than reported.

3. How do you retain the 40 unmatched orders, and what must downstream consumers be told?

Read the worked answer

Stop using inner-join semantics. In Alteryx the Join tool always writes unmatched left rows to the L anchor - that is where all 40 live, and it is the cleanest way to prove they exist before you fix anything.

Union J with L, after adding a Formula on the L branch that fills the customer attribute fields with an explicit label such as UNKNOWN (or a null-tolerant placeholder that survives group-by). Two reasons to use a literal rather than leaving nulls: many downstream summaries and chart tools silently drop null categories, so the 40 orders would disappear from the total a second time; and an explicit UNKNOWN bucket is visible on a report, which is what prompts someone to chase the missing customer records.

Why not Find Replace. It can append matched fields and leaves unmatched rows in place, so it superficially looks like a left outer join - but it is a lookup, not a join: it has no concept of many-to-many inflation, its match semantics differ, and it will happily produce one row per match. Using it for this job would replace a duplicated-key problem you understand with a duplicated-key problem you do not.

What to tell finance. 40 of 1,200 orders (3.3%) carry no customer attributes; any by-region or by-segment breakdown will show an UNKNOWN slice of that size, and $X of revenue sits in it. That is the correct number to present - suppressing it to zero would overstate coverage. Add a Count on the L anchor wired to a Message or Test tool so the 40 is monitored every run and a jump to 400 is caught the same night rather than at quarter end.