Why do my Delta/Iceberg tables get slower every week even though the query hasn't changed?

A table fed every five minutes by a streaming job accumulates thousands of small files a month - and every query against it pays per-file overhead no matter how little data is in each one.

Where the small files actually come from

Streaming and micro-batch writers commit a new file (often several, one per partition touched) on every trigger. At a 5-minute trigger, that's hundreds of files a day per partition - each one small, each one adding its own open and metadata-read cost to every future scan, regardless of how little data it actually holds.

What OPTIMIZE/compaction does and doesn't fix

Compaction rewrites many small files into fewer, larger ones, which is what actually fixes scan time. What it doesn't do: reclaim disk space - both the old and new files have to coexist, since older table snapshots (used for time-travel) still reference the originals - and it doesn't fix a partitioning scheme that was wrong to begin with.

VACUUM is a separate, riskier step

VACUUM is what actually deletes files no longer referenced by the table's current snapshot, past a retention window - that's the step that reclaims storage cost. A retention window set too short can delete files a concurrent reader or an in-flight time-travel query still needs, which is why it's a separate, deliberate command and not something compaction runs automatically.