Snowflake is a leading cloud data warehouse, and you can use Refuel to read and write data back into it.

​
Setup guide

For Refuel to read and write data back into Snowflake, the following steps need to be followed.

​
Step 1: Create a role and user

Run the following script as a user role that has the permissions to create a user, role, and warehouse (such as the sysadmin or securityadmin roles).

Create roles, users and permissions within Snowflake
begin;

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'REFUEL_ROLE';
set user_name = 'REFUEL_USER';
set user_password = '<your-password>';
set warehouse_name = 'REFUEL_WAREHOUSE';
set database_name = 'REFUEL_DATABASE';

-- change role to securityadmin for user / role steps
use role securityadmin;

-- create role for refuel
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- create a user for refuel
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);

-- set binary_input_format to BASE64
alter user identifier($user_name) set binary_input_format = 'BASE64';

-- change role to sysadmin for warehouse / database steps
use role sysadmin;

-- create a warehouse for refuel
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 refuel
create database if not exists identifier($database_name);

-- grant refuel role access to warehouse
grant usage
on warehouse identifier($warehouse_name)
to role identifier($role_name);

-- grant refuel access to database
grant create schema, monitor, usage
on database identifier($database_name)
to role identifier($role_name);

-- grant refuel read access to all tables and views in database
grant usage
on all schemas in database identifier($database_name)
to role identifier($role_name);
grant select
on all tables in database identifier($database_name)
to role identifier($role_name);
grant select
on all views in database identifier($database_name)
to role identifier($role_name);

-- if you want to limit access to specific schemas, you can use the following commands
-- grant USAGE on SCHEMA identifier($database_name).schema_name to role identifier($role_name);
-- grant SELECT on ALL TABLES in SCHEMA identifier($database_name).schema_name to role identifier($role_name);
-- grant SELECT on ALL VIEWS in SCHEMA identifier($database_name).schema_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;

​
[Optional] Step 2: Enabling Key Pair Authentication

If you want a more secure way for Refuel to communicate with Snowflake, you can enable Key Pair Authentication.

For that, follow the steps here. The only change to the list of steps we recommend is to use the AES256 algorithm instead of DES3 (faster and more secure). The complete command to generate the private key is

Generate a private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 AES256 -inform PEM -out rsa_key.p8

​
Step 3: Setup Snowflake integration in Refuel Cloud

In order to setup the Snowflake integration, navigate to Integrations within Refuel Cloud Settings, select Snowflake, and fill in the following fields:

  • Snowflake Host (account_name.snowflakecomputing.com)
  • Warehouse
  • Database
  • Role
  • Username
  • Password (If not using Key Pair authentication)
  • Private Key (If using Key Pair authentication)
  • Passphrase (If using Key Pair authentication)

OverviewOverview