By Sarah Chen — Senior Financial Analyst

Introduction

What you'll learn: a concrete, repeatable process to right‑size Parquet files, partitioning, and compaction for S3 data lakes at 10s of terabytes to multiple petabytes. This update (Oct 2026) adds current trends, fresh cost math, and operational patterns that teams I’ve worked with used successfully in 2024–2026.

Who it's for: data engineers and analytics engineers responsible for query performance, cloud spend, and operational stability of Iceberg/Delta/Hudi or Hive-style lakes.

Why this matters: poorly laid out lakes create both hard costs (higher scan and compute bills) and soft costs (pipeline fragility and toil). The numbers tell a different story: increasing average file size from 10 MB to ~256 MB cuts file counts by ~25× on a 100 TB table, materially lowering metadata and per-query overhead.

Prerequisites / Context

  • Access to a representative production dataset snapshot and query history covering at least 30 days (query engine logs, storage inventory).
  • Familiarity with your table format (Apache Iceberg, Delta Lake, Apache Hudi, or Hive), compute (Spark/Presto/Trino/Flink), and query engine (Athena, Trino, Snowflake).
  • Ability to run ad-hoc sampling and compaction jobs during maintenance windows; permissions to update table metadata or run native rewrite APIs.
  • Monitoring access: S3 request metrics, query scanned-bytes, and manifest/metadata latencies.

Step 1 — Measure the Current State (Do not guess)

Why: Baselines are required for ROI, target-setting, and risk control.

  1. Collect these metrics across 30–90 days:
    • Total table size (bytes), file count, average file size (MB), and row-group size distribution.
    • Partition cardinality and files-per-partition distribution (median, P90, P99).
    • Top 20 queries by scanned bytes and by latency (P50/P90/P99).
    • Metadata operation latencies: listing, manifest reads, manifest count per table snapshot, and failed metadata ops.
    • S3 request counts (LIST/GET/PUT) and associated costs if available.
  2. How to measure (examples):
    • Use S3 Inventory or Storage Lens to get per-prefix object counts and total bytes; combine with table format metadata for authoritative file lists.
    • Extract bytes_scanned per query from Athena/Trino logs or query engine metrics and compute percentiles.
  3. Concrete example:
    • 100 TB table = 100,000 GB. If avg file = 10 MB (0.01 GB) → file_count ≈ 10,000,000. Increase avg file size to 256 MB (0.256 GB) → file_count ≈ 390,625 (~25.6× reduction).
    • Fewer files reduces LIST and manifest reads, lowers connector/driver overhead, and generally improves query throughput at fixed compute.

Step 2 — Define Target Layouts and Performance Goals

Why: Explicit targets prevent oscillation between over- and under-compaction.

  1. Set measurable performance and cost goals:
    • Latency goals: e.g., P95 for common dashboards 10s; ad-hoc BI queries P95 60s.
    • Cost goals: reduce bytes scanned per typical dashboard by X% or cut monthly serverless query spend by $Y.
  2. Set file size targets (updated for 2026 hardware and engine behavior):
    • Analytic workloads: aim for 128–512 MB compressed per Parquet file. I recommend 256 MB as a practical middle ground for balanced parallelism and IO efficiency on modern cluster and cloud hardware.
    • Cold archival partitions: 512 MB–1 GB to minimize metadata costs; reduce small-file churn where access is rare.
    • Row-group size: 16–64 MB. Too-large row groups blunt predicate pushdown; too-small increases per-row-group overhead.
  3. Partitioning policy:
    • Continue using low-cardinality directory partitions (date/day, region). Avoid user_id or other high-cardinality keys as top-level directories.
    • For high-cardinality filtering attributes prefer data skipping (min/max), Z-order/hash-clustering, or hidden partitioning features available in Iceberg/Delta rather than directory partitions.

Step 3 — Choose a Compaction Pattern

