Snowflake

Snowflake offers a cloud-based data storage and analytics service, generally termed as data warehouse-as-a-service. Companies can use it to store and analyze data using cloud-based hardware and software.

In Snowflake, you can create both data warehouses and databases to store your data. Each data warehouse can further have one or more databases, although this is not mandatory. Snowflake provides you one data warehouse automatically when you create an account.

For Hevo to access your data, you must assign the required permissions. Snowflake uses Roles to assign permissions to users. You need ACCOUNTADMIN, SECURITYADMIN or SYSADMIN privileges to create the required roles for Hevo. Read more about Roles in Snowflake.


Prerequisites

  • An active Snowflake account.

  • The user has ACCOUNTADMIN or SECURITYADMIN privileges in Snowflake to create a role for Hevo.

  • The user must have ACCOUNTADMIN or SYSADMIN privileges in Snowflake, if a warehouse is to be created.

  • Hevo is assigned the USAGE permission on data warehouses and databases.

  • Hevo is assigned the ALL permission on the current and future schemas.


Perform the following steps to configure your Snowflake data warehouse:

Create a Snowflake Warehouse (Optional)

When you create an account, Snowflake automatically creates a warehouse for you. However, you can choose to create a different warehouse for your Activation.
To do this:

  1. Do one of the following to connect to your Snowflake account:

    • In your SQL client, connect to your Snowflake account.

    • Log in to your Snowflake instance. Click Worksheets at the top of the page.

      Connect to Snowflake account

  2. Execute the following commands:

    USE ROLE <ACCOUNTADMIN or SYSADMIN>;
    
    CREATE WAREHOUSE <warehouse_name> WITH AUTO_RESUME = TRUE
    WAREHOUSE_SIZE = <size> //size = {XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE} AUTO_SUSPEND = <time_in_seconds>; //default suspending time is 600 seconds
    

    In the above commands:

    • Auto-resume specifies whether to automatically resume a warehouse when a SQL query is submitted to it.

    • Warehouse size is the size of the warehouse depends on your cost & usability - by default it is set to XSmall.

    • Auto-suspend specifies the number of seconds of inactivity after which the warehouse is automatically suspended. When data is received, the warehouse is automatically resumed if Auto-resume is set to True.


Create a Snowflake Database (Optional)

You can use the default database provided with your Snowflake data warehouse for loading the data or create another database as per your requirements.

To create a database:

  1. Do one of the following to connect to your Snowflake account:

    • In your SQL client, connect to your Snowflake account.

    • Log in to your Snowflake instance. Click Worksheets at the top of the page.

      Connect to Snowflake account

  2. Execute the following commands:

    USE ROLE <ACCOUNTADMIN or SYSADMIN>;// use one of the roles
    CREATE DATABASE <database_name>;# replace <database_name> with a database name
    

Create a Role and Grant Permissions

The database user requires the following permissions:

  • The USAGE permission on the warehouse and the database.

  • The ALL permission on future schemas created.

Snowflake uses the concept of roles to assign permissions to the database user. You can either create a role and assign it these permissions or use the admin role.

The database user must create the bookkeeping schema. Activate uses this schema to efficiently determine the data to be loaded to the Target. This is a feature introduced in Release 1.69.

To create a role and grant permissions:

  1. Do one of the following to connect to your Snowflake account:

    • In your SQL client, connect to your Snowflake account.

    • Log in to your Snowflake instance and click Worksheets at the top of the page.
      Connect to Snowflake account

  2. Enter the following commands:

    1. Create a role for the database user:

      USE ROLE <ACCOUNTADMIN or SECURITYADMIN>;// use one of the roles
      CREATE ROLE <role_name> COMMENT = 'Role for Hevo access';# replace <role_name> with a name for the role
      
    2. Optional: If you have created a hierarchy whereby all custom roles are managed by and granted to the SYSADMIN, you can grant this new role to the SYSADMIN:

      GRANT ROLE <role_name> to role SYSADMIN;
      
    3. Optional: Create a Snowflake warehouse (optional):

      Skip this step if you want to use an existing data warehouse.

      USE ROLE <ACCOUNTADMIN or SYSADMIN>;
      CREATE WAREHOUSE <warehouse_name> WITH AUTO_RESUME = TRUE
      WAREHOUSE_SIZE = <size> //size = {XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE} AUTO_SUSPEND = <time_in_seconds>; //default suspending time is 600 seconds
      
    4. Grant warehouse-level privileges to the role you created:

      GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <role_name>;
      
    5. Create the database user:

      CREATE USER IF NOT EXISTS <user_name># replace <user_name> with a name for the user
      PASSWORD='<password>'
      COMMENT='User for Hevo database user'
      DEFAULT_ROLE='<role_name>'
      DEFAULT_WAREHOUSE='<warehouse_name>';
      
    6. Grant role to the database user

      GRANT ROLE <role_name> TO USER <user_name>;
      
    7. Grant database-level privileges to the role you created:

      GRANT USAGE ON DATABASE <database_name> TO ROLE <role_name>;
      
    8. Grant ALL permission on the future schema to the role you created:

      GRANT ALL ON FUTURE SCHEMAS IN DATABASE <database_name> TO ROLE <role_name>;
      

      Note: The ALL permission allows Hevo to access to your data and monitor usage at the object level for the warehouse and the database.


