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

VARIANT

Array of error messages for actionable errors. The array contains one error message for each query retry. If there is no query retry, the array is empty. 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