Skip to content
Finitizer
To investigate

Snowflake failed queries: warehouse time with no result

A query that fails still billed the warehouse time it used before it failed.

Jobs that fail and retry on a schedule can spend credits every day with nothing to show for it.

Run this check on your own account

Run read-only queries in your own console and paste the results. The report is worked out in your browser, with no access to your account.

What the check looks at

The count of failed queries on each warehouse over 30 days, from QUERY_HISTORY.

Why it costs money

  • The warehouse runs while a query works, whether it succeeds or not.
  • Retries run the same work again, often on the same failing input.
  • A failing job can also keep a warehouse from suspending.

How the tool measures it

To investigate. The report shows what this costs now and where to look. It claims no saving, because the results cannot prove one.

The report counts failed queries by warehouse, and query 5 marks repeated query shapes that fail. It claims no saving.

When the report raises it. Query 2 shows failed queries on any warehouse.

In the sample report

Failed queries

From the tool's own sample: made-up results for one account over 30 days, priced at Enterprise list prices in US East (N. Virginia).

What to do

  1. 1List the most frequent failures by error code in QUERY_HISTORY for the warehouse.
  2. 2Fix or pause the jobs that fail every run, starting with the longest running ones.

How to tell it worked. Count failed queries again for the same warehouse over the next two weeks.

Trade-offs. None for the bill. Fixing a job may need its owner, who may not know it fails.

The read-only queries behind it

The same SQL the tool gives you. Each reads usage views and changes nothing.

