Skip to main content

How to Auto-Cancel Running Queries

If a pipeline fails, one or more Snowflake queries may still be running, particularly if a pipeline job times out waiting for a runaway query to complete. To address this, you can implement a clean-up job at the end of pipelines to identify and cancel any such queries.

Configuration​

This solution has two main moving parts: a MATE macro to identify and cancel any running queries, and a pipeline job to execute the macro if the pipeline fails.

MATE macro​

Create the following macro in your DataOps project.

{% macro cancel_running_queries() %}
{% if execute %}

  {% set sql -%}
    SELECT QUERY_ID, QUERY_TEXT
    FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY())
    WHERE EXECUTION_STATUS = 'RUNNING'
      AND PARSE_JSON(QUERY_TAG):DataOps_Pipeline_ID::INT = {{ env_var('CI_PIPELINE_ID') }}
  {%- endset %}
  {% set results = run_query(sql) %}

  {% for row in results.rows %}
    {% if row['QUERY_TEXT'] != sql %}
      {{ log('Cancelling query ' ~ row['QUERY_ID'] ~ '(' ~ row['QUERY_TEXT'] ~ ')', True)}}
      {% set results = run_query("SELECT SYSTEM$CANCEL_QUERY('" ~ row['QUERY_ID'] ~ "')") %}
      {{ log('  - ' ~ results.rows[0][0], True) }}
    {% endif %}
  {% endfor %}

{% endif %}
{% endmacro %}

Clean-up job​

Add the following job and include it in all applicable pipeline files.

Careful...

The SNOWFLAKE_* parameters in the example job below may need adjusting depending on your project's configuration and secrets structure.

Cancel Abandoned Queries:
  extends:
    - .modelling_and_transformation_base
    - .agent_tag
  variables:
    TRANSFORM_ACTION: OPERATION
    TRANSFORM_OPERATION_NAME: cancel_running_queries
    SNOWFLAKE_ROLE: DATAOPS_VAULT(SNOWFLAKE.SOLE.ROLE)
    SNOWFLAKE_USER: DATAOPS_VAULT(SNOWFLAKE.SOLE.USERNAME)
    SNOWFLAKE_WAREHOUSE: DATAOPS_VAULT(SNOWFLAKE.SOLE.WAREHOUSE)
  stage: Clean Up
  script: /dataops
  icon: ${SNOWFLAKE_ICON}
  when: on_failure
include:
  ...

  ## Auto-cancel running queries
  - /pipelines/includes/local_includes/cancel_queries.sql

Running​

When a pipeline fails, the above job runs and should display output similar to the following if any running queries are found:

15:27:05  Running with dbt=1.5.4
15:27:06  Registered adapter: snowflake=1.5.0
15:27:06  Unable to do partial parsing because saved manifest not found. Starting full parse.
15:27:10  Found 8 models, 7 tests, 0 snapshots, 0 analyses, 801 macros, 0 operations, 0 seed files, 8 sources, 0 exposures, 0 metrics, 0 groups
15:27:11  Cancelling query 01aefc5e-0000-918b-0000-3de9006f3076(select min(1) from (select seq8() from table(generator(timelimit => 300))))
15:27:11    - query [01aefc5e-0000-918b-0000-3de9006f3076] terminated.
Last updated on