In this post we are going to cover scaling, concurrency and cost control in Snowflake: multi-cluster warehouses, the standard and economy scaling policies, statement timeouts, resource monitors and budgets, the query acceleration service, and the views that show where the credits go. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where the Snowflake bill doubled and you have to find the cause and bring it down.
In Snowflake, you pay for compute while it runs. So every scaling choice is also a cost choice: more clusters, a bigger size or a serverless feature all burn credits. Interviewers ask about this because it is what the business feels first: dashboards that queue at 9 AM, a query that runs for six hours, and a bill nobody can explain.
Part 2 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Question 1: At 9 AM, fifty analysts open dashboards and queries start queuing. How do multi-cluster warehouses fix it?
- Question 2: Your multi-cluster warehouse scales out too fast and costs too much. What is the difference between the standard and economy scaling policies?
- Question 3: One bad query ran for six hours and burned credits. How do you stop that from happening again?
- Question 4: Finance wants a hard monthly limit on credits. How do resource monitors and budgets work?
- Question 5: A few large queries make the whole warehouse slow. What is the query acceleration service, and when does it help?
- Question 6: Leadership asks where the Snowflake credits go each month. Which views and tools do you use to find out?
- Scenario 7: The Snowflake bill doubled this quarter. Walk me through how you find the cause and bring it down.
The short answer
Queuing is a concurrency problem: a multi-cluster warehouse adds clusters, and the scaling policy trades waiting for credits. Statement timeouts stop runaway queries, resource monitors give a hard stop on warehouses, budgets cover serverless too, and the ACCOUNT_USAGE views show where every credit goes.
Keep that in mind. Every question below is one piece of it.
Question 1: At 9 AM, fifty analysts open dashboards and queries start queuing. How do multi-cluster warehouses fix it?
This is a concurrency problem, not a query size problem. A cluster runs eight queries at once by default, and the rest wait in the queue. Snowflake itself says resizing isn't meant for concurrency.
The fix is a multi-cluster warehouse, on Enterprise edition and higher. It runs more than one cluster of the same size. Let's create one for BI:
CREATE WAREHOUSE bi_wh WITH
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3
SCALING_POLICY = STANDARD
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE;This is auto-scale mode: you set a minimum and a maximum. When queries queue, Snowflake starts another cluster, and as the load drops, it stops them again. There is also maximized mode, where the minimum equals the maximum and all clusters run whenever the warehouse runs.
| Mode | Clusters | Use it for |
|---|---|---|
| AUTO-SCALE | minimum below maximum: start on queuing, stop as load drops | peaks like 9 AM |
| MAXIMIZED | minimum equals maximum: all run with the warehouse | steady, known load |
For a morning peak, use auto-scale.
Now the credits. They are the size rate times the running clusters, per second. A Medium uses 4 credits an hour, so this warehouse with up to three clusters can use 12 credits an hour at the peak, and 4 when one cluster is enough.
Check the query history. Queued overload time means the warehouse was busy. Queued provisioning time means it was still starting.
Question 2: Your multi-cluster warehouse scales out too fast and costs too much. What is the difference between the standard and economy scaling policies?
The scaling policy only applies in auto-scale mode. It decides how eagerly Snowflake starts and stops the extra clusters.
Let's look at the two policies.
Standard is the default, and it favors no queuing:
- Starts a cluster as soon as queries queue
- Stops the least-loaded clusters after the load stays low
- Best for interactive dashboards
Economy favors saving credits. You accept some queuing for fewer cluster hours:
- Starts a cluster only when there is enough work to keep it busy
- Shuts down a cluster with little work left
- Best for batch loads, transformations and scheduled reports
So for dashboards where people wait, keep standard. For batch work, economy is usually fine:
ALTER WAREHOUSE etl_wh SET
SCALING_POLICY = ECONOMY
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 2;But before you change the policy, check the settings. A minimum above one keeps extra clusters running all day, and a high maximum lets one peak burn credits. So:
- Minimum 1: extra clusters only at peaks.
- Lower the maximum: it caps the peak.
- Compare a week: queued time and credits, before and after.
"Scales out too fast" is often a settings problem, not a policy problem. Say you check the minimum and maximum first.
Question 3: One bad query ran for six hours and burned credits. How do you stop that from happening again?
I answer in four steps: a timeout, a safety net, the root cause, and who can resize.
First, a timeout. The statement timeout parameter cancels any statement that runs longer than its limit. The default is 172,800 seconds, which is two days. So set it on each warehouse to what the workload needs:
ALTER WAREHOUSE bi_wh SET
STATEMENT_TIMEOUT_IN_SECONDS = 3600;
ALTER WAREHOUSE transform_wh SET
STATEMENT_TIMEOUT_IN_SECONDS = 14400;
ALTER WAREHOUSE bi_wh SET
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600;That last line is the queued timeout. It cancels queries that wait too long in the queue. Both can be set at several levels:
| Set it on | Rule |
|---|---|
| LEVELS | account, user, session, warehouse |
| BOTH SET | warehouse and session: lowest non-zero wins |
Second, a safety net: a resource monitor on the warehouse, so a runaway job hits a credit quota and the warehouse suspends. That is the next question.
Third, find out why it ran six hours, in the Query Profile or the query history. Query insights flag common causes, like an exploding join, a join with no join condition, or a filter that isn't selective.
Fourth, limit resizing. Keep the CREATE WAREHOUSE and MODIFY rights with a few people.
Someone sizes up a warehouse for one slow query and leaves it big for a month.
Question 4: Finance wants a hard monthly limit on credits. How do resource monitors and budgets work?
For a hard limit on warehouses, use a resource monitor. It is a credit quota per interval, monthly by default, with actions at percentage thresholds. Let's create one:
CREATE RESOURCE MONITOR finance_rm WITH
CREDIT_QUOTA = 500
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO SUSPEND
ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE transform_wh
SET RESOURCE_MONITOR = finance_rm;There are three actions. Notify sends an alert. Suspend lets running statements finish. Suspend immediate cancels them.
A few rules to know:
- Only the ACCOUNTADMIN can create a resource monitor.
- You can have one account monitor plus warehouse monitors, and whichever hits its suspend threshold first wins.
- Suspension isn't instant, so suspend at 90 percent, not 100.
Now the catch. Resource monitors only track warehouse credits. Serverless features like Snowpipe, automatic clustering and serverless tasks aren't covered. For those, use budgets.
A budget tracks warehouses and serverless features, for the account or for chosen objects and tags. It alerts by email, a cloud queue or a webhook when spend is forecast to pass the monthly limit. It doesn't block anything, but it can call a stored procedure, for example to suspend warehouses.
| Resource monitor | Budget | |
|---|---|---|
| TRACKS | warehouses only | warehouses, serverless |
| LIMIT | hard: suspends | alerts only |
| ALERTS | email, admins opt in | email, queue, webhook |
| ACTION | notify, suspend | alert, run a procedure |
Resource monitors for the hard stop, budgets for the full picture. And turn on resource monitor notifications: they are off by default, and each admin opts in.
Question 5: A few large queries make the whole warehouse slow. What is the query acceleration service, and when does it help?
The query acceleration service offloads parts of eligible queries to shared serverless compute. It cuts the effect of outlier queries without sizing up the warehouse for everyone. It needs Enterprise edition or higher.
Let's see where it helps:
- Large scans with an aggregation
- Large scans with a selective filter
- Large inserts or copies of many rows
And where it doesn't help:
- Too few partitions
- A filter that isn't selective
- Very high GROUP BY cardinality
- Non-deterministic functions like RANDOM or SEQ
You cap the cost with the scale factor, a multiple of the warehouse size, where 0 means no cap. It is billed per second as serverless compute, separate from the warehouse.
Check before you turn it on. The SYSTEM$ESTIMATE_QUERY_ACCELERATION function shows what one query would gain, and the query acceleration eligible view lists queries that could benefit:
ALTER WAREHOUSE bi_wh SET
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;
SELECT query_id, eligible_query_acceleration_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
ORDER BY eligible_query_acceleration_time DESC LIMIT 10;It won't fix queuing from too many users. That is a multi-cluster problem.
With acceleration on, bytes spilled to remote storage can show a value even when a query didn't use it.
Question 6: Leadership asks where the Snowflake credits go each month. Which views and tools do you use to find out?
Start in Snowsight, under Admin, Cost management. The consumption tab shows usage over time, next to the budgets and anomalies tabs. That is the view leadership understands.
For detail, let's query the ACCOUNT_USAGE views. Warehouse metering history gives hourly credits per warehouse, split into compute and cloud services, plus the credits attributed to queries. Compute minus query credits is idle time:
SELECT warehouse_name,
SUM(credits_used_compute) AS total,
SUM(credits_attributed_compute_queries) AS by_queries,
total - by_queries AS idle
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(month, -1, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total DESC;Query attribution history goes one level down: warehouse credits per query, so you can rank the most expensive queries and users. Serverless features have their own views, like automatic clustering history.
| View | Use |
|---|---|
| WAREHOUSE_METERING_HISTORY | hourly credits per warehouse |
| QUERY_ATTRIBUTION_HISTORY | credits per query, no idle |
| AUTOMATIC_CLUSTERING_HISTORY | serverless clustering |
| METERING_DAILY_HISTORY | billed, after the 10% rule |
Now the trap. Credits used in warehouse metering history can be higher than the bill, because it isn't adjusted for the 10 percent cloud services rule. For billed credits, use metering daily history.
I tag warehouses by team, and grant the SNOWFLAKE database roles to whoever needs these views, because by default only the account admin can query them. And remember the data lags a few hours.
Scenario 7: The Snowflake bill doubled this quarter. Walk me through how you find the cause and bring it down.
I walk through it in five steps. Let's go in order.
Step one, split the bill. Is the growth in warehouses, serverless features or storage? Cost management in Snowsight shows that quickly, and cost anomalies point to the days that jumped.
Step two, find the warehouse. Compare credits per warehouse, month over month, in the warehouse metering history. Usually one or two warehouses explain most of the growth.
Step three, idle or work? If compute credits are far above the credits attributed to queries, the warehouse is idling: fix auto-suspend, or find what keeps waking it. If it is real work, rank queries in query attribution history and group repeats by the parameterized query hash. That is how you catch one dashboard query running every minute.
Step four, what changed? A warehouse sized up and never sized down, a higher cluster maximum, a new pipeline, or automatic clustering on a busy table.
Step five, fix and guard it. Each finding has its fix:
| Finding | Fix |
|---|---|
| IDLE WAREHOUSE | auto-suspend, find what keeps waking it |
| ONE QUERY HASH, EVERY MINUTE | change the schedule or the dashboard refresh |
| OVERSIZED, TOO MANY CLUSTERS | size down, lower the maximum, economy for batch |
| RUNAWAY QUERIES | statement timeouts, resource monitors, budgets |
Put resource monitors and budgets in place, so the next jump raises an alert in days, not at the end of the quarter.
Key points to remember
- Queuing is concurrency: use a multi-cluster warehouse, not a bigger size
- Standard avoids queuing; economy saves credits and accepts some waiting
- Statement timeouts per warehouse stop runaway queries
- Resource monitors hard-stop warehouses; budgets cover serverless too
- Metering and query attribution views show where credits go
Part 3: Micro-Partitions, Pruning and Query Performance
Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.