Design a System for Handling Big Data Joins

To design a system joining 100TB of user listening history with artist metadata, I first categorize the datasets by size. If one table is small enough to fit in memory, I would use a Broadcast Join in Spark to replicate it across all executors, avoiding expensive shuffling. For two massive tables, a standard shuffle join is necessary but risky due to potential data skew. I would implement a two-phase strategy. First, perform a global aggregation or 'salting' technique on the join key to redistribute data evenly across partitions, preventing single-node bottlenecks common in high-traffic scenarios. Second, utilize Spark's Catalyst optimizer to push down filters before the join operation, reducing the dataset size early. For storage, I'd enforce columnar formats like Parquet to minimize I/O overhead. Finally, considering Spotify's focus on personalization, I'd ensure the pipeline supports incremental updates rather than full re-runs, perhaps leveraging Delta Lake or similar technologies to manage schema evolution and data consistency without disrupting downstream recommendation services.