Build an ETL Pipeline in Azure Data Factory

Build a pipeline that copies, cleans, and aggregates CSV data in Azure.

Introduction

30 Second Summary

Every company collects data, but raw data is messy. Files arrive with missing values, inconsistent names, and no structure. Before anyone can make decisions from that data, someone has to move it, clean it, and organize it. That process is called ETL (Extract, Transform, Load).

In this project, you will build a complete ETL pipeline in Azure Data Factory that takes messy movie rating data, filters out incomplete records, renames columns to business-friendly names, calculates average ratings per genre, and loads the polished results into a clean storage container.

What You'll Build

You will drop a raw CSV file into cloud storage, then watch your pipeline pick it up, clean it, aggregate it, and deliver polished output to a separate container on a daily schedule.

By the end of this project, you'll have:

A visual data transformation that filters bad records, renames columns, and calculates average ratings per genre, all without writing a single line of code.
A scheduled pipeline that runs automatically every day and a monitoring dashboard where you can see exactly how long each step took and whether it succeeded or failed.
Hands-on experience with Linked Services, Datasets, Copy Activities, and Mapping Data Flows, the core building blocks that every Azure data engineering role expects you to know.
Secret Mission: Add an incremental loading pattern so your pipeline only processes newly arrived files instead of reprocessing everything from scratch.

Are there any prerequisites?

You need an Azure free trial account ($200 credit for 30 days) and either Microsoft Edge or Google Chrome. No local software installations are required. Everything in this project happens in the browser through the Azure portal and Azure Data Factory Studio.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Taking a moment to name the goal helps you stay oriented as you work through each step.

Set Up Your Azure Resources

Every data pipeline needs three foundational pieces: a place to organize your resources, a place to store data, and an engine to orchestrate the flow.

In this step, you'll create all three in Azure. A Resource Group acts as a folder for your project resources. A Storage Account provides the blob containers where raw and processed data live. An Azure Data Factory instance gives you the pipeline engine that moves and transforms data between those containers.

In this step, get ready to:

Create a Resource Group and Storage Account with two blob containers.
Create an Azure Data Factory instance and open ADF Studio.
Upload sample movie data to your raw-data container.

Create a Resource Group and Storage Account

Azure groups related resources together using Resource Groups. You'll create one now, along with a Storage Account that holds your data containers.

In the Azure portal, select Storage accounts from the left-hand menu.
Click Create at the top of the Storage accounts page.
On the Basics tab, select your free trial subscription from the Subscription dropdown.
Under Resource group, click Create new.
Enter rg-adf-tutorial as the name and click OK.
In the Storage account name field, enter a globally unique name (for example, adftutstorage followed by your initials and a number). Only lowercase letters and numbers are allowed.
Select the Region closest to you. Remember this region because you'll use the same one for Data Factory.
Set Performance to Standard.

Set Redundancy to Locally-redundant storage (LRS).
Click Review + create at the bottom of the page.
Review the summary, then click Create.
Wait for the deployment to complete. You'll see a "Your deployment is complete" message.
Click Go to resource to open your new storage account.

What is a Resource Group?

A Resource Group is a logical container in Azure that holds related resources for a project. Think of it like a project folder. When you're done, you can delete the entire group to clean up everything at once.

Why Locally-redundant storage?

LRS stores three copies of your data within a single data centre. It's the cheapest redundancy option, which is perfect for a learning project where you don't need geo-replication.

Now you'll create two containers inside this Storage Account: one for raw incoming data and one for processed output.

In the left-hand menu of your storage account, scroll to Data storage and select Containers.
Click the + Container button at the top.
Enter raw-data as the container name.
Leave the Anonymous access level set to Private (no anonymous access).
Click Create.
Repeat to create a second container named processed-data with the same Private access level.

You should now see both raw-data and processed-data listed in the Containers pane.

Don't see the + Container button?

Make sure you navigated into the storage account itself (not the resource group). The Containers option is under Data storage in the storage account's own left-hand menu.

Help me find the Containers section in my Azure storage account

Create an Azure Data Factory instance

With storage ready, you need the orchestration engine that connects to it and runs your pipelines.

