Summary

The first stage of setting up the Snowflake integration is preparing the Snowflake warehouse and then completing the authentication in the integration in Planhat
This article takes you through the steps in Snowflake, including creating a service user
It's then easy to enter your Snowflake details in the integration and get connected - we show you how

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:

Setting up the Snowflake integration - an overview
Preparing Snowflake and connecting the integration - part 1 of the main instructions ⬅️ You are here
Data mapping in the Snowflake integration - part 2 of the main instructions
Snowflake integration - using different Company IDs
How to map Snowflake VARIANT columns
Snowflake troubleshooting guide

Article contents

Introduction
Setting up a new Snowflake integration, with a service user and key-pair authentication
Migration from previously set up person user and OAuth to service user and key-pair authentication

Introduction

This is a technical deep-dive article

Read on if you're a Snowflake Admin / CS Ops / Tech Specialist etc. and are preparing Snowflake and authenticating 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.

Planhat's Snowflake integration makes it easy for you to sync time-series usage data into Planhat, and sync CRM data (Company, End User and License etc.) bidirectionally.

There are two main stages to setting up the Snowflake integration.

The first stage, described in this article, is preparation in Snowflake and authentication in the integration. The main steps we'll go through here are:

A. Setup steps in Snowflake: preparing the Snowflake warehouse

Create a service user and a role for it with the necessary permissions
Generate the necessary keys for authentication and assign them to the service user
Create a database and schema if they don't exist yet
Create a default warehouse and give the role access
Create a files stage and grant permissions
For CRM data, activate "change_tracking" for the tables or views

B. Connecting Snowflake to Planhat - in the Snowflake integration in Planhat:

Enter Snowflake authentication information
Click "Authenticate Now" in the integration
Turn on the "Enable the Snowflake integration" toggle switch
Enter your service user in the "User" box, and then select your warehouse, database and schema from the dropdown menus

The next stage is data mapping, which we cover in the next article in the series.

📌 Important to note

Previously, the instructions in this article involved a "person"-type Snowflake integration user authenticating via OAuth and a password. Due to security enhancements in Snowflake, the recommended method is now via a "service"-type user and key-pair authentication, so the main instructions below now describe this method.

At the end of this article, we also include instructions for converting existing Snowflake integrations from the person/OAuth/password setup to the service/key-pair method, if you already set up your integration and would like to switch, as we recommend. The older method not only requires intermittent manual reauthorization when refresh tokens expire (every 90 days by default), but Snowflake's upcoming (at time of writing) security policy changes will also enforce setting up multi-factor authentication (MFA) methods for this user type, which makes this authentication method less straightforward.

Setting up a new Snowflake integration, with a service user and key-pair authentication

A. Preparation steps in Snowflake

Before you can connect Snowflake to Planhat's integration, there are some steps to carry out in Snowflake to prepare the Snowflake warehouse.

Make sure you have Snowflake admin access before carrying out the following steps.

Create a new worksheet. This is where you'll prepare your Snowflake warehouse for integration. In that worksheet ...
Create a role to be used for the integration:
```
CREATE OR REPLACE ROLE PHCLIENT;
```

Create a service user and assign it to the role:

CREATE OR REPLACE USER PHUSER
type = SERVICE
default_role = PHCLIENT;

GRANT role PHCLIENT to USER PHUSER;

Generate a private key and public key following the official Snowflake documentation:

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Assign the public key to the service user you created in step 3 (as described in the Snowflake documentation) - for example:
```
$ cat rsa_key.pub
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqh…
-----END PUBLIC KEY-----
```
In your Snowflake worksheet run the following SQL command:
```
ALTER USER PHUSER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
```
📌 Important to note: Public keys and private keys are unique. Use your own public key here.
Create a database and a schema if they do not exist yet:
```
create database <DATABASE>

create schema <SCHEMA>
```
... where < > denotes a new database or schema to be named. For example, you could enter create database planhat, where planhat replaces <DATABASE>.
- You can tell whether a database and/or schema exist from within Snowflake, by viewing (or clicking on) the dropdown on the top left of the worksheet. The image below shows an instance where both a database (PH_ALBERTO) and a schema (PUBLIC) do exist.
Grant user permissions to execute statements on desired tables (or views):
```
GRANT select, insert, update on all tables
 in schema <DATABASE>.<SCHEMA> to role PHCLIENT; 

GRANT select, insert, update on future tables
 in schema <DATABASE>.<SCHEMA> to role PHCLIENT; 

GRANT usage on database <DATABASE> to role PHCLIENT; 

GRANT usage on schema <DATABASE>.<SCHEMA> to role PHCLIENT;
```
- As above, < > denotes your choice of existing database or schema - name them here. For example, if my <DATABASE> was PH_ALBERTO and my <SCHEMA> was PUBLIC, I would replace <DATABASE>.<SCHEMA> with PH_ALBERTO.PUBLIC
- If you want to map views to Planhat instead of tables, use the same queries as above, but replace "tables" with "views"

