Skip to main content

Preparing Snowflake and connecting the integration

Part 1 of the the setup steps for the Snowflake integration

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

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 an integration user and role

  • 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:

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 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 an overview 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

  • Enable OAuth

  • Create an integration user and a role for it with the necessary permissions

  • 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, and log into Snowflake using the details set up earlier in the process

  • Turn on the "Enable the Snowflake integration" toggle switch

  • Enter your 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.

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.

  1. Create a new worksheet. This is where you'll prepare your Snowflake warehouse for integration. In that worksheet ...

  2. Enable OAuth with the following SQL command:

    USE role accountadmin; 

    CREATE SECURITY INTEGRATION PHCLIENT 

    TYPE=OAUTH 
    OAUTH_CLIENT=CUSTOM OAUTH_REDIRECT_URI='https://api.planhat.com/connectiondata/oauth2/authresult' 
    OAUTH_CLIENT_TYPE = CONFIDENTIAL 
    ENABLED = TRUE 
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE 
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

    • OAUTH_REFRESH_TOKEN_VALIDITY is set to its feasible maximum: 90 days (7776000). This means that Snowflake's connection to Planhat will expire every 90 days; you will need to reauthenticate when this happens. If you want the connection to be more permanent, contact Snowflake Support, who can extend token validity

  3. Create a role to be used for the integration:

    CREATE OR REPLACE ROLE PHCLIENT;

  4. Create an integration user and assign it to the role:

    CREATE OR REPLACE USER PHUSER 
    password = 'qwerty12345' 
    default_role = PHCLIENT 
    must_change_password = false; 

    GRANT role PHCLIENT to USER PHUSER;

    • If you use the exact SQL code above, you will set the password for the user with username PHUSER to qwerty12345. You can alternatively set this to whatever you like, but make a note of it as you'll need it later, when you connect Snowflake to Planhat

  5. 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.

  6. 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"

  7. 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';

  8. 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;

  9. 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

  10. 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;

  11. 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

  12. 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.

  1. 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:

  2. For the Client ID and Client Secret, replace these with your values from Snowflake

    • To get these, return to your Snowflake worksheet, and run the following SQL command:

      SELECT system$show_oauth_client_secrets('PHCLIENT');

    • This will generate a table containing a single string of the form: {"OAUTH_CLIENT_SECRET_2":"u87WvPRWwgyWDE76bXyRc6GbpXKAngF8aGcjrwq0o4s=","OAUTH_CLIENT_SECRET":"XyLTcNGfgR4LyfrhSkqmB0LUYnpbYOnmSKXFFHQa7ak=","OAUTH_CLIENT_ID":"itFTUpkCwLda13pKf4jvQcBoT9c="}

    • Copy your value of "OAUTH_CLIENT_ID" and paste it into the "Client ID" box in the integration (replacing the "xxxx")

    • Copy your value of "OAUTH_CLIENT_SECRET" and paste it into the "Client Secret" box in the integration (replacing the asterisks)

  3. For each of the 3 URL fields at the bottom, replace "xxx" in with your specific Snowflake account

    • This could be something like yxa37312.us-east-1

    • There are two alternative methods you can use to get this:

      • Take a look at the URL when you log into Snowflake. Copy everything that comes between https:// and .snowflakecomputing.com. For example, in the URL https://sx36992.europe-west4.gcp.snowflakecomputing.com/api/statements, replace the xxx with sx36992.europe-west4.gcp

      • Alternatively, in your Snowflake worksheet, you can run the following SQL command:

        DESC security integration PHCLIENT;

        This will generate a table. Look at the URLs in the 9th-12th rows of the property_value column to find your account value. Remember, you just need the part of the URL between https:// and .snowflakecomputing.com

  4. Click the "Authenticate Now" button (blue plug icon)

  5. A Snowflake login window will pop up

    • Enter the username you created earlier in step 4 of the "A. Preparation steps in Snowflake" section above (e.g. PHUSER) ...

    • ... along with the password set earlier in the same step (e.g. qwerty12345)

    • Click the "Sign in" button

    • Click the "Allow" button on the next screen of the modal ("PHCLIENT would like to access your Snowflake account")

    • When successfully connected, you will see that the "Authentication" box in the integration turns green and shows a tick/check mark

  6. You will now see the "Enable the Snowflake integration" toggle switch at the top - turn this on

  7. 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

Did this answer your question?