A data engineer sees that a Delta table has accumulated many small files from frequent streaming writes, slowing queries that filter on a high-cardinality event_id column. They also want to reclaim storage from data files no longer referenced. Which maintenance approach is correct?
Answer & explanation
Correct answer: D
WHY D: OPTIMIZE compacts small files and ZORDER BY co-locates data on event_id to speed filtered reads, and VACUUM then removes files older than the retention period that the transaction log no longer references.
WHY NOT A: VACUUM only deletes old files; it does not compact and does not accept a ZORDER clause.
WHY NOT B: OPTIMIZE without ZORDER does not cluster on the columns, and time travel does not purge files; VACUUM does.
WHY NOT C: ZORDER is not a standalone command (it is an OPTIMIZE clause), and compaction does not delete the old files when it rewrites them, so VACUUM is still needed.
WHY NOT E: VACUUM RETAIN 0 HOURS breaks time travel and can corrupt in-flight readers; it is not a recommended default.