Azure Data Factory Interview Questions and Answers Part 5 - Control Flow and Error Handling

Azure Data Factory Interview Q&A · Part 5 of 10

In this post we are going to cover control flow and error handling in Azure Data Factory: how a pipeline loops, branches, calls other pipelines and decides whether it succeeded or failed. These are 7 real Azure Data Factory interview questions, asked the way they come up on a real project, and the last one is a scenario where the pipeline must wait for a done file, then load, then notify.

Control flow is everything in a pipeline that is not moving data: Lookup, ForEach, If Condition, Switch, Until, Execute Pipeline, and the arrows between activities. Interviewers ask about it because this is where real pipelines break. A loop gives wrong values, an email never goes out, or a run shows Succeeded when the copy failed.

Part 5 of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Question 1: You must copy every table listed in a config table. How do Lookup and ForEach work together, and what are their limits?
3. Question 2: A teammate uses Set Variable inside a parallel ForEach and the values come out wrong. Why, and what is the fix?
4. Question 3: The pipeline must do something different for each department. When do you use If Condition, Switch and Until?
5. Question 4: You need a parent pipeline that calls child pipelines. How does Execute Pipeline work, and what does wait on completion change?
6. Question 5: Your manager wants an email whenever any step fails. How do success, failure, completion and skipped dependencies work?
7. Question 6: You built a try-catch with an upon-failure path, and the pipeline shows Succeeded even though the copy failed. Why, and how do you make it fail?
8. Scenario 7: The pipeline must wait until the source system drops a done file, then load, then notify. How do you design it?

The short answer

Say this first

Lookup feeds ForEach (5,000 rows, 4 MB; batch count 20 by default, 50 max). Variables are pipeline-scoped, so a parallel loop needs Append Variable or a child pipeline. Pipeline status comes from the leaf activities: a try-catch reports Succeeded, so end the catch with a Fail activity when the run must fail.

Keep that in mind. Every question below is one piece of it.

Question 1: You must copy every table listed in a config table. How do Lookup and ForEach work together, and what are their limits?

A Lookup reads the config table, and a ForEach loops over its rows. Let's walk through it in order.

• First, the config table holds one row per table to copy.
• Then the Lookup reads it with first row only turned off, so its output is an array called value.
• The ForEach takes that array as its items: @activity('LookupConfig').output.value.
• Inside the loop, a parameterized copy reads item().SchemaName and item().TableName.

Here is the ForEach part of the pipeline JSON:

pipeline.json

"type": "ForEach",
"typeProperties": {
  "items": {
    "value": "@activity('LookupConfig').output.value",
    "type": "Expression" },
  "isSequential": false,
  "batchCount": 20 }

Now the limits. A Lookup returns at most 5,000 rows and 4 MB, and above 4 MB it fails. A ForEach takes up to 100,000 items. It runs in parallel by default, with a batch count of 20 and a maximum of 50.

Limit	Value
Lookup output	5,000 rows, 4 MB
ForEach items	up to 100,000
Batch count	default 20, max 50
Nesting	no ForEach in ForEach or Until

You can't put a ForEach directly inside another ForEach or an Until. The workaround is an Execute Pipeline activity inside the loop, with the inner loop in the child pipeline.

Real project tip

If the config table can grow past 5,000 rows, page it with a two-level pipeline. And batch count is a ceiling: ADF splits the items into queues up front and never rebalances them, so one huge table holds up its whole queue.

Question 2: A teammate uses Set Variable inside a parallel ForEach and the values come out wrong. Why, and what is the fix?

Because variables belong to the pipeline, not to the loop. It's a race condition.

Let's picture it. A parallel ForEach runs up to 20 iterations at once by default. Iteration 1 is on Customer, iteration 2 on Orders, iteration 3 on Products, and all three write the same variable, vTableName. One iteration overwrites another before it reads the value back, so the last write wins.

Here are the fixes, from the one I reach for first:

• Append Variable: collect results into an array variable. That's the documented way to gather results from a loop.
• Child pipeline: move the loop body into a child pipeline and call it with Execute Pipeline. Every child run has its own variables, so nothing is shared.
• Sequential ForEach: safe, but slow.
• No variable: often you don't need one at all. Read item() or the activity output directly in the expression.

One more gotcha

Set Variable can't reference its own variable. To increment a counter, use a temporary variable and two Set Variable activities.

Question 3: The pipeline must do something different for each department. When do you use If Condition, Switch and Until?

Use If Condition for a yes or no decision, Switch for one path per value, and Until for polling.

Let's look at each one.

• If Condition takes an expression that returns true or false, and has two branches. For example: did the lookup return any rows?
• Switch takes an expression that returns a string, runs the matching case, and has default activities for everything else. That fits the department question: one case each for Finance, HR and Sales, up to 25 cases.
• Until is a do-until loop. It runs its activities, then checks the condition, and repeats until it's true. Use it for polling, with a Wait inside.

Activity	Expression returns	Use it for
IF CONDITION	true or false	a yes or no branch
SWITCH	a string; up to 25 cases plus default	one path per value
UNTIL	true or false, checked after each pass	polling, with a Wait inside

Here is one expression for each, in the same order:

expression.txt

@greater(activity('LookupRows').output.count, 0)
@pipeline().parameters.Department
@equals(activity('CheckFile').output.exists, true)

Two things to know about Until. An inner failure doesn't stop the loop, so check the status in the condition. And set a timeout: the default is 7 days.

Real project tip

