@kapa.ai How does Airbyte handle deleted records in CDC connections, for example a MongoDB source connector pushing to destination BigQuery?

How Airbyte Handles Deleted Records in CDC How Deletions Are Captured at the Source When a record is deleted in a CDC-enabled source (like MongoDB), Airbyte reads the deletion event from the database's change log. The deleted record is emitted as a regular record but with two key metadata fields populated: [CDC docs] •

_ab_cdc_deleted_at

: Timestamp of the deletion transaction (only present for

DELETE

events) •

_ab_cdc_updated_at

: Timestamp of the transaction All other data fields in the deleted record will be

null

— only the primary key (e.g.,

_id

for MongoDB) and the CDC metadata fields are included. [CDC docs] MongoDB-Specific Behavior For MongoDB specifically, the handling depends on the server version: • MongoDB < 6.x: The delete event does not contain the "before" document, so only the

_id

of the deleted document is available. • MongoDB 6.x+ with pre-image support enabled: The full "before" document can be included in the deletion record. [MongoDbDebeziumEventConverter] > ⚠️ There is a known open issue where the MongoDB source connector stores the

_id

of deleted rows as a raw JSON object (e.g.,

{"$binary": {...}}

) instead of the expected Base64 string, which can cause matching issues at the destination. [source-mongo issue] How Deletions Are Applied at the Destination (BigQuery) The behavior at the destination depends on the sync mode configured: Incremental | Append + Deduped (recommended for CDC) In this mode, the destination performs a MERGE operation. For hard deletes (the default), the logic is:

MERGE INTO target
USING source
ON target.pk = source.pk
-- Delete if _ab_cdc_deleted_at is set and deletion is newer
WHEN MATCHED AND source._ab_cdc_deleted_at IS NOT NULL
     AND source.cursor > target.cursor THEN DELETE
WHEN MATCHED AND source.cursor > target.cursor THEN UPDATE ...
WHEN NOT MATCHED AND source._ab_cdc_deleted_at IS NULL THEN INSERT ...

This means the record is physically removed from the final destination table. [CDC implementation reference] Alternatively, soft delete mode keeps the record in the destination but populates

_ab_cdc_deleted_at

as a tombstone, leaving all other fields as

null

. [CDC implementation reference] Incremental | Append Only The deletion record is simply appended as a new row with

_ab_cdc_deleted_at

populated and all other fields

null

. The original record is not removed. You would need to handle deduplication/filtering yourself downstream. > ⚠️ There is a known open issue with destination-bigquery where, in Append + Deduped mode, the deduplication process may not correctly handle deletions — resulting in the final table showing a record with only

_id

populated and all other fields set to

null

, rather than removing the record entirely. [destination-bigquery issue] Summary 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai Do we have to configure something for the deletes to be handled or is it automatically included?

Based on the knowledge sources, here's what requires configuration vs. what is automatic: What's Automatic • Once CDC is enabled on a connection, deletion events are automatically captured from the source change logs and the

_ab_cdc_deleted_at

metadata field is automatically populated on deleted records. No extra steps are needed for Airbyte to detect and pass along deletions. [CDC docs] What Requires Configuration 1. CDC must be enabled at the source level — CDC is a source-level setting. You need to configure your source connector (e.g., MongoDB) to use CDC replication mode. [CDC docs] 2. Sync mode affects how deletes are applied at the destination — You need to choose Incremental | Append + Deduped for deletions to be properly applied (i.e., records removed or tombstoned). With Incremental | Append, the deletion row is just appended and the original record stays. [CDC implementation reference] 3. Hard delete vs. Soft delete — The destination connector has a

cdc_deletion_mode

configuration option. Hard delete (the default) physically removes the record; Soft delete keeps it with the

_ab_cdc_deleted_at

timestamp populated. [CDC implementation reference] 4. New tables/columns added after CDC setup — These require a manual refresh (snapshot) before CDC begins tracking changes on them. [CDC docs] In Short The deletion detection is automatic once CDC is configured, but you need to ensure the correct sync mode (Append + Deduped) and deletion mode (hard or soft delete) are set on the destination side for deletions to be properly applied. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai How would it work for a situation with MongoDB CDC source and BigQuery destination with sync mode Incremental + Append ?

With MongoDB CDC source → BigQuery destination using Incremental | Append sync mode, here's what happens: Deletion Behavior 1. At the source: When a record is deleted in MongoDB, the CDC connector captures the deletion event from the change log and emits a record where: ◦

_ab_cdc_deleted_at

is populated with the deletion timestamp ◦ All other fields are

null

(only the

_id

is retained) ◦

_ab_cdc_updated_at

is also populated [CDC docs] 1. At the destination (BigQuery): In Incremental | Append mode, this deletion record is simply appended as a new row to the destination table. The original record is not removed or modified. [Incremental Append docs] So your BigQuery table would end up with two rows for the deleted document: • The original row with all fields populated • A new row with only

_id

and

_ab_cdc_deleted_at

filled in, all other fields

null

Practical Implication You would need to handle the deletion logic yourself downstream — for example, by filtering out records where

_ab_cdc_deleted_at IS NOT NULL

, or by deduplicating on

_id

and taking the latest record by

_ab_cdc_updated_at

. > ⚠️ There is a known issue reported with this exact setup (MongoDB → BigQuery). A user confirmed that switching to Incremental | Append mode results in the deletion record being appended with only

_id

populated and

_ab_cdc_deleted_at

filled in — which is the expected behavior for this mode, but means the destination retains the original record alongside the tombstone row. [destination-bigquery issue] Key Takeaway Incremental | Append does not apply deletions to the destination — it only records that a deletion occurred. If you want deletions to be automatically applied (records removed or tombstoned), you need Incremental | Append + Deduped mode instead. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.