Snowflake

Empower your operations by syncing modeled data into your warehouse

Supported syncing

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.COLUMNS table 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 ht_database each time.

-- 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, (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.

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.

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 Hightouch under the Authentication Method section, drag and drop or upload the private key you just generated into the Private key file field.

SSH tunneling

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.

Sync configuration

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.

Sync mode

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.

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.

Record matching

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.

Column types

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.

Field mapping

You can sync columns from your model to the columns in your Snowflake table.

Batch size

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.

Delete behavior

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:

Tips and troubleshooting

Common errors

To date, our customers haven't experienced any errors while using this destination. If you run into any issues, please don't hesitate to reach out. We're here to help.

Live debugger

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.

Sync alerts

Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.