In today’s digital landscape, leveraging cloud and low-code services to streamline data integration processes is crucial for maintaining efficiency and keeping development teams engaged. For organizations using Sitecore CDP (Customer Data Platform), seamlessly importing data from various sources is an essential component of Composable Integration.

With a number of options available for ETL , ranging from cloud solutions to SaaS products like Workato, the choice of integration tools often depends on factors such as latency (batch, real-time, near-realtime), expertise in specific ETL technology, scalability/flexibility, and cost. Among these options, cloud-based ETL solutions typically offer the lowest integration cost and the highest flexibility and scale. In this blog post, I’ll review the integration of Sitecore CDP and BatchAPI using Azure ADF and Logic app.

The process includes four activities in Azure Data Factory or Synapse Analytics and one verification step in a Logic App. While some may prefer using a Logic App, the advanced and low-code capabilities of Synapse make a strong case for utilizing Azure’s ETL features.

Create and Compress JSON File (1)

Start by compressing your JSON file into a Gzip format. I used Azure Function for flexibility, but data flow can be used in more generic situations.

Prepare File Metadata(2)

Collect the necessary metadata for your file and format it according to Sitecore CDP requirements. MD5, hex transformed along with file is needed to complete the import. I used the Get Metadata Activity and custom Azure function to perform translation from MD5 to hex

  const buffer = Buffer.from(md5, 'base64');
    /**
     * The hexadecimal string representation of the decoded base64 string.
     * @type {string}
     */
    const bufString = buffer.toString('hex');

Generate Upload URL(3)

Obtain the URL for the batch upload location. Use Web Activity to perform this without writing a one line of code.

Upload the File(4)

Upload the Gzipped JSON file to the designated location using the generated URL. Unfortuantly, as of today, there is no way to include a file in web activity, custom function with few lines of code can solve this easily.


    var request = require('request');
    const requestPromise = util.promisify(request);
    var options = {
        'method': 'PUT',
        'url': url,
        'headers': {
            'content-md5': md5,
            'x-amz-server-side-encryption': 'AES256',
            'Content-Type': 'text/plain'
        },
        body: downloaded
    };

    try {
        const response = await requestPromise(options);
        console.log(response.body);
        context.res = {
            body: response.body
        };
    } catch (error) {
        throw new Error(error.body);
    }

Perform Status Checks using Logic app(5)

Use the Azure Logic app to retrieve the current upload status of the batch file you started uploading.

You can find non-production code samples used in this blog post here: : https://github.com/brodbor/sc_cdp_npm.