How to implement incremental load using CT with Multiple tables?

Moulshree Dhanawade 1 Reputation point
2020-11-18T06:33:52.527+00:00

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?

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,715 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,476 Reputation points Microsoft Employee
    2020-11-18T23:04:42.29+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.