Progress
0% View detailed progress dashboard
Advanced

Deletes That Stay Deleted

A DELETE event reaching the warehouse is not the same as the data being gone. Trace one deleted row through the topic, the sink, its history and the backups, and learn which setting governs each copy.

Three kinds of delete What to do Monday

The question behind this page

A user asks you to delete their data. The row is removed from the source database, the connector emits a delete event, and the event reaches the warehouse. Is the data gone? Usually not. The delete event changed one copy of the data. Every other copy lives under its own setting and its own schedule, and some of those schedules are measured in weeks.

The same mistake runs the other way too. A sink that handles a delete by removing the row can bring the row back later, because delivery is at-least-once and an older change can arrive after a newer one. Both problems have one answer: a delete is an event with a position, applied by an idempotent sink, and every other copy has a known expiry.

This is not legal advice. This page explains the technical side: where data can still exist and which setting governs it. Whether a particular copy must be erased, by when, and whether an exception applies is a legal decision for your lawyer or data protection officer. Nothing here promises that following it satisfies any regulation.

It extends Deletion requests and an append-only log and does not repeat its three strategies (never carry the identifier in the clear, compaction plus tombstones, crypto-shredding). It assumes the site’s standing rule: delivery is at-least-once, and the sink is idempotent, keyed on the primary key and ordered by the source log position (LSN or SCN), never by a timestamp or a Kafka offset. End-to-end exactly-once across independent systems is not achievable; see Exactly-Once Semantics.

Three things people call a delete

A CDC pipeline has three different objects that all get called “the delete”. They are made by different components, for different readers, and they do different jobs.

The three kinds of delete in a CDC pipeline
Source DELETE event Kafka tombstone Sink delete marker
What it is An ordinary change event with op: "d", the row as it was (the before image) and the source block. On PostgreSQL, with the default replica identity on a table that has a primary key, the before image carries only the key columns; REPLICA IDENTITY FULL adds the rest of the row, and other replica identity settings differ, so check yours. On MySQL, binlog_row_image controls how much of the row is written (FULL, MINIMAL, NOBLOB); the full row needs binlog_row_image=FULL. A Kafka record with the row’s key and a null value. A row in the target table with a deleted flag and the position of the delete.
Made by The connector, from the database log. The connector, after the delete event. Debezium’s PostgreSQL connector does this when tombstones.on.delete is true, which is the default; check the setting for your connector. Your sink’s upsert or MERGE.
What it is for Telling consumers that the row was deleted, and when in the log. Telling Kafka’s log cleaner that it may drop the key’s older records on a compacted topic, and later the tombstone itself. Hiding the row from readers and stopping an older, replayed change from re-creating it.
Carries a position? Yes, in the source block. No. It has a key and no value; there is nothing in it to compare. Yes, by design. That is the point of keeping it.

Sources: Kafka design documentation, “Log Compaction Basics”; Debezium PostgreSQL connector, “Tombstone events” and tombstones.on.delete. Glossary: tombstone, compaction.

Why a tombstone alone does not delete a warehouse row

  • It is addressed to Kafka, not to your table. Kafka’s documentation describes a key with a null payload as a delete from the log. Whether anything downstream deletes a row on seeing it is up to the sink.
  • Sinks differ and often default to not deleting. Debezium’s own JDBC sink, by default, does not delete the row in the target when it consumes a delete or a tombstone; you must set delete.enabled=true and give it a primary key mapping. Other connectors differ. Read yours.
  • A flattened stream may carry only the tombstone. The Debezium event-flattening transform drops the delete event and keeps the tombstone by default. The sink then sees a key and nothing else: no operation code, no position, no before image. To keep a delete as a normal record, set the transform’s delete handling to rewrite (or rewrite-with-tombstone) and add the position with add.fields (for example op,table,lsn). The option name has changed between Debezium versions; check yours.
  • Acting on the tombstone is still a physical delete. A sink that removes the row when the tombstone arrives has the problem in the next section.
  • None of this touches earlier events. The inserts and updates that carried the person’s values are separate records in the topic, and in every copy made from it.

