Associate Developer for Apache Spark · Domain 3: Developing Apache Spark DataFrame/DataSet API Applications · 30% of the exam
Task 3.4: Combine DataFrames with operations such as Inner join, left join, broadcast join, multiple keys, cross join, union, and union all; Describe the purpose and implementation of broadcast joins
Join types and keys on given rows, cross joins, union by position against unionByName, and when and how a broadcast join is planned.
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
A developer runs:
from pyspark.sql import functions as F
orders = spark.createDataFrame([(1, 10), (2, None), (3, 30)], "order_id INT, cust_id INT")
custs = spark.createDataFrame([(10, "Ana"), (None, "Unknown")], "cust_id INT, name STRING")
res = orders.join(custs, on="cust_id", how="left")
print(res.filter(F.col("name").isNull()).count())
What does it print?
A1, because order 2's NULL key matches the Unknown customer
B0, because orders without a matching customer are dropped
C2, because orders 2 and 3 find no customer and keep a NULL name
DThe join raises an error, because both sides hold a NULL in cust_id
Show the answer and why
A1, because order 2's NULL key matches the Unknown customer
Incorrect
In an equi-join two NULL keys are not equal, so order 2 does not match the Unknown row.
B0, because orders without a matching customer are dropped
Incorrect
A left join returns all rows of the left side and appends NULL where there is no match.
C2, because orders 2 and 3 find no customer and keep a NULL name
Correct
Order 3 has no matching key and order 2's NULL key matches nothing, and the left join keeps both with NULL names.
DThe join raises an error, because both sides hold a NULL in cust_id
Incorrect
NULL keys are allowed; a join condition that evaluates to NULL is simply not satisfied.
Joining on a column name performs an equi-join. A join condition is satisfied only when it is True, and two NULL values are not equal, so a NULL key never matches, not even a NULL on the other side. A left join returns all values from the left relation and the matched values from the right, appending NULL where there is no match. Orders 2 and 3 therefore come back with a NULL name, and order 1 matches Ana.
prices holds two rows for sku A by mistake. A developer runs:
orders = spark.createDataFrame([(1, "A"), (2, "A"), (3, "B")], "order_id INT, sku STRING")
prices = spark.createDataFrame([("A", 10.0), ("A", 12.0), ("B", 7.0)], "sku STRING, price DOUBLE")
print(orders.join(prices, on="sku", how="inner").count())
What does it print?
A5
B3, one row per order
C9, every order paired with every price
D2, one row per distinct sku
Show the answer and why
A5
Correct
An inner join returns every pair of rows with matching keys: two A orders times two A prices, plus one B pair.
B3, one row per order
Incorrect
An inner join does not keep one row per left row; each order is paired with every price row of its sku.
C9, every order paired with every price
Incorrect
Only rows with matching sku values are paired; pairing every row with every row is a cross join.
D2, one row per distinct sku
Incorrect
A join does not group or deduplicate by key; it returns the matching row pairs.
An inner join, the default join type, returns the rows that have matching values in both relations: each left row is paired with every right row whose key matches. Orders 1 and 2 each match both A prices (four rows) and order 3 matches the B price (one row), so the count is 5. A duplicate key on one side multiplies the rows of the result.
On Databricks, stores has 500 rows and sales holds 2 TB. A report must list
every store with its sales, if any, and a developer writes:
from pyspark.sql import functions as F
report = F.broadcast(stores).join(sales, on="store_id", how="left")
Which join does Spark plan?
AA broadcast hash join that sends stores to every executor, as the hint asks
BNone: the query fails, because Spark cannot honor the broadcast hint
CA shuffle-based join, because the left side of a LEFT OUTER join can't be broadcast
DA broadcast hash join that sends sales, because Spark moves the hint to the other side
Show the answer and why
AA broadcast hash join that sends stores to every executor, as the hint asks
Incorrect
stores is the left side of a LEFT OUTER join, and the left side of a LEFT OUTER join can't be broadcast.
BNone: the query fails, because Spark cannot honor the broadcast hint
Incorrect
A hint is a suggestion; when the strategy does not support the join type, Spark plans another one instead of failing.
CA shuffle-based join, because the left side of a LEFT OUTER join can't be broadcast
Correct
The left side of a LEFT OUTER join can't be broadcast, and Spark is not guaranteed to use a hinted strategy that does not support the join type, so it plans a shuffle join.
DA broadcast hash join that sends sales, because Spark moves the hint to the other side
Incorrect
The hint marks stores only; nothing moves it to sales, which is the 2 TB side.
broadcast() marks a DataFrame for a broadcast join, but a join hint is a suggestion: since a strategy may not support every join type, Spark is not guaranteed to use the hinted strategy. A LEFT OUTER join keeps every left row, so its left side can't be broadcast; Databricks lists this as a reason a small table is not broadcast. Here stores is on the left, so Spark plans a shuffle-based join. Keeping every store while broadcasting it needs another formulation of the query.
A developer runs:
a = spark.createDataFrame([("x",), ("x",), ("y",)], "k STRING")
b = spark.createDataFrame([("x",), ("x",), ("z",)], "k STRING")
print(a.intersect(b).count())
What does it print?
A1, a single x
B2, x twice
C4, every matching pair of x rows
D0, no common rows
Show the answer and why
A1, a single x
Correct
intersect returns the rows found in both DataFrames and removes duplicates, so x appears once.
B2, x twice
Incorrect
Keeping both x rows is what intersectAll does; intersect removes duplicates.
C4, every matching pair of x rows
Incorrect
intersect returns rows present in both DataFrames, not every matching pair.
D0, no common rows
Incorrect
x occurs in both DataFrames, so it is in the result.
DataFrame.intersect(other) returns a new DataFrame containing rows only in both this DataFrame and the other, and any duplicates are removed; intersectAll preserves duplicates. Only x is in both inputs, so the result has one row.
Each name in the list must exist on both sides, and the join then requires all of them to match.
Dorders.join(customers, how="cust_id = id")
Incorrect
how names the join type, such as inner or left; it does not take a condition.
DataFrame.join(other, on, how) accepts as on a column name, a list of column names, a join expression (Column) or a list of Columns. Names in on must exist on both sides and produce an equi-join, so when the key columns are named differently, a join expression such as orders.cust_id == customers.id is used. how is the join type and defaults to inner.
A Spark SQL query is written as SELECT /*+ BROADCAST(a), BROADCAST(b) */ * FROM a JOIN b ON a.k = b.k, with table statistics available for both tables. Which side does Spark broadcast?
Aa, because it is the first table named in the hint
BBoth sides, because both are hinted
CNeither: two broadcast hints cancel each other
DThe smaller of a and b, based on the statistics
Show the answer and why
Aa, because it is the first table named in the hint
Incorrect
When both sides carry the broadcast hint, Spark chooses by size, not by position.
BBoth sides, because both are hinted
Incorrect
One side is broadcast: the smaller one, based on stats.
CNeither: two broadcast hints cancel each other
Incorrect
Hinting both sides is allowed; Spark picks one side.
DThe smaller of a and b, based on the statistics
Correct
If both sides of the join have broadcast hints, the one with the smaller size, based on stats, is broadcast.
The BROADCAST join hint (aliases BROADCASTJOIN and MAPJOIN) suggests a broadcast join. If both sides of the join have broadcast hints, the side with the smaller size, based on statistics, is broadcast.