Snowflake

This page describes how to sync data to your Snowflake data warehouse.

🏃‍♀️ Getting Started

This guide will walk you through connecting to Snowflake as a destination.

If you are trying to use Snowflake as a data source (to query data from Snowflake and sync to elsewhere), that process is documented separately here: Snowflake Data Source

1. Visit the Destinations page and click + New Destination.

2. Select Snowflake from the menu.

3. Enter the requested database credentials:

Credential	Description
Account	Should be in the form iq18923.us-east-1
User	User Census will use to connect
Password	Password Census will use to connect
Warehouse	The Snowflake compute warehouse Census will use
Database	The database within the Snowflake account Census will connect to
Schema	[Optional] This can be enforced or left empty. If empty, you'll have the option to select this when creating a sync.
Number of Client Connections	Value between 1 and 8 (default is 1). This is the maximum number of concurrent connections Census will use to connect to database. The default should be fine in most cases, but increasing this value can increase throughput on very large syncs.
Use SSH Tunnel	Default: Off - Toggle on to indicate that Census should connect via an SSH Tunnel. For more information, see Network Access Controls
SSH Hostname	Hostname of the Census accessible SSH Tunnel bastion.
SSH Port	Port of SSH Tunnel bastion.
SSH Username	Username Census will use to connect to bastion.

🔑 Permissions

To use Snowflake as a destination, Census requires permission to write to the desired destination tables, as well as read metadata about the table and database structures.

-- Note that creating a user may be redundant if you're already configured
-- Snowflake as a source.

-- Create a role for the census user
CREATE ROLE CENSUS_ROLE;

-- Ensure the sysadmin role inherits any privileges the census role is granted. Note that this does not grant sysadmin privileges to the census role
GRANT ROLE CENSUS_ROLE TO ROLE SYSADMIN;

-- Create a warehouse for the census user, optimizing for cost over performance
CREATE WAREHOUSE CENSUS_WAREHOUSE WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = FALSE;

-- Allow the census user to run queries in the warehouse
GRANT USAGE ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;

-- Allow the census user to start and stop the warehouse and abort running queries in the warehouse
GRANT OPERATE ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;

-- Allow the census user to see historical query statistics on queries in its warehouse
GRANT MONITOR ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;

-- Create the census user
-- Do not set DEFAULT_WORKSPACE, this will impact which tables are visible to Census
CREATE USER CENSUS WITH DEFAULT_ROLE = CENSUS_ROLE DEFAULT_WAREHOUSE = CENSUS_WAREHOUSE PASSWORD = '<strong, unique password>';

-- Grant the census role to the census user
GRANT ROLE CENSUS_ROLE TO USER CENSUS;

-- Let the census user see this database
GRANT USAGE ON DATABASE "<your database>" TO ROLE CENSUS_ROLE;

-- Let the census user see this schema
GRANT USAGE ON SCHEMA "<your database>"."<your schema>" TO ROLE CENSUS_ROLE;

-- Grant census user ability to write data to tables within a schema
-- Note: this can also be granted to specific tables as well
GRANT INSERT, UPDATE, SELECT ON ALL TABLES IN SCHEMA "<your database>"."<your schema>" TO ROLE CENSUS_ROLE;

🔀 Supported Objects and Behaviors

Object Name	Supported?	Sync Keys	Behaviors
Table	✅	Primary keys or columns with uniqueness constraints	Update or Create, Update Only, Append

Contact us if you want Census to support more sync behaviors for Snowflake.

🚦Advanced Network Configuration

Census can successfully connect to Snowflake instances that are using advanced networking controls including region constraints, IP address allow lists, or SSH Tunneling. For more information, see our Network Access Controls documentation.

🚑 Need help connecting to Snowflake?

Contact us via [e-mail] or start a conversation with us via the in-app chat.