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 three tables in this section:

  • The first table provides an example of each column value.

  • The second table defines the columns in the ACCESS_HISTORY view.

  • The third table defines the fields in the JSON array for the BASE_OBJECTS_ACCESSED, DIRECT_OBJECTS_ACCESSED, and OBJECTS_MODIFIED columns.

Column name

Example

QUERY_ID

a0fda135-d678-4184-942b-c3411ae8d1ce

QUERY_START_TIME

2022-01-25 16:17:47.388 +0000

USER_NAME

JSMITH

DIRECT_OBJECTS_ACCESSED

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "GOVERNANCE.FUNCTIONS.RETURN_SUM",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  },
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "GOVERNANCE.TABLES.T1"
  }
]
Copy

BASE_OBJECTS_ACCESSED

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "GOVERNANCE.FUNCTIONS.RETURN_SUM",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  },
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "GOVERNANCE.TABLES.T1"
  }
]
Copy

OBJECTS_MODIFIED

[
  {
    "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"
          }
        ]
      }
    ]
  },
  ...
]
Copy

OBJECT_MODIFIED_BY_DDL

{
  "objectDomain": STRING,
  "objectName": STRING,
  "objectId": NUMBER,
  "operationType": STRING,
  "properties": ARRAY
}
Copy

POLICIES_REFERENCED

[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "SSN",
        "policies": [
          {
              "policyName": "governance.policies.ssn_mask",
              "policyId": 68811,
              "policyKind": "MASKING_POLICY"
          }
        ]
      }
    ],
    "objectDomain": "VIEW",
    "objectId": 66564,
    "objectName": "GOVERNANCE.VIEWS.V1",
    "policies": [
      {
        "policyName": "governance.policies.rap1",
        "policyId": 68813,
        "policyKind": "ROW_ACCESS_POLICY"
      }
    ]
  }
]
Copy

Column Name

Data Type

Description

QUERY_ID

TEXT

An internal, system-generated identifier for the SQL statement. This value is also mentioned in the QUERY_HISTORY View.

QUERY_START_TIME

TIMESTAMP_LTZ

The statement start time (UTC time zone).

USER_NAME

TEXT

The user who issued the query.

DIRECT_OBJECTS_ACCESSED

ARRAY

A JSON array of data objects such as user-defined functions (i.e. UDFs and UDTFs), stored procedures, 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.

For additional notes about UDFs, see the UDF notes (in this topic).

BASE_OBJECTS_ACCESSED

ARRAY

A JSON array of all base data objects to execute a query, including columns, external functions, UDFs, and stored procedures.

In this example, the fields in the first array specify a UDF. These same fields in the first array also specify a stored procedure, when applicable.

Note the following:

  • This field specifies view names or view columns, including virtual columns, if a shared view is accessed in a data sharing consumer account.

  • For additional notes about UDFs, see the UDF notes (in this topic).

OBJECTS_MODIFIED

ARRAY

A JSON array that specifies the objects that were associated with a write operation in the query.

The UDF and stored procedure array is the same as what is shown earlier and appears in the arrays for baseSources and directSources depending on how the access took place. For brevity, this example omits the UDF and stored procedure array.

For additional notes about UDFs, see the UDF notes (in this topic).

OBJECT_MODIFIED_BY_DDL

OBJECT

Specifies the DDL operation on a database, schema, table, view, and column. These operations also include statements that specify a row access policy on a table or view, a masking policy on a column, and tag updates (e.g. set a tag, change a tag value) on the object or column.

POLICIES_REFERENCED

ARRAY

Specifies information about the enforced masking policy set on the column and the enforced row access policy set on the table, including policies set on intermediate objects or columns.

The fields in the JSON array for the DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED, OBJECTS_MODIFIED, and POLICIES_REFERENCED 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. For policies, specifies the column on which the masking policy is set.

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 masking policy is set on a column or a row access policy is set on a table or view, the value refers to the fully qualified name of the table or view on which the row access policy is set or the table or view that has a masking policy set on one of its columns.

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: EXTERNAL TABLE, FUNCTION, MATERIALIZED VIEW, PROCEDURE, STAGE, STREAM, or VIEW.

