some (hopefully) quick questions: Context: • airbyte version -

0.40.18

• postgres source version -

1.0.23

• redshift destination version -

0.3.51

1. We use “incremental dedup” for 2 tables (postgres -> redshift). When i was examine the queries in the log, i noticed that one query has

updatedAt >= ?

while the other uses

updatedAt > ?

, the difference is the presence of

=

. Intuitively, i thought all incremental queries should be without the

=

? Whats the deciding factor for adding/omitting the

=

sign? _update_: i just found some doc on this, our

updatedAt

timestamp for both tables are down to the millisecond resolution, so I’m not sure why there is a difference in the query… 2. The one query that has the

=

sign, the connection state has

cursor_record_count = 333

a. what does “cursor count” represent in airbyte? b. is this normal? Thanks a bunch, and happy holidays!

This is discussed in this issue: https://github.com/airbytehq/airbyte/issues/14732 Related discussions: • https://github.com/airbytehq/airbyte/issues/9672 • https://github.com/airbytehq/airbyte/issues/15158 This article goes into incremental dedup + SQL: https://airbyte.com/tutorials/incremental-data-synchronization

ya, i scanned through some of those docs yesterday and now understand the edge case. But i’m still not sure why airbyte uses

>

for one table and

>=

for the other in the same connection? by that edge case logic, everything should be

>

then? Apologies if i overlook something in those docs.

I believe this is discussed here: https://github.com/airbytehq/airbyte/issues/15158#issuecomment-1212368208

The problem of using >= is that if there is no new data, the rows with the cursor value will be duplicated each time. If a connector is scheduled to run every 30 minutes, that's 48x duplications per day. This experience seems pretty bad to me. If I see this in my data syncing tool, I would definitely look for other vendors. More importantly, it is possible that there may be a ton of data with the same cursor value. For example, the cursor is the creation date, and 1 million rows are generated programmatically with the same creation date. Under those scenarios, duplication should be avoided as much as possible.

The solution I am currently implementing is to use > by default, and use >= when needed. The idea is to track the number of rows synced together with the cursor value. So the state looks something like { users: { count: 3, cursor: "2022-01-03 00:00:00"}}. Each time before the incremental sync, count the number of rows with cursorColumn = cursorValue. If the result is different from the one in the state, we know there are changes, and use >= for the incremental query. Otherwise, use > to avoid the duplication.

got it, thanks a lot!