The Duplicate-Key Blowup

One key, 2 rows, double the output

Give products a version column and let product 4017 carry 2 rows: version 1 priced at 25 and version 2 priced at 27. The catalog now has 4 rows for 3 products. Join orders, 5 rows, to it on product_id and count the result. Order 1 carries 4017 and matches both versions, producing 2 output rows. Order 3 carries 4017 and does the same. Orders 2, 4 and 5 each match 1 catalog row. The result has 7 rows from 5 orders, and nothing in the code hints that anything multiplied.

product_idorders rowsproducts rowsoutput rows
4017224
2210212
3305111
total547

The rule behind the table: for each key, the output holds left rows times right rows. A key that is unique on the right contributes exactly its left count, 2 rows on the right double that count, and 3 rows on each side turn 6 input rows into 9 output rows. In the program below, products is 4 rows, far under the threshold, so Spark broadcasts it. Watch the 2 orders that carry 4017: each one meets both versions in its task's copy of the catalog and emits 2 rows.

orders.join(products, "product_id").collect()
orders
order_idproduct_idquantity
140172
222101
340171
433053
522102
products
product_idversionprice
4017125
4017227
221018
3305160

Why it hurts at scale

Getting 7 rows from 5 is a 40% inflation, and every total built on the result is wrong: quantity summed over the join counts orders 1 and 3 twice. Scale that to production. A fact table of 2 billion orders joined to a catalog that averages 3 versions per product comes out at 6 billion rows. The join's own shuffle is unchanged: it moves the 2 billion orders and the catalog as they are, and the multiplication happens afterwards, inside the join tasks. Everything downstream pays for it. The join tasks emit 3 times the rows, a later aggregation reads 3 times the rows, the written result is 3 times the size, and its totals are 3 times too high in a way no single row reveals. The sharpest case is a key repeated on both sides: 10,000 rows on the left and 10,000 on the right for 1 key is 100 million output rows, all emitted by the 1 task that holds that key.

A left table holds key A on 3 rows and key B on 2 rows. A right table holds A on 2 rows, B on 1 row and C on 4 rows. How many rows does an inner join on the key return?

Detect before you join

A side that should have 1 row per key can be checked in 1 statement, and the check runs as an aggregation over that side alone, far cheaper than the join it protects.

dupes = (products.groupBy("product_id").count()
         .filter(F.col("count") > 1))
dupes.show()

A count comparison catches the same problem after the fact. In an inner join to a side that is unique on the key, the result never has more rows than the left table, and in a left join it has exactly as many. A result with more rows than the left side means the right side repeated a key. Write both counts to the job's log and the first bad run shows the jump.

Prevent it

Decide which row per key the join should see, and collapse the side to it before joining. If the duplicates are versions, keep the latest: find the maximum version per product and join the catalog back to that list on both columns. Exact copies need only dropDuplicates(["product_id"]). When the rows differ and you have no rule, dropDuplicates still returns 1 row per key, but it chooses that row arbitrarily, and a different run can choose a different one.

latest = products.groupBy("product_id").agg(F.max("version").alias("version"))
current = products.join(latest, ["product_id", "version"])
result = orders.join(current, "product_id")
Do
  • Check that a dimension is unique on its key before joining to it
  • Compare the left row count with the result row count
  • Collapse to 1 row per key with a rule you can state
  • Treat a side with several rows per key as a fact, not a dimension
Avoid
  • Assume a table named like a dimension is unique on its key
  • Fix a blowup by calling dropDuplicates on the joined result
  • Rely on dropDuplicates to pick the row you meant
  • Sum a measure across a join whose row count you have not checked

The seed's order_items holds several rows per order_id, as a line-item table should. The cell shows the orders with the most items. Join orders to order_items on order_id and each order appears as many times as this count says: the expected result for a fact-to-fact join, and the blowup from this section if order_items had been mistaken for a one-row-per-order dimension.

About this lesson

This section is part of the Joins: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.