Query 2: warehouse activity, idle time and resumes
-- Finitizer Snowflake Cost Check, query 2 of 5: warehouse activity
-- sf-cost-check-v1 (2026-11-10). Read-only: it reads Account Usage and changes nothing.
-- Per warehouse: metered credits, the time queries kept it busy, and the idle time and
-- resumes it would have at each auto-suspend setting. Names and totals, no query text.
-- It reads 30 days of query history, so run it on a small warehouse. Allow a minute or two.
WITH w AS (
  SELECT DATEADD(day, -30, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))) AS ws,
         DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())) AS we
),
q AS (
  SELECT h.warehouse_name, h.warehouse_size, h.start_time, h.end_time, h.execution_time,
         h.execution_status, h.queued_overload_time,
         h.bytes_spilled_to_local_storage, h.bytes_spilled_to_remote_storage
  FROM snowflake.account_usage.query_history h, w
  WHERE h.start_time >= w.ws AND h.start_time < w.we
    AND h.warehouse_name IS NOT NULL
    AND h.warehouse_size IS NOT NULL
    AND h.end_time IS NOT NULL
),
o AS (
  SELECT q.*,
         MAX(end_time) OVER (PARTITION BY warehouse_name ORDER BY start_time, end_time
                             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end
  FROM q
),
g AS (
  SELECT o.*,
         GREATEST(0, DATEDIFF(millisecond, IFF(prev_end > start_time, prev_end, start_time), end_time)) / 1000 AS busy_s,
         IFF(prev_end < start_time, DATEDIFF(millisecond, prev_end, start_time) / 1000, NULL) AS gap_s
  FROM o
),
a AS (
  SELECT warehouse_name,
         MODE(warehouse_size) AS size_seen,
         COUNT(*) AS queries,
         COUNT_IF(UPPER(execution_status) <> 'SUCCESS') AS failed_queries,
         SUM(busy_s) AS busy_seconds,
         COUNT(gap_s) AS gaps,
         SUM(gap_s) AS gap_seconds,
         SUM(LEAST(gap_s, 75)) AS idle_60,
         COUNT_IF(gap_s > 75) AS resumes_60,
         SUM(LEAST(gap_s, 135)) AS idle_120,
         COUNT_IF(gap_s > 135) AS resumes_120,
         SUM(LEAST(gap_s, 315)) AS idle_300,
         COUNT_IF(gap_s > 315) AS resumes_300,
         SUM(LEAST(gap_s, 615)) AS idle_600,
         COUNT_IF(gap_s > 615) AS resumes_600,
         SUM(LEAST(gap_s, 915)) AS idle_900,
         COUNT_IF(gap_s > 915) AS resumes_900,
         SUM(LEAST(gap_s, 1815)) AS idle_1800,
         COUNT_IF(gap_s > 1815) AS resumes_1800,
         SUM(LEAST(gap_s, 3615)) AS idle_3600,
         COUNT_IF(gap_s > 3615) AS resumes_3600,
         SUM(LEAST(gap_s, 10815)) AS idle_10800,
         COUNT_IF(gap_s > 10815) AS resumes_10800,
         APPROX_PERCENTILE(execution_time, 0.95) / 1000 AS p95_execution_seconds,
         SUM(bytes_spilled_to_local_storage) AS spill_local_bytes,
         SUM(bytes_spilled_to_remote_storage) AS spill_remote_bytes,
         SUM(queued_overload_time) / 1000 AS queued_overload_seconds,
         COUNT_IF(queued_overload_time > 0) AS queued_queries
  FROM g
  GROUP BY warehouse_name
),
m AS (
  SELECT warehouse_name,
         SUM(credits_used_compute) AS credits_compute,
         SUM(credits_used_cloud_services) AS credits_cloud_services,
         SUM(credits_attributed_compute_queries) AS credits_attributed
  FROM snowflake.account_usage.warehouse_metering_history, w
  WHERE start_time >= w.ws AND start_time < w.we
  GROUP BY warehouse_name
)
SELECT COALESCE(m.warehouse_name, a.warehouse_name) AS warehouse_name,
       m.credits_compute, m.credits_cloud_services, m.credits_attributed,
       a.size_seen, a.queries, a.failed_queries, a.busy_seconds, a.gaps, a.gap_seconds,
       a.idle_60, a.resumes_60,
       a.idle_120, a.resumes_120,
       a.idle_300, a.resumes_300,
       a.idle_600, a.resumes_600,
       a.idle_900, a.resumes_900,
       a.idle_1800, a.resumes_1800,
       a.idle_3600, a.resumes_3600,
       a.idle_10800, a.resumes_10800,
       a.p95_execution_seconds, a.spill_local_bytes, a.spill_remote_bytes,
       a.queued_overload_seconds, a.queued_queries,
       TO_VARCHAR(w.ws, 'YYYY-MM-DD') AS window_start,
       TO_VARCHAR(w.we, 'YYYY-MM-DD') AS window_end
FROM m
FULL OUTER JOIN a ON a.warehouse_name = m.warehouse_name
CROSS JOIN w
ORDER BY m.credits_compute DESC NULLS LAST;
Query 5 (optional): costliest query shapes
-- Finitizer Snowflake Cost Check, query 5 of 5: query shapes (optional)
-- sf-cost-check-v1 (2026-11-10). Read-only: it reads Account Usage and changes nothing.
-- The 50 query shapes with the most compute credits, grouped by Snowflake’s parameterized
-- hash (the query with its literals removed), plus totals. A hash and counts, never query text.
-- QUERY_ATTRIBUTION_HISTORY leaves out queries of about 100 ms or less and idle time.
WITH w AS (
  SELECT DATEADD(day, -30, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()))) AS ws,
         DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())) AS we
),
a AS (
  SELECT query_id, query_parameterized_hash, warehouse_name,
         credits_attributed_compute, COALESCE(credits_used_query_acceleration, 0) AS qas_credits
  FROM snowflake.account_usage.query_attribution_history, w
  WHERE start_time >= w.ws AND start_time < w.we
),
q AS (
  SELECT h.query_id, h.execution_status, h.execution_time, h.bytes_scanned,
         h.partitions_scanned, h.partitions_total, h.bytes_spilled_to_local_storage,
         h.bytes_spilled_to_remote_storage, h.queued_overload_time, h.user_name, h.query_type
  FROM snowflake.account_usage.query_history h, w
  WHERE h.start_time >= w.ws AND h.start_time < w.we AND h.warehouse_size IS NOT NULL
),
s AS (
  SELECT a.query_parameterized_hash AS shape,
         MODE(a.warehouse_name) AS main_warehouse,
         COUNT(DISTINCT a.warehouse_name) AS warehouses,
         COUNT(*) AS runs,
         SUM(a.credits_attributed_compute) AS credits,
         SUM(a.qas_credits) AS qas_credits,
         COUNT_IF(UPPER(q.execution_status) <> 'SUCCESS') AS failed_runs,
         SUM(IFF(UPPER(q.execution_status) <> 'SUCCESS', a.credits_attributed_compute, 0)) AS failed_credits,
         SUM(q.execution_time) / 1000 AS execution_seconds,
         SUM(q.bytes_scanned) AS bytes_scanned,
         SUM(q.partitions_scanned) AS partitions_scanned,
         SUM(q.partitions_total) AS partitions_total,
         SUM(q.bytes_spilled_to_local_storage) AS spill_local_bytes,
         SUM(q.bytes_spilled_to_remote_storage) AS spill_remote_bytes,
         SUM(q.queued_overload_time) / 1000 AS queued_seconds,
         COUNT(DISTINCT q.user_name) AS users,
         MODE(q.query_type) AS query_type
  FROM a JOIN q ON q.query_id = a.query_id
  WHERE a.query_parameterized_hash IS NOT NULL
  GROUP BY a.query_parameterized_hash
),
t AS (
  SELECT COUNT(DISTINCT query_parameterized_hash) AS shapes_total,
         SUM(credits_attributed_compute) AS credits_total,
         COUNT(*) AS runs_total
  FROM a
)
SELECT s.*, t.shapes_total, t.credits_total, t.runs_total,
       TO_VARCHAR(w.ws, 'YYYY-MM-DD') AS window_start,
       TO_VARCHAR(w.we, 'YYYY-MM-DD') AS window_end
FROM s CROSS JOIN t CROSS JOIN w
ORDER BY s.credits DESC
LIMIT 50;

Other checks in the report

All checks

What a credit and a terabyte cost in each region and edition: Snowflake pricing in 2026. Finitizer does this for BigQuery today. Snowflake and Redshift support is on the way.