Snowflake Audit Logs

Realm Security integrates with Snowflake to ingest audit log data from your Snowflake account, giving your security team visibility into authentication events, query activity, and data access patterns.

Prerequisites

A Snowflake account on Enterprise Edition or higher (required for ACCESS_HISTORY)
The ACCOUNTADMIN role, or a custom role with IMPORTED PRIVILEGES on the SNOWFLAKE database
An AWS account with permissions to create S3 buckets and IAM roles

Note: LOGIN_HISTORY and QUERY_HISTORY are available on all Snowflake editions. ACCESS_HISTORY requires Enterprise Edition or higher. If your account does not support ACCESS_HISTORY, Realm will continue collecting login and query history without interruption.

What Data is Collected

Realm ingests three views from SNOWFLAKE.ACCOUNT_USAGE:

View	Description	Latency
`LOGIN_HISTORY`	Authentication events — successful and failed logins, MFA usage, client type, source IP	Up to 2 hours
`QUERY_HISTORY`	Every query executed — SQL text, user, role, warehouse, execution status, performance metrics	Up to 45 minutes
`ACCESS_HISTORY`	Data access patterns — which tables, views, and columns were read or modified by each query	Up to 3 hours

Important: Snowflake imposes latency on ACCOUNT_USAGE views. Events that occurred recently may not yet be queryable. This is a Snowflake platform constraint and not specific to the Realm integration.

Transport Methods

Choose the transport method that best fits your environment:

Transport	Description
AWS S3 Export	Configure Snowflake to periodically export audit logs to an S3 bucket. Realm ingests from S3. Recommended for most customers.

AWS S3 Export

This method uses Snowflake's COPY INTO command and a Snowflake Task to periodically export audit log data to an S3 bucket. Realm then ingests the exported files using your existing AWS S3 source configuration.

Overview

AWS: Create an S3 bucket and IAM role
Snowflake: Create a storage integration and external stage
Snowflake: Create export tasks to write audit logs to S3
Realm: Create a source and configure the AWS S3 input feed

Step 1: AWS — Create an S3 Bucket and IAM Role

Create the S3 Bucket

Log in to the AWS Management Console.
Navigate to S3 > Create bucket.
Give the bucket a name (e.g. my-company-snowflake-audit) and select your preferred region.
Leave all other settings as default and click Create bucket.
Note the bucket name and region — you will need them in subsequent steps.

Create an IAM Policy

In the AWS console, navigate to IAM > Policies > Create policy.
Select the JSON editor and paste the following, replacing <your-bucket-name> with your bucket name:

json

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:DeleteObject",
        "s3:DeleteObjectVersion"
      ],
      "Resource": "arn:aws:s3:::<your-bucket-name>/snowflake-audit/*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::<your-bucket-name>",
      "Condition": {
        "StringLike": {
          "s3:prefix": ["snowflake-audit/*"]
        }
      }
    }
  ]
}

Click Next, give the policy a name (e.g. SnowflakeAuditS3Policy), and click Create policy.

Create an IAM Role

In the AWS console, navigate to IAM > Roles > Create role.
Select AWS account as the trusted entity type.
Enter a placeholder AWS account ID for now (e.g. your own account ID) — you will update the trust policy after completing the Snowflake setup in Step 2.
When prompted to select Require external ID, check the box and enter a placeholder value (e.g. 00000) — you will update this after completing Step 2.
Attach the SnowflakeAuditS3Policy policy you created above.
Give the role a name (e.g. SnowflakeAuditRole) and click Create role.
Note the role ARN (e.g. arn:aws:iam::123456789012:role/SnowflakeAuditRole) — you will need it in Step 2.

Step 2: Snowflake — Create a Storage Integration and External Stage

Run the following commands in a Snowflake worksheet using the ACCOUNTADMIN role.

Create the Storage Integration

sql

CREATE OR REPLACE STORAGE INTEGRATION snowflake_audit_s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<your-iam-role-arn>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<your-bucket-name>/snowflake-audit/');

Replace <your-iam-role-arn> with the ARN from Step 1 and <your-bucket-name> with your bucket name.

Retrieve the Snowflake-Generated IAM User

sql

DESC INTEGRATION snowflake_audit_s3_integration;

From the output, copy the values for:

STORAGE_AWS_IAM_USER_ARN — the IAM user Snowflake will use to access S3
STORAGE_AWS_EXTERNAL_ID — the external ID for the trust relationship

Update the IAM Role Trust Policy in AWS

In the AWS console, navigate to IAM > Roles and open SnowflakeAuditRole.
Select the Trust relationships tab and click Edit trust policy.
Replace the existing policy with the following, substituting the values retrieved above:

json

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "<STORAGE_AWS_IAM_USER_ARN>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<STORAGE_AWS_EXTERNAL_ID>"
        }
      }
    }
  ]
}

Click Update policy.

Create the External Stage

Back in Snowflake, create the external stage that points to your S3 bucket:

sql

