DELETE Removed the Customer Record—but Did It Remove the Data?

[amitsharma1707]

DELETE Removed the Customer Record—but Did It Remove the Data?
A successful Delta Lake DELETE makes a record disappear from the current table version. However, it may not immediately remove that record from the underlying storage files.

Imagine a fictional retailer receives a verified data-erasure request:

DELETE FROM main.sales.customer_transactions
WHERE customer_id = 'C-7782';
If deletion vectors are enabled, Databricks can record this as a soft delete. The affected Parquet file is not immediately rewritten; metadata tells queries to ignore the deleted row.

This improves the performance of DELETE, UPDATE, and MERGE, but physical removal requires additional steps.

-- Rewrite files containing soft-deleted records
REORG TABLE main.sales.customer_transactions
APPLY (PURGE);

-- Preview eligible obsolete files
VACUUM main.sales.customer_transactions DRY RUN;

-- Remove eligible files after the retention requirement is satisfied
VACUUM main.sales.customer_transactions;
These operations have different responsibilities:

DELETE removes the record from the current logical table state.

REORG TABLE ... APPLY (PURGE) rewrites current files containing soft-deleted data.

Older files can still support time travel.

VACUUM physically removes eligible obsolete files after the retention threshold.

The default VACUUM retention period is seven days. Reducing it without evaluating concurrent workloads and recovery requirements can create data-loss risk.

For sensitive-data deletion, a reliable runbook should validate:

The record is absent from the current table.

Soft-deleted data has been purged from current files.

Older files have passed the required retention period.

VACUUM completed successfully.

Downstream tables, extracts, caches, and shared copies are handled separately.

The operations are recorded for audit and compliance evidence.

The key distinction is:

Logical deletion controls what queries return.
File rewriting and retention-aware cleanup control when old data is physically removed.

Deletion vectors documentation | REORG TABLE documentation | VACUUM documentation

Does your data-deletion process distinguish logical removal, file rewriting, and physical deletion?

#Databricks #DeltaLake #DeletionVectors #DataPrivacy #DataGovernance #REORG #VACUUM #DataEngineering

[diegogtr]

Nice writeup — this distinction between logical and physical deletion is exactly the kind of thing that trips people up when a "right to erasure" request lands on your desk and legal wants a straight yes/no answer.

Worth flagging that all of this soft-delete behavior only kicks in if deletion vectors are actually enabled on the table (delta.enableDeletionVectors = true, which is the default on newer runtimes/UC-managed tables now). Without DVs, DELETE just rewrites the Parquet files on the spot, so REORG...APPLY(PURGE) has nothing to do.

The retention window is the part I'd push back on a little — delta.deletedFileRetentionDuration isn't really a performance knob, it's your compliance gate. You can force it down with VACUUM ... RETAIN 0 HOURS and disabling the retention check, but that kills time travel and can break concurrent readers, so it's not something to just script — someone needs to actually sign off on giving up that safety in exchange for erasure speed.

Also worth turning on spark.databricks.delta.vacuum.logging.enabled before you need it — by default VACUUM doesn't log which files it deleted, and that log is exactly what you'd want to hand a DPO as proof the data is physically gone.

And honestly the part that breaks most erasure processes isn't any of this — it's everything downstream that still has a copy: Delta Sharing recipients, extracts, BI caches, CDC feeds, Change Data Feed itself. All of those need their own purge/retention story or the "deleted" record just keeps existing somewhere else.

So yes, in practice it ends up being three separate SLAs: logical removal is immediate, physical removal is bounded by retention + VACUUM, and downstream removal is tracked per system — and that last one is usually the slowest and riskiest leg.

[Islam_hoti]

Good breakdown, and the logical versus physical distinction is the part most teams get wrong. Three gaps worth adding to that runbook.

Change data feed. If it is enabled on the table, the deleted rows also exist in the _change_data directory as change records. Those are cleaned by VACUUM, but it is a second copy that people forget exists, so a table that looks clean after REORG and VACUUM can still have the record sitting in its change feed until retention passes.

Shallow clones. A shallow clone references the source data files rather than copying them, so a clone keeps deleted data reachable even after you have handled the source. It also runs the other way: running VACUUM on a source table that has shallow clones can leave those clones raising FileNotFoundException. Either way, clones need to be enumerated as part of the process, not discovered afterwards.

Log retention. delta.deletedFileRetentionDuration defaults to seven days and controls when VACUUM can remove data files, but delta.logRetentionDuration defaults to thirty days and governs the transaction log itself. The log carries per file column statistics, including minimum and maximum values, so for some columns the deleted values can persist in log metadata after the data files are gone. Worth checking whether that matters for the fields in question.

One operational note: on Unity Catalog managed tables, predictive optimization may already be running VACUUM on its own schedule. That is convenient but it means the timing is not yours, which matters if you need to evidence exactly when physical removal happened.