← PySpark roadmap

Topic 26 of 28 · Advanced

Enrich orders with a small unique customer dimension

Left-join orders to a small customer dimension without losing unmatched orders.

AdvancedMid-level · 3–5 yearsJoins

Apache Spark / PySpark 3.5+ with a local JVM. Not executable inside a plain Python browser runtime.

Problem statement

Left-join orders to a small customer dimension without losing unmatched orders.

A large fact table uses a memory-bounded dimension for enrichment.

Input: orders(order_id,customer_id,amount); customers(customer_id,city).

Output: order_id, city, NULL for missing customers.

  • Dimension customer IDs unique.
  • Dimension must fit executor broadcast memory in the real workload.

Sample 1

Input: orders10,20; customers10:A
Output: 10:A,20:NULL

Left join retains unmatched fact.

Sample 2

Input: no orders
Output: empty

No rows manufactured by a left join.

Brute-force solution

A sort-merge join shuffles both sides by customer ID; correct but often unnecessary for a truly small dimension.

O(fact rows + executors × dimension rows) expected distribution/probing; demonstration sorting extra.

from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
orders=spark.createDataFrame([(1,10,5),(2,20,7),(3,30,9)],"order_id int, customer_id int, amount int")
customers=spark.createDataFrame([(10,"A"),(20,"B")],"customer_id int, city string")
result=orders.join(F.broadcast(customers),"customer_id","left").select("order_id","city").orderBy("order_id")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,"A"),(2,"B"),(3,None)]
print(rows)
spark.stop()
Optimized solution

Broadcast the small side and probe it while preserving all order rows.

  1. Validate dimension uniqueness.
  2. Broadcast the small dimension.
  3. Use a left join on a named key.
from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
orders=spark.createDataFrame([(1,10,5),(2,20,7),(3,30,9)],"order_id int, customer_id int, amount int")
customers=spark.createDataFrame([(10,"A"),(20,"B")],"customer_id int, city string")
result=orders.join(F.broadcast(customers),"customer_id","left").select("order_id","city").orderBy("order_id")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,"A"),(2,"B"),(3,None)]
print(rows)
spark.stop()
Complexity analysis

Time: O(fact rows + executors × dimension rows) expected distribution/probing; demonstration sorting extra.

Auxiliary space: O(dimension size) per executor plus fact partition workspace.

Test cases
  • Missing customer.
  • Empty dimension with explicit schema.
  • Duplicate dimension keys violate input contract.
Supplied script asserts three rows including NULL for missing customer30.
Common mistakes
  • Broadcasting an unbounded dimension.
  • Using inner join and dropping unmatched facts.
Key takeaways
  • Validate join cardinality.
  • Broadcast under a memory budget.
  • Preserve left-row semantics.
Follow-up challenges
  • How would you handle a skewed large-to-large join?
  • How would you validate dimension uniqueness?
Practise PySpark with the AI mentor

Can't find a PySpark topic you need for exams or placements? Request it and we'll add it.