# Implement Azure Data Factory Pipelines

> Chapter 6 of the Courseiva DP-203 curriculum — https://courseiva.com/learn/dp-203/implement-azure-data-factory-pipelines

**Official objective:** 2.2 — Design and develop data processing

## Introduction

Azure Data Factory (ADF) is a cloud-based Extract, Transform, and Load (ETL) service. It solves the problem of moving and transforming raw data from many different sources into a single, analytics-ready format without writing complex custom code. For the DP-203 exam, understanding how to implement these pipelines is essential, as it represents the core orchestration mechanism for data engineers working on the Microsoft Azure platform.

## The Automated Bakery Assembly Line Analogy

Before a bakery owner can deliver a fresh loaf to a customer, ingredients must first arrive from a supplier (a trigger event happens). That event then activates an entire sequence of automated machines that transforms those raw ingredients into a finished, packaged loaf.

In this bakery, the owner doesn't personally mix the dough, knead it, let it rise, shape it, bake it, cool it, or wrap it. Instead, she designs a 'master recipe'—a series of instructions stored on a central computer. The computer oversees a network of specialised machines: one that dumps flour and water into a mixer, another that monitors the dough temperature, a third that operates the oven conveyor belt, and a final robot that seals the plastic bag and prints the label. This entire orchestrated sequence is the 'pipeline'.

If the bakery suddenly needs to produce twice as many loaves or switch from white bread to whole wheat, the owner doesn't rebuild the kitchen. She simply edits the master recipe—adding a new machine to grind the wheat or changing the oven timer—and the system adapts. Critically, each machine can be used in multiple different recipes. The mixer can also be part of a cookie recipe or a cake recipe. The same equipment is reused across different production lines, just like an Azure Data Factory pipeline reuses the same 'Copy Data' activity in different workflows. If a single machine breaks down (your data source server goes offline), the master recipe can be paused, the machine repaired, and then the entire line restarted from the exact point it stopped, without wasting any ingredients. This isn't manual labour; it's fully automated, scheduled, and monitored from a single dashboard.

## Core explanation

At its simplest, Azure Data Factory is a serverless data integration service. It is designed to ingest, prepare, and transform data at scale. Think of it as the 'glue' that connects your data sources (like an on-premises SQL Server database, a file in Azure Blob Storage, or a Twitter API) to your data destinations (like a data warehouse or a data lake).

The fundamental building block of ADF is the **Pipeline**. A pipeline is a logical grouping of **Activities** that together perform a unit of work. If a pipeline is a recipe, an activity is a single instruction in that recipe—like 'bake at 350 degrees' or 'add three eggs'. Activities are the actual 'doing' part: they copy data, run a SQL query, call a web service, or execute a machine learning model.

There are three main types of activities:

- **Data Movement Activities**: The most common is the 'Copy Data' activity. This moves data from a source to a sink (destination). For example, copying a CSV file from an FTP server into an Azure Data Lake Storage Gen2 container.
- **Data Transformation Activities**: These modify the data after it has been moved. Examples include 'Data Flow' (a visual tool for transforming data without coding), 'Stored Procedure' (firing a stored procedure in SQL), 'HDInsight Hive', and 'Databricks Notebook' (running a Spark job).
- **Control Flow Activities**: These orchestrate the pipeline itself. They include 'If Condition' (branching), 'ForEach' (looping over a list), 'Wait' (pausing for a fixed time), 'Set Variable', and 'Execute Pipeline' (calling a child pipeline from a parent pipeline).

To connect to any external system—whether it's a database on a server in your office or a cloud API—ADF uses a **Linked Service**. A linked service is essentially a connection string. It tells ADF: 'The source database is named AdventureWorks, running on server SQL01, and you connect using this username and password.' It is a reference to the resource, not the actual data.

Once you have a linked service to your source and a linked service to your sink, you use a **Dataset** to represent the specific structure of the data you want to read or write. A dataset is a named view of the data that points to the actual data within the linked service. For example, the linked service points to the SQL server, and the dataset points to the specific table 'Sales.Orders' within that server.

The entire orchestration is managed through **Triggers**. A trigger tells a pipeline when to run. You have three basic trigger types:

- **Schedule Trigger**: Runs the pipeline at a wall-clock time (e.g., every day at 2:00 AM).
- **Tumbling Window Trigger**: Runs on a periodic interval starting from a specified start time. It is self-pacing: if one run fails, the next one still executes. It keeps track of its own state, which is critical for 'exactly once' processing of time windows.
- **Event-based Trigger**: Runs the pipeline when an event occurs, such as when a new file lands in an Azure Blob Storage container.

Why does all this exist? Before ADF, data engineers had to write custom scripts (Python, PowerShell) or use enterprise schedulers that were hard to monitor and maintain. Every source needed its own custom connector logic. ADF provides over 90 built-in connectors, a web-based monitoring UI, and automatic retries for transient failures. It replaces fragile, hand-coded data-moving scripts with a robust, visual, fully managed service that can handle petabytes of data.

## Real-world context

Imagine you are a data engineer at a retail company called 'Northwind Traders'. Your Chief Data Officer wants a daily report showing total sales by product category for the previous day, and it must be in the company's Azure Synapse Analytics data warehouse by 7:00 AM before the board meeting. The data originates in three different places: (1) an on-premises SQL Server for North American stores, (2) a CSV file uploaded by the European logistics team to a SharePoint Online folder, and (3) an Azure SQL Database for the online web store.

Here is exactly what you do with Azure Data Factory:

1.  **Define the Linked Services**: You create three linked services. One to the on-premises SQL Server (using the Self-Hosted Integration Runtime—a small software agent installed on the company's server that bridges the on-premises network to Azure). One to SharePoint Online using the HTTP connector. One to the Azure SQL Database. Finally, you create a linked service to your Azure Synapse Analytics workspace.

2.  **Define the Datasets**: You create a dataset for the 'Stores_Sales' table in the SQL Server, a dataset representing the CSV structure in SharePoint, and a dataset for the 'Online_Sales' table in Azure SQL. You also create a dataset for the destination table in Synapse called 'Daily_Sales_Aggregated'.

3.  **Build the Pipeline**: You create a new pipeline named 'DailySalesIngestion'. Inside this pipeline, you use three 'Copy Data' activities in parallel (using a 'ForEach' loop that iterates over a list of source datasets) to move all sales data from the three sources into a staging folder in Azure Data Lake Storage Gen2. Next, you add a 'Data Flow' activity that reads these staging files, joins them by product category and store ID, aggregates the total sales amount, and writes the result to a clean CSV. Finally, you add a 'Stored Procedure' activity that runs a Synapse stored procedure to bulk insert that CSV into the 'Daily_Sales_Aggregated' table in the data warehouse.

4.  **Add a Tumbling Window Trigger**: You create a tumbling window trigger that runs this entire pipeline every day at 1:00 AM. You configure a window size of 24 hours, which ensures that the pipeline always processes exactly one day of data. If the pipeline fails because the on-premises SQL server was down for maintenance, the trigger will keep retrying (you configure a retry policy of 3) and still execute the next day's run correctly. No data is lost.

5.  **Monitor in Real Time**: The morning of a failure, you open the Azure Data Factory Monitor. You can see a red exclamation mark next to the failed run. You click into it, see that the 'Copy from SQL Server' activity failed due to a network timeout. You restart the pipeline from the point of failure—no need to re-copy the already-successful data from SharePoint or Azure SQL. The board meeting has its data by 7:03 AM.

This real scenario demonstrates that ADF is not just about moving files; it is about orchestrating a complex, heterogeneous workflow with error handling, monitoring, and scheduling, all without writing a single line of traditional application code.

## Exam focus

The DP-203 exam tests 'Implement Azure Data Factory Pipelines' primarily through scenario-based questions. You need to understand the 'why' as much as the 'what'. The examiners are very specific about which service is appropriate for which job.

**Key exam topics to master:**

- **Integration Runtimes (IR)**: The exam loves testing this. You must know the three types: Azure (for cloud-to-cloud), Self-Hosted (for on-premises or VM-to-cloud), and Azure-SSIS (for lifting and shifting SQL Server Integration Services packages). They will give you a scenario like 'You need to copy data from an on-premises server to Azure Blob Storage' and ask what you need. The answer is a Self-Hosted Integration Runtime.
- **Copy Activity Performance**: They test the performance tuning knobs: 'Data Integration Units' (DIUs, the power of the copy engine), parallel copies, and staging. If a copy is slow, you increase DIUs or add a staging area with Azure Blob Storage for the copy to use as a temporary checkpoint.
- **Control Flow vs Data Flow**: This is a major testing area. Control flow is sequencing activities (If, ForEach, Wait). Data flow is the 'Mapping Data Flow' feature for transformations executed at scale on Spark. If the question mentions 'transforming data using a visual interface without coding', the answer is Mapping Data Flow. If it mentions 'looping through a list of filenames', that is a control flow (ForEach) activity.
- **Trigger Behaviour**: You need to understand the differences between schedule, tumbling window, and event triggers. The trap question is: 'Which trigger guarantees exactly once processing for a fixed time window?' Answer: Tumbling window trigger. Schedule triggers do not have this guarantee.

**Common trap patterns:**

- **'Twist'**: The question describes a scenario where you need to process files incrementally, but they set a trap that the files arrive out of order. They then ask for the best trigger. The correct answer is not 'Event trigger' (which runs once per file and could cause race conditions), but rather a 'Tumbling window trigger' that scans a folder for files that arrived within a specific time window.
- **'Parameterisation'**: They give you a pipeline that works for one dataset and ask you how to reuse it for ten similar datasets. The trap is that novices think they need to create ten pipelines. The correct answer is to parameterise the linked service and dataset (using @pipeline().parameters.DataSetName) and pass the parameter from a master pipeline.
- **'Pipeline variable vs activity output'**: They test whether you know that pipeline variables exist only within the pipeline scope and cannot be shared between pipelines. If you need to pass a value from one pipeline to another, you must use the 'Execute Pipeline' activity with parameters.

**Key definitions to memorise:**

- **Linked Service**: Connection string (like a file path).
- **Dataset**: Named representation of data (like a specific file or table).
- **Activity**: The actual execution step (Copy, Pipeline, Data Flow).
- **Pipeline**: The orchestration container for activities.
- **Trigger**: The scheduler that starts a pipeline run.

## Step by step

1. **Define the Source and Destination Linked Services** — Create connection strings for every source and destination system. For example, create a linked service for your Azure Blob Storage account (source) and one for your Azure Synapse Analytics workspace (destination). This step is critical because without a valid linked service, the pipeline cannot connect to any external resource.
2. **Create Datasets for the Source and Sink** — Point to the specific files or tables you want to read and write. For the source dataset, specify the container and file path (e.g., 'input/customers.csv'). For the sink dataset, specify the target table (e.g., 'Staging.Customers'). Datasets make your pipeline reusable: you can change the dataset without rewriting the pipeline logic.
3. **Assemble the Pipeline Activities** — Drag a 'Copy Data' activity onto the pipeline canvas. Configure its source to point to your source dataset and its sink to point to your destination dataset. Then add a second activity, such as a 'Stored Procedure' activity, that runs after the copy completes. Connect the activities with a success line (green arrow). This forms the sequential execution flow.
4. **Configure Error Handling and Retry Policies** — In the activity settings, set the 'Retry' count to 3 and the 'Retry interval' to 300 seconds. Also add a failure path (red arrow) from the Copy activity to an 'Email Notification' activity that sends a message to the operations team. Without this, a transient network failure will stop the entire pipeline.
5. **Add a Trigger and Validate** — Create a tumbling window trigger set to run daily at 2:00 AM with a window size of 1 day. Click 'Publish all' to deploy. Then use the 'Debug' button to test the pipeline instantly. Validation prevents syntax errors and missing connections; debug lets you see if the logic works without waiting for the schedule.
6. **Monitor and Alert on Execution** — Open the Azure Data Factory Monitor from the portal. Examine the 'Pipeline runs' view. If a run fails, click it to see which activity failed and the exact error message. Configure alerts (e.g., 'Send email when pipeline run fails') using Azure Monitor. Monitoring is where you turn a one-time script into a production-grade solution.

## Comparisons

### Pipeline vs Data Flow

**Pipeline:**
- Controls the sequence and logic (If conditions, loops) of activities.
- Does not perform data transformation itself; it orchestrates other activities.
- Billed per orchestration run (per activity execution).

**Data Flow:**
- Is a specific type of activity within a pipeline.
- Performs visual, Spark-based data transformations (joins, aggregations, pivots).
- Billed per Data Flow cluster core hour used.

### Schedule Trigger vs Tumbling Window Trigger

**Schedule Trigger:**
- Runs on a fixed calendar schedule (e.g., every Monday at 8 AM).
- Does not maintain a state of what time windows have been processed.
- Can be used for any regular interval, but offers no guarantee against overlapping runs.

**Tumbling Window Trigger:**
- Runs on a periodic interval from a fixed start time (e.g., every 1 hour, starting 2020-01-01 00:00).
- Maintains its own state and guarantees exactly once processing per window.
- Ideal for incremental data loading where you need to process non-overlapping time windows.

### Azure Integration Runtime vs Self-Hosted Integration Runtime

**Azure Integration Runtime:**
- Runs entirely in Microsoft's Azure cloud.
- Can only connect to data sources that are publicly accessible or within a peered Azure VNet.
- No installation required; it is a fully managed service.

**Self-Hosted Integration Runtime:**
- Is installed on a local machine or VM inside your corporate network.
- Used to connect to on-premises data sources or those behind a firewall.
- Requires manual installation, patching, and scaling.

### Parameter vs Variable

**Parameter:**
- Defined at the pipeline level and passed in at execution time from a trigger or parent pipeline.
- Value is set once at run start and cannot be changed during pipeline execution.
- Used to make pipelines reusable (e.g., pass a different file name each run).

**Variable:**
- Defined within a pipeline and can be changed during execution using 'Set Variable' activities.
- Value can be updated multiple times as the pipeline runs (e.g., increment a counter in a loop).
- Used to store temporary state or intermediate results within a single pipeline run.

## Diagram

_This diagram shows how a single pipeline orchestrates three parallel copy activities, a central transformation, and a final load into a data warehouse._

```mermaid
flowchart TD
    A[Trigger: Event or Schedule] --> B[Pipeline: DailySalesIngestion]
    B --> C[Activity 1: Copy Data from SQL Server]
    B --> D[Activity 2: Copy Data from SharePoint]
    B --> E[Activity 3: Copy Data from Azure SQL]
    C --> F[Data Flow: Join & Aggregate]
    D --> F
    E --> F
    F --> G[Activity 4: Stored Procedure - Insert into Synapse]
    G --> H[Destination: Azure Synapse Table]
```

## Common misconceptions

- **Misconception:** Data Factory stores the actual data inside the pipeline or dataset objects. **Reality:** Data Factory only stores metadata—the connection information, schema definitions, and transformation logic. The actual data remains in the source and sink systems. ADF is an orchestrator, not a data store. (Beginners think that creating a dataset means they have 'imported' the data into ADF. They confuse the metadata model with the actual storage layer.)
- **Misconception:** A pipeline can only run on a set schedule (e.g., every hour). **Reality:** Pipelines can be triggered by events (e.g., a file landing in Blob Storage), by other pipelines (chaining), or manually. Scheduling is only one option. (Early tutorials often start with a schedule trigger, so beginners assume that is the only way. The exam tests event-based triggers heavily.)
- **Misconception:** You must write complex code (Python or C#) to transform data in ADF. **Reality:** ADF's Mapping Data Flow allows you to transform data visually using a drag-and-drop interface that generates Spark code behind the scenes. You can also run custom code (Python, Scala) via Databricks or HDInsight activities, but it is not required for most transformations. (The name 'Data Factory' sounds like a programming framework. Beginners assume writing code is mandatory, and they miss the visual transformation option that the exam explicitly tests.)
- **Misconception:** Each pipeline can only use one type of activity (copy OR transform). **Reality:** A single pipeline can mix any number of activities in any order, including parallel branches, conditional logic, and loops. You can copy from one source, run a Data Flow transformation, and then call a stored procedure—all in one pipeline. (Tutorials often show simple single-activity pipelines. Novices then think this is a limitation, not a simple example.)
- **Misconception:** Data Factory is a data warehouse or a database. **Reality:** Data Factory is a data integration and orchestration service. It moves and transforms data, but it does not store data long-term. You still need Azure Synapse, SQL Database, or Blob Storage to hold the actual data. (Microsoft's branding groups services under the 'Azure Data' umbrella. Beginners assume that since it's a 'factory', it must produce and store the final product.)
- **Misconception:** Self-Hosted Integration Runtime and Azure Integration Runtime are interchangeable. **Reality:** They serve completely different network environments. Azure IR runs in Microsoft's cloud and can only access services exposed to the public internet or connected via virtual networks. Self-Hosted IR is software you install on a local server to bridge your corporate network with Azure. If your source is on-premises, you must use Self-Hosted IR. (The word 'Integration Runtime' sounds generic. Beginners treat it as a single concept, not realising the physical deployment location dictates which one to use.)

## Key takeaways

- A pipeline groups activities; an activity performs a single unit of work like copying data or running a query.
- Linked Services hold connection strings to external resources; Datasets represent the specific data structure within that resource.
- You must use a Self-Hosted Integration Runtime to connect to any data source that is not directly accessible from the public internet.
- Tumbling window triggers guarantee exactly once processing for a fixed time interval; schedule triggers do not.
- Mapping Data Flow provides a visual, no-code interface for transforming data at scale using Spark.
- Data Factory stores only metadata; the actual data never resides inside the service itself.
- Pipeline parameters allow you to reuse the same pipeline for different scenarios by passing in values at runtime.

## FAQ

**Is Azure Data Factory free to use?**

No. You are billed based on the number of orchestration runs (how many times activities execute) and the number of Data Flow cluster cores used. There is a free monthly allowance of runs, but after that, standard pay-as-you-go rates apply.

**Can I copy data from a source that is behind my company's firewall?**

Yes, but you must install a Self-Hosted Integration Runtime on a machine inside your corporate network that has outbound internet access. This small software agent acts as a secure bridge between your local data sources and Azure.

**What is the difference between a Pipeline and a Data Flow?**

A Pipeline is the overall orchestration container that controls the order of activities. A Data Flow is a specific type of activity within a pipeline that is used to transform data visually without coding. You cannot run a Data Flow alone; it must sit inside a Pipeline.

**How do I pass a value from one pipeline to another?**

Use the 'Execute Pipeline' activity. In the child pipeline, define a pipeline parameter (e.g., 'InputDate'). In the parent pipeline, when calling the child, populate that parameter with a value (e.g., @formatDateTime(utcnow(), 'yyyy-MM-dd')).

**What happens if my Copy Data activity fails halfway through a large file?**

By default, the entire activity fails and rolls back. However, you can enable 'Fault tolerance' by setting the 'Skip incompatible rows' option. This logs the bad rows to a separate file and continues with the good rows.

**Do I need to write SQL to use Data Factory?**

No, not for basic copying. For simple copy jobs, you use the visual 'Copy Data' wizard. For transformations, Mapping Data Flow is also visual. You only need SQL if you choose to use a 'Stored Procedure' activity or 'Lookup' activity that queries a database.

**Can I schedule a pipeline to run on the last Sunday of every month?**

Not directly with a basic schedule trigger. You would need to create a 'Schedule Trigger' that runs daily, and inside the pipeline, use an 'If Condition' activity that checks if today is the last Sunday of the month. If true, proceed with the main logic; otherwise, skip.

## Check your understanding

1. **Which Integration Runtime is required to copy data from a SQL Server database running on a server in your company's on-premises data centre?**

   Answer: You must use a Self-Hosted Integration Runtime installed on a machine in the on-premises network that can connect to both the SQL Server and Azure outbound.

2. **What is the difference between a Dataset and a Linked Service?**

   Answer: A Linked Service holds the connection string (how to connect). A Dataset specifies the specific data structure within that connection (which table or file).

3. **Which trigger type guarantees that a pipeline will process each time window exactly once, even if a run fails?**

   Answer: The Tumbling Window trigger guarantees exactly once processing for each fixed time window.

4. **Can a pipeline run without any trigger?**

   Answer: Yes. You can manually trigger a pipeline run from the Azure portal, using Azure PowerShell, or via the REST API.

5. **If you need to transform data by joining two CSV files and aggregating sales by region, which type of activity should you use?**

   Answer: You should use a Mapping Data Flow activity, which provides a visual designer to perform joins and aggregations without writing code.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-203/implement-azure-data-factory-pipelines