Costs of foreach activity with a lot of small copy activities

Question

Costs of foreach activity with a lot of small copy activities

Niels Deckers 21

Hi all,

I am currently working on a pipeline that gets data from a REST API.
First I get certains ID's with a LookUp and then I use those ID's in a foreach activity to do all the GET requests with a copy activity.
something like: https://api-base-url.nl/product/[ID]
For these simple calls that return small amounts of JSON data I get 228 activity runs and 3,76 DIU/hours.
This is pretty expensive when I think about how this runs in like 1 minute when I do it with Python locally.
I already downgraded the DIU to 2 in the settings of the copy activity, but I still feel like this operation is more expensive than it should be.
Does anybody have any tips on how to cut costs for this pipeline? thanks in advance!

Accepted answer

0 additional answers

Your answer

Answer 1

MartinJaffer-MSFT 26,156

Hello @Niels Deckers and welcome to Microsoft Q&A.

While Data Factory may be good for moving large amounts of data, it is not necessarily the best when doing many many tiny calls, as you have found.

Out of curiosity, why have you chosen to run this in Data Factory, when you had a Python script to do the same?

I think we can make a hybrid solution, part Data Factory and part Python. The Custom Activity leverages Azure Batch compute. This can run Python among other things, and can be fed Datasets, or perhaps the output of the Lookup Activity. I'm not 100% certain, but I think this single activity would have less overhead than spinning up 228 separate activity runs.

Does this appeal to you?

Niels Deckers 21 Reputation points

2022-05-11T08:10:00.117+00:00

Hi Martin,

Thanks for your reply, i'll look into it!
The Python script is in my local environment and I want to automate these API calls 3 times per day for which I want to use Azure.
MartinJaffer-MSFT 26,156 Reputation points

2022-05-16T17:49:20.91+00:00
Thank you for letting me know this was helpful @Niels Deckers .
I have converted my comment to answer, so you may mark as accepted answer, if this solved your issue.
Please do let me if you have any queries.

Thanks
Martin

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Niels Deckers 21 Reputation points

2022-05-23T08:53:53.177+00:00

Hi @MartinJaffer-MSFT ,
Just to be sure regarding the hybrid solution.
Would it be best to execute the API-calls within the Python script, create an appended data frame and write the results as a JSON back to my storage account.
Then use the copy activity to do JUST ONE insert of the JSON from the Python script into my SQL-database?

Would this also be possible with an Azure Function?

Thanks for your great help! ;)
MartinJaffer-MSFT 26,156 Reputation points

2022-06-07T17:22:02.947+00:00

Yes to both of those @Niels Deckers

Share via

Costs of foreach activity with a lot of small copy activities

0 additional answers

Your answer