DMS randomly drops columns

Hi,

Possible reasons as to why DMS skips some columns include:

1. Some data types in the source might not be supported by DMS or by Kinesis as a target. Check if the missing columns have datatypes that can be properly represented in JSON since data is sent to Kinesis in JSON format.

2. Column names in PostgreSQL should be compatible with the JSON Key naming conventions and Kinesis requirements on special characters, reserved keywords and word case sensitivity etc.

3. Check if you've specified a partitioning key for Kinesis. Only columns that can be used as a partitioning key will be included by default.

4. If using DMS version 3.4.1 and higher, check the Kinesis Endpoint settings IncludeNullAndEmpty to ensure NULL and empty columns are included in the target. The default is false.

Further investigation require details of DMS version and PostgreSQL(self-managed or AWS managed) in use.

[+] Source data types for PostgreSQL - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source-PostgreSQL-DataTypes

[+] Using a PostgreSQL database as an AWS DMS source - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html

[+] Using Amazon Kinesis Data Streams as a target for AWS Database Migration Service - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Kinesis.html#:~:text=The%20default%20is%208%3B%20the,have%20this%20maximum%20limit%20increased.

[+] Streaming state changes from a relational database - https://repost.aws/articles/ARnsoLtSDMRyOTTzCB_QVKEQ/streaming-state-changes-from-a-relational-database