Incremental MERGE Misses Deletes: The Warehouse Row That Never Left

The finance dashboard still shows order 1842. Ops cancelled it yesterday and deleted the row in Postgres. The warehouse job ran on time. MERGE reported a handful of updates. Nobody's pager fired. The cancelled order sits in marts_orders because a hard delete does not have a new updated_at. There is no row left to carry a watermark. The incremental extract never saw it.

That is the whole trap. Incremental loads copy changes that still exist. Deletes are changes that do not.

This is the warehouse side of CDC. Capture can be correct and the load can still keep the ghost. The merge is where the row is supposed to disappear. Most merges never get that instruction.

What the job actually does

The usual incremental pattern is a watermark on the source table, then an upsert:

-- staging extract
SELECT *
FROM oltp.orders
WHERE updated_at > :watermark;

-- warehouse load
MERGE INTO warehouse.orders AS t
USING staging.orders_delta AS s
   ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
    status      = s.status,
    total_cents = s.total_cents,
    updated_at  = s.updated_at
WHEN NOT MATCHED THEN INSERT (
    order_id, status, total_cents, updated_at
) VALUES (
    s.order_id, s.status, s.total_cents, s.updated_at
);

Inserts land. Updates land. A delete in OLTP is an absence. Absence does not appear in orders_delta. WHEN MATCHED never fires for a key that is not in the source. The warehouse row stays.

Point the watermark at updated_at on the entity table and this is guaranteed. Soft-deleted rows can still work if the application stamps updated_at and leaves the row. Hard deletes cannot.

The delete signal has to be a row

You cannot infer a delete from a missing key in a delta. You can infer it from a missing key in a full snapshot, or from an explicit tombstone. Pick one. Mix them and you will delete the wrong keys.

Soft delete on the entity. The OLTP row stays. A column such as is_deleted or deleted_at flips, and updated_at moves. The incremental extract still returns the key. The merge can DELETE (or mark inactive) on WHEN MATCHED AND s.is_deleted. This is the cheapest warehouse change. It requires the application to stop issuing DELETE FROM orders WHERE id = ….

CDC tombstone. Log-based CDC emits a delete event with the key, even when the source row is gone. Land those events in a change table:

order_id | op | status | total_cents | source_lsn | ts_ms
1842     | d  | NULL   | NULL        | 0x1A2B3C   | ...

The extract reads the change table, not oltp.orders. The merge matches on order_id and deletes when op = 'd'. Replays are safe: deleting a key that is already gone is a no-op.

Full-extract anti-join. Nightly, load every current key. Anything in the warehouse that is not in that set is gone. This is the only honest answer when you cannot change OLTP and you do not have CDC. It is also the one that does not belong on a five-minute job.

Signal	What the delta contains	Safe on an incremental job?
updated_at only	Inserts and updates	No deletes
Soft-delete flag	Inserts, updates, and the deleted keys	Yes
CDC op = 'd'	Inserts, updates, tombstones	Yes
Full snapshot of keys	Every live key	Yes, and only then

WHEN NOT MATCHED BY SOURCE will empty the table

Snowflake, BigQuery, and Spark SQL all offer a clause that looks like it solves deletes:

MERGE INTO warehouse.orders AS t
USING staging.orders_delta AS s
   ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...
WHEN NOT MATCHED BY SOURCE THEN DELETE;

NOT MATCHED BY SOURCE means "keys in the target that are not in this USING set." If USING is a five-minute delta of 400 rows, the other several million warehouse rows are not in that set. The merge deletes them. The job is green. The dashboard is empty.

Use that clause only when USING is a complete picture of the keys that should exist — a full snapshot, or a snapshot restricted by a partition you also restrict on the target (one date, one tenant, and the same predicate on both sides). An incremental change feed is not that picture.

The incremental form names the delete on the matched tombstone, and never asks the target to forget unmatched keys:

MERGE INTO warehouse.orders AS t
USING staging.orders_delta AS s
   ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET
    status      = s.status,
    total_cents = s.total_cents,
    updated_at  = s.updated_at
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT (
    order_id, status, total_cents, updated_at
) VALUES (
    s.order_id, s.status, s.total_cents, s.updated_at
);

Same shape with a soft-delete flag: WHEN MATCHED AND s.is_deleted THEN DELETE, and skip the insert when is_deleted is already true on first sight. A row that was created and deleted between watermarks should not appear in the warehouse at all.

Watermark the change stream, not the entity

If the landing table is CDC, the watermark belongs on the change table's position (lsn, file/pos, Kafka offset, ts_ms with a tie-breaker), not on orders.updated_at. A delete has no updated_at on the entity. It has a position in the log.

Keep the extract inclusive on the low side and advance the watermark only after the merge commits. At-least-once is the contract. The merge must therefore be idempotent: a replayed update overwrites the same columns; a replayed delete leaves the key absent.

When one key moves twice in the same window — update, then delete — collapse to the latest event per key before the merge, ordered by log position. Merge is per-key matching, not a replay of the whole sequence. If both events reach USING, which row wins is an implementation detail you do not want. QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY source_lsn DESC) = 1 (or the Spark/BigQuery equivalent) is the whole pre-step.

dbt will not invent the tombstone

dbt's incremental merge strategy is the SQL above without the delete branch. unique_key is the match. New keys insert. Seen keys update. There is no op = 'd' unless you write it.

A separate incremental model that reads the CDC landing table, filters op = 'd', and delete+inserts (or a post-hook DELETE … WHERE order_id IN (SELECT …)) is the usual split: one model applies upserts, one applies deletes, both keyed the same way. Do not hide the delete in a pre-hook that nobody reviews. The CI pipeline should run a uniqueness test and a "deleted OLTP keys are absent downstream" test on a fixture that includes a tombstone.

If the source really is a full snapshot, delete+insert on the whole table (or on a partition) is clearer than a merge with NOT MATCHED BY SOURCE. The name matches the behavior.

What this is not for

Append-only facts. Page views, telemetry, immutable events — there is no entity to retract. A "delete" there is a compensating event you insert, not a key you remove. Do not MERGE those.

Type-2 history. If marts_orders is slowly changing and you keep valid_from / valid_to, a source delete closes the current version. It does not DELETE the historical rows. Closing a version is an update. Removing the grain is a different table.

A missed extract you plan to paper over with NOT MATCHED BY SOURCE. That clause is a snapshot operator. Put it on a delta and you will delete live keys that simply did not change this window.

Wire the delete as a row — a flag, a CDC tombstone, or a full key list. Point the watermark at the stream that contains that row. Teach the merge the WHEN MATCHED AND deleted branch. Then cancelled order 1842 leaves the dashboard the same run it left Postgres.