We use Matillion to CDC from AWS RDS postgresql. This morning we are getting matillion errors when we try to load new changes for one of eight tables on a CDC job

Matillion ETL
1

Matillion job error --

Numeric value '7407461\N' is not recognized

File 'qortaprod/outgoing/public/driver_data/20220301-162418527.csv', line 105, character 7

Row 105, column "TMP_PUBLIC_DRIVER_DATA"["PERSON_ID":3]

If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

 

I,8519343,LA,xx,\N,false,\N,PERSONAL,\N,\N,\N

U,6389881,CA,xx,92155xx,false,2022-03-01,PERSONAL,\N,\N,\N

U,8519343,LA,xx,95811xx,true,\N,PERSONAL,\N,\N,\N,

D,7407461\N,\N,\N,\N,\N,\N,\N,\N,

I,8519345,TX,xx,\N,false,\N,PERSONAL,\N,\N,\N,

U,8519345,TX,xx,95811xx,true,\N,PERSONAL,\N,\N,\N,

 

Above is from the S3 file in AWS. You can see the \N is before the comma in the row #4, which is the delete record that broke our CDC pull. This has been happening about once per week. We don't know if this is an AWS DMS issue or a Matillion issue. The value of 7407461 is a valid key in the table that is breaking here.

2

I might be missing something or not understanding the problem fully for which I apologize in advance...but isn't there a comma missing after 7407461 ? If you've defined column #2 as numeric then I would expect the job to fail because of the extra \N which is not numeric.

 

Isnt this how the record should look: D,7407461,\N,\N,\N,\N,\N,\N,\N,\N,