Support deletion of records that stop appearing in a source, scoped to a time window or partition

[lukewatters-airbyte]

Problem

Some API sources represent data as reports or snapshots rather than durable records — for example, a daily sales report where a cancelled order simply disappears from the report instead of being flagged as deleted. These sources have no "deleted" field and no way to signal that a record should be removed.

With Incremental sync using Append + Deduped, Airbyte only updates records that are still returned by the source. If a record stops appearing, the old copy stays in the destination table forever, even though it no longer reflects reality. This causes totals and aggregates built on the destination table to drift from the source of truth over time.

Today, the only way to remove stale records is a full Refresh, which reloads the entire stream from scratch. For destinations where many connections share the same table (e.g. one destination table serving many stores/tenants), a full Refresh on one connection isn't scoped correctly and reloading full history repeatedly across many connections is costly.

Proposed Solution

Add a way to clean up stale records that's scoped more narrowly than a full stream Refresh — for example:

When a specific time slice (like a day) is re-synced, replace all destination records for that slice instead of only updating the keys that came back.
Or, let Append + Deduped optionally remove keys that were previously seen in a window but weren't re-emitted on the latest sync of that window.

This would let deletions propagate correctly for sources that don't support CDC-style deletion markers, without requiring a full-table or full-stream reload.

Who benefits

Anyone syncing from a non-CDC API source where records can disappear over time rather than being explicitly marked as deleted — reports and snapshot-style APIs, POS/sales systems, or any source with soft deletes that are only visible by absence. Especially relevant to users with many connections writing into shared destination tables, where a full-table refresh isn't a safe option.

Additional context

A workaround today is to run a downstream, scheduled cleanup process (a filtered view, or a separate delete/merge job) outside of Airbyte, but this adds an extra maintenance step for what should be part of ingestion.

[Airbyte Support Bot (airbyte-support-bot)]

Hi lukewatters-airbyte, thank you for the detailed write-up and for thinking through the shared-destination-table implications!

You've described the gap clearly: for non-CDC "report/snapshot" sources where records disappear rather than being flagged as deleted, Incremental + Append + Deduped keeps the stale rows around, and a full stream Refresh is the only built-in cleanup — which isn't scoped well when many connections write into one shared table.

We're looking into this. We've escalated it to our team as a feature request for investigation so they can assess feasibility of a more narrowly-scoped cleanup (e.g. per time-slice/partition replacement, or optionally removing keys previously seen in a window that weren't re-emitted).

In the meantime, the downstream scheduled cleanup job you mentioned (a filtered view or a separate delete/merge) is the recommended workaround.

We've escalated this to our team for investigation: airbytehq/oncall#13102

---

Need more help? Join Airbyte Community Slack for peer support, or if you're a Cloud customer, open a support ticket referencing this URL.