Snowflake Imports

Overview

MoEngage allows customers to import users and events through tables stored inside your Snowflake databases.

Types of Imports

MoEngage can import the following from your Snowflake data warehouse:

  1. Registered Users: These are users who are already registered on MoEngage.
  2. Anonymous Users: These are users who are not yet registered on MoEngage.
  3. Events (Standard and User Defined): MoEngage can import standard events like Campaign Interaction Events as well as your own user-defined events.

Preparing the Data

MoEngage does not need any particular table schema or view to be prepared for imports. All the columns in your table/view can be either skipped or mapped separately on the MoEngage Dashboard. However, there are a few things to be taken care of before setting up the imports.

User Imports Event Imports

When you set up periodic User Imports on MoEngage, we will only sync the data that has changed since the last sync was run. MoEngage uses a timestamp column called updated_at to identify which row has changed since the previous sync. You are free to name this column as you like provided it contains the timestamp of the instant when the row's data was last changed. If you're calling this column by a different name in your table, you can always configure this mapping separately on the MoEngage Dashboard.

Required Access Permissions

MoEngage will require READ access to your database so that we can pull data into MoEngage. You can either grant these permissions to an existing database user or choose to create a new dedicated database user for MoEngage:

-- Create a role for the MoEngage user
CREATE ROLE MOENGAGE_ROLE;
-- Allow the  user to run queries in the warehouse
GRANT USAGE ON WAREHOUSE <YOUR_WAREHOUSE_NAME> TO ROLE MOENGAGE_ROLE;
-- Let the  user see this database
GRANT USAGE ON DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the  user see all schemas in this database
GRANT USAGE ON ALL SCHEMAS IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the  user read all existing tables in this schema
GRANT SELECT ON ALL TABLES IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the  user read all future tables in this schema
GRANT SELECT ON FUTURE TABLES IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the  user read all existing views in this schema
GRANT SELECT ON ALL VIEWS IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the  user read all future views in this database
GRANT SELECT ON FUTURE VIEWS IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;


-- Create the MoEngage user (MOENGAGE_USER is username here)
CREATE USER MOENGAGE_USER WITH DEFAULT_ROLE = MOENGAGE_ROLE DEFAULT_WAREHOUSE = <YOUR_WAREHOUSE_NAME> PASSWORD = '<YOUR_PASSWORD>';
-- Grant the role to the user
GRANT ROLE MOENGAGE_ROLE TO USER MOENGAGE_USER;

Remember to change the following values:

  1. <YOUR_WAREHOUSE_NAME> - The name of the warehouse you want MoEngage to use while executing queries.
  2. <YOUR_DATABASE_NAME> - The name of the database you want to import data from.
  3. <YOUR_PASSWORD> - Give a strong password to the newly created database user.

Set Up Imports from Snowflake

info

Prerequisites

Make sure that you have an existing Snowflake connection setup in the App Marketplace with relevant permissions.

You can set up Snowflake Imports from the MoEngage Dashboard >> Segment >> Imports page.

ImportsPage.png

Clicking on "+Import" button will allow you to select your source. Click on Snowflake to create a new import for either Users (Registered/Anonymous can be picked in the next step) or Events.

Step 1: Select Your Snowflake Connection and Table Source

Import Name

Enter a name for this import to easily identify it on the Imports Dashboard. Based on the type of import selected, your next steps might vary:

arrow_drop_down User Imports

You will now have the option to select whether you want to import Registered users or Anonymous users. You can also choose to import both together:

UserImportsStep1-1.png

arrow_drop_down Event Imports

For Event imports, you will now have the option to select which event it is that you want to import:

EventImportsStep1-1.png

You can either select an existing event or create a new one. If you have multiple events stored in a single table, MoEngage will look at the "Event Name" column to determine matching rows to import. The value of the "Event Name" column will be determined by the event you pick. Some of these events might have different Display Names and Event Names. You can always go to the Data Management page and view the Event Information. For example, the event "App/Site Opened" should have the value of the "Event Name" column as MOE_APP_OPENED:

You can also create a new event by clicking on the "+ Create event" option at the end of the dropdown:

CreateNewEvent.png

Give your event a name that is unique. By default, your Display Name will be the same as the Event Name. You can go to the Data Management page to view or edit this event and also your other MoEngage events. Do note that any new events you create now will only be available in your app after the first successful import.

Import Source

In this first step, "Source and format", you will need to tell MoEngage which Snowflake connection to use and the table to import from.

To get started, select a Snowflake connection to use for this Import. If you have not already created a Snowflake connection, you can click on the "+ Add connection"  option at the end of the dropdown and you will be taken to the App Marketplace to set it up. You can learn more about how to connect your Snowflake warehouse to MoEngage here.

Once you have selected your Snowflake connection, you will be required to select the schema and table to import from. If there is an error loading your schemas, ensure you have given MoEngage sufficient permissions as mentioned above.

Event Imports

