Schemas:: ACCOUNT_USAGE , READER_ACCOUNT_USAGE

QUERY_HISTORY View¶

This Account Usage view can be used to query Snowflake query history by various dimensions (time range, session, user, warehouse, etc.) within the last 365 days (1 year).

The view is available in both the ACCOUNT_USAGE and READER_ACCOUNT_USAGE schemas with the following differences:

The following column is available only in the reader account view:
- READER_ACCOUNT_NAME
The following columns are not provided in the reader account view:
- CLUSTER_NUMBER
- BYTES_SCANNED
- ROWS_PRODUCED
- OUTBOUND_DATA_TRANSFER_CLOUD
- INBOUND_DATA_TRANSFER_CLOUD
- INBOUND_DATA_TRANSFER_REGION
- INBOUND_DATA_TRANSFER_BYTES
- QUERY_ACCELERATION_BYTES_SCANNED
- QUERY_ACCELERATION_PARTITIONS_SCANNED
- QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR

Columns¶

Column Name	Data Type	Description
READER_ACCOUNT_NAME	TEXT	Name of the reader account in which the SQL statement was executed.
QUERY_ID	TEXT	Internal/system-generated identifier for the SQL statement.
QUERY_TEXT	TEXT	Text of the SQL statement. Limit is 100K characters. SQL statements containing more than 100K characters will be truncated.
DATABASE_ID	NUMBER	Internal/system-generated identifier for the database that was in use.
DATABASE_NAME	TEXT	Database that was specified in the context of the query at compilation.
SCHEMA_ID	NUMBER	Internal/system-generated identifier for the schema that was in use.
SCHEMA_NAME	TEXT	Schema that was specified in the context of the query at compilation.
QUERY_TYPE	TEXT	DML, query, etc. If the query failed, then the query type may be UNKNOWN.
SESSION_ID	NUMBER	Session that executed the statement.
USER_NAME	TEXT	User who issued the query.
ROLE_NAME	TEXT	Role that was active in the session at the time of the query.
WAREHOUSE_ID	NUMBER	Internal/system-generated identifier for the warehouse that was used.
WAREHOUSE_NAME	TEXT	Warehouse that the query executed on, if any.
WAREHOUSE_SIZE	TEXT	Size of the warehouse when this statement executed.
WAREHOUSE_TYPE	TEXT	Type of the warehouse when this statement executed.
CLUSTER_NUMBER	NUMBER	The cluster (in a multi-cluster warehouse) that this statement executed on.
QUERY_TAG	TEXT	Query tag set for this statement through the QUERY_TAG session parameter.
EXECUTION_STATUS	TEXT	Execution status for the query. Valid values: `success`, `fail`, `incident`.
ERROR_CODE	NUMBER	Error code, if the query returned an error
ERROR_MESSAGE	TEXT	Error message, if the query returned an error
START_TIME	TIMESTAMP_LTZ	Statement start time (in the local time zone)
END_TIME	TIMESTAMP_LTZ	Statement end time (in the local time zone).
TOTAL_ELAPSED_TIME	NUMBER	Elapsed time (in milliseconds).
BYTES_SCANNED	NUMBER	Number of bytes scanned by this statement.
PERCENTAGE_SCANNED_FROM_CACHE	FLOAT	The percentage of data scanned from the local disk cache. The value ranges from 0.0 to 1.0. Multiply by 100 to get a true percentage.
BYTES_WRITTEN	NUMBER	Number of bytes written (e.g. when loading into a table).
BYTES_WRITTEN_TO_RESULT	NUMBER	Number of bytes written to a result object. For example, `select * from . . .` would produce a set of results in tabular format representing each field in the selection. . . In general, the results object represents whatever is produced as a result of the query, and `BYTES_WRITTEN_TO_RESULT` represents the size of the returned result.
BYTES_READ_FROM_RESULT	NUMBER	Number of bytes read from a result object.
ROWS_PRODUCED	NUMBER	Number of rows produced by this statement.
ROWS_INSERTED	NUMBER	Number of rows inserted by the query.
ROWS_UPDATED	NUMBER	Number of rows updated by the query.
ROWS_DELETED	NUMBER	Number of rows deleted by the query.
ROWS_UNLOADED	NUMBER	Number of rows unloaded during data export.
BYTES_DELETED	NUMBER	Number of bytes deleted by the query.
PARTITIONS_SCANNED	NUMBER	Number of micro-partitions scanned.
PARTITIONS_TOTAL	NUMBER	Total micro-partitions of all tables included in this query.
BYTES_SPILLED_TO_LOCAL_STORAGE	NUMBER	Volume of data spilled to local disk.
BYTES_SPILLED_TO_REMOTE_STORAGE	NUMBER	Volume of data spilled to remote disk.
BYTES_SENT_OVER_THE_NETWORK	NUMBER	Volume of data sent over the network.
COMPILATION_TIME	NUMBER	Compilation time (in milliseconds)
EXECUTION_TIME	NUMBER	Execution time (in milliseconds)
QUEUED_PROVISIONING_TIME	NUMBER	Time (in milliseconds) spent in the warehouse queue, waiting for the warehouse compute resources to provision, due to warehouse creation, resume, or resize.
QUEUED_REPAIR_TIME	NUMBER	Time (in milliseconds) spent in the warehouse queue, waiting for compute resources in the warehouse to be repaired.
QUEUED_OVERLOAD_TIME	NUMBER	Time (in milliseconds) spent in the warehouse queue, due to the warehouse being overloaded by the current query workload.
TRANSACTION_BLOCKED_TIME	NUMBER	Time (in milliseconds) spent blocked by a concurrent DML.
OUTBOUND_DATA_TRANSFER_CLOUD	TEXT	Target cloud provider for statements that unload data to another region and/or cloud.
OUTBOUND_DATA_TRANSFER_REGION	TEXT	Target region for statements that unload data to another region and/or cloud.
OUTBOUND_DATA_TRANSFER_BYTES	NUMBER	Number of bytes transferred in statements that unload data to another region and/or cloud.
INBOUND_DATA_TRANSFER_CLOUD	TEXT	Source cloud provider for statements that load data from another region and/or cloud.
INBOUND_DATA_TRANSFER_REGION	TEXT	Source region for statements that load data from another region and/or cloud.
INBOUND_DATA_TRANSFER_BYTES	NUMBER	Number of bytes transferred in statements that load data from another region and/or cloud.
LIST_EXTERNAL_FILES_TIME	NUMBER	Time (in milliseconds) spent listing external files.
CREDITS_USED_CLOUD_SERVICES	NUMBER	Number of credits used for cloud services.
RELEASE_VERSION	TEXT	Release version in the format of `major_release.minor_release.patch_release`.
EXTERNAL_FUNCTION_TOTAL_INVOCATIONS	NUMBER	The aggregate number of times that this query called remote services. For important details, see the Usage Notes.
EXTERNAL_FUNCTION_TOTAL_SENT_ROWS	NUMBER	The total number of rows that this query sent in all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS	NUMBER	The total number of rows that this query received from all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_SENT_BYTES	NUMBER	The total number of bytes that this query sent in all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES	NUMBER	The total number of bytes that this query received from all calls to all remote services.
QUERY_LOAD_PERCENT	NUMBER	The approximate percentage of active compute resources in the warehouse for this query execution.
IS_CLIENT_GENERATED_STATEMENT	BOOLEAN	Indicates whether the query was client-generated.
QUERY_ACCELERATION_BYTES_SCANNED	NUMBER	Number of bytes scanned by the query acceleration service.
QUERY_ACCELERATION_PARTITIONS_SCANNED	NUMBER	Number of partitions scanned by the query acceleration service.
QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR	NUMBER	Upper limit scale factor that a query would have benefited from.
TRANSACTION_ID	NUMBER	ID of the transaction that contains the statement or 0 if the statement is not executed within a transaction.
CHILD_QUERIES_WAIT_TIME	NUMBER	Time (in milliseconds) to complete the cached lookup when calling a memoizable function.
ROLE_TYPE	VARCHAR	Specifies whether a `ROLE` or `DATABASE_ROLE` executed the query.
QUERY_HASH	TEXT	The hash value computed based on the canonicalized SQL text. 1
QUERY_HASH_VERSION	NUMBER	The version of the logic used to compute `QUERY_HASH`. 1
QUERY_PARAMETERIZED_HASH	TEXT	The hash value computed based on the parameterized query. 1
QUERY_PARAMETERIZED_HASH_VERSION	NUMBER	The version of the logic used to compute `QUERY_PARAMETERIZED_HASH`. 1

1(1,2,3,4): This column is present only when the 2023_06 behavior change bundle is enabled. This column is part of the query hash feature.

Usage Notes¶

Latency for the view may be up to 45 minutes.
The values for the columns EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, and EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES are affected by many factors, including:
- The number of external functions in the SQL statement.
- The number of rows per batch sent to each remote service.
- The number of retries due to transient errors (e.g. because a response was not received within the expected time).