Now Available On-Demand | AI. The Future of Data Engineering - Today. Not Just Tomorrow.

Watch now

Connecting Matillion ETL to Snowflake: A Guide

Connect 0

This guide is a walk-through of how to connect Matillion ETL to a Snowflake cloud data platform account.

In Matillion ETL, the metadata for connecting to Snowflake is held in an artifact known as an Environment. Matillion ETL Environments can also hold additional information that is used during data extraction and loading.

Prerequisites for connecting to Snowflake

The prerequisites for connecting Matillion ETL to Snowflake are:

  • Access to Matillion ETL for Snowflake
  • Permission to create and edit Environments in Matillion ETL
  • Your Matillion ETL instance has outbound internet access. However, if you are using private connectivity to Snowflake then your Matillion ETL instance only requires network access to the private Snowflake endpoint.
  • Snowflake credentials:
    • Snowflake account identifier
    • Snowflake username, plus its password or private key

Your chosen Snowflake user must have at least the following privileges:

 

Snowflake account identifiers

The preferred way to uniquely identify a Snowflake account is to use your Organization name and your Account name.

Your account identifier is the combination orgname-accountname

If you are using Snowsight, you can find your Organization name and Account name in the Organization area, as highlighted in the screenshot below.

If you are still using the Snowflake classic web interface, you can find your Organization name and Account name under your login name at the top right of the screen, as highlighted in the screenshot below.

If you are using a different SQL client, you can find your Organization name and Account name by running a SHOW ORGANIZATION ACCOUNTS query.

Note that this query requires access to the ORGADMIN role. If you do not have this, ask your Snowflake account administrator for the details.

Some Snowflake Organizations have multiple Accounts. You can find your Account name by using the CURRENT_ACCOUNT Snowflake function in a SQL query.

Use your your Organization and Account name as your Snowflake account identifier

Legacy Snowflake account identifiers

There is an older, alternative method that also uniquely identifies a Snowflake account. This is to use your account locator, plus a region and a cloud identifier.

The easiest way to find these identifiers is also to run a SHOW ORGANIZATION ACCOUNTS query. The information you will need is shown in the account_locator_url column, between the https:// and the snowflakecomputing.com part.

Private connectivity Snowflake account identifiers

If private connectivity has been enabled, you can use it by choosing a slightly different account identifier.

As a user with the ACCOUNTADMIN role, run the following SQL query:

SELECT key, value FROM TABLE(FLATTEN(input=>parse_json(system$get_privatelink_config())));

Use the value of the privatelink-account-name key.

If you do not have access to the ACCOUNTADMIN role, the query will fail. Ask your Snowflake account administrator for the information.

How to connect a Matillion ETL Environment to Snowflake

To create or edit a Matillion ETL Environment, start from the main Project menu, or from the context menu in the Environments panel.

This action will pop up a Create or an Edit Environment window containing three dialogs.

In the first dialog, choose a name for the Environment, and optionally set any AWS, Azure or GCP credentials that you intend to use.

In the second dialog, set the “Account” to the combination of your Organization name and Account name, separated by a hyphen. For example: ORGNAME-ACCOUNTNAME as shown in the screenshot below.

Then set the Snowflake username and its password or private key, using the Matillion password manager.

You may also choose to add Advanced Connection Settings to set individual JDBC driver connection parameters. In most cases you will not need to use Advanced Connection Settings.

Press Next to enter the third dialog. While you are completing the details, make certain of two things:

  • The dropdown lists for the default role, warehouse, database and schema all show values that you can select.
  • After completing the defaults, you get a success message when you press the Test button

If the dropdown lists are empty, you will need to go back to the previous screen and review the settings.

  • If the dropdown lists show a “Loading…” message for many seconds before going blank eventually, it is likely that your Snowflake Account setting is not correct, or your Matillion ETL instance does not have network access to the Snowflake endpoint.
  • If the dropdown lists go blank immediately, it is likely that your login credentials are not correct, or the user is insufficiently privileged.

 Similarly, when you press Finish, if the dialog hangs with a spinner for many seconds with a “Creating Environment…” message, it is likely that your Snowflake Account setting is not correct. You will need to go back and edit the Environment to correct it.

Press the Test button and make sure you see a success message before finishing the Environment Setup dialog

Connectivity metamodel for Matillion ETL and Snowflake

Matillion ETL stores Snowflake connection details inside an Environment object. The Environment contains the Snowflake account identifier and username, plus the security credentials, for example a password or private key.

Optionally, Environments also can hold additional cloud provider information, in the form of AWS, Azure, and GCP credentials. This information is used if you load data from (or via) external stage objects, and by various orchestration components such as the Data Transfer.

Matillion ETL Environments exist within a Project and a Group. Orchestration and Transformation Jobs are versioned within Matillion ETL projects. Jobs always execute within the context of exactly one Environment.

For Snowflake, the core administrative entity is the Account. Multiple accounts can be managed by one Snowflake Organization, although in many cases there is just one.

The unique account identifier is a combination of either:

  • Organization Name + Account Name (preferred), or
  • Locator + Region + Cloud

Once you’ve connected Matillion ETL and Snowflake

You can start to build workflows in Matillion ETL.

Learn more about using Matillion ETL and Snowflake

To read more about how Matillion ETL and Snowflake work together in a modern enterprise data stack, download our ebook, Optimizing Snowflake; A Real-World Guide.