Authenticate Snowflake

Use this page for DataSyncs import (Snowflake → HockeyStack) and export (HockeyStack → Snowflake).

Do the shared steps first, then only the grant block for the direction you need. If you need both, use one user/role and run both grant blocks.

Private key authentication is recommended.

---

Shared setup

1. Create a role and user

CREATE ROLE HOCKEYSTACK_DATASYNCS_ROLE;

CREATE USER hockeystack_datasyncs_user
  PASSWORD = 'your_secure_password'   -- omit if using key-pair auth
  DEFAULT_ROLE = 'HOCKEYSTACK_DATASYNCS_ROLE'
  MUST_CHANGE_PASSWORD = FALSE;

GRANT ROLE HOCKEYSTACK_DATASYNCS_ROLE TO USER hockeystack_datasyncs_user;

If using a key pair, assign the public key to the user:

ALTER USER hockeystack_datasyncs_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

2. Warehouse, database, and schema access

GRANT USAGE ON WAREHOUSE your_warehouse TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;
GRANT USAGE ON DATABASE your_database TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;
GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

USAGE on the warehouse is enough when auto-resume is enabled (Snowflake’s default).

For export, a dedicated schema (for example HOCKEYSTACK_EXPORTS) is the usual pattern so HockeyStack is not writing into an analytics schema you already use.

3. 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

4. IP Whitelisting (optional)

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

• 35.157.54.242 *

• 3.123.202.193 *

• 3.69.98.171 *

• 3.125.90.48 *

• 63.186.65.19

• 18.158.230.177

• 3.121.131.19

IPs with * will be deprecated soon due to migration to new infrastructure for stronger security and reliability. If you are setting up IP whitelisting now, please use the unstarred ones.

5. 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

Import only (Snowflake → HockeyStack)

HockeyStack only reads. Grant SELECT on the source table (or on every table in the schema if you will import more than one):

GRANT SELECT ON TABLE your_database.your_schema.your_table
  TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

-- optional, if you will import multiple tables from this schema:
GRANT SELECT ON ALL TABLES IN SCHEMA your_database.your_schema
  TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA your_database.your_schema
  TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

Timestamp column for incremental import

We index incremental pulls on a timestamp column. Name it added_at, or map another timestamp column during setup. If you need to add it:

ALTER TABLE your_table ADD COLUMN added_at TIMESTAMP_NTZ;
UPDATE your_table SET added_at = your_existing_timestamp_column;

unique_id (properties imports only)

Needed only when importing custom properties (Outreach calls, app/user data, etc.). Website and custom action imports do not need this.

Use an existing unique CRM id, or generate one:

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

or

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

---

Export only (HockeyStack → Snowflake)

You do not add added_at or unique_id yourself. HockeyStack writes those.

What the export job does in your account:

1. Creates the destination table if it does not exist

2. Adds columns later if the export schema grows (new touchpoints/stages)

3. Creates a temporary stage, PUTs gzipped CSV, then COPY INTO

4. MERGEs on unique_id (raw-actions exports append instead)

5. TRUNCATEs the destination table if a re-sync is needed

Test Connection checks that the role can connect, list tables in the schema, create a temporary stage, and create a temporary table.

Recommended: let HockeyStack create the table

GRANT CREATE TABLE ON SCHEMA your_database.your_schema
  TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

GRANT CREATE STAGE ON SCHEMA your_database.your_schema
  TO ROLE HOCKEYSTACK_DATASYNCS_ROLE;

CREATE TABLE is for the permanent destination table. CREATE STAGE is for the session temp stage used to load files. Objects this role creates are owned by the role, so insert/update/truncate/alter follow automatically.

Do not use a managed-access schema unless a schema admin will grant privileges after we create the table.

---

If you need both import and export

Same user and role is fine. Run the shared SQL, then both grant blocks, ideally with:

• Import tables in a read-only schema (SELECT)

• Export table in a separate write schema (CREATE TABLE + CREATE STAGE)