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 (
WHEREclauses) 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
| Feature | Purpose | Key Role Needed | Benefit for Performance Tuning |
|---|---|---|---|
| Access History | Track who accessed what | MONITOR | Identify hot tables, guide MVs & caching decisions |
| Query Failures | Track failed queries | MONITOR | Identify and fix issues, improve query reliability |
These tools are essential for Snowflake performance tuning and data governance.