ACCESS_HISTORY View

Snowflake logo in black (no text) Enterprise Edition Feature

Access History requires Enterprise Edition (or higher). To inquire about upgrading, please contact Snowflake Support.

.

Related Topics

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 will never specify view names or view columns, including virtual columns.

 
[
  {
    "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.

 
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]

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.

Usage 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.

  • 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.

  • 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

  • This view does not log accesses of the following types:

  • 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.

  • If a Data Sharing Provider account shares objects to Data Sharing Consumer accounts through a share:

    • Data Sharing Provider accounts: the queries and logs on the shared objects executed in the Provider account will not be visible to Data Sharing Consumer accounts.

    • Data Sharing Consumer accounts: the queries on the data share executed in the Consumer account will be logged and only visible to the Consumer account, not the Data Sharing Provider account. The base tables accessed by the data share will not be logged.

  • 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.

⇧
Back to top