Snowflake

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 warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
   set database_name = 'SOME_DATABASE';
   set schema_name = 'SOME_SCHEMA';
   set table_name = 'SOME_TABLE'; -- repeat for all tables

   -- 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)
   RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key copied from the connection form in the onboarding UI

   -- 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;

   -- grant service role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);

   -- grant service access to database
   grant MONITOR, USAGE
   on database identifier($database_name)
   to role identifier($role_name);

   -- use provided database
   use database identifier($database_name);

   -- grant service access to schema
   grant USAGE
   on schema identifier($schema_name)
   to role identifier($role_name);

   -- use provided schema
   use schema identifier($schema_name);

   -- grant service access to table
   grant SELECT
   on identifier($table_name)
   to role identifier($role_name);

 commit;