VACUUM & retention

Databricks · Delta Lake

Theory

When Delta rewrites data (UPDATE, MERGE, OPTIMIZE, overwrite), the old Parquet files are removed from the transaction log but stay in storage so that time travel and running readers still work. VACUUM permanently deletes files that are no longer referenced by the current version and are older than the retention threshold, which is 7 days (168 hours) by default. It frees storage and cost but ends time travel to versions that needed those files.

VACUUM t DRY RUN lists the files it would delete without deleting them. VACUUM t RETAIN 240 HOURS keeps more history; a value below 7 days is blocked by a safety check because it can break long-running queries and streams. The table property delta.deletedFileRetentionDuration changes the default. On Unity Catalog managed tables, predictive optimization runs VACUUM, OPTIMIZE and ANALYZE for you. For example, after a month of daily MERGE jobs, VACUUM removes the superseded files older than a week.

Why we use it

Without VACUUM, every rewrite leaves old files behind and storage grows forever. With careless VACUUM settings you lose the ability to roll back. Knowing retention lets you balance cost against recovery.

When to use it

Run VACUUM regularly on external tables or where predictive optimization is off, usually weekly with the default retention. Keep retention long enough for your time travel and streaming needs. Never lower it below 7 days without a clear reason.

How it works

  1. Old files pile up after MERGE and OPTIMIZE
  2. VACUUM ... DRY RUN to preview
  3. Are the files unreferenced and older than the retention period? Yes: Delete them from storage. No: Keep them for time travel.
  4. Storage cost goes down
  5. Time travel now reaches back only as far as the retention

Syntax

VACUUM t                       -- default 7-day retention
VACUUM t DRY RUN
VACUUM t RETAIN 240 HOURS
ALTER TABLE t SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 14 days')
ALTER SCHEMA s ENABLE PREDICTIVE OPTIMIZATION

Example

-- See what would be deleted
VACUUM main.sales.orders DRY RUN;

-- Delete unreferenced files older than 7 days (default)
VACUUM main.sales.orders;

-- Keep two weeks of time travel on this table
ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 14 days');

-- Let Databricks do maintenance for managed tables in this schema
ALTER SCHEMA main.sales ENABLE PREDICTIVE OPTIMIZATION;

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic