Bird
Raised Fist0
SnowflakeComparisonBeginner · 4 min read

Information_schema vs Account_usage in Snowflake: Key Differences and Usage

In Snowflake, INFORMATION_SCHEMA provides metadata about objects within a specific database with near real-time data, while ACCOUNT_USAGE offers account-wide metadata with up to 90 minutes latency. Use INFORMATION_SCHEMA for detailed, current database-level info and ACCOUNT_USAGE for broader, historical account-level insights.
⚖️

Quick Comparison

This table summarizes the main differences between INFORMATION_SCHEMA and ACCOUNT_USAGE in Snowflake.

FeatureINFORMATION_SCHEMAACCOUNT_USAGE
ScopeDatabase-level metadataAccount-level metadata
LatencyNear real-time (seconds to minutes)Up to 90 minutes delay
Data RetentionCurrent state onlyHistorical data (up to 1 year)
AccessAccessible per databaseAccessible via shared ACCOUNT_USAGE schema in SNOWFLAKE database
Use CaseQuick metadata queries within a databaseAudit, usage, and historical reporting
⚖️

Key Differences

INFORMATION_SCHEMA is a set of views available inside each database in Snowflake. It shows metadata about tables, views, columns, and other objects within that specific database. The data is updated almost instantly, so you get the current state of your database objects.

On the other hand, ACCOUNT_USAGE is a shared schema inside the SNOWFLAKE database that provides metadata across the entire Snowflake account. It includes information about all databases, warehouses, users, and query history. However, this data can be delayed by up to 90 minutes because it is aggregated and processed for account-wide reporting.

Another important difference is data retention. INFORMATION_SCHEMA only shows the current metadata state, while ACCOUNT_USAGE retains historical data for up to one year, making it useful for auditing and trend analysis.

⚖️

Code Comparison

Here is how you query the list of tables in a specific database using INFORMATION_SCHEMA:

sql
SELECT table_name, table_type, created
FROM my_database.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY created DESC;
Output
table_name | table_type | created ---------- | ---------- | ------------------- ORDERS | BASE TABLE | 2024-06-01 10:00:00 CUSTOMERS | BASE TABLE | 2024-05-20 09:30:00
↔️

Account_usage Equivalent

To get a similar list of tables across the entire account using ACCOUNT_USAGE:

sql
SELECT table_name, table_schema, database_name, created
FROM snowflake.account_usage.tables
WHERE table_schema = 'PUBLIC'
ORDER BY created DESC;
Output
table_name | table_schema | database_name | created ---------- | ------------ | ------------- | ------------------- ORDERS | PUBLIC | MY_DATABASE | 2024-06-01 10:00:00 CUSTOMERS | PUBLIC | MY_DATABASE | 2024-05-20 09:30:00 LOGS | PUBLIC | LOGS_DB | 2024-04-15 08:00:00
🎯

When to Use Which

Choose INFORMATION_SCHEMA when you need fast, up-to-date metadata about objects within a single database, such as during development or quick checks.

Choose ACCOUNT_USAGE when you require a broader view across your entire Snowflake account, especially for auditing, usage tracking, or historical analysis where some latency is acceptable.

✅

Key Takeaways

INFORMATION_SCHEMA offers near real-time metadata scoped to one database.
ACCOUNT_USAGE provides account-wide metadata with up to 90 minutes delay and historical data.
Use INFORMATION_SCHEMA for current state queries and ACCOUNT_USAGE for auditing and trends.
ACCOUNT_USAGE data retention supports up to one year of history.
Access INFORMATION_SCHEMA per database; ACCOUNT_USAGE is accessed via the shared SNOWFLAKE database.