Why: Pattern determines operational cost, complexity, and data freshness tradeoffs.

  1. Micro-compaction: merge small files within partitions frequently (hourly). Best for transactional or near-real-time streams. Pros: freshness; Cons: higher cumulative compute and more complex orchestration.
  2. Batch/offline compaction: nightly or weekly rewrite for cold/warm partitions. Best for append-heavy or mostly-read workloads.
  3. Hybrid/tiered (recommended for large lakes):
    1. Hot (0–7 days): micro-compaction or built-in table-format auto-optimize to maintain low latency.
    2. Warm (7–90 days): daily merges to hit target file sizes (128–256 MB).
    3. Cold (>90 days): bulk rewrite to larger files (512 MB–1 GB) focused on compression ratio over parallelism.
  4. Note on 2026 trend: managed lakehouse services and open-source table formats have improved auto-rewrite and clustering features. Use them where they provide transactional guarantees to avoid metadata drift, but validate at scale.

Step 4 — Select Tools and Commands (Examples & 2026 updates)

Why: Prefer table-format-native primitives when possible; they preserve transactional safety and reduce manual bookkeeping.

  1. Delta Lake:
    • OPTIMIZE remains a primary tool where supported. Use with Z-Order for common filter patterns—e.g., OPTIMIZE … WHERE date='2026-09-01'.
    • Databricks and managed runtimes increasingly expose configuration knobs to control target file size and clustering; use them before building custom rewrite jobs.
  2. Apache Iceberg:
    • RewriteDataFiles and manifest management APIs are the safe way to compact files. Iceberg's manifest segmentation and metadata pruning scales better at very large file counts.
  3. Apache Hudi:
    • Inline/async compaction and clustering features improved in recent releases (2024–2026). Use clustering for targeted rewrite by predicates.
  4. Generic Spark example (explicitly control Parquet sizes): 
    spark.read.parquet("s3://bucket/table/date=2026-09-01")
  .repartitionByRange(256, col("some_clustering_key"))
  .write
  .option("parquet.block.size", 268435456)    // 256 MB
  .option("parquet.row.group.size", 33554432) // 32 MB
  .mode("overwrite")
  .parquet("s3://bucket/compacted/date=2026-09-01")

    Why: Use the table format's atomic commit if available. If not, write to a temp prefix and perform an atomic swap using the table metadata API.

Step 5 — Implement an Incremental Compaction Pipeline

Why: One-off rewrites don't prevent future small-file churn; sustainable automation is required.

  1. Design components:
    • Detector job: nightly scan that flags partitions with small-file signals (e.g., >100 files, avg_file_size < 64 MB, or high P95 list latencies).
    • Compactor job: idempotent rewrite of flagged partitions to target size. Write to temp location and use table-format atomic commit or a two-phase swap.
    • Orchestrator and throttling: impose concurrency limits and backpressure to avoid S3 request storms and cluster contention.
  2. Operational details:
    • Use table-format locking or a lightweight external lease store (DynamoDB, etc.) for per-partition locks.
    • Maintain an audit/metrics table: compaction runs, bytes rewritten, CPU-hours consumed, and pre/post avg file size.
    • Retry safely: compaction must be idempotent and crash-safe to avoid partial rewriting.
  3. 2026 note: Many managed lakehouses now offer >80% of these capabilities natively—evaluate before building complex homegrown pipelines.

Step 6 — Monitor, Validate, and Iterate

Why: Compaction is an investment—measure the returns and tune.

  1. Key monitoring signals:
    • File count, files-per-partition, avg file size (per tier).
    • Bytes scanned per query (median and tail), query latency P50/P90/P99, and per-query compute cost.
    • Manifest read times and metadata operation error rates.
    • S3 request counts and cost trends (LIST/GET/PUT).
  2. A/B validation:
    • Keep a control set of partitions and compare metrics against compacted partitions over 2–4 weeks. Use identical query workloads to isolate the effect of file layout changes.
  3. Estimate payback (example with conservative 2026 baseline assumptions):
    • Assumptions: serverless query price ≈ $5 per TB scanned (≈ $0.005 per GB) — use your provider's current price. Example dashboard scans 500 GB/run.
    • If compaction reduces scanned bytes by 60% → saves 300 GB/run → $1.50 saved per run. At 10,000 runs/month → $15,000/month saved.
    • Compaction cost: modern rewrite jobs commonly cost 0.5–2 compute-hours per TB depending on cluster size and IO efficiency. If your cluster vCPU-hour cost is $0.05 and compaction takes 1 compute-hour/TB, rewriting 100 TB costs ≈ $5,000. Compare that to monthly savings and expected retention of benefits.
    • Always document assumptions (scan reduction %, compute cost/hr, frequency) and recalculate quarterly because usage patterns change.

