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.
Snowflake SQL API	Realm polls the Snowflake SQL API directly using key-pair (JWT) authentication. No S3 bucket, IAM role, or Snowflake Tasks required. Recommended if you don't want to manage AWS infrastructure for this integration.

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.

Snowflake SQL API

This method uses a native Realm input feed that authenticates directly to Snowflake with a key-pair (JWT) credential and polls the Snowflake SQL API for audit log data. Unlike the S3 export method, no S3 bucket, IAM role, external stage, or Snowflake Tasks are required — Realm queries SNOWFLAKE.ACCOUNT_USAGE on its own schedule.

Overview

Snowflake: Generate a key pair and assign the public key to a Snowflake user
Snowflake: Look up your account identifier
Snowflake: Choose or create a warehouse
Realm: Create a source and configure the Snowflake input feed

Step 1: Snowflake — Generate a Key Pair and Assign It to a User

Realm authenticates to the SQL API by signing a JWT with an RSA private key, so no password or shared secret is ever sent to Realm.

Generate the Key Pair

Run the following using OpenSSL:

shell

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

This produces two files:

rsa_key.pub — the public key. This gets assigned to the Snowflake user in the next step.
rsa_key.p8 — the private key. This is never uploaded to Snowflake — store it securely, as it will be provided directly to Realm when configuring the input feed.

Assign the Public Key to a Snowflake User

Realm recommends using a dedicated, least-privilege service user rather than a personal account. That user's role needs IMPORTED PRIVILEGES on the SNOWFLAKE database (see Prerequisites) and USAGE on the warehouse chosen in Step 3.

Note the username (<username> below) — you will enter it in the input feed's Username field in Step 4.

Get the raw public key value, stripped of its header/footer lines:
shell
```
grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n'
```
In a Snowflake worksheet, using the ACCOUNTADMIN role (or a role with rights to alter the target user), run:
sql
```
ALTER USER <username> SET RSA_PUBLIC_KEY='<public_key_value_from_step_1>';
```
Verify the key was assigned correctly:
sql
```
DESC USER <username>;
```
Confirm the RSA_PUBLIC_KEY_FP row is populated with a SHA256: fingerprint.

Note: Snowflake supports a second key slot (RSA_PUBLIC_KEY_2) on the same user. Use it to rotate keys without downtime — assign a new key pair to the unused slot, update the Auth value in Realm, then unset the old key once traffic has cut over.

Step 2: Snowflake — Look Up Your Account Identifier

Log in to Snowsight.
Click your account name in the bottom-left corner of the page.
The Account panel lists your account identifier (e.g. myorg-myaccount) — click it to copy the value.

Alternatively, run the following in a worksheet:

sql

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS account_identifier;

Note: Use the same account identifier format shown in Snowsight or returned by the query above. This is the value Realm expects in the Account Identifier field of the input feed.

Step 3: Snowflake — Choose or Create a Warehouse

The input feed needs a warehouse to run its queries against SNOWFLAKE.ACCOUNT_USAGE. You can reuse an existing warehouse or create a small dedicated one:

sql

CREATE WAREHOUSE IF NOT EXISTS realm_audit_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

Grant the service user's role USAGE on the warehouse:

sql

GRANT USAGE ON WAREHOUSE realm_audit_wh TO ROLE <role>;

Note the warehouse name — you will need it in Step 4.

Step 4: 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 Snowflake, and provide:
Auth: Create (or select) a credential containing the private key file (rsa_key.p8) from Step 1. Only the private key goes into this credential.
Username: The Snowflake username from Step 1 that the key pair was assigned to.
Account Identifier: The value from Step 2.
Warehouse: The warehouse name from Step 3.
Click Save.

Note: The private key must not be passphrase-protected — if you generated it with a passphrase, strip it before pasting it in:
shell
openssl pkcs8 -in rsa_key_encrypted.p8 -topk8 -nocrypt -out rsa_key.p8

Note: Realm manages the polling schedule and how far back it backfills on first run automatically — there's nothing to configure for either of those. Because of the ACCOUNT_USAGE latency described above, don't expect events to appear the moment they occur in Snowflake even once polling is running.

Troubleshooting

AWS S3 Export

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 the necessary SQS and S3 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;

Snowflake SQL API

Authentication failing / JWT rejected

Confirm the private key provided to Realm (rsa_key.p8) matches the public key assigned to the Snowflake user — re-run DESC USER <username> and compare RSA_PUBLIC_KEY_FP against the fingerprint of your local key file.
Confirm the input feed's Username field matches the Snowflake user the key pair was assigned to in Step 1 — a mismatched username will fail authentication even with a valid key.
If you rotated keys, confirm the old key wasn't unset before Realm's Auth credential was updated to the new private key.
Confirm the user account is not locked or disabled.

Queries failing or timing out

Confirm the warehouse specified in the input feed exists, is not dropped, and that the service user's role has been granted USAGE on it.
If AUTO_RESUME is disabled on the warehouse and it's suspended, queries will fail rather than triggering a resume — either enable AUTO_RESUME or ensure the warehouse is left running.

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. This applies to the SQL API transport as well, since it queries the same views. Events that occurred very recently will not yet be queryable and will appear once the latency window has passed and Realm's next poll runs.

ACCESS_HISTORY not appearing

ACCESS_HISTORY requires Snowflake Enterprise Edition or higher. If your account is on Standard Edition, this is expected — Realm continues to collect LOGIN_HISTORY and QUERY_HISTORY without interruption.

Quick Start

Realm Focus

Collector

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

Snowflake SQL API

Overview

Step 1: Snowflake — Generate a Key Pair and Assign It to a User

Generate the Key Pair

Assign the Public Key to a Snowflake User

Step 2: Snowflake — Look Up Your Account Identifier

Step 3: Snowflake — Choose or Create a Warehouse

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

Troubleshooting

AWS S3 Export

Snowflake SQL API

Resources

Realm Focus

Collector

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 ​

Snowflake SQL API ​

Overview ​

Step 1: Snowflake — Generate a Key Pair and Assign It to a User ​

Generate the Key Pair ​

Assign the Public Key to a Snowflake User ​

Step 2: Snowflake — Look Up Your Account Identifier ​

Step 3: Snowflake — Choose or Create a Warehouse ​

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

Troubleshooting ​

AWS S3 Export ​

Snowflake SQL API ​

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

Snowflake SQL API

Overview

Step 1: Snowflake — Generate a Key Pair and Assign It to a User

Generate the Key Pair

Assign the Public Key to a Snowflake User

Step 2: Snowflake — Look Up Your Account Identifier

Step 3: Snowflake — Choose or Create a Warehouse

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

Troubleshooting

AWS S3 Export

Snowflake SQL API

Resources