Compliance & Auditing in Snowflake
Governance isn't optional — it's foundational. In this lesson, we'll walk through how Snowflake supports compliance and auditing, what responsibilities fall on you versus Snowflake, and how to build real audit queries that satisfy security teams and regulators.
Snowflake's Shared Responsibility Model
Snowflake operates under a shared responsibility model:
| Responsibility Area | Snowflake | Customer |
|---|---|---|
| Physical infrastructure security | ✅ | |
| Platform patching and updates | ✅ | |
| Data encryption at rest and in transit | ✅ | |
| Identity and access management | ✅ | |
| Data classification and masking | ✅ | |
| Network policy configuration | ✅ | |
| Query and access auditing | Provides tools | Configures and monitors |
| Compliance certification maintenance | ✅ | Implements controls |
Snowflake handles infrastructure security. Your job is to configure access controls, classify sensitive data, and monitor activity.
ACCOUNT_USAGE Schema: Your Audit Backbone
The SNOWFLAKE.ACCOUNT_USAGE schema contains views with up to 365 days of historical data (45-minute to 3-hour latency).
| View | What It Tracks | Retention |
|---|---|---|
ACCESS_HISTORY | Read/write operations on tables and columns | 365 days |
LOGIN_HISTORY | Authentication attempts (success and failure) | 365 days |
QUERY_HISTORY | All queries executed | 365 days |
POLICY_REFERENCES | Where masking/row access policies are applied | Current state |
SESSIONS | Session metadata (client, IP, auth method) | 365 days |
GRANTS_TO_ROLES | Privilege grants on objects | Current state |
To query these views, a role needs IMPORTED PRIVILEGES on the SNOWFLAKE database:
USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE SYSADMIN;
INFORMATION_SCHEMA vs ACCOUNT_USAGE
| Feature | INFORMATION_SCHEMA | ACCOUNT_USAGE |
|---|---|---|
| Scope | Per-database | Entire account |
| Latency | Real-time | 45 min to 3 hours |
| History | Current state only | Up to 365 days |
| Dropped objects | Not visible | Visible |
| Access | Any role with usage | Requires IMPORTED PRIVILEGES |
Use INFORMATION_SCHEMA for real-time answers about the current database. Use ACCOUNT_USAGE for historical audit trails and cross-database visibility.
Auditing Data Access Patterns
The ACCESS_HISTORY view captures which users read from or wrote to which objects — down to the column level.
-- Find all users who accessed data in the last 30 days
SELECT
user_name,
query_id,
query_start_time,
direct_objects_accessed,
base_objects_accessed
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE query_start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY query_start_time DESC;
The direct_objects_accessed column shows what the query referenced (could be a view). The base_objects_accessed column shows the underlying tables — this is what auditors care about.
-- Column-level access tracking
SELECT
user_name,
query_start_time,
boa.value:objectName::STRING AS table_name,
col.value:columnName::STRING AS column_name
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
LATERAL FLATTEN(base_objects_accessed) boa,
LATERAL FLATTEN(boa.value:columns) col
WHERE query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND boa.value:objectName::STRING = 'MY_DB.MY_SCHEMA.CUSTOMERS'
ORDER BY query_start_time DESC;
Access history tracks both reads and writes automatically (Enterprise Edition+). There's no toggle — it's always on.
Object Dependencies and Lineage
Snowflake provides lineage through ACCESS_HISTORY and GET_OBJECT_REFERENCES:
SELECT *
FROM TABLE(GET_OBJECT_REFERENCES(
DATABASE_NAME => 'MY_DB',
SCHEMA_NAME => 'MY_SCHEMA',
OBJECT_NAME => 'CUSTOMERS'
));
For write-side lineage, use the objects_modified column in ACCESS_HISTORY:
SELECT
user_name,
query_start_time,
om.value:objectName::STRING AS modified_object,
om.value:objectDomain::STRING AS object_type
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
LATERAL FLATTEN(objects_modified) om
WHERE query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND om.value:objectName::STRING ILIKE '%SENSITIVE%'
ORDER BY query_start_time DESC;
Compliance Framework Support
| Framework | Coverage | Notes |
|---|---|---|
| SOC 2 Type II | Full platform | Annual audit, report available under NDA |
| HIPAA | BAA required | Customer must configure controls |
| PCI DSS | Level 1 Service Provider | Applies to Snowflake infrastructure |
| FedRAMP | Moderate (Gov regions) | Snowflake Government deployments |
| HITRUST | CSF Certified | Healthcare-focused |
| ISO 27001 | Full platform | Information security management |
Having the certification doesn't mean you're automatically compliant. You still need to implement access controls, encryption key management, network policies, and audit monitoring.
Building an Audit Dashboard Query
A practical query a security team might run weekly:
WITH login_summary AS (
SELECT
user_name,
COUNT(*) AS login_attempts,
SUM(CASE WHEN is_success = 'YES' THEN 1 ELSE 0 END) AS successful_logins,
SUM(CASE WHEN is_success = 'NO' THEN 1 ELSE 0 END) AS failed_logins,
COUNT(DISTINCT client_ip) AS unique_ips
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY user_name
),
access_summary AS (
SELECT
user_name,
COUNT(DISTINCT query_id) AS queries_with_data_access,
COUNT(DISTINCT boa.value:objectName::STRING) AS distinct_tables_accessed
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
LATERAL FLATTEN(base_objects_accessed) boa
WHERE query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY user_name
)
SELECT
COALESCE(l.user_name, a.user_name) AS user_name,
l.login_attempts,
l.successful_logins,
l.failed_logins,
l.unique_ips,
a.queries_with_data_access,
a.distinct_tables_accessed
FROM login_summary l
FULL OUTER JOIN access_summary a ON l.user_name = a.user_name
ORDER BY COALESCE(a.distinct_tables_accessed, 0) DESC;
Flag any user with high failed logins or unusually broad table access for investigation.
Key Takeaways
- ACCOUNT_USAGE is your primary audit data source — learn its views well.
- ACCESS_HISTORY provides column-level read/write tracking, which is what auditors want.
- Snowflake holds certifications, but you own the configuration of controls within your account.
- Build automated audit queries and schedule them as tasks or feed them into your SIEM.
- Combine login, query, and access history for a complete picture of user activity.
Next up, we'll look at how to enforce fine-grained access control with row access policies and column-level security.