Broadcast joins for small tables in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of broadcast join with small table

What is the output count of the following Spark code snippet that uses a broadcast join?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast

spark = SparkSession.builder.getOrCreate()

# Large DataFrame
df_large = spark.createDataFrame([(1, 'A'), (2, 'B'), (3, 'C')], ['id', 'value'])

# Small DataFrame to broadcast
small_df = spark.createDataFrame([(1, 'X'), (3, 'Y')], ['id', 'desc'])

# Broadcast join
joined_df = df_large.join(broadcast(small_df), 'id', 'inner')

print(joined_df.count())

A 3

B 1

C 0

D 2

💡 Hint

Think about how many matching keys exist in both DataFrames.

The join is an inner join on 'id'. The small table has ids 1 and 3, which match two rows in the large table. So the result has 2 rows.

❓ data_output

intermediate

Resulting DataFrame after broadcast join

What is the content of the DataFrame after this broadcast join?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast

spark = SparkSession.builder.getOrCreate()

df1 = spark.createDataFrame([(10, 'apple'), (20, 'banana'), (30, 'cherry')], ['key', 'fruit'])
small_df = spark.createDataFrame([(10, 'red'), (30, 'dark red')], ['key', 'color'])

result = df1.join(broadcast(small_df), 'key', 'left')
result.show()

A [Row(key=10, fruit='apple', color='red'), Row(key=20, fruit='banana', color='yellow'), Row(key=30, fruit='cherry', color='dark red')]

B [Row(key=10, fruit='apple', color=None), Row(key=20, fruit='banana', color=None), Row(key=30, fruit='cherry', color=None)]

C [Row(key=10, fruit='apple', color='red'), Row(key=20, fruit='banana', color=None), Row(key=30, fruit='cherry', color='dark red')]

D [Row(key=10, fruit='apple', color='red'), Row(key=20, fruit='banana', color='red'), Row(key=30, fruit='cherry', color='dark red')]

💡 Hint

Remember that a left join keeps all rows from the left DataFrame and adds matching rows from the right.

The left join keeps all keys from df1. Keys 10 and 30 have matching colors, key 20 has no match so color is None.

🔧 Debug

advanced

Identify the error in broadcast join code

What error will this Spark code raise when executed?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast

spark = SparkSession.builder.getOrCreate()

large_df = spark.createDataFrame([(1, 'A'), (2, 'B')], ['id', 'val'])
small_df = spark.createDataFrame([(1, 'X'), (2, 'Y')], ['key', 'desc'])

joined = large_df.join(broadcast(small_df), large_df.id == small_df.key)
joined.show()

A No error, outputs joined DataFrame with 2 rows

B AnalysisException: 'Reference 'key' is ambiguous, could be: key, key.'

C TypeError: join condition must be a Column

D ValueError: Cannot broadcast a DataFrame with more than 100MB

💡 Hint

Check if the join columns have the same name or if they are ambiguous.

The join condition uses columns with different names but the result has duplicate column names causing ambiguity in output.

🚀 Application

advanced

Choosing broadcast join for performance

You have a large DataFrame with 10 million rows and a small DataFrame with 100 rows. Which Spark join strategy is best for performance?

A Use cartesian join to combine all rows from both DataFrames

B Use broadcast join to send the small DataFrame to all worker nodes

C Use shuffle join to redistribute both DataFrames across the cluster

D Use cross join with filter to reduce data size

💡 Hint

Think about minimizing data movement for small tables.

Broadcast join sends the small DataFrame to all nodes, avoiding expensive shuffles and improving performance.

🧠 Conceptual

expert

Effect of broadcast join on shuffle partitions

When using a broadcast join in Spark, what happens to the shuffle partitions compared to a regular shuffle join?

A Shuffle partitions are eliminated for the broadcasted table, reducing shuffle overhead

B Shuffle partitions increase because data is duplicated across nodes

C Shuffle partitions remain the same as in a regular shuffle join

D Shuffle partitions are replaced by a cartesian product

💡 Hint

Consider how broadcast join avoids shuffling the small table.

Broadcast join sends the small table to all executors, so no shuffle is needed for it, reducing shuffle partitions and overhead.

Practice

1.

What is the main benefit of using broadcast() in Spark when joining tables?

easy

A. It compresses the large table to save memory.

B. It sorts both tables before joining.

C. It sends the small table to all worker nodes to speed up the join.

D. It duplicates the large table across all nodes.

Solution

1. Step 1: Understand broadcast join purpose

   Broadcast join sends the small table to all worker nodes to avoid shuffling the large table.

