Snowflake poor pruning: queries that read most of a table
Snowflake skips micro-partitions a query cannot need, which is called pruning.
A repeated query that scans most of a table's partitions keeps a warehouse busy longer than a well-pruned one.
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
For the costliest repeated query shapes: partitions scanned against partitions total, from QUERY_HISTORY, joined to credits from QUERY_ATTRIBUTION_HISTORY.
Why it costs money
- Each micro-partition stores the minimum and maximum of every column, so a filter on a column whose values are grouped together lets Snowflake skip most of the table.
- A filter on a column whose values are spread across every partition, or a function wrapped around the filtered column, can stop pruning.
- Reading more partitions means more warehouse time for the same answer.
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 marks a shape as poorly pruned when its runs scanned more than 80% of the partitions they could. It claims no saving.
When the report raises it. Optional query 5 is provided and a costly shape scans more than 80% of its partitions.
In the sample report
Poor pruning
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
- 1Copy the lookup query for the shape from the tool page and read the query text in your own Snowsight.
- 2Check whether the filter can be on a column the table is ordered or clustered by, such as a date.
- 3For a large table queried the same way every day, consider a clustering key, and watch its automatic clustering credits.
How to tell it worked. Compare PARTITIONS_SCANNED and the shape's attributed credits before and after.
Trade-offs. A clustering key adds automatic clustering credits while the table changes. It pays only for tables read much more than they are written.
The read-only query behind it
The same SQL the tool gives you. Each reads usage views and changes nothing.
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
- Snowflake auto-suspend: what idle warehouse time costsPriced
- Snowflake warehouse one size smaller: when it savesPriced
- Snowflake transient tables: cutting Fail-safe storagePriced
- Snowflake idle warehouse time: credits no query usedTo investigate
- Snowflake cloud services credits: when they billTo investigate
- Snowflake background services: credits outside warehousesTo investigate
- Snowflake warehouse pressure: queuing and spillingTo investigate
- Snowflake failed queries: warehouse time with no resultTo investigate
- Snowflake repeated queries: the same work every hourTo investigate
- Snowflake clone-retained storage: bytes kept for clonesTo investigate
- Snowflake stage storage: files left in internal stagesTo investigate
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.