Performance Monitoring and Tuning · Topic 13 of 16

ACCOUNT_USAGE Against INFORMATION_SCHEMA

Choose between ACCOUNT_USAGE and INFORMATION_SCHEMA by latency, retention, dropped objects and visibility, with history functions and view latencies.

  • 4 min
In a nutshell
  • INFORMATION_SCHEMA: no latency, short history (7 days to 6 months), current objects only, role dependent.
  • ACCOUNT_USAGE: latency 45 minutes to 3 hours (some views more), 1 year retention, includes dropped objects.
  • QUERY_HISTORY retention: 7 days in the table function, 1 year in the account view.

What it is

Snowflake offers two built in sources for metadata and history. INFORMATION_SCHEMA is a read only schema that every database contains. ACCOUNT_USAGE is a set of views in the shared SNOWFLAKE database that covers the whole account.

They overlap in content and differ in four properties: latency, retention, dropped objects and scope. Exam scenarios give you a need ("investigate a query that ran two minutes ago", "audit logins from eight months ago") and expect you to choose the source that fits.

Needmetadata or historyfresh, short historyINFORMATION_SCHEMAno latencylong history, dropped objectsACCOUNT_USAGE1 year, with latency

How it works

Side by side

PropertyINFORMATION_SCHEMAACCOUNT_USAGE
LocationEach database has its ownSNOWFLAKE.ACCOUNT_USAGE
ScopeObjects in that database, plus account level viewsWhole account
LatencyNone45 minutes to 3 hours depending on the view
Retention7 days to 6 months depending on the view or function1 year for historical views
Dropped objectsNot includedIncluded; a NULL name column marks a dropped object
VisibilityOnly objects the current role can accessVisible to ACCOUNTADMIN; other roles need granted access
Access methodViews and table functionsViews

Latencies are maximums. Most ACCOUNT_USAGE views have up to 2 hours (120 minutes) of latency, and the rest range from 45 minutes to 3 hours.

Exam tip

"Needs to see data right now" points to INFORMATION_SCHEMA. "Needs data older than a few days" or "needs dropped objects" points to ACCOUNT_USAGE.

What INFORMATION_SCHEMA contains

Every database has a read only schema named INFORMATION_SCHEMA.

  • Database level views describe objects in that database: TABLES, COLUMNS, VIEWS, SCHEMATA, TABLE_PRIVILEGES and others.
  • Account level views appear in every INFORMATION_SCHEMA: DATABASES, ENABLED_ROLES, APPLICABLE_ROLES, OBJECT_PRIVILEGES and SHARES.
  • Table functions return history. Examples: QUERY_HISTORY, LOGIN_HISTORY, TASK_HISTORY, COPY_HISTORY, WAREHOUSE_METERING_HISTORY and DATABASE_STORAGE_USAGE_HISTORY.

Table functions are called with TABLE(...):

SELECT query_id, total_elapsed_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  END_TIME_RANGE_START => DATEADD('hours', -1, CURRENT_TIMESTAMP())))
ORDER BY total_elapsed_time DESC;

What a role sees depends on its privileges. A role without access to an object does not see it in the output.

Retention of the table functions

FunctionHistory kept
QUERY_HISTORY and QUERY_HISTORY_BY_*7 days
LOGIN_HISTORY and LOGIN_HISTORY_BY_USER7 days
TASK_HISTORY7 days
COPY_HISTORY14 days
WAREHOUSE_METERING_HISTORY6 months
DATABASE_STORAGE_USAGE_HISTORY6 months

These figures are the reason a question about "last quarter" cannot be answered from INFORMATION_SCHEMA.

Queries against INFORMATION_SCHEMA with filters that are not selective enough can fail with the error "Information schema query returned too much data". Narrow the time range or add filters to avoid it.

Latency for historical views

Latency is stated per view, so memorize a few anchors.

View in ACCOUNT_USAGELatencyRetention
QUERY_HISTORY45 minutes1 year
QUERY_METERING_HISTORY1 hour1 year
AGGREGATE_QUERY_HISTORY3 hours1 year
QUERY_ATTRIBUTION_HISTORY8 hours1 year

The general range is 45 minutes to 3 hours, but a few views such as QUERY_ATTRIBUTION_HISTORY are slower. The READER_ACCOUNT_USAGE schema in a reader account has its own QUERY_HISTORY with 45 minutes latency.

Because the views are fed with a delay, a statement you just ran will not appear in ACCOUNT_USAGE.QUERY_HISTORY at once, but it does appear in INFORMATION_SCHEMA.QUERY_HISTORY immediately.

Choosing for common cases

CaseSource
Debug a query from the last few minutesINFORMATION_SCHEMA.QUERY_HISTORY
Long running query report for the last 90 daysACCOUNT_USAGE.QUERY_HISTORY
Who logged in last monthACCOUNT_USAGE.LOGIN_HISTORY
Credits per warehouse for the last yearACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
Find who dropped a tableACCOUNT_USAGE views, which keep dropped objects
List columns of one databaseINFORMATION_SCHEMA.COLUMNS
Queryjust ranneeds immediate resultINFORMATION_SCHEMAQUERY_HISTORY functionolder than 7 daysACCOUNT_USAGEQUERY_HISTORY view

Common mistakes

Using ACCOUNT_USAGE for a live investigation. It can lag by up to 3 hours or more.

Using INFORMATION_SCHEMA for a yearly report. Retention there ends at 7 days to 6 months.

Expecting INFORMATION_SCHEMA to show every object. It shows only what the current role can access.

Looking for dropped objects in INFORMATION_SCHEMA. They only appear in ACCOUNT_USAGE.

What to remember for the exam

  • INFORMATION_SCHEMA: no latency, short history (7 days to 6 months), current objects only, role dependent.
  • ACCOUNT_USAGE: latency 45 minutes to 3 hours (some views more), 1 year retention, includes dropped objects.
  • QUERY_HISTORY retention: 7 days in the table function, 1 year in the account view.
  • History functions are table functions called with TABLE(...).
  • Reader accounts use READER_ACCOUNT_USAGE.

Check yourself

  • Which source shows a query that finished one minute ago?

    INFORMATION_SCHEMA.QUERY_HISTORY, because it has no latency.

  • Which source includes dropped objects and one year of history?

    ACCOUNT_USAGE.

  • Why does a role not see some tables in INFORMATION_SCHEMA?

    The output depends on the privileges of the current role.

Topic 13 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 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.