Skip to main content

Access History in Snowflake


What is Access History?​

Access History in Snowflake provides detailed logs about who accessed what data and how it was accessed.
It’s part of the Account Usage and Information Schema views, and includes:

  • Query-level metadata
  • User, role, and object details
  • Timestamps and access paths

Why is Access History Important?​

  • ✅ Security and Compliance

    • Tracks data access for auditing (GDPR, CCPA, HIPAA, etc.).
    • Helps investigate suspicious or unauthorized activity.
  • ✅ Data Governance

    • Know which users or roles are using which datasets.
    • Supports decisions around data ownership and stewardship.
  • ✅ Performance Monitoring

    • Identify which objects (tables, views) are queried most, guiding optimization.

Example SQL: Viewing Access History​

-- View access history from Account Usage
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE OBJECT_NAME = 'SALES' AND QUERY_START_TIME > DATEADD('day', -7, CURRENT_TIMESTAMP());

-- Another example: Who accessed a specific database?
SELECT DISTINCT
USER_NAME,
OBJECT_NAME,
DIRECT_OBJECTS_ACCESSED,
BASE_OBJECTS_ACCESSED
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE OBJECT_NAME = 'MY_DATABASE';

Use Case​

A financial services firm wants to track who accessed sensitive tables (e.g., PII or financial reports). By querying ACCESS_HISTORY, they:

  • Identify users/roles accessing PII data.
  • Audit compliance with data access policies.
  • Take corrective action (e.g., adjust privileges).

Best Practices​

  • ✅ Regularly review access patterns for sensitive tables.
  • ✅ Use filters (WHERE clauses) to avoid querying massive history data.
  • ✅ Pair with Role-based Access Control (RBAC) to limit unnecessary data exposure.
  • ✅ Use automated monitoring tools to alert on suspicious activity.

Limitations​

❌ Access History is retained for 365 days only. ❌ Can be overwhelming for very large datasets (millions of records). ❌ Requires Enterprise Edition (or higher) for full ACCOUNT_USAGE views.


Advantages​

  • ✅ Provides clear, centralized audit logs.
  • ✅ Helps meet regulatory compliance (GDPR, CCPA, etc.).
  • ✅ Supports data governance and security decisions.

Disadvantages​

❌ Storage and compute usage if querying large access history tables. ❌ Limited to 365 days—older data needs to be archived separately. ❌ Doesn’t directly show row-level data accessed—only object-level.


Required Roles​

🔑 Requires MONITOR privilege on the account for ACCOUNT_USAGE views. 🔑 Typically, SECURITYADMIN or ACCOUNTADMIN roles have these privileges.


Impact on Performance Tuning​

  • ✅ Access History helps identify hot tables—tables most frequently accessed.

  • ✅ Use this insight to decide:

    • Where to create Materialized Views or clustering keys.
    • Which tables might benefit from caching or result reuse.
    • ✅ Proactive performance tuning based on actual usage patterns.

Query Failures in Snowflake


What are Query Failures?​

Query Failures in Snowflake track unsuccessful queries—queries that did not complete successfully due to:

  • Syntax errors
  • Permissions issues
  • Resource limits (warehouse size, query timeouts)
  • Data issues (e.g., missing tables)

Why Track Query Failures?​

  • ✅ Debugging

    • Understand why queries failed and fix issues quickly.
  • ✅ Security

    • Frequent failures might indicate unauthorized access attempts.
  • ✅ Performance Tuning

    • Identify queries that fail due to insufficient warehouse sizing or timeouts—adjust warehouse accordingly.

Example SQL: View Query Failures​

-- Get failed queries from Query History
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE ERROR_CODE IS NOT NULL
AND START_TIME > DATEADD('day', -7, CURRENT_TIMESTAMP());

-- Focus on a specific user
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE ERROR_CODE IS NOT NULL
AND USER_NAME = 'BI_ANALYST';

Use Case​

A team’s ETL job keeps failing overnight. By querying QUERY_HISTORY for errors:

  • They find a permission issue with a role.
  • Adjust privileges → ETL job runs smoothly again!

Best Practices​

  • ✅ Regularly monitor for repeated failures—especially on production workloads.
  • ✅ Investigate and resolve root causes promptly.
  • ✅ Use alerting tools (e.g., Snowflake’s native alerts or 3rd-party monitoring) for quick response.

Limitations​

❌ Query failures don’t always pinpoint the exact line of code—only high-level error messages. ❌ Retention is 365 days in ACCOUNT_USAGE.


Advantages​

  • ✅ Quickly identify problematic queries.
  • ✅ Save developer time by focusing on root causes.
  • ✅ Improve reliability of production jobs.

Disadvantages​

❌ High volume of failures can be noisy without proper filters. ❌ No row-level error context—only at the query level.


Required Roles​

🔑 Requires MONITOR privilege for ACCOUNT_USAGE views. 🔑 SECURITYADMIN or ACCOUNTADMIN roles typically have this access.


Impact on Performance Tuning​

  • ✅ Identifying recurring query failures can reveal:

    • Warehouse sizing issues (e.g., warehouse too small for workload).
    • Query inefficiencies (timeouts, data skew).
    • Improper SQL syntax or missing objects.
  • ✅ By fixing these failures, you boost performance stability and avoid wasted compute cycles.


🚀 Summary​

FeaturePurposeKey Role NeededBenefit for Performance Tuning
Access HistoryTrack who accessed whatMONITORIdentify hot tables, guide MVs & caching decisions
Query FailuresTrack failed queriesMONITORIdentify and fix issues, improve query reliability

These tools are essential for Snowflake performance tuning and data governance.