In a CDC feed, what should a sink do with a delete event?

## What a delete event contains When a row is deleted at the source, a log-based capture emits an event whose operation is a delete, carrying the row's key and — as far as the source log recorded it — the values the row held before it disappeared. There is no after-image: nothing exists afterwards to describe. That asymmetry is the whole reason delete handling gets its own design decision, because every other operation hands the sink a complete picture of what the row should now look like, and this one does not. How much of the before-image you get depends on what the source log was configured to record. Some engines log the full previous row, others only the key. A sink that needs the old column values — to close out a versioned history row, for instance — has to check that the source is configured to provide them rather than assuming. ## Option 1: physical delete ```sql DELETE FROM customers WHERE id = :id; ``` The target then matches the source row-for-row, which is what an operational replica or a cache wants. It is idempotent for free: applying it again affects zero rows. The costs are that history is destroyed, that the target cannot distinguish a deleted row from one that never arrived, and — the operational sting — that the sink forgets any per-row bookkeeping it kept, such as the source log position reached for that key. That matters because a replayed pre-delete update will then find no row, take the insert path, and resurrect a row the source deleted. ## Option 2: soft delete ```sql UPDATE customers SET is_deleted = TRUE, deleted_at = :ts WHERE id = :id; ``` This is the default in analytical targets. Nothing is destroyed, so a report over last quarter still sees the customer who churned yesterday; the key survives, so any position guard on the row keeps working; and re-inserting the same key later is a normal upsert that clears the flag. The price is discipline: every consumer must filter `is_deleted = FALSE`, and the day someone forgets, deleted rows silently join a total. The standard mitigation is to expose a view that applies the filter and to point consumers at the view rather than the table. ## Option 3: append to a history table If the target is an append-only record of changes, the delete is just another versioned row with an operation column set to delete. Nothing is overwritten and downstream models reconstruct current state as "the newest version per key that is not a delete". This is the most faithful representation and the most expensive to query. ## Tombstones Separately from the delete event itself, some CDC connectors emit an additional record that carries the row's key and a **null payload**. This is a tombstone, and it exists for keyed stores that retain only the latest value per key: seeing a null value for the key tells such a store the key may be removed entirely rather than kept forever with its last known value. Two consequences for a sink author: the sink must recognise a null payload as "this key is gone", not decode it as a row whose every column is null; and tombstone emission is usually optional, so a downstream keyed store that relies on it will grow without bound if it is turned off. ```text op=delete key={id:7} before={id:7,email:"[email protected]"} after=null (optional follow-up) key={id:7} value=null <- tombstone ``` ## The failure of ignoring deletes The most common mistake is a loader that only handles inserts and updates, either because it was written against a source where deletes were thought impossible, or because the target's merge logic simply had no delete branch. The symptom is quiet and slow: the target keeps every row it has ever seen, row counts drift above the source's, and aggregates — active customers, open orders, inventory value — are inflated by rows the business deleted months ago. It is usually caught by a reconciliation job that compares row counts and checksums per key range against the source, which is worth having regardless. ## Hard deletes at the source versus in your pipeline A related design conversation: many teams stop the source from hard-deleting at all, using a status column instead, so the CDC feed only ever sees updates. That removes this whole class of problems but is a source-application decision you may not control. If deletes do reach your pipeline, handle them explicitly and write the test that proves it — a delete at the source, then an assertion that the target no longer counts the row. ## What an interviewer wants to hear That delete events carry a before-image and no after-image; that the sink chooses between physical and soft delete for reasons, not by accident; that soft delete needs a filter everywhere; and that ignoring deletes inflates the target silently. Mentioning tombstones and what they are for is the differentiator at this level.