Handling Errors and Retries in ADF Pipelines

A pipeline that never fails is a pipeline nobody has run for long. Sources go offline, files arrive malformed, a database is busy. What separates a production pipeline from a demo is what happens next: whether the failure is retried when retrying could help, whether someone is told, and whether the run is recorded as failed so that nothing downstream proceeds on bad data.

Azure Data Factory provides the pieces. It also has one behaviour that surprises nearly everyone, and it is worth understanding first.

Layer 1: retry and timeout on the activity

Every execution activity has a policy (Microsoft Learn: pipelines and activities):

The defaults are the wrong way round for production. No retries means a ten-second network blip fails the night's load. A twelve-hour timeout means a hung copy occupies the pipeline until morning.

Retry suits transient failures: throttling, timeouts, a brief loss of connectivity. It does nothing for permanent ones, a missing table, a malformed file, bad credentials, and in those cases it only delays the alert. Two conditions make retry safe:

• The activity must be idempotent. A copy that appends rows, retried after failing halfway, appends some rows twice. A pre-copy script that clears the target, or an upsert, makes a retry harmless; see the Copy activity guide.
• The interval should give the fault time to pass. Thirty seconds is short for a database restarting.

Layer 2: conditional paths

When an activity ends, the arrows leaving it decide what runs next. There are four conditional paths (Microsoft Learn: errors and conditional execution):

One rule from the documentation: an Upon Completion path cannot coexist with an Upon Success or Upon Failure path on the same activity.

The natural use is a failure path that logs the error and sends a notification. And that is where the surprise is.

The surprise: caught failures turn the pipeline green

How does ADF decide whether the pipeline as a whole succeeded? Not by asking whether any activity failed. The documented rule is:

1. Evaluate the outcome of all leaf activities, those with nothing after them.
2. If a leaf was skipped, evaluate its parent instead.
3. The pipeline succeeds if and only if all evaluated activities succeeded.

So a pipeline whose Copy fails, and whose only other activity is a successful "log the error" step on the failure path, ends with one leaf, which succeeded. The pipeline is reported as succeeded.

The script below models that rule in Python for four common patterns. It is a model of the documented rule, not Data Factory; its results for the first three patterns match the summary table in Microsoft's documentation.

"""A model of how Azure Data Factory decides a pipeline's result, following the rule in Microsoft's documentation:
evaluate every leaf activity; if a leaf was skipped, evaluate its parent instead; the pipeline succeeds only if
every evaluated activity succeeded."""


def run(activities, outcome_of_main):
    """activities: {name: (parent, condition) or None for the first activity}. Handlers are assumed to succeed."""
    status = {}
    for name, dep in activities.items():                      # definitions are listed in dependency order
        if dep is None:
            status[name] = outcome_of_main
            continue
        parent, condition = dep
        fires = {"Succeeded": status[parent] == "Succeeded",
                 "Failed": status[parent] == "Failed",
                 "Completed": status[parent] in ("Succeeded", "Failed"),
                 "Skipped": status[parent] == "Skipped"}[condition]
        if not fires:
            status[name] = "Skipped"
        elif name == "FailPipeline":                          # a Fail activity always ends as Failed
            status[name] = "Failed"
        else:
            status[name] = "Succeeded"

    parents = {name: dep[0] for name, dep in activities.items() if dep}
    leaves = [n for n in activities if n not in parents.values()]

    def evaluated(node):                                      # a skipped leaf hands the question to its parent
        while status[node] == "Skipped":
            node = parents[node]
        return status[node]

    return "Succeeded" if all(evaluated(leaf) == "Succeeded" for leaf in leaves) else "Failed"


PATTERNS = {
    "try-catch: only a failure path":
        {"Copy": None, "OnFailure": ("Copy", "Failed")},
    "do-if-else: success and failure paths":
        {"Copy": None, "OnSuccess": ("Copy", "Succeeded"), "OnFailure": ("Copy", "Failed")},
    "do-if-skip-else: plus a dummy on skip":
        {"Copy": None, "OnSuccess": ("Copy", "Succeeded"), "OnFailure": ("Copy", "Failed"),
         "DummyOnSkip": ("OnSuccess", "Skipped")},
    "failure path ending in a Fail activity":
        {"Copy": None, "OnFailure": ("Copy", "Failed"), "FailPipeline": ("OnFailure", "Succeeded")},
}

print(f"{'pattern':40} {'Copy succeeds':>14} {'Copy fails':>12}")
for label, graph in PATTERNS.items():
    print(f"{label:40} {run(graph, 'Succeeded'):>14} {run(graph, 'Failed'):>12}")

pattern                                   Copy succeeds   Copy fails
try-catch: only a failure path                Succeeded    Succeeded
do-if-else: success and failure paths         Succeeded       Failed
do-if-skip-else: plus a dummy on skip         Succeeded    Succeeded
failure path ending in a Fail activity        Succeeded       Failed

Read the right-hand column: what the pipeline reports when the Copy activity fails.

