Skip to main content
Feedback

Snowflake as a source connection

info

Snowflake as a Source is currently in the beta stage.

Snowflake is a robust cloud-based data warehouse that serves as a Source in data integration workflows and is known for its high performance, scalability, and separation of storage and compute. With Data Integration, you can seamlessly extract data from multiple sources and load it into Snowflake for analysis and reporting.

Using Snowflake as a Source in Data Integration, you can leverage Snowflake’s robust data warehousing and querying capabilities while integrating that data into workflows managed by Data Integration.

Snowflake as a source compatibility versions

River modesVersions supportedSupport for managing schema drift
Multi-Tables (Standard Extraction)All VersionsYes
Custom QueryAll VersionsYes
note

You must create a new connection for using Snowflake as a Source. You cannot use connections configured for Snowflake as a Target.

Prerequisites

Before proceeding, ensure you have an active Snowflake account.

If you do not have a valid Snowflake account, create one. If you or your company already has a Snowflake account, you can connect/create a Data Integration user.

Setting up your Snowflake environment

Data Integration Snowflake environment can be configured in two ways. Select only one option, depending on your access and security requirements.

  • Method 1: This method grants an existing user the SYSADMIN role, which enables Data Integration provides access to all objects in the database and extracts from and ingests to new and existing tables.

  • Method 2: This method either creates or uses existing user, role, database, and warehouse, depending on the settings, and provides Data Integration the ability to ingest data to and/or extract data from (depending on the settings) said objects.

Method 1: Global permission with sysadmin role(First Method)

Snowflake provides a single user to have multiple roles. Data Integration must execute various commands on Snowflake and assign a SYSADMIN role to the Data Integration user.

Procedure

  1. Navigate to your Snowflake console. Log in with a user who has access to the ACCOUNTADMIN role.
  2. Select the worksheet tab.
  3. Run the following commands:
   BEGIN;
   
    /* Set variables for script, select an existing user or create a new one, replace the value after var_user with 
    the name of the user, do not forget to replace {password} with the password of your choice 
    if you are creating a new user. */
   
      set var_user = 'Data_Integration_USER';
      set var_password = '{password}';
      
    /* switch to ACCOUNTADMIN role: only an ACCOUNTADMIN can set or unset a user’s role */
       USE ROLE accountadmin;
       
       /* Create a user for {conKeyRefs.DataIntegration} or use an existing one */
      create user if not exists identifier($var_user)
      password = $var_password;
      
       GRANT ROLE SYSADMIN TO USER identifier($var_user);
       ALTER USER identifier($var_user) SET DEFAULT_ROLE = SYSADMIN;
   COMMIT;
Method 2: Specific permission with custom role(Second Method)

Copy the following script to your Snowflake console, making sure to change the variables to your desired values and only run the necessary steps.

