To allow Hightouch access to Snowflake, it's best to create a user specifically provisioned with access to the required tables and schemas. You can also use a personal Snowflake login for your credentials, as long as it has the correct permissions, specifically:
Add, update, and delete (if applicable) rows in your sync's table.
View the INFORMATION_SCHEMA.COLUMNS table which is used for gathering metadata to set up the sync
You can use the following SQL template to create a service account with the necessary roles and permissions with some considerations:
This snippet provides an example of creating a service account; you may need to alter it depending on your Snowflake implementation details.
If you want Hightouch to access multiple databases, run the snippet multiple times, changing ht_database each time.
-- Edit the following variablesset 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 HightouchCREATE ROLE IF NOTEXISTS identifier($ht_default_role)
COMMENT = $ht_comment;
-- Create Hightouch's userCREATEUSER IF NOTEXISTS 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 roleGRANT 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) TOUSER identifier($ht_username);
GRANT USAGE ON DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT USAGE ONALL SCHEMAS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANTSELECTONALL TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANTSELECTON FUTURE TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANTSELECTONALL VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANTSELECTON FUTURE VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
Check out the Hightouch CircleCI orb for an Orb template to run Permifrost.
Once you've created a Snowflake service account, you're ready to set up the connection in Hightouch.
Go to the Destinations overview page and click the Add destination button. Select Snowflake and click Continue. If you're not using a tunnel, you can then authenticate Hightouch to Snowflake by entering the following fields:
Account identifier - You can find this identifier at the beginning of your Snowflake URL, for example, (https://ACCOUNT_IDENTIFIER.snowflakecomputing.com).
Warehouse - This specifies the warehouse to use when Hightouch executes queries in Snowflake.
Database - This specifies the database to use when Hightouch executes queries in Snowflake.
Username - This can be your personal Snowflake login or a dedicated user for Hightouch.
Role (optional) - Use this field to specify the role Hightouch should use when executing queries in Snowflake. If left blank, Hightouch uses the user's default role.
Account identifier format may differ based on Snowflake account age. For example, older Snowflake accounts often have identifiers that look like ACCOUNT_LOCATOR.CLOUD_REGION_ID.CLOUD, whereas newer Snowflake accounts have identifiers that look like ORGNAME-ACCOUNT_NAME.
For more details, visit Snowflake's account identifier docs.
You have two options for finalizing your credentials:
Password (recommended)
RSA key pair
The password authentication method allows for quicker setup since you only need to enter the password for the previously specified username. Hightouch also supports RSA key pair authentication for environments with enhanced security requirements.
To ensure your credentials are correct, click Test connection. This confirms if Hightouch can connect to your database by running a basic SELECT query.
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 Hightouch under the Authentication Method section, drag and drop or upload the private key you just generated into the Private key file field.
Hightouch can connect directly to Snowflake over the public internet or via an SSH tunnel. Since data is encrypted in transit via TLS, a direct connection is suitable for most use cases. You may need to set up a tunnel if your Snowflake instance is on a private network or virtual private cloud (VPC).
Hightouch supports both standard and reverse SSH tunnels. To learn more about SSH tunneling, refer to Hightouch's tunneling documentation.
Once you've set up your Snowflake destination and have a model to pull data from, you can set up your sync configuration to begin syncing data. Go to the Syncs overview page and click the Add sync button to begin. Then, select the relevant model and the Snowflake destination you want to sync to.
Hightouch supports Upsert mode using the MERGE INTO statement, with the option to delete removed rows, Insert mode using the COPY INTOstatement, and Mirror mode which will TRUNCATE the table and then use the COPY INTOstatement to insert all rows from the model.
Mirror mode will remove any existing data in the table before inserting all rows from the model
In all three modes, Hightouch uploads your mapped data to your Snowflake's staging table using the PUT file statement. When the operation is complete, Hightouch removes the staging file(s).
Insert and mirror modes create a CSV file that is required for the COPY INTO statement. When the operation is complete, Hightouch removes the file.
Use insert mode if you are only inserting rows, want to identify
individual row errors, and sync your data faster.
To match rows from your model to rows in Snowflake, Hightouch requires you to select a unique identifier in the table you are syncing to.
The model column you select must be the same as the primary key column you selected in your model.
The Snowflake column you select must be set as a UNIQUE, PRIMARY KEY column within Snowflake.
You can see columns that fit this criteria as available options in records matching section.
If there are no fields in the dropdown, you must add a unique type column to your Snowflake table. Then, click the refresh icon to access the newly created column.
You can tune the number of rows Hightouch upserts or inserts per query based on your needs and database threshold. The default is 50,000 rows per batch.
If you want to increase the sync's speed, you can increase the batch size. Keep in mind that in upsert mode, Snowflake fails the entire batch of rows if it detects any erroneous row. If you suspect that you will have many bad rows, don't use a high batch size. To avoid locks, ensure you account for your database's capacity when increasing the batch size.
To date, our customers haven't experienced any errors while using this destination. If you run into any issues, please don't hesitate to . We're here to help.
Hightouch provides complete visibility into the API calls made during each of your sync runs. We recommend reading our article on debugging tips and tricks to learn more.