CREATE OR REPLACE DATABASE snowflake_audit_export;
CREATE OR REPLACE SCHEMA snowflake_audit_export.exports;
USE DATABASE snowflake_audit_export;
USE SCHEMA exports;

CREATE OR REPLACE FILE FORMAT json_gzip_format
  TYPE = JSON
  COMPRESSION = 'GZIP';

CREATE OR REPLACE STAGE audit_s3_stage
  STORAGE_INTEGRATION = snowflake_audit_s3_integration
  URL = 's3://<your-bucket-name>/snowflake-audit/'
  FILE_FORMAT = json_gzip_format;

Verify the stage is connected correctly:

sql

LIST @audit_s3_stage;

Step 3: Snowflake — Create Export Tasks

The following tasks run on a schedule and export new audit log records to S3 using a watermark table to track progress and avoid duplicates.

Create the Watermark Table

sql

CREATE OR REPLACE TABLE snowflake_audit_export.exports.export_watermarks (
  view_name   STRING,
  last_exported_at TIMESTAMP_TZ
);

INSERT INTO snowflake_audit_export.exports.export_watermarks VALUES
  ('LOGIN_HISTORY',  DATEADD('hour', -24, CURRENT_TIMESTAMP())),
  ('QUERY_HISTORY',  DATEADD('hour', -24, CURRENT_TIMESTAMP())),
  ('ACCESS_HISTORY', DATEADD('hour', -24, CURRENT_TIMESTAMP()));

Note: The initial watermark is set to 24 hours ago. Adjust this value if you want to backfill more historical data on first run.

Create the Export Tasks

sql

-- Login History export task
CREATE OR REPLACE TASK export_login_history
  WAREHOUSE = <your-warehouse>
  SCHEDULE = '10 MINUTE'
AS
DECLARE
  last_ts TIMESTAMP_TZ;
  current_ts TIMESTAMP_TZ;
BEGIN
  SELECT last_exported_at INTO :last_ts
    FROM snowflake_audit_export.exports.export_watermarks
    WHERE view_name = 'LOGIN_HISTORY';

  current_ts := CURRENT_TIMESTAMP();

  COPY INTO @snowflake_audit_export.exports.audit_s3_stage/login_history/
  FROM (
    SELECT OBJECT_CONSTRUCT(*)
    FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
    WHERE EVENT_TIMESTAMP > :last_ts
      AND EVENT_TIMESTAMP <= :current_ts
  )
  FILE_FORMAT = (TYPE = JSON COMPRESSION = 'GZIP')
  OVERWRITE = FALSE
  HEADER = FALSE;

  UPDATE snowflake_audit_export.exports.export_watermarks
    SET last_exported_at = :current_ts
    WHERE view_name = 'LOGIN_HISTORY';
END;

-- Query History export task
CREATE OR REPLACE TASK export_query_history
  WAREHOUSE = <your-warehouse>
  SCHEDULE = '10 MINUTE'
AS
DECLARE
  last_ts TIMESTAMP_TZ;
  current_ts TIMESTAMP_TZ;
BEGIN
  SELECT last_exported_at INTO :last_ts
    FROM snowflake_audit_export.exports.export_watermarks
    WHERE view_name = 'QUERY_HISTORY';

  current_ts := CURRENT_TIMESTAMP();

  COPY INTO @snowflake_audit_export.exports.audit_s3_stage/query_history/
  FROM (
    SELECT OBJECT_CONSTRUCT(*)
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME > :last_ts
      AND START_TIME <= :current_ts
  )
  FILE_FORMAT = (TYPE = JSON COMPRESSION = 'GZIP')
  OVERWRITE = FALSE
  HEADER = FALSE;

  UPDATE snowflake_audit_export.exports.export_watermarks
    SET last_exported_at = :current_ts
    WHERE view_name = 'QUERY_HISTORY';
END;

-- Access History export task (Enterprise Edition only)
CREATE OR REPLACE TASK export_access_history
  WAREHOUSE = <your-warehouse>
  SCHEDULE = '10 MINUTE'
AS
DECLARE
  last_ts TIMESTAMP_TZ;
  current_ts TIMESTAMP_TZ;
BEGIN
  SELECT last_exported_at INTO :last_ts
    FROM snowflake_audit_export.exports.export_watermarks
    WHERE view_name = 'ACCESS_HISTORY';

  current_ts := CURRENT_TIMESTAMP();

  COPY INTO @snowflake_audit_export.exports.audit_s3_stage/access_history/
  FROM (
    SELECT OBJECT_CONSTRUCT(*)
    FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
    WHERE QUERY_START_TIME > :last_ts
      AND QUERY_START_TIME <= :current_ts
  )
  FILE_FORMAT = (TYPE = JSON COMPRESSION = 'GZIP')
  OVERWRITE = FALSE
  HEADER = FALSE;

  UPDATE snowflake_audit_export.exports.export_watermarks
    SET last_exported_at = :current_ts
    WHERE view_name = 'ACCESS_HISTORY';
END;

Replace <your-warehouse> with the name of a Snowflake warehouse to use for the export queries. A small warehouse (XS) is sufficient.

Activate the Tasks

