Post/Put huge volume of data to an api endpoint through ADF

Hrishikesh K M 0 Reputation points
2026-10-16T07:34:29.6466667+00:00

I have few output files in my unity catalog volumes/adls gen2. I want to push the data of these files as a body for api endpoint. However the data from my table is huge which goes upto 2 million rows, which is then converted to the below formatted json file:

dataload:[

{

"Metadata": {

            "FinishExtract": true,

            "Timestamp": "20261016.124853",

            "OperationType": "Full"

           }

   },

   {

          Data: {

                    "Rows": [

                                 {

                                         "Values":[

                                                     "a",

                                                     "b",... "c"..,d,e,f,g,h,i]},

                                 {

                                          "Values":[j,k,l,m,n,o,p,q,r]}}} and so on.

this is a sample, I have total of 9 fields. The API only accepts this format as a downstream. Now, I want to understand what would be the right approach to send it in batches. The API can have 20 request and 0.5 million rows per request. The only option for me is to use ADF due to data restrictions.

I tried/explored the below approaches:

  1. Lookup + web (works, but size limit)
  2. Copy activity (content type conflict as I have to pass application/json;charset=UTF-8. I tried multiple options but nothing worked)
  3. Dataflows - Due to complex json, it was not able to format.
  4. Azure batch jobs/Azure functions/logic apps - Access restrictions, but this approach can be used if there's a workaround to solve the issue.
  5. Databricks job/notebooks - Access restricted, but this approach can be used if nothing else works.
  6. Databricks job + Web activity+ web activity - size limit.

I need my first request to go as a post request followed by put request. I can control the size and volumes my json files being generated. However, I can restrict the total volumes. I am looking for a scalable design.

Appreciate the help in advance!

Thank you.

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rukshan edirisinghe 2,365 Reputation points
    2026-10-23T03:55:39.4833333+00:00

    Hi @Hrishikesh K M

    The catch here is the wrapper. ADF's REST sink sends data as a plain JSON array of records ([ {..}, {..} ]). It can reshape each record with mapping, but it can't add your outer dataload / Metadata / Data.Rows envelope. So the Copy activity alone won't produce the format your API needs.

    Since the data already sits in Unity Catalog / ADLS, I'd build the batches where the data is and let ADF orchestrate:

    1. In a Databricks notebook, read the table and split it into chunks of 500,000 rows or fewer (for example by row number ranges).
    2. For each chunk, build the exact body your API expects (Metadata block plus Data.Rows with the Values arrays) and POST it with requests. Use gzip if the API accepts it, since 500k rows is a large payload.
    3. Send the chunks one after another, with a short delay, and log the response for each batch so a failed batch can be re-sent alone.
    4. In ADF, call that notebook with a Databricks Notebook activity, passing the table name and batch size as parameters.

    With 2 million rows that's only 4 requests, well within the 20-request limit.

    Does the API expect the Metadata block in every request, or only in the first one?

    If this approach works for your load, please consider accepting it as the answer. If the API rejects the batched bodies, let me know and I'll be happy to keep helping.

    Reference:

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.