Note that FUNCTION specifies UDFs, UDTFs, and external functions.

For policies, specifies the domain of the object on which the row access policy is set.

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.

policyName

TEXT

The fully-qualified name of the policy.

policyId

NUMBER

An identifier for the policy, which is unique within a given account and domain. This value matches the identifier for a masking policy in the MASKING_POLICIES View or the identifier for a row access policy in the ROW_ACCESS_POLICIES View

policyKind

TEXT

Either: MASKING_POLICY or ROW_ACCESS_POLICY

argumentSignature

TEXT

The name and data type for each argument in the UDF or stored procedure.

dataType

The data type of the return value for a UDF or stored procedure.

This value helps to differentiate two or more UDFs that have the same name but different return types.

The fields for the OBJECT_MODIFIED_BY_DDL column are described below.

fieldName

Data type

Description

objectDomain

TEXT

The domain of the object defined or modified by the DDL operation, which includes all objects that can be tagged and MASKING POLICY | ROW ACCESS POLICY | TAG.

objectId

NUMBER

The identifier for the object, which is unique within a given account and domain, defined or modified by the DDL operation.

objectName

TEXT

The fully qualified name of the object defined or modified by the DDL operation.

operationType

TEXT

The SQL keyword that specifies the operation on the table, view, or column: ALTER | CREATE | DROP | REPLACE | UNDROP

properties

ARRAY

A JSON array that specifies the object or column properties when you create, modify, drop, or undrop the object or column. There are two types of properties: atomic and compound.

For the properties field:

  • Atomic: one value per property (e.g. a comment has a single string value, the enabled property is a boolean and has one value).

  • Compound: the property is multi-valued (e.g. allowed_values for a tag, masking policy).

Compound properties are recorded in a JSON array. For example, if a table contains a single column named EMAIL, the column is recorded as follows:

columns: {
  "email": {
    objectId: {
      "value": 1
    },
    "subOperationType": "ADD"
  }
}
Copy

The subOperationType value can be one of the following:

  • ADD specifies adding a compound property (e.g. add a column, set allowed values).

  • DROP specifies removing a compound property.

  • ALTER specifies modifying a compound property.

The objectId specifies the identifier for the column or object, except for allowed tag values which do not have an identifier.

Usage Notes

General notes
  • The view displays data starting from February 22, 2021.

  • Latency for the view may be up to 180 minutes (3 hours).

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

  • Records in the Account Usage QUERY_HISTORY view do not always get recorded in the ACCESS_HISTORY view. The structure of the SQL statement determines whether Snowflake records an entry in the ACCESS_HISTORY view.

  • Specifying the USING clause while querying this view might cause non-referenced columns to be recorded in DIRECT_OBJECTS_ACCESSED field. As a workaround, replace the USING clause with a JOIN ... ON ... clause. For details, refer to:

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.

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

  • For shared UDFs and UDTFs:

    • In the consumer account, the local ACCESS_HISTORY view records the UDF/UDTF that was shared by the provider when the shared UDF/UDTF is invoked by the consumer.

    • In the provider account, the local ACCESS_HISTORY view records provider usage of a shared UDF/UDTF. Users in the consumer account cannot view how the provider account uses the shared UDF/UDTF.

  • For tracking policy references:

    The POLICIES_REFERENCED column contains policies that are local to the account that queries the data.

    If a provider shares a policy-protected table and a consumer accesses this table, the consumer cannot see the policy the provider set on the table or its columns.

    If a consumer creates a view (v1) from the shared object, sets a policy to the view (v1) or its columns, and a user in the consumer account accesses the protected view (v1) or another view (v2) created from the protected view (v1), the ACCESS_HISTORY view in the consumer account contains the policy that protects the view (v1) and its columns. The provider cannot see the record that corresponds to v1.

