airbytehq / airbytehq/airbyte

Source mssql: Improve support for timestamps with 7th decimal digits values and above

Open
#79,687 4 comments 0 reactions 0 assignees View on GitHub
autoteam connectors/source/mssql team/db-dw-sources
Dominant language
Python
Stars
22.1k
Forks
5.3k
PR merge metrics
PR metrics pending

Description

This is for mssql but applies to any database supporting timestamps with scale > 6 (decimal digits).
Namely Oracle

Airbyte truncates timestamps to 6 decimal digits
This means that mssql timestamp value `12:34.1234567` will lost the last decimal digit and will be sent as `12:34.123456`.
MSSQL defined 7 digits by default and supports up to 9.
When user defined cursor of timestamp such as `datetime` or `datetime2` is used,
The saved state itself is saved with 6 supported digits and that can lead into edge case where value greater than the saved state are not read if the only increment the 7th digit or above.

We previously solved the issue by changing the incremental user cursor query to be unbounded - `… WHERE cursor >= saved_cursor` with no upper bound.
This helps by reading values above the saved state when even when they only differ by 7th or 8th or 9th digit.

The problem is that the value of the saved cursor in state only had up to 6 digits, which leads to repeated read of the same values as long as the table doesn't change - an over read.

The purpose of this issue is to track progress in improving and discuss possible suggestions.

---
**Internal Tracking:**
- https://github.com/airbytehq/oncall/issues/12592
- https://github.com/airbytehq/oncall/issues/12681

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.