ADF pipeline succeeds but data is wrong - common patterns and how to catch them

Hey Chandramouli (and Amira!),

This is a great topic—nothing more frustrating than a green-check pipeline that quietly loads garbage. Here are some of the most common “silent failure” patterns I’ve seen, plus how you can proactively catch them in your ADF pipelines:

1. Type mismatches silently convert or drop data • What happens: Source column changes from string to int (or vice versa), copy activity quietly casts bad values to null or defaults. • How to catch it: – Add a “Validate Schema” activity (Data Flow schema drift or custom script) before your load. – Use Mapping Data Flow’s built-in schema drift settings to alert on unexpected column types. – Fail the pipeline if validation flags any unexpected type.
2. Upstream schema/table changes go unnoticed • What happens: Dev adds a new column, renames one, or changes a file layout—downstream queries silently skip it. • How to catch it: – Maintain a metadata table of expected table/file schemas and use a Lookup + If Condition at the start of your pipeline to compare actual vs. expected. – Use the Data Factory REST API or Azure Function to pull the dataset’s JSON schema at runtime and diff it against your “golden” schema.
3. Child pipelines swallow failures • What happens: A “Execute Pipeline” inside a ForEach or parent pipeline fails, but the parent still reports success because the error path isn’t wired up. • How to catch it: – In your child pipelines, use TRY-CATCH (OnFailure paths) and raise a custom error (Set Variable + Fail activity). – Back in the parent, hook the child’s Failure path into a Fail activity so any child error bubbles up.
4. Missing row-count or record-level checks • What happens: You run a daily load from SalesData, records go down from 10k to 2k and you never notice. • How to catch it: – After your Copy/Data Flow, insert a Validation step:

   • Compare source vs target row counts (Lookup + If Condition).  
   
   • Validate key business rules (e.g., no null dates, no negative amounts) via a simple SQL or Mapping Data Flow filter.  
   

   – Fail early if any count or rule check doesn’t pass.
5. No auditing or metrics stored • What happens: You have no baseline to know if “this run” is off by 10% vs “normal.” • How to catch it: – Log audit details to a SQL/Azure Table/Log Analytics workspace after each activity:

   • Row counts in/out, data skew warnings, error counts.  
   
   • Timestamps and execution durations for each step.  
   

   – Build a dashboard/alert when any metric deviates beyond a threshold.

Putting it all together, a robust pattern looks like:

1. Orchestrate your ETL as: • Pre-validation (schema + row count) • Core transformation/load • Post-validation (business rules + row count) • Audit logging
2. Use OnSuccess/OnFailure links on every critical activity so nothing ever silently finishes.
3. Treat your pipeline’s “Success” as “all validation steps passed,” not “all activities ran.”

Hope this helps—and I’d love to join next Tuesday’s session!

References

• Troubleshoot pipeline validation errors in ADF: https://docs.microsoft.com/azure/data-factory/pipeline-trigger-troubleshoot-guide

• Mapping Data Flow schema drift & validation: https://docs.microsoft.com/azure/data-factory/concepts-data-flow-schema-drift

• Row count and business rule checks with Lookup + If Condition: https://docs.microsoft.com/azure/data-factory/control-flow-lookup-activity

• Audit logging patterns & metrics: https://docs.microsoft.com/azure/data-factory/concepts-pipelines-activities#monitoring-and-logging

Below are common patterns that cause “green” ADF runs with “bad” data, plus concrete ways to detect and prevent them using built‑in orchestration and error‑handling features.

1. Silent upstream schema or type changes

• Pattern:
  • Source column length/type changes (e.g., nvarchar(50) → nvarchar(200), or numeric → string) and sinks accept data with implicit conversions or truncation.
  • Pipelines still succeed, but values are truncated or mis-typed.
• Detection/mitigation:
  • Add validation/data-quality steps as explicit activities (e.g., stored procedures or data flows) and treat them as mission‑critical with error handling.
  • Use the “Error handling” pattern on single activities: attach a validation activity on the Upon Failure path of critical copy/data flow activities so that any validation failure is surfaced and logged instead of silently ignored.
  • For less critical checks, use “Best effort steps” on Upon Completion to log anomalies without blocking the pipeline.

2. Child pipeline failures not propagating to parent

