Before reading this article, make sure that you already have a fully functioning Snowflake integration, and see the following screen under Profile > Integrations > Snowflake. If you don't, check out our Snowflake Setup Guide, and come back when your connection is ready.
Now Snowflake and Planhat are talking to each other, we're going to cover how you should put the integration to work, syncing data bi-directionally between the two.
Contents
CRM Data Mapping with Snowflake
The models in Planhat which are supported by this integration are as follows:
Company
Enduser
Opportunity
License
Churn
Project
Asset
Issue
NPS
Conversation
Task
Campaign
Objective
Workspace
🚀 Quick tip: the standard mappable attributes for each model can be found in our API documentation. Importantly, since the integration supports custom fields, you can map both standard and custom fields as needed.
📌 Note: at the moment, the mapping is one-to-one, meaning that a single model in Planhat maps to a single table in Snowflake. However, the same model/table can be reused meaning that, through multiple mappings, a single model/table can map to multiple tables/models.
Setting up models
To setup a model, click "Add New Section" under Profile > Integrations > Snowflake.
To set up each model:
select the model in Planhat and the table in Snowflake
choose the overall sync direction (
Send...,Receive...,Both Directions)select the unique identifier in both systems (e.g., an ID or external ID string)
map all the relevant Planhat fields to Snowflake columns
make sure the sync direction is correct (what you want it to be) for each field
Click
save sectionon the bottom left of the section editor to save your progress
📌 Note: if you select Both Directions when configuring the sync direction, data will first be pulled from Snowflake, then pushed from Planhat. This order of operations might explain any unexpected results. If you don't receive the results you're looking for when you sync, reach out to your TAM or CSM.
In the integration configurator:
Sections are designed for different models (e.g., Company, Enduser, User, Churn...) mappings
Fields are designed for fields (e.g., ID, ARR, MRR) mappings
Fields are mapped just like the models they exist within, and by default "Inherit" the sync direction of the main Section/model they reside in. However, they can be set to behave differently on an individual basis, using the same sync direction options as above.
📣 Pro tip: if you're looking to map semi-structured VARIANT columns from Snowflake consisting of multiple key-value pairs (such as JSON-formatted data) like this{"key1": "value1", "key2": "value2"}, there's one more step, explained here.
Let's run through a couple of examples...
Company
We're mapping over a Company model, meaning that we match up the models and IDs (unique identifiers) across Snowflake and Planhat, and then are free to add our first field (in the case below, "Name") mapping.
In detailed terms, the command being given maps (links) the company model in Planhat to the company model in Snowflake, using Snowflake's COMPANY_EXTERNAL_ID column, and Planhat's ExtId field. Of course, the naming of these variables is likely to be different in your own system.
We also populate the first field with a name mapping, and can simply click the "map another field" button to repeat this process for all other relevant fields.
📣 Pro tip: the company model supports references to the User model, allowing mapping of Owner, Co-Owner and Follower fields between Snowflake and Planhat. To do so, you'll need to reference a unique attribute, such as id, ExtId or Email, in the take value from User model by: and replace with value from User: fields (see "Conversations" below for an example.
Enduser
Mapping the Enduser model is slightly more complicated. In the example below, we define an enduser for a company. In this case, the unique identifier is email, but it could equally be id, ExtId or SourceId, depending on what suits your data best.
The added complexity is that the Enduser model also involves a parent <> child relationship between the company and its enduser, shown by 2 additional checkboxes: "take value from Company model by:" and "replace exported value with:". This is identical to the rest of the section settings: simply define what property of the company model to use for the reference. In this case, we use the ExtId of the parent company.
This process is repeated with this additional step, as follows:
select the model in Planhat and the table in Snowflake
choose the overall sync direction (
Send...,Receive...,Both Directions)select the unique identifier in both systems (e.g., an ID or external ID string)
map all the relevant Planhat fields to Snowflake columns
make sure the sync direction is correct (what you want it to be) for each field
Choose a proper reference to the parent model (e.g., Company)
Click
save sectionon the bottom left of the section editor
📌 Note: if you don't have a company's Planhat Id in Snowflake, you can use this same model reference approach to map using the ExtId. Simply select and configure the two checkboxes as in the screenshot above, with ExtId.
Opportunity
The mapping for other models (besides Conversations) - user, churn, asset, opportunity, project, task, issue, NPS and license - behaves in exactly the same way, as the final example below shows.
Conversations
Mapping the conversations model is almost identical to the rest of the models, except for one important difference. Since conversations (Logged Activities or Notes) have Owners, we need to make sure this attribute is mapped correctly. To do so, replicate the settings in the screenshot below, where take value from User model by: and replace with value from User: are populated with a unique attribute, such as id, ExtId or, as in the case below, Email.
📌 Note: the conversations model mapping doesn't yet support Email - only Logged Activities and Notes are included.
🚀 Quick tip: for mappings involving the "Conversations" model, ensure the relevant "Involved Contacts" field (table column) from Snowflake is formatted as an array of external or source IDs, with each cell structured like: ["source_id_1", "source_id_2", ...]
Launching the Connection
When you're done creating (and saving!) all the sections you want, click Full sync on the right side of the configurator, just below the last section.
📣 Pro tip: to confirm that your Full sync was completed as you intended it to be, click Show logs on the top right of the configurator, next to the two activation toggles. Models should be listed as having been "created", "updated", etc. with (roughly) the expected number of events to confirm that the integration is working correctly. If you suspect your integration isn't working as you set it to, reach out to your TAM or CSM for help!
Mapping Usage Data from Snowflake
Usage metrics can also be imported directly into Planhat from Snowflake, by switching from the "CRM" tab to the "Usage" tab in Profile > Integrations > Snowflake.
Setting up an import
To set up your desired import, populate the Section configurator as follows:
Data type: this defines the kind of metrics data that will be synced to Planhat, and takes one of two values:
Sync direction: there are fewer options for metric importing than CRM Data Mapping. You can choose:
Not SyncingReceive from Provider(Snowflake)
Model in Snowflake: the table or view in Snowflake where the data to import is
Run once in: a parameter for how frequently you want to fetch data from the Snowflake warehouse. This has the option to fetch every:
5 minutes10 minutes30 minutes1 hourday at:a specific hour of the day between 00:00 and 23:00
Batch size: the number of rows to be fetched per batch. For example, if your Snowflake table contains 100,000 rows, and your batch size is 10,000, Planhat will issue 10 separate sync requests to your warehouse
Key in Snowflake: this sets the Snowflake column we'll use as a unique identifier for the incoming rows of values. It needs to be numeric, and incrementing (increasing in value) for each new row added
Once you've created and populated all the sections of data you want to import into Planhat, make sure they're all saved by clicking save section.
🚀 Quick tip: unlike the CRM data mapping, there's no need to perform a Full sync when you connect up. That's because the import is set to run on a recurring basis (at whatever frequency you set it to in Run once in:) .
So sit back, relax and watch Planhat do its magic.