# Can I use Snowflake Variant & Array

**URL:** <https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610>\
**Category:** Integrator.io\
**Tags:** sql, snowflake, error-handling, import, json\
**Created:** [November 9, 2022, 8:59pm UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610 "2022-11-09T20:59:56Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![alexbaeza](https://avatars.discourse-cdn.com/v4/letter/a/ecd19e/32.png) [@alexbaeza](https://connective.celigo.com/u/alexbaeza)\
**Post date:** [November 9, 2022, 8:59pm UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/1 "2022-11-09T20:59:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![scotthenderson](https://sea1.discourse-cdn.com/flex001/user_avatar/connective.celigo.com/scotthenderson/32/6780_2.png) [@scotthenderson](https://connective.celigo.com/u/scotthenderson)\
**Post date:** [November 9, 2022, 9:24pm UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/2 "2022-11-09T21:24:52Z")

</div>

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.

 ![5301043caa20740200000001-6a02864c6c9e4ed38d447c901cfbd6d9.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/6/63932b47b9289bcb40507e6ae4654772de21af3f.png)

 ![5301043caa20740200000001-d78d0edb893b46f6a8dc3373c5e18310.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/0/0356b8a65ad7cc2b39e57c56f40561f1abb1247c.png)

---

<div class="post-metadata">

**Author:** ![alexbaeza](https://avatars.discourse-cdn.com/v4/letter/a/ecd19e/32.png) [@alexbaeza](https://connective.celigo.com/u/alexbaeza)\
**Post date:** [November 9, 2022, 11:48pm UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/3 "2022-11-09T23:48:52Z")

</div>

## Flow Configs

### Flow

 ![5301043caa20740200000001-45ff932ff3354fc88990525d1a014b04.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/7/77466461ade6e65a685ca4549fece3f8ac2f858f.png)

### Import settings

 ![5301043caa20740200000001-b281ca8c9d904f73a7c0f00c4d8d428f.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/b/bd2e20c81d38a5ea7e01f9a882ed15bed071072c.png)

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 "".

 ![5301043caa20740200000001-cca4e84e3ac8465cbbf5f6d0070684e4.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/e/e9fd4b8477e3886ae8876ca14acd6487a83cb8b7.png)

### Errors

 ![5301043caa20740200000001-49da3c00c2724c0283d401265db321b5.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/f/fb78f6dc9397bd4d9889ee454d5915505ad35613.png)

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

---

<div class="post-metadata">

**Author:** ![scotthenderson](https://sea1.discourse-cdn.com/flex001/user_avatar/connective.celigo.com/scotthenderson/32/6780_2.png) [@scotthenderson](https://connective.celigo.com/u/scotthenderson)\
**Post date:** [November 10, 2022, 12:00am UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/4 "2022-11-10T00:00:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alexbaeza](https://avatars.discourse-cdn.com/v4/letter/a/ecd19e/32.png) [@alexbaeza](https://connective.celigo.com/u/alexbaeza)\
**Post date:** [November 10, 2022, 12:12am UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/5 "2022-11-10T00:12:23Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![tylerlamparter](https://sea1.discourse-cdn.com/flex001/user_avatar/connective.celigo.com/tylerlamparter/32/9204_2.png) [@tylerlamparter](https://connective.celigo.com/u/tylerlamparter)\
**Post date:** [November 10, 2022, 12:32am UTC](https://connective.celigo.com/t/can-i-use-snowflake-variant-array/610/6 "2022-11-10T00:32:18Z")

</div>

Awesome!