If every department runs the same steps with different values, don't build 25 cases. Put the department settings in a control table and pass them as parameters. And for loops inside loops, call a child pipeline.

Question 4: You need a parent pipeline that calls child pipelines. How does Execute Pipeline work, and what does wait on completion change?

Execute Pipeline starts another pipeline in the same factory and passes it parameters. We use it to reuse one load pattern, to get around the nesting rule, and to give each run its own variables.

Here is the activity, called from inside a ForEach:

pipeline.json

"type": "ExecutePipeline",
"typeProperties": {
  "pipeline": {
    "referenceName": "PL_Child_Load",
    "type": "PipelineReference" },
  "parameters": {
    "TableName": "@item().TableName" },
  "waitOnCompletion": true }

Wait on completion is the key setting, and it's on by default. The parent waits for the child to finish. If the child fails, the Execute Pipeline activity fails too, so the parent's failure path runs.

Turn it off, and it's fire and forget. The parent moves on without waiting, so it can't react to what the child did. Use that only when nothing downstream depends on the child.

Wait on completion	Parent	If the child fails
ON (default)	waits for the child	Execute Pipeline fails
OFF	moves on at once	parent can't react

Logging tip

Each child is its own pipeline run, with its own variables. It can read the parent's run ID through @pipeline()?.TriggeredByPipelineRunId, which is great for logging.

Question 5: Your manager wants an email whenever any step fails. How do success, failure, completion and skipped dependencies work?

Every arrow between activities has a condition, and the default is Upon Success.

Condition	Runs when the previous activity
UPON SUCCESS (default)	succeeded
UPON FAILURE	failed
UPON COMPLETION	succeeded or failed
UPON SKIP	did not run

Here's the trap. When one activity depends on several others, the conditions are combined with AND. Let's walk through it.

• You wire one Send email activity with Upon Failure from step 1, step 2 and step 3.
• Step 1 fails.
• Steps 2 and 3 are skipped, not failed.
• The email needs all three to fail, so it never runs.

The clean fix is a wrapper. A parent pipeline calls the main pipeline with Execute Pipeline, and one Upon Failure path sends the email, through a Web activity and a Logic App. Across the whole factory, an Azure Monitor alert on failed pipeline runs is even simpler.

In the email, include the error message from the failed activity, the pipeline name and the run ID:

expression.txt

@activity('CopySales').error.message
@pipeline().Pipeline
@pipeline().RunId

Why it matters

With those three values, whoever is on call can open the right run straight away.

Question 6: You built a try-catch with an upon-failure path, and the pipeline shows Succeeded even though the copy failed. Why, and how do you make it fail?

Because of how ADF decides pipeline status. It looks at the leaf activities, the last ones on each path. If a leaf was skipped, it looks at that leaf's parent instead. The pipeline succeeds only if every activity it evaluates succeeded.

Let's apply that rule to two patterns.

• Try-catch. The copy has only an Upon Failure path. The copy fails, the Log error activity runs and succeeds, and it is the only leaf. So the pipeline reports Succeeded: ADF treats the error as handled.
• Do-if-else. The copy has an Upon Success path and an Upon Failure path. The copy fails, so the next step on the success branch is skipped. ADF evaluates its parent, the failed copy, and the pipeline reports Failed, even though the handler succeeded.

To make a try-catch fail, end the catch branch with a Fail activity. Log the error first, then the Fail activity raises it again, with your own message and error code:

pipeline.json

"type": "Fail",
"typeProperties": {
  "message": "@{activity('CopySales').error.message}",
  "errorCode": "500" }

Always-run steps

For steps that must run whatever happens, like writing an audit row, use Upon Completion.

Scenario 7: The pipeline must wait until the source system drops a done file, then load, then notify. How do you design it?

I build it in three stages: wait for the done file, load, then notify on success and on failure.

First, the wait. The simplest is a Validation activity. It checks for the done file, for example sales_done.done, at a sleep interval you set, and it can require a minimum size. Its timeout defaults to 12 hours, so set it to your real cutoff. Here we wait up to 6 hours and check every 300 seconds:

pipeline.json

"type": "Validation",
"typeProperties": {
  "dataset": {
    "referenceName": "DS_DoneFile",
    "type": "DatasetReference" },
  "timeout": "0.06:00:00",
  "sleep": 300 }

For custom logic, use an Until loop with Get Metadata checking exists, and a Wait inside. Or skip the polling: a storage event trigger starts the pipeline when the done file is created, filtered on blob path ends with .done.

Then the load: a Lookup on the control table, a ForEach of copies, and a stored procedure that logs the run. Then notify, and on failure use a Fail activity, so the run still shows Failed.

Here is the checklist I go through:

• Timeout: the default is 12 hours, so set your cutoff.
• Or an event trigger: blob path ends with .done.
• Log every run: a stored procedure.
• Fail honestly: a Fail activity or a wrapper pipeline.
• Archive the done file: after the load, so tomorrow's run doesn't pick up today's.

File late?

If the file is late, the Validation times out, so send an alert that says "source is late", not "load broke". The Validation timeout tells you which one happened.

Key points to remember

• Lookup: 5,000 rows, 4 MB. ForEach: batch 20, max 50
• Variables are pipeline-scoped: Append Variable or a child pipeline
• Switch for one path per value; Until for polling
• Wait on completion on: a child failure fails the parent activity
• Try-catch reports Succeeded: end the catch with a Fail activity

Next in this series

Part 6: Parameters, Expressions and Metadata-Driven Pipelines

Part of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Microsoft Learn page for anything you quote in an interview.