In the Azure portal search bar at the top, type Data Factory and select Data factories from the results.
Click Create at the top of the page.
Select your free trial subscription from the Subscription dropdown.
Under Resource group, select rg-adf-tutorial (the one you just created).
In the Name field, enter a globally unique name (for example, adf-tutorial followed by your initials).
Set Region to the same region you chose for your storage account.
Set Version to V2.

Click the Git configuration tab.
Select Configure Git later.
Click Review + create.
Review the summary, then click Create.
Wait for the deployment to complete, then click Go to resource.
On the Data Factory resource page, click Launch Studio to open Azure Data Factory Studio in a new browser tab.

ADF Studio opens with a welcome page showing quick-start options. This is the visual workspace where you'll build pipelines, data flows, and connections.

What is Azure Data Factory?

Azure Data Factory is a cloud-based data integration service. It lets you create data-driven workflows (pipelines) that move and transform data between stores, all without writing code.

ADF Studio is the visual web interface where you design pipelines by dragging activities onto a canvas, configure connections, and monitor runs.

Name already taken?

Data Factory names must be globally unique across all of Azure. If you see a validation error, try appending extra digits or a different combination of your initials.

Help me troubleshoot my Data Factory name error

Upload sample data

Your pipeline needs data to process. You'll download a sample CSV of movie ratings and upload it to the raw-data container.

Download the sample movie ratings file by opening this URL in a new browser tab:

https://raw.githubusercontent.com/kromerm/adfdataflowdocs/master/sampledata/moviesDB2.csv

Save the file as moviesDB2.csv on your computer. If your browser shows the raw text instead of downloading, right-click the page and select Save as.
Back in the Azure portal, navigate to your storage account.
In the left-hand menu, select Data storage, then select Containers.
Click on the raw-data container to open it.
Click the Upload button at the top.
Click Browse for files and select the moviesDB2.csv file you just downloaded.
Click Upload to complete the upload.

You should see moviesDB2.csv listed inside the raw-data container. This is the messy movie data your pipeline will clean and transform.

Upload button not visible?

Make sure you clicked into the raw-data container (not just the Containers list). The Upload button appears inside the container view.

Help me upload a file to my raw-data container

Before moving on, let's confirm everything is in place.

In the Azure portal search bar, type rg-adf-tutorial and select your resource group.

What do you expect to see listed here?

You should see two resources: your Storage Account and your Data Factory instance. Both live inside this single resource group.

Click on your storage account name, then select Data storage followed by Containers.
Confirm that raw-data (containing moviesDB2.csv) and processed-data (empty) both appear.

Your Azure infrastructure is ready. Next up, you'll connect Data Factory to your storage and build your first pipeline.

Build Your First Pipeline with a Copy Activity

Your storage containers are in place, with raw movie data sitting in raw-data. The next challenge: how does Azure Data Factory actually connect to your storage account and move files between containers?

ADF uses a three-part chain. A Linked Service defines the connection. Datasets point to specific files. A Pipeline with a Copy Activity performs the actual move. In this step, you'll wire up all three and prove data flows from raw to processed.

In this step, get ready to:

Create a Linked Service connecting ADF to your storage account.
Build Datasets pointing to your source and destination files.
Run a Copy Activity and verify the file lands in the processed-data container.

Create a Linked Service to your storage account

A Linked Service stores the connection details ADF needs to reach your storage account at runtime. You'll create one now using Account Key authentication.

In ADF Studio, click the Manage tab (wrench icon on the left sidebar).
Under Linked services, click + New.
Search for Azure Blob Storage in the connector list.
Select the Azure Blob Storage connector, then click Continue.
In the Name field, enter ls_blob_storage.
Set Authentication method to Account key.
Select your Azure subscription from the dropdown.
Select the storage account you created in Step 1 from the Storage account name list.

Click Test connection at the bottom of the dialog.

You should see a green checkmark with Connection successful.

Click Create to save the Linked Service.

What is a Linked Service?

A Linked Service defines the connection information ADF needs to talk to an external data store or compute resource. Think of it as a saved password entry.

Your pipeline and datasets reference this Linked Service instead of hardcoding credentials. This means you configure the connection once and reuse it everywhere.

Test connection failed?

