Support for handling Decimal values in Debezium Postgres Connector

[vivek Kumar]

Hi Team,

We have a Debezium Postgres Connector setup in our confluent cloud which is running fine and populating the data in the kafka stream, but when we are trying to load this data to snowflake using a snowflake sink connector , for some of the records the sink is failing with an error message as shown below.

The record in question here is having a column which is a double in Postgres but is being saved a FLOAT in Snowflakes.

"message": "[SF_KAFKA_CONNECTOR] Insert Row Error message:The given row cannot be converted to the internal format due to invalid value: Value cannot be ingested into Snowflake column BE_RELATIVE_PER_CHANGE of type REAL, rowIndex:0, reason: Not a valid decimal number",

We currently have decimal.handling.mode = 'string'.

Can someone please help us on how we can setup this so that we dont have a loss of precision and are also able to do this without any error.

Thanks in Advance.

Vivek Kumar

[jiri.p...@gmail.com]

Hi,

I suppose the issue is the handling mode string format. I suppose that neither Snowlake sink connector nor the Snowflake itself is able to automatically convert string to float. You either need to use a different handling mode or use an SMT that will do a convoersion.

BTW - FLOAT in Snowflak is classic 64-bit FP that can lead to loss of precision. So in this case you can use decimal handling mode set to double as the types will match.

Jiri

[vivek Kumar]

Hi Jiri,

I did try the SMT, which is also not working, sample of what I tried.

"transforms" = "Cast"
"transforms.Cast.type": "org.apache.kafka.connect.transforms.Cast$Value",
"transforms.Cast.spec": "BE_PER_CHANGE:float64, BE_RELATIVE_PER_CHANGE:float64, PERC:float64"

but I think I found something during my investigation into Postgres table I saw that the column which we were having issue handling was having some value as NaN(not a number), and it seems that these are the values which is having the issue while doing a sink at snowflakes side.

Can you help on how we can handle this kind of column with NaN values?

Thanks in advance,

Vivek Kumar

[jiri.p...@gmail.com]

Hi,

this depends on your business logic - how to handle NaN. You cn wite an SMT that would convert NaN to an arbitrary number like zero etc.

Also bear in mind that there are other potential special numbers like infinity.

Jiri