Authenticate Snowflake

How to help us optimize data retrieval

Given that Snowflake is a table based warehouse, we required a column called added_at which will be used to index your data. If necessary, this column can be generated as a copy of any other date column in your table.

1. Create a Snowflake User for DataSyncs

• Run the following SQL in Snowflake:

CREATE USER datasyncs_user PASSWORD = 'your_secure_password'
DEFAULT_ROLE = 'HOCKEYSTACK_DATASYNCS_ROLE'
MUST_CHANGE_PASSWORD = FALSE;

1. Assign a Role and Permissions

• Create a role for DataSyncs:

CREATE ROLE HOCKEYSTACK_DATASYNCS_ROLE;
GRANT USAGE ON DATABASE your_database TO ROLE DATASYNCS_ROLE;
GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA your_database.your_schema TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

• Assign the role to the user:

GRANT ROLE HOCKEYSTACK_DATASYNCS_ROLE TO USER hockeystack_datasyncs_user;

1. Enable Query Execution

• Grant the ability to create and run queries:

GRANT USAGE ON WAREHOUSE your_warehouse TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

---

Gather Connection Information for DataSyncs

• Account Identifier (e.g., account-identifier) (does not include the .snowflakecomputing.com part)

• User: hockeystack_datasyncs_user

• Password / Private Key & Private Key Passhphrase (if applicable)

  • We can authenticate using either a User & Password or a User & Private Key depending on your preference. (Private Keys are recommended for better security*)

• Role: DATASYNCS_ROLE

• Warehouse: your_warehouse

• Database: your_database

• Schema: your_schema

• Table: the specific table that you are looking to import data from

IP Whitelisting (optional)

If you have IP restrictions in place, whitelist the following IP addresses to allow HockeyStack’s workers to communicate with Snowflake:

• 18.184.228.143

• 18.192.106.69

• 35.157.54.242

• 3.69.98.171

• 3.125.90.48

Configure an account limit with Resource Monitors (optional)

Larger imports (>100k rows) can increase compute costs. Setting a quota can help ensure no unexpected compute costs:

• Refer to Snowflake's Resource Monitors documentation to see which options are available to as limits you can set on the service account you are providing to DataSyncs

Generating a Unique ID column (Properties imports)

When importing custom properties (such as Outreach Calls, User based App data, etc) a unique_id is needed to help us understand the data grain (the set of columns that define the uniqueness of a row).

This Unique ID can be an ID from a CRM, assuming that it is unique for each record in the dataset. If not, this ID can be generated from a combination of columns.

For example, if column1 , column3 , and column5 combined define the uniqueness of a row- you can create a unique_id by doing this:

ALTER TABLE your_table 
ADD COLUMN unique_id STRING 
DEFAULT CONCAT(column1, '_', column3, '_', column5);

Or, if you prefer a randomly generated ID, you can do:

ALTER TABLE your_table ADD COLUMN unique_id STRING DEFAULT GENERATE_UUID();