Destinations should support timestamp with millisecond precision

[tuliren]

Summary

Currently Postgres source returns timestamp with second precision. This causes problem for timestamp columns with millisecond precision when such column is used as the cursor in the incremental sync. The data persisted on the destination side has second precision, while the original data in the database has millisecond precision. Consequently, the timestamp in the original data is always newer than that synced to the destination because of the extra millisecond values. So the sync is always triggered even when there is is no new data.

Slack thread.

TODOs

• Here is the implementation plan. Once ready, convert each bullet point into its own issue.
• Update at least one of the existing DAT timestamp test case to have millisecond precision.
• Update each destination to support the millisecond precision timestamp. If a destination is incompatible with such timestamp, make sure its DAT does not fail (probably by overriding the assertion to still check for second precision timestamp). It may be necessary to update normalization and dbt code. Some of them may be combined in one project (e.g. some JDBC destinations).
  • Azure Blob Storage
  • BigQuery
  • ClickHouse
  • Cassandra
  • Databricks
  • Elasticsearch
  • GCS
  • Google Firestore
  • Google PubSub
  • Kafka
  • Keen (Charfigy)
  • Local CSV
  • Local JSON
  • MariaDB ColumnStore
  • Mongo DB
  • MQTT
  • Postgres
  • Pulsar
  • Redshift
  • Rockset
  • S3
  • SFTP-JSON
  • Snowflake
• Make sure all destinations can pass the new DAT test case.
• Update the following source databases to support millisecond precision timestamp
  • MySQL
  • Postgres #9963
  • SQL Server
• Linked bugs
  • Source Postgres: lost milliseconds precision when a timestamp/datetime is converted to string

┆Issue is synchronized with this Asana task by Unito

[ameyabapat-bsft]

Do we need to add snowflake-source to Update the following source databases to support millisecond precision timestamp category? I have found the similar issue #9915

[ameyabapat-bsft]

@alafanechere @tuliren any updates on source snowflake (#9915) ? This issues is magnified in our use cases where large data dump(10-100k rows) is added at source in single operation which make timestamp of lots of rows same and all them are resynced in in next sync operation.

[grishick]

Sources have been fixed. Converting this to destination-specific issue. Next step: create issues for each destination, which is capable of supporting millisecond precision

[tuliren]

@alexandr-shegeda, is GL working on the destination changes as well?