How to connect Batch to Snowflake?

Here's a user guide on how to install and use Batch's Snowflake Native app to synchronize your Snowflake data with Batch, for segmentation, personalization, and orchestration purposes.

Batch Data Sync Snowflake Native app

Batch leverages Snowflake's Native app framework to provide a ready to install Snowflake app that

allows you to sync your customer data from Snowflake into Batch in no time:

Securely manage connections to your Batch projects using Project Keys and REST API Keys.
Create and configure synchronization tasks to send data from your Snowflake dynamic tables to Batch.
Monitor and manage the status of your active data syncs, with options to suspend and resume them as needed.

This guide will walk you through each feature of the application.

Important requirement Snowpark Container Services need to be available on your Snowflake account for you to be able to use this app.

👉 To have access to Batch's Native app on your Snowflake account, please reach out to your Customer Success Manager or contact support@batch.com.

1. Install and launch the Native app

These steps need to be performed by a Snowflake Administrator (ACCOUNTADMIN role).

Once the app has been shared with your Snowflake account, you'll be able to find it in your installed apps by navigating to Data products > Apps from your Snowsight console.

When the app is launched for the first time, you'll need to authorize the following privileges needed for the app to wrok properly:

Step 1: Account privileges

1 → CREATE COMPUTE POOL : This is required by the native app to run Job services triggered by scheduled tasks. These jobs transform your Snowflake data and send it to Batch using Batch APIs.

2 → EXECUTE TASK : This is required for the native app to synchronize updates to your Snowflake data in a scheduled recurring manner with Batch.

3 → Click on the "Grant" button to grant both these privileges.

Step 2: External Access Integration

To synchronize data with Batch, the Native app needs to be able to make external calls to Batch's APIs (api.batch.com:443). This is done through an External Access Integration.

You can first click on the "Review" button to display additional information on the required external access:

Once that is done, simply click on "Connect" button.

Now, all that is left to do is clicking on the "Activate" button that appears on the top right corner of the screen. You should then be redirected to an information page through which your can launch the app.

2. Manage Batch Projects

The first step is to register your Batch projects with the application. This securely stores your credentials, allowing the app to send data to the correct Batch project.

1 → Go to the "Manage Batch Projects" tab and then the "Add a New Batch Project" section

2 → Enter your Batch Project Key

3 → Enter your Batch REST API Key

4 → Click the "Add Project" button.

The application can connect to multiple Batch projects. The maximum number of projects you can add is limited to 4 by default.

Once projects are added, they are listed at the top of the tab and can be deleted via the "Delete project" button.

3. Create a synchronization Task

This section allows you to create a task that automatically synchronizes data from a Snowflake dynamic table to one of your registered Batch projects.

3.1. Prerequisites

Prior to creating a synchronization task, you'll need to make sure the following requirements are met:

A. Identify the data you want to synchronize and store it in a dynamic table

Start by deciding which Profile attributes you want to synchronise from Snowflake to Batch.

To make sure the data is available for syncing, create a Dynamic table in Snowflake containing the data you want to send.

Using a Dynamic table will make sure all changes and inserts on your base tables are dynamically being applied to your dynamic table which will serve as an input for the Batch Native app.

Here is an example of how you can create your Dynamic table:

CREATE OR REPLACE DYNAMIC TABLE batch_sync_table 
TARGET_LAG = '60 minutes'
WAREHOUSE = MY_WAREHOUSE
AS   
-- Your SQL query here

Important:

Your table must contain a User ID column to be used as a mapping key with Batch’s Profile base. This must be the same identifier you use as Batch’s Custom User ID.
Batch’s Native app uses Batch’s Profile API to send profile data. Thus, your table data must comply with the APIs requirements (e.g. attribute max length, etc). Non compliant data will be ignored during synchronization.
Columns containing DATES or URLS must be prefixed by date_ or url_ respectively. If not, these fields will be synchronized as String attributes.

B. Grant the app with the required permissions 🔐

Snowflake Native apps do not have default access to the consumer's account objects. Thus, to allow Batch's Native app to manipulate the required objects for synchronization, the following permissions need to be granted by an Administrator:

USAGE on the Warehouse you want the app to run the synchronization tasks in
SELECT on the Dynamic table containing the data you want to synchronize with Batch
USAGE on both the Database and Schema in which the Dynamic table is stored

Here is how you can grant these rights:

GRANT USAGE ON WAREHOUSE [YOUR WAREHOUSE] TO APPLICATION BATCH_SYNC_NATIVE_APP;
GRANT USAGE ON database [YOUR DATABASE] TO APPLICATION BATCH_SYNC_NATIVE_APP;
GRANT USAGE ON schema [YOUR SCHEMA] TO APPLICATION BATCH_SYNC_NATIVE_APP;
GRANT SELECT ON dynamic table [YOUR DYNAMIC TABLE] to APPLICATION BATCH_SYNC_NATIVE_APP;

3.2. Create a new synchronization task

This allows you to create a background task that is scheduled to run periodically (every 24 hours by default) which will make sure all updates (inserts/deletes/edits) on your source Dynamic table are sent to Batch.

1 → Go to the "Create a Sync task" tab.

2 → Fill in the Warehouse Name: The name of the Snowflake warehouse that the task will use to run. This warehouse must exist and the application must have USAGE permission on it.

3 → Fill in the Project Key: The Batch Project Key for the project you want to send data to. This must match one of the projects you added in the "Manage Batch Projects" tab.

4 → Fill in the Dynamic Table Name: The fully qualified name of the source dynamic table in Snowflake (e.g., MY_DATABASE.MY_SCHEMA.BATCH_PROFILE_DATA). The application will create a stream on this table to capture new and updated rows.

5 → Fill in the Identifier Column name: The name of the column in your dynamic table that contains the user identifier (e.g., CUSTOM_ID). This is used to match users in Batch so it must be the same identifier used in your Batch Profile base.

6 → After filling in all the fields, click the "Activate data synchronization" button.

If a task with the exact same configuration (Project Key and Dynamic Table) already exists but is paused, the app will update and resume it for you.

4. Manage Synchronization Tasks

This tab provides a view to monitor and control all your active and paused synchronization tasks.

1 → Go to the "Manage Sync Tasks" tab.

For each task, the following information is displayed:

Task Name: The unique name of the task in Snowflake.
Project & Table: The associated Batch Project Key and the source dynamic table used by the task.
Created: The timestamp when the task was created.
Status: The current state of the task (STARTED or SUSPENDED)
Suspended Date: If the task is suspended, this shows when it was paused.

2 → Click the "Resume" button to resume a paused task: The task's status will change to STARTED

3 → Click the "Suspend" button to pause a running task: The task's status will change to SUSPENDED.

4 → Click the "Refresh Tasks" button to see the most up-to-date view of existing tasks

The application enforces a limit on the number of concurrently running tasks that is set to 2 by default.

5. Test and monitor the workflow

Monitor Snowflake tasks and logs for execution status and debug issues if needed.

You can use Batch’s Profile View to verify that the customer data has reached its destination, on the basis of a customer ID: How to find a user's profile on the dashboard?

You’re now ready to use your data to send personalized and engaging messages to your audience!

PreviousHow to connect Batch to Piano Composer NextMarketing Platforms

Last updated 1 month ago

Was this helpful?