Schema:: ACCOUNT_USAGE

ACCESS_HISTORY View¶

This Account Usage view can be used to query the access history of Snowflake objects (e.g. table, view, column) within the last 365 days (1 year).

Columns¶

There are two tables in this section:

The first table defines the columns in the ACCESS_HISTORY view.
The second table defines the fields in the JSON array for the BASE_OBJECTS_ACCESSED, DIRECT_OBJECTS_ACCESSED, and OBJECTS_MODIFIED columns.

Column Name	Data Type	Description	Example
QUERY_ID	TEXT	An internal, system-generated identifier for the SQL statement. This value is also mentioned in the QUERY_HISTORY View.	`a0fda135-d678-4184-942b-c3411ae8d1ce`
QUERY_START_TIME	TIMESTAMP_LTZ	The statement start time (UTC time zone).	`2022-01-25 16:17:47.388 +0000`
USER_NAME	TEXT	The user who issued the query.	`JSMITH`
DIRECT_OBJECTS_ACCESSED	ARRAY	A JSON array of data objects such as tables, views, and columns directly named in the query explicitly or through shortcuts such as using an asterisk (i.e. `*`). Virtual columns can be returned in this field.	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ]
BASE_OBJECTS_ACCESSED	ARRAY	A JSON array of all base data objects, specifically, columns of tables to execute the query. Note: This field specifies view names or view columns, including virtual columns, if a shared view is accessed in a data sharing consumer account.	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ]
OBJECTS_MODIFIED	ARRAY	A JSON array that specifies the objects that were associated with a write operation in the query.	[ { "objectDomain": "STRING", "objectId": NUMBER, "objectName": "STRING", "columns": [ { "columnId": "NUMBER", "columnName": "STRING", "baseSources": [ { "columnName": STRING, "objectDomain": "STRING", "objectId": NUMBER, "objectName": "STRING" } ], "directSources": [ { "columnName": STRING, "objectDomain": "STRING", "objectId": NUMBER, "objectName": "STRING" } ] } ] }, ... ]

The fields in the JSON array for the DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED, and OBJECTS_MODIFIED columns are described below.

Field	Data Type	Description
columnId	NUMBER	A column ID that is unique within the account. This value is identical to the columnID in the COLUMNS view.
columnName	TEXT	The name of the accessed column.
objectId	NUMBER	An identifier for the object, which is unique within a given account and domain. This number will match: The `TABLE_ID` number for a table, view, and materialized view. If a stage was accessed, this number will match the: `NAME` identifier for a user (User stage). `TABLE_ID` number for a table (Table stage). `STAGE_ID` number for a stage (Named stage).
objectName	TEXT	The fully qualified name of the object that was accessed. If a stage was accessed, this value will be the: `username` (User stage). `table_name` (Table stage). `stage_name` (Named stage).
objectDomain	TEXT	One of the following: `Table`, `View`, `Materialized view`, `External table`, `Stream`, or `Stage`.
location	TEXT	The URL of the external location when the data access is an external location (e.g. `s3://mybucket/a.csv`). . If the query does not access a stage, this field is omitted.
stageKind	TEXT	When writing to a stage, one of the following: `Table \| User \| Internal Named \| External Named` If the query does not access a stage, this field is omitted.
baseSources	TEXT	The columns that serve as the source columns for the columns specified by `directSources`. These columns facilitate column lineage.
directSources	TEXT	The columns specifically mentioned in the data write portion of the SQL statement that serves as the source columns in the target table to which data is written. These columns facilitate column lineage.

Usage Notes¶

General notes

The view displays data starting from February 22, 2021.
For increased performance, filter queries on the QUERY_START_TIME column and choose narrower time ranges. For sample queries, see Querying the ACCESS_HISTORY View.
Secure Views. The log record contains the underlying base table (i.e. BASE_OBJECTS_ACCESSED) to generate the view. Examples include queries on other Account Usage views and queries on base tables for extract, transform, and load (i.e. ETL) operations.

Read query notes

This view supports read queries of the following type:

SELECT, including CREATE TABLE … AS SELECT (i.e. CTAS).
- Snowflake records the SELECT subquery in a CTAS operation.
CREATE TABLE … CLONE
- Snowflake records the source table in a CLONE operation.
COPY INTO … TABLE
- Snowflake logs this query only when the table is specified as the source in a FROM clause.
DML operations that read data (e.g. contains a SELECT subquery, specifies certain columns in WHERE or JOIN): INSERT … SELECT, UPDATE, DELETE, and MERGE.
User-defined functions (i.e. UDFs) and Tabular SQL UDFs (UDTFs) if tables are included in queries inside the functions. This is logged in the BASE_OBJECTS_ACCESSED field.

For more details on UDFs, see the UDF notes (in this topic).

Write operation notes

This view supports write operations of the following type:

GET <internal_stage>
PUT <internal_stage>
DELETE
TRUNCATE
INSERT
- INSERT INTO … FROM SELECT *
- INSERT INTO TABLE … VALUES ()
MERGE INTO … FROM SELECT *
UPDATE
- UPDATE TABLE … FROM SELECT * FROM …
- UPDATE TABLE … WHERE …
Data loading statements:
- COPY INTO TABLE FROM internalStage
- COPY INTO TABLE FROM externalStage
- COPY INTO TABLE FROM externalLocation
Data unloading statements:
- COPY INTO internalStage FROM TABLE
- COPY INTO externalStage FROM TABLE
- COPY INTO externalLocation FROM TABLE
CREATE:
- CREATE DATABASE … CLONE
- CREATE SCHEMA … CLONE
- CREATE TABLE … CLONE
- CREATE TABLE … AS SELECT
For write operations that call the CASE function to determine the columns to access, such as a CTAS statement with the CASE function in the SELECT query, all columns referenced in every CASE branch are recorded in the BASE_OBJECTS_ACCESSED column, the DIRECT_OBJECTS_ACCESSED column, or both columns depending on how the CTAS statement is written.

Data sharing notes

If a Data Sharing provider account shares objects to Data Sharing consumer accounts through a share:

Provider accounts: The queries and logs on the shared objects executed in the provider account are not visible to Data Sharing consumer accounts.
Consumer accounts: The queries on the data share executed in the consumer account are logged and only visible to the consumer account, not the Data Sharing provider account.

For example, if the provider shares a table and a view built from the table to the consumer account, and there is a query on the shared view, Snowflake records the shared view access in the BASE_OBJECTS_ACCESSED column. This record, which includes the columnName and objectName values, allows the consumer to know which object was accessed in their account and also protects the provider because the underlying table (via the objectId and columnId) is not revealed to the consumer.
For column lineage:

If a data sharing provider makes a view available to the data sharing consumer, the source columns for the view are not visible to the consumer because the columns originate from the data sharing provider.

If the data sharing consumer moves data from the shared view to a table, Snowflake does not record the view columns as baseSources for the newly created table.

UDFs & Stored Procedure notes

This update is postponed and will be made available in a future release.

Snowflake preserves rows that already contain references to UDFs and stored procedures in your local ACCESS_HISTORY view.

Not supported

This view does not log accesses of the following types:
- Table Functions or other Account Usage views.
- RESULT_SCAN to obtain prior results.
Additionally, this view does not support:
- Sequences, including generating new values.
- Data that enters or leaves Snowflake while using an External Function.
- Intermediate views accessed between the base table and direct object.
  
  For example, consider a query on View_A with the following object structure: View_A » View_B » View_C » Base_Table.
  
  The ACCESS_HISTORY view records the query on View_A and the Base_Table, not View_B and View_C.
- The operations to populate views, materialized views, and streams.
- Data movement resulting from replication.

Usage Notes: Column Lineage¶

These additional notes pertain to column lineage:

Supported operations

Column lineage tracks details for the following SQL operations:

CREATE TABLE … AS SELECT (i.e. CTAS)
CREATE TABLE … CLONE
INSERT … SELECT …
MERGE

UPDATE, two possible variations, for example:

Self-update:

update mydb.s1.t1 set col_1 = col_1 + 1;

Two table update:

update mydb.s1.t1 from mydb.s2.t2 set t1.col1 = t2.col1;

Query Conditions

Query profile/plan

The query plan Snowflake writes determines whether the ACCESS_HISTORY view records column lineage. If a column needs to be evaluated as part of the query plan, Snowflake records the column in the ACCESS_HISTORY view, even if the end result of the query plan is that the column is not included in the end result.

For example, consider the following INSERT statement with a WHERE clause for a particular column value:
insert into a(c1) select c2 from b where c3 > 1;
Even if the WHERE clause evaluates to FALSE, Snowflake records the c2 column as a source column for the c1 column. The c3 column is not listed as a source column for either baseSources or directSources.
Masked columns:
- The masked column is always listed in the directSources field.
- The record in the baseSources field depends on the policy definition. For example:
  - If the masking policy conditions use a CASE function, then all of the columns referenced in each of the CASE branches are recorded in the baseSources field.
  - If the masking policy conditions only specify a constant value (e.g. *****), then the baseSources field is empty.
UDFs:
- When passing a column as an argument to a UDF and writing the result to another column, the column that is passed as the argument is recorded in the directSources field. For example:
  insert into A(col1) select f(col2) from B;
  In this example, Snowflake records col2 in the directSources field because the column is an argument for the UDF named f.
- The record in the baseSources field depends on the UDF definition.

View columns

View columns are not considered to be source columns and are not listed in the baseSources field when data from a view column is copied to a table column. The view columns in this case are listed in the directSources field.

EXISTS Subquery

Columns that are referenced in the EXISTS subquery clause are not considered to be source columns.