Snowflake

Last updated on Feb 03, 2023
On This Page

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

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 + Worksheet.

      Connect to Snowflake account

  2. Run 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 which depends on your cost & usability. Default: 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 + Worksheet.

      Connect to Snowflake account

  2. Run 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 USAGE permission on the warehouse and the database.

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. Click + Worksheet.

      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>;

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. Click + Worksheet.

        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.

      The bookkeeping schema has to be created only once, during the configuration of the selected database. The role that creates this schema has OWNERSHIP privileges on it.

  2. (Optional) Grant privileges on the hevo schema to other roles:

    Note: All roles who need to use Activate must have access to the hevo schema. Use these commands to grant privileges to other roles.

    1. Grant privileges using one of the following:

      • Grant all privileges:
      GRANT ALL PRIVILEGES ON SCHEMA hevo TO ROLE <role_assigned_to_activate_user>;
      • Grant only the essential privileges:
      GRANT USAGE ON SCHEMA hevo TO ROLE <role_assigned_to_activate_user>;
GRANT CREATE TABLE ON SCHEMA hevo TO ROLE <role_assigned_to_activate_user>;

    2. Verify the assigned privileges:

      SHOW GRANTS ON SCHEMA hevo; // check the assigned permissions on the schema.

Obtain your Snowflake Account and Region Name

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

      2. In the Select Warehouse page, click + ADD WAREHOUSE.

        Add Warehouse

      3. 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 the 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.

    • Snowflake Account URL: The account URL that you retrieved in Step 5 above.

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

    • Database Password: The password of the database user.

    • Database Name: The name of the database from which data is to be synchronized.

    • Schema Name: The name of the schema on which the database user has USAGE privileges. Default value: PUBLIC.

      Note: The 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 & CONTINUE.

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
Jun-21-2022 NA Updated the Configure Snowflake as a Warehouse section to reflect the UI changes.
Mar-31-2022 NA Updated the screenshots to reflect the latest Snowflake UI.
Sep-20-2021 1.72 Updated the following sections, Prerequisites, Create a Role and Grant Permissions to remove the mentions of the ALL permission, as the user creates the hevo schema now.
Updated the section, Create and Grant Privileges on the Bookkeeping Schema to grant appropriate permissions on the hevo schema to Activate roles.
Sep-09-2021 1.71 Added section, Create and Grant Privileges on the Bookkeeping Schema.
Jul-12-2021 1.67 New document.
Was this page helpful?

Tell us what went wrong