What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?

Partitioning physically divides data into separate directories based on column values, enabling Spark to skip irrelevant data during queries (partition pruning). Bucketing, conversely, organizes data within partitions into a fixed number of files by hashing a column's value, co-locating rows with the same key to optimize joins and aggregations.

Mechanics and Benefits

Partitioning creates a hierarchical directory structure (e.g., data/year=2023/month=01/). When a query filters on a partitioned column, Spark's query optimizer can perform "partition pruning," reading only the necessary directories, significantly reducing I/O. This is a fundamental optimization for data lakes built on systems like HDFS or cloud object storage (e.g., S3).

Bucketing takes this a step further. Within each partition, data is hashed across a predefined number of files (buckets). Rows with the same bucketing key (e.g., user_id) are guaranteed to reside in the same bucket file. This co-location is crucial for performance. When performing joins or group-by operations on the bucketing column, Spark can avoid a full data shuffle across the cluster. If two tables are bucketed on their join key with the same number of buckets, Spark can perform a highly efficient bucket-aware sort-merge join, reading only corresponding bucket files from each table.

When to Use Bucketing and Trade-offs

Bucketing is most beneficial for tables frequently involved in joins or aggregations on a specific high-cardinality column, such as user_id or product_id. It trades a higher write cost (due to the additional shuffle required to distribute data into buckets) for significant read-time performance gains.

df.write \
  .partitionBy("event_date") \
  .bucketBy(100, "user_id") \
  .mode("overwrite") \
  .saveAsTable("my_bucketed_table")

Trade-offs: While partitioning reduces data scanned, too many partitions can lead to the "small file problem," where numerous tiny files incur high metadata overhead and inefficient processing. Bucketing helps mitigate this by ensuring a fixed number of files per partition. However, an excessively high number of buckets can also increase metadata overhead.

In the interview, also mention that bucketing is analogous to clustering keys in data warehouses like Snowflake, where data with similar values is physically stored together to optimize query performance.