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
Using Snowflake on AWS VPS, PrivateLink, and Azure
- Permission to create a new
censusdatabase or an existing
censusdatabase 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_schemaschema, 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 use role ACCOUNTADMIN; -- 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 password = '[INSERT-STRONG-PASSWORD-HERE]'; 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 use role ACCOUNTADMIN; -- 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;