Performance Optimization, Querying, and Transformation · Topic 2 of 6
Query History, Attribution and Workload Management
Find queuing, slow and costly queries in SNOWFLAKE.ACCOUNT_USAGE views, attribute credits per query, and group similar workloads on the right warehouses.
- 5 min
- Queuing shows as
QUEUED_OVERLOAD_TIME; fix with a multi cluster warehouse or by separating workloads. - ACCOUNT_USAGE covers the whole account, includes dropped objects, keeps 1 year and has latency. INFORMATION_SCHEMA is real time with shorter retention.
- Only ACCOUNTADMIN sees the SNOWFLAKE database by default; grant
IMPORTED PRIVILEGESor database roles to others.
What it is
The Query Profile explains one query. To see patterns across thousands of queries, you use the SNOWFLAKE.ACCOUNT_USAGE schema, a set of views in the shared SNOWFLAKE database that records history and cost for the whole account.
Workload management is what you do with those findings: decide which queries share a warehouse, how big each warehouse is, and how it handles many users at once. Queuing is the symptom that most often starts that conversation.
How it works
Queuing
A warehouse runs a limited number of queries at the same time. When it is full, new queries wait in a queue. The Query Profile and Query Insights report a long queue as Query was in the queue for the warehouse for too long.
The QUERY_HISTORY view splits waiting time into separate columns:
| Column | Meaning | Typical fix |
|---|---|---|
QUEUED_OVERLOAD_TIME | Waited because the warehouse was busy with other queries | Multi cluster warehouse, or move workloads apart |
QUEUED_PROVISIONING_TIME | Waited while the warehouse started or resized | Expected after resume; adjust auto suspend if frequent |
QUEUED_REPAIR_TIME | Waited while compute was repaired | Rare, nothing to tune |
The WAREHOUSE_LOAD_HISTORY view shows the same picture over time per warehouse, with average running and queued load.
Queuing is a concurrency problem, so scale out with a multi cluster warehouse (Enterprise Edition or higher). Spilling is a memory problem, so scale up to a larger size.
The ACCOUNT_USAGE views
By default only the ACCOUNTADMIN role can query the SNOWFLAKE database. Other roles get access through a grant of IMPORTED PRIVILEGES on the database or through SNOWFLAKE database roles such as USAGE_VIEWER and GOVERNANCE_VIEWER.
| Feature | ACCOUNT_USAGE views | INFORMATION_SCHEMA |
|---|---|---|
| Scope | Whole account | One database |
| Dropped objects | Included | Not included |
| Latency | Minutes to hours, depending on the view | None, real time |
| Retention | 1 year | Shorter, for example 7 days for query history |
Use INFORMATION_SCHEMA for "what just happened" and ACCOUNT_USAGE for trends, audits and anything older than a few days.
Query history
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY has one row per query for the last 365 days, with a latency of up to 45 minutes. Useful columns include:
TOTAL_ELAPSED_TIME,COMPILATION_TIME,EXECUTION_TIMEand the three queued time columns.BYTES_SCANNED,PARTITIONS_SCANNED,PARTITIONS_TOTALfor pruning.BYTES_SPILLED_TO_LOCAL_STORAGEandBYTES_SPILLED_TO_REMOTE_STORAGEfor memory pressure.WAREHOUSE_NAME,USER_NAME,ROLE_NAMEandQUERY_TAGfor who ran it and why.QUERY_PARAMETERIZED_HASH, which groups queries that differ only in literal values.
For the last few minutes, use the INFORMATION_SCHEMA.QUERY_HISTORY table function or Snowsight Query History instead.
Query attribution
SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY shows the warehouse compute credits attributed to each query. Key columns are CREDITS_ATTRIBUTED_COMPUTE, CREDITS_USED_QUERY_ACCELERATION, QUERY_TAG, USER_NAME, QUERY_PARAMETERIZED_HASH and ROOT_QUERY_ID for nested calls such as stored procedures.
What it does not include matters for the exam:
- Warehouse idle time is not attributed to any query.
- Very short queries, about 100 ms or less, are excluded.
- Storage, data transfer, cloud services and serverless feature costs are not in this view.
- Data can lag by up to eight hours.
To charge costs to a project, set QUERY_TAG at session level (ALTER SESSION SET QUERY_TAG = 'finance_etl') and group attribution rows by tag.
Workload management best practices
The main idea is grouping similar workloads: queries with similar size, timing and priority share a warehouse, and different workloads get their own.
| Workload | Typical pattern | Warehouse design |
|---|---|---|
| Data loading and ELT | Large, scheduled, heavy scans | Dedicated warehouse sized for the batch, suspends when done |
| BI and dashboards | Many short queries, many users | Multi cluster warehouse to absorb concurrency |
| Data science and ad hoc | Unpredictable, sometimes very large | Separate warehouse so it cannot slow BI |
| Snowpark ML training | Memory heavy | Snowpark optimized warehouse |
Other best practices:
- Size up for complex queries that spill; scale out for many users.
- Set
AUTO_SUSPENDandAUTO_RESUMEso idle warehouses stop billing. - Use
STATEMENT_TIMEOUT_IN_SECONDS(default 172800, which is two days) andSTATEMENT_QUEUED_TIMEOUT_IN_SECONDS(default 0, no limit) to stop runaway or stuck queries. - Use resource monitors to cap credit use, and query tags for attribution.
- Choose the multi cluster scaling policy: Standard (the default) starts clusters quickly to avoid queuing, Economy favors keeping clusters fully loaded to save credits.
Common mistakes
Putting every workload on one large warehouse. ELT jobs then queue behind dashboards, or dashboards slow down during loads. Separate warehouses isolate them and make cost per workload visible.
Expecting ACCOUNT_USAGE to show a query from one minute ago. The views have latency. Use Snowsight Query History or INFORMATION_SCHEMA for very recent queries.
Assuming attribution totals equal the warehouse bill. Idle time and short queries are not attributed, so the sum of attributed credits is lower than metered warehouse credits.
Increasing the warehouse size does not remove overload queuing caused by many concurrent users. Only more clusters (or moving workloads to other warehouses) adds parallel capacity for more queries.
What to remember for the exam
- Queuing shows as
QUEUED_OVERLOAD_TIME; fix with a multi cluster warehouse or by separating workloads. - ACCOUNT_USAGE covers the whole account, includes dropped objects, keeps 1 year and has latency. INFORMATION_SCHEMA is real time with shorter retention.
- Only ACCOUNTADMIN sees the SNOWFLAKE database by default; grant
IMPORTED PRIVILEGESor database roles to others. QUERY_HISTORYgives elapsed time, queuing, spilling, pruning and tags per query.QUERY_ATTRIBUTION_HISTORYgives compute credits per query, excluding idle time.- Group similar workloads on their own warehouses; scale up for complexity, out for concurrency.
Check yourself
Dashboards slow down every morning and QUERY_HISTORY shows high QUEUED_OVERLOAD_TIME. What should you change?
Make the BI warehouse a multi cluster warehouse, or move other workloads off it, because the queries are waiting for concurrency.
Which view shows compute credits per query for a chargeback report?
SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY, grouped by QUERY_TAG or USER_NAME.
Why would you query INFORMATION_SCHEMA instead of ACCOUNT_USAGE for query history?
INFORMATION_SCHEMA has no latency, so it shows queries that just ran, while ACCOUNT_USAGE can lag by up to 45 minutes.
Topic 2 of 6 in Performance Optimization, Querying, and Transformation
Test yourself on Performance Optimization, Querying, and Transformation
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.