Snowflake
This page describes how to sync data to your Snowflake data warehouse.
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.
- 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. |
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;
Object Name | Supported? | Sync Keys | Behaviors |
Table | ✅ | Primary keys or columns with uniqueness constraints | Update or Create, Update Only, Append |
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.
Last modified 1mo ago