1. The Problem
Partitioning dataset by `tenant_id` (4,500 tenants) and `date` generated 150,000 tiny Parquet files averaging 4 KB each, causing Spark query listing operations to stall.
2. What I Initially Thought
3. What I Learned
Optimal Parquet file size is 128 MB to 1 GB. Over-partitioning creates the 'Small File Problem' where cloud storage API listing time exceeds actual data reading time.
4. What I Built
Partition optimization script coalescing small partition outputs into target 128 MB file sizes and re-partitioning by date only (`year=YYYY/month=MM`).
# Coalesce output partitions before writing to storage
df.write \
.mode("overwrite") \
.partitionBy("year", "month") \
.option("maxRecordsPerFile", 500000) \
.parquet("abfss://silver/tasks/")5. The Experiment
Dataset over-partitioned into 150,000 tiny files (4 KB each). Query listing overhead: 2.1 minutes.
Re-partitioned storage by `year/month` and compacted small files into 128 MB Parquet files.
File count reduced from 150,000 files to 42 target files. Query duration dropped from 2.1 minutes to 1.8 seconds.
6. What Went Wrong
Used `repartition(1)` initially, which forced full cluster dataset shuffle onto a single executor node, causing an OOM crash.
7. Engineering Decision & Trade-offs
Established strict rule: Only partition datasets larger than 100 GB, and limit partition key cardinality to < 100 distinct values.
8. What I Would Do Differently in Production
In Delta Lake or Databricks, schedule automated `OPTIMIZE` and `ZORDER` jobs nightly to compact small files continuously.
Questions I Can Now Answer Confidently in an Interview:
- What is the Small File Problem in distributed storage engines like HDFS or Azure ADLS?
- What is the difference between repartition() and coalesce() in Apache Spark?
- How do you choose the right partition key for a cloud data lake table?