Snowflake

OneSignal Snowflake integration overview

​

Overview

• 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.

​

Export OneSignal events to Snowflake

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

• OneSignal Professional Plan (not available on free apps)
• Snowflake account
• SECURITYADMIN or ACCOUNTADMIN role in Snowflake (for setup)

​

1. Gather your Snowflake account details

• Snowflake Host: Your account URL in the format <account_identifier>.snowflakecomputing.com
• Database name: The database where OneSignal will write event data
• Schema name: The schema within the database for OneSignal tables (this will be auto created by OneSignal)
• Warehouse name: The warehouse to use for data loading operations

Snowflake account identifier location

​

2. Run setup script in Snowflake

begin;

   -- create variables for user / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'ONESIGNAL_ROLE';
   set user_name = 'ONESIGNAL_USER';
   set warehouse_name = 'ONESIGNAL_WAREHOUSE';
   set database_name = 'ONESIGNAL';

   -- change role to securityadmin for user / role steps
   use role securityadmin;

   -- create role for onesignal
   create role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;

   -- create a user for onesignal
   create user if not exists identifier($user_name)
   default_role = $role_name
   default_warehouse = $warehouse_name;

   grant role identifier($role_name) to user identifier($user_name);

   -- set binary_input_format to BASE64
   ALTER USER identifier($user_name) SET BINARY_INPUT_FORMAT = 'BASE64';

   -- set timestamp_input_format to AUTO for the user
   ALTER USER identifier($user_name) SET TIMESTAMP_INPUT_FORMAT = 'AUTO';

   -- change role to sysadmin for warehouse / database steps
   use role sysadmin;

   -- create a warehouse for onesignal
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;

   -- create database for onesignal
   create database if not exists identifier($database_name);

   -- grant onesignal role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);

   -- grant onesignal access to database
   grant CREATE SCHEMA, MONITOR, USAGE
   on database identifier($database_name)
   to role identifier($role_name);

 commit;

​

3. Generate key pair for authentication

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes256 -inform PEM -out rsa_key.p8

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

ALTER USER ONESIGNAL_USER SET RSA_PUBLIC_KEY='<YOUR_PUBLIC_KEY_CONTENT>';

​

4. Connect OneSignal

​

5. View data in Snowflake

-- View recent message events
SELECT *
FROM <your-database>.<your-schema>.message_events
ORDER BY _CREATED DESC
LIMIT 100;

​

Message events and properties

​

Message event kinds

​

Event data schema

​

Notes

• Syncs after saving/activating may take an additional 15-30 minutes to complete.
• Deactivating may still result in one final sync after deactivation.
• To ensure efficient data synchronization, our system automatically creates and manages staging datasets. These datasets, named with a pattern like fivetran_{two random words}_staging, temporarily store data during processing before it’s integrated into your main schema. These staging datasets are essential for maintaining a streamlined workflow and should not be deleted, as they will be automatically recreated.

​

Import events from Snowflake

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

• Access to Event Streams for outbound message events (Plan limitations and overages apply)
• Access to Custom Events for inbound event syncing (Plan limitations and overages apply)
• 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

-- 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;

-- 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;

-- 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;

-- 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;

​

Event data mapping

​

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

-- 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

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

​

Network Security

​

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

​

FAQ

​

Which authentication method should I use?

​

Can I use an existing warehouse?

​

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