Snowflake Credit Spike? Check Query Compilation Time

A small warehouse I look after normally uses a third of a credit a day. One day it used seven.

No query executed for long. They did wait, in compile, for minutes at a time. Nothing was queued. Bytes scanned were trivial. Every query was fast to execute and slow to compile, and there were hundreds of them at once. Snowflake bills for the time the warehouse is running. The warehouse resumes when a statement is submitted and, in my query history, stayed up while statements sat in compile. So compile time kept the meter running even though execution was seconds.

Here's the query I ran to find it, and what I'd change.

Run this first

This splits the last six hours by warehouse into compile seconds and execute seconds. It uses the INFORMATION_SCHEMA table function, so it works without ACCOUNT_USAGE access, but without MONITOR on the warehouses you'll only see your own queries.

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 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;

This sums durations, so overlapping statements add up to more than wall-clock time. It shows where the time went, not the credits. Pair it with WAREHOUSE_METERING_HISTORY for the credits.

On a healthy dbt warehouse I get compile at 30 to 45 percent of compile plus execute and a worst single compile of a second or two. That's normal. Lots of short models, each one parsed and planned. The bad day looks different: worst compile in the tens or hundreds of seconds, and compile percent near 90.

Four things about this function:

• It keeps seven days of history and returns at most 10,000 rows, newest first. The 10,000-row cap is applied before the WHERE and GROUP BY, so on a busy account narrow the window until the row count comes back under the cap.
• Pass timestamps as TIMESTAMP_LTZ. A ::timestamp_tz literal gets rejected.
• end_time_range_end => current_timestamp() excludes running queries from the result.
• compilation_time and execution_time are in milliseconds.

What makes compile time grow

Two things, and each makes the other worse.

Deep query plans. A statement with dozens of chained CTEs, or a long chain of select prev.*, <new columns> steps, gives the optimizer a big plan to build. The query behind my bad day had 41 chained CTEs ending in a 14-deep select chain. It compiled in 31 seconds and executed in 1 to 4 seconds against 51 MB. Bytes scanned told me nothing.

Concurrency. Compilation runs in Snowflake's shared cloud services layer, not on your warehouse, and it slows down badly when many statements compile at once. Same query text, same day, measured by how many statements were compiling alongside it:

These are the buckets I had enough queries in; I had no observations between 10 and 29.

Concurrent statements	Compile time
under 10	33 s
30 to 79	145 s
80 to 159	286 s
160 and up	744 s, worst 995 s

The trigger that day was an automated session that sent about 275 validation SELECTs plus 21 EXPLAINs of that same heavy statement at once. EXPLAIN compiles in cloud services and does not use warehouse compute. Its cost that day was contention: 21 EXPLAINs of the heaviest statement compiling alongside 275 SELECTs slowed every compile on the account. The warehouse stayed resumed for most of the day across about 50 resume cycles.

Why auto-suspend didn't fire

Auto-suspend counts from the last statement finishing. In my query history, a compiling statement kept the warehouse from suspending, so a 60-second auto-suspend never kicked in while anything sat in compile. A trickle does the same thing. I've watched four parallel processes send about 400 small queries over six hours and keep an X-Small awake the whole time, six times the baseline cost, with nothing individually slow.

Fixes, in the order I'd try them

1. Flatten the heavy query. Materialize the middle of the CTE chain into a table or an intermediate dbt model. Compile time grows with plan depth, and the downstream query gets a much smaller plan.
2. Stop the fan-out. If a tool or agent validates by running many SELECTs or EXPLAINs in parallel, throttle it or batch the checks into one statement. Concurrency is the multiplier.
3. Move the noise to its own warehouse. Put automated validation on a small warehouse so it can't hold the production warehouse open. A separate warehouse isolates warehouse uptime, not cloud services compilation. Set auto-suspend to 60 seconds. A shorter setting buys nothing because Snowflake bills a 60-second minimum per resume. Read the query text first, though. Queries tagged as dbt usually belong on the dbt warehouse, and moving them only moves the bill.

One more check before you blame the warehouse

Read the query_text of the worst compilers before you decide a service is running on the wrong warehouse. In my case everything on the expensive warehouse belonged there. The cost was the shape of one query and how many times it was compiled at once, not where it ran.

I'm not a Snowflake performance specialist. I run a handful of accounts for clients and this is the check that caught the problem. If you have a better first query for this, send it my way.