Skip to main content
All CollectionsIntegrationsSnowflake
How to map Snowflake VARIANT columns
How to map Snowflake VARIANT columns

It's simple to map Snowflake VARIANT - just specify the attribute in your field mapping

Christian Dreyer avatar
Written by Christian Dreyer
Updated over 5 months ago

Summary

  • Snowflake columns can contain semi-structured data called "VARIANT". These can store any data type in key-value pairs

  • When you map VARIANT type columns, you'll also need to define which attribute of the column you'd like to map to a field in Planhat

    • You do this by specifying the "Specific Attribute" when completing field mapping in the integration

    • If you don't configure this, you will see "[Object]" displayed in the mapped field in Planhat

  • If you have a nested VARIANT, you can use dot notation to access nested attributes

Who is this article for?

  • Planhat users who are setting up the Snowflake integration (e.g. Tech/Ops)

Series

This article is part of a series on the Snowflake integration:

Article contents

Introduction

This is a technical deep-dive article

Read on if you're a Snowflake Admin / CS Ops / Tech Specialist etc. and are configuring mapping in the Snowflake integration in Planhat.

If instead you'd like an overview of the Snowflake integration, please refer to our main article for a summary of the key points.

📌 Important to note

This article follows on from our guide on setting up mapping in the Snowflake integration. We highly recommend you familiarise yourself with the contents of that article before reading this one.

In the Snowflake integration, you map Planhat fields to columns in Snowflake tables/views.

Snowflake columns can contain semi-structured data called "VARIANT".

  • These can store any data type in key-value pairs

  • 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. Fortunately, it's really easy!

📌 Important to note

Currently, the mapping of Snowflake VARIANTS to Planhat is unidirectional, meaning that the sync direction will always be set to "Receive from Provider".

The potential problem, and the solution

In the Snowflake mapping article, we explained how to map whole columns in Snowflake to fields in Planhat.

But when you map a Snowflake VARIANT to a Planhat field, you can potentially get an unintended outcome: the mapped field can show in Planhat as "[Object]", rather than the value you were looking to map across. In the example below (from an End User Profile), I tried to map a VARIANT across to the End User "Phone" field, but you can see the field just displays "[Object]"!

This happens simply because the integration 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 Planhat 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.

Worked example

Let's illustrate this with an example. In this case, 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 when I am completing my field mapping in the integration.

Now that I've told the integration 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.

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 (e.g. one for "salesperson" and one for "customer" in the example above), but each have nested key-value pairs (here including "name" and "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 integration.

Further reading

  • This article has helped you troubleshoot "[Object]" appearing in fields when Snowflake mapping VARIANT columns. If you need support with any other issue related to the Snowflake article, check out our troubleshooting guide here

  • If you would like to refamiliarise yourself with the general data mapping process in the Snowflake, you can refer back to this article

Did this answer your question?