Run Query

Description

This job runs a SQL Query in Snowflake in an asynchronous fashion and checks it periodically

Use Cases

We recommend creating a Snowflake Run Query Task for every "Query" you need to run within Snowflake.

In Snowflake, pretty much everything you can do can be done using a query. This ranges from simple SQL statements to anything within the DDL/DML spectrum. Furthermore, you can also run Data Quality tests, dynamically assign roles, and clone tables using Snowflake Queries.

Snowflake also have a number of new and exciting use-cases such as provisioning Snowpark Container services, Dynamic Tables and Snowflake Tasks (which can render sub-dags). These are all supported in Orchestra too. There are a number of advantages to triggering Snowflake queries in Orchestra vs. other workflow orchestration tools

• You can co-ordinate tasks outside of Snowflake- these would typically be other Snowflake jobs, data loading steps, or other tasks in Snowflake-adjacent environments e.g. dbt, a BI tool

• You can use Orchestra to trigger jobs across Snowflake Accounts / Environments

• When Snowflake jobs run, Data Warehouse cost is incurred. Running these operations on a schedule you set explicitly ensures these costs do not go out of hand. Orchestra also fetches cost per query which can be monitored and easily accessed by users

• We aggregate metadata from the Snowflake Task in the same place as the metadata from other operations in your Pipeline

Parameters

These parameters are required to run the Snowflake Run Query Task

Name	Data type	Restrictions	Example
Query ID	String	N.A.	SELECT * FROM TABLE

Variants

Orchestra has a variant of the Run Query Task which is to run a query as a data quality test. This very much resembles how SQL tests in popular packages like dbt, Great Expectations, or Elementary are today. In the event that the "Run as Data Quality Test" box is ticked, Orchestra will fail the task if the query returns any results. This can be helpful for enforcing data quality tests upon teams that wish to interact with Snowflake but are not comfortable using dbt.

It can also be helpful in the case of wanting to do highly custom testing. It can be arduous to specify every single custom test in dbt using a macro - where this is not preferred, Orchestra can be used as a testing layer as well.

How to run a Snowflake Query in Orchestra. Note the option to "Run as data quality test"

Anomaly Detection using Snowflake Cortex

You can run an Anomaly Detection test by leveraging the multi-query statement below:

begin
    call orchestra_anomaly_model!DETECT_ANOMALIES(
        INPUT_DATA => SYSTEM$QUERY_REFERENCE("select sales,to_timestamp_ntz(date) as date from sales_data where date > '01-Jan-24'"),
        TIMESTAMP_COLNAME => 'date',
        TARGET_COLNAME => 'sales'
    );
    LET x := SQLID;
    CREATE OR REPLACE TEMPORARY TABLE my_anomalies AS SELECT * FROM TABLE(RESULT_SCAN(:x));
END;
SELECT * FROM my_anomalies where IS_ANOMALY = TRUE;

Error handling

Responses

If we receive the following error codes from the Snowflake driver (which we use, rather than the REST API Directly), we will throw an error

Error	Description	Behaviour
Any Python Error Class	Unauthorised	We will raise an error and parse what information is available from the response as the Orchestra message