How do you decide between ADF Mapping Data Flows and an external engine for a team's transformations?

## The three placements, one orchestrator Whichever way this decision goes, Azure Data Factory remains the thing that schedules, retries and monitors the work. What is being chosen is where the transformation *executes*: 1. **In a Mapping Data Flow**, on a Spark cluster the service provisions and manages, defined visually. 2. **In the target database or warehouse**, with ADF invoking SQL through a Stored Procedure or Script activity - the ELT shape, where the compute you already pay for does the work. 3. **On an external engine**, such as a notebook activity against a Spark workspace, where the logic is code your engineers own. A principal-level answer names all three and then argues on criteria rather than preference. ## Criterion one: who maintains it The visual designer is a real asset when the people responsible for the pipelines are analysts or integration specialists rather than software engineers. They can read the graph, see the transformations, preview intermediate data and change a join without a code review cycle. For that team, forcing everything into notebooks produces code nobody can safely modify. The same property is a liability for a team of engineers. They will build the transformation faster in SQL or Python, and they will lose time fighting an expression language that is less expressive than the one they already know. ## Criterion two: testability and change control This is where data flows are weakest and where it matters most at scale. A flow is stored as a JSON definition. It is versioned in Git like everything else in the factory, but a pull request diff on a reordered transformation graph is close to unreadable, and there is no natural unit test for "this derived column handles nulls correctly". Verification tends to be manual: turn on debug, preview, eyeball. SQL and notebooks sit in a repository where a change is a legible diff, logic can be exercised against fixtures, and data assertions can run as part of the build. If the transformation layer is large, frequently changed, or subject to audit, that difference dominates every other consideration. ## Criterion three: cost shape A data flow pays for a managed cluster including its acquisition, per execution. That is reasonable for substantial transformation and poor for many small jobs - the reason people consolidate flows and set a time to live. Pushing SQL into a warehouse that is already running uses capacity you have already bought. An external engine sits between: you control cluster sizing and reuse, and you take on the operational responsibility that comes with it. None of these is universally cheapest; the honest framing is that the data flow's fixed startup cost punishes frequent small work, and warehouse pushdown is usually the cheapest place to run something the warehouse can already do well. ## Criterion four: the capability ceiling Mapping Data Flows cover the standard relational repertoire - join, aggregate, window, pivot, surrogate key, conditional split, row-level insert and update intent. They do not cover custom libraries, machine learning, unusual file parsing, or logic that is genuinely algorithmic. There is also a narrower set of stores usable as data flow sources and sinks than the Copy activity supports, and data flows run only on Azure integration runtimes, so data behind a private network boundary generally has to be landed first. When you find yourself designing around those limits, you have already outgrown the tool. ## Criterion five: portability A transformation expressed as a data flow is expressed in a product. SQL and notebook code move to another platform with effort but without a rewrite of the logic. That matters more for a long-lived core mart than for a departmental feed, and it is a legitimate factor rather than an ideological one. ## A defensible default - Data already landed in a warehouse and reshaping expressible in SQL: **push it down**, orchestrated by ADF. - A low-code team, moderate reshaping, sources the data flow supports: **Mapping Data Flow**, consolidated into few flows with a sensible time to live. - Complex, heavily reviewed, or engineering-owned logic: **code on an external engine**, with ADF as the scheduler. - Pure movement in every case: **Copy activity**, never a data flow. The worst outcome is unexamined uniformity in either direction - every transformation forced into data flows because the tool is there, or every trivial cleanup pushed into a notebook because code is fashionable. State the criteria, accept a mixed estate, and set the rule for when each is chosen so the team does not relitigate it per pipeline.