6.3 Data Retention Policies, Time Travel Security, & Tag Governance

6.3 Data Retention Policies, Time Travel Security, & Tag Governance

DP-750 Exam Focus: Understand Delta Lake transaction log retention (delta.logRetentionDuration), deleted data file retention (delta.deletedFileRetentionDuration), and time travel mechanics. Master the operational and governance consequences of VACUUM, the required protocol for GDPR/CCPA hard data deletion, and how to apply and query Unity Catalog tags for Attribute-Based Access Control (ABAC) and data classification.

---

1. Delta Lake Time Travel & Transaction Log Mechanics

Delta Lake brings ACID transactional guarantees to cloud object storage (ADLS Gen2) by pairing immutable Parquet data files with an ordered, serialized transaction log located in the _delta_log/ directory.

Every write operation—whether an INSERT, UPDATE, DELETE, MERGE, or OPTIMIZE—creates a new atomic commit JSON file (000000.json, 000001.json, etc.). When rows are updated or deleted, Delta Lake does not modify existing Parquet files in place. Instead, it writes new Parquet files containing the modified data and records tombstones (metadata markers) in the transaction log indicating that the older Parquet files are logically obsolete for future transactions.

                                DELTA LAKE SNAPSHOT ISOLATION

  _delta_log/
  +---------------+     +---------------+     +---------------+ 
  |  000000.json  | --> |  000001.json  | --> |  000002.json  |
  | (Add: file_A) |     | (Add: file_B) |     | (Remove: file_A| <-- Tombstone Marker
  +---------------+     +---------------+     |  Add: file_C) |
                                              +---------------+ 
                                                      |
         +--------------------------------------------+--------------------------------------------+
         | Snapshot at Version 1 (file_A, file_B)                                  | Snapshot at Version 2 (file_B, file_C)
         v                                                                         v
  [ Query Version 1 ]                                                       [ Query Version 2 (Current) ]
  Reads: file_A.parquet, file_B.parquet                                     Reads: file_B.parquet, file_C.parquet

Time Travel Query Syntax

Data engineers can query historical states of any Delta table using version numbers or timestamps:

-- 1. Querying table state at a specific commit version
SELECT * FROM prod_sales.curated.orders VERSION AS OF 14;

-- 2. Querying table state at a specific historical timestamp
SELECT * FROM prod_sales.curated.orders TIMESTAMP AS OF '2026-08-01 12:00:00';

-- 3. PySpark DataFrame Time Travel API
df_v14 = spark.read.table("prod_sales.curated.orders").option("versionAsOf", 14).load()
df_ts = spark.read.table("prod_sales.curated.orders").option("timestampAsOf", "2026-08-01T12:00:00Z").load()

Auditing History with DESCRIBE HISTORY

The DESCRIBE HISTORY command displays the complete chronological log of commits, detailing operations, user identities, cluster IDs, and affected file metrics:

DESCRIBE HISTORY prod_sales.curated.orders;

Restoring Historical Snapshots

If a pipeline erroneously corrupts a table, engineers can roll back the table instantaneously using RESTORE:

RESTORE TABLE prod_sales.curated.orders TO VERSION AS OF 13;

Restoring does not delete transaction history; it appends a new commit (version 16) that resets the active snapshot to match version 13.

---

2. Table Retention Properties: Log vs. File Retention

Delta Lake data retention is governed by two complementary table properties configured in TBLPROPERTIES:

