Can I use Snowflake Variant & Array

[alexbaeza]

I am trying to import a JSON array into Snowflake as either an ARRAY or VARIANT data type.

When I try to configure this in my import mapping, the result is this error:

  Message: 
    SQL compilation error:
  Unsupported data type 'VARIANT'.
  Code: 002040
  Source: Snowflake
  Timestamp: 2022-11-09T20:38:03.038Z
  Error ID: 484461026
  Classification : Connection

Sample Input

{
  ...
  "PaymentMethodDetails": [
    "Standard"
  ],
  ...
}

Target Table

In snowflake this JSON array is mapped to a column with either the ARRAY or VARIANT data type (both fail with the same error above.

[scotthenderson]

Yes, you can import data into a variant column in Snowflake, and this is the best practice for ELT use cases. Here are some pics from one of my working flows. Please note that 'V' is a variant column. LMK if you need more info.

[alexbaeza]

Flow Configs

Flow

Import settings

The parsed Output from my export looks like this:

{
  "page_of_records": [
    {
      "record": {
        "payload": {
          "Orders": [
            {
              "AmazonOrderId": "113-8158555-4786664",
              "EarliestShipDate": "2022-01-06T07:59:59Z",
              "SalesChannel": "Amazon.com",
              ...

Mappings

I don't have the "PaymentExecutionDetail" key value pair in my source records, but I want to map it for when I import the entire dataset (right now I am testing with just 100 orders).
In the Output it looks like it has taken this mapping and interpreted to be a string value of "".

Errors

As mentioned earlier the retry data does not contain the key value pair for PaymentExecutionDetail

[scotthenderson]

Your setup is not quite the same as what I sent above, and you should copy my exact setup (i.e. 'data type = JSON' and the expression should be '{{{jsonSerialize this}}}' . Also, due to the possible shifting columns bug that we are looking into now, I highly recommend removing all individual field mappings where the source record field could be empty, and instead just map the entire JSON object into a single variant column. You can define Snowflake views to separate out all the fields into their own columns.

[alexbaeza]

Thank you!

I think I misunderstood what {{{jsonSerialize this}}} was.

I now realize that jsonSerialize is a helper and this represents the entire record.

This is exactly what I was looking for.

Now each record is stored entirely in a single variant column!

[tylerlamparter]

Awesome!