I want to keep incremental refresh while deleting ...
# ask-ai
y
I want to keep incremental refresh while deleting rows that are deleted in the source. Is that possible?
k
Short answer: yes, but only in certain ways; standard incremental alone does not delete rows in the destination. ### 1. What standard incremental does For normal incremental modes (Incremental Append, Incremental Append + Dedupe): - Airbyte queries “new or updated” records based on a cursor field. - Deleted rows are never sent, so they are not removed from the destination. [[Inc. sync tutorial](https://airbyte.com/tutorials/incremental-data-synchronization#step-7)] - This is explicitly a limitation: “records in the warehouse destination will never be deleted or mutated.” [[Inc. sync tutorial](https://airbyte.com/tutorials/incremental-data-synchronization#step-7)] So with plain incremental, the destination will not automatically mirror deletes from the source. ### 2. Options to propagate deletes You have these main options: 1. Use Change Data Capture (CDC) - With CDC on supported database sources, Airbyte reads the change log (inserts/updates/deletes). - Deletions are correctly applied so rows are removed from the final table. [[CDC vs incremental](https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication); [CDC delete example](https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication)] - In forum guidance, this is called out as the way for incremental sync with deletes. [[Forum 372](https://discuss.airbyte.io/t/372)] 2. Use a source that exposes a delete flag (soft deletes) - If the source has a
_deleted
(or similar) field, you sync it and handle deletion in your modeling layer. - Example: the Convex source exposes
_deleted
, and Incremental Dedupe “with deletes” will automatically remove deleted documents in the destination. [[Convex source](https://docs.airbyte.com/integrations/sources/convex)] - For many REST APIs, deletes are not exposed this way, so connectors cannot infer deletions. [[Forum 372](https://discuss.airbyte.io/t/372)] 3. Periodic Refresh Sync to mirror the source - A Refresh Sync wipes the cursor so the stream is re-synced from scratch, which can be used specifically when “the source… does not sync record deletions/removals, and you wish to mirror the source stream, which would include removing deleted records.” [[Refreshing your data](https://docs.airbyte.com/platform/operator-guides/refreshes)] - For Incremental Append + Dedupe, you can choose “Refresh stream and remove records” so that only records still present in the source remain in the final table after the refresh. [[Refreshing your data](https://docs.airbyte.com/platform/operator-guides/refreshes)] 4. Full Refresh Overwrite (if you can’t use incremental + CDC) - Community guidance: if you cannot use CDC and must have the destination mirror the source, the fallback is Full Refresh | Overwrite so the table is rebuilt each run. [[Forum 372](https://discuss.airbyte.io/t/372)] ### 3. Putting it together If you want to **keep incremental refresh and have deletes reflected**, your viable patterns are: - Incremental + CDC on a CDC-capable source (best option when supported). [[CDC vs incremental](https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication)] - Incremental with a delete flag (soft deletes) and use downstream SQL/transformations (or a connector like Convex that already uses
_deleted
). [[Convex source](https://docs.airbyte.com/integrations/sources/convex)] - Incremental most of the time + occasional Refresh Sync (remove records) to re-align with the source and drop rows that no longer exist. [[Refreshing your data](https://docs.airbyte.com/platform/operator-guides/refreshes)] If you share your specific source and destination, I can narrow down which of these is actually available for your setup.
l
Hi @Yuki Kakegawa I needed something similar for this and opened a discussion https://github.com/airbytehq/airbyte/discussions/69356 however I think things are quite slow on their community side