Planhat's bi-directional integration with Snowflake allows you to get your customer data into Planhat seamlessly, and push customer data back and forth continuously. This ensures that your systems and teams are always on the same page, with the latest information.

In this set-up guide, we're going to cover the types of data that you can sync between Planhat and Snowflake, how to sync them, what the syncing process looks and feels like, and how you can adjust the integration to suit your needs.

Contents

  1. Preparing the Snowflake Warehouse

  2. Creating the Connection & Configuring in Planhat


Preparing the Snowflake Warehouse

Before we can connect Snowflake to Planhat, we need to prepare the Snowflake warehouse. Make sure you have account admin access before attempting the following steps.

In Snowflake...

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

  • 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_ALLOW_NON_TLS_REDIRECT_URI = TRUE
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;


    🚀 Quick tip: 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, requiring you to sign in with your username and password via Profile > Connections > Snowflake > "Enabled". If you want the connection to be more permanent, contact Snowflake Support, who can extend token validity.

  • Create a role to be used for integration

    CREATE OR REPLACE ROLE PHCLIENT;

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

    📌 Note: look carefully at the SQL code above - you just set the password for the user with username phuser to qwerty12345. You can set this to whatever you like, but you'll need this later, when you finalise the connection in Planhat.

  • 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 e.g., create database planhat, where planhat replaces <DATABASE>.

    You can tell whether a database and/or schema exist right 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

    📌 Note: as above, < > denotes an existing database or schema to be named. In this case, my <DATABASE> is PH_ALBERTO and my <SCHEMA> is PUBLIC, meaning <DATABASE>.<SCHEMA> is PH_ALBERTO.PUBLIC.

    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;

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

    📌 Note: you can always change this to another warehouse via the Planhat "Integrations" page under: Profile > Integrations > Snowflake

  • 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

    Stages are used to upload files with data when importing data from Planhat. Stages are also used to further extract files to tables.

    As before, note that < > denotes an existing database or schema to be named. Again, using the example above, my <DATABASE> is PH_ALBERTO and my <SCHEMA> is PUBLIC.

    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);

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

  • [Only for CRM-type tables] Prime the tables to export data to Planhat

    Tables containing CRM data, like companies and endusers, should have change_tracking enabled, as follows:

    ALTER table <DATABASE>.<SCHEMA>.<TABLE> SET change_tracking = true;

    🚀 Quick tip: you'll need to enable change_tracking for all the CRM-type tables in Snowflake you want to export data from, so simply repeat the command above, using a different <TABLE> input each time. 📌 Remember! Whenever you enable change_tracking for any table, you need to press the "Full-sync" button in the Snowflake integration in Planhat.

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 for more!).


Creating & Configuring the Connection

Great stuff. Now your Snowflake instance is prepped and ready to go, it's time to show some love to Planhat's native configurator. The good news is, there's isn't much more SQL 🥳 !

Create the Connection

  • Head to the "Connections" page, via Profile (in the bottom left corner) > Connections and add a new connection, via the "Set up Connection" button

    📌 Note: you can add any number of connections here, and every integration instance configuration will be assigned to its own connection.

  • The below popup will now be visible. Populate the following fields as follows:

    • Name: < Any Name >

    • Description: < Description for Connection >

    • Scope: refresh_token

  • Go back into your Snowflake Worksheet and run the following SQL command:

    DESC security integration PHCLIENT;

    This will generate a table with 19 rows and 4 columns. The 3rd column is property_value, which is what we're interested in. Map the following values from the query result, into Planhat's connection configurator:

    • Authorize URL:

      https://<ACCOUNT>.snowflakecomputing.com/oauth/authorize (using the <ACCOUNT> shown in the the property_value column of the 9th, 10th, 11th or 12th rows)

    • Token URL:

      https://<ACCOUNT>.snowflakecomputing.com/oauth/token-request (using the <ACCOUNT> shown in the the property_value column of the 9th, 10th, 11th or 12th rows)

    • API Base URL with Base Path:

      https://<ACCOUNT>.snowflakecomputing.com/api/statements (using the <ACCOUNT> shown in the the property_value column of the 9th, 10th, 11th or 12th rows)

      🚀 Quick tip: a quicker way of completing the above, without an SQL command, is to take a look at the URL when you login to Snowflake. Everything that comes between https:// and .snowflakecomputing.com counts as an <ACCOUNT>. For example in the URL https://sx36992.europe-west4.gcp.snowflakecomputing.com/api/statements, the <ACCOUNT> to insert into the above expressions is sx36992.europe-west4.gcp

  • Return to the Snowflake Worksheet one last time, 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":"u87WvPRWwgyWDE76bXuRc6GapXKAngF8aGcjrwq0o4s=","OAUTH_CLIENT_SECRET":"XyLTcNGfgR4LyfrhSkqmB0LUYnpbYOnmSKPFFHQa7ak=","OAUTH_CLIENT_ID":"itFTUckCwLda12pKf4jvQcBoT9c="}

    We're interested in...

    • Client Id:

      OAUTH_CLIENT_ID

    • Client Secret:

      OAUTH_CLIENT_SECRET

    So extract these values from the Snowflake query result and input them into the connector configurator in Planhat.

    When you're done, the configurator should look a bit like this...

  • Click "Add" to complete the connection, then click the "Enabled" switch

This switch will open a new tab with a login and password screen.

  • Enter the username selected earlier (see the 3rd code block down from here), phuser, along with the password set earlier, qwerty12345

📌 Note: if you see the error screen below, there's a mismatch between what you prepared in Snowflake, and what you connected to Planhat. You'll need to edit the Connection in Planhat, ensure there's an exact match on all inputs (most notably, the OAUTH_REDIRECT_URI, which should link to the Planhat API), and try to enable it again. If this issue persists, reach out to your Planhat TAM or CSM.

Configure the Connection

  • Head to the "Integrations" page, via Profile (in the bottom left corner) > Integrations, select "Snowflake"

    If you don't see "Snowflake" as an option, please contact your CSM and ask how to obtain access.

  • Complete the "Connection" field by inputting "Snowflake" (or whatever you named the above connection)

  • Complete the "user" field which appears by using the same user set earlier on (see the 3rd code block down from here), phuser

  • If everything has happened correctly, the Warehouses list should now appear, as below

  • Choose the desired Warehouse, Database and Schema from the pre-populated drop-downs

Now you've successfully set up the Snowflake integration, you're ready to begin configuring objects integrations 🎉. See this article next, for how it's done.

Did this answer your question?