ADF Control Flow: ForEach, If and Until Activities

A pipeline without control flow runs the same steps the same way every time. Real pipelines need more: do this for each table, skip that if nothing arrived, wait until the job finishes. Azure Data Factory provides these through control activities, which do not move data themselves but decide what the other activities do.

Four of them carry most of the load. All the JSON below is written from Microsoft's documentation and has not been deployed.

ForEach: the loop

ForEach takes an array and runs its inner activities once per element. Inside the loop, @item() is the current element (Microsoft Learn: ForEach activity).

{
  "name": "ForEachTable",
  "type": "ForEach",
  "typeProperties": {
    "items": { "value": "@pipeline().parameters.tables", "type": "Expression" },
    "isSequential": false,
    "batchCount": 5,
    "activities": [
      {
        "name": "CopyOneTable",
        "type": "ExecutePipeline",
        "typeProperties": {
          "pipeline": { "referenceName": "CopyTableToLake", "type": "PipelineReference" },
          "waitOnCompletion": true,
          "parameters": {
            "schemaName": "@item().schemaName",
            "tableName": "@item().tableName"
          }
        }
      }
    ]
  }
}

The array usually comes from a pipeline parameter, as here, or from an earlier activity: @activity('GetTableList').output.value for a Lookup, or output.childItems for Get Metadata. Lookup and Get Metadata covers those.

Three things about ForEach decide whether it behaves.

It is parallel by default. isSequential defaults to false, so up to batchCount iterations run at once. That is what you want for copying independent tables, and the reason to set batchCount deliberately: fifty simultaneous queries against a small source database will not make anyone happy. Start low.

Parallel iterations must not share state. Microsoft's own caution is that concurrent iterations writing to different files in a folder are fine, while concurrent writes to the exact same file will most likely cause an error. The same applies to variables: the documentation states that Set Variable cannot be used inside a parallel ForEach, because variables are global to the whole pipeline.

It has limits. A maximum batchCount of 50, a maximum of 100,000 items, and no nesting: a ForEach cannot be placed inside another ForEach or an Until.

The standard answer to the nesting limit, and to most ForEach problems, is the pattern in the example: put the loop body in a separate pipeline and call it with Execute Pipeline. The inner pipeline can contain its own loop, has its own variables, and can be started and checked on its own.

If Condition: two branches

If Condition evaluates an expression that must return true or false, and runs one of two sets of activities (Microsoft Learn: If Condition activity).

{
  "name": "IfRowsCopied",
  "type": "IfCondition",
  "dependsOn": [ { "activity": "CopyOrders", "dependencyConditions": [ "Succeeded" ] } ],
  "typeProperties": {
    "expression": {
      "value": "@greater(activity('CopyOrders').output.rowsCopied, 0)",
      "type": "Expression"
    },
    "ifTrueActivities": [
      {
        "name": "RunMerge",
        "type": "SqlServerStoredProcedure",
        "linkedServiceName": { "referenceName": "Warehouse", "type": "LinkedServiceReference" },
        "typeProperties": { "storedProcedureName": "etl.merge_orders" }
      }
    ],
    "ifFalseActivities": [
      {
        "name": "NoteNothingToDo",
        "type": "SetVariable",
        "typeProperties": { "variableName": "status", "value": "no new rows" }
      }
    ]
  }
}

The expression must produce a boolean. A common error is an expression that returns the string "true", which happens when interpolation is used; write it as a whole-value expression starting with @, and build the test from functions such as equals, greater, and and not.

Use If Condition for genuine either-or logic: run the merge only if rows arrived, take a different path for a full load than for an incremental one. Do not use it for error handling. Whether an activity failed is handled by dependency conditions on the arrows between activities; see handling errors and retries.

Switch: several branches

When the choice has more than two outcomes, Switch is cleaner than nested conditions. It evaluates an expression that returns a string, runs the activities of the case whose value matches, and runs defaultActivities when none do. There is a maximum of 25 cases (Microsoft Learn: Switch activity).

A typical use is one ingestion pipeline handling several source types: a case for sql, one for api, one for file, each calling the pipeline that knows how to handle it, with the default raising an error for an unknown type. Always fill in the default. A silent no-op for an unexpected value is how a new source type goes unloaded without anyone noticing.

Until: repeat until a condition holds

Until runs its activities, evaluates its expression, and repeats until the expression is true. It is a do-until loop: the body always runs at least once (Microsoft Learn: Until activity).

{
  "name": "UntilExportReady",
  "type": "Until",
  "typeProperties": {
    "expression": {
      "value": "@equals(activity('CheckStatus').output.status, 'Completed')",
      "type": "Expression"
    },
    "timeout": "0.02:00:00",
    "activities": [
      {
        "name": "CheckStatus",
        "type": "WebActivity",
        "typeProperties": { "url": "https://example.com/api/exports/latest", "method": "GET" }
      },
      {
        "name": "WaitBeforeRetry",
        "type": "Wait",
        "dependsOn": [ { "activity": "CheckStatus", "dependencyConditions": [ "Succeeded" ] } ],
        "typeProperties": { "waitTimeInSeconds": 60 }
      }
    ]
  }
}

Its main job is polling: an external system is asked to produce something, and the pipeline checks every so often until it is ready.

Two settings keep an Until loop safe.

Set the timeout. The default is seven days, and the maximum is 90 days. A loop whose condition never becomes true will run, and bill for its inner activities, for a week unless you say otherwise. The example allows two hours.

Always include a Wait. Without one the loop spins as fast as its activities allow, generating activity runs and hammering whatever it polls.

Until is also how you write a counted loop or paginate through an API when the number of pages is unknown in advance. That needs a variable for the counter or cursor, with the two-step update that ADF requires; see global parameters and variables.

Choosing

Mistakes to avoid

• Leaving the batch count at its default against a source that cannot take twenty parallel queries.
• Setting variables inside a parallel ForEach.
• Parallel iterations writing to the same file or the same table partition.
• An Until with no Wait, or with the seven-day default timeout.
• A Switch with an empty default.
• Deep logic inside containers. Inner activities count towards a pipeline's default soft limit of 120 activities, and a pipeline that large is hard to read long before it reaches the limit. Split it into child pipelines.
• Looping where a set operation would do. A ForEach that runs one query per row should usually be one query.

For the expressions used in the conditions here, see ADF expressions and functions.

Azure Data Factory and pipeline orchestration are covered in the Data Engineering course.

Sources

• ForEach activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• If Condition activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Switch activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Until activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Pipelines and activities in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn