How to implement incremental load using CT with Multiple tables?

Question

I am trying a scenario where my source is SQL DB and the destination is Azure SQL DB. I am incrementally loading data using lookup and for-each loop for multiple tables using the timestamp for multiple tables. But the same thing fails when it comes to implementing incremental pipeline with a Change tracking approach.

How can I implement incremental load with multiple tables using CT? What will my query be in the copy activity source side? What /pre-requirements steps do I need to take to implement this?

Answer 1

Hello @Moulshree Dhanawade ,

Thanks for the ask and using this forum .

There are two challenges here , you will need the table name ( which I think you already have from the lookup ) and also the columns of the table . As you will have to use that in the in the query in the copy activity ( I am assuming that you are following this tutorial ) . You will need the columns names in point#8 query .

select **data_source_table.PersonID,data_source_table.Name,data_source_table.Age**, CT.SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION from data_source_table RIGHT OUTER JOIN CHANGETABLE(CHANGES data_source_table, @{activity('LookupLastChangeTrackingVersionActivity').output.firstRow.SYS_CHANGE_VERSION}) as CT on data_source_table.PersonID = CT.PersonID where CT.SYS_CHANGE_VERSION <= @{activity('LookupCurrentChangeTrackingVersionActivity').output.firstRow.CurrentChangeTrackingVersion}  

You can bring in multiple columns in Lookup and then use that to build the expression .

Let me know as to how it goes .

Thanks
Himanshu