Physically Purging Deleted Data: VACUUM, Retention and REORG PURGE

1.Why a DELETE is not yet an erasure

A Delta DELETE writes a new table version. The current version no longer contains the user, but the data files behind earlier versions are still in cloud storage, and anyone who can query the table can travel back to them.

SELECT * FROM people10m VERSION AS OF 123;

Querying an old version needs both its log entries and its data files. Two separate settings control how long each of those is kept, and confusing them is the most common mistake in this topic.

For erasure, the data files are what matter. Even after log entries expire, the files holding the deleted records stay in cloud storage until something removes them. That is why datasets need regular maintenance to remove previous versions of the data.

Sources12

2.VACUUM and predictive optimization on Unity Catalog managed tables

VACUUM removes data files that the table no longer references and that are older than the retention threshold. The files it removes may contain modified or deleted records, so running it regularly is both a cost measure and a compliance measure. The clock starts when a file was logically removed from the transaction log, not at the file's modification time in storage. After VACUUM, you can no longer time travel to versions older than the retention period.

The Unity Catalog route is predictive optimization. It runs VACUUM automatically on Unity Catalog managed tables, and Databricks recommends enabling it on all of them. Lakeflow pipelines also maintain their own streaming tables and materialized views. If predictive optimization isn't enabled, they run maintenance within 24 hours of an update. Outside those two cases, you run VACUUM yourself.

VACUUM table_name DRY RUN

DRY RUN returns a list of up to 1000 files that would be deleted. The retention window comes from delta.deletedFileRetentionDuration, which defaults to 7 days. Raising it gives you longer time travel. It also keeps erased records in storage longer and increases storage cost, which matters when you have a one-month deadline to meet.

ALTER TABLE table_name SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '30 days');

Lowering the threshold below 7 days is possible but risky. Databricks strongly recommends at least 7 days, because long-running jobs may write files that are not yet committed, and a short window could delete them. A safety check blocks dangerous VACUUM commands. You can disable it by setting spark.databricks.delta.retentionDurationCheck.enabled to false, but only when you are sure no operation on the table runs longer than the interval you plan to use.

Sources342

3.Deletion vectors: REORG TABLE ... APPLY (PURGE) before VACUUM

Deletion vectors speed up DELETE, UPDATE and MERGE. Instead of rewriting each Parquet file that holds an affected row, they mark the row as modified in metadata, and reads apply those marks at query time. On Delta Lake tables you have to enable deletion vectors explicitly. Apache Iceberg v3 tables have them by default.

This is a soft delete. Data modifications on tables with deletion vectors, and dropping columns with column mapping enabled, both leave the old values in the table's current files. Running VACUUM alone won't remove them, because those files are still referenced by the current version.

REORG TABLE events WHERE date >= '2022-01-01' APPLY (PURGE);

APPLY (PURGE) rewrites only the files that contain soft-deleted data. The optional WHERE clause accepts only filters on partition key attributes. The command is idempotent, so running it a second time has no effect. It creates a new table version, but the pre-REORG files are still kept for time travel. That means you need a delay: VACUUM can delete those older files only after they pass the retention threshold. Shortening the retention period shortens the wait, at the cost of less history.

REORG TABLE events APPLY ( ? );

Sources563

4.Copies that outlive VACUUM

VACUUM is the last step for files the table manages, but erased data can survive elsewhere. When disk caching is enabled, a cluster may still hold data from Parquet files that VACUUM has deleted, so old table versions could still be queried from that cluster until it restarts. Change data feed records are stored in the _change_data directory, and VACUUM removes them along with the rest. Table history is also not an archive. Databricks says not to use it as a long-term backup, so a compliant purge design shouldn't depend on old versions sticking around.

Sources31

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Once a DELETE commits, the customer's data is gone from storage.Why is that wrong?

   The DELETE creates a new version, but the earlier data files stay in cloud storage and can be reached with time travel until VACUUM removes them.

   Covered in Why a DELETE is not yet an erasure

2. 2.VACUUM keeps deleted data files for 30 days by default.Why is that wrong?

   The VACUUM threshold (deletedFileRetentionDuration) defaults to 7 days. The 30-day default is logRetentionDuration, which governs table history.

   Covered in VACUUM and predictive optimization on Unity Catalog managed tables

3. 3.On a table with deletion vectors, DELETE followed by VACUUM physically erases the rows.Why is that wrong?

   Deletion vectors only mark rows as deleted in metadata, so the data stays in the current files. You must run REORG TABLE ... APPLY (PURGE), then VACUUM once the old files expire.

   Covered in Deletion vectors: REORG TABLE ... APPLY (PURGE) before VACUUM

Practise it for real

Erase one user from a Delta table with deletion vectors and confirm the files are physically purged.

1. 1.Run spark.sql("DELETE FROM bronze.users WHERE user_id = 5") against a test copy of the table.

   Why: This removes the user from the current table version.

   You should see: A new table version in which user 5 no longer appears.

2. 2.Run DESCRIBE HISTORY on the table, then query an earlier version with VERSION AS OF.

   Why: This shows that the deleted rows can still be reached through time travel.

   You should see: The DELETE appears in the history, and the earlier version still returns user 5.

3. 3.Run REORG TABLE on the table with APPLY (PURGE).

   Why: With deletion vectors the delete was soft, and REORG rewrites the current files without the deleted rows.

   You should see: A new version in the history. Older versions still reference the old files.

4. 4.Once the retention threshold has passed, run VACUUM with DRY RUN, then run VACUUM.

   Why: DRY RUN lets you check the list of files before the permanent deletion.

   You should see: DRY RUN lists the expired files. After VACUUM, time travel to versions older than the retention period fails.