Absence-As-Delete Sync

A nightly model returns 4,000 rows instead of 1.2 million because an upstream column was renamed and a join predicate stopped matching. Every test on the model is a not-null check on columns that survived, so it passes. The sync runs on its own schedule, reads the table, and clears a lifecycle field on more than a million accounts in the CRM. Nothing errors. The sync tool reports a normal busy night.

Absence-as-delete is the semantics, not the bug. The sync's contract is "make the destination match the query result", so a row that is not in the result is an instruction to remove it. This is rsync --delete pointed at a system of record. It is a legitimate and useful mode; the anti-pattern is running it without a guard on how much the source may shrink.

Why it matters

This is one of the few data-platform defects that damages a system the data team does not own and cannot restore. Analytical culture treats a rerun as free, because the worst case is a stale table for an hour. Operational culture treats a write as final. Reverse ETL joins the two without translating between them, and absence-as-delete is where the mismatch becomes expensive.

The blast radius is also the wrong shape. A partial failure would be survivable; what absence-as-delete produces is a complete, successful, well-formed application of a wrong intent across the entire dataset in a single run.

Implementation patterns

• A volume guard on the sync. Refuse to run when the source row count deviates more than a set margin, commonly around 10%, from the trailing 7-day median, and page instead of proceeding. One configuration line, and it catches the whole class rather than the instance.
• Gate on the model's published state, not on a clock. The sync consumes a contract carrying freshness and completeness, so a failed or stale model cannot be read at all.
• Separate the fields. Write only into destination fields the operational system never writes itself, so the worst case is stale advice rather than erased records.
• Prefer explicit deletes. Where removal is genuinely required, emit a delete event or a deleted flag from the model, so removal is an instruction the model intended rather than an inference from absence.
• Volume and distribution tests on the model itself, asserting row counts and key-column distributions, not only nullability. Nullability tests pass on a correct query returning 0.3% of its rows.
• Keep a reversal path: retain the previous sync's payload for long enough to push it back, and rehearse doing so.

Industry example

The pattern is vendor-independent and every reverse-ETL product ships the mode, usually as the default for the simplest configuration. Its closest relative is the family of incidents, well represented in public postmortems since about 2015, where administrative tooling applied a correct operation to an incorrect set of identifiers and deleted customer estates. The structure is identical: a well-formed instruction executed successfully against the wrong population, with restoration measured in days or weeks because nobody had designed for undoing it at that granularity.

Failure scenarios

• Silent mass clearance after an upstream schema change, discovered by the business rather than by the platform.
• Unrecoverable manual edits. Destination-side edits made since the last sync are lost with the synced values, and no warehouse snapshot contains them.
• Downstream automation fires on the cleared state - suppression rules, campaign triggers, entitlement checks - so the damage propagates beyond the field that was cleared.
• Slow-motion version: the source shrinks by 3% a night for a fortnight, each run passing any naive guard, and the destination erodes without a single alarming day.

Trade-offs

When not to use it

Absence-as-delete is correct when the warehouse is genuinely the only writer of the data. A territory mapping, a product catalogue push or a feature-flag audience is owned end to end by the model, and adding delta tracking there is complexity bought for nothing. The test is ownership rather than risk appetite: if any human or system on the destination side can edit the field, the sync must never clear it. The second condition is size - on a dataset of a few thousand rows the volume guard is cheap and a full comparison is instant, so the mode carries its own safety.

Interview question

Q: Marketing wants warehouse segments pushed into the campaign tool hourly, and the vendor's default configuration is a full sync. What do you require before agreeing, and what do you refuse outright?

What a strong answer covers: requiring a volume guard and a gate on the model's freshness state before the first sync runs; requiring named ownership of every destination field and refusing any field the tool's users also edit; refusing to put anything in the path that triggers money movement or customer-facing entitlements; naming the monitoring that would detect a wrong-but-valid result, which is row count against a trailing median rather than job success; and stating the rollback plan in terms the marketing team will accept before they need it.

Quick check

Quiz: A reverse-ETL sync reports 1.2 million records synced and the business reports a million blank fields. How can both be true? - Full-sync mode counts clearances as successful writes, so a mass deletion and a normal busy night look identical in the tool's own telemetry.

Flashcard: What single guard stops a wrong-but-valid model from becoming a mass deletion downstream? - A source row-count deviation limit against the trailing median, checked before the sync runs, which blocks the class of "correct query returning far fewer rows" rather than one instance of it.