Connecting To Snowflake
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.
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
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.
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. See here for more about case sensitivity in Snowflake.
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!