In Databricks, VACUUM is a command used to reclaim storage space by removing no-longer-needed data files. It’s particularly useful for Delta Lake tables but can also be applied to other file-based tables.

How VACUUM works on Delta Lake tables:

1. Identifies Unreferenced Files: VACUUM scans the Delta table’s transaction log and identifies data files that are no longer part of the latest table version due to operations like UPDATE, DELETE, or MERGE.
2. Checks Retention Period: It also considers a retention period (default: 7 days) and only removes files that have not been referenced for longer than this period. This ensures you can still access previous table versions for a limited time.
3. Deletes Unneeded Files: VACUUM safely deletes unreferenced and expired files from the underlying file system, freeing up storage space.

Benefits of using VACUUM:

• Reduces Storage Costs: By removing unnecessary files, VACUUM helps you optimize storage costs, especially in cloud environments where storage is often billed based on usage.
• Improves Performance: Removing stale files can improve the performance of queries and operations that scan the table’s data.

Important Considerations:

• Retention Period: Choose the retention period carefully. A shorter period frees up more space but limits your ability to time travel and access older table versions.
• Time Travel: After running VACUUM, you won’t be able to query table versions older than the retention period.
• Non-Delta Tables: VACUUM on non-Delta tables removes only uncommitted files older than the retention period. Databricks automatically trigger VACUUM operations for non-Delta tables as data is written.

How to run VACUUM:

SQL

VACUUM tableName RETAIN num HOURS;

Replace tableName with the name of your Delta Lake table and num with the desired retention period in hours.

Example:

SQL

VACUUM myDeltaTable RETAIN 24 HOURS;

This command will remove files from myDeltaTable that haven’t been referenced for more than 24 hours.