How would you set OPTIMIZE and VACUUM policy across hundreds of Delta Lake tables?

## Why a single global schedule fails The tempting answer — "nightly OPTIMIZE and nightly VACUUM on everything" — fails on three counts. It spends compute rewriting tables nobody queries. It shortens time travel on tables whose consumers assumed a longer window. And it puts maintenance jobs in commit conflict with pipelines that write at the same hour. A policy has to be *derived* from how each table is written, read and depended upon. ## Segment the estate A workable classification uses two axes. **Write shape.** Streaming or micro-batch sinks produce many small files continuously. Merge-driven tables (CDC targets, slowly changing dimensions) produce moderate churn and file rewrites. Batch tables overwritten daily produce whatever file size the writer chose, once. **Read value.** A table behind an executive dashboard queried hundreds of times a day repays layout work quickly. A regulatory archive read twice a year does not — for it, storage cost and retention matter and compaction does not. The policy then almost writes itself: streaming plus high-read gets aggressive layout maintenance; batch plus low-read gets retention management only. ## Compaction policy - **Prevent rather than repair.** Optimized writes (`delta.autoOptimize.optimizeWrite`) and auto-compaction (`delta.autoOptimize.autoCompact`) keep a streaming sink's file count bounded inline, which is cheaper than letting a day's worth of fragments accumulate and rewriting them at midnight. - **Scope every scheduled run.** `OPTIMIZE tbl WHERE partition >= ...` restricted to partitions written since the last run. Whole-table optimization of a historical table is a recurring bill for work already done. - **Prefer incremental clustering where available.** `ZORDER BY` re-clusters a whole partition once it changes, so on continuously written tables its cost is unrelated to the increment. Liquid clustering (`CLUSTER BY`, applied by plain `OPTIMIZE`) is incremental and lets keys change as query patterns drift — a better fit for tables under permanent maintenance, at the price of a one-way protocol feature. - **Justify each schedule with numbers.** `DESCRIBE HISTORY` gives `numRemovedFiles`, `numAddedFiles` and `totalFilesSkipped` per run. A job that mostly skips is running too often; a job whose partitions still show thousands of files is running too rarely or is fighting over-partitioning that compaction cannot fix. ## Retention policy Treat the time-travel window as a **published contract per tier** — for example 7 days for staging, 30 days for curated tables consumers may need to roll back or audit. Then set both clocks: ```sql ALTER TABLE curated.orders SET TBLPROPERTIES ( delta.deletedFileRetentionDuration = 'interval 30 days', delta.logRetentionDuration = 'interval 30 days' ); ``` The usable window is the **shorter** of the two, and the cost is storage: every file rewritten by OPTIMIZE, MERGE or an overwrite is retained for the entire window. On a heavily compacted table that can multiply the footprint several times over. This is the real capacity-planning conversation — recoverability versus object-storage bill — and it should be decided per tier, not per team's mood. Operational rules that go with it: run `VACUUM` on a slower cadence than OPTIMIZE (weekly is common), never below the 168-hour safety floor, and use `DRY RUN` the first time on any table. The floor exists because VACUUM also deletes files under the table directory the log does not track — including a concurrent writer's in-flight output. And remember raising retention is not retroactive: it widens the future window and resurrects nothing. ## Concurrency and isolation Maintenance is a writer like any other, subject to the same optimistic-concurrency check: if OPTIMIZE and a MERGE rewrite overlapping files, the later commit fails its conflict validation and must retry. Practical mitigations are scheduling maintenance in windows that avoid the heavy write path, scoping OPTIMIZE by partition so its file set does not overlap the live partitions a merge is touching, and building retry into the maintenance job rather than paging someone when a commit loses a race. Give maintenance its own compute so a large rewrite does not starve the pipelines that produce data. ## Governance and observability At hundreds of tables, per-table hand-tuning does not scale. What does: - **Defaults by tier**, applied at table creation from a template, so a new table inherits sane properties without anyone deciding. - **A maintenance inventory** — file counts, median file size, days since last OPTIMIZE, days since last VACUUM, current retention — driven off table history and file listings, so exceptions surface instead of hiding. - **Exception review**, not blanket tuning: chase the twenty worst tables, leave the rest on defaults. - **Cost attribution**, since both compaction compute and retained storage land on somebody's budget, and a policy nobody pays for drifts. ## What a strong answer sounds like Name the two axes, refuse the global schedule, tie retention to a consumer-facing contract rather than a default, show awareness that compaction is amortized write amplification justified by measured read savings, and mention the concurrency and cost dimensions. The weak answer is a cron expression.