What you'll learn: How to run cost-controlled compaction for Delta Lake tables stored on S3 in October 2026. This update adds new metrics, tooling notes, and practical thresholds tuned for current cloud and engine capabilities. If you operate lakehouse tables on S3 and need predictable costs and improved query performance without overpaying for compute, read on.

Prerequisites / context

  • Environment: Delta Lake tables on S3 or S3-compatible object storage with consistent listing semantics. This guide applies to Delta Lake OSS and managed Delta services.
  • Compute: Spark 3.x+ (Databricks Runtime 12+ or equivalent). Delta Lake's REWRITE DATA and OPTIMIZE semantics are widely available; verify your version.
  • Access: SQL/Scala/Python to inspect Delta transaction logs, list files, and run compaction jobs.
  • Monitoring / cost data: cloud billing (tagged), S3 request metrics, and query latency traces. Also helpful in 2026: S3 Inventory reports and engine-level metadata caches.

Why update this now? Since August 2026 we’ve seen three trends that change the compaction trade-offs:

  • Managed lakehouse platforms and Delta OSS have matured built-in auto-compaction features. These reduce operational overhead but often need governance and budget controls.
  • Network and instance throughput improvements (10–100 Gbps links on many clusters) make larger file targets more attractive for throughput-heavy workloads—so target sizes and cost models need adjustment.
  • Spot / preemptible capacity is a practical lever for lowering compaction compute costs, but it requires robust retry and atomic commit strategies to avoid partial rewrites.

Why compaction on S3 still matters (and what the numbers tell)

Small files remain an operational penalty in two measurable ways:

  • S3 request economics and latency: Public S3 pricing (us-east-1, Oct 2026) remains in the same order of magnitude: Standard storage ≈ $0.023/GB‑month, PUT/COPY/POST/LIST ≈ $0.005 per 1,000, GET ≈ $0.0004 per 1,000. With millions of small files, request billing is only part of the cost; increased LIST/GET calls inflate driver/executor metadata time and contribute to higher query compute.
  • Query and planner overhead: Opening a file still costs CPU and driver coordination. Example: 10 TB of data split into 1 MB files = ~10 million files. Even with 5 ms overhead per file (conservative), that’s ~14 hours of CPU time just in file-open overhead across the cluster. The numbers tell a different story than marketing: throwing more cores at the problem without reducing metadata work is expensive and brittle.

Step 1 — Measure the problem, quantitatively (updated)

  1. Collect a baseline for 7–14 days:
    • Files per table and per partition. Use Delta transaction logs or S3 Inventory to get counts without repeatedly listing S3 buckets. Example: S3 Inventory can deliver a daily CSV of object metadata you can ingest to compute file counts cheaply.
    • File-size distribution percentiles (P10, P50, P90, P99). Track skew: a table with P50=256 MB but P90=1 MB indicates hot partitions that need different treatment.
    • S3 request counts by operation (GET, LIST) and billable cost by tag. Tag fast-growing tables so you can attribute request spikes to compaction or queries.
    • Query latency percentiles (p50, p95) for representative queries, plus CPU-hours consumed per query class.
  2. Define data-driven thresholds (2026 defaults):
    • Start considering compaction if average file size < 128 MB for the partition or table. With current cluster throughput, 128–512 MB is often the efficient range for full-table scans.
    • Trigger when small files are >30% of file count but < 2% of bytes (metadata churn signal). The small-files-as-percent-of-bytes threshold increased slightly from 1% to 2% because modern workloads tolerate more small metadata entries when they're infrequent.
    • Table-level action if file count > 250k for tables < 10 TB, or if full-table list latency increases >50% over baseline.

Step 2 — Choose a compaction strategy (revised)

Pick the pattern that matches ingestion velocity, SLAs, and operational tolerance for rewrite windows.

  1. Managed/auto-compaction (when available):
    1. Many managed Delta services now offer auto-compaction. Use them if you need minimal ops, but bind them to budgets and a verification step. Fine print reveals these services can overspend if not capped—treat auto-compaction as a convenience, not a replacement for controls.
  2. Periodic batch compaction (recommended for most):
    1. Nightly/weekly OPTIMIZE/REWRITE jobs that target 128–512 MB files. Use bigger targets (256–512 MB) for heavy full-table scan workloads; use 64–128 MB for high-concurrency interactive workloads.
    2. Prefer partition-scoped jobs to reduce lock contention.
  3. Streaming-triggered micro-compactions:
    1. Trigger when N small files are created (e.g., 100 files) or after M minutes of quiescence (e.g., 30 minutes). For low-latency analytics, micro-compactions reduce small-file buildup without full rewrites.
  4. On-write coalescing (write-side):
    1. Buffer at the producer to write larger files (e.g., batch Kafka micro-batches into 64–256 MB files). This is the most cost-efficient long-term approach but increases producer complexity and write latency.

