Snowflake tables have columns (fields, in Planhat) which can contain semi-structured data called a VARIANT. These can store any data type in different key-value pairs, each capable of taking different data types. One of the most common examples is JSON-formatted data, with the form: {"key1": "value1", "key2": "value2"}.

In this article, we'll cover how to map the attributes of these Snowflake VARIANTS to Planhat.


In the Snowflake mapping article, we explained how to map whole columns in Snowflake to fields in Planhat. But if you try to map a Snowflake VARIANT to Planhat, you'll get an unintended outcome: the mapped field will show everywhere in Planhat as [Object], not the value you were looking to map across. In the screenshot below, we tried to map a VARIANT across to the end-user "Phone" field, but get [Object] instead!

This happens simply because Planhat hasn't been told which key-value pair to pull and map from the VARIANT, so it pulls the whole cell as a single {OBJECT}, and can't parse it properly.

So, when you map VARIANT type columns, you'll also need to specify which attribute of the column you'd like to map to which field in Planhat. The good thing is, as soon as you select a VARIANT column on the integration configuration page (see here for a refresher), Planhat will automatically display another input field, called "Specific Attribute" for you to input the path.

Example

Let's take a look at an example. A column of a snowflake table has the following form:

Each row of the column contains contains an OBJECT consisting of two VARIANTS, with either the key "a" or the key "b". Imagine the key "a" is the Phone number I'm looking to map across. I simply enter "a" in the "Specific Attribute:" field.

Now that I've told Planhat which key-value pair in the VARIANT to extract the relevant data from, it can complete the mapping - and the "Phone" field is populated with the value corresponding to the "a" key.

📌 Note: at the moment, the mapping of Snowflake VARIANTS to Planhat is unidirectional, meaning that the "sync direction:" will always be set to Receive from Provider.

Nested VARIANTS

If you have a nested VARIANT, for example:

{"salesperson" : { 

"id": "55",
"name": "Frank Beasley"

},

"customer" : [

{

"name": "Joyce Ridgely",
"phone": "16504378889",
"address": "San Francisco, CA"

}
]
}

where a single Snowflake column contains the 2 key-value pairs, one for "salesperson" and one for "customer", but each have nested key-value pairs, including "name", "phone", etc. Then you can use dot notation to access nested attributes.

So, if you wanted to map the customer phone number in this case, you'd simply enter customer.phone into the "Specific Attribute:" field in the configurator.

Did this answer your question?