Snowflake Prerequisites
Configuring your Snowflake destination.
Prerequisites
- Your Snowflake user must be granted
securityadmin
andsysadmin
roles to complete the next step. To verify these roles, runSHOW GRANTS TO USER <your_username>;
and review therole
column.
Step 1: Create role, user, warehouse, and database in the data warehouse
- Review and make any changes to the following setup script.
begin;
-- create variables for user / password / role / warehouse / database
set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
set user_password = 'some_password'; -- alphanumeric only, special characters are not allowed
set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
set database_name = 'TRANSFER_DATABASE'; -- all letters must be uppercase
-- change role to securityadmin for user / role steps
use role securityadmin;
-- create role for data transfer service
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;
-- create a user for data transfer service
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;
grant role identifier($role_name) to user identifier($user_name);
-- change role to sysadmin for warehouse / database steps
use role sysadmin;
-- create a warehouse for data transfer service
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;
-- create database for data transfer service
create database if not exists identifier($database_name);
-- grant service role access to warehouse
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);
-- grant service access to database
grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_name)
to role identifier($role_name);
commit;
NOTE: special characters are not allowed for the password and might cause connection issues. We recommend using exclusively URL safe, alphanumeric characters.
📘 Using an existing warehouse or database
By default, this script creates a new warehouse and a new database. If you'd prefer to use an existing warehouse/database, change the
warehouse_name
variable fromTRANSFER_WAREHOUSE
to the name of the warehouse to be shared/database_name
variable fromTRANSFER_DATABASE
to the name of the database to be shared.
- In the Snowflake interface, select the All Queries checkbox, and click "Run". This will run every query in the script at once. If successful, you will see
Statement executed successfully
in the query results.
Step 2: Configure the Snowflake access policy
If your Snowflake data warehouse is using Snowflake Access Policies, a new policy must be added to allow the transfer service static IP (34.171.168.215/32
) to write to the warehouse.
- Review current network policies to check for existing IP safelists.
SHOW NETWORK POLICIES;
- If there is no existing Snowflake Network Policies (the
SHOW
query returns no results), you can skip to Step 3. - If there is an existing Snowflake Network Policy, you must alter the existing policy or create a new one to safelist the data transfer service static IP address (
34.171.168.215/32
). Use theCREATE NETWORK POLICY
command to specify the IP addresses that can access your Snowflake warehouse.
CREATE NETWORK POLICY <transfer_service_policy_name> ALLOWED_IP_LIST = ('34.171.168.215/32');
Creating your first network policy
If you have no existing network policies and you create your first as part of this step, all other IPs outside of the
ALLOWED_IP_LIST
will be blocked. Snowflake does not allow setting a network policy that blocks your current IP address. (An error message results while trying to create a network policy that blocks the current IP address.) But be careful when setting your first network policy.
Step 3: Gather the required setup information
For the data export setup you will need:
- Hostname and database name of the destination Snowflake data warehouse
- Username and password of the newly created user
- If your Snowflake data warehouse is using Snowflake Access Policies, you will need to have the data-syncing service's static IP available as well.
- To do so, use the following command:
CREATE NETWORK POLICY <transfer_service_policy_name> ALLOWED_IP_LIST = ('34.171.168.215/32');
- Note: If you have no existing network policies and you create your first as part of this step, all other IPs outside of theÂ
ALLOWED_IP_LIST
 will be blocked. Snowflake does not allow setting a network policy that blocks your current IP address. (An error message results while trying to create a network policy that blocks the current IP address.) But be careful when setting your first network policy.
- To do so, use the following command:
Visit the LogRocket Streaming Data Export settings page to complete the setup.
Updated 3 months ago
Learn about how to configure the Streaming Data Export integration in app!