Data Engineer Interview Questions Set2

Let’s directly compare Partitioning vs Bucketing in Spark from an optimization point of view.

✅ TL;DR Answer

Purpose	Best Choice
Filtering / Scanning	✅ Partitioning
Joining Large Tables	✅ Bucketing

Feature	Partitioning	Bucketing
Definition	Splits data into directory-based partitions	Splits data into fixed number of hash buckets
Best For	Optimizing filters and reads	Optimizing joins and aggregations
Join Optimization	❌ No shuffle avoidance	✅ Can avoid shuffles if bucketed on same column
Filtering Speed	✅ Spark skips irrelevant partitions	❌ Buckets don’t help filtering directly
Storage	One folder per partition value	One folder per bucket; same table structure
Flexibility	High – works in all file formats	Requires Hive table and bucket config
Limitations	Too many partitions = small file problem	Must specify number of buckets at write time
Example Write	`.write.partitionBy("col")`	`.write.bucketBy(4, "col").sortBy("col").saveAsTable(...)`
Use in PySpark	✅ Easy, supported with Parquet/Delta	⚠️ Works only via Hive-compatible tables

Use Case	Use Which?
Filter last month’s data	Partitioning
Join fact table with dim table	Bucketing
Drill-down dashboard filters	Partitioning
Joining 2 big tables by user_id	Bucketing
Loading daily/hourly batches	Partitioning
Avoiding join shuffle overhead	Bucketing

If your goal is…	Then use…
Fast reads with selective filters	✅ Partitioning
Fast joins on large tables	✅ Bucketing
Reduce shuffle + sort overhead in joins	✅ Bucketing
Schema evolution & flexible formats	✅ Partitioning

You can combine both for best performance:

df.write.partitionBy("dt").bucketBy(4, "user_id").sortBy("user_id").saveAsTable("fact_table")

🔥 Partition on date for filtering, and bucket on user_id for efficient joins.