The Snowflake Cost Optimization Playbook

Find it

Inspect auto-suspend on every warehouse

SHOW WAREHOUSES;
-- Look at the auto_suspend column (seconds).
-- Anything 300+ on an interactive warehouse is usually worth tightening.

Fix it

Tighten to 60s for interactive / BI warehouses

ALTER WAREHOUSE bi_wh SET
  AUTO_SUSPEND = 60      -- seconds of idle before it suspends
  AUTO_RESUME  = TRUE;   -- wakes automatically on the next query

Find it

Find your biggest credit consumers (last 30 days)

SELECT warehouse_name,
       SUM(credits_used)                AS credits,
       SUM(credits_used_compute)        AS compute,
       SUM(credits_used_cloud_services) AS cloud_services
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY credits DESC;

Fix it

Right-size to the workload, then verify it still performs

ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'MEDIUM';
-- Re-run the representative jobs and compare execution_time + credits.
-- A larger warehouse finishes faster, so the right comparison is
-- credits-per-job, not credits-per-hour.

Find it

Surface heavy, poorly-pruned queries (last 7 days)

SELECT query_id,
       warehouse_name,
       partitions_scanned,
       partitions_total,
       bytes_scanned,
       execution_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND partitions_total > 0
  AND partitions_scanned >= partitions_total * 0.9  -- scanning almost everything
ORDER BY bytes_scanned DESC
LIMIT 50;

Fix it

Fix the heavy hitters, not every query

-- Common fixes, in rough order of effort vs. payoff:
--  * Filter on the columns data is naturally ordered by (load/event date).
--  * Select only needed columns — avoid SELECT * on wide tables.
--  * Add a clustering key only on very large, frequently-filtered tables:
ALTER TABLE events CLUSTER BY (event_date);
--  * Replace repeated heavy aggregations with a scheduled summary table.

Find it

See what serverless features are actually costing

SELECT service_type,
       SUM(credits_used) AS credits
FROM snowflake.account_usage.metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY credits DESC;
-- service_type includes AUTO_CLUSTERING, MATERIALIZED_VIEW,
-- SEARCH_OPTIMIZATION, PIPE, QUERY_ACCELERATION, and more.

Fix it

Turn off what no longer earns its keep

-- Example: drop auto-clustering on a table where it is not helping
ALTER TABLE big_table SUSPEND RECLUSTER;
-- Example: remove search optimization where queries no longer use it
ALTER TABLE big_table DROP SEARCH OPTIMIZATION;

Find it

Find your largest tables and their Time Travel / Fail-safe footprint

SELECT table_catalog,
       table_schema,
       table_name,
       active_bytes,
       time_travel_bytes,
       failsafe_bytes
FROM snowflake.account_usage.table_storage_metrics
ORDER BY (time_travel_bytes + failsafe_bytes) DESC
LIMIT 50;

Fix it

Match retention to how far back you actually recover

-- Shorten Time Travel on large staging/transient data:
ALTER TABLE staging_events SET DATA_RETENTION_TIME_IN_DAYS = 1;
-- Use TRANSIENT tables for reload-able staging (no Fail-safe):
CREATE TRANSIENT TABLE staging_events_t (...);

Find it

Check whether any resource monitors exist at all

SHOW RESOURCE MONITORS;
-- An empty or near-empty result is the finding.

Fix it

Put a budget with notify-then-suspend triggers in place

CREATE OR REPLACE RESOURCE MONITOR monthly_cap
  WITH CREDIT_QUOTA = 1000          -- your monthly budget in credits
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80  PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND
           ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = monthly_cap;