23. Diagnosing wrong row counts

The most common join bug isn't an error — it's a silently wrong row count. Two causes account for almost all of it: dropped rows (from inner joins) and fan-out (from duplicate keys).

Fan-out: one row becomes many

If the key isn't unique on the side you're joining to, each left row matches several right rows and multiplies. Our orders has that duplicated row (order 1021 appears twice). Join without cleaning it and the duplicate rides along:

print("raw orders joined  :", orders.join(customers, "customer_id").count())
print("deduped orders join:", orders.dropDuplicates().join(customers, "customer_id").count())

Now imagine aggregating after that join — the duplicated order would be counted twice in revenue. Fan-out corrupts sums, not just counts.

The habit: know your key's grain

Before joining, ask "is the key unique on each side?" Check it:

# how many customer_ids appear more than once in customers? (should be 0)
customers.groupBy("customer_id").count().filter(F.col("count") > 1).show()

If a key that should be unique isn't, dedup or aggregate to the right grain before the join — never after.

A quick checklist when counts look wrong

• More rows than expected? Fan-out — a duplicate/non-unique join key. Dedup or pre-aggregate the many-side.
• Fewer rows than expected? An inner join dropped non-matches. Switch to a left join and inspect the nulls (lesson 4.1).
• Sums too high? Fan-out again — you aggregated after a fanned-out join.

Your turn

Count each customer's orders correctly — deduplicate orders first so the double-counted order 1021 doesn't inflate anyone's total — then group by customer_id and count. Return customer_id and the count. Assign to result.

result = orders  # <- dropDuplicates, groupBy customer_id, count()
result.show()

result = orders.dropDuplicates().groupBy("customer_id").count()