QUERY_HISTORY view (Account Usage): Changes to columns and new columns¶

Attention

This behavior change is in the 2024_02 bundle.

For the current status of the bundle, refer to Bundle History.

The QUERY_HISTORY view includes the following new columns and changes to columns:

New columns in QUERY_HISTORY view¶

When this behavior change bundle is enabled, the Account Usage QUERY_HISTORY view includes the following new columns:

Column name	Data type	Description
SECONDARY_ROLE_STATS	VARCHAR	A JSON-formatted string that contains three fields regarding secondary roles that were evaluated in the query: a list of secondary roles or `ALL` depending on the session, a count of the number of secondary roles, and the internal/system-generated ID for each secondary role. The count and number of IDs have a maximum of 50.
ROWS_WRITTEN_TO_RESULT	NUMBER	Number of rows written to a result object. For CREATE TABLE AS SELECT (CTAS) and all DML operations, this result is `1`. The values in the ROWS_INSERTED, ROWS_UPDATED, and ROWS_DELETED columns reflect the number of rows actually inserted, updated, or deleted. For more information, see ROWS_PRODUCED column deprecated
ROWS_INSERTED	NUMBER	Number of rows inserted by the query.
QUERY_RETRY_TIME	NUMBER	Total execution time (in milliseconds) for query retries caused by actionable errors. For more information, see Query retry columns.
QUERY_RETRY_CAUSE	VARCHAR	Error that caused the query to retry. If there is no query retry, the field is NULL. For more information, see Query retry columns.
FAULT_HANDLING_TIME	NUMBER	Total execution time (in milliseconds) for query retries caused by errors that are not actionable. For more information, see Query retry columns.

These columns are added as the last (right-most) columns in the view.

For more information, see also Information Schema: New columns in output for QUERY_HISTORY, QUERY_HISTORY_BY_* functions.

Query retry columns¶

A query might need to be retried one or more times in order to successfully complete. There can be multiple causes that result in a query retry. Some of these causes are actionable, that is, a user can make changes to reduce or eliminate query retries for a specific query. For example, if a query is retried due to an out of memory error, modifying warehouse settings might resolve the issue.

Some query retries are caused by a fault tolerance that is not actionable. That is, there is no change a user can make to prevent the query retry. For example, a network outage might result in a query retry. In this case, there is no change to the query or to the warehouse that executes it that can prevent the query retry.

The QUERY_RETRY_TIME, QUERY_RETRY_CAUSE, and FAULT_HANDLING_TIME columns can help you optimize queries that are retried and better understand fluctuations in query performance.

ROWS_PRODUCED column deprecated¶

The ROWS_PRODUCED column will be deprecated in a future release. The value in the ROWS_PRODUCED column doesn’t always reflect the logical number of rows affected by a query. For example, the value in the ROWS_PRODUCED column might include rows that were deleted due to rewriting of micro-partitions and could be larger than the actual number of rows affected. Snowflake recommends using the ROWS_INSERTED, ROWS_UPDATED, ROWS_WRITTEN_TO RESULTS, or ROWS_DELETED columns instead.

Changes to columns in QUERY_HISTORY view¶

The following columns are included in the Account Usage QUERY_HISTORY view:

BYTES_WRITTEN_TO_RESULT
ROWS_INSERTED

The values in the these columns for specific types of queries are as follows:

Before the change:

BYTES_WRITTEN_TO_RESULT	`0` for small queries.
ROWS_INSERTED:	`0` for CREATE TABLE AS SELECT (CTAS) queries.

After the change:

BYTES_WRITTEN_TO_RESULT	Number of bytes written to a result object for small queries.
ROWS_INSERTED:	Number of rows inserted for CREATE TABLE AS SELECT (CTAS) queries.

For more information, see also Information Schema: New columns in output for QUERY_HISTORY, QUERY_HISTORY_BY_* functions.

Ref: 1431, 1524, 1540