Complex Data Flow

Question

Complex Data Flow

maliadalar 1

I have a employee table. I would like to track address changes and other changes(phone, email, etc.)

I would like to run API call if address change to find address property(lets say find new address zip code) and also update if other fields are changed as well.

How can I develop my dataflow so it can only calls API when address changes and updates fields when other changes happen?

Please consider that API is costly to run every time a single change happen to a row even it is not the address itself.

MartinJaffer-MSFT 26,161 Reputation points

2022-07-27T17:11:28.573+00:00

Hello @maliadalar and welcome to Microsoft Q&A.

As I understand, the ask is about how to only make calls to api when data has changed. Specifically, using Dataflow.

Before jumping to implementation, there is a question which must be asked:

If I am to only make calls to your api when there is changed data, how do I know when data has changed? I need to know more about your setup.

The api you want to limit calls to -- is it the data source, you are fetching data from to find out changes? Or is it a sink?
Is there another data source involved?
maliadalar 1 Reputation point

2022-07-28T14:27:48.527+00:00

Sure,

I have source table(OLTP) and sink table(OLAP) that I compare if there is a change in the data (address, phone, email) I tried to make my dataflow as hashing these fields and compare the hashes between two table and get only the one that has different value. However I stuck in this part because I need to track two changes separately. One is address itself and second is phone, email which does not require API call. If I do hash for all of them, API will be called even for phone or email changes in my data which is not efficient.

I tried two separate hash but cannot figure out how to sink at the same time.

I hope this explanation would help.

thanks,
MartinJaffer-MSFT 26,161 Reputation points

2022-08-23T19:04:36.897+00:00

Hello @maliadalar ,
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 .

Thanks
Martin

1 answer

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2022-07-27T17:11:28.573+00:00

Hello @maliadalar and welcome to Microsoft Q&A.

As I understand, the ask is about how to only make calls to api when data has changed. Specifically, using Dataflow.

Before jumping to implementation, there is a question which must be asked:

If I am to only make calls to your api when there is changed data, how do I know when data has changed? I need to know more about your setup.

The api you want to limit calls to -- is it the data source, you are fetching data from to find out changes? Or is it a sink?
Is there another data source involved?
maliadalar 1 Reputation point

2022-07-28T14:27:48.527+00:00

Sure,

I have source table(OLTP) and sink table(OLAP) that I compare if there is a change in the data (address, phone, email) I tried to make my dataflow as hashing these fields and compare the hashes between two table and get only the one that has different value. However I stuck in this part because I need to track two changes separately. One is address itself and second is phone, email which does not require API call. If I do hash for all of them, API will be called even for phone or email changes in my data which is not efficient.

I tried two separate hash but cannot figure out how to sink at the same time.

I hope this explanation would help.

thanks,
MartinJaffer-MSFT 26,161 Reputation points

2022-08-23T19:04:36.897+00:00

Hello @maliadalar ,
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 .

Thanks
Martin

Answer 1

@maliadalar it sounds like what you need is either a "change feed" or "incremental load".

I'm not certain what database you are using for your OLTP, so I can't say whether yours has change feed or other features which could push changes as they are made. This way you could sign up to receive the address data you are looking for.

If your source table has a column that says when a row was last updated, that can be used to pull all changes since your last data load. (see watermark type incremental load)

On the other side, since it sounds like you already have all the data from both the OLTP and the OLAP in your dataflow as sources, why not just do a join, and then a conditional split. The conditional split could be on whether OLTP address <> == != OLAP address. This would result in one stream with all the mismatches. Then you only need to call the api on the mismatches.

Share via

Complex Data Flow

1 answer

Your answer