Account Usage¶

In the SNOWFLAKE database, the ACCOUNT_USAGE and READER_ACCOUNT_USAGE schemas enable querying object metadata, as well as historical usage data, for your account and all reader accounts (if any) associated with the account.

Overview of Account Usage schemas¶

ACCOUNT_USAGE:

Views that display object metadata and usage metrics for your account.

In general, these views mirror the corresponding views and table functions in the Snowflake Snowflake Information Schema, but with the following differences:

• Records for dropped objects included in each view.
• Longer retention time for historical usage data.
• Data latency.

For more details, see Differences Between Account Usage and Information Schema (in this topic). For more details about each view, see ACCOUNT_USAGE Views (in this topic).

READER_ACCOUNT_USAGE:

Views that display object metadata and usage metrics for all the reader accounts that have been created for your account (as a Secure Data Sharing provider).

These views are a small subset of the ACCOUNT_USAGE views that apply to reader accounts. Also, each view in this schema contains an additional READER_ACCOUNT_NAME column for filtering results by reader account.

For more details about each view, see READER_ACCOUNT_USAGE Views (in this topic).

Note that these views are empty if no reader accounts have been created for your account.

Differences between Account Usage and Information Schema¶

The Account Usage views and the corresponding views (or table functions) in the Snowflake Information Schema utilize identical structures and naming conventions, but with some key differences, as described in this section:

For more details, see the following sections.

Dropped object records¶

Account usage views include records for all objects that have been dropped. Many of the views for object types contain an additional DELETED column that displays the timestamp when the object was dropped.

In addition, because objects can be dropped and recreated with the same name, to differentiate between objects records that have the same name, the account usage views include ID columns, where appropriate, that display the internal IDs generated and assigned to each record by the system.

If a column for an object name (e.g. the TABLE_NAME column) is NULL, that object has been dropped. In this case, the columns for the names and IDs of the parent objects (e.g. the DATABASE_NAME and SCHEMA_NAME columns) are also NULL.

Note that in some views, the column for the object name might still contain the name of the object, even if the object has been dropped.

Data latency¶

Due to the process of extracting the data from Snowflake’s internal metadata store, the account usage views have some natural latency:

• For most of the views, the latency is 2 hours (120 minutes).
• For the remaining views, the latency varies between 45 minutes and 3 hours.

For details, see the list of views for each schema (in this topic). Also, note that these are all maximum time lengths; the actual latency for a given view when the view is queried may be less.

In contrast, views/table functions in the Snowflake Information Schema do not have any latency.

Historical data retention¶

Certain account usage views provide historical usage metrics. The retention period for these views is 1 year (365 days).

In contrast, the corresponding views and table functions in the Snowflake Information Schema have much shorter retention periods, ranging from 7 days to 6 months, depending on the view.

ACCOUNT_USAGE views¶

The ACCOUNT_USAGE schema contains the following views:

_{[1] All latency times are approximate; in some instances, the actual latency may be lower.}

_{[2] The latency of the views for a given table may be up to 2 days if both of the following conditions are true: 1. Fewer than 32 DML statements have been added to the given table since it was last updated in LOAD_HISTORY or COPY_HISTORY. 2. Fewer than 100 rows have been added to the given table since it was last updated in LOAD_HISTORY or COPY_HISTORY.}

_{[3] Unless otherwise noted, the Account Usage view is available to all accounts.}

Account Usage table functions¶

Currently, Snowflake supports one ACCOUNT_USAGE table function:

READER_ACCOUNT_USAGE views¶

The READER_ACCOUNT_USAGE schema contains the following views:

_{[1] All latency times are approximate; in some instances, the actual latency may be lower.}

Enabling other roles to use schemas in the SNOWFLAKE database¶

By default, the SNOWFLAKE database is visible to all users; however, access to schemas in this database can be granted by a user with the ACCOUNTADMIN role using either of the following approaches:

• Grant IMPORTED PRIVILEGES on the SNOWFLAKE database.
• Grant a SNOWFLAKE database role to an account role.

For example, to grant IMPORTED PRIVILEGES on the SNOWFLAKE database to two additional roles:

Copy codeExpand code block

USE ROLE ACCOUNTADMIN;

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE SYSADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE customrole1;

Show lessSee more

Scroll to top

A user with that is granted the customrole1 role can query a view as follows:

Copy codeExpand code block

USE ROLE customrole1;

SELECT database_name, database_owner FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

Show lessSee more

Scroll to top

For additional examples, see Querying the Account Usage views.

ACCOUNT_USAGE schema SNOWFLAKE database roles¶

In addition, you can grant finer control to accounts using SNOWFLAKE Database roles. For more information on database roles, see database roles.