BEGIN;
 /* Set variables for script, You can choose to work with the default suggested values in the script or use your own. 
 Do not forget to replace {password} with the password of your choice if you are creating a new user. */
   SET var_user = 'DATA_INTEGRATION_USER';
   SET var_password = '{password}';
   SET var_role = 'DATA_INTEGRATION_ROLE';
   SET var_database = 'DATA_INTEGRATION_DATABASE';
   SET var_warehouse = 'DATA_INTEGRATION_WAREHOUSE';
   SET var_schema = 'DATA_INTEGRATION_SCHEMA';  -- Add your schema here
   
  /* Switch to securityadmin role:  
Role that can manage any object grant globally, as well as create, monitor, and manage users and roles */
   USE ROLE SECURITYADMIN;
   
   /* Create role for DATA_INTEGRATION */
   CREATE ROLE IF NOT EXISTS IDENTIFIER($var_role);
   
   /* Create a user for DATA_INTEGRATION */
   CREATE USER IF NOT EXISTS IDENTIFIER($var_user)
   PASSWORD = $var_password
 DEFAULT_ROLE = $var_role
 DEFAULT_WAREHOUSE = $var_warehouse;
   
   /* Grant role to the new user */
   GRANT ROLE IDENTIFIER($var_role) TO USER IDENTIFIER($var_user);
   
  /* switch to sysadmin role: 
Role that has privileges to create warehouses and databases (and other objects) in an account. */
   USE ROLE SYSADMIN;
   
   /* Create warehouse for Data_Integration (optional) */
   CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($var_warehouse)
 WAREHOUSE_SIZE = XSMALL
 WAREHOUSE_TYPE = STANDARD
 AUTO_SUSPEND = 300
 AUTO_RESUME = TRUE
 INITIALLY_SUSPENDED = TRUE;
   
   /* Create database for Data_Integration (optional) */
   CREATE DATABASE IF NOT EXISTS IDENTIFIER($var_database);
   
   /* Create schema in the database (optional) */
   CREATE SCHEMA IF NOT EXISTS IDENTIFIER($var_database).IDENTIFIER($var_schema);
  
  /* Grant Data_Integration role access to the warehouse */
   GRANT USAGE
   ON WAREHOUSE IDENTIFIER($var_warehouse)
   TO ROLE IDENTIFIER($var_role);
   
   /* Grant Data_Integration role access to the database */
   GRANT USAGE
   ON DATABASE IDENTIFIER($var_database)
   TO ROLE IDENTIFIER($var_role);
   
   /* Grant Data_Integration role access to the schema */
   GRANT USAGE
   ON SCHEMA IDENTIFIER($var_database).IDENTIFIER($var_schema)
   TO ROLE IDENTIFIER($var_role);
   
   /* Grant minimum access to tables for COPY INTO operations */
   GRANT SELECT
   ON ALL TABLES IN SCHEMA IDENTIFIER($var_database).IDENTIFIER($var_schema)
   TO ROLE IDENTIFIER($var_role);
   
   /* Grant the Data_Integration role the ability to use the COPY INTO command */
   GRANT INSERT
   ON ALL TABLES IN SCHEMA IDENTIFIER($var_database).IDENTIFIER($var_schema)
   TO ROLE IDENTIFIER($var_role);

COMMIT;
note

If you want to add Masking Policy permissions for this user, refer to the enforce masking policy section.

(Optional)Create network policy for Data Integration IPs

In some cases, your Snowflake account may be access-restricted by IPs or domains. In such cases, you must add Data Integration IPs to your Snowflake Network Policy to connect successfully.

note

Open Data Integration IPs in Snowflake Network Policies may block any other unspecified IPs in the network policy. Make sure you have whitelisted all your IPs in Snowflake’s network policies before creating Data Integration. Read more about network policies here.

Create a Network Policy for Data Integration IPs

Procedure

  1. Log in to your Snowflake account.
  2. Make sure the user is set to an ACCOUNTADMIN or SYSADMIN role.
  3. In the worksheet, run the following command:
   CREATE OR REPLACE NETWORK POLICY Data_IntegrationIPs ALLOWED_IP_LIST = (Copy our most recent whitelisted IPs here)
info

Data Integration Whitelist IPs

Supported objects

When using Snowflake as a source, the following objects are fully supported:

  • Tables
  • Views
  • Materialized Views (available exclusively for Enterprise accounts)

This compatibility ensures a seamless integration with Snowflake's data storage and processing capabilities. You can efficiently extract data from standard tables, which store raw data records, as well as from views, which may present aggregated or transformed data based on your business requirements.

Establishing a connection

You can connect to Snowflake using one of three methods:

You can connect to your Snowflake account using Basic Authentication.

Procedure

  1. Navigate to Data Integration console.
  2. Click Connections from the left-hand menu.
  3. In the Snowflake Connection form type, enter your Connection Name.
  4. Select the Basic Authentication Type.
  5. Enter your Username and Password.
    note

    Snowflake passwords are restricted from including special characters such as {, }, ", ?, /, #,*, and others.

  6. Enter the name of your Warehouse (mandatory).
  7. Enter your Database name (mandatory).
  8. Enter the name of your Role (optional).
    note

    If you leave it blank, the account's default one will be used. In this case, the default Role must have access to the selected Warehouse; otherwise, the connection will fail.

  9. Enter your Account Name (mandatory).
    note

    To obtain your Account Name, click on your account details at the bottom left of the Snowflake account. Hover over your account to copy your Account URL that follows the format: https://account-name.eu-central-1.snowflakecomputing.com. In this case, the Account Name is account-name.eu-central-1. If you use Snowflake on other cloud platforms, include the cloud provider in the account name by appending it with a dot. For example: account-name.eu-central-1.gcp. :::

  10. Click Test Connection to verify your connection is up to the task. If the connection succeeded, you can use this connection in Data Integration. You can pipe data to Snowflake by leveraging this connection in any River.
On this Page