Cloud · Azure in Depth

Metadata-driven ADF pipelines with Lookup and ForEach

azureMediumazureadfmetadata-drivenlookupforeachparameters

Interview question

How would you copy hundreds of tables with one Azure Data Factory pipeline instead of building one pipeline per table?

How to answer

The short answer

Building a separate pipeline for each table does not scale, and every change has to be repeated hundreds of times. The usual answer is a metadata-driven pipeline: keep the list of tables in a control table, read it at run time, and loop over it.

Output
Pipeline A: Lookup activity (firstRowOnly = false)  -> list of tables
            Execute Pipeline activity               -> pass the list as a parameter
Pipeline B: ForEach activity over the list
              Copy activity (parameterised source and sink datasets)

How each piece works:

  • Lookup runs a query such as SELECT schema_name, table_name FROM etl_control WHERE enabled = 1 and returns the rows. With firstRowOnly set to false it returns the whole list as @activity('LookupTableList').output.value.
  • ForEach takes that list as its items and runs the inner activities once per element, which you read with @item(). With isSequential set to false, iterations run in parallel, and batchCount caps how many run at once.
  • Parameterised datasets accept the table name, so one dataset serves every table. The Copy activity passes @item().table_name into it.

Microsoft's own bulk-copy tutorial splits the work into two pipelines (one that looks up the tables, one that loops and copies) and links them with Execute Pipeline. One reason is that ForEach cannot be nested directly inside another ForEach.

What to say about the risks

  • Parallel copies all hit the source system together. Set batchCount to a number the source database can handle, not the maximum.
  • Lookup output has a size and row limit (check the current numbers in the ADF documentation), so a very large control list needs paging or a different design.
  • A control table with an enabled flag lets operations turn a table off without editing and republishing the pipeline.

The same idea combines well with the watermark pattern: the control table holds one watermark per table.