How to trigger an ADF based on any data changes within a table in database

Nandan Hegde 29,896 MVP

Hello All,
Is it possible to trigger an ADF pipeline after some data changes have been made in a table within a database ?

We are using MDS for master data management and want to push the data into the Azure SQL Datawarehouse the moment someone updates/inserts data into MDS.
So is there any way to auto trigger the ADF job the moment someone publishes/saves data into MDS?

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-15T01:35:02.773+00:00

Hello @Nandan Hegde
Thanks for the ask and using the forum .

I think we can always have a trigger run scheduled which can check all the tables in MDS and then update the the sink tables .
When there is no update or insert the trigger runs and does nothing . We can also implement this using change tracking ( I am assuming that you have SQL based MDS ) . I
Lets say that you have the trigger scheduled every 15 mins and it runs at 9:00 AM , 9:15 AM ... .

-----------
Comment 1/2 ------------------------
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-15T01:35:50.973+00:00

-----------
Comment 2/2 ------------------------

Best case scenario : We update the data at 9:14 AM and the very next minute ADF triggers the pipeline and start the sync .
Worst case scenario : We update the data at 9:01 AM and now we need to wait for 14 mins before the trigger to fire and updates to complete .
The ask is can you guys wait for 15 mins ? If yes then all ADF / Logic apps / and ASA can solve the problem ,
if not then we can think to call the ADF REST API for every update/ Insert . I dont know if any service does this unless we write something oif our known .

https://learn.microsoft.com/en-us/rest/api/datafactory/pipelines/createrun

Thanks
Himanshu
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-17T22:57:34.69+00:00

Hello @Nandan Hegde ,
We have not heard back from you on this and was just following up .
Incase if you have resolution , request you to share the same here , so that other community members can benefit from that .
Thanks
Himanshu
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-23T20:15:52.047+00:00

Hello @Nandan Hegde ,
We have not heard back from you on this yet and was just following up .
Incase if you have resolution , request you to share the same here , so that other community members can benefit from that .
Thanks
Himanshu
Sonam Jain 1 Reputation point Microsoft Employee

2021-03-17T05:29:47.097+00:00

I have a similar requirement as follows- I have enabled Change Data Capture on Azure SQL tables and want to trigger ADF pipeline as soon as there is an entry into CDC table. Can you please help how this can be achieved in Data Factory.
DJ Mata 1 Reputation point

2021-04-18T04:41:51.96+00:00

Hi Nandan ,
Wanted to check if you were able to achieve this .
I have exact same situation and I am keen to know how you solved this .
Thanks in advance.
DJ Mata 1 Reputation point

2021-04-18T04:42:49.463+00:00

Hi @HimanshuSinha-msft
Your approach results in lot of compute power wasted .
Is there a better way .
DJ Mata 1 Reputation point

2021-04-18T05:22:35.13+00:00

Hi @Nandan Hegde
Wanted to check if you were able to achieve this .
I have exact same situation and I am keen to know how you solved this .
Thanks in advance.