Comment on page
Snowflake Connector: Guide
To be able to install the script, you need to have permissions to use the
ACCOUNTADMINrole on your Snowflake instance.
// 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)
// 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:
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
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
orghere 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_slugis a unique identifier per partner, you will receive this in the invitation flow email.
dcn_account_locator_idis 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
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.
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
This is where one can run a specific match with a partner.
- 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_runfunction uses the role that is created for this partnership to stage data from
source_tablein 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
SELECTpermission on all existing and future tables in
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