Snowflake

​

Overview

​

Snowflake credential setup

• This snippet provides an example of creating a service account; you may need to alter it depending on your Snowflake implementation details.
• RSA key pair authentication setup is detailed in the Provide credentials section.
• If you want Hightouch to access multiple databases, run the snippet multiple times, changing ht_database each time.
• If you want Hightouch to have read-only access, omit the last line of the query.
• The snippet includes lines for creating a new warehouse and database for Hightouch to use; if you already have databases and warehouses you intend to use, omit these lines.

-- Edit the following variables
set ht_username='HIGHTOUCH_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;

-- Only if you want to create a new warehouse for Hightouch to use
CREATE WAREHOUSE IF NOT EXISTS identifier($ht_default_warehouse);

-- Create Hightouch's user
CREATE USER IF NOT EXISTS identifier($ht_username)
TYPE=service
rsa_public_key='<public_key>'
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);

-- Only if you want to create a new database for Hightouch to use
CREATE DATABASE IF NOT EXISTS identifier($ht_database);

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);

-- Grant write access for the Lightning Sync Engine (faster syncs) & Sync Logs - Recommended
USE identifier($ht_database);
CREATE SCHEMA IF NOT EXISTS hightouch_audit;
CREATE SCHEMA IF NOT EXISTS hightouch_planner;
GRANT OWNERSHIP ON SCHEMA hightouch_audit TO ROLE identifier($ht_default_role);
GRANT OWNERSHIP ON SCHEMA hightouch_planner TO ROLE identifier($ht_default_role);

​

Connection configuration

​

Configure your source

