Snowflake Database

Follow our setup guide to connect your Snowflake data warehouse to Gravity.

Prerequisites

To connect Snowflake to Gravity, you need the following:

• A Snowflake account with the appropriate permissions to create a user and warehouse for Gravity.

• Gravity account owner permission to add destinations.

IMPORTANT: In Snowflake, if you use double quotes around an identifier name, it makes the identifier name case-sensitive. We recommend using the create <identifier> <identifier_name> or the create <identifier> "IDENTIFIER_NAME" format. See Snowflake's documentation on identifiers for more information.

Setup instructions

Choose Snowflake warehouse type

You can choose to create an exclusive warehouse for Gravity or use an existing warehouse:

• You can create and use an exclusive warehouse for Gravity. The benefit of this route is that Gravity operations will never contend with your queries for resources and this is useful when it comes to diagnosing performance issues if you face concurrency/waiting problems in your data warehouse.

• You can use a shared warehouse to reduce your warehouse running cost. Gravity does not consume much resources through loading data incrementally but it will possibly drive longer wait times if you share a warehouse with multiple applications. This is not a Gravity issue but a warehouse/cost design decision you need to make.

Run script in Snowflake warehouse

1. Log in to your Snowflake data warehouse.

2. Copy the following script to a new worksheet and select the All Queries checkbox.

3. DO NOT execute this script yet. See Step 4 for notes on which items you need to replace, including the password.

   begin;
    
      -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
      set role_name = 'GRAVITY_ROLE';
      set user_name = 'GRAVITY_USER';
      set user_password = 'SETPASSWORD';
      set warehouse_name = 'GRAVITY_WAREHOUSE';
      set database_name = 'GRAVITY_DATABASE';
   
      -- change role to securityadmin for user / role steps
      use role securityadmin;
    
      -- create role for gravity
      create role if not exists identifier($role_name);
      grant role identifier($role_name) to role SYSADMIN;
    
      -- create a user for gravity
      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 gravity
      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 gravity
      create database if not exists identifier($database_name);
    
      -- grant gravity role access to warehouse
      grant USAGE
      on warehouse identifier($warehouse_name)
      to role identifier($role_name);
    
      -- grant gravity access to database
      grant CREATE SCHEMA, MONITOR, USAGE
      on database identifier($database_name)
      to role identifier($role_name);
   
      -- change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)
      use role ACCOUNTADMIN;
      grant CREATE INTEGRATION on account to role identifier($role_name);
      use role sysadmin;
    
    commit; 
   
   content_copy

4. If you want to create a new warehouse do not make any changes. If you want to use a shared warehouse to ingest data, change the GRAVITY_WAREHOUSE value in the script to the name of the shared warehouse.

5. Replace the default GRAVITY_ROLE, GRAVITY_DATABASE, GRAVITY_USER, and SETPASSWORD values with values that conform to your specific naming conventions for those resources.

6. Run the script.

Configure Snowflake network policy

If you don't have a network policy you need to create one or you can update your policy. The addresses below are Gravity IP Addresses.

To create: Use the CREATE NETWORK POLICY and an example is here:

CREATE NETWORK POLICY <gravity_ip_whitelist> ALLOWED_IP_LIST = ('35.242.183.251/32', '35.246.26.172/32', '35.197.252.236/32','35.234.142.96/32', '35.234.128.102/32', '34.105.214.159/32');

To update: Use the ALTER NETWORK POLICY and an example is here

ALTER NETWORK POLICY <your_network_policy_name> SET {[ALLOWED_IP_LIST] = ('35.242.183.251/32', '35.246.26.172/32', '35.197.252.236/32','35.234.142.96/32', '35.234.128.102/32', '34.105.214.159/32')]};

Create Destination in Gravity

1. Log in to your Gravity and click on Destinations, Choose Snowflake

2. Insert your Host Name. Port Number and Database Name.

3. Insert your User name and Password

4. Click Save & Test.

   • Test checks connectivity to Snowflake and permission to create schemas and tables on your Snowflake database.

Gravity has now validated the destination and if successful you can add sources or create jobs. If unsuccessful, please go back through the steps above or contact Gravity Support.

---