Microsoft Tools choice for row by row processing

Question

Microsoft Tools choice for row by row processing

na 146

We have a business process in which we must process the records one by one.

get the record from a database.
check it exists in another system via api.
if the record exists, do an update via an api.
if the record does not exist, do an insert, via api.

We are doing this using data factory and it works, but it feels cumbersome. Its more suited to simpler movements of lots of data.
We considered and tried logic apps, but agian, they feel convoluted and can become complex and cumbersome very quickly. they are also not very fast.

Are azure functions a better fit for such a pipeline?
Ideally we would do this in python, and simply run a python notebook but i know of no such facility in azure outside of databricks, which again, needs ADF.

MartinJaffer-MSFT 26,161 Reputation points

2022-08-03T20:18:52.413+00:00
(continuation)

It would help if you could share the circumstances.

Does it run on a schedule or on-demand?

How much data / how long would one session run?

How frequently would this run?
MartinJaffer-MSFT 26,161 Reputation points

2022-08-09T15:53:46.753+00:00

Hello @na ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help . If my replies answered your question, please mark as accepted answer. ( I converted from comment to answer post type)

Thanks
Martin

1 answer

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2022-08-03T20:18:52.413+00:00

(continuation)

It would help if you could share the circumstances.

Does it run on a schedule or on-demand?

How much data / how long would one session run?

How frequently would this run?
MartinJaffer-MSFT 26,161 Reputation points

2022-08-09T15:53:46.753+00:00

Hello @na ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help . If my replies answered your question, please mark as accepted answer. ( I converted from comment to answer post type)

Thanks
Martin

Answer 1

MartinJaffer-MSFT 26,161

Hello @na and welcome to Microsoft Q&A.

As I understand, you are looking for a platform/service to run Python, fetch records from a database, and for each record fetched, interact with an api.

You have not mentioned which flavor of database (Azure SQL, on-prem SQL, noSQL, Cosmos, oracle, etc.). Some services have inbuilt integrations with certain database types.

You have mentioned Databricks, but I question whether it really needs ADF. I am mostly confident you can do your entire ask in Databricks alone, without ADF. Databricks does have some database integrations.

Azure Synapse also runs Python, and has integrations with multiple azure database products. Depending upon your database, it might even be hosted in Synapse! Synapse is like ADF and Databricks put together with other stuff too.

If you want something more bare-bones, you could try Azure Batch. Or even get your own VM. For those you bring your own code, and other stuff.

As to whether to use Azure Functions, I do have some concerns. Mainly about how long you expect the execution to take and which trigger method. There are some timeout durations to worry about, mostly on Consumption plan and http trigger. Durable functions may help. Azure Functions is outside my area of expertise, so it may still be valid options.

I'm sure there are other options as well.

na 146 Reputation points

2022-08-04T08:37:46.653+00:00

My circumstances are that i have to submit the most recently updated records to an API, to update the data in another system.
The API can only take one record at a time so its a sequential pipeline, i.e. if there are 20 records, the pipeline has to run 20 times.
Each run could have anything up to 1000 records to process.

ADF can do this, but does not feel a good fit. its more for heavy lifting of lots of data.
I only mention Databricks as it runs python notebooks and i think python notebooks would be ideal for this, however as with ADF, Databricks is overkill for this task.
Similar with synapse, its complete overkill for what could be a simple script that needs to be run on a regular basis.
Durable functions may be a better solution. They are a lot more complicated however than a python notebook.

The source is a combination of sql server on prem and sql azure.
MartinJaffer-MSFT 26,161 Reputation points

2022-08-04T17:43:41.407+00:00

This is true, @na . Over tha past couple seasons I have found ADF is just not the right tool for large volumes of small api calls.

I guess this next deciding factor, is the grouping.

Azure Functions is great if you make 1 call to Azure Functions for 1 record. Not so much if you pass all 1000 records to Azure Functions in one call, and have Azure Functions iterate over them. For this latter case, I'd recommend Azure Batch or some other compute service I'm not familiar with. I'll ask my colleagues for recommendations.

There is another option you may want to look into. API Management. A colleague suggested it when another customer has a somewhat similar ask as yours. I'm not sure whether API management can talk to SQL's, but it is good for doing anything API.

To confirm, you are doing scheduled batches, not streaming or on-demand jobs?

Share via

Microsoft Tools choice for row by row processing

1 answer

Your answer