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
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_HISTORYretention: 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.
How it works
Side by side
| Property | INFORMATION_SCHEMA | ACCOUNT_USAGE |
|---|---|---|
| Location | Each database has its own | SNOWFLAKE.ACCOUNT_USAGE |
| Scope | Objects in that database, plus account level views | Whole account |
| Latency | None | 45 minutes to 3 hours depending on the view |
| Retention | 7 days to 6 months depending on the view or function | 1 year for historical views |
| Dropped objects | Not included | Included; a NULL name column marks a dropped object |
| Visibility | Only objects the current role can access | Visible to ACCOUNTADMIN; other roles need granted access |
| Access method | Views and table functions | Views |
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.
"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_PRIVILEGESand others. - Account level views appear in every
INFORMATION_SCHEMA:DATABASES,ENABLED_ROLES,APPLICABLE_ROLES,OBJECT_PRIVILEGESandSHARES. - Table functions return history. Examples:
QUERY_HISTORY,LOGIN_HISTORY,TASK_HISTORY,COPY_HISTORY,WAREHOUSE_METERING_HISTORYandDATABASE_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
| Function | History kept |
|---|---|
QUERY_HISTORY and QUERY_HISTORY_BY_* | 7 days |
LOGIN_HISTORY and LOGIN_HISTORY_BY_USER | 7 days |
TASK_HISTORY | 7 days |
COPY_HISTORY | 14 days |
WAREHOUSE_METERING_HISTORY | 6 months |
DATABASE_STORAGE_USAGE_HISTORY | 6 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_USAGE | Latency | Retention |
|---|---|---|
QUERY_HISTORY | 45 minutes | 1 year |
QUERY_METERING_HISTORY | 1 hour | 1 year |
AGGREGATE_QUERY_HISTORY | 3 hours | 1 year |
QUERY_ATTRIBUTION_HISTORY | 8 hours | 1 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
| Case | Source |
|---|---|
| Debug a query from the last few minutes | INFORMATION_SCHEMA.QUERY_HISTORY |
| Long running query report for the last 90 days | ACCOUNT_USAGE.QUERY_HISTORY |
| Who logged in last month | ACCOUNT_USAGE.LOGIN_HISTORY |
| Credits per warehouse for the last year | ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY |
| Find who dropped a table | ACCOUNT_USAGE views, which keep dropped objects |
| List columns of one database | INFORMATION_SCHEMA.COLUMNS |
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_HISTORYretention: 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.