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.
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.
- Validate dimension uniqueness.
- Broadcast the small dimension.
- 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?