Snowflake Access History

Governance teams ask simple questions with serious consequences. Who read the customer table last month? Which reports touched salary data? Which job changed this column? Access History records every query's reads and writes at the object and column level, so those questions get exact answers in minutes.

The Visitor Log Comparison

A secure office building keeps a visitor log at the front desk. The log lists who entered, when, and which rooms they visited. Access History serves as that log for data. Every query leaves a record that names the user, the time, and each table and column the query read or modified.

WHAT ACCESS HISTORY CAPTURES
============================

 USER runs a query
        |
        v
 +----------------------------------------------------+
 | ACCESS_HISTORY record                              |
 |  query id, user, role, start time                  |
 |  DIRECT_OBJECTS_ACCESSED   objects named in query  |
 |  BASE_OBJECTS_ACCESSED     underlying tables       |
 |  OBJECTS_MODIFIED          what the query wrote    |
 +----------------------------------------------------+

Direct and Base Objects

Queries often read through views. Suppose an analyst queries a view called v_customers, and the view reads the customers table. The direct object is the view, because the query named it. The base object is the customers table, because that is where the data lives. Reports about sensitive data should check base objects, since users reach tables through many views.

Where to Find the Data

The view lives at SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY. Enterprise Edition or higher includes it. Records appear with a delay of up to a few hours, which suits audits and not live monitoring.

Who Read a Sensitive Table?

SELECT ah.user_name,
       ah.query_start_time,
       ah.query_id
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
     LATERAL FLATTEN(input => ah.base_objects_accessed) obj
WHERE obj.value:objectName::STRING = 'SALES_DB.PUBLIC.CUSTOMERS'
  AND ah.query_start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY ah.query_start_time DESC;

FLATTEN unpacks the list of objects in each record, and the filter keeps only records that touched the customers table.

Which Columns Did Users Read?

SELECT ah.user_name,
       col.value:columnName::STRING AS column_name,
       COUNT(*)                      AS times_read
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
     LATERAL FLATTEN(input => ah.base_objects_accessed) obj,
     LATERAL FLATTEN(input => obj.value:columns) col
WHERE obj.value:objectName::STRING = 'SALES_DB.PUBLIC.CUSTOMERS'
GROUP BY 1, 2
ORDER BY times_read DESC;

Two FLATTEN calls reach inside two levels of nesting: the object list and the column list inside each object.

Tracking Writes

SELECT ah.user_name, ah.query_start_time, m.value:objectName::STRING AS changed_object
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
     LATERAL FLATTEN(input => ah.objects_modified) m
WHERE ah.query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP());

Common Uses

QUESTION                                 HOW ACCESS HISTORY HELPS
---------------------------------------  ---------------------------------
Who viewed salary data?                  Filter on base objects and columns
Is this table still used?                Count reads per month before retiring
Which role reads personal data?          Group by role name
Did a masked column leak into reports?   Trace column reads through views
What changed this table yesterday?       List modifying queries

Retention and Cost

Account Usage views keep one year of history. Teams that need longer retention copy results into their own audit table on a schedule. Queries against the view run on a warehouse and consume credits, so filter by time range and keep audit queries narrow.

CREATE TABLE governance.audit.access_snapshot AS
SELECT query_id, user_name, query_start_time, base_objects_accessed
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE query_start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP());

Privacy Reminder

Access History shows who looked at data and not what they saw. Grant access to the view only to governance and security roles, and treat the records as sensitive business information.

A Practical Scenario

A finance team plans to archive old tables. Before dropping anything, an engineer runs a query that counts reads per table over twelve months. Fourteen tables show zero reads, and the owners confirm they can go. The company saves storage cost and removes confusing clutter from the catalog.

Frequently Asked Questions

Does Access History record failed queries?

The view focuses on queries that read or modify objects. Use Query History to review failed statements and their error messages.

Can users hide their activity?

Users cannot edit or delete these records. Only roles with Account Usage access read them, which makes the view dependable evidence for audits.

Key Points

  • Access History logs reads and writes at object and column level.
  • Base objects reveal the underlying tables behind any view.
  • FLATTEN unpacks the nested JSON lists in each record.
  • The data appears with a delay and needs Enterprise Edition or higher.
  • Snapshot results into an audit table for retention beyond one year.
  • Limit access to governance and security roles.
Previous lesson
Back to course
Next lesson