Comment on page
Snowflake Connector: Guide
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.
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.// 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('', '', '');
// 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('', '', '');
// MATCH RESULT (partnership_slug, match_id)
call optable_partnership_v1.public.match_get_results('', '');
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()
A utility procedure to get the current version of the installed script.
A utility procedure to get help for using the script.
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).
Lists all the partners currently connected to your snowflake account. This will also return the roles assigned to each partnership.
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
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.
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.
Lists all the matches that are created for a specified partner using the
partnership_slug
.This is where one can run a specific match with a partner.
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.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>;
A utility to fetch the result of a match.
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.
Last modified 30d ago