Information_schema vs Account_usage in Snowflake: Key Differences and Usage
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.
| Feature | INFORMATION_SCHEMA | ACCOUNT_USAGE |
|---|---|---|
| Scope | Database-level metadata | Account-level metadata |
| Latency | Near real-time (seconds to minutes) | Up to 90 minutes delay |
| Data Retention | Current state only | Historical data (up to 1 year) |
| Access | Accessible per database | Accessible via shared ACCOUNT_USAGE schema in SNOWFLAKE database |
| Use Case | Quick metadata queries within a database | Audit, 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:
SELECT table_name, table_type, created FROM my_database.information_schema.tables WHERE table_schema = 'PUBLIC' ORDER BY created DESC;
Account_usage Equivalent
To get a similar list of tables across the entire account using ACCOUNT_USAGE:
SELECT table_name, table_schema, database_name, created FROM snowflake.account_usage.tables WHERE table_schema = 'PUBLIC' ORDER BY created DESC;
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.INFORMATION_SCHEMA for current state queries and ACCOUNT_USAGE for auditing and trends.ACCOUNT_USAGE data retention supports up to one year of history.INFORMATION_SCHEMA per database; ACCOUNT_USAGE is accessed via the shared SNOWFLAKE database.