The Frozen Join and the Duplicate Ripple
A 4.2M-row transactions table is joined to a 900K-row customer dimension on a numeric cust_id. Both inputs are already sorted by cust_id and are coming from a Sort tool upstream. The workflow joins, then feeds a Summarize, and downstream counts are inflated by roughly 11%. The analyst suspects the join. Inspecting the Join tool output shows 38,000 rows in the Left-unjoined anchor, but the analyst knows there are only ~2,000 orphan customers. The customer dimension was built from a CRM extract where cust_id is stored as a string with occasional leading zeros, but was typed as Int32 in the source. Some records in the dimension have the same cust_id because of a soft-delete flag that was never filtered.
1. Diagnose the two separate causes of the 11% inflation and the unexpected Left-unjoined count. What specifically does each symptom point to?
Read the worked answer
Two distinct problems are layered here.
1. Left-unjoined count too high (38K vs ~2K expected). The Join tool matches on data type and value. If the transactions side is Int32 but the dimension side carries a type mismatch (e.g., one side is a string because a leading-zero record couldn't cast cleanly and the Select tool left it as V_String), those rows fall to Left-unjoined even though they "look" equal. The leading-zero records are the tell: 007 as a string is not equal to 7 as an Int32. The fix is to normalize both keys to the same type explicitly with a Select tool (both Int32, or both a trimmed/zero-stripped string) before the Join, not to rely on implicit coercion.
2. 11% row inflation. Duplicate cust_id values on the dimension side (soft-deleted records not filtered) create a many-to-one fan-out: every transaction matching a duplicated customer is emitted once per duplicate. 38K orphan rows plus duplicates both push the downstream Summarize count up. The Join tool's J anchor emits all matches, so duplicates on the right multiply left rows.
2. The analyst wants to "just use a Join Multiple and dedupe later." Why is that a poor fix, and what is the correct sequence of tools?
Read the worked answer
Join Multiple does not solve fan-out; it makes it worse by allowing additional match keys and still emitting every combination. Deduping after the join is a band-aid that hides the real problem and can silently drop legitimate rows if the dedupe key is wrong.
Correct sequence:
- Select on the dimension: cast
cust_idto Int32 (or both sides to a normalized string), and confirm no nulls survive the cast. - Filter the dimension to
is_deleted = 0(or the equivalent flag) before the join, so one row percust_idremains. Verify with a Unique tool oncust_idand check the Duplicate anchor is empty. - Join on the now-clean, type-matched key.
- Inspect the
L(Left-unjoined) anchor against the expected ~2K orphans as an assertion. - Optionally add a Test tool or a row-count check so the workflow fails loudly if duplicates sneak back in.
The principle: fix cardinality and type at the source of the join, never downstream of it.