Create and Grant Privileges on the Bookkeeping Schema

Hevo maintains a bookkeeping schema, hevo, for all Activations in the configured database. If this schema does not exist, you must create it.

  1. Create the schema:
    1. Connect to your Snowflake account using one of these methods:

      • In your SQL client, connect to your Snowflake account.

      • Log in to your Snowflake instance and click Worksheets at the top of the page. Connect to Snowflake account

    2. Access the configured database:

      USE [DATABASE] <database_name>; # The DATABASE keyword is optional.
      
    3. Create the bookkeeping schema:

      USE ROLE <role_name>; // Use the role created earlier for the database user.
      CREATE SCHEMA IF NOT EXISTS hevo; # The schema is created only if it does not exist.
      

      Note: The bookkeeping schema has to be created only once during the configuration of the selected database.

  2. Grant privileges on the schema to the role you created for Activate:

    GRANT ALL PRIVILEGES ON SCHEMA hevo TO ROLE <role_assigned_to_activate_user>;
    GRANT CREATE STAGE ON SCHEMA hevo TO ROLE <role_assigned_to_activate_user>;
    

    Note: All users who need to create, modify, and execute Activations must have access to the hevo schema.


Obtain your Snowflake Account and Region Names

The Snowflake data warehouse may be hosted on any of the following Cloud providers:

  • Amazon Web Services (AWS)

  • Google Cloud Platform (GCP)

  • Microsoft Azure (Azure)

You can derive the account name and region from your Snowflake web interface URL.

For most accounts, the URL looks like https://account_name.region.snowflakecomputing.com.

For example, https://westeros.us-east-2.aws.snowflakecomputing.com. Here, westeros is your account name, us-east-2 is the region, and aws is the service provider.

However, if your Snowflake instance is hosted on AWS, US West region, the URL looks like https://account_name.snowflakecomputing.com.

Select the account name and region as per the applicable URL.


Configure Snowflake as a Warehouse

Skip this step if you already have a Snowflake data warehouse set up.

  1. Do one of the following:

    • Click ACTIVATE in the Asset Palette, and:

      1. In the ACTIVATIONS tab, click + CREATE ACTIVATION.

      Create Activation

      1. In the Select Warehouse page, click ADD WAREHOUSE.

      Add Warehouse

      1. In the Select Warehouse Type page, select Snowflake.
    • Click DESTINATIONS in the Asset Palette, and:

      1. Click + CREATE in the Destinations List View.

      2. In Add Destination page select Snowflake as the Destination type.

  2. In the Configure your Snowflake Warehouse page, specify the following:

    Snowflake Warehouse Settings

    • Warehouse Name: A unique name for your Warehouse.

    • Account Name: The name provided by Snowflake to your account.

    • Region: The region in which the Snowflake account is located. If no account region is present in URL then your account is located in US West (Oregon).

    • Database User: The Hevo user that you created. This user has a non-administrative role in the Snowflake database.

    • Database Password: Password of the database user.

    • Database Name: Name of the database where the data is to be loaded.

    • Schema Name: Name of the schema in the database where the data is to be loaded.

      Note: Schema name is case-sensitive.

    • Warehouse: The Snowflake warehouse associated with your database, where the SQL queries and DML operations are performed.

  3. Click Test Connection to test connectivity with the Snowflake warehouse.

  4. Once the test is successful, click SAVE WAREHOUSE.


Limitations

None.


See Also


Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Sep-09-2021 1.71 Added section, Create and Grant Privileges on the Bookkeeping Schema.
Jul-12-2021 1.67 New document.
Last updated on 09 Sep 2021