ACCESS_HISTORY View: New PARENT_QUERY_ID and ROOT_QUERY_ID columns (Pending)¶

The Account Usage ACCESS_HISTORY view behaves as follows:

Before the change

A query on the view does not include the PARENT_QUERY_ID and ROOT_QUERY_ID columns.

After the change

A query on the view includes the PARENT_QUERY_ID and ROOT_QUERY_ID columns, which are defined as follows:

Column name	Data type	Description
PARENT_QUERY_ID	TEXT	The query ID of the parent job or NULL if the job does not have a parent.
ROOT_QUERY_ID	TEXT	The query ID of the top most job in the chain or NULL if the job does not have a parent.

These columns start to record data when the 2023_08 bundle is enabled and are the last two columns in the view. The query ID corresponds to a query in the QUERY_ID column in the Account Usage ACCESS_HISTORY view. The columns record query IDs for these kinds of queries:

• A query performs a read or write operation on another object.

  The read or write operation must be an operation that the ACCESS_HISTORY view currently supports.

• A query performs a read or write operation on an object that calls a stored procedure. Nested stored procedure calls are also supported.

For example, if you have these statements run in order:

CREATE OR REPLACE PROCEDURE myproc_child()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  SELECT * FROM mydb.mysch.mytable;
  RETURN 1;
  END
$$;

CREATE OR REPLACE PROCEDURE myproc_parent()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  CALL myproc_child();
  RETURN 1;
  END
$$;

CALL myproc_parent();

Copy

A query on the ACCESS_HISTORY view records the information as follows:

USE ROLE GOVERNANCE_VIEWER;

SELECT
  query_id,
  parent_query_id,
  root_query_id,
  direct_objects_accessed
FROM
  SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;

Copy

+----------+-----------------+---------------+-----------------------------------+
| QUERY_ID | PARENT_QUERY_ID | ROOT_QUERY_ID | DIRECT_OBJECTS_ACCESSED           |
+----------+-----------------+---------------+-----------------------------------+
|  1       | NULL            | NULL          | [{"objectName": "myproc_parent"}] |
|  2       | 1               | 1             | [{"objectName": "myproc_child"}]  |
|  3       | 2               | 1             | [{"objectName": "mytable"}]       |
+----------+-----------------+---------------+-----------------------------------+

• The first row corresponds to calling the second procedure named myproc_parent as shown in the DIRECT_OBJECTS_ACCESSED column.

  The PARENT_QUERY_ID and ROOT_QUERY_ID columns return NULL because you called this stored procedure directly.

• The second row corresponds to the query that calls the first procedure named myproc_child as shown in the DIRECT_OBJECTS_ACCESSED column.

  The PARENT_QUERY_ID and ROOT_QUERY_ID columns return the same query ID because the query calling myproc_child was initiated by the query calling myproc_parent, which you called directly.

• The third row corresponds to the query that accessed the table named mytable in the myproc_child procedure as shown in the DIRECT_OBJECTS_ACCESSED column.

  The PARENT_QUERY_ID column returns the query ID of the query that accessed mytable, which corresponds to calling myproc_child. That stored procedure was initiated by the query calling myproc_parent, which is shown in the ROOT_QUERY_ID column.

Ref: 1265