• Pattern:
  • Parent pipeline uses Execute Pipeline but downstream logic is wired only on the parent’s success path or ignores the child’s leaf‑level status.
  • Child fails, but parent still appears successful or continues as if everything is fine.
• Detection/mitigation:
  • ADF evaluates pipeline success based on all leaf‑level activities; ensure the child pipeline’s failure is not being masked by skip/conditional logic.
  • In the parent, explicitly branch on the Upon Success and Upon Failure outcomes of the Execute Pipeline activity so that failure paths trigger error handling or halt downstream processing.
  • Use the “Generic error handling” pattern: add a final error‑handling activity and connect UponFailure and UponSkip from the last critical activity (which may be an Execute Pipeline) to that handler so any failure in the chain triggers a centralized handler.

3. Partial loads and best‑effort steps hiding data issues

• Pattern:
  • A copy or data flow partially processes data (e.g., some files or partitions fail) but downstream steps still run and overwrite or append to curated tables.
  • Because orchestration is configured as “best effort,” the pipeline is green while data is incomplete.
• Detection/mitigation:
  • Reserve “Best effort steps” (activities on Upon Completion) for non‑critical logging/notifications only.
  • For core data movement/transform stages, chain activities using Upon Success so that downstream stages only run when all upstream work is successful.
  • Use the “And” pattern: when multiple upstream activities must all succeed before a consolidation step, connect them to the next activity via Upon Success so that any single failure blocks the consolidation.

4. Error handling that runs, but pipeline still appears healthy

• Pattern:
  • Error‑handling activities clean up or log issues but orchestration continues as if nothing happened, leading to downstream tables built on incomplete or rolled‑back data.
• Detection/mitigation:
  • Use the “Try‑Catch‑Proceed” pattern deliberately:
    • First activity = main work.
    • Upon Failure → error‑handling activity (cleanup/log).
    • Next activity is connected from both the error‑handling activity’s UponFailure/UponSkip and from the first activity (depending on design).
  • For scenarios where failure must stop the pipeline, do not connect the main path via Upon Completion; instead, let the error‑handling branch end the pipeline or explicitly fail (e.g., via a stored procedure or custom error) after cleanup.

5. Transient or capacity issues causing inconsistent data

• Pattern:
  • Integration runtime capacity limits or transient internal errors cause some data flows to fail intermittently.
  • If retries or branching are misconfigured, some runs succeed partially and still feed downstream processes.
• Detection/mitigation:
  • Configure retries on activities to handle transient issues, as recommended in the mapping data flow troubleshooting guidance.
  • Monitor integration runtime capacity and avoid overloading a single IR with many parallel data flows; if needed, split pipelines across multiple IRs or stagger triggers.
  • Ensure that any activity that exhausts retries and fails is wired to error‑handling logic and that downstream activities depend on Upon Success only.

6. Logging and observability gaps

• Pattern:
  • Pipelines technically succeed but there is no systematic logging of row counts, schema versions, or anomaly metrics, so data issues are discovered late.
• Detection/mitigation:
  • Add dedicated logging activities (e.g., Web, stored procedure, or script) after critical stages using Upon Completion for “best effort” telemetry that does not block the pipeline.
  • Combine with the “Generic error handling” pattern so that any failure path also logs detailed context (activity name, error, row counts) to a central store.

Putting it together as a robust pattern

• For each critical stage:
  • Main activity → Upon Failure → error‑handling activity (cleanup/log) using the “Error handling” pattern.
  • Downstream business‑critical activities depend on Upon Success from all required upstream activities (the “And” pattern).
• At the pipeline level:
  • Final “Generic Error Handling” activity wired from UponFailure and UponSkip of the last critical activity to centralize failure logging.
  • Optional non‑blocking telemetry/logging activities on Upon Completion for observability.

This combination ensures that:

• Any real failure is surfaced and can stop or divert the pipeline.
• Non‑critical logging does not block processing.
• Parent/child pipelines propagate failures correctly.
• Schema/type issues are caught by explicit validation and error‑handling steps instead of silently corrupting data.

References:

• Errors and Conditional execution
• Troubleshoot pipeline orchestration and triggers in Azure Data Factory
• Troubleshoot mapping data flows in Azure Data Factory (ADF)