← Delta Optimization track

Databricks · Delta Optimization · Lesson 09

Deletion
Vectors.

Merge-on-read for Delta: a DELETE/UPDATE/MERGE marks rows as gone in a tiny side-file instead of rewriting the whole Parquet file — cheap writes, with a small read-time cost.

Write DBR 14.3 LTS+ Read DBR 12.2 LTS+ Property delta.enableDeletionVectors Verified Jun 2026 docs

The problem it solves

Parquet files are immutable — you can't edit rows in place. So by default, when you delete or update even one row, Delta uses copy-on-write: it reads the entire Parquet file holding that row, writes a brand-new file minus the row, and swaps it in. One changed row can rewrite hundreds of megabytes.

Deletion vectors flip this to merge-on-read. A DELETE/UPDATE/MERGE just marks the affected rows as gone (a "soft delete") in a small side-file and leaves the big data file alone. The cost moves to read time: every reader applies the deletion vector to hide the marked rows and return the correct, current state.

Analogy
Copy-on-write is photocopying a 300-page book to remove one sentence. A deletion vector is a sticky note on the cover — "skip line 12 on page 88." The book is untouched; anyone reading it just honors the note.

Use case
A GDPR/CDC pipeline doing many small DELETEs and MERGE upserts into a large customers table all day. With copy-on-write each delete rewrites whole files; with deletion vectors those writes are near-instant, and you periodically run OPTIMIZE or REORG … APPLY (PURGE) to fold the soft deletes into the files for good.

Why it matters

The one rule to remember Deletion vectors trade a cheap write for a small read-time cost (applying the vector). Data is soft-deleted until OPTIMIZE / auto compaction / REORG … APPLY (PURGE) rewrites the file — and only VACUUM (Lesson 10) actually removes the old files from storage.

See it work — copy-on-write vs merge-on-read

A 24-file Delta table. The 3 rows you'll delete live in 3 different files. Pick a mode, then click DELETE 3 rows and watch what the engine has to write.

Copy-on-write vs deletion vectors

Each cell is a ~256 MB Parquet file. The 3 target files are marked •.

DELETE FROM customers WHERE customer_id IN (42, 88, 130);
24 files on disk · 3 target files marked •
file untouched file rewritten (copy-on-write) unaffected file
0
files rewritten
0 MB
bytes written
Pick a mode and click DELETE 3 rows.

Simplified illustration. Real files vary in size; a deletion vector is a small bitmap-style side-file (kilobytes), not zero bytes. Copy-on-write rewrites whole files containing affected rows.

See it work — the deletion-vector lifecycle

Soft-delete is not the end of the story. Click each step to see what happens from the cheap write all the way through to bytes leaving storage.

Soft-delete → read → purge → vacuum

Click a step to expand it. Each one explains what the command touches.

1 DELETE marks rows (soft-delete) — file untouched
The DELETE/UPDATE/MERGE writes a tiny deletion vector recording which row positions are gone. The Parquet file is not rewritten — the write is proportional to the change, not the file size.
DELETE FROM catalog.schema.customers WHERE customer_id = 42;
2 Reads apply the deletion vector to hide marked rows
Every scan reads the data file and its deletion vector, then filters out the marked rows so you always see current state. This is the small read-time cost of merge-on-read — minimized by Photon predictive I/O.
SELECT * FROM catalog.schema.customers; -- DV applied, row 42 hidden
3 OPTIMIZE / REORG … APPLY (PURGE) physically rewrites files
To fold soft-deletes into the data, a file with a deletion vector is physically rewritten without the dead rows when OPTIMIZE runs, auto compaction touches it, or you run REORG TABLE … APPLY (PURGE) (rewrites all files with DV-recorded changes). On big tables, purgeMode = rows only touches files that actually have soft-deletes.
REORG TABLE catalog.schema.customers APPLY (PURGE);
4 VACUUM removes the now-unreferenced old files (Lesson 10)
Rewriting only replaces the file; the old file still sits in storage until VACUUM deletes it after the retention window (delta.deletedFileRetentionDuration, default 7 days). This is the step that actually reclaims space and makes a GDPR delete physical — covered in Lesson 10.
VACUUM catalog.schema.customers; -- removes files past the retention threshold
A DELETE is cheap, but the bytes don't leave storage until step 3 (rewrite) then step 4 (VACUUM).

