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 USAGE permissions on data warehouses.

  • Hevo is assigned USAGE and CREATE SCHEMA permissions on databases.

  • Hevo is assigned ALL permissions 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 into 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 into 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
    

Step 3 Create a Role and Grant Permissions

Snowflake uses the concept of Roles to assign privileges to the database user. The database user requires the following permissions:

  • The USAGE permission on the warehouse,

  • The USAGE and CREATE SCHEMA permissions on the database, and

  • The ALL permission on future schemas created.

You can either create a role and assign it these permissions or use the admin role.

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 into 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,CREATE SCHEMA 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.


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 BigQuery 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:

    • 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.



Revision History

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

Date Release Description of Change
Jul-12-2021 1.67 New document.
Last updated on 08 Jul 2021