Syntax error when running vacuum with USING INVENTORY command

Upgrade your Databricks Runtime to version 15.2 or above.

Written by jessica.santos

Last published at: January 22nd, 2025

Problem

You are attempting to run Delta VACUUM with the USING INVENTORY operation according to the documentation, Efficient Delta Vacuum with File Inventory.  You use an SQL command such as the following, and get a syntax error. 

 

SQL command

VACUUM <your-schema>.<your-table> using inventory (select 's3://'||bucket||'/'||key as path, length, isDir, modificationTime
from inventory.datalake_report
where bucket = '<your-datalake>'
and table = '<your-schema>.<your-table>'
)
RETAIN 24 HOURS

 

Error

[PARSE_SYNTAX_ERROR] Syntax error at or near 'VACUUM'.

 

Cause

Your cluster is using a Databricks Runtime version below 15.2. Vacuum inventory support was released as part of Databricks Runtime starting with 15.2. 

 

Solution

Upgrade your cluster's Databricks Runtime to 15.2 or above to be able to run VACUUM with the USING INVENTORY SQL command.