Empower your operations by syncing modeled data into your warehouse
|Sync Type||Description||Supported Sync Modes|
|Any data set||Sync data from any source to a Snowflake table||Upsert, Insert|
Snowflake credential setup
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.COLUMNStable which is used for gathering metadata to set up the sync
Service account setup
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
-- 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);
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.
Connect to Snowflake
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, (
- 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
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
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 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, and Insert mode using the
In both modes, Hightouch uploads your mapped data to your Snowflake's staging table using the
PUT file statement. When the upsert or insert operation is complete, Hightouch removes the staging file(s).
Insert mode also creates a CSV file that is required for the
COPY INTO statement. When the insert 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
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.
If you see type errors, it may be because your model is producing the wrong format. If so, use typecasting or liquid templating to resolve the issue.
You can sync columns from your model to the columns in your Snowflake table.
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.
The delete behavior you select dictates what to do when a row no longer appears in your model's query results. You have the following options:
|Do nothing||Keep the row in Snowflake|
|Delete row||Remove the row from Snowflake entirely|
Tips and troubleshooting
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.
Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.