Tag-based masking notes

If a user accesses a table or view protected by a tag-based masking policy, the POLICIES_REFERENCED column contains the masking policy applied through the tag when Snowflake enforces the masking policy on the protected column.

The ACCESS_HISTORY view does not record any tag information.

UDFs & Stored Procedure notes

These notes apply to external functions, UDFs and UDTFs for all languages, including when these functions have the SECURE property, and stored procedures with owner’s rights and caller’s rights:

Column details:

  • The DIRECT_OBJECTS_ACCESSED column records explicit mention of these functions and procedures in a query.

    Snowflake does not record nested UDFs (i.e. a UDF mentioned in the definition of another UDF) in this column.

  • The BASE_OBJECTS_ACCESSED column records external functions, shared functions, non-SQL UDFs, and stored procedures that are called in a query.

  • The OBJECTS_MODIFIED column records:

    • The UDF/UDTF when the result of calling the function copies the result to another column.

    • The UDF, UDTF, and an external function can be recorded in the arrays for baseSources and directSources depending on how the query is written.

Not supported

This view does not log accesses of the following types:

  • Snowflake-provided table functions or other Account Usage views.

  • RESULT_SCAN to obtain prior results.

  • Sequences, including generating new values.

  • 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 update 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:

Query Conditions
  • Query profile/plan

    The query plan Snowflake writes determines whether the ACCESS_HISTORY view contains column lineage. If a column needs to be evaluated as part of the query plan, Snowflake contains 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;
    
    Copy

    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;
      
      Copy

      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.

Usage Notes: OBJECT_MODIFIED_BY_DDL Column

  • IF [ NOT ] EXISTS clauses.

    The OBJECT_MODIFIED_BY_DDL column only records CREATE or REPLACE when creating or modifying an object.

  • Snowflake supports the following object domains.

    • Table and external table.

    • View and materialized view

    • Schema

    • Database.

The column records these changes based on the following SQL operations. The DROP and UNDROP operations apply to tables and views, not columns.

CREATE OR REPLACE

ALTER ... { SET | UNSET }

ALTER ... ADD ROW ACCESS POLICY

ALTER ... DROP ROW ACCESS POLICY

ALTER ... DROP ALL ROW ACCESS POLICIES

DROP | UNDROP
Copy

The following table summarizes the relationship between DDL operations, supported domains, and the properties Snowflake records.

Operation

Domain

Properties

Notes

CREATE [ OR REPLACE ]

TABLE | EXTERNAL TABLE | VIEW | MATERIALIZED VIEW

Column name, column identifier.

CREATE DATABASE and CREATE SCHEMA operations do not have properties recorded.

CREATE

TABLE … { AS SELECT | USING TEMPLATE | LIKE | CLONE }

Column name, column identifier.

Snowflake records the creation source for LIKE and CLONE operations.

Snowflake does not record the creation source when the source object is from a share or with USING TEMPLATE.

ALTER … RENAME TO

ALTER TABLE … RENAME COLUMN

TABLE | VIEW | MATERIALIZED VIEW | DATABASE | SCHEMA

The new name of the object or column.

ALTER … SWAP WITH

TABLE | SCHEMA | DATABASE

objectName, objectId, objectDomain

There are two records in the view, one for each swap target. Each record contains the same query identifier value.

ALTER … { ADD | DROP } COLUMN

TABLE

Column name, column identifier, and the ADD or DROP subOperationType.

DROP

TABLE | VIEW | MATERIALIZED VIEW | DATABASE | SCHEMA

Snowflake does not record properties for these operations.

UNDROP

TABLE | SCHEMA | DATABASE

Snowflake does not record properties for these operations.

Usage Notes

IF [ NOT ] EXISTS clauses. The OBJECT_MODIFIED_BY_DDL column only records CREATE or REPLACE when creating or modifying an object.