Orchestrate Data Movement and Transformation. In the world of data engineering, raw data is useless on its own—it needs to be moved from where it is collected to where it can be processed, and it needs to be transformed into a clean, structured format that analysts and machine learning models can use. Orchestration is the automated coordination of these steps, and it is a core skill for the DP-203 exam because Azure expects you to design pipelines that reliably move and transform data at scale without manual babysitting.
Jump to a section
A simple way to picture Orchestrate Data Movement and Transformation
A Bakery Kitchen Manager is the person who orchestrates the movement and transformation of ingredients from the store room to the final baked goods on the display shelves.
The bakery receives raw ingredients—flour, sugar, eggs, butter—in bulk deliveries throughout the week. The manager's job is to coordinate the entire flow: deciding when to move flour from the dry storage to the mixing station, when to bring chilled butter from the walk-in fridge to the pastry bench, and how to schedule the use of the ovens so that multiple batches of bread, cakes, and pastries are produced in the right order without causing a bottleneck. The manager doesn't mix the dough or bake the loaves themselves; instead, they design the sequence of steps. They decide that the sourdough starter needs to be fed at 6am, the croissant dough must rest in the fridge for exactly two hours before rolling, and the cake batter must be baked immediately after mixing to keep its rise. If the manager didn't plan and monitor this movement and transformation, ingredients would pile up at one station, ovens would sit idle while mixers ran, or the finished products would be cold and stale before reaching the shop front. The manager also handles failures: if the oven breaks, they reroute the unbaked loaves to a backup oven or adjust the schedule so that no ingredient is wasted. This role of coordinating, sequencing, and transforming raw inputs into finished outputs is exactly what orchestration does for data pipelines in the cloud.
Orchestration is the automated coordination and management of multiple tasks that need to happen in a specific order to move and transform data from source to destination. Think of it as the conductor of an orchestra—no single musician can play the whole symphony alone, but the conductor ensures every section plays at the right time, with the right volume, and in harmony.
In data engineering, the 'musicians' are different Azure services like Azure Data Factory (ADF), Azure Synapse Pipelines, Databricks notebooks, and Azure Functions. The 'sheet music' is the pipeline definition that specifies which tasks to run, in what sequence, and what to do if something goes wrong. Orchestration solves the problem of manual scripting, where a data engineer used to write hundreds of lines of custom code to check whether a file arrived, copy it, clean it, and load it into a database. That approach was brittle—any failure would stop everything and require human intervention.
Azure Data Factory is the primary orchestration tool for DP-203. It allows you to build pipelines that contain a series of activities. Activities can be data movement (like copying a file from Blob Storage to Azure SQL Database) or data transformation (like running a mapping data flow or a stored procedure). Pipelines can be triggered on a schedule, by an event (like a new file arriving in a folder), or manually.
The key components of orchestration on Azure include:
Pipeline: A container that holds a set of activities executed in a sequence or parallel.
Activity: A single step in a pipeline, such as 'Copy Data' or 'Execute Databricks Notebook'.
Trigger: The event that starts the pipeline, e.g., a time schedule ('every hour') or a storage event ('when a new CSV file lands').
Dataset: A named reference to the data you want to use (like 'SalesOrders' pointing to a specific table in Azure SQL).
Linked Service: A connection string that tells Azure Data Factory how to connect to a specific data store (like a storage account or a database).
Integration Runtime: The compute infrastructure that actually performs the data movement—either a managed Azure-hosted runtime or a self-hosted runtime inside your own network.
Transformation in orchestration means changing the shape, format, or value of the data as it moves. For example, you might have raw sales logs in JSON format that you need to flatten into columns, remove null rows, aggregate daily totals, and then load into a star-schema data warehouse. Azure Data Factory's 'Mapping Data Flow' allows you to design visual transformations (like joins, pivots, aggregations) that run on Azure Databricks or Spark compute underneath. Alternatively, you can use a Databricks notebook activity and pass parameters to run custom Python or Scala code within the pipeline.
Orchestration replaces the old ETL (Extract, Transform, Load) batch scripts that were hard-coded, hard to monitor, and fragile. Before orchestration tools like Azure Data Factory, data engineers would write complex shell scripts or Python programs that called database commands, waited for file locks to release, and tried to handle errors with try-except blocks. If any intermediate step failed, the entire job would crash and the engineer would only find out when the morning report was blank. Orchestration provides built-in retry logic, dependency tracking, email alerts, and a visual monitoring dashboard.
Why does Azure orchestration matter for the DP-203 exam? Because the exam asks you to design data processing solutions that are reliable, scalable, and maintainable. You need to know which activity type to use for a given task, how to chain activities with dependencies, how to implement conditional logic (if the source is empty, skip the load step), and how to handle failures with retry policies and fallback paths. The exam will also test your understanding of integration runtimes—when to use Azure-hosted versus self-hosted for on-premises data movement.
A typical orchestrated pipeline might look like this:
Trigger: A blob arrives in the 'incoming' container at 1am.
Validation activity: Check that the file has the correct schema and enough rows.
Copy data activity: Move the file from Blob Storage to Azure Data Lake Storage Gen2.
Transform activity: Execute a Databricks notebook that cleans and joins the data.
Load activity: Write the transformed data into a Synapse dedicated SQL pool table.
Notification activity: Send an email to the team with success or failure status.
Error handling: If step 2 fails, log the error in a separate table and do not proceed.
All of this is defined declaratively in the pipeline, so the engineer does not need to write custom infrastructure code. The orchestration engine (Azure Data Factory) manages parallel execution, retries, and resource allocation automatically.
For the exam, remember that orchestration is about 'when' and 'in what order' tasks happen, while transformation is about 'what change' happens to the data itself. They are two sides of the same coin, but the DP-203 exam distinguishes between designing the pipeline structure (orchestration) and implementing the data processing logic (transformation). Mastering both is essential for the 'Design and develop data processing' objective.
Define the data sources and destinations
Identify where your raw data lives (e.g., on-premises SQL Server, Azure Blob Storage, SFTP server) and where you want the transformed data to land (e.g., Azure Synapse SQL pool, Azure Data Lake Storage). Create linked services in Azure Data Factory for each source and destination. This step is crucial because it establishes the connection strings and authentication methods the pipeline will use. Without correct linked services, no data can flow.
Create datasets to reference specific data structures
A dataset points to a specific table, folder, or file within a linked service. For example, you might create a dataset called 'RawSales' that points to the 'incoming/sales/' folder in Blob Storage. Datasets act as named references that activities can use. They also allow you to define schema, column names, and file formats (CSV, Parquet, JSON). This step turns generic connections into concrete data objects.
Build the pipeline with activities in sequence
Add activities to the pipeline canvas in the order you want them to execute. Start with a 'Get Metadata' activity to check if a file exists or validate its size. Then add a 'Copy Data' activity to move it. After that, add a 'Mapping Data Flow' or 'Execute Databricks Notebook' to transform the data. End with a 'Stored Procedure' activity to load into a final table. Each activity has input dependencies you must wire together, controlling the flow.
Implement control flow and error handling
Use conditional activities (If Condition, Switch) to implement branching logic, e.g., if the source file is empty, skip the transform step and log a warning. Add an 'Until' activity to retry a step until it succeeds, with a configurable timeout. Configure retry policies on individual activities (e.g., retry 3 times). Also set up a 'Failure' path that sends an email or logs the error to a separate table. This ensures the pipeline is resilient and self-healing.
Configure triggers and publish the pipeline
Choose the right trigger for your scenario: a schedule trigger for daily processing at the same time, a tumbling window trigger for processing past time windows in order, or an event trigger for file arrivals. Attach the trigger to your pipeline and publish it. Once published, Azure Data Factory will automatically start the pipeline based on the trigger. After publishing, monitor the first few runs in the monitoring view to confirm everything works as designed.
An IT professional working as a data engineer at a retail company would use Azure Data Factory orchestration daily to manage their sales data pipeline.
Let's walk through a concrete scenario: The company has a chain of 200 stores that upload daily sales transactions to a shared SFTP server. The data arrives in CSV format with inconsistent column naming and occasional missing fields. The data engineering team is responsible for getting this raw data into a central Azure SQL Database that powers the weekly business reports.
Step by step, the engineer builds an orchestration solution:
First, they configure a self-hosted integration runtime on a virtual machine inside the company's network to connect to the on-premises SFTP server securely. They register this runtime in Azure Data Factory so it can be used by pipelines.
They create a linked service for the SFTP server, specifying the hostname, port, and credentials (using Azure Key Vault to store the password for security).
They define a dataset named 'StoreSalesRaw' that points to the folder where store CSVs land, with a wildcard file path to pick up any new file each night.
Next, they create a pipeline called 'DailySalesOrchestrator'. Inside it, they add a 'Get Metadata' activity to check the file size and last modified date to confirm a new file exists. This prevents the pipeline from failing on an empty folder.
They add a 'Copy Data' activity that moves files from the SFTP-linked dataset to a staging container in Azure Blob Storage. They configure it to copy in binary mode to preserve file integrity.
Then they add a 'Mapping Data Flow' activity that reads the raw CSV, renames columns to standard names (e.g., 'store_id' instead of 'Store Number'), removes rows where the sale amount is null, and aggregates sales by product category. This runs on a cluster of Azure Databricks compute.
Finally, they add a 'Stored Procedure' activity that executes a SQL script in Azure SQL Database to merge the aggregated data into the sales fact table, avoiding duplicates.
To handle errors, they add a 'Conditional' activity: if any step fails, the pipeline sends an alert via Azure Logic Apps to a Teams channel and logs the error in a separate 'PipelineLog' table. If it succeeds, the pipeline triggers a second pipeline that generates the daily report.
They set a schedule trigger to run at 1am every day. They also add an event trigger so that if a store uploads a file mid-day (for same-day analysis), the pipeline starts immediately, parallel to the scheduled run.
The engineer monitors the pipeline using Azure Data Factory's monitoring view, where they can see the run history, duration, and error messages for each activity. If a copy fails due to a network timeout, the pipeline automatically retries up to three times with a two-minute delay between attempts.
This orchestration replaces what used to be a fragile PowerShell script that ran as a scheduled task on a single server. If that server rebooted during processing, the data would be lost. Now, the orchestration is resilient, auditable, and fully managed by Azure. The engineer spends their time improving the data quality logic and handling edge cases, rather than fixing broken overnight jobs.
The key insight for the professional: orchestration is not just about moving data; it is about building a fault-tolerant, observable, and scalable system that keeps the business data flowing without someone watching the screen at 2am.
The DP-203 exam tests your ability to design and develop data processing solutions using Azure orchestration tools. The exam does not ask you to write code from memory, but it expects you to understand the capabilities, limitations, and appropriate use cases for each orchestration component.
The specific exam topics related to orchestration include:
Designing pipelines in Azure Data Factory or Azure Synapse Pipelines.
Choosing between activities: Copy Data, Mapping Data Flow, Execute Pipeline, Execute Databricks Notebook, Stored Procedure, and Web activity.
Configuring triggers: schedule triggers, tumbling window triggers, storage event triggers, and custom event triggers.
Implementing control flow: conditional activities (If Condition, Switch), iteration (Until, For Each), metadata-driven orchestration (lookup activities, get metadata), and parallelism.
Managing integration runtimes: Azure Integration Runtime (managed), Self-Hosted Integration Runtime (on-premises), and Azure-SSIS Integration Runtime (for SSIS package migration).
Securing orchestration: using Azure Key Vault for secrets, managed identities for authentication, and private endpoints for network isolation.
Monitoring and alerting: using Data Factory monitor, Azure Monitor, and pipeline-run metrics.
Common question traps the exam sets:
Trap 1: Confusing a Copy Data activity with a Mapping Data Flow. The Copy Data activity only copies data from source to sink without performing complex transformations. Mapping Data Flow performs transformations like joins, aggregations, and pivots. If a question asks for 'enriching data with a lookup from another table', Mapping Data Flow is the correct answer, not Copy Data.
Trap 2: Using a schedule trigger when a storage event trigger is more appropriate. If a file arrives at unpredictable times, an event trigger (BlobCreated or BlobDeleted) is the right choice. A schedule trigger would either miss arrivals or run empty pipelines.
Trap 3: Forgetting that a Self-Hosted Integration Runtime is required for on-premises data sources. The question might describe moving data from an on-premises SQL Server, and the incorrect answers propose using just the Azure IR. The correct answer will mention configuring or using the Self-Hosted IR.
Trap 4: Overlooking error handling and retry policies. The exam might present a scenario where a pipeline fails due to a transient error. The correct design includes setting a retry policy (e.g., retry 3 times with 1-minute intervals) rather than failing immediately.
Trap 5: Choosing the wrong activity to call a REST API. The 'Web' activity is for making HTTP requests, while 'Execute Databricks Notebook' or 'Lookup' are not designed for that purpose.
Key definitions to memorise for the exam:
Linked Service: Connection info (like server name, database name, credential reference) to a data store or compute resource.
Dataset: A specific resource within a data store (like a table, folder, or file).
Pipeline: A group of activities executed together.
Activity: A single execution step (copy, transform, call a function).
Trigger: What starts the pipeline.
Integration Runtime: The compute that runs the activity (provides the actual data movement or transformation).
Parameter: A value passed into a pipeline or dataset at runtime (e.g., file name).
Variable: A value that can be modified during pipeline execution (e.g., a counter).
The exam also tests your ability to choose the right orchestration pattern for a given business requirement. For example:
'Process files as they arrive' -> Use an event trigger with a pipeline that processes one file per run.
'Process all files from yesterday at 2am' -> Use a tumbling window trigger (which automatically accounts for date ranges).
'Avoid duplicate processing' -> Use a lookup activity to check if a file has already been processed, or store a watermark value.
'Run a series of pipelines in order where some can run in parallel' -> Use the Execute Pipeline activity with respective dependency settings (e.g., 'Wait for completion' on some, 'Skip' on others).
Finally, there is a strong focus on data movement scenarios: incremental loads instead of full refreshes, using watermark columns, Change Data Capture (CDC) with Azure Data Factory, and the integration with Azure Synapse Link for real-time ingestion. The exam will test whether you can design an orchestration pattern that minimises latency and cost while maximising reliability.
Orchestration in Azure Data Factory coordinates the order and timing of data movement and transformation activities without requiring custom scripting.
A pipeline is a container of activities; an activity is a single step like Copy Data or Execute Databricks Notebook.
Mapping Data Flows perform visual transformations like joins and aggregations but run on Spark clusters under the hood.
Self-Hosted Integration Runtime is mandatory for copying data from on-premises data stores to Azure.
Tumbling window triggers are ideal for processing data in fixed time intervals and guarantee exactly-once processing when configured correctly.
Always use Azure Key Vault to store credentials and secrets referenced in linked services for security.
Event triggers can miss files if not paired with idempotent processing logic or a backup schedule trigger.
The exam tests your ability to choose the correct activity type (Copy Data vs Mapping Data Flow vs Web activity) based on the transformation requirement.
These come up on the exam all the time. Here's how to tell them apart.
Schedule Trigger
Runs at a fixed clock time (e.g., 1am every day).
Does not automatically backfill missed windows.
Pipeline runs are independent of each other; no sliding window concept.
Tumbling Window Trigger
Runs for a specific time window (e.g., 15-minute intervals).
Automatically handles backfill for past windows if the trigger is created late.
Guarantees exactly-once processing for each window, with each run parameterised by window start and end.
Self-Hosted Integration Runtime
Installed on a machine inside your network or on-premises.
Required for data sources not accessible over public internet.
Managed by you (updates, scaling, high availability).
Azure Integration Runtime
Fully managed by Azure, no infrastructure to maintain.
Can only access Azure and public endpoints (HTTP/S).
Automatically scales and is always up-to-date.
Copy Data Activity
Moves data from source to sink with minimal transformation.
Supports basic schema mapping (rename columns, skip columns).
Does not support complex joins, aggregations, or pivots.
Mapping Data Flow
Designed for in-flight data transformation (joins, aggregations, pivots).
Runs on Azure Databricks clusters (Spark compute).
Visual designer for building transformation logic.
Mistake
Azure Data Factory is the same as SSIS, just in the cloud.
Correct
Azure Data Factory is a cloud-native orchestration service, while SSIS is an on-premises ETL tool (though Azure-SSIS IR can run SSIS packages in the cloud). ADF uses declarative pipelines and activities, not the package design model of SSIS.
Because both tools are from Microsoft and are used for data integration, beginners assume they are identical. The exam specifically tests the differences.
Mistake
If I use a Mapping Data Flow, I don't need Databricks or any other compute engine.
Correct
Mapping Data Flow actually runs on Azure Databricks clusters (or Spark clusters) behind the scenes. You don't manage the clusters yourself, but the compute is still there and incurs associated costs.
The visual interface of Mapping Data Flow makes it seem like a 'no-code' tool that magically processes data. Beginners do not realise the underlying compute requirement.
Mistake
A schedule trigger will always fire exactly at the configured time, regardless of time zone.
Correct
Schedule triggers in Azure Data Factory use UTC by default unless you explicitly set the time zone. If you schedule for 8am without specifying a time zone, it will run at 8am UTC, which may be 3am or 4pm in your local time.
Most people assume cloud services use their local time. The exam loves to test time zone related misunderstandings in trigger configuration.
Mistake
Event triggers guarantee that every file is processed exactly once without any extra effort.
Correct
Event triggers can miss events if the file arrives while the pipeline run is in progress or if there is a transient subscription delay. You need to implement idempotency (e.g., store processed file names) and use tumbling window triggers for guaranteed processing of time-based slices.
Event-based triggers sound simple, but cloud eventing systems are 'at least once' or 'best effort' in many cases. Beginners assume perfect delivery.
Mistake
One pipeline can only run one activity at a time.
Correct
Pipelines support parallelism by default. You can design activities that run concurrently (e.g., two copy activities in parallel) and also control dependencies (e.g., 'wait for' or 'skip').
The sequential visual representation of pipelines (usually drawn left to right) leads beginners to assume linear execution. The exam tests understanding of parallelism and dependencies.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A schedule trigger runs a pipeline at a fixed time (e.g., 1am every day). A tumbling window trigger runs the pipeline for a specific time window (e.g., every 15 minutes) and guarantees exactly-once processing for each window, including backfill support if a window was missed.
You do not need to know how to write Databricks code in depth, but you must understand that the Execute Databricks Notebook activity allows you to run a notebook as a step in a pipeline, and that Mapping Data Flows run on Databricks compute internally.
A Self-Hosted Integration Runtime is software you install on a machine inside your own network to copy data from on-premises or private cloud data stores to Azure. You need it whenever your source or destination is not directly accessible over the public internet.
Yes. You can use activities like 'Execute Databricks Notebook', 'Execute Azure Function', 'Web Activity' (to call any API), and 'Stored Procedure' to run arbitrary code or logic within a pipeline. Orchestration is about coordinating any type of task, not just data copy.
Implement idempotency by checking a processed-files table or using a Lookup activity at the start of the pipeline to see if the file name already exists in a log. Alternatively, move processed files to a different folder after successful processing.
Copy Data simply moves data from a source to a sink without transforming it (though it supports basic schema mapping). Mapping Data Flow lets you visually build complex transformations like joins, aggregations, and pivots, running on Spark.
No. When you use Mapping Data Flow, Azure Data Factory automatically manages Spark clusters for you. You define the cluster size and time-to-live settings, but you don't need to provision or decommission them manually.
You've finished Orchestrate Data Movement and Transformation. Continue through the DP-203 study guide to build a complete picture of the exam.
Done with this chapter?