---
url: https://docs.realmsec.info/integration/snowflake-snowpipe.md
---

# Snowflake (Snowpipe) Integration

Realm Security sends data to [Snowflake](https://www.snowflake.com/) with [Snowpipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro). Realm writes files to a cloud storage location that you own. Snowpipe then loads each new file into a Snowflake table.

***

## How Snowpipe Ingestion Works

This integration uses Snowpipe automated loading, also called auto-ingest. Every storage backend uses the same flow:

1. A Realm output feed writes files in the **Data Lake** format to a folder (prefix) in your cloud storage.
2. The cloud storage service publishes an event about each new file to a message service. The message service delivers the event to a queue that Snowflake reads. The event contains the file path, not the data.
3. Snowflake compares the file path with the location of each **pipe** that uses the queue. A pipe's location is the URL of its **external stage**.
4. The pipe reads the file through the stage and loads the rows into the table with its `COPY INTO` statement. Snowpipe records each loaded file, so it does not load the same file twice.

A Snowflake **storage integration** gives the stage read access to your storage. With a storage integration, you do not store cloud credentials in Snowflake.

You create these Snowflake objects:

| Object | Shared by all storage backends | Purpose |
|---|---|---|
| Table | Yes | Holds the loaded events |
| File format | Yes | Tells Snowflake how to read the Data Lake files |
| Storage integration | No | Gives Snowflake read access to your storage |
| External stage | No | Points to the folder that Realm writes to |
| Pipe | No | Loads new files from the stage into the table |
| Notification integration | Only for Google Cloud Storage and Azure | Connects the pipe to a queue that you create. Amazon S3 does not need one, because Snowflake creates the queue. |

> **Note:** Each pipe loads every file in its location. If two pipes have overlapping locations, for example `s3://<bucket>/realm/` and `s3://<bucket>/realm/okta/`, both pipes load the same files. If both pipes load the same table, the table gets duplicate rows.

Realm supports these storage backends for Snowpipe:

| Storage backend | Realm output feed | How Snowflake learns about new files |
|---|---|---|
| [Amazon S3](#snowpipe-via-amazon-s3) | AWS S3 | S3 event notification to an SQS queue that Snowflake manages |

***

## Prerequisites

* Access to the Realm Security console
* A Snowflake role with the `CREATE INTEGRATION` privilege on the account. By default, only `ACCOUNTADMIN` has this privilege.

Each storage backend section lists its own additional prerequisites.

***

## Create the Table and File Format

Do this once, before you set up a storage backend. All storage backends load into the same table and use the same file format. Run the following statements. Replace `<database>` and `<schema>` with your own values.

```sql
USE ROLE ACCOUNTADMIN;
USE SCHEMA <database>.<schema>;

CREATE TABLE REALM_EVENTS (
  RAW            STRING,
  PARSED         VARIANT,
  OBSERVABLES    VARIANT,
  REALM_METADATA VARIANT,
  ENRICHMENTS    VARIANT
);

CREATE FILE FORMAT REALM_DATA_LAKE_JSON
  TYPE = JSON
  COMPRESSION = AUTO;
```

* The table has one column for each top-level key of the Realm Data Lake format.
* `COMPRESSION = AUTO` detects the compression of each file. Realm compresses files with `gzip` by default.

The table columns hold the following data. For the full Data Lake format, see [Data Lake Format](#data-lake-format).

| Column | Type | Content |
|---|---|---|
| `RAW` | `STRING` | The original log line, as Realm received it |
| `PARSED` | `VARIANT` | The parsed event, as a JSON object |
| `OBSERVABLES` | `VARIANT` | Observables that Realm extracted from the event, grouped by type |
| `REALM_METADATA` | `VARIANT` | Realm source, feed, destination, and timestamp metadata |
| `ENRICHMENTS` | `VARIANT` | Enrichment records. `NULL` when enrichments are not enabled for the output feed. |

***

## Snowpipe via Amazon S3

![Snowpipe via Amazon S3 Architecture](../images/snowflake-snowpipe-s3.png)

The Realm AWS S3 output feed writes files to a prefix in your bucket. Amazon S3 sends an event notification to an SQS queue that Snowflake manages, and Snowpipe loads the file into the table.

### Amazon S3 Prerequisites

In addition to the [general prerequisites](#prerequisites), you need:

* An AWS account with permission to create S3 buckets, IAM policies, IAM users, and IAM roles, and to configure event notifications on a bucket
* The table and file format from [Create the Table and File Format](#create-the-table-and-file-format)

Your Snowflake account can be on any cloud platform. Snowpipe cannot load from a bucket in an AWS government region when your Snowflake account is in a commercial region, or the reverse.

> **Note:** The examples in this section use the bucket prefix `realm/`. You can use a different prefix. If you do, use the same prefix in every step, and always end it with a forward slash (`/`).

### Step 1: Create the S3 Bucket

If your Snowflake account is on AWS, put the bucket in the same AWS region as your Snowflake account. A bucket in a different region or on a different cloud works, but the cloud provider can charge data transfer fees when Snowpipe reads files. To find the region of your Snowflake account, run `SELECT CURRENT_REGION();` in Snowflake. A result of `AWS_US_EAST_1` is the AWS region `us-east-1`.

1. In the AWS console, go to **S3 > Buckets** and click **Create bucket**.
2. Enter the bucket details:
   > **Bucket name:** `<bucket>`\
   > **AWS Region:** `<your Snowflake account's region>`
3. Keep **Object Ownership** set to **ACLs disabled (recommended)**. Then the bucket owner owns every file that Realm writes, and the Snowflake IAM role in Step 3 can read the files.
4. Keep **Block all public access** turned on.
5. Click **Create bucket**.

> **Note:** If you set **Default encryption** to SSE-KMS, both AWS identities need permission to use the KMS key. Add `kms:GenerateDataKey` to the Realm IAM user policy in Step 2, and `kms:Decrypt` to the Snowflake IAM role policy in Step 3. The default, SSE-S3, needs no extra permission.

### Step 2: Create an IAM User for Realm

Realm writes files to the bucket with an AWS access key. This IAM user can only write to the `realm/` prefix.

1. Go to **IAM > Policies** and click **Create policy**.

2. Click **JSON** and replace the contents with the following policy. Replace `<bucket>` with the bucket name from Step 1.

   ```json
   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "RealmWriteObjects",
         "Effect": "Allow",
         "Action": "s3:PutObject",
         "Resource": "arn:aws:s3:::<bucket>/realm/*"
       },
       {
         "Sid": "RealmHealthCheck",
         "Effect": "Allow",
         "Action": "s3:ListBucket",
         "Resource": "arn:aws:s3:::<bucket>"
       }
     ]
   }
   ```

3. Click **Next** and enter the policy details:
   > **Name:** `rlm-snowpipe-write`\
   > **Description:** Grant Realm write access to the Snowpipe prefix.

4. Click **Create policy**.

5. Go to **IAM > Users** and click **Create user**.

   > **User name:** `<tenant_name>-realm.security-snowpipe`

   Replace `<tenant_name>` with a short name for your organization, for example `acme`.

6. Do not give the user console access. Click **Next**.

7. Select **Attach policies directly**, select the `rlm-snowpipe-write` policy, and click **Next**.

8. Click **Create user**.

9. Open the new user, go to **Security credentials**, and click **Create access key**.

10. Select **Third-party service**, select the confirmation checkbox, and click **Next**.

11. Click **Create access key**.

12. Copy the **Access key** and the **Secret access key**. You need them in Step 7.

> **Important:** Copy and save the **Secret access key** immediately. AWS does not show it again.

The `s3:ListBucket` permission is for the Realm health check of the bucket.

### Step 3: Create an IAM Policy and Role for Snowflake

Snowflake reads the bucket through an IAM role. These steps follow [Configure a Snowflake storage integration to access Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration).

1. If your Snowflake account is on AWS, go to **IAM > Account settings**. Under **Security Token Service (STS)**, find the region of your Snowflake account. If its status is **Inactive**, set it to **Active**.

2. Go to **IAM > Policies** and click **Create policy**.

3. Click **JSON** and replace the contents with the following read-only policy. Replace `<bucket>` with your bucket name.

   ```json
   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": ["s3:GetObject", "s3:GetObjectVersion"],
         "Resource": "arn:aws:s3:::<bucket>/realm/*"
       },
       {
         "Effect": "Allow",
         "Action": ["s3:ListBucket", "s3:GetBucketLocation"],
         "Resource": "arn:aws:s3:::<bucket>",
         "Condition": { "StringLike": { "s3:prefix": ["realm/*"] } }
       }
     ]
   }
   ```

4. Click **Next**, name the policy `snowflake-snowpipe-read`, and click **Create policy**.

5. Go to **IAM > Roles** and click **Create role**.

6. Select **AWS account**, then select **Another AWS account**.

7. In **Account ID**, enter your own AWS account ID. This is a temporary value.

8. Select **Require external ID** and enter `0000`. This is also a temporary value.

9. Click **Next**, select the `snowflake-snowpipe-read` policy, and click **Next**.

10. Name the role `snowflake-snowpipe-role` and click **Create role**.

11. Open the role and copy its **ARN**. It has the form `arn:aws:iam::<account-id>:role/snowflake-snowpipe-role`.

You replace the two temporary values in Step 4, after Snowflake gives you its IAM user ARN and external ID.

### Step 4: Create the Storage Integration

1. In Snowflake, open a SQL worksheet with the `ACCOUNTADMIN` role, or another role with the `CREATE INTEGRATION` privilege.

2. Run the following statements. Replace `<role-arn>` with the role ARN from Step 3 and `<bucket>` with your bucket name.

   ```sql
   USE ROLE ACCOUNTADMIN;

   CREATE STORAGE INTEGRATION REALM_S3_INT
     TYPE = EXTERNAL_STAGE
     STORAGE_PROVIDER = 'S3'
     ENABLED = TRUE
     STORAGE_AWS_ROLE_ARN = '<role-arn>'
     STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket>/realm/');

   DESC INTEGRATION REALM_S3_INT;
   ```

3. From the `DESC INTEGRATION` output, copy the values of `STORAGE_AWS_IAM_USER_ARN` and `STORAGE_AWS_EXTERNAL_ID`.

4. In the AWS console, go to **IAM > Roles** and open `snowflake-snowpipe-role`.

5. Go to **Trust relationships** and click **Edit trust policy**.

6. Replace the policy with the following. Use the two values from the `DESC INTEGRATION` output.

   ```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>" }
         }
       }
     ]
   }
   ```

7. Click **Update policy**.

> **Important:** Do not run `CREATE OR REPLACE` on this storage integration later. A new integration gets a new external ID, and the trust policy then no longer matches. To change the integration, use `ALTER STORAGE INTEGRATION`.

### Step 5: Create the Stage and Pipe

> **Note:** This example uses `ACCOUNTADMIN` to create the pipe. Snowflake recommends a separate role that owns the pipe and has only the privileges the pipe needs. See [Create a role to own the pipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3).

1. Create the stage in the schema that has the `REALM_EVENTS` table. The stage uses the `REALM_DATA_LAKE_JSON` file format from [Create the Table and File Format](#create-the-table-and-file-format). Replace `<database>`, `<schema>`, and `<bucket>` with your own values.

   ```sql
   USE ROLE ACCOUNTADMIN;
   USE SCHEMA <database>.<schema>;

   CREATE STAGE REALM_S3_STAGE
     URL = 's3://<bucket>/realm/'
     STORAGE_INTEGRATION = REALM_S3_INT
     FILE_FORMAT = REALM_DATA_LAKE_JSON;
   ```

2. Check that Snowflake can read the bucket through the storage integration and the trust policy. Run the following statement.

   ```sql
   LIST @REALM_S3_STAGE;
   ```

   An empty result is correct, because the prefix has no files yet. If the command returns an access error, check the trust policy from Step 4, or run [`SYSTEM$VALIDATE_STORAGE_INTEGRATION`](https://docs.snowflake.com/en/sql-reference/functions/system_validate_storage_integration).

3. Set up automatic loading. The pipe loads each new file in the stage into the `REALM_EVENTS` table. Run the following statement.

   ```sql
   CREATE PIPE REALM_S3_PIPE
     AUTO_INGEST = TRUE
   AS
   COPY INTO REALM_EVENTS (RAW, PARSED, OBSERVABLES, REALM_METADATA, ENRICHMENTS)
   FROM (
     SELECT $1:raw::STRING, $1:parsed, $1:observables, $1:realm_metadata, $1:enrichments
     FROM @REALM_S3_STAGE
   );
   ```

4. Get the ARN of the SQS queue that Snowflake manages for the pipe. You need it in Step 6. Run the following statement, and copy the value of the `notification_channel` column.

   ```sql
   DESC PIPE REALM_S3_PIPE;
   ```

   The value has the form `arn:aws:sqs:<region>:<account-id>:sf-snowpipe-...`.

### Step 6: Configure the S3 Event Notification

1. In the AWS console, go to **S3 > Buckets** and open your bucket.
2. Go to **Properties > Event notifications** and click **Create event notification**.
3. Enter the notification details:
   > **Event name:** `snowpipe-realm`\
   > **Prefix:** `realm/`
4. Leave **Suffix** empty. Realm adds a compression extension to the file names, so a suffix filter can miss files.
5. Under **Event types**, select **All object create events**.
6. Under **Destination**, select **SQS queue**, then select **Enter SQS queue ARN**.
7. Paste the `notification_channel` value from Step 5.
8. Click **Save changes**.

Snowflake owns the SQS queue, in Snowflake's AWS account. You do not change any permissions on the queue.

> **Note:** If S3 reports that the notification overlaps an existing one, another notification on the bucket already sends object-create events for the same prefix. Open that notification and check its destination:
>
> * **The same SQS queue ARN that `DESC PIPE` returned.** You do not need a second notification. Skip the rest of this step.
> * **An Amazon SNS topic.** Use that topic. Drop the pipe, then create it again with `AWS_SNS_TOPIC` set to the topic ARN. Follow **Option 2** in [Automating Snowpipe for Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3).
> * **Any other destination**, for example an application's SQS queue or a Lambda function. Send the events through a new Amazon SNS topic, and subscribe the existing destination to it. Then drop the pipe and create it again with `AWS_SNS_TOPIC`, as in Option 2.
>
> You cannot add `AWS_SNS_TOPIC` to an existing pipe with `ALTER PIPE`, so you must create the pipe again.

### Step 7: Create the Destination in Realm

1. In the Realm console, navigate to **Destinations** and click **Add Destination**.
   > **Name:** `Snowflake`\
   > **Type:** `Data Lake`
2. Click **Add Output Feed** and select **AWS S3** as the method.
3. Fill in the following fields:
   > **Name:** `Snowflake (Snowpipe) S3`\
   > **Bucket Name:** `<bucket>`\
   > **AWS Region:** `<bucket region, e.g. us-east-1>`\
   > **AWS Access Key ID:** `<Access key from Step 2>`\
   > **AWS Secret Access Key:** `<Secret access key from Step 2>`\
   > **Key Prefix:** `realm/`\
   > **Format:** `Data Lake`
4. Click **Add**.
5. Connect the destination to the log sources that you want to send to Snowflake.

> **Important:** Set **Key Prefix** to a value that starts with `realm/` and ends with `/`. Realm puts the Key Prefix directly in front of each file name and adds no `/` between them. For example:
>
> * `realm/` writes `realm/1761657600-<id>.log.gz`. This is correct.
> * `realm` writes `realm1761657600-<id>.log.gz` at the top of the bucket.
> * An empty Key Prefix writes to the default path, `<destination name>/<source name>/YYYY/MM/DD/`.
>
> In the last two cases, the file is outside `realm/`. The IAM user from Step 2 can write only under `realm/`, so the write fails with an access error, and no data reaches Snowflake.

***

## Troubleshooting

Snowpipe usually loads a file within one minute after the storage sends the event.

> **Note:** Snowpipe does not need a warehouse, but the queries in this section do. Select a warehouse in your worksheet before you run them.

To check that data arrives, run:

```sql
SELECT RAW,
       REALM_METADATA:source::STRING          AS SOURCE,
       REALM_METADATA:source_type::STRING     AS SOURCE_TYPE,
       REALM_METADATA:event_timestamp::TIMESTAMP_TZ AS EVENT_TIME
FROM REALM_EVENTS
ORDER BY EVENT_TIME DESC
LIMIT 10;
```

### No Rows Arrive in the Table

Check the status of the pipe for your storage backend, for example `REALM_S3_PIPE`:

```sql
SELECT SYSTEM$PIPE_STATUS('<pipe name>');
```

* **`lastReceivedMessageTimestamp` is missing or old.** No event notifications reach the queue. Do the checks for your storage backend below. This field shows the last notification on the queue for any pipe that uses it.
* **`lastReceivedMessageTimestamp` is recent, but `lastForwardedMessageTimestamp` is missing or old.** Notifications reach the queue, but none of them are for files in this pipe's location. The Key Prefix on the output feed probably does not match the stage URL.
* **`lastForwardedMessageTimestamp` is recent.** The pipe received the files. Run the following query to see the status of each file, and the error if a load failed:

  ```sql
  SELECT *
  FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
    TABLE_NAME => 'REALM_EVENTS',
    START_TIME => DATEADD(hour, -1, CURRENT_TIMESTAMP())));
  ```

### Every Column Is NULL

The output feed does not use the `Data Lake` format. Edit the output feed and set **Format** to `Data Lake`.

### Amazon S3 Checks

1. Check that the Key Prefix on the output feed starts with `realm/` and ends with `/`.
2. In the S3 console, check that new files arrive under `realm/`. If no files arrive, check the IAM user policy from [Step 2](#step-2-create-an-iam-user-for-realm).
3. Check that the event notification from [Step 6](#step-6-configure-the-s3-event-notification) uses the prefix `realm/` and the `notification_channel` value of `REALM_S3_PIPE`.

***

## Data Lake Format

Each file contains newline-delimited JSON objects, one object per event. Each object has these top-level keys:

```json
{
  "raw": "<original log line>",
  "parsed": { "...": "parsed event fields" },
  "observables": {
    "ip_address": ["192.0.2.10", "198.51.100.7"],
    "username": ["alice"]
  },
  "realm_metadata": {
    "source": "<source name>",
    "source_type": "<source format>",
    "event_type": "<event type>",
    "input_feed": "<input feed name>",
    "output_feed": "<output feed name>",
    "destination": "<destination name>",
    "collector": "<collector name>",
    "event_timestamp": "2026-10-30T14:05:11.123Z",
    "ingested_at": "2026-10-30T14:05:12.456Z"
  },
  "enrichments": [
    {
      "src_field": "<event field>",
      "dataset": "<enrichment dataset>",
      "dataset_field": "<dataset field>",
      "value": "<value>"
    }
  ]
}
```

* Realm omits a key when it has no value. For example, `realm_metadata.collector` is present only for events that a Collector forwarded, and `enrichments` is present only when enrichments are enabled for the output feed.
* `observables` maps each observable type to an array of string values.
* `event_timestamp` and `ingested_at` are RFC 3339 strings. To query them as timestamps, cast them, for example `REALM_METADATA:event_timestamp::TIMESTAMP_TZ`.

***

## Support

For more information about Snowpipe, refer to the Snowflake documentation:

* [Introduction to Snowpipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro)
* [Troubleshooting Snowpipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-ts)

Amazon S3:

* [Automating Snowpipe for Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3)
* [Configure a Snowflake storage integration to access Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration)