Snowflake

JustAI connects to Snowflake through a secure Amazon S3 exchange. Your data team exports engagement and conversion events from Snowflake to an org-specific S3 prefix, and JustAI ingests those files for experiment measurement. JustAI can also write variant and message-level records to S3 for loading into Snowflake.

This guide covers both directions. You only need to configure the flows your team uses.

Before you start

Coordinate with your JustAI contact to get:

The S3 bucket and dedicated prefix for your organization.
The AWS IAM role ARN and external ID to use in your Snowflake storage integration.
The required event fields, accepted file format, export cadence, and historical backfill window.

You will also need a Snowflake role that can create a storage integration, stage, file format, and scheduled task. Keep the S3 prefix limited to your organization and grant only the read or write permissions required for each flow.

Send Snowflake events to JustAI

Use this flow when engagement or conversion events live in Snowflake and JustAI needs them to measure experiment outcomes.

1. Create a storage integration

Create a Snowflake storage integration for the S3 location provided by JustAI. Replace every placeholder with the values from your JustAI contact.

CREATE OR REPLACE STORAGE INTEGRATION justai_s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<JUSTAI_AWS_ROLE_ARN>'
  STORAGE_AWS_EXTERNAL_ID = '<EXTERNAL_ID>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<BUCKET>/<ORG_PREFIX>/snowflake/');

Run the following command and share the returned Snowflake IAM user ARN and external ID with JustAI. JustAI will use them to finish the S3 trust policy.

DESC INTEGRATION justai_s3_integration;

Do not put AWS access keys or secrets directly in the stage definition.

2. Create a file format and external stage

Parquet is preferred because it preserves types and is efficient for batch processing.

CREATE OR REPLACE FILE FORMAT justai_parquet_format
  TYPE = PARQUET;

CREATE OR REPLACE STAGE justai_event_export_stage
  URL = 's3://<BUCKET>/<ORG_PREFIX>/snowflake/events/'
  STORAGE_INTEGRATION = justai_s3_integration
  FILE_FORMAT = justai_parquet_format;

3. Build the event export

The source fields depend on your ESP:

Iterable: events can carry Iterable message, template, campaign, and user identifiers alongside the JustAI copy_id.
Customer.io and other ESPs: engagement events may use provider-specific journey, action, delivery, or message identifiers instead of copy_id. Join those events to the JustAI message-level assignment export using the identifiers agreed with JustAI, then include the matched copy_id in the event export.

The table below is a normalized export shape, not the native schema for every provider:

Field	Required	Description
`event_timestamp`	Yes	When the event occurred, in the agreed timestamp format.
`event_name`	Yes	The outcome being measured, such as `send`, `click`, or `purchase`.
`copy_id`	Yes	The JustAI copy ID returned when content was selected.
`user_id`	Recommended	Your stable user identifier for joining events across systems.
`event_id`	Recommended	A stable, unique identifier used to prevent duplicate counting.
`template_id`	Optional	The JustAI template identifier.
`campaign_id`	Optional	Your messaging-platform campaign identifier.

The source query depends on your warehouse model. For an Iterable integration that already stores copy_id with each event, the query can be a direct projection:

SELECT
  event_timestamp,
  event_name,
  copy_id,
  user_id,
  event_id,
  template_id,
  campaign_id
FROM analytics.messaging_events
WHERE event_timestamp >= DATEADD('hour', -24, CURRENT_TIMESTAMP());

For an ESP whose engagement events do not contain copy_id, first join them to the JustAI assignment records. This example uses placeholder provider identifiers; replace the join with the identifiers and attribution rules agreed with JustAI:

SELECT
  event.event_timestamp,
  event.event_name,
  assignment.copy_id,
  event.user_id,
  event.event_id,
  assignment.template_id,
  event.campaign_id
FROM analytics.esp_events AS event
JOIN raw.justai_message_records AS assignment
  ON event.provider_message_id = assignment.provider_message_id
WHERE event.event_timestamp >= DATEADD('hour', -24, CURRENT_TIMESTAMP());

4. Export files to S3

Use COPY INTO <location> to unload the query results. Partition exports by date and use a stable schedule after the source data is complete. Set HEADER = TRUE so Parquet files retain the agreed field names.

Before rerunning or backfilling a partition, remove every file under that exact partition path. OVERWRITE = TRUE only replaces matching filenames, so it can leave stale files when a rerun produces a different set of output files.

REMOVE @justai_event_export_stage/dt=2026-07-21/;

COPY INTO @justai_event_export_stage/dt=2026-07-21/events_
FROM (
  SELECT
    event_timestamp,
    event_name,
    copy_id,
    user_id,
    event_id,
    template_id,
    campaign_id
  FROM analytics.messaging_events
  WHERE event_timestamp >= '2026-07-21 00:00:00'
    AND event_timestamp < '2026-07-22 00:00:00'
)
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE;

Schedule these statements with a Snowflake task or your existing orchestrator. Only remove the bounded partition being replaced, and do not run two exports for the same partition concurrently.

Load JustAI data into Snowflake

Use this flow when your team wants to join JustAI variant metadata and message-level assignment records to its warehouse data.

1. Confirm the outbound dataset

JustAI will provide the S3 location, file format, delivery schedule, and schema. Depending on your integration, the export can include:

Variant metadata, including copy_id, template_id, content fields, and configured attributes.
Message-level records showing which copy_id was selected for a user or message.

2. Create Snowflake tables

Create target tables that match the schema agreed with JustAI. Keep raw ingestion tables separate from modeled analytics tables so schema changes and backfills can be handled safely.

3. Create an external stage and load the files

Create a read-enabled stage for the outbound S3 prefix using the storage integration pattern above. Then load a delivery with COPY INTO <table>:

COPY INTO raw.justai_message_records
FROM @justai_outbound_stage/message_records/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

For scheduled batch loads, use a Snowflake task. If you need lower-latency ingestion, ask your JustAI contact whether Snowpipe notifications are available for your S3 prefix.

Validate the integration

Before enabling the recurring schedule:

Export a small, bounded time window.
Confirm files appear under the agreed S3 prefix and can be read by the receiving side.
Compare row counts and a sample of copy_id, event, and timestamp values between Snowflake and the destination.
Confirm duplicate event IDs and rerun behavior do not inflate metrics.
Ask JustAI to verify that the test events appear in the expected template or experiment.

Troubleshooting

Access denied when using the stage

Run DESC INTEGRATION justai_s3_integration and confirm JustAI added the returned identity to the bucket trust policy.
Confirm the stage URL is inside STORAGE_ALLOWED_LOCATIONS.
Confirm the S3 policy grants access to your exact organization prefix.

Files arrive but no events appear in JustAI

Confirm the export finished before the JustAI ingestion schedule began.
Check that copy_id, event_name, and event_timestamp are populated and use the agreed types.
Verify the partition path and file format match the handoff details.
Compare the exported time window with the event timestamps, including timezone handling.

A backfill creates duplicate metrics

Export stable event_id values and rewrite complete date partitions rather than appending a second copy of the same events. Tell JustAI which partitions were replaced so they can rerun the corresponding ingestion window.