• Try-catch. Only a failure path. The handler runs and succeeds, so the pipeline succeeds. Schedulers, monitoring and any parent pipeline see a green run.
• Do-if-else. Both a success path and a failure path. The success-path activity is skipped, so its parent, the failed Copy, is evaluated. The pipeline fails.
• Do-if-skip-else. A dummy activity on the skip path makes the pipeline succeed again.
• Failure path ending in a Fail activity. The handler runs, then the run is explicitly failed.

None of these is wrong. They are different intentions. The mistake is choosing one without knowing which.

Deciding what a failure should mean

Ask one question: after this step fails and is handled, should the pipeline count as successful?

• Yes, the failure is expected and dealt with. An optional file is missing; a fallback source is used. Use try-catch.
• No, the load did not happen. This is the usual case. The pipeline must end as failed so that dependent pipelines stop, tumbling windows can be rerun, and the monitoring view tells the truth. Handle the error, then fail.

For the second case, the clearest construction is the fourth pattern: log and notify on the failure path, then a Fail activity. The Fail activity exists to throw an error intentionally, with a message and an error code you choose (Microsoft Learn: Fail activity).

Written from the documentation, not deployed:

[
  {
    "name": "CopyOrders",
    "type": "Copy",
    "policy": { "timeout": "01:00:00", "retry": 2, "retryIntervalInSeconds": 120 }
  },
  {
    "name": "LogCopyFailure",
    "type": "SqlServerStoredProcedure",
    "dependsOn": [ { "activity": "CopyOrders", "dependencyConditions": [ "Failed" ] } ],
    "linkedServiceName": { "referenceName": "ControlDb", "type": "LinkedServiceReference" },
    "typeProperties": {
      "storedProcedureName": "etl.log_failure",
      "storedProcedureParameters": {
        "pipelineName": { "value": "@pipeline().Pipeline", "type": "String" },
        "runId": { "value": "@pipeline().RunId", "type": "String" },
        "errorMessage": { "value": "@activity('CopyOrders').error.message", "type": "String" }
      }
    }
  },
  {
    "name": "FailRun",
    "type": "Fail",
    "dependsOn": [ { "activity": "LogCopyFailure", "dependencyConditions": [ "Completed" ] } ],
    "typeProperties": {
      "message": "CopyOrders failed; see etl.failure_log",
      "errorCode": "ORDERS_COPY_FAILED"
    }
  }
]

The Fail activity depends on the logging step with Completed, so the run is failed even if the logging itself breaks. The error's text is available as @activity('CopyOrders').error.message.

Layer 3: retry of the whole pipeline

Some failures are best handled by running the pipeline again.

• Tumbling window triggers have a retry policy for failed pipeline runs, and a failed window can be rerun later with the same window boundaries. See tumbling window triggers.
• Rerun from a chosen activity in the monitoring view restarts a run from a specific point instead of the beginning, which matters for long pipelines.
• A parent pipeline can call a child and take a different path if it fails.

All three require the same thing as activity retries: steps that are safe to repeat.

A shared error handler

Repeating a log-and-notify block after every activity clutters a pipeline. Two tidier arrangements:

• One handler at the end of a chain. Activities are chained on success; a single handler is attached to the last activity with both the failure and the skip conditions, so it runs whether the last step failed or an earlier one did and the rest were skipped. Confirm the behaviour in a debug run before relying on it.
• A reusable child pipeline that takes the pipeline name, run ID and error message as parameters, writes them to a log table and sends the notification. Every pipeline calls it from its failure path.

Send the notification from a service designed for it, a Logic App or a web hook called by a Web activity, or rely on alerts on failed pipeline runs in Azure Monitor. The second is simpler and cannot be forgotten in a new pipeline; monitoring and alerts in ADF covers it.

Failures that are not errors

The most damaging failures raise no error at all:

• A copy that succeeds with zero rows. Check rowsCopied in an If Condition and fail if it is unexpectedly zero.
• Rows skipped by fault tolerance. Check rowsSkipped against a threshold.
• A Lookup that returned nothing.
• A source file that changed shape. Compare the column count before loading.

Turn each into an explicit Fail activity with a clear message. A pipeline that says exactly what was wrong is worth more than one that is merely red.

Checklist

• Timeout and retry set on every activity that touches a network.
• Sinks that tolerate a retry.
• A failure path that logs the run ID and error message.
• A deliberate decision on whether a handled failure should fail the pipeline, and a Fail activity where it should.
• An alert on failed pipeline runs at the factory level.
• Explicit checks for zero rows and skipped rows.
• Clean-up attached with a condition that still runs after a failure.

For how the dependency conditions work in general, see pipeline dependencies and scheduling.

Azure Data Factory, with pipeline monitoring and error handling, is covered in the Data Engineering course.

Sources

• Errors and conditional execution in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Pipelines and activities in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Execute a Fail activity in Azure Data Factory and Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Create a trigger that runs a pipeline on a tumbling window (Microsoft Learn) — Microsoft Learn