• Account identifier: You can find this identifier at the beginning of your Snowflake URL, for example, (https://ACCOUNT_IDENTIFIER.snowflakecomputing.com). Only enter the ACCOUNT_IDENTIFIER and not the full URL.
• Warehouse: This specifies the warehouse to use when Hightouch executes queries in Snowflake. You can choose to route all queries to a single warehouse, or configure separate warehouses optimized for different workloads (recommended):
  • Route all queries to a single warehouse: Use this option if you want to use a single warehouse for all Hightouch operations.
  • Route queries to different warehouses (split by workload) (Recommended): This option allows you to optimize performance and cost by routing different types of queries to different warehouses:
    • Batch warehouse: For background jobs like sync runs, schema sampling, audience snapshots, and other workloads that are not latency-sensitive. This warehouse can be optimized for cost efficiency.
    • Interactive warehouse: For latency-sensitive queries (e.g., audience previews) and heavier workloads that need more compute (e.g., identity resolution). This warehouse should be sized up for best experience.
• Database: This specifies the database to use when Hightouch executes queries in Snowflake.

​

Choose your sync engine

​

Basic versus Lightning engine comparison

​

Lightning engine setup

CREATE SCHEMA IF NOT EXISTS hightouch_audit;
CREATE SCHEMA IF NOT EXISTS hightouch_planner;
GRANT OWNERSHIP ON SCHEMA hightouch_audit TO ROLE identifier($ht_default_role);
GRANT OWNERSHIP ON SCHEMA hightouch_planner TO ROLE identifier($ht_default_role);

​

Provide credentials

• Username: This can be your personal Snowflake login or a dedicated user for Hightouch. When using the Basic engine, this user must have read access to the data you wish to sync. If using the Lightning engine, you must also grant the permissions described in the preceding snippet. Note: Use the “Login name”, which may differ from the Snowflake username.
• 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.

• RSA key pair (recommended)
• Password

​

RSA authentication

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

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

​

Test your connection

• Network connectivity
• Snowflake credentials
• Permission to list schemas and tables
• Permission to write to hightouch_planner schema
• Permission to write to hightouch_audit schema

​

Manage warehouse compute

• Keep Hightouch’s Snowflake usage predictable and easy to review
• Scope Hightouch’s access to only the data it needs
• Avoid unnecessary compute for common activation workflows

​

Recommended Snowflake setup

• Use a dedicated user, role, and warehouse
  So compute usage and data access are easy to track and control.
• Set practical warehouse defaults
  Start with a modest size, shorten idle time, and add reasonable query time limits.
• Separate interactive and scheduled work when needed
  Run UI-driven queries and background jobs on different warehouses if you need stronger performance isolation.
• Design models with query cost in mind
  Keep parent models focused, handle large event tables carefully, and combine tables only when it clearly helps.
• Review usage regularly
  Use Snowflake’s cost dashboards, resource monitors, and query history to confirm performance and spend.

​

Use a dedicated user, role, and warehouse

• A user (who runs queries)
• A role (what they can access)
• A virtual warehouse (where queries run)

​

Scope access with a dedicated role

• Grant read access only to the databases and schemas that back your Hightouch models.
• Grant write access only when required (for example, for Warehouse Sync Logs or Hightouch-managed audit schemas).
• Avoid broad account-level privileges.

• Masking policies on sensitive columns
• Row- or column-level security
• Views that expose filtered or redacted data

​

Separate Hightouch from human users

• Set its default role to the Hightouch role.
• Set its default warehouse to the Hightouch warehouse.
• Use strong authentication (for example, RSA key pair authentication).

​

Isolate compute with a dedicated warehouse

• Create a virtual warehouse dedicated to Hightouch (for example, HIGHTOUCH_WH).
• Attach it to the Snowflake source configuration in Hightouch.

• Attribute Snowflake credits to Hightouch
• Adjust warehouse settings without impacting other workloads

​

Set practical warehouse defaults

​

Choose a starting size and adjust as needed

• How long syncs and other Hightouch jobs take
• Whether Customer Studio previews remain responsive
• Whether Snowflake reports consistent queuing

​

Reduce idle time with auto-suspend

​

Add query time limits as a safeguard

• Covers your typical sync runtimes
• Prevents a query from running indefinitely

• Review Snowflake query history
• Identify the models or filters causing long runtimes
• Simplify the query before increasing the timeout

​

Use multi-cluster only when concurrency is the issue (optional)

• Many Hightouch jobs run at once
• You see consistent queuing during peak times
• Usage spikes unpredictably

​

Separate interactive and scheduled work

• Interactive: audience previews, traits exploration, dashboards, identity resolution, and other UI actions
• Batch: sync runs, heavy traits, sampling refreshes, and snapshots

​

Common pattern: two warehouses

• An interactive warehouse for fast UI queries
• A batch warehouse for scheduled and background jobs

1. Create two warehouses (for example, HIGHTOUCH_INTERACTIVE_WH and HIGHTOUCH_BATCH_WH).
2. Configure Hightouch to send interactive work to one and batch work to the other.
3. Set size and auto-suspend settings independently.

​

Design Customer Studio models with query cost in mind

​

Keep parent and related models simple

• The parent model should represent the main entity (for example, customer or account).
• Include stable identifiers and frequently used segmentation fields.
• Include keys needed to join related models.

​

Limit event tables to what activation requires

• Limit time windows to what activation actually needs
• Organize tables based on common filters (often date or tenant)
• Create smaller summary tables for common metrics when helpful

​

Combine tables only when it clearly helps

• Keep data in separate tables by default.
• Combine tables only when it clearly improves common Hightouch queries.
• Avoid flattening very large tables unless it solves a specific performance problem.

​

Improve audience building performance

• Sampling runs previews against a subset of rows for faster results.
• You can still run full queries for production syncs or reporting.

​

Reduce cost

​

Reduce unnecessary query volume

• Keeping warehouses active longer
• Increasing concurrency, which may require larger warehouses

​

Right-size warehouses for your workload

​

Monitor cost and usage

​

Review cost dashboards

• Filter by warehouse name to track credit usage
• Compare Hightouch warehouses with other workloads

​

Use budgets and resource monitors

• Set budgets for Hightouch warehouses
• Configure alerts or automatic suspension for unexpected spikes

​

Inspect query history

• Filter by Hightouch user, role, or warehouse
• Identify expensive or slow queries
• Tie them back to specific models or workflows

​

Create models

• writing a query in the SQL editor
• using the visual table selector
• leveraging existing dbt models
• leveraging existing Looker Looks
• leveraging existing Sigma workbooks

​

FAQ

​

How can I tell how much compute Hightouch is using?

1. Go to Admin → Cost Management.
2. Open the Consumption tab.
3. Filter by your Hightouch warehouse name.

• Credit usage over time
• Breakdown by warehouse
• Query and service-level cost trends

​

How can I identify specific queries driving cost?

​

SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY

• It accounts for concurrent queries by allocating weighted compute usage.
• It excludes idle time between query completion and warehouse shutdown.
• It allows you to identify the most expensive individual queries.

​

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

• Full SQL query text
• Query duration
• Bytes scanned and written
• Rows produced
• Queuing time
• Spill metrics

• Which queries consume the most credits
• Whether queuing is contributing to performance issues
• Whether large scans or joins are driving cost

​

Example: Identify expensive Hightouch queries in the last day

SELECT
  QAH.*,
  QHBW.QUERY_TEXT,
  QHBW.QUEUED_PROVISIONING_TIME,
  QHBW.ROWS_WRITTEN_TO_RESULT
FROM
  SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY AS QAH
  JOIN (
    SELECT
      *
    FROM
      TABLE(
        SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
          WAREHOUSE_NAME => 'HIGHTOUCH_SYNCS',
          RESULT_LIMIT => 10000
        )
      )
  ) AS QHBW
    ON QHBW.QUERY_ID = QAH.QUERY_ID
WHERE
  QAH.START_TIME > CURRENT_DATE - INTERVAL '1 DAY'
ORDER BY
  QAH.CREDITS_ATTRIBUTED_COMPUTE DESC;

• WAREHOUSE_NAME to match your Hightouch warehouse
• The time window in the WHERE clause
• The RESULT_LIMIT parameter

• Rank queries by compute consumption
• Inspect SQL text to identify inefficient joins or scans
• Confirm that configuration changes (warehouse size, routing, schema adjustments) are having the intended effect

​

Tips and troubleshooting

​

Connection timeout

​

Network error: Could not reach Snowflake

​

SQL compilation error with quoted identifiers

SELECT "job number" AS job_number, "first name" AS first_name
FROM my_schema.my_table

​

JWT token is invalid