Create a default warehouse:

Planhat expects Snowflake to have a default warehouse named planhat_wh to allow it to request basic parameters. Create this warehouse as follows:

CREATE or REPLACE warehouse planhat_wh 
warehouse_size = XSMALL 
auto_suspend = 600 
auto_resume = TRUE 
initially_suspended = TRUE 
comment = 'Default warehouse for Planhat';

Allow the integration role to use the default warehouse:

GRANT operate ON warehouse planhat_wh to role PHCLIENT; 

GRANT usage ON warehouse planhat_wh to role PHCLIENT;

Create a files stage:
```
CREATE or REPLACE stage <DATABASE>.<SCHEMA>.PH_STAGE 
encryption = (type = 'SNOWFLAKE_SSE') 
copy_options = (on_error='skip_file') 
file_format = (
 field_delimiter = none
 record_delimiter = '\n' 
) 

copy_options = (
 on_error = continue);
```
- As before, note that < > denotes an existing database or schema to be named. For example, using the example above, instead of <DATABASE> I would say PH_ALBERTO and instead of <SCHEMA> I would say PUBLIC
- Stages are used to upload files with data when importing data from Planhat. Stages are also used to further extract files to tables

Grant permissions to stages:

GRANT read on stage <DATABASE>.<SCHEMA>.PH_STAGE to role PHCLIENT;

GRANT write on stage <DATABASE>.<SCHEMA>.PH_STAGE to role PHCLIENT;

For CRM data: activate change_tracking on tables or views:
```
ALTER table <DATABASE>.<SCHEMA>.<TABLE> SET change_tracking = true;
```
- If you are mapping views rather than tables, just replace table with view, and <TABLE> with the name of the relevant view, in the above query
- Tables containing CRM data - such as Companies, End Users and Licenses, etc. as opposed to time-series usage data - should have change_tracking enabled. This is because the automated hourly partial sync of CRM data requires this
- You'll need to enable change_tracking for all the CRM-type tables/views in Snowflake you want to sync data from, so simply repeat the command above, using a different <TABLE> input each time
Run all the SQL code you just wrote into the worksheet, and leave it open while you switch to Planhat - we'll be coming back later on!

B. Authenticating the Snowflake integration in Planhat

Now you've prepared Snowflake, you can connect it to the integration in Planhat.

In your Planhat tenant, go to the Operations Module (icon at the bottom in the black left-hand bar), ensure the "INTEGRATE" tab is selected, and then click on the Snowflake integration
- You can find the Snowflake integration more quickly by typing in the search box, or clicking into the "Data Warehouse" section, both shown below
- You will see this form to complete for the authentication:
Select "Key Pair" in the "Authentication method" dropdown
In the "API Base URL with Base Path" field (see screenshot above), fill in your Snowflake Account URL
- This could be something like https://<orgname>-<account_name>.snowflakecomputing.com
- You should be able to find this value in your account details in Snowflake - click on your avatar in the bottom-left corner, then select "Account" in the middle, and you'll see a "View account details" link to click
- In the opened modal you'll be able to find this value as "Account/Server URL"
- Copy this value and paste it into the "API Base URL with Base Path" field in Planhat. Make sure you include the "https://" prefix at the beginning of the URL
Click the "Authenticate Now" button (blue plug icon)
An authentication modal will pop up, in which you'll need to provide your private key file
- Click "Import Private key" to open a file browser, where you should select the previously generated RSA private key file (rsa_key.p8)
- If you generated an encrypted private key, you'll also need to fill in the passphrase for the private key so Planhat can use it.
- When all set, click on the "Set up" button
- When successfully connected, you will see that the "Authentication" box in the integration turns green and shows a checkmark (tick)
You will now see the "Enable the Snowflake integration" toggle switch at the top - turn this on
Enter your user in the "User" box, and then select your warehouse, database and schema from the dropdown menus that appear next to it

🎉 Congratulations - your connection is now fully set up! You are now ready to start the data mapping, which we go through in the next article.

Next steps

If you experience any issues in the authentication/setup steps in this article, check out our troubleshooting advice here
Once you've followed the instructions in this article and connected Snowflake to the integration in your Planhat tenant, the next step is to configure the data mapping. Learn how to do this in our next article in the series

Migration from previously set up person user and OAuth to service user and key-pair authentication

Previously (before July 2025), when you set up the Snowflake integration in Planhat, the only option to authenticate was through through OAuth and a password with an integration "person" user. This setup not only requires intermittent manual reauthorization when refresh tokens expire (every 90 days by default), but also Snowflake's upcoming (at time of writing) security policy changes will also enforce setting up multi-factor authentication (MFA) methods for these person users, which makes this authentication method less straightforward.

If you already set up the Snowflake integration using the older method, we recommend that you migrate from OAuth to the new key-pair authentication method. The instructions below guide you on how to do this. You will note there are some similarities with the method we described above for setting up key-pair authentication from scratch.