# Connecting to Snowflake

Privacy Dynamics can connect to your Snowflake accounts. This guide helps you authenticate and authorize Privacy Dynamics to access your data in Snowflake.

# Requirements

To complete this guide, you will need the following:

  • A Snowflake account with at least one database.
  • A Snowflake user with the ACCOUNTADMIN role.
  • A Privacy Dynamics account.

# Instructions

Before you can connect to Snowflake in Privacy Dynamics, you will want to create a new service account user, role, and warehouse for Privacy Dynamics to use.

# Configure Your Snowflake Account

TIP

The examples below use the new Snowflake UI, Snowsight, but the same steps can be taken in the legacy Console.

# Step 1: Create A New User For Privacy Dynamics

  1. Log into your Snowflake account with a user that has been granted ACCOUNTADMIN.
  2. Ensure you are using the ACCOUNTADMIN role, and then select Admin > Users & Roles. Users and Roles Page
  3. Select the + User button in the top-right corner. Enter a User Name (we use SVC_PVCY), Password, and Comment. Uncheck the box "Force user to change password on first time login", then select Create User. New User Modal
  4. Save the new user's credentials to a password manager, so you and your team will have access to it later.

TIP

For Privacy Dynamics to connecting with this user, the user must have DEFAULT_WAREHOUSE and DEFAULT_ROLE set. We will do that in the next step, after those resources are created.

# Step 2: Create a Role and Warehouse and Grant Privileges to the New User

The user we just created will need privileges granted to it. We recommend creating a new role with only the required privileges for Privacy Dynamics to work. We also recommend creating a new warehouse for the service account to use -- this ensures we're not competing for compute resources and allows you to track the cost of our compute.

The following script completes the setup for our service account. You will need to customize this with the names of your databases, schemas, and service account user, and then execute it in the Snowflake console using the ACCOUNTADMIN role

-- CONFIGURE ROLE, WAREHOUSE, AND DATABASES
create role if not exists anonymizer
    comment = 'Role for Privacy Dynamics Service Account';
revoke all on account from role anonymizer;
revoke role public from role anonymizer;

create warehouse if not exists anonymizing 
    warehouse_size = XSMALL 
    initially_suspended = TRUE 
    auto_suspend = 60 -- seconds; minimum suggested by Snowflake
    auto_resume = TRUE
    comment = 'Warehouse for Privacy Dynamics anonymization jobs'
;
grant usage on warehouse anonymizing to role anonymizer;

-- assumes RAW_SENSITIVE (origin) and PRIVACY_SAFE (destination) databases 
-- already exist. Change these to the names of your databases
grant usage on database raw_sensitive to role anonymizer;
grant usage on database privacy_safe to role anonymizer;

-- CONFIGURE ORIGIN SCHEMA(s)

-- any existing schemas that you want us to read from need their own grants
-- we require usage on the schema and select on any tables/views in that schema
grant usage on schema raw_sensitive.health to role anonymizer;
grant select on all tables in schema raw_sensitive.health to role anonymizer;
grant select on all views in schema raw_sensitive.health to role anonymizer;
-- repeat 3 lines above for any additional schemas

-- optionally grant access to any future schemas or relations in source database
grant usage on future schemas in database raw_sensitive to role anonymizer;
grant select on future tables in database raw_sensitive to role anonymizer;
grant select on future views in database raw_sensitive to role anonymizer;

-- CONFIGURE DESTINATION SCHEMA(s)

-- create database is optional if the privacy_safe database is already created
grant create database on account to role anonymizer;

-- Option 1: Allow us to create new schemas in the destination database
grant create schema on database privacy_safe to role anonymizer;

-- Option 2: Allow us to write to an existing schema owned by another role
grant usage on schema privacy_safe.health to role anonymizer;
grant create table on schema privacy_safe.health to role anonymizer;
-- repeat 2 lines above for any additional schemas

-- GRANT ROLE TO USERS AND OTHER ROLES
grant role anonymizer to user svc_pvcy;
alter user svc_pvcy set default_role = anonymizer;
alter user svc_pvcy set default_warehouse = anonymizing;
grant role anonymizer to role sysadmin;

# Add the Snowflake Connection in Privacy Dynamics

  1. Sign in to your Privacy Dynamics account.
  2. Go to the Connections page (opens new window).
  3. Select Add Connection.
  4. Choose Snowflake and select Next.
  5. Enter the connection details:
    • Name - enter a name for you to identify the connection.
    • Account - the first part of your account URL or locator URL, of the form hostname.region.cloud (or just hostname if on AWS and US-West-2).
    • User - the username of the service account we created earlier (SVC_PVCY).
    • Password - the password for the service account user.
    • Database - the name of the database you would like to use. To read from one database and write to another, simply create a new connection for each database.
    • Warehouse (Optional) - the name of the warehouse you would like to use. If no Warehouse is specified, we will use the User's default Warehouse.
    • Role (Optional) - the name of the role you would like to use. The User must have been granted access to the Role, and the Role must contain the privileges necessary to interact with the Database and Warehouse. If no Role is specified, we will use the User's default Role. If the User's specified or default Role cannot interact with the Warehouse, the Connection may succeed, but subsequent jobs will fail.
  6. Select TEST CONNECTION to verify the credentials.
  7. Select ADD CONNECTION and your connection saves if there are no errors.
Last Updated: 11/7/2022, 7:43:43 PM
logo green

Empowering innovative and ethical data teams