Make sure you selected the correct storage account from the dropdown. If your subscription does not appear, confirm you are signed into the same account that owns the Azure free trial.

Help me troubleshoot this connection issue

Create source and destination Datasets

Datasets tell ADF where to look for data and where to put it. You need two: one pointing at the raw CSV file, and one pointing at the destination in the processed container.

Switch to the Author tab (pencil icon on the left sidebar).
Under Datasets, click the + icon, then select New dataset.
In the New Dataset dialog, search for Azure Blob Storage and select it.
Click Continue.
In the Select Format dialog, choose DelimitedText and click Continue.
In the Set Properties dialog, enter ds_raw_movies for Name.
Set Linked service to ls_blob_storage.
Check the First row as header checkbox.

Next to File path, click Browse.
Navigate to raw-data, select moviesDB2.csv, and click OK.
Click OK to create the dataset.

You should see ds_raw_movies listed under the Datasets section in the left panel.

Now create the destination dataset using the same flow.

Under Datasets, click + and select New dataset again.
Search for Azure Blob Storage, select it, and click Continue.
Choose DelimitedText and click Continue.
Enter ds_processed_movies for Name.
Set Linked service to ls_blob_storage.
Check First row as header.
For File path, browse to the processed-data container and enter moviesDB2.csv as the file name.
Click OK to create the dataset.

Both ds_raw_movies and ds_processed_movies now appear in your Datasets list.

What is a Dataset?

A Dataset is a named view of the data you want to use in your pipeline. It does not hold data itself.

It points to a specific file (or folder) in a data store through a Linked Service, and describes the format (CSV, JSON, Parquet, etc.).

Create the pipeline and add the Copy Activity

Now you have the connection and the data pointers. The final piece is a Pipeline with a Copy Activity that uses them to move data.

In the Author tab, click the + icon.
Select Pipeline, then Pipeline from the submenu.
In the General panel under Properties, set Name to pl_copy_movies.

An empty pipeline canvas appears. This is where you arrange activities.

In the Activities toolbox on the left of the canvas, expand the Move and Transform category.
Drag the Copy data activity onto the pipeline canvas.
With the Copy activity selected, look at the properties panel at the bottom. In the General tab, set Name to CopyRawToProcessed.
Switch to the Source tab in the activity properties. Set Source dataset to ds_raw_movies.
Switch to the Sink tab. Set Sink dataset to ds_processed_movies.

What is a Copy Activity?

The Copy Activity is ADF's built-in data mover. It reads from a source dataset and writes to a sink (destination) dataset. Under the hood, it handles serialization, parallelism, and retry logic for you.

Source is where data comes from. Sink is where data goes. You'll see these terms throughout ADF.

Run and verify the pipeline

Everything is wired up. Before you run it, take a moment to predict the outcome.

What do you think will happen when you trigger this pipeline? Will the file appear in processed-data, or will something go wrong?

Click Debug at the top of the pipeline canvas.

The Output panel appears at the bottom. Watch the status column update from In Progress to Succeeded.

Switch to the Azure portal tab in your browser.
Navigate to your storage account.
Click Data storage in the left sidebar, then select Containers.
Click the processed-data container.

You should see moviesDB2.csv now listed inside the processed-data container. Your pipeline successfully copied the file from raw to processed.

Pipeline status shows Failed?

Click the activity name in the Output panel to see the error details. Common causes:

The file path in ds_raw_movies doesn't match the actual container or file name. Double-check that you selected raw-data/moviesDB2.csv.
The Linked Service was not saved correctly. Go to the Manage tab and re-run Test connection on ls_blob_storage.
The processed-data container does not exist. Return to the storage account and confirm both containers are present.

Help me troubleshoot my failed pipeline run

You just proved data can flow through ADF from source to destination. But open that copied file and look at the data. It's still messy, with missing genres and no aggregation. Next up, you'll add a Mapping Data Flow that cleans and transforms this data automatically.

Transform Data with a Mapping Data Flow

Your pipeline can move data from one container to another. That is a good start, but the raw CSV still contains incomplete records and flat rows with no aggregation.

