MotherDuck

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

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)

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

Setup

Create MotherDuck service token

Generate an access token for OneSignal to connect to MotherDuck:

Log in to the MotherDuck Web UI at app.motherduck.com
Click your profile in the top-left corner
Navigate to Settings > General > Access Tokens
Click Create Token
Set expiration date (or leave unlimited)
Copy the generated service token

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

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

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

First steps & settings

Message channels

Journeys

OneSignal AI

Audience & events

Analytics & Integrations

Privacy & Security

MotherDuck

Overview

Requirements

MotherDuck

Setup

Event data mapping

Example Event Query

Processing Modes

Table Mode

SQL Query Mode

Limitations

FAQ

How do I optimize query performance in MotherDuck?

Can I sync from multiple MotherDuck databases?

​Overview

​Requirements

​MotherDuck

​Setup

​Event data mapping

​Example Event Query

​Processing Modes

​Table Mode

​SQL Query Mode

​Limitations

​FAQ

​How do I optimize query performance in MotherDuck?

​Can I sync from multiple MotherDuck databases?

Overview

Requirements

MotherDuck

Setup

Event data mapping

Example Event Query

Processing Modes

Table Mode

SQL Query Mode

Limitations

FAQ

How do I optimize query performance in MotherDuck?

Can I sync from multiple MotherDuck databases?