Resurrection: the late, older update

Delivery is at-least-once, so an event can arrive twice and an older one can arrive after a newer one. Common ways that happens: a connector restart re-emits changes from before the last committed offset, a consumer is rewound, a failed update waits in a dead-letter queue and is re-driven later, or a second pipeline writes the same table. The row with id = 7 sees these changes, at these source log positions:

  1. Position 100: insert, email a@example.com.
  2. Position 140: update, email b@example.com.
  3. Position 180: delete.

Then the update at 140 is delivered again, after the delete. Two sinks handle the same arrivals:

State of one key after each arrival, with a physical delete and with a delete marker
Arrival Sink A: removes the row on delete Sink B: delete marker with position
1. insert at 100 (7, a@, 100) (7, a@, deleted = false, 100)
2. update at 140 (7, b@, 140) (7, b@, deleted = false, 140)
3. delete at 180 row removed (7, NULL, deleted = true, 180)
4. update at 140 again the row is back: (7, b@, 140), with the email that was supposed to be erased unchanged; the guard asks whether 180 < 140 and it is not
5. delete at 180 again row removed again unchanged; 180 < 180 is false, so applying it twice is harmless

Sink A removed the stored position along with the row, so the late update found nothing to compare against, took the insert path and re-created the row. Sink B kept the delete as a row that carries its position and nulls the payload, so the same guard that handles every other replay handles this one. This is the rule from Making Sinks Bulletproof applied to deletes. A tombstone cannot do this job: it has no position.

Try it in the playground: Delete followed by a late update starts with Sink A’s behaviour (the order comes back) and lets you switch on a position guard and delete markers. It opens a separate tool that contacts a third-party database (see the privacy note). 

CREATE TABLE customers (
  id         BIGINT  PRIMARY KEY,
  email      TEXT,                          -- NULL on a delete: erase the payload with the marker
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
  source_lsn BIGINT  NOT NULL               -- position of the change that wrote this row
);

-- Apply ONE change (a batch is first reduced to the newest change per key,
-- as in the merge cookbook). A delete is the same statement with
-- email = NULL and is_deleted = TRUE; it also inserts a marker for a key
-- the table has never seen, so a late older insert cannot create the row.
INSERT INTO customers (id, email, is_deleted, source_lsn)
VALUES (7, 'b@example.com', FALSE, 140)
ON CONFLICT (id) DO UPDATE
SET email      = EXCLUDED.email,
    is_deleted = EXCLUDED.is_deleted,
    source_lsn = EXCLUDED.source_lsn
WHERE customers.source_lsn < EXCLUDED.source_lsn;

-- Readers never see markers.
CREATE VIEW customers_current AS
SELECT id, email FROM customers WHERE NOT is_deleted;

Tested as written against SQLite in this site’s unit tests (the five arrivals above, and the case where the delete arrives first). Not run against PostgreSQL, whose ON CONFLICT ... DO UPDATE ... WHERE form it follows. The position is assumed to be one number that is unique per change for a key; if it can repeat, compare a (position, ordinal) pair as described on the page linked above. The full per-engine versions are in the merge cookbook, and the pattern is explained on Materialization 101.

Trace one deleted row

Take one person’s row and list every place a copy can exist after the delete event has been applied in the sink. All values below were read from the vendors’ documentation on 2026-10-09 (versions in What this page did not test); defaults change, so check the number for your version before you rely on it.

In the stream

