Why it hurts
Every file costs a listing call, a footer read and a task. 50,000 files of 100 KB read far slower than 40 files of 128 MB holding the same data. Symptoms customers report: "the query takes minutes to start", or a Spark job with tens of thousands of tiny tasks.
1. Measure it
-- Delta
DESCRIBE DETAIL sales.orders; -- numFiles and sizeInBytes → average file size
-- Iceberg (Spark)
SELECT COUNT(*) AS files, AVG(file_size_in_bytes)/1024/1024 AS avg_mb
FROM catalog.sales.orders.files;
# Plain Parquet folder on S3
aws s3 ls s3://lake/sales/orders/ --recursive --summarize | tail -2
An average under about 16 MB on a large table is a problem. Aim for 128 MB–1 GB.
2. Compact
Delta Lake:
OPTIMIZE sales.orders;
OPTIMIZE sales.orders WHERE order_date >= '2026-10-01'; -- recent partitions only
OPTIMIZE sales.orders ZORDER BY (customer_id); -- also co-locate a filter column
Then remove old files after the retention period:
VACUUM sales.orders RETAIN 168 HOURS;
Iceberg:
CALL catalog.system.rewrite_data_files(table => 'sales.orders');
CALL catalog.system.expire_snapshots(table => 'sales.orders', older_than => TIMESTAMP '2026-10-15 00:00:00');
Plain Parquet: read the partition, repartition(n) to the target size, write to a new path, then swap.
3. Stop creating them
| Cause | Fix |
|---|---|
| Streaming micro-batches every few seconds | A longer trigger interval, or auto compaction / optimized writes (delta.autoOptimize.optimizeWrite, autoCompact) |
| Partitioning by a high-cardinality column (customer_id, hour) | Partition by date only, or use liquid clustering / Z-order instead |
spark.sql.shuffle.partitions = 200 on a tiny dataset | Enable AQE coalescing (default in Spark 3.2+), or coalesce() before writing |
| Many small appends from ingestion tools | Land in a staging table, compact into the main table on a schedule |
Warning
Running
VACUUMwith a short retention breaks time travel and can fail readers still using old snapshots. Keep the default 7 days unless you know nothing reads older versions.
Tip
Schedule a nightly
OPTIMIZEon the 20 largest tables and track the average file size as a metric. It's one of the cheapest performance wins in a lakehouse.