Skip to content

Syncing account data using CDI

Learn how to sync your Braze account data using CDI.

Prerequisites

Before you can sync your account data using CDI, you’ll need to configure your accounts schema.

How syncing works

  • Each sync imports rows where UPDATED_AT is later than the last synced timestamp.
  • Data from the integration creates or updates accounts based on the provided id.
  • If DELETED is true, the account is deleted.
  • Syncing doesn’t consume data points, but all synced data counts toward your total accounts usage, measured by total stored data—there’s no need to limit to only changed data.
  • Fields not in your accounts schema are dropped; update the schema before syncing new fields.

Syncing your account data

You can sync your account data using CDI through a data warehouse or a file storage.

To integrate your data source with your data warehouse:

  1. Create a source table in Snowflake. Use the names in the example or choose your own database, schema, and table names. You can also use a view or materialized view instead of a table. 
    1
2
3
4
5
6
7
8
9
10
11
12
13

     CREATE DATABASE BRAZE_CLOUD_PRODUCTION;
 CREATE SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION;
 CREATE OR REPLACE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC (
      UPDATED_AT TIMESTAMP_NTZ(9) NOT NULL DEFAULT SYSDATE(),
      --ID of the account to be created or updated
      ID VARCHAR(16777216) NOT NULL,
      --Name of the account to be created or updated
      NAME VARCHAR(16777216) NOT NULL,
      --Account fields and values that should be added or updated
      PAYLOAD VARCHAR(16777216) NOT NULL,
      --The account associated with this ID should be deleted
      DELETED BOOLEAN
 );
  2. Create a role, warehouse, and user, and grant permissions. If you already have credentials from another sync, you can reuse them—just make sure they have access to the accounts table. 
    1
2
3
4
5
6
7
8
9
10
11

     CREATE ROLE BRAZE_INGESTION_ROLE;

 GRANT USAGE ON DATABASE BRAZE_CLOUD_PRODUCTION TO ROLE BRAZE_INGESTION_ROLE;
 GRANT USAGE ON SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION TO ROLE BRAZE_INGESTION_ROLE;
 GRANT SELECT ON TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC TO ROLE BRAZE_INGESTION_ROLE;

 CREATE WAREHOUSE BRAZE_INGESTION_WAREHOUSE;
 GRANT USAGE ON WAREHOUSE BRAZE_INGESTION_WAREHOUSE TO ROLE BRAZE_INGESTION_ROLE;

 CREATE USER BRAZE_INGESTION_USER;
 GRANT ROLE BRAZE_INGESTION_ROLE TO USER BRAZE_INGESTION_USER;
  3. If you use network policies, allowlist the Braze IPs so the CDI service can connect. For the list of IPs, see Cloud Data Ingestion.
  4. In the Braze dashboard, go to Data Settings > Cloud Data Ingestion and create a new sync.
  5. Enter connection details (or reuse existing ones), then add the source table.
  6. Select the Accounts sync type, then enter the integration name and schedule.
  7. Choose the sync frequency.
  8. Add the public key from the dashboard to the user you created. This requires a user with SECURITYADMIN access or higher in Snowflake.
  9. Select Test Connection to confirm the setup.
  10. When you’re finished, save the sync.
  1. Create a source table in Redshift. Use the names in the example or choose your own database, schema, and table names. You can also use a view or materialized view instead of a table. 
    1
2
3
4
5
6
7
8
9
10
11
12
13

     CREATE DATABASE BRAZE_CLOUD_PRODUCTION;
 CREATE SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION;
 CREATE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC (
    updated_at timestamptz default sysdate not null,
    --ID of the account to be created or updated
    id varchar not null,
    --Name of the account to be created or updated
    name varchar not null,
    --Account fields and values that should be added or updated
    payload varchar(max),
    --The account associated with this ID should be deleted
    deleted boolean
 )

  2. Create a user and grant permissions. If you already have credentials from another sync, you can reuse them—just make sure they have access to the accounts table.

    1
2
3

     CREATE USER braze_user PASSWORD '{password}';
 GRANT USAGE ON SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION to braze_user;
 GRANT SELECT ON TABLE ACCOUNTS_SYNC TO braze_user;
  3. If you have a firewall or network policies, allow Braze access to your Redshift instance. For the list of IPs, see Cloud Data Ingestion.
  1. (Optional) Create a new project or dataset for your source table. 
    1

     CREATE SCHEMA BRAZE-CLOUD-PRODUCTION.INGESTION;
  2. Create the source table for your CDI integration: 
    1
2
3
4
5
6
7
8

     CREATE TABLE `BRAZE-CLOUD-PRODUCTION.INGESTION.ACCOUNTS_SYNC`
 (
   updated_at TIMESTAMP DEFAULT current_timestamp,
   id STRING,
   name STRING,
   payload JSON,
   deleted BOOLEAN
 );

    Refer to the following when creating your source table:

  1. Create a user and grant permissions. If you already have credentials from another sync, you can reuse them as long as they have access to the accounts table.

    After granting permissions, generate a JSON key. See Keys create and delete for instructions. You’ll upload it in the Braze dashboard later.

  1. If you use network policies, allow Braze IPs to access your BigQuery instance. For the list of IPs, see Cloud Data Ingestion.
  1. Create a catalog or schema for your source table. 
    1

     CREATE SCHEMA BRAZE-CLOUD-PRODUCTION.INGESTION;
  2. Create the source table for your CDI integration: 
    1
