Connect Snowflake

Learn how to integrate Snowflake with Harness FME to enable Warehouse Native Experimentation.

Warehouse Native Experimentation allows you to run experiments on data that already lives in your data warehouse. By connecting Harness FME directly to your Snowflake instance, you can securely query and analyze experiment data from your source of truth.

To begin, connect your Snowflake instance as a data source.

Prerequisites

Ensure that you have the following before getting started:

Access to your organization's Snowflake instance
A Snowflake role with appropriate read access to the database and schema containing experiment data, and write access to a results table
A designated results table where experiment results are stored in Snowflake

Setup

Harness recommends the following best practices:

Use a service account rather than a personal Snowflake user.
Grant read-only access to the databases and schemas Harness FME queries.
Rotate private keys periodically.
If your Snowflake instance enforces inbound restrictions, confirm network and IP allowlisting.

To integrate Snowflake as a data warehouse for Warehouse Native Experimentation:

From the Harness FME navigation menu, click FME Settings and click View on a project on the Projects page. Then, navigate to the Data Source tab.
Select Snowflake as your data warehouse. In the Data Sources tab of your Harness FME project, select Snowflake from the list of supported data warehouses.
PROJECT EXPERIMENTATION TYPE
A project uses a single experimentation type based on the metric source used.
When you add a data source to a project, the project’s experimentation type is set to Warehouse Native. All metrics in the project must then use Warehouse Native metric sources.
If a project instead uses metrics created from an ingested event source, the project’s experimentation type is set to Cloud.
Enter the following connection details:
Field
Description
Example
Server (Account Identifier)
Your Snowflake account identifier or server URL.
xy12345.us-west-2
Warehouse
The compute warehouse Harness FME should use to execute queries.
ANALYTICS_WH
Database
The database containing your experimentation data.
PROD_EXPERIMENTS
Schema
The schema within your database containing your experiment or metric data.
AB_TESTING
Username
The Snowflake username tied to your private key.
fme_service_user
Role
The Snowflake role to assume for this connection.
DATA_ANALYST
Results Table Name
The name of the table where experiment results are stored.
EXPERIMENT_RESULTS
NOTE
Harness FME respects Snowflake's built-in role-based access controls. The data source connection only has access to objects allowed for the specified role.
Select an authentication method. Harness FME supports two authentication methods for connecting to Snowflake:
- Private key for secure, password-less access. You can either upload a private key file or paste a private key directly into the text field. Ensure the key corresponds to the username provided and is not encrypted with a passphrase.
- Password authentication: Provide a Snowflake password with read access to the required databases and schemas. Ensure the user has permission to query the tables required for experiment metrics.
Test the connection by clicking Test Connection. Harness FME confirms the following:
- The credentials and key pair are valid.
- The warehouse and role are accessible.
- The specified database and schema exist and are accessible.
Select a database and a schema. After authentication, you can browser available databases, schemas, and tables based on your role permissions. Select the database and schema that contain your assignment and metric source tables.
Specify a results table. Create a results table where Harness FME will write experiment analysis results. Ensure the following:
- The table exists in your database.
- The schema matches the expected format for experiment results below.
Field
Type
Description
METRICID
VARCHAR
Unique identifier for the metric being calculated.
METRICNAME
VARCHAR
Human-readable name of the metric being calculated.
METRICRESULTID
VARCHAR
Unique identifier representing a specific calculation per metric, per experiment, per analysis run.
EXPID
VARCHAR
Unique identifier for the experiment associated with this metric calculation.
EXPNAME
VARCHAR
Human-readable name of the experiment associated with this metric calculation.
TREATMENT
VARCHAR
The experiment variant (e.g., Control or Treatment) associated with the metric results.
DIMENSIONNAME
VARCHAR
The name of the dimension being analyzed (e.g., country, platform).
DIMENSIONVALUE
VARCHAR
The corresponding value of the analyzed dimension.
ATTRIBUTEDKEYSCOUNT
NUMBER
Count of unique keys (users, sessions, etc.) attributed to this metric result.
REQUESTTIMESTAMP
TIMESTAMP_NTZ
Timestamp when the metric computation request occurred.
MIN
FLOAT
Minimum observed value for the metric.
MAX
FLOAT
Maximum observed value for the metric.
COUNT
NUMBER
Total number of observations included in the metric calculation.
SUM
FLOAT
Sum of all observed metric values.
MEAN
FLOAT
Average (mean) of the metric values.
P50
FLOAT
50th percentile (median) metric value.
P95
FLOAT
95th percentile metric value.
P99
FLOAT
99th percentile metric value.
VARIANCE
FLOAT
Variance of the metric values.
EXCLUDEDUSERCOUNT
NUMBER
Number of users excluded from the analysis (due to filters, SRM, etc.).
ASOFTIMESTAMP
TIMESTAMP_NTZ
Timestamp representing when the result snapshot was written.
To create the results table with the correct structure, run the following SQL statement in Snowflake:
```
CREATE OR REPLACE TABLE <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME> (
   METRICRESULTID VARCHAR(16777216),
   TREATMENT VARCHAR(16777216),
   DIMENSIONNAME VARCHAR(16777216),
   DIMENSIONVALUE VARCHAR(16777216),
   ATTRIBUTEDKEYSCOUNT NUMBER(38,0),
   REQUESTTIMESTAMP TIMESTAMP_NTZ(9),
   MIN FLOAT,
   MAX FLOAT,
   COUNT NUMBER(38,0),
   SUM FLOAT,
   MEAN FLOAT,
   P50 FLOAT,
   P95 FLOAT,
   P99 FLOAT,
   VARIANCE FLOAT,
   EXCLUDEDUSERCOUNT NUMBER(38,0),
   ASOFTIMESTAMP TIMESTAMP_NTZ(9),
   METRICID VARCHAR(16777216),
   METRICNAME VARCHAR(16777216),
   EXPID VARCHAR(16777216),
   EXPNAME VARCHAR(16777216)
);
```
Save and activate. Once the test passes, click Save to create the connection.

Your Snowflake data source can now be used to create assignment and metric sources for Warehouse Native Experimentation.

Example Snowflake configuration

Setting

Example

Vendor

Snowflake

Server

xy12345.us-west-2

Warehouse

ANALYTICS_WH

Database

PROD_EXPERIMENTS

Schema

PUBLIC

Username

fme_service_user

Role

DATA_ANALYST

Results Table

EXPERIMENT_RESULTS

PreviousIntegrations

NextAmazon Redshift

Last updated 9 days ago

Was this helpful?