8.4 Data Retention, Snapshot Isolation, & VACUUM Operations

8.4 Data Retention, Snapshot Isolation, & VACUUM Operations

Because Delta Lake operates on an immutable, multi-version concurrency control (MVCC) model, updates, deletes, merges, and compaction operations do not overwrite or delete existing Parquet files in place. Instead, they write new Parquet files and record remove actions in the _delta_log/ transaction log.

While this architectural model enables powerful features like Time Travel, point-in-time auditing, and zero-downtime rollback, it also causes unreferenced historical data files to accumulate in cloud storage over time. Data engineers must manage this storage lifecycle using the VACUUM command while understanding its operational constraints and safety guardrails.

---

1. Delta Lake Time Travel Mechanics

Time Travel enables data engineers and analysts to query historical snapshots of a Delta table exactly as they existed at a specific transaction version or point in time.

+-------------------------------------------------------------------------+
|                        TIME TRAVEL USE CASES                            |
+-------------------------------------------------------------------------+
|  1. Pipeline Debugging: Compare current output with prior version       |
|  2. ML Reproducibility: Train models against identical historical data |
|  3. Instant Rollback: Restore table after accidental corrupted write    |
|  4. Regulatory Auditing: Reconstruct regulatory reports at period-end   |
+-------------------------------------------------------------------------+

Time Travel Syntax

1. SQL Version and Timestamp Syntax:

-- Query specific version number
SELECT * FROM sales_silver VERSION AS OF 14;

-- Query exact point in time (ISO 8601 or standard timestamp string)
SELECT * FROM sales_silver TIMESTAMP AS OF '2026-08-20 14:30:00';

-- Syntax using the @ symbol
SELECT * FROM sales_silver@v14;
SELECT * FROM sales_silver@20260820143000000;

2. PySpark DataFrame Syntax:

# Load specific version
df_v14 = spark.read.format("delta").option("versionAsOf", 14).table("sales_silver")

# Load specific timestamp
df_ts = spark.read.format("delta").option("timestampAsOf", "2026-08-20 14:30:00").table("sales_silver")

3. Table Rollback with RESTORE:

-- Rollback the table to a known good historical snapshot
RESTORE TABLE sales_silver TO VERSION AS OF 12;
RESTORE TABLE sales_silver TO TIMESTAMP AS OF '2026-08-19 00:00:00';

---

2. Physical File Cleanup with the VACUUM Command

When rows are deleted or updated, the old Parquet files remain on ADLS Gen2 storage as "tombstoned" files. The VACUUM command scans the transaction log, identifies all files with remove actions that are older than a retention threshold, and physically deletes them from cloud object storage.

-- Remove tombstoned files older than the default retention period (7 days / 168 hours)
VACUUM sales_silver;

-- Explicitly specify retention duration (e.g., retain 14 days / 336 hours)
VACUUM sales_silver RETAIN 336 HOURS;

-- Perform a Dry Run to preview files that would be deleted without deleting them
VACUUM sales_silver DRY RUN;

                    VACUUM OPERATIONAL WORKFLOW

  ADLS Gen2 Storage Files              Transaction Log Status
  +---------------------------+        +-------------------------------------+
  | File A (Active v12)       | <----> | Referenced in latest snapshot: KEEP |
  | File B (Removed at v8)    | <----> | Removed 9 days ago: DELETE (older)  |
  | File C (Removed at v11)   | <----> | Removed 2 days ago: KEEP (< 7 days) |
  | File D (Staged uncommitted| <----> | Unreferenced in log > 7d: DELETE    |
  +---------------------------+        +-------------------------------------+

---

3. The 7-Day Safety Retention Guardrail

By default, Delta Lake enforces a 7-day (168-hour) minimum retention threshold for VACUUM. Attempting to run VACUUM with a retention threshold lower than 168 hours will fail with an IllegalArgumentException:

java.lang.IllegalArgumentException: 
requirement failed: The retention threshold is 168 hours. You are trying to vacuum with 0 hours.

Why the 7-Day Threshold Exists:

1. Long-Running Queries: If a reader started a 2-hour analytical query on snapshot version 10, physically deleting version 10 files after 30 minutes would cause the query to crash with a FileNotFoundException.
2. Concurrent Writers: Concurrent streaming or batch jobs staging files or validating OCC commits could have their active temporary files deleted mid-transaction.
3. Catastrophic Data Loss Prevention: Prevents accidental immediate deletion of entire tables.

Overriding the Retention Guardrail (Emergency / Dev Only)

To force VACUUM with zero or low retention (e.g., for compliance with GDPR "Right to Be Forgotten" or cleaning dev environments), you must explicitly disable the retention check in the Spark session:

-- 1. Disable safety retention check in Spark configuration
SET spark.databricks.delta.vacuum.retentionDurationCheck.enabled = false;

-- 2. Execute zero-hour VACUUM to purge all historical data immediately
VACUUM sales_silver RETAIN 0 HOURS;

Exam Trap & Caution: Disabling retentionDurationCheck and running VACUUM RETAIN 0 HOURS in production is dangerous. It will crash any concurrent readers/writers and permanently destroys all Time Travel history prior to the current snapshot.

---

4. Log Retention vs. Data File Retention

Delta Lake manages metadata lifecycle and physical data lifecycle using two independent retention properties:

+-----------------------------------------------------------------------------------+
|                    DATA RETENTION VS. TRANSACTION LOG RETENTION                   |
+-----------------------------------------------------------------------------------+
|  PROPERTY: delta.deletedFileRetentionDuration                                     |
|  - Default: 'interval 7 days'                                                     |
|  - Controls: Physical Parquet data files marked as removed                        |
|  - Action: Cleaned up ONLY when VACUUM is explicitly executed                     |
|                                                                                   |
|  PROPERTY: delta.logRetentionDuration                                             |
|  - Default: 'interval 30 days'                                                    |
|  - Controls: Commit JSON files (00000000000000000000.json) in _delta_log          |
|  - Action: Automatically pruned during checkpointing every 10 commits             |
+-----------------------------------------------------------------------------------+

Setting Table Retention Properties:

ALTER TABLE sales_silver SET TBLPROPERTIES (
    'delta.deletedFileRetentionDuration' = 'interval 14 days',
    'delta.logRetentionDuration' = 'interval 60 days'
);

What Happens to Time Travel After VACUUM?

• If you run VACUUM sales_silver RETAIN 168 HOURS (7 days), you can still query the table history metadata using DESCRIBE HISTORY sales_silver (because the JSON logs are kept for 30 days).
• However, attempting to query data rows via SELECT * FROM sales_silver VERSION AS OF 2 (where version 2 was created 10 days ago) will fail with a FileNotFoundException because the underlying Parquet payload files were physically deleted.

---

5. Multi-Threaded Parallel Deletion Performance

On large enterprise tables containing millions of tombstoned files, single-threaded driver deletion during VACUUM can take hours due to cloud object storage REST API latency.

Azure Databricks accelerates VACUUM by distributing file listing and deletion across Spark worker executors:

-- Enable parallel executor-based deletion for high-scale VACUUM operations
SET spark.databricks.delta.vacuum.parallelDelete.enabled = true;

When enabled, worker tasks issue concurrent HTTP DELETE calls to ADLS Gen2, reducing multi-million file vacuum times from hours to minutes.