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

  1. CRM Data Mapping with Snowflake

  2. Usage Data Import from Snowflake


CRM Data Mapping with Snowflake

Currently, only CRM data is mappable between Planhat and Snowflake. The models in Planhat which are supported by this integration are as follows:

  • Company

  • Enduser

  • User

  • Churn

  • Asset

  • Opportunity

  • Project

  • Task

  • Conversation

  • Issue

  • NPS

  • License

🚀 Quick tip: the standard mappable attributes for each model can be found in our API documentation. Importantly, since the integration supports custom fields, you're not limited to the standard set.

Of course, we'll be extending this integration to more models over time, and we hope to support every model in Planhat as soon as possible.

📌 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 you want mapped between Snowflake and Planhat, in the new Section:

  1. select the model in Planhat and the table in Snowflake

  2. choose the overall sync direction (Send..., Receive..., Both Directions)

  3. select the unique identifier in both systems (e.g., an ID or external ID string)

  4. map all the relevant Planhat fields to Snowflake columns

  5. make sure the sync direction is correct (what you want it to be) for each field

  6. Click save section on the bottom left of the section editor to save your progress

📌 Note: if you select Both Directions when configuring the sync direction of the integration, 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 model (e.g., Company, Enduser, User, Churn...) mappings

  • Fields are designed for field (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 now 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:

  1. select the model in Planhat and the table in Snowflake

  2. choose the overall sync direction (Send..., Receive..., Both Directions)

  3. select the unique identifier in both systems (e.g., an ID or external ID string)

  4. map all the relevant Planhat fields to Snowflake columns

  5. make sure the sync direction is correct (what you want it to be) for each field

  6. Choose a proper reference to the parent model (e.g., Company)

  7. Click save section on the bottom left of the section editor

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!

Usage Data Import 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. The process is even simpler than the CRM mapping - we'll run through it now.

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:

    • User activities, which are detailed here

    • Custom Metrics, which are detailed here

  • Sync direction: there are fewer options for metric importing than CRM Data Mapping. You can choose:

    • Not Syncing

    • Receive 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 minutes

    • 10 minutes

    • 30 minutes

    • 1 hour

    • day 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 for 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.

Did this answer your question?