Snowflake ACCESS_HISTORY: Column-Level Tracking Guide

SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY is a view that records, at the column level, which columns of which objects were accessed by every query run in your Snowflake account. It serves both as an audit trail for compliance requirements such as GDPR, CCPA, and SOX, and as a foundation for data lineage tracking. It is available on Enterprise Edition or higher.

Structure of ACCESS_HISTORY

The ACCESS_HISTORY view contains several semi-structured columns (ARRAY / OBJECT types), each recording access information at a different level.

direct_objects_accessed vs base_objects_accessed

-- 例: ビュー経由でテーブルにアクセスした場合
-- CREATE VIEW v_customers AS SELECT name, email FROM customers;
-- SELECT name FROM v_customers;

-- direct_objects_accessed の内容:
-- [{ "objectName": "V_CUSTOMERS",
--    "objectDomain": "View",
--    "columns": [{"columnName": "NAME"}] }]

-- base_objects_accessed の内容:
-- [{ "objectName": "CUSTOMERS",
--    "objectDomain": "Table",
--    "columns": [{"columnName": "NAME"}] }]

-- → ビュー V_CUSTOMERS を直接参照したが、
--   実際にアクセスされたベーステーブルは CUSTOMERS の NAME 列

Audit Query for READ Operations

-- 特定テーブルの列にアクセスしたユーザーと頻度を分析
SELECT
  ah.user_name,
  bo.value:objectName::STRING AS table_name,
  col.value:columnName::STRING AS column_name,
  COUNT(*) AS access_count,
  MIN(ah.query_start_time) AS first_access,
  MAX(ah.query_start_time) AS last_access
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
  LATERAL FLATTEN(input => ah.base_objects_accessed) bo,
  LATERAL FLATTEN(input => bo.value:columns) col
WHERE ah.query_start_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
  AND bo.value:objectName::STRING = 'CUSTOMERS'
GROUP BY 1, 2, 3
ORDER BY access_count DESC;

Audit Query for WRITE Operations

-- テーブルへのWRITE操作を追跡
SELECT
  ah.query_id,
  ah.user_name,
  ah.query_start_time,
  om.value:objectName::STRING AS modified_table,
  om.value:objectDomain::STRING AS object_type,
  col.value:columnName::STRING AS modified_column
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
  LATERAL FLATTEN(input => ah.objects_modified) om,
  LATERAL FLATTEN(input => om.value:columns) col
WHERE ah.query_start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND om.value:objectName::STRING = 'CUSTOMER_PII'
ORDER BY ah.query_start_time DESC;

Policy Auditing with policies_referenced

When a masking policy or row access policy is evaluated during query execution, the policies_referenced column records the policy name and kind. This lets you see which policies are applied and how often.

-- マスキングポリシーの適用状況を分析
SELECT
  pol.value:policyName::STRING AS policy_name,
  pol.value:policyKind::STRING AS policy_kind,
  COUNT(DISTINCT ah.query_id) AS query_count,
  COUNT(DISTINCT ah.user_name) AS user_count
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
  LATERAL FLATTEN(input => ah.policies_referenced) pol
WHERE ah.query_start_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY query_count DESC;

Building Data Lineage

By combining base_objects_accessed and objects_modifiedin ACCESS_HISTORY, you can build a data lineage graph that shows which table's data flowed into which other tables.

-- データフロー（ソーステーブル → ターゲットテーブル）の可視化
SELECT
  bo.value:objectName::STRING AS source_table,
  om.value:objectName::STRING AS target_table,
  ah.user_name,
  COUNT(*) AS flow_count,
  MAX(ah.query_start_time) AS latest_flow
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
  LATERAL FLATTEN(input => ah.base_objects_accessed) bo,
  LATERAL FLATTEN(input => ah.objects_modified) om
WHERE ah.query_start_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
  AND ARRAY_SIZE(ah.objects_modified) > 0
GROUP BY 1, 2, 3
ORDER BY flow_count DESC;

Detecting Access to Sensitive Columns

-- PII列（email, ssn, phone等）へのアクセスを検出
SELECT
  ah.user_name,
  bo.value:objectName::STRING AS table_name,
  col.value:columnName::STRING AS column_name,
  COUNT(*) AS access_count
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
  LATERAL FLATTEN(input => ah.base_objects_accessed) bo,
  LATERAL FLATTEN(input => bo.value:columns) col
WHERE ah.query_start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND col.value:columnName::STRING IN ('EMAIL', 'SSN', 'PHONE_NUMBER', 'CREDIT_CARD')
GROUP BY 1, 2, 3
ORDER BY access_count DESC;

Use Case Summary

Best Practices

• Use LATERAL FLATTEN to expand ARRAY columns into relational rows: base_objects_accessed and policies_referenced are ARRAY-typed, so expand them into rows with LATERAL FLATTEN before aggregating.
• Automate daily audit reports with Tasks: Aggregate PII column access summaries on a schedule with a Task, and raise alerts when anomalies are detected.
• Integrate with Tag-based Masking: Classify sensitive columns with tags and audit policy application via policies_referenced in ACCESS_HISTORY.
• Effective even for tracking dropped objects: ACCOUNT_USAGE retains 365 days of history, so you can still track past access to tables that have already been dropped.

Check Your Understanding

Frequently Asked Questions