Snowflake Long Running Queries and Warehouse Timeout Sentinel

Runaway queries in Snowflake waste credits, block warehouse capacity, and can cascade into pipeline failures. Without automated detection, long-running queries often go unnoticed until a downstream dbt model times out or an invoice arrives. This sentinel queries SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY every 15 minutes for queries in RUNNING state that exceed the configured execution time threshold and fires a Slack alert with the offending query IDs, users, warehouses, and runtime in minutes.

How it works

1. query_long_running (io.kestra.plugin.jdbc.snowflake.Query) connects over JDBC and retrieves up to 10 queries currently in RUNNING state whose execution time exceeds inputs.execution_time_ms_threshold milliseconds, ordered by duration descending.
2. check_long_queries (io.kestra.plugin.core.flow.If) evaluates whether the result set is non-empty.
   • then, alert_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) posts a Slack message listing each query ID, owner, warehouse, and runtime in minutes.
   • else, log_ok (io.kestra.plugin.core.log.Log) records a clean check, confirming no runaway queries were found.
3. The errors block fires a Slack alert if the JDBC query itself cannot complete, preventing silent monitoring blackouts.
4. A Schedule trigger fires every 15 minutes and ships disabled.

What you get

• Automated detection of Snowflake queries running beyond a configurable time limit.
• Slack alerts with query IDs, user names, warehouse names, and elapsed minutes for fast triage.
• Audit log of every sentinel check in Kestra's execution history.
• Failure alerting if the sentinel itself loses connectivity to Snowflake.

Who it's for

• Data engineers and analytics engineers managing large Snowflake transformation workloads.
• Platform SREs responsible for shared warehouse utilization and cost governance.
• FinOps teams tracking credit waste from inefficient or stuck queries.

Why orchestrate this with Kestra

Snowflake does not provide native push alerts for long-running queries. Third-party monitoring tools can query QUERY_HISTORY but require additional infrastructure and credential management. Kestra encapsulates the query, conditional evaluation, Slack dispatch, and error handling in a single, version-controlled YAML flow with full execution history, without any extra servers or agents.

Prerequisites

• A Snowflake user with SELECT on SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY.
• A Slack incoming webhook for the alerting channel.

Secrets

• SNOWFLAKE_PASSWORD: Password for the Snowflake user in inputs.username.
• SLACK_WEBHOOK: Slack incoming webhook URL.

Quick start

1. Store SNOWFLAKE_PASSWORD and SLACK_WEBHOOK in your Kestra namespace secrets.
2. Import the blueprint and configure account, username, and execution_time_ms_threshold (default is 1 800 000 ms = 30 minutes).
3. Run manually to confirm connectivity and inspect the returned rows.
4. Enable the Schedule trigger for continuous 15-minute monitoring.

How to extend

• Add a io.kestra.plugin.jdbc.snowflake.Query task in the then branch to call SELECT SYSTEM$CANCEL_QUERY('<query_id>') and terminate runaway queries automatically after approval.
• Persist detected rows to a data warehouse table for trend analysis of slow-query frequency by user and warehouse.
• Add PagerDuty or OpsGenie webhook tasks for on-call escalation on critical warehouses.
• Parameterize LIMIT and the one-hour lookback window as additional inputs for broader coverage.

Links

• Kestra Snowflake Plugin
• Snowflake QUERY_HISTORY View
• Kestra Slack Plugin
• Kestra If Task