Deleted Rows Still Occupy Files: Verify a Delta Lake Purge

The operating question is not “does SELECT still return the row?” It is “what, exactly, can we prove was removed, from which storage scope, without breaking readers that still depend on older files?” A row can become logically invisible before the Parquet file containing its old value is rewritten, and a rewritten file can become obsolete before retention allows physical removal.

Databricks: Deletion vectors in Databricks (updated September 11, 2026) describes deletion-vector changes as soft deletes. Databricks: Remove unused data files with vacuum (updated September 11, 2026) separates that rewrite from later file deletion.

This runbook is for data engineers, platform owners, security reviewers and data stewards operating Databricks on AWS against a Unity Catalog managed Delta table, using a declared compatible Databricks Runtime and the default FULL vacuum path. It does not generalize Databricks SQL commands to every Delta implementation. It also does not certify universal erasure: backups, exports, object-store versions, caches and other copies require their own evidence.

The output is a scoped purge-evidence record: a chain from authorization, to current-table state, to rewrite completion, to retention eligibility, to obsolete-file cleanup, with residual work left visible rather than implied away. The documentation cited here was checked on September 16, 2026; its September 11 update date is a documentation timestamp, not a feature-launch date.

State exactly what a purge decision must prove

Start with an authorized request, not with a maintenance command. A useful example is: “Remove the synthetic subject SYNTH-0042 from sandbox_governance.purge_demo.customer_events, then provide evidence about the current table and obsolete Delta data files.” That sentence defines a target and a table, but it does not authorize deletion of unrelated exports, backups, clones or cloud-object versions. Those are distinct assets with distinct owners.

The evidence chain should separate four claims that are often collapsed into one. Databricks DELETE FROM removes rows that match a predicate from the table’s logical state; when deletion vectors are active, row changes can be represented as metadata-backed soft deletes rather than immediate Parquet rewrites. REORG TABLE ... APPLY (PURGE) rewrites files containing soft-deleted data. The old files can still remain afterward until they become eligible for VACUUM.

Proposed operating control: assign one evidence ID to all four stages and refuse to close the request if any stage is represented only by a screenshot, a verbal statement or a current SELECT. A purge record is strong when every claim names the artifact that supports it and the boundary that artifact does not cross.

Separate logical state from the physical file lifecycle

Delta Lake is transactional, but the transaction log and the data-file lifecycle answer different questions. A new table version records what the current snapshot should reference. It does not mean every byte from older versions is immediately removed. Databricks states that each modifying operation creates a new table version and that table history can support auditing and time travel only while the required data files remain available.

Table-history retention and data-file retention are separate controls. Databricks: Work with table history (updated September 11, 2026) gives a default 30-day log-retention setting, while the vacuum guidance gives a default seven-day data-file retention threshold.

This is the narrow operational extension of broader data lake and warehouse management foundations: storage organization and governance are prerequisites, but purge proof requires tracking which version changed visibility, which operation rewrote files and which later operation removed obsolete files.

Deletion vectors and the active table snapshot

Do not infer that deletion vectors are enabled because the workspace or runtime supports them. Databricks says Delta tables must explicitly have deletion vectors enabled, and DESCRIBE DETAIL can report table properties, protocol versions and tableFeatures. The same deletion-vector documentation warns that enabling the feature upgrades the table protocol and that clients without deletion-vector support cannot read the table afterward. Databricks: Review table details with DESCRIBE DETAIL (updated September 11, 2026) is therefore an evidence source, not a convenience command.

For this runbook, record actual feature state before you explain a deletion as “soft.” If deletion vectors are absent, the physical path of DELETE can differ. The invariant remains: current-row absence is a logical-state claim, and physical purge requires file-level lifecycle evidence. Do not add a protocol-changing command merely to make the example resemble the documentation.

Rewritten files, obsolete files and historical versions

REORG TABLE ... APPLY (PURGE) rewrites only files containing soft-deleted data and is documented as idempotent. Databricks also states that the soft-deleted values may still remain in the old files after the rewrite and that VACUUM is the operation that later removes eligible old files. An OPTIMIZE or compaction event is not an equivalent purge certificate because the deletion-vector guide says compaction does not strictly guarantee that every deletion-vector change is physically applied.

