@kapa.ai How should a connection (Mysql -> Snowflake) where the stream is synced as 'Incremental - Append' using CDC, handle deletes? In both Hard and Soft delete modes

Thinking completed In Incremental | Append mode, records in the destination are *never deleted or mutated*—every change extracted from the source is appended as a new row to the destination table [Incremental Sync - Append]. When using CDC with MySQL, Airbyte captures deletion events directly from the database's binary log and outputs them with a populated

_ab_cdc_deleted_at

timestamp metadata column [Change Data Capture (CDC)]. The knowledge sources do not explicitly mention destination-level "Hard Delete" vs "Soft Delete" configuration settings for the pure Incremental - Append sync mode. Instead, deletion modes (such as

HARD_DELETE

and

SOFT_DELETE

) apply to deduplication / upsert operations (such as Incremental - Append + Deduped) [SnowflakeDirectLoadSqlGeneratorTest; SnowflakeDirectLoadSqlGeneratorTest]. Here is how deletes are handled: 1. In Incremental - Append Mode Because the mode is append-only: • All events are appended: Airbyte will not delete any existing rows from your Snowflake destination table [Incremental Sync - Append]. • Deletion event representation: When a row is deleted in MySQL, a new record is appended to Snowflake where

_ab_cdc_deleted_at

contains the deletion timestamp (and typically payload fields are

null

except for the primary key) [Explore Airbyte's Change Data Capture (CDC) synchronization]. 2. How Hard vs. Soft Delete Modes Behave (in Deduplicated / Upsert Syncs) In modes where deduplication and CDC deletion handling occur (e.g., direct load upserts using a primary key): • Soft Delete Mode: The destination does not run a

DELETE

statement. Instead, it upserts the incoming deletion record wholesale [SnowflakeDirectLoadSqlGeneratorTest]. The row remains in the final table in Snowflake, with

_ab_cdc_deleted_at

populated, marking it as soft-deleted [BasicFunctionalityIntegrationTest]. • Hard Delete Mode: When a record is processed where

_ab_cdc_deleted_at IS NOT NULL

, the destination executes a

WHEN MATCHED ... THEN DELETE

SQL clause, physically removing the matching record from the target table in Snowflake [SnowflakeDirectLoadSqlGeneratorTest; BasicFunctionalityIntegrationTest]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.