Managing Delta Table Retention Policies

Understanding the Components of Table History

Before we configure retention, let's quickly review the structure of a Delta table. As you know, a Delta table consists of two main parts stored in your cloud storage:

1. Data Files: Parquet files that contain the actual data for your table.
2. Transaction Log (_delta_log): A directory containing JSON files that record every transaction (e.g., ADD, REMOVE) that has ever occurred on the table.

For time travel to work, Delta Lake needs both the transaction log entry for a specific version and the underlying data files referenced by that log entry. If either one is missing, you cannot query that historical version of the table.

Our goal today is to learn how to control how long each of these components is kept.

Configuring Log and Data Retention

Delta Lake provides two key table properties to manage the retention of logs and data files. It's important to understand the distinction between them.

As the reading explains, the two key properties are:

1. delta.logRetentionDuration:

   • What it does: Controls how long the transaction log files (the history of changes) are kept.
   • Default: 30 days.
   • Purpose: Primarily for auditing. Running DESCRIBE HISTORY will only show changes within this retention window.

2. delta.deletedFileRetentionDuration:

   • What it does: Controls how long ago a data file must be marked as "unreferenced" before it can be physically deleted by the VACUUM command.
   • Default: 7 days.
   • Purpose: Acts as a safety net for time travel and prevents premature deletion of data files. If you VACUUM your table, any file that is no longer part of the latest version and is older than this duration will be deleted.

You can set these properties when you create a table or alter an existing one using TBLPROPERTIES.

SQL Example:

-- Set properties during table creation
CREATE TABLE transactions (
  id INT,
  amount DECIMAL(10, 2),
  timestamp TIMESTAMP
)
USING DELTA
TBLPROPERTIES (
  'delta.logRetentionDuration' = 'interval 90 days',
  'delta.deletedFileRetentionDuration' = 'interval 30 days'
);

-- Alter properties on an existing table
ALTER TABLE transactions SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 14 days'
);

Important Note: A critical rule is that logRetentionDuration must be greater than or equal to deletedFileRetentionDuration. This makes logical sense—there's no point in keeping a transaction log for a version of the table whose data files have already been deleted.

The VACUUM Command: Cleaning Up Old Data

The deletedFileRetentionDuration property is only a threshold; it doesn't delete anything by itself. The command that performs the actual cleanup is VACUUM.

VACUUM is a maintenance operation that you run on a Delta table to physically remove data files that are no longer referenced by a table version and are older than the data retention period. Given your experience optimizing ETL processes, you can think of this as a garbage collection process for your Delta tables, essential for managing storage costs.

The following video provides a practical, hands-on look at VACUUM, which aligns well with your preference for practice-oriented learning.

Hands-On Practice with VACUUM

Let's walk through a scenario in your Databricks notebook. This will solidify your understanding of how data retention and VACUUM interact.

Step 1: Create a Table and Generate History
First, create a table and perform an update to create some old, unreferenced file versions.

# Create initial data
data = [(i,) for i in range(10)]
df = spark.createDataFrame(data, ["id"])
df.write.format("delta").mode("overwrite").saveAsTable("vacuum_test")




# Update the data to create new files and mark old ones as unreferenced
spark.sql("UPDATE vacuum_test SET id = id + 100")




# Check history
display(spark.sql("DESCRIBE HISTORY vacuum_test"))

You should see two versions of the table: the initial creation and the update. The update operation created new Parquet files and marked the original ones as obsolete.

Step 2: Dry Run VACUUM
Before deleting anything, always perform a DRY RUN. This will show you a list of files that would be deleted without actually deleting them.

-- This will likely return an empty list because the files are not yet older than the default 7-day retention period.
VACUUM vacuum_test DRY RUN

Step 3: Override Retention for Demonstration
For this exercise, we'll tell VACUUM to delete files older than 0 hours. This is extremely dangerous in production, as it can delete files that are still being written or used by long-running queries. Databricks has a safety check to prevent this by default.

-- This command will fail with an error!
VACUUM vacuum_test RETAIN 0 HOURS

You'll get an exception recommending you don't use a retention period of less than 168 hours (7 days). To proceed, you must first disable this safety check for your Spark session.

# Disable the safety check (for demo purposes only!)
spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")




# Now, the VACUUM with 0 hours will work
# The DRY RUN will now list the original files from version 0
VACUUM vacuum_test DRY RUN RETAIN 0 HOURS

This shows the power of the RETAIN clause and the importance of the built-in safety features.

Step 4: Execute VACUUM and See the Consequences
Now, run the VACUUM for real and then try to time travel to the initial version.

-- Physically delete the old files
VACUUM vacuum_test RETAIN 0 HOURS

-- This time travel query will now FAIL!
SELECT * FROM vacuum_test VERSION AS OF 0

The query fails because while the transaction log for version 0 still exists, the VACUUM command has deleted the data files it points to. This demonstrates perfectly the relationship between data retention, VACUUM, and time travel.

The following video segment shows exactly this outcome.

Conclusion

In this lesson, you've learned to configure two of the most fundamental properties for managing Delta tables. This knowledge is essential for building robust, cost-effective, and auditable data pipelines.

Key Takeaways:

• There are two distinct retention settings: delta.logRetentionDuration for transaction history (auditing) and delta.deletedFileRetentionDuration for physical data files (time travel and storage).
• The VACUUM command is used to reclaim storage by physically deleting old data files that are beyond the deletedFileRetentionDuration.
• Time travel is only possible when both the transaction log and the corresponding data files exist for a given version.
• Always use DRY RUN before executing VACUUM in production, and be extremely cautious about setting low retention periods.

Preview of the Next Lesson:
We've just focused on cleaning up old data files to manage storage. In the next lesson, we will shift our attention to improving the performance of your active data. You will learn how to use the OPTIMIZE and Z-ORDER commands to compact small files and intelligently organize data, which can dramatically speed up read queries on your Delta tables.