In this step, you will build a Mapping Data Flow that visually cleans, filters, and reshapes your movie data without writing code. By the end, your pipeline will output average ratings grouped by genre into a dedicated folder.

In this step, get ready to:

Create a Mapping Data Flow that filters out incomplete records, renames columns, and aggregates ratings by genre.
Connect the Data Flow to your existing pipeline so it runs after the Copy Activity.
Debug the full pipeline and confirm clean aggregated output appears in a new folder.

Create the data flow and configure the source

A Mapping Data Flow is a visual transformation canvas. You chain transformations together left to right, starting with a source and ending at a sink.

In ADF Studio, switch to the Author tab (pencil icon on the left sidebar).
Click the + icon, then select Data flow, then Data flow again.
Name the data flow df_transform_movies.
Click Add Source on the canvas.
In the Output stream name field, enter RawMovies.
Set the Dataset dropdown to ds_raw_movies.

You should see a single blue RawMovies node on the canvas with a small + icon to its right.

What is a source in a data flow?

A source is where data enters the flow. It points to a dataset you already created. Every transformation you add after it receives rows from this source.

Add the Filter transformation

The raw CSV has rows where the genre or rating is missing. A Filter transformation drops those incomplete rows before they reach your aggregation.

Click the + icon to the right of the RawMovies node.
Select Filter from the transformation list.
Name it FilterIncomplete.
Click the expression box next to Filter on to open the expression builder.
Enter this expression and click Save and finish:

!isNull(genres) && !isNull(Rating)

What does this filter expression do?

The isNull() function checks whether a column is empty. The ! operator flips it to "is NOT null." Combined with && (AND), this expression keeps only rows where both genres and Rating have values.

Add the Select transformation to rename columns

Column names like movie and genres are vague. A Select transformation lets you rename them to business-friendly names without changing the data itself.

Click the + icon to the right of FilterIncomplete.
Select Select from the transformation list.
Name it RenameColumns.
In the Name as field next to movie, type MovieTitle.
In the Name as field next to genres, type Genre.
Leave Rating unchanged.

Your canvas now shows three connected nodes: RawMovies flowing into FilterIncomplete flowing into RenameColumns.

Add the Aggregate transformation

Now you will calculate average ratings per genre. The Aggregate transformation groups rows by a column and computes summary values.

Click the + icon to the right of RenameColumns.
Select Aggregate from the transformation list.
Name it AvgRatingByGenre.
In the Group by tab, select Genre from the column dropdown.
Switch to the Aggregates tab.
In the Column field, type AverageRating.
Click the expression box and enter this expression, then click Save and finish:

avg(toFloat(Rating))

What does this aggregate do?

The toFloat(Rating) converts the text-based rating to a number. Then avg() computes the mean across all rows sharing the same Genre value. The output has one row per genre with a calculated average.

Add the Sink transformation and create the output dataset

A sink is the final destination for your transformed data. You will create a new dataset that writes output to a genre-ratings folder inside processed-data.

Click the + icon to the right of AvgRatingByGenre.
Select Sink from the transformation list.
Name it ProcessedOutput.
Next to the Dataset dropdown, click + New.
Search for Azure Blob Storage, select it, then choose DelimitedText format.
Click Continue.
Name the dataset ds_genre_ratings.
Set the Linked service to ls_blob_storage.

Browse the file path to processed-data/genre-ratings/ and leave the file name blank.
Check First row as header.
Click OK.

Your completed data flow canvas should now show five connected nodes from left to right: RawMovies, FilterIncomplete, RenameColumns, AvgRatingByGenre, and ProcessedOutput.

Why leave the file name blank?

When you leave the file name empty, ADF generates output files automatically inside the folder. This is the standard pattern for data flows that produce partitioned output at scale.

Add the data flow to your pipeline

The data flow exists on its own. To run it as part of your pipeline, you need to add a Data Flow activity and connect it after the Copy Activity.

Switch to the Author tab and open your pipeline pl_copy_movies.
In the Activities toolbox on the left, expand Move and Transform.
Drag the Data flow activity onto the pipeline canvas, below the existing Copy activity.
Name the activity TransformMovies.
In the Settings tab at the bottom, set the Data flow dropdown to df_transform_movies.
Hover over the green box on the right edge of CopyRawToProcessed and drag the green arrow to TransformMovies.