Step 3 — Implement compaction jobs (concrete, modern example)

Use transient clusters, spot instances, and isolated partition scopes. Example workflow and SQL that works on Delta REWRITE DATA or OPTIMIZE-enabled runtimes:

  1. Identify candidate partitions: Use the Delta _files view or S3 Inventory to pick partitions where avg file < 128 MB.
-- Example: average file size per partition using Delta's _files table
SELECT partitionValues[0] AS dt,
       COUNT(*) AS file_count,
       SUM(size)/COUNT(*) AS avg_size_bytes
FROM delta.`s3://my-bucket/tables/events`._files
GROUP BY partitionValues[0]
HAVING avg_size_bytes < 128*1024*1024;
  1. Compact per partition with predictable resources:
    1. Launch a transient cluster with a fixed budget and prefer spot capacity for ~70–80% of workers. Expect a 10–30% preemption rate; design idempotent work and short-running tasks.
    2. Rewrite using REWRITE DATA (Delta) or INSERT OVERWRITE with a coalesce strategy. Example (generic SQL):
-- Delta REWRITE DATA (if supported) or an INSERT OVERWRITE strategy
ALTER TABLE delta.`s3://my-bucket/tables/events`
REWRITE DATA WHERE dt = '2026-09-30' WITH (
  target_file_size = 268435456  -- 256 MB
);

If REWRITE DATA isn't available, use an INSERT OVERWRITE into the same partition and atomic swap. Always run row-count and checksum comparisons before commit.

Step 4 — Control compute costs with an explicit cost model (updated)

Compaction trades compute for lower recurring costs and better query performance. Use a per-job estimate and a budget enforcement step in your orchestrator.

  1. Estimate compute hours: Calculate Data scanned (GB) ÷ effective throughput (GB/hr) = hours. Updated example using modern throughput:
  • Assume sustained throughput per job ≈ 2 TB/hr for a modest transient cluster (this is conservative for 25 Gbps clusters; measure your environment).
  • 10 TB scanned ÷ 2 TB/hr = 5 hours. If your transient cluster cost (spot-weighted) ≈ $0.80/hr, compaction compute ≈ $4.00. Replace with on-demand costs to estimate worst-case.
  1. Estimate S3 request & operational savings: S3 request cost reductions are often small in dollars but large operationally. Example: rewriting 10M 1 MB files into 50k files eliminates many file-open overheads. Request cost delta may be $10/month, while analyst-query compute reductions can be hundreds of dollars monthly.
  2. Set per-table weekly budgets: Example: $100/week per high-activity table. If an estimate exceeds this, split work by date range or reduce target file size to speed jobs and lower preemption risk.

Step 5 — Automate and schedule safely (2026 tooling notes)

  1. Orchestration with guarded steps: Use Airflow, Dagster, or cloud-native job orchestrators. Include:
    1. Metric check to list candidate partitions.
    2. Budget check: estimate cost and abort if it exceeds per-table budget.
    3. Acquire partition-level lock (lightweight) or use optimistic retry semantics for writers.
    4. Run compaction on transient cluster with timeouts, retries, and spot-awareness.
    5. Verification: row counts, checksums, and partition-level sampling before commit.
  2. Use manifests & metadata caches: Persist manifests (file lists) to avoid repeated LIST calls across runs. Many engines now support manifest files that speed planning and reduce S3 LIST pressure.
  3. Timebox and circuit-break: If a compaction exceeds its runtime or cost threshold, abort and split the partition into smaller ranges rather than letting it run uncontrolled.