Likewise, a history entry is not proof that the underlying version remains readable. Databricks explicitly separates table-history retention from time-travel availability and warns not to treat table history as long-term archival backup. The completion record should therefore distinguish “history metadata exists” from “required historical data files still exist.”

Inventory table scope, owners and dependent readers

Before any state-changing step, create a scope ledger. For the worked example, the declared platform scope is Databricks on AWS, Unity Catalog managed Delta, default FULL VACUUM, no unverified direct writer to the managed storage location. The actual runtime must be recorded from the compute that performs the work. Databricks documents REORG TABLE for Databricks SQL and Databricks Runtime 11.3 LTS and above, while its deletion-vector guidance recommends Databricks Runtime 14.3 LTS and above to write deletion-vector tables with all optimizations and 12.2 LTS and above for reads.

Use ownership explicitly. The platform administrator may have technical permission to run maintenance, but that does not create business authorization to erase data. This is where data engineer and DBA ownership boundaries are useful background: operational roles overlap, but the data owner, platform owner and security reviewer still need named responsibilities for this request.

DESCRIBE DETAIL <qualified_table> is a read-only inspection command documented to return table format, location, partitions, properties, protocol versions and enabled features. Capture its result under the evidence ID before the delete so later reviewers can tell what table and feature state the runbook actually addressed.

Set the retention and evidence contract before executing

A purge request needs a retention contract before deletion, because the cleanup stage intentionally destroys some time-travel capability. Databricks says the default data-file retention threshold for VACUUM is seven days and strongly recommends at least seven days; it warns that too-short retention can delete uncommitted files from long-running jobs. Separately, table history has a default 30-day log-retention setting, but that does not keep data files alive for time travel after vacuum.

The local contract should be stricter than “use the default.” It should name the approved retention basis and the longest supported reader or writer interval. Proposed local control: set R = max(vendor safety floor, organization recovery requirement, longest supported operation window plus margin). This is an operating model, not a Databricks formula. In the hypothetical example below, R = 7 days only because the scenario declares that the organization has approved the documented default and no supported operation approaches seven days.

Reader safety and the retention clock

For deletion-vector purge evidence, start the critical cleanup clock at the rewrite completion, not at the original business request and not merely at the DELETE. Databricks explains that REORG ... APPLY (PURGE) creates a new table version and that older files used by prior versions remain until they have expired according to VACUUM retention. The vacuum guide explicitly says the vacuum must be delayed after the REORG so older files can expire.

There is a wording tension worth preserving. The deletion-vector page says to run VACUUM “with the retention threshold set to the purge completion timestamp,” while the vacuum guide explains eligibility as a retention interval and says to wait after REORG. This runbook does not translate the first wording into an absolute-timestamp syntax or a zero-hour vacuum. Operationally, record rewrite time T1, retain the approved interval R, and schedule candidate review no earlier than T1 + R.

Scope limits and residual-copy decisions

The table cleanup record should enumerate residual locations without inventing their behavior. Databricks documents that VACUUM removes eligible unused table data files and does not govern transaction-log cleanup; it also warns that disk cache can temporarily contain data from Parquet files already deleted by vacuum until the cluster is restarted. That cache behavior is inside documented scope and should be recorded if relevant.

Backups, exports, object-store versions, copied datasets, downstream extracts and recovery systems are different. The cited Delta documentation does not establish the lifecycle for every such copy. Therefore mark each as verified separately, out of scope by approval, or unresolved/escalated. Do not infer “purged” or “retained” merely from the Delta table operation.

Validate the logical deletion without overstating it

Use a disposable nonproduction table for executable examples. The following example assumes sandbox_governance.purge_demo.customer_events already exists, contains only synthetic data, and has been authorized for the exercise. DELETE FROM is state-changing; Databricks documents it for Delta Lake tables and says the predicate determines which rows are removed.

-- Read-only precheck on a disposable synthetic table.
SELECT COUNT(*) AS target_rows_before
FROM sandbox_governance.purge_demo.customer_events
WHERE subject_id = 'SYNTH-0042';

-- STATE-CHANGING: execute only under the approved synthetic request.
DELETE FROM sandbox_governance.purge_demo.customer_events
WHERE subject_id = 'SYNTH-0042';