Tasks are created in a suspended state. Resume them to begin exporting:

sql

ALTER TASK export_login_history RESUME;
ALTER TASK export_query_history RESUME;
ALTER TASK export_access_history RESUME;  -- Omit if not on Enterprise Edition

Verify the tasks are running:

sql

SELECT name, state, last_completed_time, error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
ORDER BY scheduled_time DESC
LIMIT 20;

Step 4: Configure S3 Event Notifications for Realm

For Realm to pick up new files as they arrive, configure SQS event notifications on your S3 bucket following the AWS S3 source setup guide. The setup is identical regardless of log format — point the SQS queue at the snowflake-audit/ prefix of your bucket.

Step 5: Realm — Create a Source and Configure the Input Feed

In Realm, go to Sources > Add Source.
Enter a name for your source (e.g. Snowflake Audit Logs).
Set Product Format to Snowflake Audit Logs.
Click Save.
From the source page, click Add Input Feed, select AWS S3, and configure it with the credentials and SQS queue URL from Step 4.
Click Save.

Troubleshooting

No files appearing in S3

Confirm the Snowflake tasks are in RESUMED state — run SHOW TASKS in Snowflake and check the state column.
Check the task history for errors: SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) ORDER BY scheduled_time DESC LIMIT 20.
Verify the IAM role trust policy was updated with the correct STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID from the DESC INTEGRATION output.
Confirm the Snowflake warehouse is not suspended — a suspended warehouse will cause tasks to fail silently.

No events appearing in Realm after files land in S3

Confirm the SQS queue is correctly configured to receive event notifications from the S3 bucket prefix snowflake-audit/.
Verify the Realm input feed credentials have s3:GetObject and sqs:ReceiveMessage permissions.

Duplicate events

The watermark table prevents duplicates within a single pipeline run, but if tasks are deleted and recreated the watermarks reset. Do not delete the export_watermarks table. If you need to reset the watermarks, update the values manually:

sql

UPDATE snowflake_audit_export.exports.export_watermarks
  SET last_exported_at = '<desired_start_timestamp>'
  WHERE view_name = 'LOGIN_HISTORY';

Missing recent events

Snowflake ACCOUNT_USAGE views have inherent latency — up to 2 hours for LOGIN_HISTORY, 45 minutes for QUERY_HISTORY, and 3 hours for ACCESS_HISTORY. Events that occurred very recently will not yet be available in the views and will appear in the next export cycle after the latency window has passed.

ACCESS_HISTORY task failing

ACCESS_HISTORY requires Snowflake Enterprise Edition or higher. If your account is on Standard Edition, suspend or drop the export_access_history task:

sql

ALTER TASK export_access_history SUSPEND;

Realm Focus

Snowflake Audit Logs

Prerequisites

What Data is Collected

Transport Methods

AWS S3 Export

Overview

Step 1: AWS — Create an S3 Bucket and IAM Role

Create the S3 Bucket

Create an IAM Policy

Create an IAM Role

Step 2: Snowflake — Create a Storage Integration and External Stage

Create the Storage Integration

Retrieve the Snowflake-Generated IAM User

Update the IAM Role Trust Policy in AWS

Create the External Stage

Step 3: Snowflake — Create Export Tasks

Create the Watermark Table

Create the Export Tasks

Activate the Tasks

Step 4: Configure S3 Event Notifications for Realm

Step 5: Realm — Create a Source and Configure the Input Feed

Troubleshooting

Resources

Realm Focus

Snowflake Audit Logs ​

Prerequisites ​

What Data is Collected ​

Transport Methods ​

AWS S3 Export ​

Overview ​

Step 1: AWS — Create an S3 Bucket and IAM Role ​

Create the S3 Bucket ​

Create an IAM Policy ​

Create an IAM Role ​

Step 2: Snowflake — Create a Storage Integration and External Stage ​

Create the Storage Integration ​

Retrieve the Snowflake-Generated IAM User ​

Update the IAM Role Trust Policy in AWS ​

Create the External Stage ​

Step 3: Snowflake — Create Export Tasks ​

Create the Watermark Table ​

Create the Export Tasks ​

Activate the Tasks ​

Step 4: Configure S3 Event Notifications for Realm ​

Step 5: Realm — Create a Source and Configure the Input Feed ​

Troubleshooting ​

Resources ​

Snowflake Audit Logs

Prerequisites

What Data is Collected

Transport Methods

AWS S3 Export

Overview

Step 1: AWS — Create an S3 Bucket and IAM Role

Create the S3 Bucket

Create an IAM Policy

Create an IAM Role

Step 2: Snowflake — Create a Storage Integration and External Stage

Create the Storage Integration

Retrieve the Snowflake-Generated IAM User

Update the IAM Role Trust Policy in AWS

Create the External Stage

Step 3: Snowflake — Create Export Tasks

Create the Watermark Table

Create the Export Tasks

Activate the Tasks

Step 4: Configure S3 Event Notifications for Realm

Step 5: Realm — Create a Source and Configure the Input Feed

Troubleshooting

Resources