Performance Optimization · Topic 8 of 9

ACCOUNT_USAGE, INFORMATION_SCHEMA and Resource Monitors

Compare ACCOUNT_USAGE and INFORMATION_SCHEMA by latency and retention, and cap credit spend with resource monitors and their trigger actions.

  • 4 min
In a nutshell
  • ACCOUNT_USAGE: whole account, latency, 365 day history, includes dropped objects. INFORMATION_SCHEMA: one database, no latency, short retention.
  • Resource monitor triggers: NOTIFY, SUSPEND, SUSPEND_IMMEDIATE, at percentages of CREDIT_QUOTA.
  • Monitors apply to an account or specific warehouses, and cover warehouse credits only.

What it is

Monitoring in Snowflake rests on two metadata sources and one spending control. ACCOUNT_USAGE views cover the whole account over a long history. INFORMATION_SCHEMA views and table functions cover one database in near real time. Resource monitors watch credit use and can notify or suspend warehouses.

An Architect decides which source answers a question, and which limit protects the budget.

Questionwhat happened?history, whole accountACCOUNT_USAGESNOWFLAKE databaseor recent, one databaseINFORMATION_SCHEMAreal timecontrol spendResource monitornotify or suspend

How it works

ACCOUNT_USAGE compared with INFORMATION_SCHEMA

AspectSNOWFLAKE.ACCOUNT_USAGE<db>.INFORMATION_SCHEMA
ScopeEntire account, all databasesOne database, plus account level table functions
LatencyDelayed, from about 45 minutes up to a few hours depending on the viewNone or very low
RetentionTypically 365 daysShort, such as 7 days for query history, up to 6 months for some views
Dropped objectsIncludedNot included
FormViewsViews and table functions
AccessShared SNOWFLAKE database; granted through database roles such as USAGE_VIEWER or GOVERNANCE_VIEWEREvery database; visibility follows privileges

Choose ACCOUNT_USAGE for trends, audits, chargeback and history over months. Choose INFORMATION_SCHEMA for what is happening now, such as the QUERY_HISTORY table function for a query that just ran, or WAREHOUSE_LOAD_HISTORY for current load.

Watch out

A query you ran two minutes ago may not appear in ACCOUNT_USAGE.QUERY_HISTORY yet. Use the INFORMATION_SCHEMA function when you need it immediately.

By default only ACCOUNTADMIN can read the SNOWFLAKE database. Other roles receive the database roles granted to them.

Useful views for performance and cost

ViewPurpose
QUERY_HISTORYPer query timing, bytes, spill, queue and cache data
WAREHOUSE_METERING_HISTORYCredits per warehouse over time
WAREHOUSE_LOAD_HISTORYRunning and queued load
METERING_HISTORY and METERING_DAILY_HISTORYCredits across services, including serverless
AUTOMATIC_CLUSTERING_HISTORY, SEARCH_OPTIMIZATION_HISTORYServerless feature credits
LOGIN_HISTORY, ACCESS_HISTORYSecurity and audit
STORAGE_USAGE, TABLE_STORAGE_METRICSStorage and Time Travel use
Credit reportmonthlyreadsWAREHOUSE_METERING_HISTORYper warehousejoinsQUERY_HISTORYwhich queriesfindsCostly workload

Resource monitors

A resource monitor tracks credits used by virtual warehouses and runs actions at thresholds.

CREATE RESOURCE MONITOR monthly_cap
  WITH CREDIT_QUOTA = 1000
       FREQUENCY = MONTHLY
       START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 90 PERCENT DO SUSPEND
           ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = monthly_cap;
-- or account wide
ALTER ACCOUNT SET RESOURCE_MONITOR = monthly_cap;
PropertyMeaning
CREDIT_QUOTACredits allowed per period
FREQUENCYDAILY, WEEKLY, MONTHLY, YEARLY or NEVER; the quota resets at the start of each period
START_TIMESTAMPWhen monitoring begins
TRIGGERSPercentage thresholds with an action
ActionEffect
NOTIFYSends a notification to account administrators who enabled notifications
SUSPENDSuspends the assigned warehouses after running queries finish
SUSPEND_IMMEDIATESuspends at once and cancels running queries

Rules to remember:

  • Only ACCOUNTADMIN can create resource monitors by default; privileges to view and modify can be granted.
  • A monitor can be set on the account or on one or more warehouses. A warehouse has at most one monitor, though the account monitor also applies.
  • Resource monitors cover credits used by virtual warehouses. Serverless features such as Snowpipe, Automatic Clustering and serverless tasks are not controlled by them; use budgets and the metering views to watch those.
  • Thresholds may go above 100 percent, so a monitor can act after the quota is passed.
  • Credits can overshoot a threshold slightly, because checks run periodically and running queries finish unless SUSPEND_IMMEDIATE is used.
Exam tip

A common safe layout is NOTIFY at 75, SUSPEND at 90 and SUSPEND_IMMEDIATE at 100 percent. Use SUSPEND to protect running work and SUSPEND_IMMEDIATE only as a hard stop.

Monitoring design

NeedTool
Hard credit ceiling per teamResource monitor on that team's warehouse
Early warningResource monitor NOTIFY trigger, or an alert on the metering view
Slow query listQUERY_HISTORY ordered by elapsed time
Serverless costMETERING_HISTORY, feature history views, budgets
ChargebackWarehouses per team, tags, and WAREHOUSE_METERING_HISTORY

Common mistakes

Expecting immediate rows in ACCOUNT_USAGE. There is latency.

Assuming resource monitors stop all spend. They apply to warehouse credits, not serverless features.

Using SUSPEND_IMMEDIATE as the only action. It cancels running queries; add an earlier SUSPEND and NOTIFY.

Putting several monitors on one warehouse. Only one monitor per warehouse is allowed.

What to remember for the exam

  • ACCOUNT_USAGE: whole account, latency, 365 day history, includes dropped objects. INFORMATION_SCHEMA: one database, no latency, short retention.
  • Resource monitor triggers: NOTIFY, SUSPEND, SUSPEND_IMMEDIATE, at percentages of CREDIT_QUOTA.
  • Monitors apply to an account or specific warehouses, and cover warehouse credits only.
  • ACCOUNTADMIN creates resource monitors by default.
  • Use WAREHOUSE_METERING_HISTORY and QUERY_HISTORY together to find the source of credits.

Check yourself

  • A query finished a minute ago and you need its spill figures. Where do you look?

    The INFORMATION_SCHEMA QUERY_HISTORY table function, because ACCOUNT_USAGE has latency.

  • Which action cancels running queries when the quota is hit?

    SUSPEND_IMMEDIATE. SUSPEND waits for running queries to finish.

  • Do resource monitors limit Snowpipe credit use?

    No. They cover virtual warehouse credits; serverless usage is watched through metering views and budgets.

Topic 8 of 9 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake ARA-C01 question bank has 687 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.