Skip to main content

​
Overview

The OneSignal + MotherDuck integration enables automatic syncing of custom events from your MotherDuck databases to OneSignal to trigger automated messaging campaigns and Journeys based on user behavior. MotherDuck is a DuckDB-in-the-cloud service that provides fast OLAP (Online Analytical Processing) capabilities with the simplicity of SQL.

​
Requirements

​
MotherDuck

  • MotherDuck account with database access
  • Service token for authentication
  • Database containing event data
  • Tables or views with structured event information

​
Setup

1

Create MotherDuck service token

Generate an access token for OneSignal to connect to MotherDuck:
  1. Log in to the MotherDuck Web UI at app.motherduck.com
  2. Click your profile in the top-left corner
  3. Navigate to Settings > General > Access Tokens
  4. Click Create Token
  5. Set expiration date (or leave unlimited)
  6. Copy the generated service token
2

Prepare your event data

Ensure your MotherDuck database contains properly structured event tables:
-- Example event table structure
CREATE TABLE user_events (
    event_name VARCHAR,
    user_id VARCHAR,
    event_timestamp TIMESTAMP,
    event_properties JSON,
    session_id VARCHAR
);
3

Connect to OneSignal

In OneSignal, go to Data > Integrations and click Add Integration.Select MotherDuck and provide:
  • Service Token: Token from Step 1
  • Database Name: Your MotherDuck database name
  • Connection String: md:your_database_name
4

Configure data sync

Select the tables or write custom SQL queries to define which event data to sync:
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL 7 DAYS

​
Event data mapping

Map your to OneSignal’s custom events format:
OneSignal FieldDescriptionRequired
nameevent_nameEvent identifierYes
external_iduser_idUser identifierYes
timestampevent_timestampWhen event occurredNo
propertiesevent_dataNo

​
Example Event Query

-- Optimized event query for OneSignal sync
SELECT
    event_name,
    user_id,
    event_timestamp,
    {
        'source': 'motherduck',
        'session_id': session_id,
        'device_type': device_type,
        'value': event_value
    }::JSON as event_properties
FROM analytics.user_events
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL 1 DAY
ORDER BY event_timestamp DESC

​
Processing Modes

​
Table Mode

Sync entire tables directly from your MotherDuck database. OneSignal will automatically map columns to event fields.

​
SQL Query Mode

Write custom DuckDB SQL queries to transform and filter your event data: 
-- Advanced event aggregation
SELECT
    'daily_summary' as event_name,
    user_id,
    DATE_TRUNC('day', event_timestamp) as event_timestamp,
    {
        'total_events': COUNT(*),
        'unique_sessions': COUNT(DISTINCT session_id),
        'last_activity': MAX(event_timestamp)
    }::JSON as event_properties
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY user_id, DATE_TRUNC('day', event_timestamp)

​
Limitations

  • Query complexity affects sync performance
  • Large result sets may impact sync speed
  • JSON parsing requires proper column typing

​
FAQ

​
How do I optimize query performance in MotherDuck?

Use DuckDB’s columnar storage advantages by selecting only needed columns and applying filters early in your queries.

​
Can I sync from multiple MotherDuck databases?

Yes, you can create separate integrations for each MotherDuck database in your account.