Skip to main content
All CollectionsIntegrationsSnowflake
Data mapping in the Snowflake integration
Data mapping in the Snowflake integration

Part 2 of the the setup steps for the Snowflake integration

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

Summary

  • The second stage of setting up the Snowflake integration is configuring data mapping

  • For CRM data, you choose which Planhat data model to map with which Snowflake table or view

    • You also select the sync direction (which can be bidirectional), specify the unique IDs on each side, and set up field mapping between Planhat fields and Snowflake columns

    • You save your settings, carry out a manual full sync, and enable the automatic hourly sync if desired

  • For time-series usage data, you choose whether you want to map each Snowflake table or view into Planhat to either User Activities or Custom Metrics in Planhat

    • You also specify the key (ID) in Snowflake, set up field mapping, and choose the batch size and sync frequency

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 setting up data mapping in the Snowflake integration in Planhat.

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

📌 Important to note

This is part 2 of our Snowflake integration setup guide.

Before reading this article, make sure that you have already prepared your Snowflake warehouse, successfully connected the Planhat Snowflake integration to Snowflake, turned on the integration, and selected your user, warehouse, database and schema:

If you haven't yet completed those steps, follow part 1 of the setup guide, and come back to this article when your connection is ready.

Now Snowflake and Planhat are connected, we're going to cover how you can configure it for your specific selection of data, and start syncing data between the two systems.

You can choose whether to sync CRM data and/or usage data in the Snowflake integration. This article is divided into these two main parts:

  1. CRM data in the Snowflake integration

  2. Usage data in the Snowflake integration

In summary, you will be creating and configuring sync sections, and the main principles for each sync section are:

  • Choose what data model/type in Planhat to map to what column in Snowflake

  • For CRM data, choose the sync direction

  • Specify the IDs/keys used in Planhat and Snowflake

  • Set up field mapping

  • For usage data, choose the sync frequency and batch size

1. CRM data in the Snowflake integration

The Snowflake integration has broad and flexible CRM syncing capabilities, and you can choose which Planhat data models to sync with Snowflake. Choose from the wide range of standard models, from the main ones such as Company, End User and License, to more specialised models such as Campaign, Objective and Workspace.

You choose which Planhat models to map, and you can map as many or as few as you want.

Each sync section corresponds to 1 Planhat model and 1 Snowflake table (or view), but you can create more sync sections if you would like to map the same model or table/view again - example shown below.

The integration supports both Planhat standard and custom fields. Again, you choose which to map to your choice of Snowflake columns.

🚀 Tip

If you set up a sync section for the Conversation model, this includes "Logged Activities" and "Notes". The Snowflake integration doesn't support syncing emails.

How to set up CRM data mapping

Firstly, ensure you have followed all the steps in our article on preparing Snowflake and connecting the integration.

Before you begin data mapping, make sure that you have specified your user, warehouse, database and schema at the top of the integration. These need to be set before you can see the big blue "Add New Section" button and start mapping your data.

Also ensure the "CRM" tab is selected - this is the default tab when you first open the integration:

Click the blue "Add New Section" button to add a new sync section. Each sync section is for mapping one Planhat model to one Snowflake table or view; simply add another one when you want to map another model / table (or view) combination.

It's easy to set this up. The basic steps are:

  1. Select the Planhat model on the left, and the Snowflake table/view you'd like to connect to it on the right

  2. Choose the overall sync direction - this can be:

    • "Send to Provider" [Snowflake]

    • "Receive from Provider" [Snowflake]

    • "Both Directions"

    • "Not Syncing"

    See here for further details

  3. Select the unique IDs (keys) in Planhat and Snowflake

    • Specify the ID field on the Planhat side (e.g. External ID or Source ID)

    • Choose a suitable column on the Snowflake side - IDs must be unique and text

    • See here for further details

  4. Set up field mapping

    • Map your choice of Planhat fields to your choice of Snowflake columns

    • See here for further details

    • For Planhat models other than Company, configure the reference to Company, by expanding the field mapping row for "Company Id" and selecting the appropriate ID(s) - see here for further details

  5. Click "Save Section" at the bottom right of the section to save your configuration

... simple! 👍

Sync direction

