Configure your Snowflake warehouse

  • Updated

Connect your Snowflake warehouse to Optimizely Analytics to unify customer and product data, create advanced segments, and measure key outcomes like revenue and retention. This direct connection keeps your analytics accurate, eliminates manual exports, and ensures experimentation insights align with your single source of truth. 

You must log in with Account Administrator privileges to create a warehouse, assign roles, or add users to your organization. 

Perform the following steps to configure your Snowflake warehouse for Optimizely Analytics:

Create a warehouse for Optimizely Analytics

Begin with a medium-sized warehouse and scale up or down based on observed query latencies and warehouse costs. The warehouse size depends on the volume of data that needs to be analyzed by Analytics and varies based on the queries' latencies.

To create a warehouse, you must use the Account Administrator role that has the permission to run these commands. 

Use the following command to create a medium-sized warehouse: 

SYNTAX –
create warehouse if not exists [WAREHOUSE_NAME] warehouse_size=[WAREHOUSE_SIZE] initially_suspended=true auto_suspend=1 statement_queued_timeout_in_seconds=300;

EXAMPLE –
create warehouse if not exists netspring_wh_a warehouse_size='Medium' initially_suspended=true auto_suspend=1 statement_queued_timeout_in_seconds=300;

Create a user and role

Create a user and define a role, replace [PASSWORD] with your password, and share it with Optimizely Analytics. In this article, example_role is the defined role name for all example commands. You must replace it with the name of the role you created for Optimizely Analytics.

While we still support basic authentication using a username and password, this method is being deprecated by Snowflake. Read about Snowflake's deprecation timeline.
SYNTAX –
create role if not exists [ANALYTICS_ROLE];
EXAMPLE –
create role if not exists example_role;

SYNTAX –
create user if not exists [ANALYTICS_USER] password=[PASSWORD] default_role=[ANALYTICS_ROLE] default_warehouse=[WAREHOUSE_NAME];
EXAMPLE –
create user if not exists 'Mavis Gates' password='password@123' default_role=example_role default_warehouse='netspring_wh_a';

SYNTAX –
grant role [ANALYTICS_ROLE] to user [ANALYTICS_USER];
EXAMPLE –
grant role example_role to user 'Mavis Gates';

Optimizely Analytics uses the user's default role to perform operations on Snowflake. Execute the following SQL to make the role you created for Optimizely Analytics the default role:

SYNTAX –
ALTER USER SYS_NETSPRING SET DEFAULT_ROLE = NEW_ROLE;
EXAMPLE –
alter user sys_netspring set default_role = example_role;

Grant usage permission

You must grant Optimizely Analytics access to the Snowflake warehouse to interact with the database objects and perform necessary operations. Use the following code to grant the created user role access to a specific warehouse for Analytics: 

SYNTAX –
`grant operate, usage, monitor on warehouse [WAREHOUSE_NAME] to role [NETSPRING_ROLE];`

EXAMPLE –
`grant operate, usage, monitor on warehouse netspring_wh_a to role example_role;`

The fields differ depending on the warehouse you choose.

Grant access to databases, schemas, tables, and views

Granting access at these levels ensures the connection has the necessary permissions for data extraction, transformation, or analysis, while maintaining security and control over accessible data. Use the following commands:

Function Command Example command
Grant usage access to relevant databases and schemas. grant usage on database [DATABASE_NAME] 
to role [NETSPRING_ROLE]; 		grant usage on the database netspring_db to role example_role;
Grant usage access to all schemas in a database. grant usage on all schemas in the database  [DATABASE_NAME] to role [NETSPRING_ROLE]; grant usage on all schemas in the database netspring_db to role example_role;
Grant access to execute select on all tables in the database. grant selects all tables in the database [DATABASE_NAME] to role  [NETSPRING_ROLE]; grant selects all tables in the database netspring_db to role example_role;
Grant access to execute select on all future tables in the database. grant select on future tables in the database  [DATABASE_NAME] to role  [NETSPRING_ROLE]; grant select on future tables in the database netspring_db to role example_role;
Grant access to execute select on all tables in a schema. grant selects all tables in the schema  [DATABASE_NAME].[SCHEMA_NAME] to role [NETSPRING_ROLE]; grant selects all tables in the schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on all future tables in a schema. grant select on future tables in the schema  [DATABASE_NAME].[SCHEMA_NAME] to role [NETSPRING_ROLE]; grant select on future tables in the schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on the table. grant select on  [DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME] to role [NETSPRING_ROLE]; grant select on netspring_db.netspring_schema.netspring_table to role example_role;

IP allowlisting (conditional)

Ensure the warehouse accepts incoming Optimizely Analytics requests over the public internet. This action is conditional and only required if the warehouse cluster is guarded by a security group (such as a firewall) that prevents access to the cluster from Optimizely Analytics. 

Following is the IP allowlist: 

  • 35.196.71.222
  • 34.73.142.185
  • 34.148.77.115
  • 34.73.63.141
  • 34.74.199.69
  • 34.74.109.219
  • 34.139.128.201
  • 35.243.168.58

Performance guidance

The following techniques are various approaches you can take to ensure that Optimizely Analytics runs at an optimal cost and performance profile:

  1. Ensure your events/conversions table is clustered by event date (not time), and event type (the same column selected in the Semantics tab of the dataset in Optimizely Analytics), in that order.
  2. (Experimentation Analytics only) Check if the warehouse is clustered by the experiment ID column and decision date (not time) if it has a separate decisions table.
  3. Ensure that you have created a new schema in your warehouse, give Optimizely Analytics read and write access to it, and then enter the name of that schema in the Optimizely Analytics app settings, in the Materialization section. This enables the materialization of repeated queries, which is a large cost/performance boost.
  4. Ensure your warehouse instance size is appropriate.
  5. Consider creating a new warehouse for Optimizely Analytics, if this same warehouse instance is also used by other workloads, so that Optimizely Analytics queries are isolated from other workloads.

Create a writable schema in Snowflake

After configuring your warehouse and granting permissions, create a writable scratch space for Optimizely Analytics. This space optimizes internal operations, enhancing performance, resource utilization, and user experience, making it essential for advanced analytics in Snowflake.

SYNTAX –
create database [DATABASE_NAME];
EXAMPLE –
create database netspring_db;

SYNTAX –
use database [DATABASE_NAME];
EXAMPLE –
use database netspring_db;

SYNTAX –
create schema [SCHEMA_NAME];
EXAMPLE –
create schema netspring_schema;

SYNTAX –
grant all privileges on [DATABASE_NAME] to role [NETSPRING_ROLE];
EXAMPLE –
grant all privileges on netspring_db to role example_role;

SYNTAX –
grant all privileges on [SCHEMA_NAME] to role [NETSPRING_ROLE];
EXAMPLE –
grant all privileges on netspring_schema to role netspring_role;