Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Snowflake bill doubled

Snowflake, BigQuery & Databricks · Snowflake

Snowflake bill doubled

Hardwarehouses-16
scenariosnowflakecostcreditsresource-monitor

Question

The Snowflake bill doubled this month. How do you find out why and fix it?

Solution

Find out which part of the bill grew before you change anything. Snowflake cost is mostly warehouse credits, but serverless features and storage can also grow, so first split the bill by category and by warehouse.

Step 1: where did the credits go

The SNOWFLAKE.ACCOUNT_USAGE schema has the data (it can lag by some minutes to a few hours):

SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -60, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;

Break it by day to see when the jump began. METERING_DAILY_HISTORY splits cost by service type: warehouses, automatic clustering, Snowpipe, serverless tasks, search optimization, materialized views, and so on. If the growth is in one serverless service, you have your answer fast.

Step 2: which queries and who

QUERY_HISTORY shows who ran what, how long it took, and how much it scanned. Group by user, role, or query tag (if you set one) to find the heavy spenders. Look for a handful of expensive queries repeated many times.

Usual causes

  • A warehouse that no longer suspends: a long auto-suspend, or a BI tool or driver that sends a tiny query every few minutes and keeps it awake.
  • A warehouse resized upwards to fix one slow query and never resized back.
  • A dbt or pipeline change that turned incremental models into full refreshes.
  • A runaway task or a retry loop running every minute.
  • New automatic clustering on a busy table, a noisy Snowpipe with lots of tiny files, or a new materialized view.
  • Storage growth from long Time Travel on tables rewritten daily.

Fixes

Lower auto-suspend, right-size warehouses, fix the pipeline, drop clustering that is not paying off, and combine small files.

Prevent a repeat

Create resource monitors with credit quotas that notify at 75 and 90 percent and suspend at 100 percent. Use query tags so every pipeline and team is attributable, and set up a weekly cost report. Mention that you would explain the cause to the owner of the workload, because the cheapest fix is usually a conversation.

PreviousNext