Fix Skew with Salting

PySpark data engineering interview problem. Difficulty: advanced. Pattern: Skew. About 20 minutes. Part of the Pro drill bank.

User 1 has most of the rows in the left table, so a normal join would send all of them to one task. Salting spreads the hot key. You add a random-like number (the salt) to the big side, copy the small side once for every salt value, and join on both the key and the salt. Now the hot key is spread over several tasks, and the result is the same.

You have 2 tables. left has user_id and payload, with a skewed key (user 1 has 4 rows). right has user_id and name. Add a column salt to left with the value abs(hash(payload)) % 2. Duplicate each row of right once for each salt value, 0 and 1, by exploding an array of the two numbers. Join the 2 tables on both user_id and salt. Return user_id, payload and name, sorted by user_id and payload. The editor creates both tables and ends with result = left. Change the last line to build your answer.

Requirements

• Add salt to left as abs(hash(payload)) % 2.
• Copy each row of right for the salts 0 and 1, using F.explode(F.array(...)).
• Join on user_id and salt.
• Return user_id, payload, name, sorted by user_id, payload, in result.

Constraints

• The number of salt buckets is 2.
• Every left row must match exactly 1 row of the exploded right, so the result has 6 rows.
• Do not forget the salt in the join keys. Without it you get twice as many rows.

Examples

Input: skewed left Output: user_id | payload | name 1 | a | Ada 1 | b | Ada 1 | c | Ada 1 | d | Ada 2 | e | Alan 3 | f | Grace Salting spreads the hot user_id=1 key.

Topics: lakebench, pyspark, salt, join.

More PySpark interview questions · All interview problems · Learn data engineering