Account usage views and monitoring in Snowflake provide comprehensive visibility into your account's activities, resource consumption, and security posture. These features are essential for administrators managing Snowflake environments and are important topics for the SnowPro Core Certification.
…Account usage views and monitoring in Snowflake provide comprehensive visibility into your account's activities, resource consumption, and security posture. These features are essential for administrators managing Snowflake environments and are important topics for the SnowPro Core Certification.
Snowflake offers the ACCOUNT_USAGE schema within the SNOWFLAKE database, which contains views that store historical data about account activity for up to one year. This differs from the INFORMATION_SCHEMA, which provides real-time data but with shorter retention periods.
Key account usage views include:
1. LOGIN_HISTORY - Tracks all login attempts, both successful and failed, helping identify potential security threats or unauthorized access attempts.
2. QUERY_HISTORY - Records all queries executed in the account, including execution time, data scanned, and user information.
3. WAREHOUSE_METERING_HISTORY - Shows credit consumption by virtual warehouses, enabling cost management and optimization.
4. STORAGE_USAGE - Monitors data storage consumption over time.
5. ACCESS_HISTORY - Tracks which users accessed specific data objects, supporting audit and compliance requirements.
6. SESSIONS - Provides details about user sessions including authentication methods used.
For security monitoring, administrators should regularly review:
- Failed login attempts to detect brute force attacks
- Unusual query patterns that might indicate data exfiltration
- Changes to user privileges and role assignments
- Network policy modifications
Access to ACCOUNT_USAGE views requires the ACCOUNTADMIN role or specific privileges granted through IMPORTED PRIVILEGES on the SNOWFLAKE database.
Best practices for monitoring include setting up alerts for suspicious activities, creating dashboards for ongoing visibility, and establishing regular audit reviews. Organizations can also integrate Snowflake monitoring data with external SIEM tools for comprehensive security management.
Understanding these monitoring capabilities helps organizations maintain security compliance, optimize costs, and troubleshoot performance issues effectively within their Snowflake environment.
Account Usage Views and Monitoring in Snowflake
Why Account Usage Monitoring is Important
Account usage monitoring is critical for organizations using Snowflake because it provides visibility into resource consumption, security events, query performance, and cost management. Understanding how your Snowflake account is being utilized helps optimize performance, control costs, ensure compliance, and maintain security standards.
What is Account Usage Monitoring?
Snowflake provides a shared database called SNOWFLAKE that contains a schema named ACCOUNT_USAGE. This schema contains views that store historical data about your account's activity, including:
- Query history and performance metrics - Login attempts and authentication events - Storage usage across databases and tables - Warehouse credit consumption - User and role management activities - Data sharing information
How Account Usage Works
The ACCOUNT_USAGE schema differs from INFORMATION_SCHEMA in several key ways:
1. Data Latency: Account Usage views have a latency of 45 minutes to 3 hours, meaning data is not real-time
2. Data Retention: Historical data is retained for up to 365 days (1 year), compared to 7-14 days for Information Schema
3. Scope: Account Usage provides account-level data, while Information Schema is database-specific
4. Access: Requires the ACCOUNTADMIN role or specific privileges granted through IMPORTED PRIVILEGES on the SNOWFLAKE database
Key Account Usage Views
- QUERY_HISTORY: Details of all queries executed - LOGIN_HISTORY: All login attempts including failures - WAREHOUSE_METERING_HISTORY: Credit usage per warehouse - STORAGE_USAGE: Daily storage consumption - ACCESS_HISTORY: Data access patterns for compliance - USERS/ROLES: User and role configurations
Exam Tips: Answering Questions on Account Usage Views and Monitoring
1. Remember the latency difference: If a question asks about real-time monitoring, ACCOUNT_USAGE is NOT the answer due to its 45-minute to 3-hour delay. INFORMATION_SCHEMA provides near real-time data.
2. Know the retention periods: ACCOUNT_USAGE retains data for 365 days. INFORMATION_SCHEMA retains data for only 7-14 days depending on the view.
3. Understand access requirements: Questions about who can access ACCOUNT_USAGE typically involve ACCOUNTADMIN or users with IMPORTED PRIVILEGES on the SNOWFLAKE database.
4. Distinguish between schemas: When questions mention account-wide historical analysis, think ACCOUNT_USAGE. For current session or database-specific queries, think INFORMATION_SCHEMA.
5. Know common view names: Be familiar with QUERY_HISTORY, LOGIN_HISTORY, WAREHOUSE_METERING_HISTORY, and STORAGE_USAGE as these appear frequently in exam scenarios.
6. Cost monitoring: Questions about tracking credit consumption or billing analysis will reference WAREHOUSE_METERING_HISTORY or METERING_HISTORY views.
7. Security auditing: For questions about tracking failed login attempts or authentication issues, LOGIN_HISTORY is the relevant view.
8. The SNOWFLAKE database is shared: Remember this is a system-provided shared database, not something you create or manage.