Copies of a deleted row in the Kafka stream and what governs them
Copy Does the delete event touch it? What governs how long it lives
Earlier change events in the topic (the values) No. They are separate records. cleanup.policy (default delete) and, for that policy, retention.ms (default 7 days). With compact, older records for the key are removed when the log cleaner runs; the newest segment is never compacted, and min.compaction.lag.ms and max.compaction.lag.ms bound when a record becomes eligible. Compaction is background work, not a deadline.
The delete event itself It is the delete event. It holds the before image, and what that contains depends on the source. On PostgreSQL, with the default replica identity on a table that has a primary key, that is only the key columns; REPLICA IDENTITY FULL adds the rest of the row, and other replica identity settings differ, so check yours. On MySQL, binlog_row_image controls how much of the row is written (FULL, MINIMAL, NOBLOB); the full row needs binlog_row_image=FULL. With tombstones.on.delete=false only the delete event is emitted, and Debezium’s documentation says that Kafka can remove all messages for a key only when the last one has a null value. On a compacted topic that leaves the delete event, with whatever before image it carries (on PostgreSQL, with the default replica identity on a table that has a primary key, only the key columns, while REPLICA IDENTITY FULL adds the rest of the row and other replica identity settings differ; on MySQL binlog_row_image controls how much of the row is written, and the full row needs binlog_row_image=FULL), as the key’s last record.
The tombstone It is the delete signal for the log. delete.retention.ms (default 1 day). A consumer that is more than this far behind can miss it. See below.
Dead-letter topic No. An event that failed earlier may be sitting here with the values. A normal topic with its own retention.ms and cleanup.policy, named by errors.deadletterqueue.topic.name on a sink connector (blank by default). With errors.deadletterqueue.context.headers.enable it also carries error context headers. See DLQ triage and the glossary.
Archive or raw staging table No. Append-only copies keep every version. Whatever lifecycle rule you set. The lessons here advise keeping raw envelopes for N days so you can reprocess (late arrivals), and some pipelines archive the whole stream on purpose (use cases). That N is also your erasure delay for this copy.

Sources: Apache Kafka topic configuration and design (Log Compaction) pages; Kafka Connect configuration; Debezium PostgreSQL connector.

In the sink and after it

Copies of a deleted row in the sink, its history and backups, and what governs them
Copy What the delete does to it What governs how long it lives
Current-state row With a marker: the payload is nulled and the row stays as (key, deleted, position). Your merge, and your purge job (below).
Delta Lake table history DELETE removes the data from the latest version only. Without deletion vectors the old files stay until VACUUM. With deletion vectors enabled, DELETE does not rewrite the Parquet file, so the deleted values stay in the live file until REORG TABLE ... APPLY (PURGE) rewrites it. After that, VACUUM removes the old files once they are past delta.deletedFileRetentionDuration. VACUUM is not triggered automatically in open-source Delta; it removes unreferenced files older than delta.deletedFileRetentionDuration (default 7 days). Time travel is limited by delta.logRetentionDuration (default 30 days) and by whether the data files still exist. Change data feed files in _change_data follow the same retention and go with VACUUM.
Iceberg table snapshots A delete is a new snapshot. Old snapshots, and the data files they reference, stay available for time travel. Snapshots accumulate until the expireSnapshots operation runs. Expiry uses history.expire.max-snapshot-age-ms (default 5 days) and history.expire.min-snapshots-to-keep (default 1); data files are removed only when no remaining snapshot references them.
Iceberg delete files (format v2 and later) With merge-on-read (format v2 and later) a row-level delete writes a delete file (position or equality) and does not rewrite the data file. The default copy-on-write rewrites the affected data files. Format v3 uses deletion vectors for position deletes. write.delete.mode, write.update.mode and write.merge.mode default to copy-on-write; merge-on-read writes delete files instead. The deleted values stay in the original data file until it is rewritten (compaction) and the older snapshots are expired.
BigQuery table with CDC A CDC DELETE counts as applied only once upsert_stream_apply_watermark has passed the time it was streamed. BigQuery keeps deletes for a 2-day retention window to handle out-of-order deletes. If CDC operations fail, or the reservation has too little capacity, the documentation warns that you might unintentionally retain data you meant to delete. After that, time travel applies (7 days by default, 2 to 7 configurable). BigQuery documents a fixed 7-day fail-safe for table deletion or replacement and partition deletion. It does not say whether row-level CDC deletes enter fail-safe.
Snowflake table Time Travel preserves the pre-delete state of the data. DATA_RETENTION_TIME_IN_DAYS: 1 day by default, and up to 90 days for permanent objects on Enterprise Edition or higher. Then a non-configurable 7-day Fail-safe that only Snowflake can recover from.
Backups, replicas, exports, caches, search indexes Not touched. A restore brings the old rows back. Their own schedules. See the erasure section.