The Snowflake integration is bidirectional for CRM data, meaning you can choose to push data to, and pull data from, each system.

You set the sync direction independently for each sync section (pair of Planhat data model plus Snowflake table/view), offering a great deal of flexibility.

For each sync section, you can choose from "Send to Provider", "Receive from Provider", or "Both Directions" - or "Not Syncing" means no data will be synced.

It's possible to change the sync direction after your initial configuration - e.g. you could initially select "Receive from Provider" when you're setting up your Planhat tenant, and then change it to "Both Directions" later on.

For data security reasons, regardless of direction, the integration does not delete records (e.g. if you manually delete an End User in Planhat, the integration won't delete the corresponding data in Snowflake), but records can be created and updated.

📌 Important to note

If a section is set to "Both Directions", data will first be fetched from Snowflake, and then pushed from Planhat. This order of operations might explain any unexpected results. If you don't get the results you're looking for when you sync, reach out to your TAM or CSM.

📌 Important to note

Data cannot be synced from Planhat back to Snowflake views, as views are built on the fly in Snowflake.

Unique IDs

For each sync section, you will see boxes for IDs under the Planhat model and Snowflake table/view.

The ID on the Planhat side could be Source ID or External ID, or alternatively email address for End User (your customers) and User (your colleagues). These are all fields on the Planhat models, containing a unique ID to specify that particular record (e.g. the Company record for Apple). Note that "Id" in the dropdown box corresponds to the Planhat ID.

For the ID on the Snowflake side, select a suitable column from the dropdown menu. The ID/key must be unique and text. The name of the ID in Snowflake can be different, depending on your own system.

Here are a few examples of what this could look like in the integration:

Field mapping

For each of the sync sections, you can set up field mapping: link up specific Planhat fields (standard or custom fields) with specific Snowflake columns. (Note that the integration does not create fields - it only syncs data between them - so you would need to create any required custom fields first before selecting them in the integration.)

The field mapping for a sync section will look something like this:

Click the image to view it enlarged

This is very simple to set up.

  1. Click the blue "Map a Field" button to add a new field mapping row

  2. Select a Planhat field from the left-hand dropdown menu

    • Selecting a field will automatically detect and display the field type

  3. Choose the sync direction

    • The default is the same sync direction as the parent model/table/view

  4. Select a Snowflake column from the right-hand dropdown menu

    • Selecting a field will automatically detect and display the field type

If you want to delete a field mapping row, simply click the red "x" on the right-hand side.

🚀 Tip

If you're looking to map semi-structured VARIANT columns from Snowflake, consisting of multiple key-value pairs (such as JSON-formatted data) such as {"key1": "value1", "key2": "value2"}, there's one more step, explained here.

📌 Important to note

Mapping User-type fields

The Snowflake integration supports mapping of Team Member fields, which refer to the Planhat User model. (Note that this is a separate model to End User, which is for customer contacts.)

For example, this includes mapping the "Owner" and "Co-Owner" standard fields on the Company model.

When setting up these field mappings, click the chevron on the right-hand side of the row to expand it and show further configuration. Then, specify what type of unique ID is used to identify the Users, such as "External ID" or "Email". This flexibility enables you to use your choice of ID in Snowflake to associate Companies etc. with Users, rather than just using the User Planhat ID.

Below is an example of field mapping on the Company model. Because in this case it's set to receive from Snowflake, the "Replace value with when receiving from Snowflake" needs to be defined on the left-hand side; the right-hand side would need to be filled out if sending to Snowflake.

📌 Important to note

Mapping Company associations - converting between Company IDs

  • 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

  • For further details, see this in-depth article

🚀 Tip

When mapping the "Involved Contacts" field in the Conversation model, ensure that the mapped column in Snowflake is formatted as an array of external or source IDs, with each cell structured like: ["source_id_1", "source_id_2", ...].

Enabling the CRM data sync

  1. When you're done creating (and saving!) all the sections you want, click the blue "Full sync" button on top right side of the integration (just above the sync sections)

    • Make sure you have enabled "change_tracking" (on all the relevant tables/views) in Snowflake first (which you should have already completed as part of the setup here)

    • The full sync sets the baseline; in the full sync, the integration can detect that change tracking is enabled

  2. Enable the toggle switch "activate Snowflake automation synchronization", assuming you would like the integration to run the automatic, hourly, partial sync

    • If you don't want CRM data to sync automatically in this way, you can leave the toggle switch off. You can manually run full syncs via the "Full sync" button and/or the arrow buttons on the right-hand side of sync sections

🚀 Tip

To confirm that your "Full sync" successfully completed, click "Show Logs" at the top right of the integration. You should be able to see creations and updates with (roughly) the expected number of events. If you have any questions, you can reach out to your TAM or CSM for help.

📌 Important to note

If you use a data transformation tool that regularly rebuilds tables in Snowflake, the transaction tree necessary for change tracking will be broken on each table rebuild. If change tracking was initially enabled, our Snowflake integration detects this, and will run a full sync of your CRM data, after which the change tracking will resume.

2. Usage data in the Snowflake integration

The Snowflake integration also supports syncing of usage (time-series) data. This is always one-way: from Snowflake into Planhat.

🚀 Tip

There are two types of raw time-series data in Planhat:

  • "User Activities" are actions carried out by End Users, e.g. logging in

  • "Custom Metrics" are other numerical values you want to track over time, e.g. amount of storage used by a Company

Once this usage data has been imported into Planhat, you can easily use Calculated Metrics to convert and combine raw metrics into new, more complex metrics to show exactly what you want and need to know about your customer's usage of your products.

So, for example, you can send in unaggregated logins as a User Activity, and then within Planhat create Calculated Metrics to look at total number of logins in the last 14 days, or the average number of logins per week, and so on.

Before you start mapping usage data, ensure you have followed all the steps in our article on preparing Snowflake and connecting the integration. Make sure you have specified the user, warehouse, database and schema at the top of the integration. These need to be set before you can see the big blue "Add New Section" button and start mapping your data.

To set up the usage data configuration, make sure you have selected the "USAGE" tab in the integration:

How to set up usage data mapping

Simply click the blue "Add New Section" button to add a new sync section, and then follow the steps to complete the form.

  1. "Data type" - choose from "User Activities" and "Custom Metrics"

    • Remember that:

      • "User Activities" are actions carried out by End Users, e.g. logging in

      • "Custom Metrics" are other numerical values you want to track over time, e.g. amount of storage used by a Company

  2. "Sync direction" - to enable the sync, set this to "Receive from Provider"

    • It's only possible to have usage data sync from Snowflake, not to Snowflake

  3. "Model in Snowflake" - select the table or view in Snowflake containing the data to import

  4. "Batch size" - define 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

  5. "Run once in" - choose how frequently you want the integration to fetch data from the Snowflake warehouse. This has the option to fetch every:

    • "5 minutes"

    • "10 minutes"

    • "30 minutes"

    • "1 hour"

    • "Every day at:" - a specific hour of the day - choose from any between 00:00 and 23:00

  6. "Key in Snowflake" - here you specify which Snowflake column we'll use as a unique ID for the incoming rows of values

    • It needs to be numeric, and incrementing (increasing in value) for each new row added - this is so we can identify which rows are new

    • Note that with usage data (as opposed to CRM data), you don't need to specify a key/ID on the Planhat side

  7. "Field mappings" - here you can map User Activity or Custom Metric properties to columns in Snowflake

    • This is easy as you can choose from dropdown menus

    • You can use the Planhat API docs for property details, or refer to the examples below for inspiration

  8. Once you've completed the form, save your configuration by clicking "Save Section"

  9. If you want to connect another Snowflake table/view to User Activities or Custom Metrics, simply click the blue "Add New Section" button at the bottom, and repeat the steps you've just gone through

  10. The integration will automatically sync in the usage data at the frequency you set. (There is no "Full sync" button for the usage data tab of the integration.) So sit back and watch your usage data sync into Planhat like magic!

Here are a couple of examples showing what a completed (and saved) sync section can look like:

Click the images to view them enlarged

Next steps

  • For more information on field mapping of "Company Id" to use different ID types in Snowflake, see here

  • If you'd like to learn how to map "VARIANT" columns from Snowflake to Planhat, check out our guide here

  • If you'd like additional troubleshooting advice when setting up the Snowflake integration, view this article

Did this answer your question?