azure data factory – Incremental Load for Multiple tables
There is pipeline from D365 to Azure Sql database which will be handling by Source team whenever the pipeline runs data will be coming into Azure sql database and watermarkcolumn will be update in Azure sql tables,the source team is doing incremental push everytime for D365 to azure sql database. Now I have Created a incremental load pipeline for 3181 tables from Azure sql database to dedicated Sql pool which is datawarehouse by taking Master pipeline and child pipeline and I have created configuration table in Azure SQL database ( I am getting source data from D365 to Azure SQL database) and added all the table names and in master pipeline I have taken look up activity for all the tables to come from configuration table and For each activity to process the look up activity and inside For each activity I have given execute pipeline activity and inside execute pipeline activity I have invoked child pipeline and in child pipeline I have taken two lookup activities,one copy activity and one stored procedure activity .
one look up activity is for taking old watermark value from watermark table created in the datawarehouse and another lookup activity is for taking new Watermarkvalue from the Azure sql database tables and copy activity will copy the data which have new Watermarkvalue and I have stored procedure for updating the new watermark value after copying the data . the stored procedure activity will execute the stored procedure after copying the data into dataware house. while running the pipeline it is running for 12 hrs.
so we have Seperated 3181 tables to 53 modules and we have created 53 triggers on master pipeline and we are triggering those into 3 batches. one batch has 17 schedule triggers and we are giving half an hour time between 3 batches . So its running in 2 to 3 hours but we got an problem the data coming from D365 to Azure sql database from source side has changed no, 70 percent tables they are doing incremental push and 30 percent they are doing full load push. it will the affect the record count every time in the datawarehouse according to logic i have used in my pipeline. What Logic should I apply for this?
Read more here: Source link
