How to add Pitchly as a security integration in Snowflake
The Snowflake <> Pitchly Integration enables Pitchly users to quickly map data within their Snowflake account to fields within Pitchly. Several steps must be taken by an Administrator with admin access to the Warehouse and Database within your Snowflake instance to create Pitchly as a Security Integration and authorize the communication between the systems.
Create Security Integration Pitchly
The first step is to create a new OAuth Security Integration in your Snowflake account.
From a new SQL Worksheet, the following query can be copied and run.
CREATE SECURITY INTEGRATION pitchly
TYPE=OAUTH
OAUTH_CLIENT=CUSTOM
OAUTH_REDIRECT_URI='https://platform.pitchly.com/integrations/oauth/callback'
OAUTH_CLIENT_TYPE='CONFIDENTIAL'
OAUTH_ISSUE_REFRESH_TOKENS=TRUE
OAUTH_REFRESH_TOKEN_VALIDITY=7776000
OAUTH_ENFORCE_PKCE=TRUE
COMMENT='This integration allows data to move from Snowflake to Pitchly'
ENABLED=TRUE;
Additional Information can be found on Snowflake’s documentation site.
Retrieve Client ID and Client Secret
Once the Security Integration has been created, you will be able to retrieve the Client ID and Client Secret. These values are required when configuring the integration within Pitchly. Reference our article covering how to connect Snowflake and Pitchly for more information.
You can continue working in your previous SQL Worksheet, or create a new one, as needed.
The following query can be copied and run.
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PITCHLY');
This will return a single row and column with JSON data. The JSON data will contain the Client ID and two secrets. Only one secret is required. However, Snowflake will provide two secrets to allow for token rotation.
You can re-run this query to access the information again.
Create Pitchly Role
The Pitchly <> Snowflake integration uses a special role named PITCHLY. There are three steps involved: creating the role, granting the role access and permissions, and granting usage and select permissions.
From within your SQL Worksheet, copy and run the following queries.
Create Role
CREATE ROLE PITCHLY
Assign Role Access & Permissions
GRANT ROLE PITCHLY TO GRANT ROLE ACCOUNTADMIN TO ROLE PITCHLYGRANT ALL ON ACCOUNT TO ROLE PITCHLY
The Pitchly role will need to be assigned to the user who will log into Pitchy and authenticate the connection. This does not need to be the same user who performs the field mapping steps. Pitchly users do not need to be Snowflake users, with the exception of the role indicated above.
Give Usage and Select Permissions
GRANT USAGE ON DATABASE GRANT SELECT ON TABLE GRANT SELECT ON VIEW
Usage and Select should be granted to each database and view that you want available during the Pitchly sync process.
We’d love to hear from you. If you have any feedback on product enhancements or additional questions, email the team at PitchlySupport@pitchly.com.