Thank you for posting query in Microsoft stack overflow Platform.

You are observing unexpected behaviour in the number of records returned by the REST API when calling it through Azure Data Factory (ADF), especially with larger pagination sizes. Specifically:

When using size=100, the user gets 102 or 103 records in the sink (instead of 100 as expected).

When using size=75 or size=50, the user gets the exact count (75 or 50) in the sink, as expected.

This inconsistency in the number of records returned is causing issues with downstream pipeline logic

Following are possibilities that may cause the issue

API response -

The API might include additional metadata or wrapper information (such as pagination information, total record count, etc.) within the response body. For example, a single API call might return 100 records and 2 additional metadata records, causing the total to be 102. The same happens for size=200, returning 204 records.

Cache Sink Behaviour –

Cache Sink might be writing extra records when writing results to memory. This might be due to: -

If ADF retrieves partial records from the API and stores them in the cache, the total count might increase unexpectedly.

If there is also an alignment problem with the data flow as handled by the ADF (e.g., the schema is not correct or the partitioning does not correct), the records might get written repeatedly, hence duplicates.

In addition to the above updates, I would encourage you to check below link where different ways of pagination explained with detailed steps. Kindly choose any approach from there which better fits for you.

• Azure Data Factory pagination rule (for Rest API) - Workaround

• Copy data from REST API which sends response in Pages using Azure data factory - It's a video which explains complete steps detailly.

• Pagination support in Copy activity in Azure data factory - It's an official documentation where different ways of pagination explained with examples.

Regards,
Vrishabh