Hi Bigiron ,
Thankyou for using Microsoft Q&A platform and thanks for posting your question here.
As I understand your query, you want to know how to cherry pick the tables and load the data of only specific tables from one DB to another using ADF pipeline. Please let me know if that is not the ask here.
You can create a control table in your source SQL DB, and use that for looking up for Active tables
In ADF, use lookup activity to select only those tablenames where isActive flag is 'Y' and use foreach activity to iterate through those tables and using copy activity, load the data from source to destination .
You can also incorporate the logic to perform full load/Incremental load for each of the tables based on the size of the data for each table. For example, if a table has small set of data, you can keep it as full load table and perform truncate and reload everytime. However, if there is huge set of data, then it's better to implement incremental load for those tables and perform upsert for changed data.
For reference, you can go through these videos in sequence, which walksthrough the same requirement in synapse analytics, however, it can be implemented in ADF too changing the sink to SQL DB instead of synapse.
How to do full load from On Premise SQL Server till ADLS
How to load latest and greatest data
How to perform incremental load
How to perform Upsert for Incremental records
Additionally, you can explore CDC feature in ADF which is the easiest and quickest way to handle incremental load in SQL. For more details, kindly check the below resources:
Change data capture in Azure Data Factory and Azure Synapse Analytics
CDC(change data capture) for SQL Source in Mapping data flows in Azure Data Factory or Synapse
CDC (change data capture) Resource in Azure Data Factory
Hope it helps. Kindly accept the answer and take the survey. Thankyou