2
3
4
5
6
7
8

     CREATE TABLE `BRAZE-CLOUD-PRODUCTION.INGESTION.ACCOUNTS_SYNC`
 (
   updated_at TIMESTAMP DEFAULT current_timestamp(),
   id STRING,
   name STRING,
   payload STRING, STRUCT, or MAP,
   deleted BOOLEAN
 );

    Refer to the following when creating your source table:

  1. Create a personal access token in Databricks:
    1. Select your username, then select User Settings.
    2. On the Access tokens tab, select Generate new token.
    3. Add a comment to identify the token, such as “Braze CDI”.
    4. Leave Lifetime (days) blank for no expiration, then select Generate.
    5. Copy and save the token securely for use in the Braze dashboard.
  1. If you use network policies, allow Braze IPs to access your Databricks instance. For the list of IPs, see Cloud Data Ingestion.
  1. Create one or more tables for your CDI integration with these fields: 
    1
2
3
4
5
6
7
8
9

     CREATE OR ALTER TABLE [warehouse].[schema].[CDI_table_name] 
 (
   UPDATED_AT DATETIME2(6) NOT NULL,
   PAYLOAD VARCHAR NOT NULL,
   ID VARCHAR NOT NULL,
   NAME VARCHAR NOT NULL,
   DELETED BIT
 )
 GO
  1. Create a service principal and grant permissions. If you already have credentials from another sync, you can reuse them—just make sure they have access to the accounts table.
  1. If you use network policies, allow Braze IPs to access your Microsoft Fabric instance. For the list of IPs, see Cloud Data Ingestion.

To sync account data from file storage, create a source file with the following fields.

The following examples show valid JSON and CSV formats for syncing account data from file storage.

1
2
3
4

{"id":"s3-qa-0","name":"account0","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}"}
{"id":"s3-qa-1","name":"account1","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}","deleted":true}
{"id":"s3-qa-2","name":"account2","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}","deleted":false}
{"id":"s3-qa-3","name":"account3","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}"}

1
2
3

ID,NAME,PAYLOAD,DELETED
85,"ACCOUNT_1","{""region"": ""APAC"", ""employees"": 850}",TRUE 
1,"ACCOUNT_2","{""region"": ""EMEA"", ""employees"": 10000}",FALSE

1
2
3

ID,NAME,PAYLOAD
85,"ACCOUNT_1","{""region"": ""APAC"", ""employees"": 850}"
1,"ACCOUNT_2","{""region"": ""EMEA"", ""employees"": 10000}"

Creating a sync view

Creating a sync view in your data warehouse lets the source refresh automatically without needing to rewrite additional queries.

For example, if you have a table of account data called account_details_1 with account_id, account_name, and three additional attributes, you could create a sync view like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE VIEW BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS 
SELECT
    CURRENT_TIMESTAMP as UPDATED_AT,
    account_id as id,
    account_name as name,
    TO_JSON(
        OBJECT_CONSTRUCT (
            'attribute_1',
            attribute_1,
            'attribute_2',
            attribute_2,
            'attribute_3',
            attribute_3)
    )as PAYLOAD FROM "account_details_1";

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS
SELECT
    CURRENT_TIMESTAMP as UPDATED_AT,
    account_id as id,
    account_name as name,
    JSON_SERIALIZE(
        OBJECT (
            'attribute_1',
            attribute_1,
            'attribute_2',
            attribute_2,
            'attribute_3',
            attribute_3)
    ) as PAYLOAD FROM "account_details_1";

1
2
3
4
5
6
7
8
9
10
11
12

CREATE view IF NOT EXISTS BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS (SELECT
    last_updated as UPDATED_AT,
    account_id as ID,
    account_name as NAME,
    TO_JSON(
      STRUCT(
      attribute_1,
      attribute_2,
      attribute_3,
      )
    ) as PAYLOAD 
  FROM `BRAZE_CLOUD_PRODUCTION.INGESTION.account_details_1`);

1
2
3
4
5
6
7
8
9
10
11
12

CREATE view IF NOT EXISTS BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS (SELECT
    last_updated as UPDATED_AT,
    account_id as ID,
    account_name as NAME,
    TO_JSON(
      STRUCT(
      attribute_1,
      attribute_2,
      attribute_3,
      )
    ) as PAYLOAD 
  FROM `BRAZE_CLOUD_PRODUCTION.INGESTION.account_details_1`);

1
2
3
4
5
6
7
8

CREATE VIEW [BRAZE_CLOUD_PRODUCTION].[INGESTION].[ACCOUNTS_SYNC]
AS SELECT 
    account_id as ID,
    account_name as NAME,
    CURRENT_TIMESTAMP as UPDATED_AT,
    JSON_OBJECT('attribute_1':attribute_1, 'attribute_2':attribute_2, 'attribute_3':attribute_3, 'attribute_4':attribute_4) as PAYLOAD

FROM [braze].[account_details_1] ;
Github   Edit this page on GitHub
HOW HELPFUL WAS THIS PAGE?
New Stuff!