Monitoring and Managing Your Snowflake Account with Account Usage and Information Schema

We will explore the tools Snowflake provides for monitoring and managing your account. These include the SNOWFLAKE database, specifically its Account Usage views and the Information Schema, which are essential for gaining insights into account activity, object usage, and system performance.

Understanding how to utilise these tools is key to ensuring efficient management and optimal performance within your Snowflake environment.

Table of Contents

The SNOWFLAKE Database, and why it is important

The SNOWFLAKE database is a shared, read-only database provided by default in all Snowflake accounts. It contains several schemas, offering various usage and metadata views that are vital for account administration. The most important of these schemas is ACCOUNT_USAGE, which provides detailed metadata and historical usage metrics.

For example, the QUERY_HISTORY view tracks all queries executed within your account, while the WAREHOUSE_METERING_HISTORY view offers insights into the credit usage of warehouses over time. These views enable administrators to monitor query activity and resource consumption effectively.

Example Query:

To check the number of queries executed in the last hour, you can run the following SQL query:

-- USE THE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;
-- GET A COUNT OF QUERIES RUN IN THE LAST HOUR
SELECT
    COUNT(*)
FROM
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    START_TIME > DATEADD('hour', -1, CURRENT_TIMESTAMP);

This query provides a quick overview of recent query activity in your Snowflake account.

Information Schema

Every Snowflake database includes its own INFORMATION_SCHEMA. This schema adheres to the SQL-92 ANSI Information Schema standard but includes Snowflake-specific enhancements. It provides real-time metadata about database objects and account-level objects, making it ideal for situations where the most up-to-date data is required.

One of the main advantages of the Information Schema is that it provides real-time data, unlike the Account Usage views, which can have a latency of a few hours. This makes it especially useful when you need immediate insight into the current state of tables, columns, or roles. Example Query:

To retrieve metadata about tables in a specific database, you can execute the following SQL query:

Change <YOUR_DATABASE> to the desired database you want to query.

-- USE THE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;

-- GET INFORMATION ON TABLES THAT HAVE BEEN CREATED, ALTERED, ETC
SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    CREATED,
    LAST_ALTERED
FROM
    < YOUR_DATABASE >.INFORMATION_SCHEMA.TABLES;

This query retrieves information about all tables within a specified database, including when each table was created and last altered.

Differences Between Account Usage and Information Schema

While both the Account Usage views and Information Schema provide valuable data, there are some key differences:

• Latency: Account Usage views have a latency of two to three hours, meaning recent changes may not appear immediately. In contrast, the Information Schema provides real-time data, making it more suitable for scenarios where up-to-the-minute information is required.

• Retention Period: Account Usage views retain data for up to one year, which is helpful for long-term analysis and trend reporting. Information Schema views, on the other hand, have shorter retention periods, typically ranging from seven days to six months, depending on the view.

• Dropped Objects: Account Usage views provide metadata for dropped objects, allowing administrators to track the history of objects that no longer exist. Information Schema, however, only reflects the current state of active objects.

Example Use Cases

Billing & Resource Usage

Account Usage views are particularly useful for monitoring credit consumption, query execution patterns, and overall account activity. These insights can help administrators manage costs more effectively and identify areas where optimisation is needed.

Example Query:

To calculate the total credits used by a specific warehouse over the past three months, use the following query:

-- USE THE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;

-- GET INFORMATION ON CREDIT CONSUMPTION IN LAST 3 MONTHS
SELECT
    WAREHOUSE_NAME,
    SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE
    START_TIME > DATEADD('month', -3, CURRENT_DATE)
GROUP BY
    WAREHOUSE_NAME;

This query helps track warehouse credit consumption, allowing you to manage resources and expenses.

Object Management

The Information Schema is ideal for day-to-day management of database objects such as tables, views, and roles. It allows administrators to quickly retrieve current database structures and permissions within an environment.

Example Query:

To list all roles and their assigned privileges in a specific database, you can run this query:

Change <YOUR_DATABASE> to the desired database you want to query.

-- USE THE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;

-- GET INFORMATION ON ROLES AND THEIR GRANTEES
SELECT
    GRANTEE AS GRANTEE_NAME,
    ROLE_NAME,
    ROLE_OWNER,
    IS_GRANTABLE
FROM
    <YOUR_DATABASE>.INFORMATION_SCHEMA.APPLICABLE_ROLES;

This query provides detailed information on the roles within a database and the privileges assigned to them.

Access Control

By default, only users with the ACCOUNTADMIN role have access to the SNOWFLAKE database and its schemas. However, these privileges can be extended to other roles, providing broader access to usage data and metadata across your organisation.