Data-Engineer-Associate - Data Transformation and Modeling (22% of the exam) - Section 3.2

Combine DataFrames with inner, left, broadcast, multiple-key and cross joins, and with union and union all.

Choose and write the right join for a requirement, including joins on multiple keys, broadcast joins for a small dimension table, and cross joins. Distinguish SQL UNION from UNION ALL and PySpark union from unionByName, and predict row counts.

inner and left joinsbroadcast joincross joinUNION versus UNION ALLunionByName

Practice question for this objective

Free sampleData Transformation and Modelingmedium

A pipeline joins a clickstream DataFrame of 12 million sessions to a customer DataFrame with an inner join on customer_id, and the marketing team reports that the daily session count in the resulting report is about 8 percent below the raw feed. Anonymous sessions carry a null customer_id and have no matching customer row. The report has to retain every session, with the customer attributes empty where no customer exists. Which change meets the requirement?

report = sessions.join(customers, on="customer_id", how="inner")

AKeep the inner join and add a second pass that unions the unmatched sessions back on, with the customer columns dropped from that side.
BChange the join type to cross, so that every session is paired with the customer DataFrame and no session row is discarded by the join.
CChange the join type to left, so that every session row is retained and the customer columns hold nulls wherever no match is found. Correct
DReplace the null customer_id values with an empty string before the inner join, so that the anonymous sessions find a match on the customer side.

A left join keeps every row of the left side and nulls the right side columns, whereas an inner join discards unmatched rows. An inner join emits a row only where the join condition is satisfied on both sides, so sessions with no customer, including those whose customer_id is null, are dropped. A left join emits every left row unconditionally and supplies nulls for the right side columns when no match exists, which keeps the session count intact while still attaching customer attributes where they are available.

Why A is wrong: This does recover the missing sessions, so it looks workable, but dropping the customer columns leaves the two sides with different schemas, so the union is rejected and a single join type already gives the wanted result.

Why B is wrong: A cross join does keep every session, which is the surface appeal, but it pairs each session with every customer row, multiplying the report to an enormous size instead of preserving one row per session.

Why C is correct: A left join preserves every row of the left side regardless of whether the right side matches, filling the right side columns with nulls, which is precisely the outcome the report requires.

Why D is wrong: Substituting a sentinel value is a familiar tactic for null handling, but no customer row carries an empty customer_id either, so the inner join still discards those sessions and the count remains short.

See more Data-Engineer-Associate practice questions, answers explained.

Exam traps in Data Transformation and Modeling

Answers that look right on this material and are not. Each one is a distractor from a different question in the Data-Engineer-Associate bank for this domain.

Raise spark.sql.shuffle.partitions from its default of 200 to 800, so that the join stage divides its shuffled data into far smaller tasks across the workers.
Why it is wrong: Tempting because a slow shuffle join often does benefit from a different partition count, but this setting only sizes the shuffle that is already planned and never changes which join strategy the planner picks.
The threshold caps the total broadcast memory a single query may use across all executors, so raising it to 100 MB reserves that quantity of heap on every worker for the duration of the join.
Why it is wrong: This is tempting because a broadcast really does consume executor heap, but the setting is a planning threshold compared against an estimated table size rather than a reservation of memory on the workers.
Three rows, because a left join keeps only the keys present on both sides and quietly discards the two orders whose customer_id has no matching customer row.
Why it is wrong: This describes an inner join. It is a common slip because both joins return the same three matched rows, but the left join additionally preserves every unmatched row from its left input.

Examworthy is not affiliated with or endorsed by Databricks. Original, blueprint-aligned practice material only.