Create a Sync from MSSQL to Batch Profile Events

Before you start

To create an MSSQL → Batch Profile Events sync, you'll need:

• Access to the Batch dashboard

• An MSSQL-compatible database (Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric Warehouse, or SQL Server 2012+) containing one row per event

• Credentials to connect: either a username/password or a Microsoft Entra ID service principal (Client ID + Client Secret)

• A table (or view) that follows the Cloud Sync events input format (see below)

---

1) Prepare your MSSQL table or view

Cloud Sync expects your MSSQL source (table or view) to include:

1. A profile identifier (to know which profile the event belongs to)

2. An event name (the type of event being recorded)

3. A cursor field (to know which rows are new since the last run)

4. Any number of event attribute columns (sent to Batch as event properties)

---

1.1 One row per event

Your source must contain one row per event. Unlike profile attribute syncs, multiple rows can share the same custom_id — each row creates a separate event on the corresponding profile.

---

1.2 Required columns

Your table (or view) must include the following columns:

Column	Required	Description
custom_id	✅	The identifier of the profile the event belongs to
event_name	✅	The name of the event (e.g. add_to_cart, purchase)
last_updated_at	✅	Cursor used for incremental sync

last_updated_at must be set at the time the event row is inserted and must not change afterwards. It is used only to determine which rows to fetch on the next sync run, not as the event timestamp.

---

1.3 Optional columns

Column	Description
event_time	The timestamp of the event, in RFC 3339 UTC format (e.g. 2026-04-17T04:25:00Z). If omitted, Batch uses the time of reception.

---

1.4 Event attribute naming rules

SQL column names cannot contain characters like $, (, or ). Cloud Sync relies on prefixes in column names to represent typed event attributes.

Supported prefixes

Prefix	Meaning	Example
date__	Date attribute	date__purchased_at
url__	URL attribute	url__item_url

Any column without a prefix is sent to Batch as a standard string, number, or boolean attribute.

---

1.5 Handling objects

Object event attributes must be stored as a JSON string in your MSSQL column. The connector parses the string and sends it in the correct format to the Profile API.

'{"color":"red","size":"M"}'

Example using FOR JSON PATH:

SELECT
    user_id                                      AS custom_id,
    'purchase'                                   AS event_name,
    inserted_at                                  AS last_updated_at,
    (SELECT brand, size FROM product_details
     WHERE id = p.id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product_details
FROM raw.purchases p;

---

1.6 Example schema (e-commerce)

CREATE TABLE events_data.batch_events (
    custom_id          NVARCHAR(255),
    event_name         NVARCHAR(255),
    last_updated_at    DATETIME2,

    -- Optional event fields
    event_time         NVARCHAR(30),         -- RFC 3339 UTC, e.g. '2026-04-17T04:25:00Z'

    -- Standard event attributes
    item               NVARCHAR(255),
    quantity           INT,
    price              FLOAT,

    -- Typed event attributes
    url__item_url      NVARCHAR(2048),        -- URL attribute
    date__purchased_at NVARCHAR(10),          -- date attribute

    -- Object event attribute (stringified JSON)
    product_details    NVARCHAR(MAX)          -- e.g. '{"brand":"Nike","size":"42"}'
);

How this maps in Batch:

• custom_id identifies the profile the event is attached to

• event_name sets the event type

• event_time sets the event timestamp (falls back to reception time if omitted)

• item, quantity, price become standard event attributes

• url__item_url is interpreted as a URL attribute

• date__purchased_at is interpreted as a date attribute

• product_details is interpreted as an object attribute

---

1.7 Using a View

If your raw event table doesn't match the expected naming or format, create an MSSQL View that converts your schema into the correct conventions.

CREATE OR ALTER VIEW events_data.batch_events_view AS
SELECT
    CAST(e.user_id AS NVARCHAR(255))                             AS custom_id,
    e.event_type                                                  AS event_name,
    e.inserted_at                                                 AS last_updated_at,

    FORMAT(e.occurred_at AT TIME ZONE 'UTC', 'yyyy-MM-ddTHH:mm:ssZ') AS event_time,

    e.item_name                                                   AS item,
    e.qty                                                         AS quantity,
    e.unit_price                                                  AS price,

    e.item_url                                                    AS url__item_url,
    CONVERT(NVARCHAR(10), e.purchase_date, 23)                    AS date__purchased_at,

    (SELECT e.brand, e.size FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product_details
FROM raw.events e;

This approach lets you:

• rename fields with the correct prefixes (date__, url__)

• set a reliable last_updated_at based on insertion time

• format event_time to RFC 3339 UTC

• stringify object columns correctly

---

1.8 Handling nulls

If an optional column value is NULL, that attribute is simply omitted from the event. It does not affect other attributes on the same event or on the profile.

---

2) Set up database access

Batch connects to your MSSQL database using either username/password or Microsoft Entra ID (recommended for Azure SQL).

Option A — Username / Password

Create a dedicated read-only user in your database and grant it SELECT access on the relevant table or view:

CREATE LOGIN batch_sync WITH PASSWORD = 'YourStrongPassword!';
CREATE USER batch_sync FOR LOGIN batch_sync;
GRANT SELECT ON events_data.batch_events_view TO batch_sync;

Option B — Microsoft Entra ID (recommended for Azure SQL)

This method uses a service principal (App Registration) to authenticate without storing a password.

2.1 Create an App Registration in Entra ID

1. Go to entra.microsoft.com

2. Navigate to Entra ID → App registrations → New registration

3. Give it a name (e.g. Batch Cloud Sync) and register it

4. Note the Application (Client) ID — you'll need it later

5. Go to Certificates & secrets → New client secret

6. Note the Value of the client secret — it is only shown once

2.2 Create a database user for the service principal

In your Azure SQL Database, run the following as an admin:

CREATE USER [Batch Cloud Sync] FROM EXTERNAL PROVIDER;
GRANT SELECT ON events_data.batch_events_view TO [Batch Cloud Sync];

The user name in brackets must match exactly the display name of your App Registration in Entra ID.

---

3) Create the Sync in the Batch dashboard

