Snowflake

Overview

Type: Warehouse

Website: https://www.snowflake.com/en/

General docs: https://docs.snowflake.com/

Authentication

To connect Snowflake to Orchestra, you will need:

  • a Snowflake account

Account

We recommend creating a new user, a new role, and a new warehouse to access Snowflake. This user and role will need to be given permissions to execute the queries being sent by Orchestra, and to be able to monitor the progress of them. There are more detailed instructions within the Orchestra UI on how to configure this new user and role.

A Snowflake connection is created from the properties listed below. user, password, and account identifier are static for each integration. The other properties can be configured at the Task level, but default values must be set here.

  • user: the name of the user connecting to Snowflake. This is shown in the top left of the screen when logged into Snowflake

  • password: the password of the user

  • account identifier: this is displayed by clicking the value in the bottom left corner, and then choosing the correct account, and clicking Copy account URL. The value Orchestra requires is https://{ACCOUNT}.snowflakecomputing.com (i.e. without the snowflakecomputing.com suffix)

    • We prefer the abc12345.region-subregion-cloudprovider format rather than the identifier (abc345.hyz134) format

  • warehouse: the name of the warehouse to use for the connection. A list of warehouses is shown in the Admin -> Warehouses section

  • database

  • schema

  • role: the list of roles is shown in the top left of the screen when logged into Snowflake

Permissions

There are some caveats with Snowflake permissions to be aware of when using Orchestra to execute queries and Tasks (Capital "T") on Snowflake:

  • In order to execute Snowflake Tasks, the Orchestra role needs to either be the owner of the task or have the global EXECUTE TASK privilege assigned to it This can be achieved by running the following command GRANT EXECUTE TASK ON ACCOUNT TO ROLE ORCHESTRA_ROLE; or GRANT OWNERSHIP ON TASK TEST_TASK TO ROLE ORCHESTRA_ROLE;

  • It is possible to trigger tasks in another warehouse to the one configured on either the task level or the connection level. In this case, Orchestra might be able to start the task but may not be able to monitor it. Ensure that the role specified for Orchestra to use has MONITOR and USAGE privileges on all warehouses in which tasks or queries may run:

    • GRANT MONITOR, USAGE ON WAREHOUSE "<WAREHOUSE>" TO ROLE <ORCHESTRA_ROLE>;

  • Currently, we do not validate that the user/role has access to the specified database/schema in the connection object - please double check that whatever user/role is being used is able to perform all the necessary actions on the resources it will be invoking

Job nameDescription

Snowflake run query

Executes a query using the driver in an asynchronous fashion, while monitoring the query

PreviousRun WorkflowNextRun Query

Last updated