The green arrow means "on success." The transform will only run after the copy completes successfully.

Why chain activities with the green arrow?

The arrow creates a dependency. If the Copy Activity fails, the Data Flow will not execute. This prevents transforming stale or missing data.

Debug and verify the output

Before you click Debug, think about what you expect to happen. The pipeline now has two activities. What do you think the output will contain?

Click Debug at the top of the pipeline canvas.
Watch the Output panel at the bottom. The Copy Activity finishes first.

Why does the Data Flow take longer?

Mapping Data Flows run on managed Apache Spark clusters. The first debug run spins up a cluster, which takes 2-4 minutes. Subsequent runs in the same session reuse the cluster and finish faster.

Wait for both activities to show Succeeded in the Output panel.
In the Azure portal, navigate to your storage account.
Go to Containers and open the processed-data container.
Open the genre-ratings folder.
Click on the output CSV file inside. Select Edit to view its contents.

You should see clean data with two columns: Genre and AverageRating. Each row shows one genre with its calculated average rating.

Don't see the genre-ratings folder?

Check the Output panel in ADF Studio. If the Data Flow activity shows Failed, click on the error icon to read the message. A common cause is a typo in the filter or aggregate expression.
Confirm that the ds_genre_ratings dataset path is processed-data/genre-ratings/ with no file name. If you accidentally typed a file name, ADF writes to that single file instead of generating output in the folder.
Make sure the green success arrow connects CopyRawToProcessed to TransformMovies. Without it, the Data Flow may not run.

If you are still stuck, help me debug my Data Flow run failure in Azure Data Factory.

Your pipeline now does more than move data. It filters incomplete records, renames columns to business-friendly names, and calculates aggregated ratings per genre. Next up, you will schedule this pipeline to run automatically and learn to monitor it like a production data engineer.

Schedule and Monitor Your Pipeline

Your pipeline now copies and transforms data on demand. Every time you want fresh results, you click Debug manually.

A production pipeline runs itself. Triggers automate execution on a schedule or in response to events. Monitoring lets you catch failures before your stakeholders notice. In this step, you will make your pipeline production-ready by adding a daily schedule and confirming it through the monitoring dashboard.

In this step, get ready to:

Create a schedule trigger that runs your pipeline daily.
Publish all ADF resources to activate the trigger.
Explore the monitoring dashboard to view pipeline and trigger runs.

Create a schedule trigger

A trigger tells ADF when to run your pipeline without manual intervention. You will create a daily schedule trigger that fires once every 24 hours.

In ADF Studio, switch to the Author tab (pencil icon on the left).
Open your pipeline pl_copy_movies.
Click Add trigger at the top of the pipeline canvas.
Select New/Edit.
In the Add triggers panel, click Choose trigger, then click + New.

The New Trigger configuration window appears. Fill in the following settings:

Set Name to trg_daily_movies.
Confirm Type is set to Schedule.
Set Start date to today's date.
Set Recurrence to Every 1 Day.
For the Activated option, select Yes.
Click OK.

What does this trigger do?

A schedule trigger fires at a fixed interval you define. Setting the recurrence to every 1 day means your pipeline will execute once per day at the start time you chose.

The trigger only takes effect after you publish. Saving in the UI alone does not activate it.

On the trigger run parameters page that appears, click OK (your pipeline has no parameters to configure).
Click Publish all at the top of ADF Studio to save and activate all your resources.
Wait for the Successfully published notification to appear.

Why publish all?

Publishing deploys your changes from the ADF Studio drafting environment to the live Data Factory service. Until you publish, your trigger exists only as a draft. After publishing, the trigger is active and will fire at the next scheduled time.

Not seeing the publish button?

Make sure you are on the Author tab (pencil icon). The Publish all button appears at the top of the Author tab, not inside the Monitor or Manage tabs.

help me publish my ADF resources

Explore the monitoring dashboard

With everything published, you can now see your pipeline history and confirm the trigger is active. The Monitor tab is where data engineers track every pipeline execution in production.

Click the Monitor tab (gauge icon on the left sidebar).
You land on Pipeline runs. This view shows your debug runs from the previous steps with a status of Succeeded.

