🐛 Source Snowflake: In timestamp cursor fields, seconds in cursor values are rounded to ceil and causing to sync rows whose cursor column values are smaller than cursor.

[ameyabapat-bsft]

Environment

• Airbyte version: 0.35.5-alpha
• OS Version / Instance: AWS EC2
• Deployment: Docker
• Source Connector and version: snowflake - 0.1.5
• Destination Connector and version: S3 - 0.2.5
• Severity: Medium
• Step where error happened: Sync job

Current Behavior

Even though there is no change in cursor value, incremental sync pulls up rows whose cursor columns values are smaller than cursor value.
Cursor value(timestamp) is getting rounded to ceil when last (milliseconds) field is more than 500.
Ex: Snowflake timestamp value: 2022-01-28 10:30:33.614 which got round to 2022-01-28T10:30:34Z by airbyte.

Expected Behavior

It should not sync rows whose value is smaller than cursor value.

Logs

Please check them in sequence after reading steps to reproduce.
logs-58-0.txt
logs-59-0.txt
logs-60-0.txt
logs-61-0.txt
logs-62-0.txt

Steps to Reproduce

1. Create Snowflake -> Aws S3 incremental connection and manual frequency.
   My snowflake table is
   CREATE OR REPLACE TABLE test_timestamp ( id int, request_timestamp_formatted TIMESTAMP_NTZ );
   Set request_timestamp_formatted as cursor field.

2. Add 132 rows to the table.
   insert into test_timestamp select * from (select seq2(0), current_timestamp() from table(generator(rowcount => 132))) order by seq2(0) limit 132;

3. Run Sync. It should sync 132 records.

4. Add 100 records.
   insert into test_timestamp select * from (select seq2(0), current_timestamp() from table(generator(rowcount => 132))) order by seq2(0) limit 100;

5. It should sync 232 records as per the greater than equal to cursor value logic.

6. Add 3 more rows.
   insert into test_timestamp select * from (select seq2(0), current_timestamp() from table(generator(rowcount => 132))) order by seq2(0) limit 3;

7. Run Sync. It should sync 103 records: 100 repeated + 3 new.

8. On all subsequent syncs, It pulls up 103 records every-time.

Ideally it should sync only 3 records where cursor value is equal to newly added 3 records but it keeps on syncing old 100 records as well whose cursor column value is less than cursor value.
For reference:
My snowflake db structure after these steps.
select count(*), request_timestamp_formatted from test_timestamp group by request_timestamp_formatted;

Slack Thread : https://airbytehq.slack.com/archives/C01MFR03D5W/p1643368624680109

Are you willing to submit a PR?

I am not fully verse with code base. I feel we would need to fix at jdbc level on which this source is built on. I could try to fix given some pointers on code change locations.

[alafanechere]

@irynakruk could you please try to reproduce this issue?

[alafanechere]

I do confirm it looks closely related to this epic: #8904

[alexandr-shegeda]

will be fixed in the scope of this PR