Snowflake Configuration for Matillion ETL
  • Dark
    Light

Snowflake Configuration for Matillion ETL

  • Dark
    Light

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

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

Snowflake Warehouses.

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

Snowflake Warehouse creation.

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

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


Add Warehouse details.

Please Note

Make a note of all warehouse names you create, if you want to set one of them as the default within your Matillion ETL instance in the future.


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.

Grant Warehouse access.

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.

Set privileges.

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

Grant role privileges.

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


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


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.

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.

Snowflake databases.

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

Snowflake database creation.

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


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.

Database details.

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


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.

Grant database access.

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.

Set privileges.

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

Grant role privileges.

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


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.

Please 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. For more information, read Case Sensitivity in Snowflake.


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.

Access database schema.

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


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

Snowflake schema creation.

Please Note

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


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.

Schema details.

Please Note

In the Create Schema dialog, you have the two options. The first one, you can select the Show SQL link to view the SQL query that will create your schema. You can select the query if required. However, you cannot modify this field.

The second option in this dialog allows you to tick (unticked as default) the Managed Access checkbox, to allow the schema owner (the role of the ownership privilege to grant access, or revoke privileges on future objects within the intended schema. For more information, read Security Privileges Required to Manage Future Grants.


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.

Grant schema access.

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.

Please Note

All privileges with the exception of 'CREATE PIPE' are required.


Set privileges.

Please Note

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.


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

Grant role privileges.

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


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



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.