Skip to main content
All CollectionsIntegrationsSnowflake
Snowflake integration - using different Company IDs
Snowflake integration - using different Company IDs

Configuring the Snowflake integration to work with your choice of ID type to identify Companies in Snowflake

Carly Hammond avatar
Written by Carly Hammond
Updated over a week ago

Summary

  • In Planhat, the Company model is at the top of the data structure. Other models (such as End Users) link to Company

  • Although in Planhat this link works via the Company Planhat ID, if you're using the Company Source ID or Company External ID in Snowflake instead, the integration can convert between the ID types for you

  • It's easy to configure in the integration which ID you want to use. You do this via the field mapping row for "Company Id" in each relevant sync section

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 to 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 Planhat data structure, Company is at the top of the hierarchy, and almost all data models (with the exception of Issue and User) need to link to a Company - so, for example, End Users and Licenses are attached to a Company.

Although in Planhat, this link is recorded in the "Company ID" field on the model, which uses the Planhat ID type, you can configure the integration to convert this to the Company's External ID or Source ID instead, giving you flexibility to use your choice of ID type in Snowflake.


The theory

In Planhat, there are 3 different types of IDs (which you may also see referred to as "keys" or "keyables"). These are fields/properties on data models, used to identify specific records (such as the Bob Smith record on the End User model).

  • Planhat ID - this is the native ID in Planhat, generated in Planhat for that record. (It's often referred to, in dropdowns etc., as simply "ID")

  • Source ID - the ID for that record from an external system, usually your CRM

  • External ID - the ID for that record in your own system (or a different external system)

In Planhat, End Users (and records of other models) relate to their parent Company record via the Planhat ID. So for example:

(Note that abbreviations are used in this example in place of the actual IDs.)

On the Company model in Planhat:

Planhat ID

Source ID

External ID

Company 1 (e.g. Apple)

p1

s1

e1

Company 2 (e.g. Microsoft)

p2

s2

e2

On the End User model in Planhat:

Company Planhat ID

First name

Last name

Person 1

p1

Bob

Smith

Person 2

p2

Priya

Patel

You may well not have the Planhat IDs (denoted by "p1" etc.) saved in Snowflake (and indeed, note that Planhat IDs can't be generated in Snowflake and sent to Planhat - they can only be produced in Planhat and then sent externally).

So, for example, let's say that you have an End User table in Snowflake, where the Company reference for each End User is the External ID, not the Planhat ID:

In Snowflake:

Company external ID

First name

Last name

Person 1

e1

Bob

Smith

Person 2

e2

Priya

Patel

Person 3

e1

Gemma

Brown

How can this work when different types of Company IDs are used for End Users in Planhat and Snowflake?

Well fortunately, the integration can handle the conversion. You simply need to specify in the integration which ID to use.

So, for instance, in this case you can set the integration to look at Company External IDs for End Users. Then, when a new End User is fetched from Snowflake and synced into Planhat (Gemma Brown in this case), instead of mapping her to a Company by looking for a Company Planhat ID, the integration can take her Company External ID ("e1" in this example), and use that to match her new End User record to the right Company in Planhat.

It works much the same way when syncing from Planhat to Snowflake - you just need to specify which type of ID to use, and the integration can convert between the ID types, therefore maintaining the relationship between different models as they are synced between the two systems.


How to configure Company IDs

Within the Snowflake integration, you configure which type of ID you're using via the boxes "Replace with value when receiving from Snowflake" and "Replace with value when sending to Snowflake", nested under the "Company Id" Planhat field.

Simply click the chevron on the right-hand side of the field mapping row to expand it and show the further configuration to complete.

Here's what it could look like if you're using External ID in Snowflake, like in our example above:

Here's an example using source ID instead:

In both of these examples, because they're set to just receive from Snowflake, only the left-hand side ("Replace with value when receiving from Snowflake") needs to be defined. The right-hand side would need to be filled out if you were sending to Snowflake.

You can also see in the screenshots above that the name of the column in your Snowflake table can be whatever you want ("ORG_ID" and "ACCOUNTID" in these examples).


Further reading

For our main article on data mapping in the Snowflake integration, see here.

Did this answer your question?