DE//AGENT
Explore
Runbook community intermediate severity: medium

Small files explosion

A table has fragmented into thousands of tiny files and query planning now dominates runtime.

At a glance

When Planning or listing time dominates query time on one table; Metadata operations (OPTIMIZE/REPAIR) have become very slow
Where Lakehouse
Why Fragmentation turns every read into a metadata crawl; compaction restores predictable latency without touching consumers.

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Symptoms

  1. Table has tens of thousands of tiny files
  2. Query planning and listing dominate query time
  3. Metadata operations (OPTIMIZE/REPAIR) become very slow

Incident notebook

Impact

Every query on the table pays a growing planning tax; metadata operations stretch into hours and block maintenance windows.

Escalation

No page — schedule compaction; escalate only if planning time threatens a critical dashboard's freshness SLA.

Evidence to collect first

  • File count and average file size from the table's metadata (files/history)
  • Query plans splitting time spent in planning/listing vs scan
  • The write jobs feeding the table (stream cadence, micro-batch size)

Rollback

Not applicable — compaction is additive. Halt any concurrent OPTIMIZE that contends with the ingestion window.

Follow-up

  • Enable auto-compaction or schedule the maintenance job after ingestion
  • Reassess partition granularity (date vs hour/user)

Diagnosis

  • List file sizes; avg < 32 MB and a growing count is the signature.
  • For Iceberg/Delta, query the metadata tables (`files`, `history`).

Possible causes

  • Streaming or high-frequency writes without compaction
  • Over-partitioning (partition per hour, per user)
  • Many small appends from a micro-batch job

Commands

SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files;
OPTIMIZE db.tbl ZORDER BY (user_id);  -- Delta
CALL catalog.system.rewrite_data_files('db.tbl');  -- Iceberg

Fix

  • Compact to a 128-256 MB target; rewrite manifests if huge.
  • Reduce partition granularity if the layout over-partitions.
  • Schedule compaction after the ingestion window, never during it.

Prevention

  • Auto-compaction on the table (Delta) or a scheduled maintenance job.
  • Partition by date, not by hour/user, unless access patterns demand it.

Monitoring

  • file count, avg file size, planning time

Common gotchas

  • Compact after the ingestion window, never during it.
  • ZORDER rewrites the whole table — only worth it on a real query predicate.
  • Over-partitioning is often the root cause; coarsening the layout beats compacting harder.

Production considerations

  • Enable Delta auto-compaction or schedule Iceberg rewrite_data_files.
  • Target 128-256 MB files and alert on file-count growth.

Tradeoffs

Choose this when Costs & caveats
compaction fixes latency without consumer changes compaction costs compute and needs a maintenance window

Diagram

planning slow
   |
   |- avg file < 32 MB --> fragmentation --> compact to 128-256 MB
   |
   |- partition per hour/app --> over-partitioning --> coarsen the layout

Registry stats

Verification community · Sep 22, 2026
Completeness 75
Trust multiplier ×0.5
Composite 29.25 · adequate
Freshness 32 days since last source check
Difficulty intermediate
Severity medium
Version 1.0.0
License CC-BY-4.0
Security flags 3