Skip to main content

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 AreaSnowflakeCustomer
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 auditingProvides toolsConfigures 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).

ViewWhat It TracksRetention
ACCESS_HISTORYRead/write operations on tables and columns365 days
LOGIN_HISTORYAuthentication attempts (success and failure)365 days
QUERY_HISTORYAll queries executed365 days
POLICY_REFERENCESWhere masking/row access policies are appliedCurrent state
SESSIONSSession metadata (client, IP, auth method)365 days
GRANTS_TO_ROLESPrivilege grants on objectsCurrent 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​

FeatureINFORMATION_SCHEMAACCOUNT_USAGE
ScopePer-databaseEntire account
LatencyReal-time45 min to 3 hours
HistoryCurrent state onlyUp to 365 days
Dropped objectsNot visibleVisible
AccessAny role with usageRequires 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​

FrameworkCoverageNotes
SOC 2 Type IIFull platformAnnual audit, report available under NDA
HIPAABAA requiredCustomer must configure controls
PCI DSSLevel 1 Service ProviderApplies to Snowflake infrastructure
FedRAMPModerate (Gov regions)Snowflake Government deployments
HITRUSTCSF CertifiedHealthcare-focused
ISO 27001Full platformInformation 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.