Sources: Delta Lake batch, update and utility documentation and change data feed documentation; Apache Iceberg maintenance, configuration and specification pages; BigQuery change data capture and time travel documentation; Snowflake Time Travel and Fail-safe documentation. URLs in Further resources.

Kafka: what a consumer that falls behind misses

Kafka’s documentation is specific. A consumer that reads from the start sees the final state of every key, and sees the delete markers too, provided it reaches the head of the log in less than delete.retention.ms. Because the cleaner removes tombstones while consumers read, a consumer that lags by more than that can miss them. The setting also bounds how long a read that starts at offset 0 has to finish to get a valid snapshot of the final state.

Two consequences for a CDC sink. First, a sink that already holds the row and then falls behind by more than a day (the default) can come back to a topic where the tombstone is gone and the log no longer says the key was deleted, so it keeps the row. Second, a sink bootstrapped by reading a compacted topic needs to finish within the window or it may end up with a state that never saw the delete. Alert when consumer lag approaches delete.retention.ms for any compacted topic you depend on for deletes. See observability for lag signals.

Delta Lake: two clocks, and VACUUM safety

The 30-day figure is the time-travel history. The bytes leave storage on a different clock: the 7-day deleted-file retention, and only when someone runs VACUUM. With deletion vectors enabled there is a step before that: DELETE only marks the rows, and REORG TABLE ... APPLY (PURGE) is what rewrites the files that hold them (OPTIMIZE and other DML have no strict guarantee of doing so). The purge creates a new table version, and the time it completes is the timestamp the VACUUM retention threshold counts from. I did not verify the default of the delta.enableDeletionVectors table property for your version; check it. Delta recommends keeping the retention interval at least 7 days because shorter intervals can break concurrent readers or writers, and has a safety check that blocks shorter intervals unless you turn it off. So you cannot speed up erasure without taking a reader-safety risk; that trade belongs in your written decision. A managed platform may schedule maintenance for you; check yours rather than assuming either way.

Purging markers safely

Markers cost storage and they hold a key (and a position) for a person who asked to be forgotten, so they need a retention policy. The wrong moment to remove one is when “the row has been deleted for a while”. A marker exists to defeat older events. It can go when both of these are true:

  1. The delete has propagated. Every consumer and replica of the table has applied or read the marker: downstream incremental models, marts, caches and search indexes. Purge it first and a downstream job that has not seen it yet never learns of the delete.
  2. Nothing that can be replayed holds an older event for that key. The oldest source position that any replay source can still deliver must be newer than the marker. Replay sources are the topic (up to its retention, or for a compacted topic its un-compacted history), the dead-letter queue, the archive, the raw staging table, and any re-snapshot or restored backup you might load. Replays carry the position they had the first time, so this is a comparison of positions, not of clock times. 
-- Physical delete (purge): the only DELETE in this design.
-- :purge_below is the LOWEST source position that any replay source (topic,
-- dead-letter queue, archive, staging, planned re-snapshot) can still deliver,
-- and it is only valid if every consumer of the table is already past the marker.
-- The comparison is strict: a marker AT the bound stays.
DELETE FROM customers
WHERE is_deleted AND source_lsn < :purge_below;

This is the same purge as in the merge cookbook. Computing :purge_below is the hard part: read the first retained record of each partition of each replay source and take the lowest source position among them, within one source log lineage (positions restart after a failover that loses the log tail or a restore; see the lineage note on Making Sinks Bulletproof). If you cannot get positions out of a replay source, bound by the age of the marker against the longest replay window plus a margin, using load time, not commit time.

