Databricks SQL: Remove Expired Files Safely with VACUUM

Fast-track learning 🇩🇪 🇯🇵 🇰🇷 🇨🇳 🐍 — Get Certified in 30 Days — ICH . KOMME . GLEICH

Databricks SQL: Remove Expired Files Safely with VACUUM

This tutorial demonstrates how to safely remove expired files from a Databricks SQL table using the VACUUM command. We’ll build upon progressively complex scenarios to illustrate best practices.

Script 1: Basic VACUUM Implementation

This script sets up a simple table, inserts some sample data with varying timestamps, and then performs a basic VACUUM operation to remove files older than 7 days. This is the foundational step.

Explanation:

CREATE TABLE IF NOT EXISTS sales_data (...): Creates a table named `sales_data` if it doesn’t already exist. This table holds sales information including a sale ID, product name, and the date of the sale.
INSERT INTO sales_data (...) VALUES (...): Inserts three rows of sample sales data into the `sales_data` table. The timestamps are varied, allowing us to test the VACUUM functionality.
VACUUM sales_data RETAIN 7 DAY;: This is the core command. It removes files from the `sales_data` table that are older than 7 days from the current date. The `RETAIN` clause specifies the retention period.
SELECT COUNT() FROM sales_data;: This query counts the number of rows remaining in the `sales_data` table after the VACUUM operation. This is our validation step.

Key takeaway: The `VACUUM` command removes files older than the specified retention period. This is crucial to manage storage costs and ensure data freshness.

Script 2: VACUUM with Different Retention Period

This script modifies the previous scenario by using a different retention period (14 days) and demonstrates the effect on the table’s size.

Explanation:

USE CATALOG sales_data;: Switches the active catalog to `sales_data`, allowing us to refer to the table by its catalog name.
The remaining steps are similar to Script 1, except the `RETAIN` clause is set to `14 DAY`. This means files older than 14 days will be removed.

Key takeaway: The `RETAIN` clause controls the retention period for data. Adjusting this value impacts the amount of data stored and the frequency of VACUUM operations.

Script 3: VACUUM after Data Modification

This script simulates a scenario where data is updated and then VACUUM is executed. This demonstrates the behavior after changes have been made to the data.

Explanation:

ENGINE=HERO;: Specifies the storage engine for the table. HERO provides automatic VACUUM capabilities.
INSERT INTO sales_data (...) VALUES (...): Inserts three rows with `last_updated` columns.
UPDATE sales_data SET last_updated = CURRENT_TIMESTAMP() WHERE sale_id = 1;: Updates the `last_updated` column for `sale_id = 1`, demonstrating data modification.
VACUUM sales_data RETAIN 7 DAY;: Removes files older than 7 days from the `sales_data` table.
SELECT COUNT() FROM sales_data;: Counts the remaining rows.

Key takeaway: VACUUM operates on the current state of the table. Data modifications before a VACUUM operation will not be affected. The `HERO` engine simplifies this by handling the necessary updates behind the scenes.