Click on the pipeline name pl_copy_movies in one of the runs to drill into the activity runs.
Examine the activity-level details: each activity shows its Duration, Status, and data read/written.

What do the activity run details show?

Each row represents one activity inside your pipeline. You can see how long the Copy Activity took versus the Data Flow activity. The Data Flow is typically slower because it spins up a Spark cluster.

The Triggered By column on the Pipeline runs view tells you whether a run was started by a manual debug, a Trigger Now action, or a scheduled trigger.

Click All pipeline runs at the top to return to the pipeline runs list.
Select Trigger runs on the left side of the Monitor view to switch to the trigger history.

Before you check the trigger status, what do you expect to see here? The trigger was just published moments ago and is set to run once per day.

Confirm that trg_daily_movies appears in the list with an Active status.

The trigger is listed but has not fired yet. It will execute at the next scheduled time (tomorrow at the start time you set). The important thing is that the status reads Active, which proves your pipeline is now automated.

Don't see your trigger in the list?

Make sure you clicked Publish all and received the success notification. Triggers only appear in the Monitor view after publishing.

Also confirm you are on the Trigger runs view (not Pipeline runs). The two views show different information.

help me find my trigger in ADF Monitor

Secret mission

Add Incremental Loading

Real-world pipelines don't re-process all historical data every time they run. In this secret mission, you'll build a second pipeline that uses a watermark pattern to detect and copy only newly arrived files. This is the same incremental load pattern tested on data engineering certifications like DP-700.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. Your scheduled trigger fires daily, so leaving resources active will consume a small amount of your Azure free trial credit.

Cost warning

Your active trigger runs your pipeline daily. Each run includes a Data Flow activity that spins up a Spark cluster and charges per vCore-hour. With minimal data this stays well under $1/month, but it does consume your free trial credit over time.

If you are not continuing immediately, choose Pause or Delete below to avoid any charges.

Resources you used:

Resource Group rg-adf-tutorial (contains all resources below).
Storage Account with three containers: raw-data, processed-data, and incremental-output.
Azure Data Factory instance with pipelines, data flows, and linked services.
Schedule trigger trg_daily_movies (currently Active, fires every day).

Keep everything running

No action needed. Choose this if you are still actively building or plan to continue to a follow-up project soon.

Your trigger will continue to fire daily. With the small sample data in this project, costs stay well under $1/month against your $200 free trial credit.
Monitor your credit balance in the Azure portal under Cost Management + Billing.

Pause - I'll come back to this later

Stop your scheduled trigger to prevent automatic pipeline runs. Your resources stay intact so you can resume anytime.

In ADF Studio, click the Manage tab (wrench icon) on the left.
Under Author, select Triggers.
Hover over trg_daily_movies and click the Stop button.
Click Publish all at the top to save the change.

With the trigger stopped, no pipeline runs will fire and no Data Flow compute charges will accumulate. Storage costs for the small CSV files are fractions of a cent per month.

Delete - I don't want to use this again

Remove all project resources in one action by deleting the resource group. This is irreversible.

In the Azure portal, search for Resource groups in the top search bar and select it.
Click rg-adf-tutorial from the list.
Click Delete resource group at the top of the page.
Type rg-adf-tutorial in the confirmation field to confirm.
Click Delete.

This removes the Storage Account, Data Factory instance, all pipelines, data flows, triggers, linked services, and datasets in one operation. You will need to rebuild from scratch if you want to continue later.

Nice Work!

You just built a fully operational ETL pipeline in Azure Data Factory that transforms messy CSV data into clean, aggregated output on an automated schedule.

You've learned how to:

Build a complete ETL pipeline that copies raw movie data from Azure Blob Storage and connects storage to ADF through Linked Services and Datasets.
Create a Mapping Data Flow that filters incomplete records, renames columns, and calculates average ratings per genre, all without writing code.
Automate pipeline execution with a scheduled trigger and track runs, activity duration, and data throughput through the ADF monitoring dashboard.
Secret Mission: Implemented an incremental loading pattern using watermark dates and control flow activities, a production pattern tested on data engineering certifications.

Ready to quiz yourself?