Skip to content

Snowflake configuration for Matillion ETL

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

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

  5. 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.
  6. Use the Grant privileges to drop-down menu to assign warehouse privileges to a user's role. For more information, read Grant role privileges.

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

    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

    You must remember the name of the intended database. You will need to refer to this in your Matillion ETL instance later on.

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

    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.

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

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

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

    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.

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

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.

    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.

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

  3. 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.
  4. 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.
  5. 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.
  6. 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.
    • 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.
  7. Use the Grant privileges to drop-down menu to assign schema privileges to a user's role. For more information, read Grant role privileges.

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

    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.

© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement