Question

A data engineering team must load data from 40 source tables in an on-premises SQL Server into a Fabric Lakehouse every night. Each table has a different name and watermark column, but the copy logic is identical. The team wants to avoid building 40 separate copy activities and instead drive the process from a control table that stores the table names and watermark values. Which orchestration approach in a Fabric Data pipeline best meets this requirement?

Accepted Answer

Use a Lookup activity to read the control table, then a ForEach activity that iterates the results and calls a parameterized Copy activity using dynamic expressions.

Answer

Create a Dataflow Gen2 with 40 separate queries, one per source table, and schedule it nightly.

Answer

Create 40 individual Copy activities in the pipeline and connect them sequentially with success dependencies.

Answer

Use a single Notebook activity with hardcoded table names and run it on a nightly schedule.