Articles in this section

Connecting to Snowflake natively with MessageGears

Gail Des Jardin
Updated

Overview

Snowflake is a cloud SQL database service that can store large amounts of customer data for use with high-performance applications. Snowflake gives you scalable access to your data anywhere, anytime, secured with global governance policies. If you are ready for more advanced work and features using Snowflake, a number of their official guides can be found at the following link.

MessageGears Accelerator enables you to connect directly to Snowflake so that you can use customer data from the data warehouse to create audiences quickly and easily. Instantly access your customer data and activate it without any friction, data latency, or the burden of making copies of your data. Whether you're a data admin who is a pro at SQL, or you're a marketer who wants to drag-and-drop recipient attributes, you can segment recipient data and target them in real-time with personalized campaigns. Because we connect natively to Snowflake, the data will always be fresh and secure. You can even write the campaign details back to Snowflake using Audience Recording. For more information, see Creating a New Audience Recording.

To begin activating your data, you will need to establish a connection to Snowflake. The following steps will be key to get you up and running:

  1. Create a New Database, Schema, and Warehouse for Customer Data
  2. Create MessageGears Role and Apply the Necessary Permissions
  3. Create a MessageGears Analytics Schema for Event Feed Data
  4. Create Snowflake Connection in Accelerator

1: Create a New Database, Schema, and Warehouse for Customer Data

You can use the following SQL commands as examples:

USE ROLE SYSADMIN;  -- Built-in Snowflake admin role

CREATE DATABASE IF NOT EXISTS IDENTIFIER(CustomerData);

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER(CustomerData_WH)
WITH WAREHOUSE_SIZE = 'LARGE' -- Be sure to modify this as necessary for your environment's needs
AUTO_SUSPEND = 300 -- Suspend after 5 minutes of inactivity
AUTO_RESUME = TRUE; -- Auto resume when activity starts again

USE DATABASE CustomerData; -- Created above
CREATE SCHEMA MessageGears;

2: Create MessageGears Role and Apply the Necessary Permissions

Next, you will need to create the Snowflake user and role for the connection with the proper access to the Snowflake Data Warehouse. You can use an existing user and role, but for the purposes of this guide, we will be creating a new one by going through the following steps:

  1. Log in to your Snowflake account with a user that is assigned administrative privileges to manage users and roles.
  2. Open a worksheet and run the following commands to create the new role, apply permissions, and create the new user:
    USE ROLE SECURITYADMIN; -- Built-in Snowflake admin role

    SET New_Role = 'MessageGears_Role'
    SET New_User = 'MessageGears_User'
    SET New_DB = 'CustomerData' -- Created in step 1 above
    SET New_WH = 'CustomerData_WH' -- Created in step 1 above
    SET New_Schema = 'MessageGears' -- Created in step 1 above

    CREATE ROLE IF NOT EXISTS IDENTIFIER($New_Role);

    GRANT ALL
     ON WAREHOUSE IDENTIFIER($New_WH)
     TO ROLE IDENTIFIER($New_Role);

    GRANT USAGE
     ON DATABASE IDENTIFIER($New_DB)
     TO ROLE IDENTIFIER($New_Role);

    GRANT CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE PIPE, CREATE PROCEDURE, CREATE FUNCTION, MODIFY, USAGE
     ON ALL SCHEMAS IN DATABASE IDENTIFIER($New_DB)
     TO ROLE IDENTIFIER($New_Role);

    GRANT USAGE 
     ON FUTURE SCHEMAS IN DATABASE IDENTIFIER($New_DB) 
     TO ROLE IDENTIFIER($New_Role);

    GRANT SELECT 
     ON FUTURE TABLES IN SCHEMA IDENTIFIER($New_Schema)
     TO ROLE IDENTIFIER($New_Role);

    GRANT SELECT 
     ON FUTURE VIEWS IN SCHEMA IDENTIFIER($New_Schema)
     TO ROLE IDENTIFIER($New_Role);

    CREATE USER IF NOT EXISTS IDENTIFIER($New_User) password='EnterPasswordHere';

    GRANT ROLE IDENTIFIER($New_Role) TO USER IDENTIFIER($New_User);

3: Create a MessageGears Analytics Schema for Event Feed Data

In a new or existing worksheet, you will need to run the following commands to create a schema and set of tables to store event feed analytics data coming from MessageGears:

USE DATABASE CustomerData; -- Created in step 1 above
CREATE SCHEMA MessageGears_Events;
USE SCHEMA MessageGears_Events;
  
