Your nightly Spark job writes tens of thousands of tiny files, and the job that reads them the next morning is slow. What happened, and what do you change?

Where the file count comes from

The number of files a write produces is not something Spark chooses on your behalf. It is the number of tasks in the final stage multiplied by the number of distinct partition values each of those tasks happens to hold, because every task writes independently and cannot write into another task's file.

That multiplication is where the explosion lives. If the stage before the write is a shuffle, its parallelism defaults to spark.sql.shuffle.partitions, which is 200 unless you changed it. If you then write with a partitioning clause on a column with a thousand distinct values, and the rows for each value are spread across all 200 tasks because the shuffle hashed on something else entirely, you get up to two hundred thousand files, each holding a small fraction of a partition's rows. Nothing failed. The write succeeded, the row count is right, and the layout is unusable.

Being able to say that out loud, in that form, is most of the answer. It also tells you which two levers exist: reduce the number of writing tasks, or make each task hold few partition values.

Why the reader pays more than the writer

The write stage is often barely slower, which is why this is discovered the next morning rather than the night before. The cost lands on whoever reads the data, for reasons that are cumulative rather than dramatic.

Listing dominates first. Before any data is read, the driver must enumerate the files, and on object storage that is a paginated series of round trips whose latency scales with the number of objects rather than their size. Then each Parquet file is a self-contained unit with a footer holding its schema and its statistics, so a reader opens, seeks and parses metadata per file, and the fixed cost per open stops being negligible once the payload behind it is a few kilobytes. Spark accounts for this explicitly when it plans splits: spark.sql.files.openCostInBytes adds a notional size to every file so that many tiny files are not packed into one task as if they were free.

The deeper loss is that Parquet's advantages stop working. Predicate pushdown and column pruning rely on row-group statistics covering enough rows to be selective, and a file containing a handful of rows has statistics that exclude nothing. Compression suffers for the same reason, since dictionary and run-length encoding need repetition within a column chunk to have anything to compress. You end up with a columnar format performing like a row format, at a higher metadata cost.

Repartition to the write layout; do not coalesce to one task

The tempting fix is coalesce(1), and it is the wrong one for a reason worth understanding. coalesce does not shuffle: it merges partitions by narrowing the existing stage, and that narrowing propagates backwards. Coalescing to one partition before the write does not just make one task write one file, it makes one task perform the entire preceding computation, so a job that used two hundred cores for its aggregation now uses one for everything after the last shuffle.

repartition shuffles, which costs a stage but preserves upstream parallelism and, more importantly, lets you choose the distribution. Repartitioning on the same columns you partition the output by is what collapses the multiplication: after the shuffle, all rows for a given partition value sit in one task, so that value produces one file rather than two hundred.

# One task per output partition value: files = number of distinct values.
(df.repartition("event_date", "country")
   .write
   .partitionBy("event_date", "country")
   # Caps a single skewed value at a bounded file size instead of one huge file.
   .option("maxRecordsPerFile", 2_000_000)
   .parquet(path))

The maxRecordsPerFile option is the counterweight to the repartition: it stops the largest partition value becoming one enormous file that no reader can split usefully, which is the failure you create when you over-correct. Adaptive query execution, enabled by default since Spark 3.2, will coalesce small shuffle partitions for you and helps with the generic case, but it works on shuffle partitions rather than on output partition values, so it does not remove the need to align the repartition with the write.

Compaction, and doing it without breaking readers

Existing data still has to be dealt with, and rewriting a directory in place is where teams cause an incident while fixing one. A plain read-then-overwrite of a Parquet directory has a window in which the old files are gone and the new ones are not yet listed, and any reader that started before the rewrite holds file paths that no longer exist.

This is the concrete argument for a transactional table format rather than a folder convention. Because readers resolve files through a committed snapshot rather than by listing a directory, a compaction job can write new files, commit an atomic metadata change that swaps which files the table consists of, and leave the old files for a retention window. Readers in flight finish against the snapshot they started on, and no one observes a half-compacted table. If you are genuinely on bare Parquet, the safe approximation is to compact into a new location and swap the pointer that consumers resolve, rather than mutating the path they are reading.

The root cause is usually the partition column

All of the above treats the symptom. The design error is normally that the output was partitioned on a column with far too many distinct values, chosen because queries filter on it. Partitioning is a coarse pruning mechanism and it earns its keep only when each partition holds a substantial amount of data. Partitioning by date is usually right; adding country, then customer segment, then hour, multiplies the directory count and divides the data until every leaf is smaller than a single row group.

For the columns that need selectivity below that granularity, the right tool is ordering within the file rather than another directory level: sort the data on the filtered column so that row-group statistics become selective, which gives you pruning without multiplying files. The interview signal is recognising that partitioning and sorting solve the same problem at different granularities, and that reaching for another partition level is what created the file explosion in the first place.

The file count is task count multiplied by partition values per task, so fix it by aligning the shuffle with the write layout rather than by collapsing parallelism, and question whether the partition column should have that many distinct values at all.