Contents x
    Snowflake Configuration for Matillion ETL
      • Dark
        Light
      Contents

      Snowflake Configuration for Matillion ETL

        • Dark
          Light
        Article Summary

        Overview

        This guide provides a brief summary of the how new Snowflake resources should be configured for use with Matillion ETL as launched from the Matillion Hub.

        If you are new to Snowflake, it is important to first refer to Snowflake's Getting Started documentation.

        Default Warehouse

        1. Matillion ETL requires a default warehouse. Access the Snowflake Console, and click Warehouses, situated at the top of the console to view a list of available warehouses.

        2. To create a Snowflake Warehouse, click +Create, situated near the top of the console.

        3. Enter details of the new warehouse in the fields provided, and click Finish to create the new Snowflake Warehouse.

        Note
        • Optionally, you can select the Show SQL link to view the SQL query that will create your warehouse. You can select the query if required. However, you cannot modify this field.
        • We advise making note of your new warehouse names if you want to set one as the default within your Matillion ETL instance.

        1. New and existing warehouses must allow you to have full access to them. To grant these privileges, select the intended warehouse from the available list, and click +Grant Privileges, situated to the right-hand side of the console.

        2. The Grant Privileges pop-up dialog will appear for the selected warehouse. Use the Privileges to grant drop-down menu to select the appropriate type of access.

        3. Use the Grant privileges to drop-down menu to assign warehouse privileges to a user's role. For more information, read Grant role privileges.

        4. Click Grant to set your grant privileges on the selected Snowflake warehouse.

        Note

        The with Grant Option enables the selected role to grant privileges to other roles. This option is un-selected by default. Tick the checkbox to enable it. For more information, read Optional Parameters.

        Default Database

        All data in Snowflake is stored and maintained in databases. Each database consists of one or more schemas (this will be explained later), which are logical groupings of database objects, such as tables and views.

        1. Matillion ETL requires a default Database. Access the Snowflake Console, and click Databases, situated at the top of the console to view a list of available databases.

        2. To create a Snowflake database, click +Create, situated near the top of the console. For more information, read Create Database.

        Note

        It is important to note, you must remember the name of the intended database. You will need to refer to this in your Matillion ETL instance later on.

        1. The Create Database pop-up dialog will appear. Enter a new Name and provide details in the Comment field for the database you are creating, then click Finish.
        Note

        In the Create Database dialog, you have the option to select the Show SQL link to view the SQL query that will create your database. You can select the query if required. However, you cannot modify this field.

        1. New and existing databases must allow you to have full access to them. To grant these privileges, select the intended database from the available list, and click +Grant Privileges, situated to the right-hand side of the console.

        2. The Grant Privileges pop-up dialog will appear for the selected database. Use the Privileges to grant drop-down menu to select the appropriate type of access.

        3. Use the Grant privileges to drop-down menu to assign database privileges to a user's role. For more information, read Grant role privileges.

        Note

        The with Grant Option enables the selected role to grant privileges to other roles. This option is un-selected by default. Tick the checkbox to enable it. For more information, read Optional Parameters.

        1. Click Grant to set your grant privileges on the selected Snowflake database.

        Default Schema

        A schema determines the structure of a database. A default schema is required within your Matillion ETL instance.

        Note

        Before creating a schema, it is important to mention Matillion ETL uses quoted identifiers, meaning that (by default) all identifiers are case-sensitive in Snowflake. Thus, all table and column names used in Matillion ETL components are case sensitive by default.

        If you wish to change this (and have all identifiers resolve to uppercase names automatically), consider changing the QUOTED_IDENTIFIERS_IGNORE_CASE parameter inside Snowflake.
        This option is not available from within Matillion ETL, and should be decided as early in the process as possible.

        1. To view and create your own schema, access the Snowflake Console, and click Databases, situated at the top of the console, then from the list of databases, click on the intended database. Click Schemas, situated at the top of the console.
        Note

        New Databases will include pre-made Schemas. You can view these in the Schemas tab, situated at the top of the intended database's console page.

        1. To create a new Snowflake schema, click +Create, situated near the top of the console. For more information, read Create Schema.

        2. The Create Schema pop-up dialog will appear. Enter a new Name and provide details in the Comment field for the schema you are creating, then click Finish.

        3. This dialog allows you to select Managed Access to allow the schema owner to grant access, or revoke privileges on future objects within the intended schema. For more information, read Security Privileges Required to Manage Future Grants.

        4. New and existing schemas must allow you to have full access to them. To grant these privileges, select the intended schema from the available list, and click +Grant Privileges, situated to the right-hand side of the console.

        5. The Grant Privileges pop-up dialog will appear for the selected schema. Use the Privileges to grant drop-down menu to select the appropriate type of access.

        Note
        • All privileges with the exception of 'CREATE PIPE' are required.
        • An additional drop-down menu called Privileges to grant will be available to use, if you ticked the Managed Access checkbox earlier in the Create Schema pop-up dialog. For more information, read Grant Privileges.

        1. Use the Grant privileges to drop-down menu to assign schema privileges to a user's role. For more information, read Grant role privileges.

        2. Click Grant to set your grant privileges on the selected Snowflake schema.

        Note

        The with Grant Option enables the selected role to grant privileges to other roles. This option is un-selected by default. Tick the checkbox to enable it. For more information, read Optional Parameters.

        Gathering Snowflake Account Details

        The following section provides details on gathering information about your Snowflake account.

        Login into your Snowflake account at snowflakecomputing.com. The login URL should take the form:

        https://<AccountName>.<region_id>.snowflakecomputing.com/

        or if your region is US West:

        https://<AccountName>.snowflakecomputing.com/

        Thus your account name is:

        <AccountName>.<region_id>

        Azure users must include the ".azure" part of the URL.

        <AccountName>.<region_id>.azure

        For more information on Snowflake Region IDs and their place in Account URLs, read Snowflake documentation.

        What's Next