Snowflake

begin;

-- create variables for user / role / warehouse / database
set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
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; -- establish SYSADMIN as the parent of the new role. Note: this does not grant the access privileges of SYSADMIN to the new role.

-- create a user for data transfer service
create user if not exists identifier($user_name)
RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key as required in the prerequisite

-- set default role and warehouse to new user
alter user identifier($user_name) SET default_role = $role_name;
alter user identifier($user_name) SET default_warehouse = $warehouse_name;
alter user identifier($user_name) SET type = service;

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;