-- Read-only logical validation.
SELECT COUNT(*) AS target_rows_after
FROM sandbox_governance.purge_demo.customer_events
WHERE subject_id = 'SYNTH-0042';

A successful result is not “zero rows therefore purged.” It is “the current table snapshot no longer returns the approved predicate.” Capture at least one unchanged control predicate as well, so an empty result caused by deleting the entire table or querying the wrong environment cannot pass unnoticed.

Databricks documents DESCRIBE HISTORY as returning provenance fields such as version, timestamp, user, operation, parameters and operation metrics. Its history schema lists delete metrics including numDeletedRows, numAddedFiles and numRemovedFiles, but availability can vary by write path and operation details. Treat those metrics as supporting evidence, not a substitute for the predicate check.

Inspect feature and client compatibility before rewriting

A table feature is also a reader contract. Databricks warns that enabling deletion vectors upgrades the table protocol and can make unsupported clients unable to read the table. The current compatibility table states that Databricks Runtime 12.2 LTS and above can read deletion-vector tables; write support varies by Photon and operation, and Databricks Runtime 14.3 LTS and above is the documented recommendation for writing with all optimizations. Open-source Delta clients have their own version floors.

Do not convert those platform statements into “our connector is compatible.” The actual inventory must include every supported reader and writer, including scheduled jobs, streaming queries, SQL warehouses, sharing recipients and third-party engines. A client/version that has not been tested against the table feature is an unresolved dependency, even when another client from the same ecosystem is documented as compatible.

The stop rule is simple: untested critical reader = hold. Do not add ALTER TABLE protocol changes, drop features or upgrade features solely to make the purge runbook proceed. Those are separate changes with separate compatibility impact.

For evidence, capture DESCRIBE DETAIL <qualified_table> before REORG and record tableFeatures, reader/writer protocol fields and properties. Databricks notes that the DESCRIBE DETAIL schema can vary by runtime and enabled features, so store the raw result rather than relying on a fixed hand-built parser that assumes every field is present.

Run a scoped REORG and capture its completion evidence

Once the logical delete and compatibility review pass, force the soft-deleted rows out of the current active files. Databricks documents the syntax REORG TABLE <table> APPLY (PURGE) and says it rewrites files containing soft-deleted data. It is idempotent: running the same purge again against an unchanged dataset has no effect. Databricks SQL: REORG TABLE (updated September 11, 2026) also states that old files may still contain the data afterward.

-- STATE-CHANGING: rewrites affected files in the authorized Delta table.
REORG TABLE sandbox_governance.purge_demo.customer_events APPLY (PURGE);

-- Read-only evidence capture after the rewrite.
DESCRIBE HISTORY sandbox_governance.purge_demo.customer_events;

Do not write WHERE subject_id = 'SYNTH-0042' onto REORG. The documented WHERE form for APPLY (PURGE) accepts only predicates involving partition key attributes. If you need to limit maintenance by partition, first prove that the predicate uses actual partition columns; otherwise run the table-scoped purge or redesign the maintenance window.

The operation record belongs in the same evidence chain as the request. This resembles the discipline in database administration automation: repeatable changes need versioned, attributable execution evidence. Here, however, the critical artifact is not “automation ran”; it is the table version and timestamp that establish the new retention boundary.

Wait for eligibility and review the deletion candidates

REORG completion is the start of a waiting period, not permission to vacuum immediately. Databricks says data files are deleted only after they expire under the vacuum retention period and explicitly instructs operators to delay VACUUM after REORG. The documented default data-file threshold is seven days, and Databricks strongly recommends at least seven days because long-running jobs can otherwise lose uncommitted files. This runbook does not recommend RETAIN 0 HOURS and does not disable the retention safety check.

There is also a documentation-stage issue around VACUUM LITE. The vacuum guide marks LITE Public Preview in Databricks Runtime 16.4 LTS and above, while the SQL reference says FULL/LITE applies in Runtime 16.1 and above. Because those live pages differ on the runtime floor, and because LITE has extra prerequisites and can omit files not represented in the log, this core runbook uses the default FULL path only. That unresolved documentation difference should be rechecked before adopting LITE.

Retention-aware scheduling after the rewrite

Assume the hypothetical REORG commits at T1 = 2026-09-16 10:00 UTC and the approved retention is R = 7 days. The earliest candidate-review time is 2026-09-23 10:00 UTC, subject to reader/writer safety and any longer organization policy. This is an invented timeline, not a test result. The business request might have been approved days earlier; that does not move T1 backward.

