Cursor loading same data again in Airbyte sync

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

When using Airbyte for incremental append sync from MSSQL to Clickhouse, encountering issue where updating a row in the source table with a newer date results in all 10 rows being loaded again instead of just the updated row. However, adding a new row with a newer date after the initial load works correctly.


Question

Did anyone had this experience with cursor where it loads the same data again?

I have 10 rows in the MSSQL source table. They all have the same date (1975-01-01T00:00:00.000000) in the column that is used as cursor field.
I run the sync in Airbyte (Incremental Append) and it loads these 10 rows nicely to the target table in Clickhouse. The cursor value is set to 1975-01-01T00:00:00.000000.
I update one of the rows in the source table so it has newer date - 1980-01-01T00:00:00.000000. I run the sync for the second time and it loads 10 rows again instead of only one row with the updated date. Why?

But it works good when I first load 10 rows and then insert one more row with newer date and then it correctly loads only this one row.


This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["cursor", "incremental-append", "mssql-source", "clickhouse-target", "sync-issue"]
2

It’s likely that the cursor is being stored with the original value (1975-01-01). Then that cursor value is sent and rows newer or equal to it are retrieved. This helps to catch conditions in which data is incomplete or still being written (e.g. when you’re at the current date and the type is date only).

Since these overlaps tend to be tiny in real-world scenarios, it’s better to err on the side of caution than to risk missing even a single row of data.

At least that’s my assumption of how this is working; it varies a bit for each system and connector type depending on how they interpret the cursors. (A few connectors also have settings where you can add a lookback window to re-pull additional overlap.)