How do I reduce the number of small output files?

Summary

• In Spark file writes, the file count follows the write plan: each write task produces its own files, and because a file sits in one partition directory, a task holding rows for many partition values writes a file for each one.
• Fix the plan first. repartition by the same columns you pass to partitionBy, or add the SQL REBALANCE hint, which the Spark docs describe as useful for avoiding too small or too big files when writing a query result to a table.
• After a shuffle, AQE coalesces partitions, but by default (spark.sql.adaptive.coalescePartitions.parallelismFirst=true) it ignores the 64 MB advisory size to maximize parallelism. Setting it to false makes AQE aim for spark.sql.adaptive.advisoryPartitionSizeInBytes.
• Table formats help at write time: Delta Lake (3.1.0 or later) offers optimized writes and auto compaction, and Iceberg's default hash write distribution clusters rows by partition before writing.
• Compact what already exists with Delta Lake OPTIMIZE or Iceberg's rewrite_data_files procedure, and schedule it for streaming tables, since every micro-batch commits new files.

How do I reduce the number of small output files?

Reduce small output files by changing the write plan so fewer, fuller tasks write each partition, then compact whatever small files remain. In Spark file writes, each write task produces its own files, and a file can't span partition directories, so a task holding rows for many partition values writes many small files. Two changes cover most cases: repartition by your partitionBy columns before the write, and let Adaptive Query Execution (AQE) size the final shuffle, either through the REBALANCE hint or with parallelismFirst turned off. Use Delta Lake OPTIMIZE or Iceberg rewrite_data_files for tables that already have the problem.

Why Spark writes small files

Symptom	Likely cause	Fix
Many small files after a join or aggregation	Each post-shuffle task writes its own files; spark.sql.shuffle.partitions defaults to 200	Let AQE coalesce toward the advisory size, or add REBALANCE
Many files in every partition directory	Rows for each partition value are spread across many tasks	repartition by the partitionBy columns
Steady growth from a streaming query	Every micro-batch commits new files	Longer trigger intervals or scheduled availableNow runs, then compaction
Files stop at a fixed row count	spark.sql.files.maxRecordsPerFile is set (default 0 means no limit)	Raise it or set it back to 0

Fix the write plan in Spark

spark.conf.set("spark.sql.adaptive.coalescePartitions.parallelismFirst", "false")
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "256MB")

(df.repartition("event_date")        # all rows for a date land in one task
   .write.partitionBy("event_date")
   .mode("append")
   .parquet("/data/events"))

Because hash repartitioning by event_date puts every row for a date in the same task, each partition directory gets one file from this write. AQE then coalesces the shuffle partitions toward the advisory size. That size measures shuffle bytes, which are row-based, while Parquet output is columnar and compresses further, so the Iceberg docs advise setting it above your target file size. The trade-off: a very large date becomes one large task.
In SQL, the partitioning hints do the same job. COALESCE and REPARTITION match the DataFrame methods, and REBALANCE asks AQE to make every output partition a reasonable size, splitting skewed partitions so they don't get too big. The hint is ignored if AQE is disabled.

INSERT INTO events
SELECT /*+ REBALANCE(event_date) */ * FROM staged_events;

Option	When to use it	Trade-off
coalesce(n)	Cutting task count at the end of a plan without a shuffle	A drastic coalesce can push upstream work onto fewer nodes
repartition(n) or repartition(cols)	You need a set file count or one task per partition value	Adds a full shuffle; a hot value becomes one large task
REBALANCE hint	Writing a query result to a table	Requires AQE; best effort
parallelismFirst=false	Any write that follows a shuffle	Fewer, larger tasks also means less parallelism in that stage

Let the table format help

Delta Lake. From Delta Lake 3.1.0, optimized writes reduce the number of small files written to each partition by shuffling data before the write, and they're most effective for partitioned tables. Auto compaction runs synchronously after a successful write and combines small files within partitions, using 128 MB as the default target. Optimized writes are disabled by default. Enable either feature per table or per session (spark.databricks.delta.optimizeWrite.enabled, spark.databricks.delta.autoCompact.enabled), and optimized writes also per write with the optimizeWrite option. The Delta docs note that optimized writes remove the need to call coalesce(n) or repartition(n) just before writing.
Apache Iceberg. The default write.distribution-mode is hash, which shuffles rows by partition value before writing, and write.target-file-size-bytes (default 512 MB) is where Iceberg rolls over to a new file. Because Spark can't write a file larger than its task, the distribution mode and AQE sizing still decide the final count.

/* Delta Lake table */
ALTER TABLE events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact' = 'true'
);
OPTIMIZE events WHERE event_date >= '2026-10-01';

/* Iceberg table */
CALL catalog_name.system.rewrite_data_files('db.events');

How Databricks approaches small files

Databricks always applies auto compaction and optimized writes to MERGE, UPDATE and DELETE operations, and it tunes file sizes itself for Unity Catalog managed tables. If you use optimized writes, Databricks recommends against running coalesce(n) or repartition(n) just before a write. Where predictive optimization is enabled, it schedules OPTIMIZE, VACUUM and ANALYZE for managed tables in both formats, Delta Lake and Iceberg, so compaction stops being your job. With liquid clustering enabled, OPTIMIZE also reorganizes data by the clustering keys.

FAQs

Should I just call coalesce(1)?

Only for small outputs. The PySpark docs warn that a drastic coalesce can run your computation on fewer nodes than you'd like; repartition(1) adds a shuffle but keeps the upstream work parallel.

Does maxRecordsPerFile reduce file counts?

No. spark.sql.files.maxRecordsPerFile caps the records written to a single file, so it can only add files. Its default, 0, means no limit.

Is compaction safe while people query the table?

For Delta Lake, yes. Readers use snapshot isolation, so OPTIMIZE doesn't interrupt them, and it makes no data-related changes, so a read before and after returns the same results.