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.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
Two questions: which rows you want, and how Spark will compute them.
Which rows
Logical join types
| Join | Returns | Use it when |
|---|---|---|
| Inner | Rows that match on both sides | You only want records present in both. |
| Left outer | All left rows, nulls where the right has no match | Keep every fact row even if the dimension is missing. The most common warehouse join. |
| Full outer | All rows from both sides | Reconciliation: find what is missing on either side. |
| Left semi | Left rows that have a match, left columns only, no duplication | "Does it exist?" checks. Faster and cleaner than IN or EXISTS. |
| Left anti | Left rows with no match on the right | Find missing or new records, incremental loads of rows not yet in the target. |
| Cross | Every left row paired with every right row | Tiny lookup tables only. Dangerous on big data. |
| Non-equi (range) | Rows matching a condition like a BETWEEN b AND c | Time-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
| Strategy | How it works | Best when | Watch out for |
|---|---|---|---|
| Broadcast hash | The 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 hash | Both 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-merge | Both sides shuffled and sorted, then merged. | The default for two big tables; spills safely. | Expensive shuffle and sort; sensitive to skew. |
| Broadcast nested loop | Compares every pair, one side broadcast. | Non-equi conditions with one small side. | Quadratic cost. |
| Cartesian | Every pair across partitions. | Explicit cross joins only. | Often an accident from a missing condition. |
Decide
Choosing in practice
| Situation | What to do |
|---|---|
| Big fact with small dimension | Broadcast the dimension; keep statistics current with ANALYZE. |
| Two big tables | Sort-merge; filter and select columns before the join to shrink the shuffle. |
| Same two big tables joined often | Cluster both on the join key. This cuts files read; it does not remove the shuffle by itself. |
| Hot key | See the skew page. |
| Range or time lookup | Turn 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 Delta | MERGE with the key in the ON clause plus a cluster or partition filter. |
| Streaming | Stream 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
| Problem | Symptom | Fix |
|---|---|---|
| Skew | One task far slower than the median | AQE skew handling, isolate or salt the hot key. |
| Null keys | One huge partition of nulls | Nulls never match in an equi-join: filter them out and union back for outer joins. |
| Duplicate keys on both sides | Row count explodes | Check key uniqueness first; deduplicate the dimension; count rows before and after. |
| Key types differ | Implicit cast, poor plan, wrong matches | Cast both sides to the same type. |
| Join on expressions | ON lower(a.x) = lower(b.x) blocks optimizations | Store the normalized column and join on it. |
| Accidental cross join | Very long job | Check the condition. |
| Broadcast timeout | Could not execute broadcast in 300 secs | Remove 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
- Left semi versus inner join: when is semi better?
- Why is NOT IN risky and what do you use instead?
- How does Spark decide on a broadcast, and how can one fail the driver?
- A big join is slow because of one hot key. Walk through your fixes.
- How would you join events to a table of validity date ranges?
- After a join, row counts doubled. How do you debug it?
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