Snowflake

Sync custom events from Snowflake to OneSignal to trigger automated Journeys and personalized messaging campaigns based on user behavior.

OneSignal Snowflake integration overview

---

Overview

The OneSignal + Snowflake integration supports two powerful data pipelines:

• Export: Automatically send messaging event data (push, email, SMS, in-app) from OneSignal to Snowflake for analysis and reporting.
• Import: Sync custom user events from your Snowflake datasets to OneSignal to trigger automated Journeys and personalized messaging.

Together, these integrations give you complete control over user engagement data—powering advanced analytics and real-time behavior-driven messaging.

---

Export OneSignal events to Snowflake

Send messaging performance and engagement events (e.g., sends, opens, clicks) to Snowflake to:

• Build custom dashboards and reports
• Track delivery and engagement trends across channels
• Combine OneSignal data with other business data for analysis

Requirements

• OneSignal Professional Plan (not available on free apps).
• Snowflake account
• Ensure your Snowflake account has the IMPORT SHARE and CREATE DATABASE roles.
  • We recommend using the ACCOUNTADMIN role for accepting the share.

Setup Steps

1. Sign up

In order to activate the Snowflake integration, you’ll need to request access via the OneSignal Marketplace listing for Message Events Data. From there we’ll reach out to your team to obtain more information.

2. Provide Account Information

Once we connect with your team, you’ll need to provide some details to us so we can share data with you. We will need your Snowflake Account ID and your Snowflake Organization ID. The easiest way to get that info is to click the “Copy account identifier” button (as shown below).

You should be in contact with us via the OneSignal Marketplace listing. However, if you have any further questions about providing account information, you can email [e-mail]

Copying Snowflake account identifier

Once you are accepted into the program, be on the lookout for a few items:

• Email from OneSignal welcoming you into the program
• Email from Snowflake notifying that the data share is complete

Snowflake data share notification email

3. Accept data share within Snowflake

Once you receive an email from Snowflake notifying you about the data share being complete, you should be able to log into the Snowflake app and accept the data share.

Navigate to Data → Private Sharing where you should be able to see a new Privately Shared Listing. Once you click Get, your data will start replicating to your region.

Snowflake private sharing interface

Note: If the Get button is not available but a Request button is, the permissions of the role attempting to make the share does not include CREATE DATABASE or IMPORT SHARE. We suggest switching to the ACCOUNTADMIN role.

It will take some time for the data to be replicated. Snowflake will follow up with an additional email once the data is available. You should see a dialog that looks like this

Data replication status dialog

Once the data is ready to use, your listing should have a new Get button available. Click the Get button and you will be greeted with a new dialog

Database configuration dialog

Set the name of the database and database roles to something you prefer. Then click Get

Data share setup completion

That’s it! Data is now being synced from OneSignal to Snowflake. The sync cadence is set to once every 24 hours, so the initial data may not be available until the next day.

Events and Properties

An overview of the data available on Snowflake’s Marketplace

Message Events Data

See Event Streams for more details.

For each message event generated by a user, the following metadata will be attached to the record.

Column Name	Type	Description	Example
event_id	UUID	A unique ID that represents the event	dc76df03-287c-403a-aabd-b2d4fe44e5c6
event_kind	string	The type of event that occurred. The list of event kinds can be found here.	message.email.opened
event_impression_timestamp	unix_timestamp	The time that Onesignal processes the event	1684951205
subscription_id	UUID	A unique ID that represents the device/subscription	6e8b6978-75a4-4882-878c-2d15a78c2409
subscription_language	string	The language of this subscription	en
subscription_timezone	integer	The specified timezone of this subscription	-25200
subscription_device_type	string	The type of device this subscription uses.	SMS
onesignal_id	UUID	The onesignal_id of the user that made the impression	dc76df03-287c-403a-aabd-b2d4fe44e5c6
external_id	string	The external_id of the user that made the impression	MyExternalId
message_id	UUID	A unique ID that represents the message	dc76df03-287c-403a-aabd-b2d4fe44e5c6
message_name	string	The human-readable id of the Notification/Message	Campaign 1
message_title	string	The Title of the Notification/Message.English only at this time	Bonus 50 coins when you play!
message_body	string	The Body of the message. (Truncated) English only at this time.	Play against your friends and earn an additional...

Message Event Kinds

See Event Streams for more details.

Message Events are the first category of data that we’ll be offering on Snowflake’s Marketplace. Please reach out to us at [e-mail] with any requests for additional categories of data!

Data Retention

Data shared from OneSignal via Snowflake is retained for a period of 30 days. To ensure data preservation beyond this timeframe, we recommend you copy the relevant data to your own tables.

---

Import events from Snowflake

Send behavioral event data from Snowflake to OneSignal to:

• Trigger Journeys based on user activity
• Personalize messaging based on behavioral data

Requirements

• Access to Custom Events (currently in beta)
• Updated Account Plan (not available on free apps).

• Snowflake account with warehouse access
• Event data stored in Snowflake tables or views
• Network connectivity from OneSignal to your Snowflake instance
• User credentials with appropriate permissions

Setup Steps

1. Create dedicated role for OneSignal

   Create a role hierarchy following Snowflake best practices:

   -- Create a role for the census user
   CREATE ROLE CENSUS_ROLE;
   
   -- Ensure the sysadmin role inherits any privileges the census role is granted
   GRANT ROLE CENSUS_ROLE TO ROLE SYSADMIN;
   

