49. Pick the Join Strategy

Difficulty: Medium · Topics: Joins, Conditional Logic, Filtering & Selection

tables lists table sizes (name, size_mb). joins lists planned joins: join_id, left_table, right_table, join_type ('inner', 'left', 'right' or 'full').

Decide the strategy Spark would pick with a broadcast threshold of 10 MB:

• 'broadcast right' if the right table is at most 10 MB and the join type is inner or left, and, for inner joins, the right table is not larger than the left
• otherwise 'broadcast left' if the left table is at most 10 MB and the join type is inner or right
• otherwise 'sort-merge'

Return join_id and strategy, sorted by join_id.

Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

tables

name	size_mb
orders	50000
customers	8000
countries	1
currencies	0.5
promos	9

joins

join_id	left_table	right_table	join_type
1	orders	countries	inner
2	orders	customers	left
3	countries	orders	left
4	promos	orders	right
5	orders	currencies	full
6	currencies	promos	inner

Expected output

join_id	strategy
1	broadcast right
2	sort-merge
3	sort-merge
4	broadcast left
5	sort-merge
6	broadcast left

Hints

Hint 1

Join tables twice, once for each side; rename its columns first so the two copies do not clash.

Hint 2

The rule for outer joins: the side whose unmatched rows must be kept cannot be broadcast. A full outer join cannot broadcast either side.

Hint 3

Build the result with a when chain in the order of the rules.

Learn the concepts

• Broadcast joins · 3 min read. Ship a small table to every executor and skip the shuffle, and when broadcasting backfires.
• Adaptive Query Execution · 4 min read. Spark re-plans a running query from real statistics: coalescing, join switching and skew splitting.
• filter / where · 4 min read. Keep the rows that match a condition, and the null and operator traps that silently drop data.

PySpark functions you'll practise

• join
• when / otherwise
• select
• orderBy
• isin

Related problems

• What Changed Between Two Versions · Hard · Joins
• Reconcile Two Sources (Full Outer Join) · Hard · Joins
• How Much Does Each Query Read? · Medium · Joins
• Fill Missing Dates (Calendar Spine) · Hard · Joins
• Sessionize a Clickstream · Hard · Window Functions

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems