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
In a nutshell
  • 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 PRIVILEGES or 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:

ColumnMeaningTypical fix
QUEUED_OVERLOAD_TIMEWaited because the warehouse was busy with other queriesMulti cluster warehouse, or move workloads apart
QUEUED_PROVISIONING_TIMEWaited while the warehouse started or resizedExpected after resume; adjust auto suspend if frequent
QUEUED_REPAIR_TIMEWaited while compute was repairedRare, nothing to tune

The WAREHOUSE_LOAD_HISTORY view shows the same picture over time per warehouse, with average running and queued load.

Many usersBI dashboardssubmitWarehouseone cluster busyoverflowQueueQUEUED_OVERLOAD_TIMEfix withMulti cluster warehousemore clusters
Exam tip

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.

FeatureACCOUNT_USAGE viewsINFORMATION_SCHEMA
ScopeWhole accountOne database
Dropped objectsIncludedNot included
LatencyMinutes to hours, depending on the viewNone, real time
Retention1 yearShorter, 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_TIME and the three queued time columns.
  • BYTES_SCANNED, PARTITIONS_SCANNED, PARTITIONS_TOTAL for pruning.
  • BYTES_SPILLED_TO_LOCAL_STORAGE and BYTES_SPILLED_TO_REMOTE_STORAGE for memory pressure.
  • WAREHOUSE_NAME, USER_NAME, ROLE_NAME and QUERY_TAG for 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.

QUERY_ATTRIBUTION_HISTORYcredits per querygroup byQUERY_TAG or USER_NAMEproducesCost per teamchargeback

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.

WorkloadTypical patternWarehouse design
Data loading and ELTLarge, scheduled, heavy scansDedicated warehouse sized for the batch, suspends when done
BI and dashboardsMany short queries, many usersMulti cluster warehouse to absorb concurrency
Data science and ad hocUnpredictable, sometimes very largeSeparate warehouse so it cannot slow BI
Snowpark ML trainingMemory heavySnowpark optimized warehouse

Other best practices:

  • Size up for complex queries that spill; scale out for many users.
  • Set AUTO_SUSPEND and AUTO_RESUME so idle warehouses stop billing.
  • Use STATEMENT_TIMEOUT_IN_SECONDS (default 172800, which is two days) and STATEMENT_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.

Watch out

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 PRIVILEGES or database roles to others.
  • QUERY_HISTORY gives elapsed time, queuing, spilling, pruning and tags per query.
  • QUERY_ATTRIBUTION_HISTORY gives 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.