In Databricks, using VACUUM RETAIN 0 HOURS on a Delta table is risky and can lead to data loss and inconsistencies. Here’s why and what you should know:

Understanding VACUUM

• Purpose: The VACUUM command in Databricks is designed to remove old files from Delta tables that are no longer needed for time travel or data recovery. This helps to optimize storage costs.
• Default Retention: VACUUM retains files for seven days for time travel and recovery.
• Retention Override: You can override this default with the RETAIN clause, specifying a shorter retention period.

Risks of RETAIN 0 HOURS

• Data Loss: Setting the retention period to zero hours means that all old file versions are immediately eligible for deletion. This can delete data that concurrent readers or writers still use, leading to data loss and inconsistencies.
• Transaction Conflicts:  Long-running or lagging transactions referencing older file versions may fail if those files are suddenly removed.
• Streaming Issues: Streaming queries behind the latest table updates may also be affected.

When to Use with Caution

Consider overriding the retention period in particular scenarios with no active readers or writers and no long-running or lagging processes. However, this is strongly discouraged in production environments.

Safe Practices

• Default Retention: In most cases, sticking with the default 7-day retention period is safest.
• Longer Retention: You can increase the retention period if you need more extended time travel.
• Caution with Overrides: If you must override the retention, do so with extreme caution only after thoroughly verifying that it’s safe.

Disabling the Safety Check (Not Recommended)

Databricks has a safety check to prevent accidental data loss when using RETAIN 0 HOURS. You can disable this check by setting the Spark configuration spark. data bricks.delta.retentionDurationCheck.enabled to false. However, this is highly discouraged as it removes the last line of defense against data corruption.

Example (For Demonstration Only – Do Not Use in Production)

SQL

SET spark.data bricks.delta.retentionDurationCheck.enabled = false;

VACUUM your_table_name RETAIN 0 HOURS;

Alternative: If you must permanently remove data for compliance or privacy reasons, consider using the DELETE command or DROP TABLE instead of VACUUM.