In addition to the above steps, MoEngage provides additional support for tables containing multiple events. If your table contains multiple events, you need to first "Preview" the table and then select the "Table contains multiple events" option:

MoEngage will use the values of the "Event name" column to filter out rows that need to be imported. We will import only those rows that match the selected event name. You can mark an existing column of your table as the column containing the event name. When you select this column and Preview again, we will show you the filtered rows for you to confirm before proceeding:

After you preview your table, you can move on to the next step.

Step 2: Map Your Columns to MoEngage Attributes

In this step, you need to map the columns of your table to the attributes present in MoEngage. All your columns will be shown one below the other:

  1. Column name - This is the column name to be mapped. Below the column name, we also show a sample value (picked from the first row of the fetched table in the previous step) for your reference.
  2. Map attribute - Here you need to pick which MoEngage attribute you want to map the table column to. You can also choose to create a new attribute. Some attributes support ingestion from multiple data types, so you need to pick the data type of the column as well. For the "DateTime" columns, you also need to pick the format.
  3. More actions - You can optionally choose to skip the column. The skipped column will not be imported.

Depending on the type of import, there are a few mandatory mappings required:

arrow_drop_down User Imports
Registered Users Anonymous Users All Users
Mapping Description
User ID Your table should mandatorily have a column that contains a unique user identifier (that identifies a user’s account in your system).
Updated at MoEngage will use this column to determine which rows have been added/updated since the last sync. You can select whether this timestamp is in UTC Timezone or App Timezone.
arrow_drop_down Event Imports
Mapping Description
User ID This column will be used to match the users in MoEngage to your events.
Event time You need to map the column that contains the timestamp of when the event occurred. You can select whether this timestamp is in UTC Timezone or App Timezone. If you have chosen UTC, then the "Event Time" of the imported event will be converted to the timezone chosen in your MoEngage Dashboard settings:AppTimezone.png

Once a mandatory mapping is marked, it will reflect against the column name in the mapping table and you will no longer be able to mark the column as skippable.

Just like new events, you can also create a new attribute from the dropdown by clicking on "+ Create attribute":

CreateNewAttribute.png

Give your attribute a name and select its Data type. You can edit this as well as existing attributes from the Data Management page. Do note that any new attributes you create now will only be available in your app after the first successful import.

Manifest Files

Optionally, you can choose to auto-map these columns by uploading a manifest file

To upload a manifest file, click the "Upload mapping file" option on the top-right of the mapping table:

UploadMapping.png

Upload your manifest file and click Done. Your mappings will be auto-configured accordingly. Any columns with non-MoEngage attributes will be left blank and can either manually map the column or create a new attribute for it. Make sure your manifest file follows the expected conventions as mentioned.

Any additional columns that are present in your Manifest File but not there in your table will be ignored. Also, if the mapping for an existing table column is not present in the manifest file, MoEngage will keep the mapping blank for you to manually configure it.

Do note that if a column in the manifest file is mapped to a non-existent MoEngage Attribute, then the mapping will be blank and you will need to first manually create a new attribute from the UI and then map it.

Save as a Segment

If you're importing users, you can also add them to a custom segment inside MoEngage. Each time the sync is run, the imported users will continue to be added to this custom segment. We will not delete any users from this segment. You can then send targeted campaigns to this segment. To save your imported users in a custom segment, enable the "Save as a custom segment" option:

Give your Segment a name and select the Identifier column in your table.

Import Behaviour

In the case of User Imports, you can also choose to update existing users only. This is helpful when you want to bulk update users' attributes in MoEngage without creating any new users. To enable this, check the "Update existing users only" option under Import Behaviour:

Sending Import Notifications

ImportStatus.png

You can, optionally, choose to get notified about your imports' statuses via email. Select up to 10 emails to send the status emails to. You will receive an email about the following events:

  1. An import was created
  2. An import was successful
  3. An import failed

When you are satisfied with all mappings, click Next. If there are any errors, correct them to move forward.

Step 3: Select the Import Frequency

frequency import.png

In this step, you need to define when to sync with your tables. We support the following types of imports:

  1. One-Time imports: You can either choose to run the import as soon as possible or at a later date and time (scheduled). All existing rows that match the import criteria will be imported.
  2. Periodic: You can choose to run your imports every few minutes, hourly, daily, weekly, or monthly, or with intervals and advanced configurations.

Optionally, you can specify whether the import should end after a specified set of occurrences, or at a particular date. Click Done when ready.

warning

Warning

Please note that the first time the import runs, we will import all the matching rows from your table. On every subsequent import, we will pull in only the changed rows.

Duplicate Imports

You can only configure one unique import at a time. An import is considered a duplicated import when all of these are the same:

  1. Import Type - Users / Events
  2. Import Sub-Type - Event Name / Registered / Anonymous / All users - If the event name is the same, or there already exists a registered/anonymous/all users import with the same Snowflake connection
  3. Snowflake connection
  4. Import schema & table

As long as any one of the above is different, the import is considered unique.

Was this article helpful?
0 out of 0 found this helpful

How can we improve this article?