ACCESS_HISTORY view¶

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.

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

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

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

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

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

The fields in the JSON array for the direct_objects_accessed, base_objects_accessed, objects_modified, and policies_referenced columns are described below.

The fields for the object_modified_by_ddl column are described below.

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"
  }
}

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¶

Latency and historical data:

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

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

Ancestor queries:

The parent_query_id and root_query_id columns begin to record data starting on January 15-16, 2024, depending on when your Snowflake account was updated based on the 2023_08 behavior change bundle transitioning to enabled by default. This date is necessary to distinguish between the following records in the view:

• Queries that ran before the bundle was enabled by default.

• Queries that ran after the feature was enabled by default but do not have a value in the parent_query_id.

General notes:

• 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 and Organization Usage views and queries on base tables for extract, transform, and load (i.e. ETL) operations.

• Records in the 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:

  • JOIN and USING (in the JOIN reference topic)

  • Tracking Sensitive stage data movement (in the Access History query example)

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.

Hybrid tables:

Short-running queries that operate exclusively against hybrid tables will no longer generate a record in the QUERY_HISTORY view, in QUERY_HISTORY, or in the output of the QUERY_HISTORY table function. To monitor such queries, use the AGGREGATE_QUERY_HISTORY.

To monitor Access History for such queries, use the AGGREGATE_ACCESS_HISTORY. This view allows you to more easily monitor high-throughput operational workloads for Access History.

Snowflake Native App Framework notes:

Some queries related to a Snowflake Native App are redacted. For details, see Information redacted from SQL commands and views.

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, Account Usage views, and Organization Usage views.

• RESULT_SCAN to obtain prior results.

• An Access History record is generated when DDL operations are performed on sequences. It is not generated when a sequence is used in any other operations, 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.

• Failed queries, although logged in the QUERY_HISTORY view, will not be logged in the ACCESS_HISTORY view.

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

    Copy

  • Two table update:

    UPDATE mydb.s1.t1 FROM mydb.s2.t2 SET t1.col1 = t2.col1;
    

    Copy

• ALTER TABLE … RENAME TO

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

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