Connecting To Snowflake

Overview


To connect Matillion ETL to Snowflake is a 2-part process:
  • The Snowflake account and assets must be set up correctly.
  • The Matillion ETL must use details of the Snowflake account to connect to it.
Below are 2 sections covering each step respectively. In the first step we will find 3 resources (Warehouse, Database, Schema) on the Snowflake account and ensure they are Matillion ETL ready.

In the second step we will fill out connection details in Matillion ETL to connect to the Snowflake account.

Note: To use Snowflake it is required that you have a working Matillion Instance hosted on AWS. A guide to setting up such an instance can be found here
 

Setting Up The Snowflake Account for Matillion ETL


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

https://<AccountName>.snowflakecomputing.com/console#/warehouses

For Matillion to connect to Snowflake, a default Warehouse, Database and Schema must be decided upon. The name of each must be input into Matillion.

When at the console, available Warehouses can be found at the 'Warehouses' tab at the top of the page.



It is only important to note the name of the warehouse you wish to be default within Matillion ETL. If desired, a new Warehouse can be created if necessary using the + 'Create' button.

Whether the Warehouse to be used is new or not, it is necessary that the user has full privileges to it. This can be accomplished by using the + 'Grant Privileges' button at the right-side of the window while a Warehouse is selected from the list.


Select a role that is attached to the user and select all privileges to go to this role. Then close the dialog using the 'Grant' button.

Matillion ETL also requires a default Database, reachable through the 'Databases' tab. Again, it is necessary to remember the name of the desired Database but new ones can be created using very similar to the description above.



Again, the Database must be set up to give privileges to role that is attached to the user. For databases, all privileges are required.

Finally, a default Schema must be chosen. Browse to the 'Schemas' section of the 'Databases' tab to view a list of Schemas. Even new Databases should come with some premade Schemas and again, a new Schema can be added with the + Create button. Once again, the selected Schema must give privileges to a role that is attached to the user. All privileges with the exception of 'CREATE PIPE' are required.

Now that you, as the user, have the names of your preferred Warehouse, Database and Schema as well as access to each through the privileges assigned to a role attached to you, Matillion can be launched.
 

Connecting Matillion ETL to Snowflake


Now log into your Matillion ETL instance that should be launched on AWS. Documentation describing this process can be found here. If the instance is new, the dialog to Create a Project should open as you log into the instance. Otherwise use Project → Switch Project → Create Project. A dialog similar to the one shown below should appear.


The Project Group, Project Name, Project Description and Name fields can be completed with anything convenient to the user. AWS and GCP credentials are, for the purpose of this article, optional although may be necessary for some functions and are detailed here.

In the 'Connection Settings' portion of the dialog, we will be using the Snowflake details.

Account: The name of the snowflake account (the subdomain of the login URL given at the top of this page).

Default Warehouse: The name of the default Warehouse as described in the previous section.

Default Database: The name of the default Database as described in the previous section.

Default Schema: The name of the default Schema as described in the previous section.

Username: The user of the Snowflake account that has the above described roles and privileges attached.

Password: The password matching that username.

Enable SSL:  Enable encryption of data passed between the Matillion ETL instance and the Snowflake Warehouse.

Test: Test these connection details.

When you are satisfied with the details, click 'OK' to connect to Snowflake. Matillion ETL is now ready for use with Snowflake!