Vendors that keep delete markers for you chose a window, not a proof. BigQuery holds CDC deletes for 2 days, and Databricks keeps a deleted row as a tombstone for 2 days by default (pipelines.cdc.tombstoneGCThresholdInSeconds), telling you to set it above the longest expected delay between an event arriving and the pipeline running. Treat those as the same question your own purge answers: how late can an older event be?

Erasure requests: the technical question

Not legal advice. Read the regulation yourself and ask a lawyer. This section only separates what the law text says from the engineering facts, so that you can bring the right facts to that conversation.

What the official text says

The text below is from Regulation (EU) 2016/679 as published on EUR-Lex (links in Further resources).

  • Article 17(1) gives the data subject the right to obtain erasure of personal data from the controller “without undue delay”, and obliges the controller to erase without undue delay, where one of six listed grounds applies (for example the data are no longer necessary for the purposes they were collected for, or the data subject withdraws consent and there is no other legal ground). “Without undue delay” is not a number of days.
  • Article 17(3) says paragraphs 1 and 2 do not apply to the extent that processing is necessary for: (a) exercising freedom of expression and information; (b) compliance with a legal obligation that requires the processing, or a task in the public interest or in the exercise of official authority; (c) public health reasons; (d) archiving in the public interest, scientific or historical research or statistical purposes, where erasure would likely make the objectives impossible or seriously impair them; or (e) the establishment, exercise or defence of legal claims.
  • Article 19 requires the controller to communicate the erasure to each recipient to whom the data were disclosed, “unless this proves impossible or involves disproportionate effort”. Downstream systems and other organisations that received the data may be recipients; whether a given consumer counts is for your lawyer. Article 17(2) adds that a controller that made the data public and must erase it has to take reasonable steps to inform other controllers processing it.
  • Article 12(3) requires the controller to provide information on action taken on the request without undue delay and in any event within one month of receipt, extendable by two further months where necessary. That is the deadline for telling the person what was done. It is not a statement about how long each copy may take to expire.

The 17(3) exceptions apply “to the extent that” processing is necessary, so they can cover some data and not other data. In pipeline terms that means a decision per table or per column, not per person: the invoice rows you must keep and the marketing profile you must erase can sit in the same topic. Your pipeline needs to be able to say which copy it kept and on what stated basis. Who decides, and what basis is acceptable, is for your lawyer.

The text of the regulation, as published, does not use the words “backup”, “snapshot” or “time travel”, so it does not answer the engineering questions below. The UK Information Commissioner’s Office, writing about the UK’s own version of the regulation, has published guidance that, where a valid request is received and no exemption applies, you “will have to take steps to ensure erasure from backup systems as well as live systems”; that data in backups should be put “beyond use” until overwritten on an established schedule, that individuals should be told clearly what will happen to backup copies, and that the outcome is context-specific. That is one regulator’s published view, not EU law, and others may differ.

The technical questions

For each row of the trace tables above, answer these in writing:

  1. Does a copy of this person’s data still exist there after the delete is applied?
  2. Until when, and which setting decides that date?
  3. Who or what can still read it (time-travel queries, a restore, a replay)?
  4. What would remove it sooner, and what does that cost (for Delta, a shorter retention has a reader-safety cost)?
  5. Is any exception under 17(3) being relied on for this copy, who decided that, and where is it recorded?

Backups and retention copies

Backups, replicas, exports and archives are separate stores with their own schedules. A delete event does not remove anything from them, and neither does applying a marker in the sink. Time-travel windows and fail-safe periods behave like backups: you cannot edit them, you can only wait for them to expire (or shorten them going forward), and a table recovered from them contains the row again.

The engineering consequence is a restore rule. After you restore a table from a backup or a time-travel version, the table is older than the deletes you have since applied, and the topic may no longer hold those deletes. Keep a record of erasures (a surrogate key and the position and date of the delete, kept for at least as long as your oldest restorable backup) and re-apply it after any restore, before readers see the table. Whether and how long you may keep that record is again for your lawyer.