Proposed local control: before candidate review, reconfirm that no supported operation has a runtime or retry window approaching R, and freeze the evidence if a new long-running workload was introduced after approval. The seven-day default is a product default and recommendation, not proof that seven days is safe for every organization.

Dry-run evidence and candidate reconciliation

Databricks documents VACUUM <table> DRY RUN as a preview that does not delete files. The SQL reference says DRY RUN returns a list of up to 1,000 files to be deleted, so the preview can be truncated and must not be treated as a complete manifest for a table with more candidates.

-- READ-ONLY PREVIEW: does not remove files.
VACUUM sandbox_governance.purge_demo.customer_events DRY RUN;

A dry run is a proposal, not a receipt. Preserve it before actual cleanup so reviewers can compare intended candidate scope with the later operation metrics. If the preview contains a surprising path, stop. “It is old” is not sufficient authorization.

Execute cleanup and verify the bounded physical result

When the candidate review passes, run the documented default VACUUM against the qualified Delta table. This is state-changing: eligible obsolete files are removed. Databricks says Delta data files are deleted according to when they were logically removed from the transaction log plus the retention duration, not according to object-storage modification timestamps. The operation also reduces available time-travel history because older table versions may no longer have their required files.

-- STATE-CHANGING: removes eligible obsolete files under the table's configured retention.
VACUUM sandbox_governance.purge_demo.customer_events;

-- Read-only audit evidence.
DESCRIBE HISTORY sandbox_governance.purge_demo.customer_events;

For Unity Catalog managed tables, Databricks says vacuum audit information is committed to the transaction log and can be queried with DESCRIBE HISTORY. The history-metrics reference lists numDeletedFiles, numVacuumedDirectories and numFilesToDelete for VACUUM. Capture the actual operation result and history row; do not fabricate a reclaimed-byte number or infer that every candidate succeeded if the command failed or was interrupted.

Do not turn a storage permission error into a deletion claim. “Cannot list this prefix” proves only that the observer lacks access. Likewise, a clean SELECT after vacuum proves the current snapshot, not the fate of every backup or cache. Databricks also warns that disk cache can retain data from deleted Parquet files until the cluster is restarted, so cache disposition must be handled as a separate residual-control item when that feature is relevant.

Evaluate clones and historical readers on their own terms

Unity Catalog shallow clones require special treatment because they deliberately share source data files rather than copying them. The live Databricks page marks Unity Catalog shallow clones as Public Preview, requires different runtime floors for managed and external tables, and says source/clone relationships expand the set of files considered valid for vacuum. Databricks: Shallow clone for Unity Catalog tables (updated September 11, 2026) is the authority for this runbook’s clone behavior.

The important correction to a common oversimplification is explicit: Databricks says running VACUUM on the source of a Unity Catalog shallow clone does not break the cloned table because Unity Catalog tracks the relationship and protects files still required by the source or any shallow clone. Only files no longer valid for any tracked source/clone state are removed. Managed and external tables differ in where a vacuum can remove source data files.

Do not delete a clone merely to meet a purge deadline. Clone deletion is a separate destructive decision with its own owner and business impact. If an authorized clone legitimately keeps a file valid, the table purge can be held or scoped rather than forcing an unsafe dependency break.

Historical readers need the same discipline. Databricks says VACUUM removes data files needed for versions older than the retention window even though log history may remain longer. Therefore, “version 123 still appears in history” is not a recovery guarantee. Time travel is a reader capability contingent on both log and data retention, not an unconditional rollback plan.

Work a hypothetical purge request through the ledger

The following trace is entirely hypothetical. No command was executed as part of this research, and no file counts or storage savings are claimed. The table is the synthetic Unity Catalog managed Delta table used throughout; the approved retention R is seven days because this invented scenario declares that seven days satisfies both the Databricks recommendation and the local recovery requirement. In a real estate, R should be longer whenever workload or policy requires it.

The recovery and retention context around this example is adjacent to multicloud database recovery and retention responsibilities, but the evidence here remains specifically about Databricks Delta table files.

