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;

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

WHERE EXTERNAL_ID = '12345'::VARCHAR

CREATE VIEW your_schema.message_events_combined AS
SELECT * FROM (
  SELECT
    EVENT_ID,
    EVENT_KIND,
    EVENT_TIMESTAMP,
    SUBSCRIPTION_ID,
    LANGUAGE,
    SUBSCRIPTION_TIMEZONE,
    SUBSCRIPTION_DEVICE_TYPE,
    ONESIGNAL_ID,
    EXTERNAL_ID,
    MESSAGE_ID,
    MESSAGE_NAME,
    MESSAGE_TITLE,
    MESSAGE_CONTENTS,
    ROW_NUMBER() OVER (PARTITION BY EVENT_ID ORDER BY EVENT_TIMESTAMP DESC) AS row_num
  FROM (
    -- New integration data
    SELECT
      EVENT_ID,
      EVENT_KIND,
      EVENT_TIMESTAMP,
      SUBSCRIPTION_ID,
      LANGUAGE,
      SUBSCRIPTION_TIMEZONE,
      SUBSCRIPTION_DEVICE_TYPE,
      ONESIGNAL_ID,
      EXTERNAL_ID,
      MESSAGE_ID,
      MESSAGE_NAME,
      MESSAGE_TITLE,
      MESSAGE_CONTENTS
    FROM <new-database>.<new-schema>.MESSAGE_EVENTS

    UNION ALL

    -- Legacy backup data (aliased to match new column names)
    SELECT
      EVENT_ID,
      EVENT_KIND,
      EVENT_IMPRESSION_TIMESTAMP AS EVENT_TIMESTAMP,
      SUBSCRIPTION_ID,
      SUBSCRIPTION_LANGUAGE AS LANGUAGE,
      SUBSCRIPTION_TIMEZONE,
      SUBSCRIPTION_DEVICE_TYPE,
      ONESIGNAL_ID,
      EXTERNAL_ID,
      MESSAGE_ID,
      MESSAGE_NAME,
      MESSAGE_TITLE,
      MESSAGE_BODY AS MESSAGE_CONTENTS
    FROM <legacy-backup-database>.<legacy-backup-schema>.MESSAGE_EVENTS
  )
)
WHERE row_num = 1;

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

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

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