Search documentation...

K

Snowflake

Hundreds of industry leaders use Hightouch to turn Snowflake into a marketing, sales, success and operational engine

You can now connect Snowflake to Hightouch instantly using Snowflake Partner Connect.

Create the Warehouse connection in Hightouch

You'll need to allowlist Hightouch's IP addresses to allow our systems to contact your warehouse. Reference our docs to determine which IPs you need to allowlist.

To connect Hightouch to your Snowflake Warehouse, we recommend creating a user specifically provisioned with read-only access to the tables and schemas required. Write access is required if you plan on using Warehouse Planning or Sync Logs.

The method used will vary depending on your particular implementation details, but the SQL snippet below can be used to expedite the process to create a service account along with necessary roles and permissions.

Please Note:

  • If you want Hightouch to access multiple databases, please run the snippet multiple times changing ht_database each time.
  • If you want Hightouch to have only read-only access, omit the last line of the query.
-- Edit the following variables
set ht_username='HIGHTOUCH_USER';
set ht_password='<a secure password>';
set ht_first_name='Hightouch';
set ht_last_name='User';
set ht_default_warehouse='<warehouse>';
set ht_database='<database>';
set ht_default_namespace='<database.schema>';
set ht_default_role='HIGHTOUCH_ROLE';
set ht_comment='Used for Hightouch integrations';

-- Set role for grants
USE ROLE ACCOUNTADMIN;

-- Create a role for Hightouch
CREATE ROLE IF NOT EXISTS identifier($ht_default_role)
COMMENT = $ht_comment;

-- Create Hightouch's user
CREATE USER IF NOT EXISTS identifier($ht_username)
PASSWORD=$ht_password
FIRST_NAME=$ht_first_name
LAST_NAME=$ht_last_name
DEFAULT_WAREHOUSE=$ht_default_warehouse
DEFAULT_NAMESPACE=$ht_default_namespace
DEFAULT_ROLE=$ht_default_role
COMMENT=$ht_comment;


-- Grant permissions to the role
GRANT ROLE identifier($ht_default_role) TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE identifier($ht_default_warehouse) TO ROLE identifier($ht_default_role);
GRANT ROLE identifier($ht_default_role) TO USER identifier($ht_username);

GRANT USAGE ON DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);

-- (Optional) Grant write access for Warehouse Planning & Sync Logs
GRANT CREATE SCHEMA ON DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);

If you are using Permifrost, you can configure the role/user as follows:

roles:
  - ht_readonly_role:
      warehouses:
        - <warehouse>
      privileges:
        databases:
          read:
            - <database>
        schemas:
          read:
            - <database>.*
        tables:
          read:
            - <database>.*.*

users:
  - ht_user:
      can_login: yes
      member_of:
        - ht_readonly_role

Check out our CircleCI orb to make running Permifrost from a Github repo easy.


Once you have your role and user provisioned, you can add them to Hightouch:

  1. In Hightouch, go to Sources or click https://app.hightouch.com/sources

    Selecting Add Source

  2. Click "Create Source"

    Select Connect Source

  3. Select Snowflake

  4. For Account, enter your Snowflake account. Usually, this is in the format <identifier>.<region>.<cloud provider>, e.g. abc123.us-east-2.aws). See the Snowflake docs for more information.

  5. For Database, enter your Snowflake database name.

  6. For Username, enter your Snowflake username, e.g. ht_user

  7. For Password, enter your Snowflake user's password.

  8. For Role, enter the Role that Hightouch should use for queries, e.g. ht_readonly_role. Use DEFAULT to use the default role. A default role must be set for the user for this to work.

  9. Click "Test" to test the connection. Hightouch is able to successfully connect, click the "Complete" button at the bottom of the page

RSA Authentication

Generate a private key and public key by running the following commands in your terminal:

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Then in Snowflake, execute an ALTER USER command to assign the public key to your Hightouch Snowflake user:

alter user ht_user set rsa_public_key='MIIBIjANBgkqh...';

Finally, in the Authentication Method of the Snowflake Source settings, select RSA Private Key and drop your private key you just generated into the Private Key File field

Snowflake RSA

    Need help?

    Our team is relentlessly focused on your success. We're ready to jump on a call to help unblock you.

    • Connection issues with your data warehouse?
    • Confusing API responses from destination systems?
    • Unsupported destination objects or modes?
    • Help with complex SQL queries?

    Feature Requests?

    If you see something that's missing from our app, let us know and we'll work with you to build it!

    We want to hear your suggestions for new sources, destinations, and other features that would help you activate your data.

On this page

Create the Warehouse connection in HightouchRSA Authentication

Was this page helpful?