Common Mistakes

  • Compacting everything at once—this can create S3 request storms, high transient costs, and long-running jobs with diminishing returns. Stage compaction by tier.
  • Using directory partitioning for high-cardinality fields. This creates many tiny directories and metadata overhead; prefer data skipping or clustering instead.
  • Ignoring row-group sizing. Large compressed files with tiny row groups defeat column pruning and increase I/O.
  • Ignoring S3 request costs. Shrinking storage bytes can be offset by rising LIST/GET request charges if you compact too frequently.
  • DIY rewrites that bypass transactional APIs. That leads to partial writes and query failures—use table-format native rewrites or atomic swap patterns.

Pro Tips

  • Prefer Parquet writer versions and compression codecs with wide engine support. Parquet v2 features (wider dictionary and encoding options) are increasingly supported; test them before enabling cluster-wide.
  • Zstandard (ZSTD) at compression levels 3–6 usually balances compression ratio and CPU cost well—test level-by-level on your data.
  • Use engine features such as data skipping indexes, bloom filters, and statistics. In many workloads, data skipping yields larger scan reductions than aggressive compaction alone.
  • In ingestion pipelines, increase micro-batch size or buffer duration to produce fewer, larger files upstream—this prevents most small-file problems without extra downstream compaction cost.
  • Turn file-size targets into SLAs: e.g., "Warm partitions should hit ≥128 MB avg file size within 24 hours of ingestion." Making this a measurable guardrail reduces operational back-and-forth.
  • Benchmark compaction cost on a representative 1–2% subset before wide rollout. Track compute-hours per TB and bytes_rewritten/second to estimate full-lake cost accurately.

FAQ

How large should Parquet files be in 2026?

For analytic workloads on S3, aim for 128–512 MB compressed per file. I still recommend ~256 MB for warm data as a starting point—cold, infrequently accessed partitions can be larger (512 MB–1 GB). Keep row-group size 16–64 MB to preserve predicate pushdown and columnar read efficiency.

Won’t bigger files reduce parallelism?

Not necessarily. Modern query engines split large Parquet files by row-group and byte ranges. The practical tradeoff is between task overhead and IO parallelism; 128–256 MB is a good middle ground for interactive workloads on typical clusters in 2026.

Should I rely on managed 'auto-optimize' features?

Managed auto-optimize features reduce operational toil and are increasingly robust, but don't blindly enable them at scale. Validate on representative tables, confirm they respect your SLAs for freshness and metadata, and monitor the S3 request and compute patterns they generate.

How do I avoid S3 cost spikes during compaction?

Throttle compaction concurrency, schedule heavy rewrites during low-usage windows, use multipart uploads, and leverage the table format's manifest/metadata APIs to limit list operations. Monitor S3 LIST/GET/PUT counts as part of compaction ROI calculations—request costs can negate storage or query savings if ignored.

What success metrics should I report to stakeholders?

Report pre/post: avg file size, total file count, bytes scanned per representative query (median and tail), query P95 latency, compaction CPU-hours per TB, and S3 request counts. Translate those into dollar savings for query bills and compaction costs to show net ROI.

References & context: recommendations above are derived from vendor documentation, improvements to table formats and managed lakehouse services through 2024–2026, and benchmarks run across multiple production environments. Always validate numbers against your own logs and current cloud prices.

Final note: compaction remains an investment with measurable returns when you start with measurement, set conservative targets (e.g., compact the worst 10% first), validate with A/B testing, and automate safely. In 2026 the ecosystem gives you more native tools—use them, but verify at scale. The numbers tell a different story: small, repeatable wins compound into significant cost and latency improvements.