Snowflake Numeric value is not recognized

Situation

I am exporting order data from Amazon SP-API and Importing it to Snowflake. When I try to import the data into Snowflake I get an error stating that the numeric value 'Order' is not recognized. I don't understand why Snowflake thinks this value should be numeric, In Celigo I have tried mapping this key with and without manually setting the datatype to String.

Any help would be greatly appreciated.

Basic Configuration Details

I am using the "Hybrid" connection to Amazon

I am using "SP-API" api type

My export type is "Test - export only 1 record"

My parsed output looks like this (the orders list contains 100 order objects):

{
  "page_of_records": [
    {
      "record": {
        "payload": {
          "Orders": [
            {...},
            ...
          ]}}}]}

Error

When I try to import the records into snowflake I get this error:

 Message: 
    Numeric value 'Other' is not recognized
  Code: 100038
  Source: Snowflake
  Timestamp: 2022-11-09T18:42:25.197Z
  Error ID: 484384545
  Classification : Value

My retry data shows that the only key that has a value of "Other" is "PaymentMethod". I have confirmed that If I remove payment method from the import mapping, this error goes away.

{
  "payload": {
    "Orders": [
      {       ...,
        "PaymentMethod": "Other",
        ...
      }
    ],
    "NextToken": "zzz",
    "CreatedBefore": "2022-11-09T18:40:08Z"
  }
}

If I edit any of the retry data (not the PaymentMethod), and retry then the error also goes away for this one record.

Data Investigation

Since this is a test export, only one page of data is returned, and the "Orders" array only contains 100 orders.

All 100 orders have the same key value pair of:

"PaymentMethod": "Other"

So, I don't think this error is caused by having one of the records incorrectly sending an integer value.

One to Many Configuration

In my import I break out the nested orders using the one to many option:

Import Type

Import Mapping

PaymentMethod field Configuration

I have tried this integration with and without the Data type being explicitly set to String.

Snowflake

In snowflake the definition for the "paymentmethod" column is:

CREATE TABLE ...(
  ...,
  paymentmethod VARCHAR,
  ...);

SP-API reference

PaymentMethod

Scott Henderson CTO

November 09, 2022 23:22

The error being returned by Snowflake "Numeric value 'Other' is not recognized" makes me think that the column in Snowflake is not a VARCHAR (i.e. it is likely setup as an INTEGER or equivalent). Can you double check this? If you are positive it is setup correctly in Snowflake, then I def recommend contacting support.

Alex Baeza

November 10, 2022 00:14

No need to contact support, I am now importing the entire record as a variant.

From there I should be able to manipulate the data in snowflake from there. I think the data type issues were caused by shifting columns in snowflake due to missing key value pairs in some records in the JSON response.

Thanks for all your help.

November 10, 2022 00:36

My pleasure!