CREATE TABLE messagegears_bounce (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 local_ip varchar(255) DEFAULT NULL,
 remote_ip varchar(255) DEFAULT NULL,
 category varchar(255) DEFAULT NULL,
 category_code number(11,0) DEFAULT NULL,
 details string DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_click (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 ip_address varchar(255) DEFAULT NULL,
 user_agent varchar(510) DEFAULT NULL,
 microsite boolean DEFAULT FALSE,
 url string DEFAULT NULL,
 url_name varchar(510) DEFAULT NULL,
 url_id varchar(510) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_delivery (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 local_ip varchar(255) DEFAULT NULL,
 remote_ip varchar(255) DEFAULT NULL,
 message_size number(11,0) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_open (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 ip_address varchar(255) DEFAULT NULL,
 user_agent varchar(510) DEFAULT NULL,
 microsite boolean DEFAULT FALSE,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_spamcomplaint (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 isp varchar(255) DEFAULT NULL,
 ip_address varchar(30) DEFAULT NULL,
 subject varchar(510) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_unsubscribe (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 ip_address varchar(255) DEFAULT NULL,
 user_agent varchar(510) DEFAULT NULL,
 microsite boolean DEFAULT FALSE,
 PRIMARY KEY (activity_id)
);

-- Note: No Primary Key on Render Errors
CREATE TABLE messagegears_render_error (
 account_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 email_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 error_code varchar(255) DEFAULT NULL,
 error_message string DEFAULT NULL
);

-- PUSH EVENT TABLES
CREATE TABLE messagegears_push_delivery (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 device_id varchar(255) DEFAULT NULL,
 service varchar(255) DEFAULT NULL,
 application_id varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 push_delivery_service varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_bounce (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 device_id varchar(255) DEFAULT NULL,
 service varchar(255) DEFAULT NULL,
 application_id varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 category varchar(255) DEFAULT NULL,
 details varchar DEFAULT NULL,
 push_delivery_service varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_render_error (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 device_id varchar(255) DEFAULT NULL,
 service varchar(255) DEFAULT NULL,
 application_id varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 error_code varchar(255) DEFAULT NULL,
 error_message varchar DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

-- SMS EVENT TABLES
CREATE TABLE messagegears_sms_delivery (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 sms_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 origination_number varchar(255) DEFAULT NULL,
 sms_name varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_bounce (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 sms_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 category varchar(255) DEFAULT NULL,
 details varchar DEFAULT NULL,
 origination_number varchar(255) DEFAULT NULL,
 sms_name varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_render_error (
 account_id number(11,0) DEFAULT NULL,
 activity_id varchar(36) NOT NULL,
 request_id varchar(50) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 correlation_id varchar(255) DEFAULT NULL,
 sms_address varchar(255) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_version number(11,0) DEFAULT NULL,
 error_code varchar(255) DEFAULT NULL,
 error_message varchar DEFAULT NULL,
 origination_number varchar(255) DEFAULT NULL,
 sms_name varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_inbound (
 activity_id varchar(36) NOT NULL,
 activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 sms_address varchar(255) DEFAULT NULL,
 message_type varchar(36) NOT NULL,
 message varchar DEFAULT NULL,
 to_address varchar(255) DEFAULT NULL,
 origination_number varchar(255) DEFAULT NULL,
 sms_name varchar(255) DEFAULT NULL,
 PRIMARY KEY (activity_id)
);

-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE messagegears_campaign_metadata (
 job_id number(11,0) DEFAULT NULL,
 request_id varchar(50) NOT NULL,
 account_id number(11,0) DEFAULT NULL,
 start_time timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 campaign_id number(11,0) DEFAULT NULL,
 campaign_name varchar(255) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 template_id number(11,0) DEFAULT NULL,
 template_name varchar(255) DEFAULT NULL,
 PRIMARY KEY (job_id)
);
-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE messagegears_send_history (
 job_id number(11,0) DEFAULT NULL,
 recipient_id varchar(255) DEFAULT NULL,
 campaign_id number(11,0) DEFAULT NULL,
 job_category varchar(255) DEFAULT NULL,
 send_time timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

4: Create Snowflake Connection in Accelerator

Next, you are going to need to connect a Snowflake Connection in Accelerator. MessageGears' campaign management software integrates directly with Snowflake, and this integration allows users to access their Snowflake data natively with our audience builder, content personalization, and orchestration features without replication, data mapping, or synchronization.

The following are the last three high-level steps required to get a Snowflake Native Connection up and running:

    1. Create your Cloud File Storage Name
    2. Create a Snowflake Stage in the console
    3. Create the Database Connection

For more information on this process, see our help center article, Snowflake Native Connection.

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

Comments

0 comments

Please sign in to leave a comment.