\text{delta.logRetentionDuration} &= \text{Duration transaction log commits are preserved (Default: 30 days)} \\ \text{delta.deletedFileRetentionDuration} &= \text{Duration tombstoned data files are preserved before VACUUM eligibility (Default: 7 days)} \end{aligned}$$ ### 1. `delta.logRetentionDuration` - **Default Value:** `interval 30 days` - **Function:** Controls how long `.json` commit files and checkpoint files are retained in `_delta_log/` before being cleaned up during automatic log compaction. - **Impact:** Once commit logs older than this threshold are purged, you can no longer query history or time travel past that timestamp, even if physical data files still exist. ### 2. `delta.deletedFileRetentionDuration` - **Default Value:** `interval 7 days` (168 hours) - **Function:** Controls how long tombstoned (logically removed or replaced) Parquet files remain in storage before the `VACUUM` command is permitted to physically delete them. - **Safety Margin:** The default 7-day threshold ensures that concurrent long-running queries or streaming readers referencing older snapshots do not fail due to missing underlying files. ```sql -- Altering retention properties for an enterprise Delta table ALTER TABLE prod_sales.curated.orders SET TBLPROPERTIES ( 'delta.logRetentionDuration' = 'interval 60 days', 'delta.deletedFileRetentionDuration' = 'interval 14 days' ); ``` --- ## 3. The `VACUUM` Command & Storage Lifecycle Management While logical `DELETE`, `UPDATE`, and `MERGE` operations mark older files as unreferenced, they do not delete physical files from Azure Data Lake Storage Gen2. Over time, storage costs increase as obsolete Parquet files accumulate. The **`VACUUM`** command recursively scans the Delta table directory and permanently deletes Parquet data files that are no longer referenced in the latest table snapshot and are older than the specified retention threshold. ``` VACUUM EXECUTION TIMELINE Day -10 Day -7 (Retention Threshold) Day 0 (Now) ----+------------------------+------------------------------------+----------------> | | | v v v [ File 101.parquet ] [ Threshold Boundary ] [ Active Snapshot ] (Tombstoned 10 days ago) (7 Days / 168 Hours) (Currently Referenced Files) | v PERMANENTLY DELETED by: VACUUM prod_sales.curated.orders RETAIN 168 HOURS; ``` ### `VACUUM` Syntax and Operations ```sql -- 1. Dry run: Preview which files will be deleted without removing them VACUUM prod_sales.curated.orders RETAIN 168 HOURS DRY RUN; -- 2. Execute physical vacuum with default retention (168 hours / 7 days) VACUUM prod_sales.curated.orders; -- 3. Execute physical vacuum with explicit 14-day retention VACUUM prod_sales.curated.orders RETAIN 336 HOURS; ``` ### Critical Governance & Safety Rules 1. **Irrevocable Time Travel Truncation:** Once `VACUUM` removes tombstoned files, any attempt to time travel back to a version referencing those deleted files fails with a `FileNotFoundException`. 2. **The Retention Safety Check:** Databricks enforces a built-in safety check preventing `VACUUM` with a retention threshold under 168 hours (7 days). Overriding this requires disabling `spark.databricks.delta.vacuum.parallelDelete.enabled` or setting `spark.databricks.delta.retentionDurationCheck.enabled = false` in cluster Spark configuration. --- ## 4. GDPR / CCPA Compliance: The "Right to be Forgotten" Protocol Under data privacy regulations like GDPR (Article 17) and CCPA, organizations must permanently erase personal data (PII) upon consumer request within strict statutory timeframes (e.g., 30 days). ### The Problem: Logical Delete vs. Physical Storage Executing a standard `DELETE FROM customers WHERE customer_id = 'CUST_8841'` merely marks the row as deleted in the newest snapshot commit. The customer's PII remains completely legible in older Parquet files accessible via time travel, violating compliance mandates. ``` GDPR HARD PURGE COMPLIANCE WORKFLOW Step 1: Execute Logical Delete / Anonymization +-------------------------------------------------------------------------+ | DELETE FROM prod_crm.customers.dim_customer WHERE customer_id = '8841'; | +-------------------------------------------------------------------------+ | v Step 2: Lower File Retention Threshold +-------------------------------------------------------------------------+ | ALTER TABLE prod_crm.customers.dim_customer | | SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '0 hours'); | +-------------------------------------------------------------------------+ | v Step 3: Execute Physical Vacuum (Hard Purge) +-------------------------------------------------------------------------+ | -- Executed on compute with retention check disabled | | VACUUM prod_crm.customers.dim_customer RETAIN 0 HOURS; | +-------------------------------------------------------------------------+ | v Step 4: Restore Enterprise Retention Policy +-------------------------------------------------------------------------+ | ALTER TABLE prod_crm.customers.dim_customer | | SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '7 days'); | +-------------------------------------------------------------------------+ ``` > **Exam Scenario:** To ensure GDPR compliance for data deletion requests, you must execute the `DELETE` statement to remove data from the active snapshot AND execute `VACUUM` to purge tombstoned files from ADLS Gen2 storage.