Simplified illustration of the lifecycle. Exact triggers and timing depend on maintenance schedules, retention settings, and (on UC managed tables) predictive optimization.

The commands you'll actually use

Enable deletion vectors (SQL)

SQL-- At creation (Delta is the default format, so no USING DELTA)
CREATE TABLE catalog.schema.customers (
  customer_id BIGINT,
  email       STRING,
  region      STRING
)
TBLPROPERTIES ('delta.enableDeletionVectors' = true);   -- opt in to merge-on-read

-- Or on an existing table (normal tables only — NOT materialized views / streaming tables)
ALTER TABLE catalog.schema.customers
  SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);

Modify rows — writes a vector, not a rewrite (SQL)

SQL-- A small DELETE writes a tiny deletion vector; the Parquet file is untouched
DELETE FROM catalog.schema.customers WHERE customer_id = 42;

-- UPDATE and MERGE behave the same way: soft-delete old row + write new value
UPDATE catalog.schema.customers SET region = 'EU' WHERE customer_id = 99;

Enable + modify via the DeltaTable API (PySpark)

PYSPARKfrom delta.tables import DeltaTable

# ALTER works on normal tables (not MV / streaming tables)
spark.sql("""
  ALTER TABLE catalog.schema.customers
  SET TBLPROPERTIES ('delta.enableDeletionVectors' = true)
""")

dt = DeltaTable.forName(spark, "catalog.schema.customers")
dt.delete("customer_id = 42")                       # writes a deletion vector
dt.update("customer_id = 99", {"region": "'EU'"})   # soft-delete old + write new value

Physically apply the soft-deletes (SQL)

SQL-- Fold deletion vectors into data files (rewrite files that carry DV changes)
REORG TABLE catalog.schema.customers APPLY (PURGE);

-- Faster purge on a large table: only touch files that actually have soft-deletes
SET spark.databricks.delta.reorg.purgeMode = rows;   -- vs default 'all' (scan all footers)
REORG TABLE catalog.schema.customers APPLY (PURGE);

-- OPTIMIZE also physically applies DVs as it bin-packs / reclusters
OPTIMIZE catalog.schema.customers;

Then remove the old files (Lesson 10)

SQL-- Rewriting only replaces files; VACUUM removes the old ones after retention (default 7 days)
VACUUM catalog.schema.customers;

Downgrade the protocol (DBR 14.1+)

SQL-- Drop the feature so older clients can read again (normal tables; not MV/streaming)
ALTER TABLE catalog.schema.customers DROP FEATURE deletionVectors;

Copy-on-write vs merge-on-read

AspectCopy-on-write (default)Merge-on-read (deletion vectors)
What a DELETE/UPDATE doesRewrites the whole Parquet file(s)Writes a tiny deletion vector; file untouched
Write costProportional to file size (high)Proportional to the change (low)
Read costNone extraSmall — readers apply the vector
Storage reclaimedAt write time (old file unreferenced)After OPTIMIZE/REORG … PURGE then VACUUM
Best forRead-heavy tables with rare editsHigh-churn DML, many small deletes, CDC/GDPR, MERGE upserts
EnableDefault — nothing to dodelta.enableDeletionVectors = true (or via liquid clustering)
ProtocolUnchangedUpgraded (writer v7 / reader v3); old clients can't read

Runtime support

Deletion vectors upgrade the protocol, so old clients can't read the table. Know the version floors before enabling broadly.

CapabilityMinimum runtime
WRITE with all optimizationsDBR 14.3 LTS+
READ a DV-enabled tableDBR 12.2 LTS+
Row-level concurrency with DVsDBR 14.2+
Non-Photon DELETE / UPDATE / MERGE12.2 LTS+ / 14.1+ / 14.3 LTS+
Photon DELETE/UPDATE/MERGEall three from 12.2 LTS+
DROP FEATURE deletionVectors (downgrade)DBR 14.1+

Uses, edge cases & limitations

Uses — when to reach for it (and when not)

Edge cases interviewers probe

Limitations (honest constraints)

Quick gotchas