Ideal file size

The ideal file size to target in a data lake is roughly 128 MB to 1 GB for compressed Parquet files, with 256 MB to 512 MB representing the sweet spot for modern cloud object storage. Files smaller than 128 MB suffer from excessive HTTP request overhead, metadata bloat, and poor columnar compression ratios. Conversely, files larger than 1 GB reduce query parallelism, create memory pressure during reader decompression, and cause severe write amplification during row-level updates.

Balancing I/O overhead with parallel execution

Understanding why this size window works best requires examining the mechanics of distributed query execution:

• Why small files hurt: Object stores like AWS S3 charge per GET request and exhibit latency per request. Opening ten thousand 5 MB files incurs ten thousand HTTP handshakes, footer downloads, and decompression allocations. Compression algorithms also require large blocks of similar data to achieve high ratios, which small files cannot provide.
• Why oversized files hurt: If a 100 GB table consists of only two 50 GB files, a 64-core compute cluster can allocate work across only two executor cores unless the files support clean multi-threaded splitting. In addition, if a single row needs updating in a copy-on-write table, the engine must rewrite all 50 GB to modify one record.

Impact on table format metadata

File sizing directly impacts table format metadata layers:

• Having millions of small files bloats Iceberg manifest files and Delta transaction log state, slowing down query planning.
• Having healthy 256 MB to 512 MB files keeps metadata compact and ensures query planning finishes in under a second.

Engine compaction configs

Table formats manage file sizing through explicit configuration properties:

• In Delta Lake, you can configure delta.targetFileSize to 256 MB or enable auto-compaction and optimized writes.
• In Apache Iceberg, you can configure write.target-file-size-bytes to 512 MB to guide writers during file closure.
• In streaming pipelines, decouple ingestion from optimization by letting micro-batches append small files and running scheduled compaction jobs (like OPTIMIZE in Delta or rewrite_data_files in Iceberg) to merge them into healthy targets.