Snowflake Connector: Guide

Quickstart

After having received and accepted a Snowflake Connector invitation, you will be required to follow the installation steps below in your Snowflake account to complete the connection.

Step 1: Installing the Script

To be able to install the script, you need to have permissions to use the ACCOUNTADMIN role on your Snowflake instance.

Open SnowSight UI (or Snowflake's Classical UI) and paste the script found here: https://github.com/Optable/optable-snowflake-partner/blob/main/src/sql/snowflake_partner.sql

The script will create a database optable_partnership, a warehouse optable_partnership_v1_setup and several user defined functions and procedures.

Step 2: Establish Partnership & Grant Permissions

// SPECIFY ROLE
USE ROLE ACCOUNTADMIN;

// SPECIFY WAREHOUSE
USE WAREHOUSE OPTABLE_PARTNERSHIP_V1_SETUP;

// ESTABLISH PARTNERSHIP CONNECTION
call optable_partnership_v1.public.partner_connect('', '', '');

// GRANT PERMISSIONS (partnership_slug, database_name, schema_name)
call optable_partnership_v1.public.grant_permission('', '', '');

Step 3: Create & Run Match

// MATCH CREATE (partnership_slug, match_name)
call optable_partnership_v1.public.match_create('', '');

// MATCH LIST (partnership_slug)
call optable_partnership_v1.public.match_list('');

// MATCH RUN (partnership_slug, match_id, source_table)
call optable_partnership_v1.public.match_run('', '', '');

Step 4: Get Match Results

// MATCH RESULT (partnership_slug, match_id)
call optable_partnership_v1.public.match_get_results('', '');

User Defined Functions & Procedures

It is important to note that when using any of the procedures listed below, it needs to be prefixed by the following: optable_partnership_v1.public.*

See example: optable_partnership_v1.public.help()

version()

A utility procedure to get the current version of the installed script.

help()

A utility procedure to get help for using the script.

partner_connect(org VARCHAR, partnership_slug VARCHAR, dcn_account_locator_id VARCHAR)

To run this function, you need to have permission to use both ACCOUNTADMIN and SECURITYADMIN roles.

This is called to connect with an Optable DCN partner (accepting the invite). Upon a successful partnership invite, and once you enter your snowflake account information, you will receive a downloadable script by email that contains the exact call to partner_connect.

• org here is the name of the organization that has invited you, you can change this argument as you wish, it is preferred to use a name that helps you identify the partner.

• partner_slug is a unique identifier per partner, you will receive this in the invitation flow email.

• dcn_account_locator_id is the locator ID of the snowflake account that is used by the Optable DCN customer to perform matches with you, which can be found in the invitation email.

Upon a successful partnership setup, the following resources will be created in your account:

• Two databases for this partnership, with proper shares to enable matches with the Optable DCN partner.

• A role that is used to perform all operations for this partnership (create match, run match, etc.)

The role by default only has permission to access/execute to the functions introduced for this partnership, so if you need to run any matches, you need to grant the partnership role necessary permissions (more on this later).

partner_list()

Lists all the partners currently connected to your snowflake account. This will also return the roles assigned to each partnership.

partner_disconnect(partnership_slug VARCHAR)

To run this function, you need to have permission to use ACCOUNTADMIN role.

This is used to end a partnership with an Optable DCN. partnership_slug is the same unique ID that was used during partner_connect ​

match_create(partnership_slug VARCHAR, match_name VARCHAR)

Creates a match object with a descriptive name. Every match can have infinite number of match runs. A match run is where data exchange happens with an Optable DCN partner. ​

• partnership_slug is the same slug that is used during partner_connect
• match_name can be any descriptive name of the match. ​

Once the match is created, a match ID is returned by the function, that can be used to run the match.

match_list(partnership_slug VARCHAR)

Lists all the matches that are created for a specified partner using the partnership_slug.

match_run(partnership_slug VARCHAR, match_id VARCHAR, source_table VARCHAR)

This is where one can run a specific match with a partner.

• partnership_slug is the same slug that was used during partner_connect
• match_id is the id that is returned by match_create and match_list
• source_table is the fully qualified name of the table that we intend to use to match with the Optable DCN partner.

The source_table should follow the same tabular data format as mentioned here, with the following differences: ​

• Scopes is not supported

• Traits is not supported

• We do not normalize phone numbers. If a phone number is not pre-normalized with (E.164), then we won't to do the normalization for you. But we still hash the phone numbers in SHA256 regardless if they are not already hashed.

match_run function uses the role that is created for this partnership to stage data from source_table in a temp table used solely for the purpose of a match run. That means the role needs to have permission to read (SELECT) data from the source_table. For this purpose, we provide a utility routine/function to grant necessary permissions for partnership roles, as described below.

grant_permission(partnership_slug VARCHAR, database_name VARCHAR, schema_name VARCHAR)

This is a utility function to grant necessary permissions to the partnership role (identified by partnership_slug) to have SELECT permission on all existing and future tables in database_name and schema_name. ​ The grants are as follows: ​

GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE <partnership_role>;
GRANT USAGE ON DATABASE <database_name> TO ROLE <partnership_role>;
GRANT USAGE ON SCHEMA <schema_name> TO ROLE <partnership_role>;

match_get_results(partnership_slug VARCHAR, match_id VARCHAR)

A utility to fetch the result of a match.

• partnership_slug is the same slug that was used during partner_connect
• match_id is the id that was returned by match_create

cleanup_profiles(partnership_slug VARCHAR)

This function removes all the profiles that were part of previous match runs, and no longer needed. This only deletes them in the staging table that is used for matches only. This is a utility that can run periodically to garbage collect unnecessary data in the staging table.

• partnership_slug is the same slug that was used during partner_connect