Home › Learn › Databricks interview prep › Joins

Databricks interview prep · data engineering

Join types and which to use when

Inner, outer, semi, anti, cross and range joins, then broadcast, shuffle-hash and sort-merge: which to use when, plus the join problems interviewers probe.

Maintenance commandsRow-level securityCompute choiceJoinsSkew and shuffleAQE, Photon, POCost and performance leversDeclarative and semanticAsset BundlesLakeflow ConnectUniFormVector Searchdbt and ODCSEgress and ingressMaintenance in picturesClustering and compactionMaintenance strategySlow writesBackup and recoveryTaggingQuestion bank

Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.

What rows do I want?(logical join)How big is each side?Spark picks a strategy:broadcast, sort-merge,shuffle hashAQE can change it at runtime

Two questions: which rows you want, and how Spark will compute them.

Which rows

Logical join types

JoinReturnsUse it when
InnerRows that match on both sidesYou only want records present in both.
Left outerAll left rows, nulls where the right has no matchKeep every fact row even if the dimension is missing. The most common warehouse join.
Full outerAll rows from both sidesReconciliation: find what is missing on either side.
Left semiLeft rows that have a match, left columns only, no duplication"Does it exist?" checks. Faster and cleaner than IN or EXISTS.
Left antiLeft rows with no match on the rightFind missing or new records, incremental loads of rows not yet in the target.
CrossEvery left row paired with every right rowTiny lookup tables only. Dangerous on big data.
Non-equi (range)Rows matching a condition like a BETWEEN b AND cTime-range and SCD lookups; needs care, see below.
ASOF (point in time)The nearest earlier row"Price as of the trade time". Otherwise use a window function.

Use semi and anti joins instead of inner join plus DISTINCT, and instead of NOT IN, which returns nothing if the right side contains one null.

How computed

Physical strategies

StrategyHow it worksBest whenWatch out for
Broadcast hashThe small table is sent to every executor; no shuffle of the big table.One side is small (default threshold about 10 MB; AQE can switch at run time).The small side goes through the driver; a table that is not small causes driver OOM and timeouts.
Shuffle hashBoth sides shuffled by key, one side built into a hash table per partition.One side much smaller but too big to broadcast.Memory per partition; a hot key can overflow it.
Sort-mergeBoth sides shuffled and sorted, then merged.The default for two big tables; spills safely.Expensive shuffle and sort; sensitive to skew.
Broadcast nested loopCompares every pair, one side broadcast.Non-equi conditions with one small side.Quadratic cost.
CartesianEvery pair across partitions.Explicit cross joins only.Often an accident from a missing condition.

Decide

Choosing in practice

SituationWhat to do
Big fact with small dimensionBroadcast the dimension; keep statistics current with ANALYZE.
Two big tablesSort-merge; filter and select columns before the join to shrink the shuffle.
Same two big tables joined oftenCluster both on the join key. This cuts files read; it does not remove the shuffle by itself.
Hot keySee the skew page.
Range or time lookupTurn it into an equi-join by joining on a time bucket first, then filter exactly. Otherwise Spark falls back to nested loop.
Incremental merge into DeltaMERGE with the key in the ON clause plus a cluster or partition filter.
StreamingStream to static re-reads the static side each batch. Stream to stream needs watermarks and a time bound so state can be cleaned.

Hard problems

When a join goes wrong

ProblemSymptomFix
SkewOne task far slower than the medianAQE skew handling, isolate or salt the hot key.
Null keysOne huge partition of nullsNulls never match in an equi-join: filter them out and union back for outer joins.
Duplicate keys on both sidesRow count explodesCheck key uniqueness first; deduplicate the dimension; count rows before and after.
Key types differImplicit cast, poor plan, wrong matchesCast both sides to the same type.
Join on expressionsON lower(a.x) = lower(b.x) blocks optimizationsStore the normalized column and join on it.
Accidental cross joinVery long jobCheck the condition.
Broadcast timeoutCould not execute broadcast in 300 secsRemove the hint or pre-filter.

Tools

Hints

SELECT /*+ BROADCAST(d) */ ...          -- force broadcast of d
SELECT /*+ MERGE(a, b) */ ...           -- force sort-merge
SELECT /*+ SHUFFLE_HASH(b) */ ...       -- prefer shuffle hash, b as build side
SELECT /*+ SKEW('a', 'key') */ ...      -- skew hint (Databricks)

Use hints only after reading EXPLAIN. A broadcast hint on a table that doubles in size becomes tomorrow's outage.

Practice

Questions to rehearse

  1. Left semi versus inner join: when is semi better?
  2. Why is NOT IN risky and what do you use instead?
  3. How does Spark decide on a broadcast, and how can one fail the driver?
  4. A big join is slow because of one hot key. Walk through your fixes.
  5. How would you join events to a table of validity date ranges?
  6. After a join, row counts doubled. How do you debug it?
In an interview, say: Pick the join by the question you are asking: match, keep all, exists, missing. Then make the plan fit the sizes: broadcast the small side, sort-merge for two big sides, and fix nulls and skew before they take the job down.

FAQ

Questions people ask

What is the difference between a semi join and an inner join?

A semi join only filters the left rows by existence on the right and never duplicates them. An inner join returns columns from both and can multiply rows.

Why did my plan change at run time?

Adaptive query execution re-plans after shuffle stages using real sizes. Read the final plan in the query profile.

Next

Where to go next

All Databricks interview topics · All data engineering · Databricks on this site