Authorize Snowflake

Authorize your Snowflake Data Warehouse and sync data with Stacksync

Connect Snowflake on Stacksync in less than two minutes

To follow this tutorial, the (human user) Snowflake account needs the SECURITY_ADMIN and ACCOUNTADMIN. Read more here.

1. Select Snowflake in the Create Sync page in Stacksync.

2. Copy the user public key for the next steps

3. Run the setup script in Snowflake

Create a new Snowflake user (see the next section to reuse an existing Snowflake user)

Copy the following script to create a new user with the necessary permissions. In the SECTION TO EDIT, enter the following values:

• warehouse_name

• database_name

• schema_name

• rsa_public_key

-----------------------SECTION TO EDIT------------------------------------
-- create the variables for user and role details
set role_name = 'STACKSYNC_ROLE'; --enter the role name
set user_name = 'STACKSYNC_USER'; -- enter the username
set warehouse_name = 'COMPUTE_WH'; -- enter the warehouse you want to use
set database_name = 'STACKSYNC_TEST'; --enter database name
set schema_name = 'PUBLIC'; -- enter schema name
set user_type = 'service';
-----------------------END OF SECTION TO EDIT------------------------------------

-- change role to security admin to create user and role
use role securityadmin;

-- create the Stacksync Role
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

 -- create a user for Stacksync
create user if not exists identifier($user_name)
 -- copy paste public key from stacksync.
rsa_public_key = '-----BEGIN PUBLIC KEY-----
....TODO REPLACE WITH STACKSYNC PROVIDED RSA PUBLIC KEY....
-----END PUBLIC KEY-----'

-----------------------END OF SECTION TO EDIT-----------------------------

type = $user_type
default_role = $role_name
default_warehouse = $warehouse_name;

-- grant Stacksync role to the new user
grant role identifier($role_name) to user identifier($user_name);

-- change role to accountadmin to grant permissions to the Stacksync Role
use role accountadmin;

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

-- grant Stacksync role access to database and create schemas
grant usage on database identifier($database_name) to role identifier($role_name);
grant create schema on database identifier($database_name) to role identifier($role_name);

-- select the database
use database identifier($database_name);

-- grant table and stream privileges to all the schemas you want to sync
grant usage on schema identifier($schema_name) to role identifier($role_name);
grant create table on schema identifier($schema_name) to role identifier($role_name);
grant create stream on schema identifier($schema_name) to role identifier($role_name);
grant select on all tables in schema identifier($schema_name) to role identifier($role_name);
grant select on future tables in schema identifier($schema_name) to role identifier($role_name);

-- repeat the steps above for all the schemas you want to sync.

Use an existing Snowflake user

You can simply set the rsa_public_key for an existing user (make sure the user type is set to service).

ALTER USER <username> SET RSA_PUBLIC_KEY='<public_key_copied_from_stacksync>';

4. Find the credentials for your Snowflake instance.

Note the database name, schema name, and account identifier.

Account Identifier uniquely identifies a Snowflake account within your organization. It is a combination of your organization name and account name. It can be found in the Snowflake web interface in the Account Details section.

Learn More

Once copied, paste this information in the Stacksync connection screen.

5. Enter the credentials for Snowflake and Click Authorize App.

That's it! 🎉