Step 6 — Validate outcomes and measure ROI (updated example)

  1. After compaction, re-measure:
    • Average file size, file counts, and P90 file size
    • Query p95 latency and CPU-hours for representative queries
    • S3 request counts and monthly cost
    • Delta transaction log growth and vacuumable files
  2. ROI example (concrete):
  • Pre-compaction: 10 TB, 10M files (avg 1 MB). Representative query p95 = 120s, analysts run 600 queries/month at an average cluster cost of $2.00/query — monthly analyst compute = $1,200.
  • Post-compaction: 50k files (avg 200 MB), p95 = 40s, analyst compute monthly = $400. Monthly savings ≈ $800 on analyst compute; one-time compaction cost $300 (spot-weighted). Payback ≈ 11 days.

Note: the numbers tell a different story than S3 request savings alone. Most ROI comes from reduced downstream compute and developer time.

Common mistakes (updated)

  • Compacting everything indiscriminately: Rewriting cold historical data wastes compute. Start with hot partitions where queries and metadata overhead are concentrated.
  • No verification or audit trail: Always run row-count and checksum checks. Keep logs of bytes rewritten, files reduced, compute hours, and cost. This data lets you prove ROI and tune cadence.
  • Ignoring transaction-log growth: Frequent compactions create more transaction log entries. Coordinate compaction with VACUUM and log-retention policies. Don’t lower retention below governance requirements.
  • Over-reliance on spot-only clusters: Spot instances reduce cost but increase preemption risk. Design compaction tasks to be idempotent and use short task durations to limit wasted work.
  • Trusting auto-compaction without controls: Built-in auto-optimize features are useful, but the fine print reveals possible runaway costs. Enable budget guards and verification hooks.

Pro tips

  • Choose file sizes by query profile: For heavy full-table scans, target 256–512 MB per file. For high-concurrency interactive workloads, choose 64–128 MB to increase parallelism without file-open overhead.
  • Combine compaction with clustering/Z-order: For selective queries on high-cardinality fields (user_id, account_id), run compaction with clustering to reduce I/O and speed selective queries.
  • Use S3 Inventory & manifest files for planning: Inventory reduces repeated LIST calls and lets you compute file-size distributions offline cheaply.
  • Sample before full runs: For very large tables, sample 1–5% of partitions to estimate compute and success rate, then scale out.
  • Log metrics: Record bytes rewritten, files removed, runtime, spot preemption rate, and cost. Use these to tune cadence and target sizes.

When to consider alternatives

If ingestion is extremely high-velocity and rewriting is unacceptable, move effort upstream: implement write-side coalescing at producers or use a format with built-in log-compaction semantics (Apache Hudi, Iceberg rewrite strategies). These add upstream complexity but reduce downstream compaction windows.

FAQ

How often should I run compaction?

Start with weekly compaction for high-activity tables and monthly for moderate-activity. Use data-driven triggers: run when average file size < 128 MB or when small files >30% of file count but <2% of bytes. Measure p95 query latency and compute-hours; if those improve materially after compaction, shorten cadence. The numbers—not intuition—should drive the schedule.

What target file size should I pick in 2026?

Use 128–512 MB as your starting range. For modern cluster network speeds and large full-table scans, 256–512 MB yields better sequential throughput. For high-concurrency interactive workloads, favor 64–128 MB to increase parallelism. Test with representative queries and measure p95 latency and CPU-hours to finalize.

Will compaction break time travel or VACUUM guarantees?

No—compaction rewrites data files and appends new transaction log entries; it does not instantly delete historical versions. VACUUM still controls physical deletions. Don’t reduce vacuum retention below governance needs; instead coordinate compaction and vacuum in your orchestrator.

Can I safely use spot/preemptible instances for compaction?

Yes—if you design jobs for preemption. Expect 10–30% preemption depending on region and time. Use short tasks, idempotent writes, checkpoints, and an atomic commit pattern. If your job is long-running and cannot easily restart, use a mixed pool (spot + reserved) or limit to on-demand instances.

How do I prevent auto-compaction from overspending?

Implement budget checks in your orchestrator: estimate job cost (bytes to rewrite ÷ estimated throughput × cluster $/hr) and compare to per-table budget. If the estimate exceeds budget, split work or postpone. Also add verification steps and a per-run hard cap that aborts the job if it hits time or cost limits.

Closing: measure, automate, and iterate

Compaction remains a repeatable lever you can use to control both cost and performance. In 2026 the tooling has improved—auto-compaction, manifests, S3 Inventory, and faster networks—so there's less manual toil. But the core discipline is unchanged: measure first, set thresholds, guard budgets, and validate. The numbers tell the real story—use them to make compaction predictable, not ad hoc.