AWS DMS Converts Source PostgreSQL TIMESTAMP column to different value in S3

Question

• DMS details:

• Source Endpoint is PostgreSQL DB.

• Target Endpoint is S3 - Parquet file.

Source DB has a column paymentdate with datatype timestamp. There is a row with value stored as 0002-11-30 00:00:00.000 in the paymentdate.

When processed via DMS, above row is replicated as value 0003-11-30 00:00:00.000 into S3 parquet file. No transformation is applied for column as part of DMS definition (i.e. No Mapping rules defined for paymentdate).

This is observed only in case of specific case of this timestamp value. For rest of the values (i.e. Valid dates like 1993-11-30 00:00:00.000, 2020-11-30 00:00:00.000 etc.) DMS is replicating properly.

How to handle such scenario? If possible, I do not want to apply transformation for paymentdate as it impacts downstream application too.

Answer 1

After some research, applying transformation into DMS to convert data type to datetime for the column resolved the issue.

{
  "rule-type": "transformation",
  "rule-id": "12345",
  "rule-name": "change-paymentdate-column-scale",
  "rule-action": "change-data-type",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "public",
    "table-name": "tableABC",
    "column-name": "paymentdate"
  },
  "data-type": {
    "type": "datetime"
  }
},

Interestingly, earlier it was implicitly converted into S3 as timestamp only, but may be not able to handle all the dates.