Introduction
Snowflake is a data platform that allows organizations to store, analyze, and process large volumes of structured and semi-structured data in a highly scalable and efficient manner. With its unique architecture, Snowflake allows organizations to consolidate their data, perform quick analytics, and gain valuable data-driven insights accessible to all users.
MoEngage <> Snowflake
The MoEngage <> Snowflake integration allows you to set up a direct connection between your Snowflake instance and MoEngage app to sync data on a recurring basis. You can define a schedule to run the sync periodically - syncs can be as frequently as every hour or as infrequently as once per month. When a sync runs, MoEngage will directly connect to your data warehouse instance, retrieve all new data from the specified table, and update the corresponding data on your MoEngage dashboard.
Benefits
Reduce integration time
- No more searching for the right ETL tool, as MoEngage directly integrates with Snowflake.
- Long and complicated ETL pipelines are now replaced with a one-time integration setup that gives MoEngage direct access to your data without actual data flow.
- This decreases the dependency on tech teams significantly.
Faster data processing
- The power of the Snowflake infrastructure enables us to store, process, and query massive amounts of data in near real-time.
- Any changes in the original schema are propagated immediately without having to change any configuration on MoEngage’s end, which is a significant advantage over the traditional ETL pipelines.
- Since there is no need for ETL tools and external cloud providers, the cost of import is significantly lower than traditional data pipelines.
Integration use-cases
- Keep your users in MoEngage up-to-date with the user data in your Snowflake account.
- Import event data from your Snowflake account directly into MoEngage.
- Import users into MoEngage and retarget them via custom segments.
Integration
library_add_check |
Prerequisites Ensure you have a Snowflake account with enough permissions to create new users, schemas or tables. |
Set up a new Snowflake Connection
In order to import or export from Snowflake, you will first need to connect MoEngage to your Snowflake data warehouse. To create a new Snowflake Connection:
- On your MoEngage Dashboard, go to the App Marketplace.
- Search for "Snowflake".
- Go to the "Integrate" tab, and click "+ Add Connection".
- Provide your Snowflake data warehouse details:
Field | Description | Example |
Connection Name | Give this connection an identifiable name. | My Snowflake Prod |
Snowflake Account Identifier |
The account identifier of your Snowflake account.
If you're using Snowsight, you can find click on your account name at the bottom of the left nav. Select the account you want to import from and then click on the “copy” icon to copy the Account Identifier. For up-to-date information, you can visit Snowflake docs. |
Format should be: ACCOUNT_IDENTIFIER.snowflakecomputing.com |
Warehouse |
The name of the warehouse MoEngage will use to execute the queries. |
COMPUTE_WAREHOUSE |
Database |
The database that MoEngage should use to read the tables from. You can pick the schema while setting up imports/exports. |
MY_DEMO_DB |
Username |
Either an existing database username with the read access or create a new one for MoEngage. |
|
Authentication Method |
MoEngage can connect to your Snowflake Instance using two methods:
MoEngage also supports the rotating of keys. If your IT Policy requires to rotate the keys, you can do it by following these steps:
|
|
Role |
This role will be used in executing queries from MoEngage. |
- You can click Test connection to test your details or directly click on Save connection to proceed.
Once you have set up a Snowflake Connection, you can use that to set up various imports and exports in MoEngage.
If your Snowflake instances aren't public and are on AWS, we recommend you set up AWS Private Link with MoEngage AWS account. Please reach out to your account manager for more information on this.
Import users and events from Snowflake into MoEngage
Read our Snowflake Imports guide to set up MoEngage <> Snowflake imports for your account.
Export events from MoEngage to Snowflake
Step 1: Make sure your user's assigned role has required permissions
MoEngage will require WRITE
access to your database so that we can write data into your Snowflake database. 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 SCHEMA <YOUR_DATABASE_NAME>.<YOUR_SCHEMA_NAME> TO ROLE MOENGAGE_ROLE;
-- Let the user create a table in your schema
GRANT CREATE TABLE ON SCHEMA <YOUR_DATABASE_NAME>.<YOUR_SCHEMA_NAME> TO ROLE MOENGAGE_ROLE;
-- Let the user insert data in your tables
GRANT INSERT, SELECT ON ALL TABLES IN SCHEMA <YOUR_DATABASE_NAME>.<YOUR_SCHEMA_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:
-
<YOUR_WAREHOUSE_NAME>
- The name of the warehouse you want MoEngage to use while executing queries. -
<YOUR_DATABASE_NAME>
- The name of the database you want to import data from. -
<YOUR_PASSWORD>
- Give a strong password to the newly created database user.
Step 2: Create a new export in the App Marketplace
info |
Information
|
On your MoEngage dashboard, navigate to App Marketplace >> Snowflake.
-
-
- Click Integrate >> Add Integration
- Select your Snowflake connection.
- Select which schema MoEngage will use to create new tables and export your events into.
- Select the events and additional user properties you want to export to Snowflake.
- Click "Save Export".
-
MoEngage will start dumping events to the newly created table every hour.
Sample Table Structure
MoEngage will create the following two tables in your schema:
-
test_connection_moengage
- We will use this table to verify if your connection details are valid from time to time. -
moe_events_<table_creation_timestamp>
- We will dump (append) all your events in this table.
The overall structure of the table will remain fixed for all types of events. Any new user property or event attribute that is exported will be part of the user_attributes
or event_attributes
column respectively. All the events will be dumped in a single table containing all necessary information such as event names, event times, etc for easy analysis.
Field | Type | NL |
---|---|---|
app_name | VARCHAR | NN |
event_name | VARCHAR | NN |
event_code | VARCHAR | NN |
event_uuid | VARCHAR | NN |
event_time | TIMESTAMP_NTZ | NN |
event_type | VARCHAR | NN |
event_source | VARCHAR | NN |
uid | VARCHAR | NULL |
user_attributes_moengage_user_id | VARCHAR | NULL |
device_attributes_moengage_device_id | VARCHAR | NULL |
push_id | VARCHAR | NULL |
email_address | VARCHAR | NULL |
mobile_number | VARCHAR | NULL |
event_attributes_app_version | VARCHAR | NULL |
event_attributes_sdk_version | VARCHAR | NULL |
event_attributes_platform | VARCHAR | NULL |
event_attributes_campaign_id | VARCHAR | NULL |
event_attributes_campaign_name | VARCHAR | NULL |
event_attributes_campaign_type | VARCHAR | NULL |
event_attributes_campaign_channel | VARCHAR | NULL |
event_attributes_readable_campaign_id | VARCHAR | NULL |
event_attributes_parent_campaign_id | VARCHAR | NULL |
event_attributes_parent_flow_id | VARCHAR | NULL |
event_attributes_parent_flow_name | VARCHAR | NULL |
event_attributes_variation_id | VARCHAR | NULL |
event_attributes_locale_id | VARCHAR | NULL |
event_attributes_locale_name | VARCHAR | NULL |
event_attributes_url | VARCHAR | NULL |
event_attributes_timestamp | TIMESTAMP_NTZ | NULL |
event_attributes_first_session | BOOLEAN | NULL |
event_attributes_logged_in_status | VARCHAR | NULL |
event_attributes | VARIANT | NULL |
user_attributes | VARIANT | NULL |
device_attributes | VARIANT | NULL |
Export Frequency
We will dump the data at hourly intervals.