--- name: snowflake-cost-spike-check description: Diagnoses a Snowflake credit or cost spike by comparing warehouse credits with query compile time, execution time and query volume, using read-only queries. Use when someone asks why Snowflake credits jumped, why a warehouse stayed on, or why auto-suspend did not seem to work. --- # Snowflake Cost Spike Check Find out why a Snowflake warehouse used more credits than usual. Work from evidence: credits first, then where the time went, then which queries kept the warehouse running. Every query in this skill is read-only. Never run `ALTER`, `CREATE`, `DROP`, `GRANT` or any statement that changes the account. Recommend changes; let the user make them. ## Before you start Ask the user for, or confirm: 1. **Which warehouse or warehouses**, or "all" if they do not know. 2. **The spike window**: the day or hours that looked wrong, and a normal period to compare against (for example, the same weekday a week earlier). 3. **Their role.** Ask which role they will run the queries as. 4. **Whether they can run SQL** themselves, or will paste results from Snowsight, or will paste a screenshot or export of the cost page. ### Access and latency caveats (tell the user) - `SNOWFLAKE.ACCOUNT_USAGE` views need the right role. By default only `ACCOUNTADMIN` can read them. Other roles need `IMPORTED PRIVILEGES` on the `SNOWFLAKE` database, or a Snowflake database role that grants access to these views. If a query fails with "does not exist or not authorized", it is usually access, not a typo. - `ACCOUNT_USAGE` has latency. Rows arrive minutes to a few hours after the activity, and the delay differs by view. A spike from the last few hours may be missing or partial. Say so if the window is recent. - For the last few hours, `INFORMATION_SCHEMA.QUERY_HISTORY()` (a table function) has less delay and works without `ACCOUNT_USAGE` access. It keeps only seven days of history, returns at most 10,000 rows, and without `MONITOR` on a warehouse it shows only the user's own queries. ## Step 1: Confirm the spike in credits Use `WAREHOUSE_METERING_HISTORY`. It is hourly, by warehouse, and is the source for credits. ```sql select warehouse_name, date_trunc('day', start_time) as day, round(sum(credits_used), 2) as credits_used, round(sum(credits_used_compute), 2) as credits_compute, round(sum(credits_used_cloud_services), 2) as credits_cloud_services from snowflake.account_usage.warehouse_metering_history where start_time >= dateadd('day', -14, current_timestamp()) group by 1, 2 order by 1, 2; ``` Check: - Is the spike in one warehouse or many? One warehouse points to a workload. Many points to a schedule, a new tool or a shared cause. - Is it compute credits or cloud services credits? Compute means the warehouse was running. Cloud services can rise with heavy compilation or metadata work. - Then narrow to hours for the spike day by changing `date_trunc('day', ...)` to `date_trunc('hour', ...)`. A warehouse billed every hour of the day was never idle. Do not compute dollars unless the user gives their contracted price per credit. Report credits. ## Step 2: Split the time into compile and execute Credits tell you how much. Query history tells you where the time went. Compare compile time with execution time for the spike window. ```sql select warehouse_name, count(*) as queries, round(sum(compilation_time) / 1000) as compile_seconds, round(sum(execution_time) / 1000) as execute_seconds, round(sum(compilation_time) / nullif(sum(compilation_time) + sum(execution_time), 0) * 100) as compile_pct, round(max(compilation_time) / 1000) as worst_compile_seconds from snowflake.account_usage.query_history where start_time >= ''::timestamp_ltz and start_time < ''::timestamp_ltz and warehouse_name is not null group by 1 order by compile_seconds desc; ``` Run the same query for the normal period and compare. Notes: - `compilation_time` and `execution_time` are in milliseconds. - Durations are summed, so overlapping queries add up to more than wall-clock time. This shows where time went, not credits. - A healthy dbt-style workload with many short models can show a meaningful compile share and a worst compile of a second or two. A problem looks different: worst compile in the tens or hundreds of seconds, and compile share far above the normal period. For the last few hours, use the `INFORMATION_SCHEMA` version instead: ```sql select warehouse_name, count(*) as queries, round(sum(compilation_time) / 1000) as compile_seconds, round(sum(execution_time) / 1000) as execute_seconds, round(max(compilation_time) / 1000) as worst_compile_seconds from table(information_schema.query_history( end_time_range_start => dateadd('hour', -6, current_timestamp()), end_time_range_end => current_timestamp(), result_limit => 10000)) where warehouse_name is not null group by 1 order by compile_seconds desc; ``` Pass timestamps as `TIMESTAMP_LTZ`. The 10,000-row cap applies before the `WHERE` and `GROUP BY`, so on a busy account narrow the window until the row count is under the cap. ## Step 3: Find what kept the warehouse awake Look at volume by hour and by who sent it. ```sql select date_trunc('hour', start_time) as hour, user_name, role_name, query_type, count(*) as queries, round(sum(compilation_time) / 1000) as compile_seconds, round(sum(execution_time) / 1000) as execute_seconds, round(sum(queued_overload_time) / 1000) as queued_seconds from snowflake.account_usage.query_history where warehouse_name = '' and start_time >= ''::timestamp_ltz and start_time < ''::timestamp_ltz group by 1, 2, 3, 4 order by 1, queries desc; ``` Then list the worst compilers and read their text: ```sql select query_id, user_name, query_tag, query_type, round(compilation_time / 1000) as compile_seconds, round(execution_time / 1000) as execute_seconds, bytes_scanned, left(query_text, 500) as query_text_start from snowflake.account_usage.query_history where warehouse_name = '' and start_time >= ''::timestamp_ltz and start_time < ''::timestamp_ltz order by compilation_time desc limit 25; ``` ## Step 4: Match the pattern Use what you found to pick the most likely cause. Name the evidence for each. | Pattern in the results | Likely cause | |---|---| | Worst compile far above normal, execution seconds small, bytes scanned small | Deep query plans (many chained CTEs or long `select prev.*` chains) compiling slowly | | Many statements compiling at once, compile times climbing with concurrency | Fan-out: a tool, agent or test suite sending many `SELECT`s or `EXPLAIN`s in parallel | | Steady trickle of small queries across many hours, nothing slow | Trickle keeping the warehouse resumed; auto-suspend counts from the last statement finishing | | Execution seconds and bytes scanned up sharply | A heavier query, a missing filter, or more data | | High `queued_overload_time` | Warehouse too busy for the load; check concurrency before resizing | | Spike only in cloud services credits | Heavy compilation or metadata work; check compile time and query count | Two checks before concluding: - **Auto-suspend did fire, or did not need to.** `AUTO_SUSPEND = 60` does not mean the warehouse stops after 60 seconds. The timer starts after the last statement finishes. A trickle or long compiles can keep it up all day with auto-suspend working as designed. - **Read the query text before blaming the warehouse.** Queries tagged as dbt usually belong on the dbt warehouse. Moving them only moves the bill. ## Step 5: Recommend fixes, in order 1. **Flatten the heavy query.** Materialize the middle of a long CTE chain into a table or intermediate model so the downstream plan is smaller. 2. **Stop the fan-out.** Throttle a tool that runs many checks in parallel, or batch the checks into one statement. Concurrency multiplies compile time. 3. **Move automated noise to its own small warehouse** with 60-second auto-suspend, so it cannot hold the production warehouse open. A separate warehouse isolates warehouse uptime, not cloud services compilation. A setting below 60 seconds buys nothing because each resume bills a 60-second minimum. 4. **Add or check resource monitors.** An account-level monthly monitor and a daily monitor on the development warehouse, with notify thresholds before suspend. Monitor emails need the recipient to verify their email and turn on notifications in Snowsight. 5. **Separate workloads** if loading, transformation, reporting and development share one warehouse. One warehouse per workload makes the next spike easier to trace. ## Output format Reply with: 1. **Summary**: one or two sentences. Which warehouse, how many credits on the spike day against the normal period, and the most likely cause. 2. **Evidence**: a small table of the numbers from each step, with the query each came from. 3. **Cause**: the pattern from Step 4 and why the evidence supports it. If the evidence is mixed, say so and list what would settle it. 4. **Fixes**: from Step 5, in the order to try them, each tied to the evidence. 5. **Gaps**: anything you could not check (latency, missing access, only own queries visible). Do not invent numbers. If a query was not run, say it was not run. Built by Greyfield Data (greyfielddata.com). Free to use and adapt.