I am using mysql cdc to replicate records on azure blob storage. In one table I have selected few columns not all and replicate. Now I see 100s of duplicate records sometimes they even have the save

_ab_cdc_updated_at

value. Then it is hard to guess which one is the latest one

Duplicate Records in MySQL CDC Replication This is a known challenge with CDC replication in Airbyte. Here's what's happening and how to address it: Why Duplicates Occur When using CDC, Airbyte captures all change events from the database transaction log. For

UPDATE

operations in particular, some databases (like MSSQL) emit both a "before" and "after" image of the row — and similar behavior can occur in MySQL CDC. This can result in multiple records with the same

_ab_cdc_updated_at

timestamp but different field values. [CDC Docs] How Airbyte Differentiates CDC Records Airbyte adds metadata columns to help identify the most recent record: [CDC Docs] •

_ab_cdc_updated_at

— timestamp of the database transaction •

_ab_cdc_deleted_at

— present only for

DELETE

operations (null otherwise) •

_ab_cdc_lsn

— the log sequence number (position in the log where the record was retrieved) •

_ab_cdc_log_file

&

_ab_cdc_log_pos

— specific to MySQL, the file name and position in the log How to Identify the Latest Record When

_ab_cdc_updated_at

is the same across duplicates, use

_ab_cdc_lsn

(and/or

_ab_cdc_log_pos

for MySQL) to determine ordering. The record with the highest LSN is the most recent one. [CDC Docs] For example, in your deduplication logic:

SELECT * FROM your_table QUALIFY ROW_NUMBER() OVER (     PARTITION BY <primary_key>     ORDER BY _ab_cdc_updated_at DESC, _ab_cdc_lsn DESC ) = 1

Recommended Approach 1. Use Deduplicated sync mode if your destination supports it — Airbyte's deduplication logic uses the primary key and cursor (including LSN) to keep only the latest record in the final table. 2. Do not rely solely on

_ab_cdc_updated_at

for ordering when duplicates exist — always include

_ab_cdc_lsn

or

_ab_cdc_log_pos

as a tiebreaker. 3. Selecting only a subset of columns may make it harder to distinguish records if the changed column is not included in your selection — consider including the CDC metadata columns (

_ab_cdc_lsn

,

_ab_cdc_log_pos

) in your sync. Important Note The knowledge sources do not contain enough detail about the specific behavior of MySQL CDC to Azure Blob Storage with partial column selection to give a fully definitive root cause. I'd recommend checking the MySQL connector changelog and ensuring you're on the latest connector version, as CDC-related bugs are frequently fixed in newer releases. [Forum Discussion] 👍 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 I have

_ab_cdc_cursor

field. what would be datatype of it

_ab_cdc_cursor Data Type Based on the MySQL migration guide, MySQL CDC syncs have a default cursor field called

_ab_cdc_cursor

. [MySQL Migration Guide] However, the knowledge sources only explicitly define the data type of

_ab_cdc_cursor

for the MongoDB source connector. In MongoDB's implementation, it is defined as:

{
  "type": "number",
  "airbyte_type": "integer"
}

Which maps to an integer type. [MongoDbCdcConnectorMetadataInjector] The knowledge sources do not explicitly define the data type of

_ab_cdc_cursor

for the MySQL source connector specifically. For MySQL CDC, the cursor is typically based on the log sequence number (LSN), which is generally represented as an integer/long type — but I'd recommend: 1. Checking the actual schema discovered by Airbyte for your stream in the connection's Schema tab 2. Reviewing the MySQL source connector documentation for the exact type definition 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.