Skip to content

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.

Study it

Sample questions

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?

  1. A1, because order 2's NULL key matches the Unknown customer
  2. B0, because orders without a matching customer are dropped
  3. C2, because orders 2 and 3 find no customer and keep a NULL name
  4. 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.

Question 2 · choose 1

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?

  1. A5
  2. B3, one row per order
  3. C9, every order paired with every price
  4. 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.

Question 3 · choose 1

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?

  1. AA broadcast hash join that sends stores to every executor, as the hint asks
  2. BNone: the query fails, because Spark cannot honor the broadcast hint
  3. CA shuffle-based join, because the left side of a LEFT OUTER join can't be broadcast
  4. 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.

Question 4 · choose 1

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?

  1. A1, a single x
  2. B2, x twice
  3. C4, every matching pair of x rows
  4. 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.

Question 5 · choose 1

orders has a column cust_id and customers has a column id that holds the same values. Which call joins each order to its customer?

  1. Aorders.join(customers, on="cust_id")
  2. Borders.join(customers, orders.cust_id == customers.id)
  3. Corders.join(customers, on=["cust_id", "id"], how="inner")
  4. Dorders.join(customers, how="cust_id = id")
Show the answer and why
  • Aorders.join(customers, on="cust_id")

    Incorrect

    A column name in on must exist on both sides; customers has no cust_id.

  • Borders.join(customers, orders.cust_id == customers.id)

    Correct

    on accepts a join expression, which compares columns with different names on the two sides.

  • Corders.join(customers, on=["cust_id", "id"], how="inner")

    Incorrect

    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.

Question 6 · choose 1

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?

  1. Aa, because it is the first table named in the hint
  2. BBoth sides, because both are hinted
  3. CNeither: two broadcast hints cancel each other
  4. 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.

Databricks documentation

Practice domain 3 →Practice all domains →