2. Create dedicated warehouse

   Create a cost-optimized warehouse for OneSignal operations:

   -- Create a warehouse for the census role, optimizing for cost over performance
   CREATE WAREHOUSE CENSUS_WAREHOUSE WITH
       WAREHOUSE_SIZE = XSMALL
       AUTO_SUSPEND = 60
       AUTO_RESUME = TRUE
       INITIALLY_SUSPENDED = FALSE;
   
   GRANT USAGE ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;
   GRANT OPERATE ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;
   GRANT MONITOR ON WAREHOUSE CENSUS_WAREHOUSE TO ROLE CENSUS_ROLE;
   

3. Create user and grant permissions

   Create the OneSignal user and grant access to your event data:

   -- Create the census user
   CREATE USER CENSUS WITH
       DEFAULT_ROLE = CENSUS_ROLE
       DEFAULT_WAREHOUSE = CENSUS_WAREHOUSE
       PASSWORD = '<strong-unique-password>';
   
   GRANT ROLE CENSUS_ROLE TO USER CENSUS;
   
   -- Grant access to your event data (replace with your actual database/schema)
   GRANT USAGE ON DATABASE "<your-database>" TO ROLE CENSUS_ROLE;
   GRANT USAGE ON SCHEMA "<your-database>"."<your-schema>" TO ROLE CENSUS_ROLE;
   GRANT SELECT ON ALL TABLES IN SCHEMA "<your-database>"."<your-schema>" TO ROLE CENSUS_ROLE;
   GRANT SELECT ON FUTURE TABLES IN SCHEMA "<your-database>"."<your-schema>" TO ROLE CENSUS_ROLE;
   GRANT SELECT ON ALL VIEWS IN SCHEMA "<your-database>"."<your-schema>" TO ROLE CENSUS_ROLE;
   GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<your-database>"."<your-schema>" TO ROLE CENSUS_ROLE;
   

4. Create bookkeeping database (Advanced Sync Engine)

   Create a private database for OneSignal’s sync state management:

   -- Create a private bookkeeping database
   CREATE DATABASE "CENSUS";
   GRANT ALL PRIVILEGES ON DATABASE "CENSUS" TO ROLE CENSUS_ROLE;
   
   CREATE SCHEMA "CENSUS"."CENSUS";
   GRANT ALL PRIVILEGES ON SCHEMA "CENSUS"."CENSUS" TO ROLE CENSUS_ROLE;
   GRANT CREATE STAGE ON SCHEMA "CENSUS"."CENSUS" TO ROLE CENSUS_ROLE;
   

   Skip this step if using Basic Sync Engine or read-only mode.

5. Configure authentication

   Set up key-pair authentication (recommended) for enhanced security:

   1. Generate a public/private key pair following Snowflake’s documentation
   2. Configure the public key on your Snowflake user
   3. Use the private key in OneSignal’s connection settings

   Alternatively, you can use password authentication (deprecated - will be blocked November 2025).

6. Connect to OneSignal

   In OneSignal, go to Data > Integrations and click Add Integration.

   Select Snowflake and provide the following connection details:

   • Account Name: Your Snowflake account identifier (e.g., abc123.us-east-1)
   • Warehouse: CENSUS_WAREHOUSE
   • User: CENSUS
   • Database: Your event data database name
   • Schema: Your event data schema name
   • Authentication: Key-pair (provide private key and optional passphrase)

---

Event data mapping

Map your to OneSignal’s custom events format:

OneSignal Field	Description	Required
name	event_name	Event identifier	Yes
external_id	user_id	User identifier	Yes
timestamp	event_timestamp	When event occurred	No
properties	event_data	No

Example Event Table Schema

-- Example Snowflake event table
CREATE TABLE analytics.user_events (
    event_id STRING,
    event_name STRING NOT NULL,
    user_id STRING NOT NULL,
    event_timestamp TIMESTAMP_TZ DEFAULT CURRENT_TIMESTAMP(),
    event_properties VARIANT,
    session_id STRING,
    device_type STRING
);

SQL Query Mode

Write custom SQL queries to transform your event data:

-- Example: Recent high-value events
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties
FROM analytics.user_events
WHERE event_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP())
    AND event_properties:value::NUMBER > 100
ORDER BY event_timestamp DESC;

Advanced configuration

Managing Warehouse Costs

• Use X-Small warehouse size for cost optimization
• Configure auto-suspend (60 seconds) and auto-resume
• Schedule syncs during off-peak hours
• Consider sharing warehouse with other batch processing systems

Live Syncs Support

For real-time event processing, enable change tracking on your event tables:

ALTER TABLE "analytics"."user_events" SET CHANGE_TRACKING = TRUE;

Network Security

If using Snowflake’s Allowed IPs network policy, add OneSignal’s IP addresses to your allowlist. Contact OneSignal support for the current IP ranges.

---

Limitations

• Complex analytical queries may impact warehouse performance and costs
• User/Password authentication will be deprecated November 2025
• The CENSUS database is reserved for OneSignal operations only

---

Disconnect data share with OneSignal

Please email us at [e-mail] to request to deactivate your data share with OneSignal.

---

FAQ

Which authentication method should I use?

Use Key-pair authentication (recommended). User/Password authentication will be blocked by Snowflake starting November 2025.

Can I use an existing warehouse?

Yes, you can share a warehouse with other batch processing systems like dbt or Fivetran to optimize costs. Ensure the warehouse has sufficient capacity for your event processing needs.

How can I optimize costs?

• Use X-Small warehouse size
• Configure aggressive auto-suspend (60 seconds)
• Schedule syncs during off-peak hours
• Use hourly/daily syncs instead of continuous syncing

---