What you can do to make the answers shorter

  • Do not put the identifier in the clear in the first place, or encrypt per subject and destroy the key (the strategies on the Security page). That works on every copy at once, including the ones you cannot edit.
  • Choose retention on purpose for the history of tables that hold personal data (time travel, snapshot age, fail-safe where configurable), and schedule the jobs that make expiry real (REORG TABLE ... APPLY (PURGE) then VACUUM for Delta tables with deletion vectors; expireSnapshots for Iceberg).
  • Null the payload columns in the same statement that writes the marker, and keep only the key and the position.
  • Give the dead-letter topic, the archive and the staging tables a retention that you have written down, rather than the default.

What to do Monday

  1. Pick one table that holds personal data and fill in the two trace tables for it, with the actual values from your cluster and warehouse, not the defaults on this page.
  2. Read your connector config: is tombstones.on.delete what you intend, and if you flatten events, does the sink still receive the delete with its position (rewrite plus add.fields) or only a bare tombstone?
  3. Find out what your sink does on a tombstone and on a delete event (for the Debezium JDBC sink, delete.enabled). If it removes the row, replay the five arrivals from the resurrection table against it.
  4. Make the sink keep delete markers that carry a position, and put a view in front so readers filter them. Null the payload on delete.
  5. Write down your replay window: topic retention, dead-letter retention, archive and staging retention, and the oldest backup you could restore. Compute :purge_below from it and schedule the purge.
  6. Add an alert for consumer lag against delete.retention.ms on every compacted topic that carries deletes.
  7. For Delta, Iceberg, BigQuery or Snowflake, check that the maintenance that makes expiry real is actually scheduled (VACUUM, plus REORG TABLE ... APPLY (PURGE) if deletion vectors are on; expireSnapshots), and record the retention numbers.
  8. Set the retention of the dead-letter topic and the raw archive on purpose. If something is kept longer than a request deadline allows, write down why.
  9. Write the restore rule: after any restore, re-apply the erasure record before the table is readable.
  10. Take the filled-in tables to your lawyer or data protection officer and ask what is acceptable for each copy. Record which Article 17(3) exceptions, if any, they accept, and by whom.

What this page did not test

  • No vendor service was run. Every figure in the trace tables comes from the documentation pages listed below, read on 2026-10-09: Apache Kafka documentation at kafka.apache.org/43/, Debezium “stable” documentation, Delta Lake documentation at docs.delta.io, Apache Iceberg 1.12.0 documentation, BigQuery and Snowflake documentation, and the Databricks pipeline reference. Check your version.
  • The SQL on this page was executed against SQLite only. It is written in the PostgreSQL ON CONFLICT form but was not run on PostgreSQL, and not on any warehouse.
  • Not verified from vendor documentation: whether a managed Delta platform runs VACUUM for you; whether BigQuery’s fail-safe covers row-level CDC deletes (its page lists table and partition deletion and replacement); how an Iceberg engine you use applies delete files during compaction.
  • The legal text is quoted from EUR-Lex. How a regulator or court would read it for any pipeline is not covered, and the UK guidance is one regulator’s view of a different instrument.
  • The Playground’s Retention & Erasure scenario exists, but it does not model a late older update after a delete or any storage-level retention, so this page does not link to it as a demo.

Deletes Knowledge Check

Test your understanding of delete markers, tombstones and where deleted data can still live.

Q1

A sink physically deletes the row when the delete event arrives. A replayed update with an older log position then arrives. What happens?

Q2

What does a Kafka tombstone (a record with a key and a null value) carry that a sink could use to order it against other changes for the same key?

Q3

A Delta table has default retention settings and deletion vectors are off. You run DELETE for one person's rows. When do the bytes leave storage?

Q4

When is it safe to physically remove a delete marker from the sink?

0/4 correct

Further resources

On this site

Primary sources (read 2026-10-09)

Progress 0% No progress yet
Progress is stored locally in this browser.