Is there a way to permanenetly purge data in Databricks based on certain condition ?

soumend7115 — Wed, 14 May 2025 11:26:23 GMT

Like, from a particular Databricks table, I want to permanently purge certain rows based on a specific condition e.g., WHERE <col1>="Val1" and <col2>="Val2"

Re: Is there a way to permanenetly purge data in Databricks based on certain condition ?

Lucas_TBrabo — Wed, 14 May 2025 12:44:21 GMT

Hi @soumend7115!

I will assume you are talking about managed tables in Unity Catalog here, if thats not the case, let me know.

We can segregate this in two steps:

You can use a DELETE FROM SQL statement to remove rows that match your condition. For example: DELETE FROM table_name WHERE col1 = 'Val1' AND col2 = 'Val2'
After running the delete command, the data files are retained a retention period (default is 7 days) to support time travel and rollback. In order to permanently purge the data you can run the VACUUM command. For example:
VACUUM table_name RETAIN 0 HOURS
This physically deletes unreferenced files immediately. CAUTION: Setting retention to zero hours disables time travel for those files and should only be used if you're certain the data must be irrecoverable

Worth mentioning: If you have Deletion Vectors enabled in the table, you may need to run a REORG TABLE command.

Documentation reference:

topic Is there a way to permanenetly purge data in Databricks based on certain condition ? in Data Engineering

Is there a way to permanenetly purge data in Databricks based on certain condition ?

Re: Is there a way to permanenetly purge data in Databricks based on certain condition ?