2. Step 2: Identify the benefit

   This reduces network data movement and speeds up the join operation.

3. Final Answer:

   It sends the small table to all worker nodes to speed up the join. -> Option C

4. Quick Check:

   Broadcast join = send small table to all nodes [OK]

Hint: Broadcast small table to all nodes to avoid shuffle [OK]

Common Mistakes:

• Thinking broadcast compresses large table
• Assuming broadcast sorts tables
• Believing broadcast duplicates large table

2.

Which of the following is the correct syntax to broadcast a small DataFrame small_df in Spark before joining?

from pyspark.sql.functions import broadcast

result = large_df.join(____, 'id')

easy

A. small_df.join(broadcast())

B. small_df.broadcast()

C. broadcast(large_df)

D. broadcast(small_df)

Solution

1. Step 1: Recall broadcast syntax

   In Spark, you use broadcast() function around the small DataFrame to mark it for broadcasting.

2. Step 2: Apply to join

   The join should be large_df.join(broadcast(small_df), 'id') to broadcast the small table.

3. Final Answer:

   broadcast(small_df) -> Option D

4. Quick Check:

   Use broadcast() function on small_df [OK]

Hint: Use broadcast() around small DataFrame in join [OK]

Common Mistakes:

• Calling broadcast as a method on DataFrame
• Broadcasting the large DataFrame instead
• Incorrect join syntax with broadcast

3.

Given the following Spark code, what will be the output count?

from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast

spark = SparkSession.builder.getOrCreate()

large_data = [(1, 'A'), (2, 'B'), (3, 'C')]
small_data = [(1, 'X'), (2, 'Y')]

large_df = spark.createDataFrame(large_data, ['id', 'val1'])
small_df = spark.createDataFrame(small_data, ['id', 'val2'])

joined_df = large_df.join(broadcast(small_df), 'id')
print(joined_df.count())

medium

A. 2

B. 5

C. 3

D. 0

Solution

1. Step 1: Analyze join keys and data

   large_df has ids 1, 2, 3; small_df has ids 1, 2. Join on 'id' keeps only matching ids.

2. Step 2: Count matching rows

   Ids 1 and 2 match in both tables, so joined_df has 2 rows.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Join count = matching ids = 2 [OK]

Hint: Count matching keys in both tables after join [OK]

Common Mistakes:

• Counting all rows from large_df ignoring join
• Assuming broadcast changes join result count
• Counting unmatched rows

4.

Identify the error in the following Spark code that attempts a broadcast join:

from pyspark.sql.functions import broadcast

result = large_df.join(small_df.broadcast(), 'id')

medium

A. Missing import for broadcast function.

B. Using broadcast as a method on DataFrame is incorrect.

C. Join key 'id' does not exist in both DataFrames.

D. Broadcasting large_df instead of small_df.

Solution

1. Step 1: Check broadcast usage

   Broadcast is a function, not a method on DataFrame, so small_df.broadcast() is invalid syntax.

2. Step 2: Correct usage

   Use broadcast(small_df) instead to mark small_df for broadcasting.

3. Final Answer:

   Using broadcast as a method on DataFrame is incorrect. -> Option B

4. Quick Check:

   broadcast() is a function, not a DataFrame method [OK]

Hint: Use broadcast() function, not DataFrame.broadcast() [OK]

Common Mistakes:

• Calling broadcast as a DataFrame method
• Ignoring import of broadcast function
• Confusing which DataFrame to broadcast

5.

You have a large DataFrame orders with millions of rows and a small DataFrame countries with 10 rows. You want to join them on country_code. Which approach will give the best performance?

hard

A. Use orders.join(broadcast(countries), 'country_code') to broadcast the small table.

B. Use orders.join(countries, 'country_code') without broadcasting.

C. Broadcast the large orders DataFrame instead.

D. Collect both DataFrames to driver and join locally.

Solution

1. Step 1: Identify table sizes

   Since countries is small, broadcasting it avoids shuffling the large orders DataFrame.

2. Step 2: Choose best join method

   Broadcast join sends the small table to all nodes, speeding up the join and reducing network traffic.

3. Final Answer:

   Use orders.join(broadcast(countries), 'country_code') to broadcast the small table. -> Option A

4. Quick Check:

   Broadcast small table for best join performance [OK]

Hint: Broadcast the small table in large-small joins [OK]

Common Mistakes:

• Broadcasting the large DataFrame wastes memory
• Not broadcasting causes expensive shuffle
• Collecting large data to driver causes failure