Cloud Sync is configured from the dashboard via a dedicated Sync module.

1. Open the Batch dashboard

2. Go to Data → Cloud Sync

3. Click Create Sync

4. Select MSSQL as the source

---

3.1 Configure your MSSQL connection

Enter:

• Host — your server hostname (e.g. myserver.database.windows.net for Azure SQL, or <workspace>.datawarehouse.fabric.microsoft.com for Microsoft Fabric Warehouse)

• Port — default is 1433

• Database — the name of your database

• Schema — the schema containing your table or view

• Table or View — the name of the source table or view

Then fill in your credentials:

• Username / Password if using SQL authentication, or

• Entra ID Client ID and Entra ID Client Secret if using Microsoft Entra ID

When using Microsoft Entra ID authentication, an encrypted connection is required. Make sure your server accepts encrypted connections.

Batch validates the connection before continuing.

---

3.2 Select the destination

In the Destination dropdown, select Batch > Profile events.

---

3.3 Configure event mapping

Cloud Sync applies a simple mapping model:

• custom_id → identifies which Batch profile to attach the event to

• event_name → sets the event type

• last_updated_at → used only for incremental sync logic

• all other columns → mapped to event attributes

---

4) How incremental sync works

Cloud Sync uses incremental processing, which means it does not re-import your full dataset at every run. Instead, it fetches only the rows that are new since the last successful sync.

---

4.1 The last_updated_at cursor

Batch stores the last successful cursor value internally.

At each run, Batch fetches only rows where:

• last_updated_at is greater than the last stored cursor

This makes sync runs faster, more scalable, and more cost-efficient.

For events, last_updated_at should reflect when the row was inserted into the table, not when the event occurred (event_time). Do not backfill or modify last_updated_at after insertion.

---

4.2 Inserts and deletes

Incremental syncs capture:

• ✅ new event rows (inserts)

They do not capture:

• ❌ deletions — events sent to Batch are immutable; they cannot be removed via Cloud Sync

---

4.3 Best practices for reliable incremental syncs

To avoid missing events:

• Set last_updated_at at insertion time and never modify it afterwards

• Use a View if you need to rename columns or apply type conversions

• Index on last_updated_at for large tables

---

5) Test and enable your Sync

Before enabling the schedule:

1. Run a test sync

2. Verify:

   • Events are created on the correct profiles

   • event_time is set correctly (or defaults to reception time as expected)

   • date__ and url__ fields are interpreted correctly

   • Object attributes are correctly stringified

Once enabled, Batch automatically handles batching and retries.