The decisive point is that the business request at T0 does not start the physical purge eligibility clock for rewritten files. T1 does. After vacuum, the completion claim is still bounded: “eligible obsolete Delta table files were removed according to the successful platform operation and recorded retention basis.” It is not “all copies everywhere are erased.”

Handle failure without erasing the evidence

A reliable purge process must make failure durable. Do not overwrite a failed run with a clean rerun and then present only the successful event. Preserve the first error, the table version at which it occurred, the candidate preview used, and the decision that allowed or blocked a retry. Databricks documents REORG ... APPLY (PURGE) as idempotent, so a repeat against the same dataset is a documented safe execution property; that does not mean every surrounding workflow step is automatically idempotent.

A bounded recovery plan has two phases. Before physical cleanup, pause, correct the issue and use the retained Delta history/files only within the verified retention capabilities. After successful physical cleanup, do not claim that RESTORE or time travel can recreate files that vacuum has removed. Databricks explicitly warns that the ability to query older versions is lost when required data files are vacuumed.

The handover packet for a failed case should contain the request ID, latest safe table version, failed command/job reference, error text, last successful proof claim, unresolved dependency owner and the next authorized action. That preserves accountability without pretending that a rerun erases operational history.

Build data engineering judgment through verifiable artifacts

This runbook is less about memorizing three SQL commands than about learning to separate evidence domains. A mature data engineer can say which artifact proves authorization, which proves current-table state, which proves compatibility, which proves file rewrite, which establishes retention eligibility and which remains outside the table boundary.

For broader sequencing of foundational skills, the data engineering learning roadmap covers the wider learning path. The live program page also lists storage provisioning, data pipelining, Spark/Hadoop, batch and streaming ingestion, encryption and data-governance topics, with a three-month, 12–14-hours-per-week structure for learners working toward a bachelor’s or higher-level degree.

For readers building those foundations in a structured format, Refonte Learning’s Data Engineering Program publishes coverage of data storage, pipelines, ETL/warehousing, Spark/Hadoop, batch and streaming processing, encryption and governance. Treat that as foundational preparation; the page does not establish that Databricks-specific deletion-vector, REORG or VACUUM purge labs are included.

Issue a scoped completion record and residual-work handover

Close only the claims that have evidence. For the in-scope Databricks table, a pass requires authorized scope, successful logical validation, observed feature/client compatibility, a trustworthy REORG completion boundary, elapsed approved retention from that rewrite, reviewed vacuum candidates, successful cleanup evidence and a final current-table validation. A hold means the process is incomplete but safely paused. Escalate means a policy owner, platform specialist or copy owner must decide something the runbook cannot infer.

A recommended completion sentence is deliberately narrow: “PASS for the declared Unity Catalog managed Delta table scope: the approved row is absent from the current snapshot; affected current files were rewritten by a completed REORG ... APPLY (PURGE); the approved retention interval elapsed from the rewrite boundary; and a subsequent successful FULL VACUUM removed eligible obsolete table files according to Databricks’ table-maintenance semantics. Backups, exports, object-store versions and other declared residual copies remain governed by the statuses listed below.” The product behavior underlying that sentence is documented; the approval model and evidence threshold are proposed local controls.

For handover, list each residual asset with owner, system, expected disposition, evidence due date and status. Do not leave “backup team” or “security” as a generic owner. If cache risk is relevant, record the cluster/cache control performed or the reason it remains open. If a shallow clone still legitimately references data files, record that dependency instead of claiming a failed purge.

Does a SELECT returning zero rows prove deletion? It proves only that the target predicate is absent from the current table snapshot. With deletion vectors, old values can still be represented in current Parquet files until rewritten, and older files can remain after the rewrite.

Does REORG TABLE ... APPLY (PURGE) alone remove the old files? No. Databricks states that the old files may still contain the soft-deleted data after REORG; VACUUM removes eligible old files later.

Does the default seven-day retention prove safety for every job? No. Seven days is the documented default and recommended minimum, but Databricks specifically warns operators to consider long-running jobs. Your supported workload window can require a longer retention period.

Does source cleanup always invalidate Unity Catalog shallow clones? No. Databricks documents the opposite for Unity Catalog shallow clones: tracked source/clone relationships expand valid-file protection, and source vacuum does not simply break the clone. Managed and external behaviors still differ, so the relationship must be evaluated in its actual table type and runtime context.