Why do I have thousands of tiny output files?

Summary

• The number of files a Spark write produces follows the partition count of the DataFrame being written. A small dataset spread over many partitions lands as many small files, which is why the Spark docs describe coalesce, repartition and the SQL partitioning hints as ways to control the number of output files.
• After a join or aggregation, the partition count comes from spark.sql.shuffle.partitions, which defaults to 200. Adaptive query execution (AQE) coalesces small shuffle partitions, but by default it favors parallelism over its 64 MB advisory partition size.
• partitionBy multiplies files across directories, one per partition value. The Spark docs say it "has limited applicability to columns with high cardinality".
• Fix new writes with coalesce(n) (no shuffle), repartition or the REBALANCE hint. Fix existing tables by compacting them with Delta Lake OPTIMIZE or the Apache Iceberg rewrite_data_files procedure.

Why do I have thousands of tiny output files?

Spark writes your data in as many pieces as the final DataFrame has partitions, so file count tracks partition count, not data volume. That's why the Spark docs present coalesce, repartition and the SQL partitioning hints as the way to "control the number of output files". Join or aggregate a small dataset and the result can carry up to spark.sql.shuffle.partitions partitions (default 200). Add partitionBy and those files spread across every partition directory. Size partitions before the write, and compact what's already on disk.

What causes small files in Spark?

Symptom	Likely cause	Fix
Many files after a join or aggregation	The result keeps the shuffle partition count, spark.sql.shuffle.partitions (default 200)	Lower it for the session, or coalesce(n) before the write
Tiny files even with AQE on	spark.sql.adaptive.coalescePartitions.parallelismFirst (default true) ignores the 64 MB advisory size and only respects the 1 MB minimum partition size	Set it to false, or use the REBALANCE hint
Small files in every directory of a partitioned table	partitionBy on a high-cardinality column	Partition on a coarser column, or REBALANCE(col) before the write
Small files pile up in a table fed by a streaming query	The Iceberg docs note that "streaming queries may produce small data files that should be compacted"	Compact on a schedule (see below)

How do I write fewer, larger files?

coalesce(n) is the cheapest option. It's a narrow dependency: going from 1000 partitions to 100 happens without a shuffle, with each new partition claiming 10 of the current ones. AQE, on by default since Spark 3.2.0, also merges small shuffle partitions at runtime; turning off parallelismFirst makes it coalesce toward spark.sql.adaptive.advisoryPartitionSizeInBytes instead of the cluster's default parallelism.

# Fewer files from a small result: coalesce avoids a shuffle
daily.coalesce(16).write.mode("overwrite").format("parquet").save("/data/daily")

# Session level: fewer shuffle partitions for joins and aggregations
spark.conf.set("spark.sql.shuffle.partitions", "64")

# Let AQE coalesce toward the advisory size instead of maximum parallelism
spark.conf.set("spark.sql.adaptive.coalescePartitions.parallelismFirst", "false")

In SQL, partitioning hints do the same job. REBALANCE asks Spark to make every output partition "a reasonable size (not too small and not too big)", optionally by columns, and the docs call it useful "when you need to write the result of this query to a table, to avoid too small/big files". It's ignored if AQE is off.

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

INSERT INTO daily_summary
SELECT /*+ COALESCE(8) */ * FROM summary_staging;

How do I fix small files that are already written?

Compact them with the table format's own command. Delta Lake OPTIMIZE (1.2.0 and above) coalesces small files into larger ones and "makes no data related changes to the table", so reads return the same results before and after. Apache Iceberg's rewrite_data_files procedure combines small files "to reduce metadata overhead and runtime file open cost", using the binpack strategy by default.

-- Delta Lake: whole table, or only matching partitions
OPTIMIZE delta.`/data/events`;
OPTIMIZE events WHERE date >= '2026-01-01';

-- Apache Iceberg (catalog ice)
CALL ice.system.rewrite_data_files('db.events');

Delta Lake 3.1.0 and above can also reduce the problem at write time. Optimized writes (delta.autoOptimize.optimizeWrite, off by default) shuffle data before writing so fewer files land in each partition, at the cost of some write latency. Auto compaction (delta.autoOptimize.autoCompact) compacts small files synchronously after a successful write.

How Databricks approaches small files

On Databricks, auto compaction and optimized writes are always enabled for MERGE, UPDATE and DELETE. In Databricks Runtime 13.3 LTS and above, all tables registered in Unity Catalog have optimized writes enabled for CTAS statements and INSERT operations on partitioned tables. For Unity Catalog managed tables (Delta Lake and Iceberg), predictive optimization automatically runs OPTIMIZE, VACUUM and ANALYZE, so you don't schedule compaction yourself. In Databricks Runtime 13.3 LTS and above, Databricks recommends clustering for table layout.

FAQs

Does coalesce shuffle data?

No. coalesce creates a narrow dependency, so it merges existing partitions without a shuffle. repartition adds a shuffle step and returns a hash-partitioned DataFrame.

Should I coalesce to a single file?

Only for small outputs. The PySpark docs warn that a drastic coalesce to one partition can run your computation on fewer nodes than you like. Calling repartition() instead adds a shuffle but keeps upstream partitions running in parallel.

Can I set a target file size?

Core Spark offers related settings: spark.sql.files.maxRecordsPerFile caps records per file (default 0, no limit), and AQE sizes shuffle partitions with spark.sql.adaptive.advisoryPartitionSizeInBytes. Table formats add byte targets: Iceberg's write.target-file-size-bytes defaults to 512 MB, and on Databricks you can set the delta.targetFileSize table property (with Unity Catalog managed tables on SQL warehouses or Databricks Runtime 11.3 LTS and above, only OPTIMIZE respects it).

Why are small files slow to read?

Each file carries an open cost and metadata. Spark estimates that cost with spark.sql.files.openCostInBytes (default 4 MB) when packing files into read partitions of up to spark.sql.files.maxPartitionBytes (default 128 MB). The Iceberg docs say small data files cause "an unnecessary amount of metadata and less efficient queries from file open costs".