Your job writes thousands of tiny files and queries on the output are slow. How do you fix it?

Control how many tasks write and how the rows are spread across them, so each output file is a useful size, and compact what is already there. Many small files make every reader pay to list and open each one. The usual causes are writing straight after a shuffle with many partitions, partitioning the table by a column with too many values, or a streaming job that writes every few seconds.

Why it happens

Spark writes in parallel: every task writes its own files. The number of files is roughly the number of writing tasks times the number of table partitions each task's rows fall into.

• A write after a join or aggregation uses the shuffle's partition count, 200 by default, even for a small result.
• partitionBy("customer_id") on a column with 100,000 values creates folders that each hold a few rows, multiplied by the tasks that touched them.
• A stream that commits every 10 seconds writes new files every 10 seconds.

Fixes when writing

1. Size the write. Bring the data into fewer, fuller partitions before writing:

   (df.repartition("event_date")          # rows of one date go to one task
      .write.partitionBy("event_date")
      .format("delta").mode("append").save(path))
   

   For an unpartitioned table, coalesce(n) with n from the output size divided by the file size you want (Databricks aims optimized writes at 128 MB, a reasonable default). Repartitioning by the table's partition column means each folder gets files from one task, not from all of them.
2. Partition the table by a low-cardinality column. Partition by date, not by customer or ID. If you need fast lookups on a high-cardinality column, use clustering inside the files instead of folders.
3. Let the platform size files. On Delta tables in Databricks, optimized writes add a shuffle before the write to aim for about 128 MB files, and auto compaction merges small files after a write. Unity Catalog managed tables get much of this tuned for you.
4. Cap huge files too. maxRecordsPerFile stops a single task from writing one enormous file, which matters after a coalesce(1).
5. Streaming: use a longer trigger interval when the latency allows it, and compact the table on a schedule.

Fixes for files already written

• Delta Lake: run OPTIMIZE on the table, or on the recent partitions only. Large tables need it on a schedule, since auto compaction is not a full replacement.
• Plain Parquet: read the partition, rewrite it with the right number of files, and swap it in.
• The replaced small files stay in storage until VACUUM removes them, by default only once they are older than 7 days, so plan for the overlap.

Trade-offs to mention

A repartition before writing adds a shuffle, so writes take longer. That is usually worth it for a table read many times, and not for a scratch table read once.

What interviewers want: why the file count is tasks times partitions, a write-side fix (repartition by the partition column, sensible partitioning, optimized writes) and a read-side fix (compaction), with the trade-off stated.