READER_ACCOUNT_USAGE schema SNOWFLAKE database roles¶

Querying the Account Usage views¶

This section includes considerations when querying the Account Usage views along with query examples.

Selecting columns¶

Reconciling cost views¶

There are several Account Usage views that contain data related to the cost of compute resources, storage, and data transfers. If you are trying to reconcile these views against a corresponding view in the ORGANIZATION_USAGE schema, you must first set the timezone of the session to UTC.

For example, if you are trying to reconcile ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY to the account’s data in ORGANIZATION_USAGE.WAREHOUSE_METERING_HISTORY, you must run the following command before querying the Account Usage view:

ALTER SESSION SET TIMEZONE = UTC;

Examples¶

The following examples show some typical/useful queries using the views in the ACCOUNT_USAGE schema.

Examples: User login metrics¶

Average number of seconds between failed login attempts by user (month-to-date):

Copy codeExpand code block

select user_name,
       count(*) as failed_logins,
       avg(seconds_between_login_attempts) as average_seconds_between_login_attempts
from (
      select user_name,
             timediff(seconds, event_timestamp, lead(event_timestamp)
                 over(partition by user_name order by event_timestamp)) as seconds_between_login_attempts
      from login_history
      where event_timestamp > date_trunc(month, current_date)
      and is_success = 'NO'
     )
group by 1
order by 3;

Show lessSee more

Scroll to top

Failed logins by user (month-to-date):

Copy codeExpand code block

select user_name,
       sum(iff(is_success = 'NO', 1, 0)) as failed_logins,
       count(*) as logins,
       sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from login_history
where event_timestamp > date_trunc(month, current_date)
group by 1
order by 4 desc;

Show lessSee more

Scroll to top

Failed logins by user and connecting client (month-to-date):

Copy codeExpand code block

select reported_client_type,
       user_name,
       sum(iff(is_success = 'NO', 1, 0)) as failed_logins,
       count(*) as logins,
       sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from login_history
where event_timestamp > date_trunc(month, current_date)
group by 1,2
order by 5 desc;

Show lessSee more

Scroll to top

Examples: Warehouse performance¶

Examples: Warehouse credit usage¶

Credits used by each warehouse in your account (month-to-date):

Copy codeExpand code block

select warehouse_name,
       sum(credits_used) as total_credits_used
from warehouse_metering_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Show lessSee more

Scroll to top

Credits used over time by each warehouse in your account (month-to-date):

Copy codeExpand code block

select start_time::date as usage_date,
       warehouse_name,
       sum(credits_used) as total_credits_used
from warehouse_metering_history
where start_time >= date_trunc(month, current_date)
group by 1,2
order by 2,1;

Show lessSee more

Scroll to top

Examples: Data storage usage¶

Billable terabytes stored in your account over time:

Copy codeExpand code block

select date_trunc(month, usage_date) as usage_month
  , avg(storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as billable_tb
from storage_usage
group by 1
order by 1;

Show lessSee more

Scroll to top

Examples: User query totals and execution times¶

Total jobs executed in your account (month-to-date):

Copy codeExpand code block

select count(*) as number_of_jobs
from query_history
where start_time >= date_trunc(month, current_date);

Show lessSee more

Scroll to top

Total jobs executed by each warehouse in your account (month-to-date):

Copy codeExpand code block

select warehouse_name,
       count(*) as number_of_jobs
from query_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Show lessSee more

Scroll to top

Average query execution time by user (month-to-date):

Copy codeExpand code block

select user_name,
       avg(execution_time) as average_execution_time
from query_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Show lessSee more

Scroll to top

Average query execution time by query type and warehouse size (month-to-date):

Copy codeExpand code block

select query_type,
       warehouse_size,
       avg(execution_time) as average_execution_time
from query_history
where start_time >= date_trunc(month, current_date)
group by 1,2
order by 3 desc;

Show lessSee more

Scroll to top

Examples: Obtain a query count for every login event¶

Join columns from LOGIN_HISTORY, QUERY_HISTORY, and SESSIONS to obtain a query count for each user login event.

Note

The SESSIONS view records information starting on July 20-21, 2020, therefore the query result will only contain overlapping information for each of the three views starting from this date.

Copy codeExpand code block

select l.user_name,
       l.event_timestamp as login_time,
       l.client_ip,
       l.reported_client_type,
       l.first_authentication_factor,
       l.second_authentication_factor,
       count(q.query_id)
from snowflake.account_usage.login_history l
join snowflake.account_usage.sessions s on l.event_id = s.login_event_id
join snowflake.account_usage.query_history q on q.session_id = s.session_id
group by 1,2,3,4,5,6
order by l.user_name
;

Show lessSee more

Scroll to top