Configuring Snowflake Access

This article will describe how to configure Snowflake Data Warehouse credentials for use by Census, and why those permissions are needed.

Snowflake’s Query Execution Warehouse

As part of setting up your Snowflake connection, you will need to choose a Query Execution Warehouse where Census queries should run. Snowflake is unique amongst data warehouse products in that it allows you to configure which computational environment to use when running queries.
For Census, you can use an existing warehouse, or create a new warehouse in Snowflake specifically for Census jobs. Census can run on any size warehouse, all the way down to X-Small, though some large jobs may run faster on a larger warehouse.

Using Snowflake on AWS VPS, PrivateLink, and Azure

Connecting to a Snowflake instance running on AWS VPS or PrivateLink, or on Azure, requires a modified connection configuration. Please contact your Census account manager to have this configured for you.

Required Permissions

Census reads data from one or more tables (possibly across different schemata) in your data warehouse and publishes it to the corresponding objects in external systems, such as Salesforce. 
To limit the load on your database as well as external APIs, Census computes a “diff” to determine changes between each update. In order to compute these diffs, Census creates and writes to a number of tables in the census database.
In order for the Census connection to work correctly, the account you provide to Census must have these permissions:
  • Permission to create a new census database or an existing census database and full admin access to all schema/tables within that database (including creating and deleting schema and tables, and reading and writing to all tables). 
  • Read-only access to the information_schema schema, which Census uses to list the available schemata, tables, and views, and identity the data types for columns within tables to be synced.
  • Read-only access to any tables and views in any schemata that you would like Census to publish to Salesforce.

Here is an example of the set of SQL commands needed to configure Census's access to Snowflake. Note that some of these steps are optional or may needed to be repeated to give Census access to all of the data you'd like to provide.

-- Change role to ACCOUNTADMIN for user / role steps

-- Create role for Census
create role if not exists census_role;
grant role census_role to role SYSADMIN;

-- Create a user for Census
create user if not exists census;
alter user census set
default_role = census_role
default_warehouse = census
grant role census_role to user census;

-- Change role to SYSADMIN for warehouse / database steps
use role SYSADMIN;

-- Optional: create a dedicated computation warehouse for Census
create warehouse if not exists census
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- Switch back to role to ACCOUNTADMIN for permissions steps

-- Grant Census access to warehouse
grant all privileges
on warehouse census
to role census_role;

-- Create database for Census 
-- Census will try to create this if it does not exist already
create database if not exists census;

-- Grant Census access to database
grant all privileges
on database census
to role census_role;

-- Grant Census the access it needs to the appropriate existing data 
-- Repeat for each database/schema you'd like Census to have access to

grant usage on database [INSERT-DATABASE-NAME] to role census_role;

grant usage on schema [INSERT-DATABASE-NAME].[INSERT-SCHEMA-NAME] to role census_role;

grant select on future tables in schema [INSERT-DATABASE-NAME].[INSERT-SCHEMA-NAME] to role census_role;
grant select on future views in schema [INSERT-DATABASE-NAME].[INSERT-SCHEMA-NAME] to role census_role;

IP Whitelisting

This account will always connect from one of these static IP addresses:
You may optionally limit Census’s access by whitelisting only these IPs. Visit the Snowflake Help Center for more details on how to specify these IPs as part of your network policy.