Create a Sync from Clickhouse to Batch Profile Attributes

Before you start

To create a ClickHouse → Batch sync, you'll need:

• Access to the Batch dashboard
• A ClickHouse table or view containing one row per profile
• Your ClickHouse credentials (Host, Port, Database, Username, Password)
• A table (or view) that follows the Cloud Sync input format (see below)

---

1) Prepare your ClickHouse table or view

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

1. A profile identifier (to know which profile to update)
2. A cursor field (to know what changed since the last run)
3. Any number of attribute columns (sent to Batch as profile attributes)

---

1.1 One row per profile

Your source must contain one row per profile. Each row is interpreted as an update to a single Batch profile.

---

1.2 Required columns

Your table (or view) must include:

Column	Required	Description
custom_id	✅	The profile identifier in Batch
last_updated_at	✅	Cursor used for incremental sync

last_updated_at must be updated every time any synced attribute changes, otherwise updates may not be picked up by the next run.

---

1.3 Attribute naming rules (ClickHouse-compatible)

Cloud Sync reads ClickHouse columns and converts them into Batch profile attributes.

ClickHouse column names do not support characters like $, (, or ). Cloud Sync relies on prefixes in column names to represent typed or native fields.

Supported prefixes

Prefix	Meaning	Example
date__	Date attribute	date__birthday
url__	URL attribute	url__avatar
batch__	Native profile fields (instead of $...)	batch__email_address

---

1.4 Handling arrays

Array attributes must be passed as a String column containing a stringified JSON array. Native ClickHouse Array() types are not supported.

'["value1","value2","value3"]'

Example:

SELECT
  user_id                          AS custom_id,
  updated_at                       AS last_updated_at,
  '["art","design"]'               AS interests  -- String, not Array(String)
FROM raw.users;

---

1.5 Example schema (e-commerce)

CREATE TABLE customer_data.batch_profiles (
    custom_id            String,
    last_updated_at      DateTime64(3, 'UTC'),

    -- Native profile fields
    batch__email_address String,

    -- Standard attributes
    plan                 String,
    country              String,
    lifetime_value       Float64,
    is_vip               UInt8,

    -- Array attribute (stringified, not Array(String))
    interests            String,   -- e.g. '["art","design"]'

    -- Typed attributes
    url__avatar          String,
    date__birthday       String,
    date__last_purchase  String
) ENGINE = MergeTree()
ORDER BY (custom_id);

How this maps in Batch:

• custom_id identifies the profile
• batch__email_address updates the profile's native email field
• plan, country, lifetime_value, is_vip become standard attributes
• interests is interpreted as an array attribute (must be a stringified JSON array)
• url__avatar is interpreted as a URL attribute
• date__birthday and date__last_purchase are interpreted as date attributes

---

1.6 Using a View

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

CREATE OR REPLACE VIEW customer_data.batch_profiles_view AS
SELECT
  toString(u.user_id)                              AS custom_id,
  greatest(u.updated_at, o.last_order_updated_at)  AS last_updated_at,

  u.email                                          AS batch__email_address,
  u.country,
  u.plan,

  u.avatar_url                                     AS url__avatar,
  formatDateTime(u.birth_date, '%Y-%m-%d')         AS date__birthday,

  o.last_order_date                                AS date__last_purchase,
  o.total_spent                                    AS lifetime_value,
  u.is_vip,

  -- Stringify arrays before passing to Cloud Sync
  toJSONString(u.interests)                        AS interests
FROM raw.users u
LEFT JOIN raw.user_orders_summary o
  ON u.user_id = o.user_id;

This approach lets you:

• rename fields with the correct prefixes (batch__, date__, url__)
• compute a reliable last_updated_at
• stringify array columns correctly
• ensure you always expose one row per profile

---

1.7 Handling nulls

If a column value is NULL, Batch interprets it as attribute removal for that profile.

If you don't want an attribute removed:

• ensure your view returns a non-null value, or
• exclude the column from the sync entirely.

---

1.8 Attributes limits and constraints

All attributes sent through Cloud Sync must respect the same limits and constraints as the Batch Profile API. See Profile API documentation, the attributes object.

---

2) 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 ClickHouse as the source

---

2.1 Configure your ClickHouse connection

Enter the following fields:

Field	Description
Host	Your ClickHouse server hostname (e.g. your-host.example.com)
Port	ClickHouse HTTP(S) port — defaults to 8123
Database	The database containing your source table/view (default: default)
Username	Your ClickHouse username
Password	Your ClickHouse password
SSL	Toggle on to use a secure connection (recommended for production)
Table	The table or view name

Batch validates the connection before continuing.

---

2.2 Configure profile mapping

Cloud Sync applies a simple mapping model:

• custom_id → identifies which Batch profile to update
• all other columns → mapped to profile attributes
• last_updated_at → used only for incremental sync logic

---

3) 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 changed since the last successful sync.

---

3.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.

---

3.2 Inserts, updates, and deletes

Incremental syncs naturally capture:

• ✅ inserts
• ✅ updates

They do not automatically capture:

• ❌ deletes

If you need deletions reflected in Batch, rely on a different pipeline or implement soft deletes by setting all attributes to null in the ClickHouse view when a profile is deleted.

---

3.3 Best practices for reliable incremental syncs

To avoid missing changes:

• Ensure last_updated_at updates every time a synced column changes
• Avoid timestamps that only reflect partial updates
• Use a View if you need computed fields or type conversions
• Use ReplacingMergeTree and order by last_updated_at for large tables

---

4) Test and enable your Sync

Before enabling the schedule:

1. Run a test sync
2. Verify:

   • Profiles are created or updated correctly
   • batch__, date__, and url__ fields are interpreted correctly
   • Array attributes are correctly stringified (e.g. ["art","design"])
   • Null values behave as expected (null → attribute removal)

Once enabled, Batch automatically handles batching and retries.