A data engineer runs table-compaction (OPTIMIZE) on a large digitised-artifact metadata table every night, and the underlying storage size keeps growing even though the number of active files stays roughly the same after each run. What is the most likely reason, and what should the engineer do next?
Select an answer to reveal the explanation.
Short Explanation
Compacting files is like photocopying a messy stack of pages into one clean stack — the old messy pages don't disappear just because a tidy new copy exists. OPTIMIZE rewrites small files into bigger ones, but the original small files stick around (so older table versions can still be read) until something explicitly clears them out. That something is VACUUM: it deletes files no longer referenced by the current table state once they age past the retention window.
Full Explanation
Delta tables are versioned: every write, including a compaction rewrite, adds a new version to the transaction log while leaving prior files in place so older versions of the table remain queryable for time travel. Running OPTIMIZE repeatedly without ever running VACUUM means the newly-compacted files accumulate on top of storage that was never freed, which explains growing storage size alongside a stable active-file count. VACUUM is the maintenance operation that permanently deletes files no longer referenced by the current table version, once they're older than the configured retention threshold. Re-running OPTIMIZE with a bigger target size would only compact further; it does nothing to remove already-orphaned small files still sitting in storage. V-Order affects how files are encoded for read performance, not whether old files get deleted, so re-enabling it wouldn't reclaim any space. And treating this as a capacity-limit support issue skips past the actual cause — the table is generating unreferenced files it never cleans up, which a quota increase would only mask temporarily. A caveat: lowering the VACUUM retention window too aggressively can break time travel or concurrent long-running reads against older versions, so it should stay at a safe minimum rather than being set to zero. A concrete check: compare the table's total files on disk against the count of files referenced by its latest transaction log entry before and after a VACUUM run.