Azure Official Partner Build ETL pipelines with Azure Data Factory

Azure Account / 2026-05-21 17:12:38

Introduction: ETL Is Not Dead, It’s Just Moving to the Cloud

Azure Official Partner If you’ve ever tried to stitch together data from a dozen sources using a handful of scripts that “work on my machine,” congratulations: you’ve lived the ETL dream. Or nightmare. The dream is moving data into a usable shape. The nightmare is moving that data reliably, securely, and without waking up at 2 a.m. because an SFTP server decided to rename files “for fun.”

Azure Official Partner Azure Data Factory (ADF) helps tame that chaos. It’s a service for building data integration pipelines that can extract data, transform it, and load it into destinations like Azure SQL, data lakes, or warehouses. You design workflows visually, parameterize them for reuse, and monitor everything through built-in tooling. Think of ADF as the conductor of your data orchestra: it might not play the instruments, but it definitely tells them when to come in—on time—and in tune.

In this article, you’ll learn how to build ETL pipelines with Azure Data Factory, including the core components (pipelines, datasets, linked services), the most common activities (copy and transform), and the practical concerns that actually matter in real life (scheduling, errors, monitoring, security, and schema changes). We’ll keep it readable, structured, and original—like your best coworker who actually documents things.

What Is ETL, and Why Should You Care?

ETL stands for Extract, Transform, Load. In plain terms:

Extract: pull data from a source (database, API, files, events).
Transform: clean, map, reshape, validate, join, aggregate, and generally turn messy data into “human-friendly” data.
Load: write the results into a target system for analytics or application use.

Why care? Because ETL is where “analytics” begins. If your data is late, wrong, or inconsistently shaped, your dashboards will be confident and incorrect, which is the worst kind of incorrect. In other words: you want ETL that’s repeatable, trackable, and resilient.

A Data Factory pipeline is basically a repeatable ETL workflow. It runs on a schedule or when triggered, uses activities to move and transform data, and provides monitoring and operational visibility. It’s the difference between “we’ll rerun it and hope” and “we have logs, alerts, and a plan.”

Azure Data Factory: The Cast of Characters

Before building anything, it helps to know the main objects in ADF. If ETL is a restaurant, these are the kitchen, the menu, the suppliers, and the ticket system.

Data Factory

A data factory is the container where you build and manage pipelines. It belongs to an Azure subscription and has settings for integration runtime, managed virtual networks (if needed), and deployments.

Pipelines

Pipelines are the workflows. They contain activities (like “copy from source to sink,” “run a data flow,” or “call another pipeline”). Pipelines can be parameterized and executed on-demand or via triggers.

Datasets

Datasets define the shape of your data at endpoints. For example, a dataset might describe a table in SQL Server or a folder of Parquet files in a data lake. Datasets don’t move data by themselves; they describe where data lives and how it should be interpreted.

Linked Services

Linked services define connections to external systems. Examples: an Azure SQL database, a Blob Storage account, an on-premises database, or an SFTP server. You’ll often reuse linked services across datasets and activities.

Integration Runtime

Integration runtime is the engine that actually performs data movement and some processing. There’s a cloud integration runtime (for Azure-to-Azure scenarios), and you can set up self-hosted integration runtime for on-premises sources. If you’ve ever wondered where “magic” happens when data moves across networks, this is it.

Data Flows

Data flows (often built with the Mapping Data Flow feature) are where transformations happen at scale. Instead of writing transformation logic inside a copy activity, you can create a visual data flow that reads, transforms, and writes data with a Spark-like execution engine.

Step One: Create Your Data Factory and First Linked Services

Let’s start from a clean slate. In the Azure portal, create a new Data Factory. Name it something recognizable (future you will thank you), and choose the correct Azure region. Now for linked services.

Set up a Storage Linked Service

Most ETL pipelines eventually land data in a lake. Blob Storage is a common destination. You’ll create a linked service for your storage account. You’ll configure authentication (managed identity, account key, SAS, etc.). If you can use managed identity, do it. It’s less “credential-in-a-text-file” and more “grown-up cloud practice.”

Set up a Source Linked Service

Now create a linked service for your source. It might be:

Azure SQL Database or SQL Managed Instance
Synapse dedicated SQL pool
Another storage account
An on-premises SQL Server (requires self-hosted integration runtime)
SFTP / FTP (usually tricky but doable)

In each case, configure connectivity and authentication. If you need to reach private endpoints or networks, you’ll want to think about virtual network setup early rather than praying later.

Step Two: Create Datasets (The “Describe Data, Don’t Move It” Phase)

Now that connections exist, define datasets. Datasets answer: what are we reading, and what are we writing?

Example: Dataset for Source Table

Suppose your source is an Azure SQL table called Orders. Your dataset will include:

Connection via the linked service
Table name or query
Schema details (column names and types)
If applicable, parameterization for dynamic queries

Tip: parameterize datasets when you need to change schema location or time windows. For example, you might want to read from Orders_2026_05 depending on the run date.

Example: Dataset for Sink Files in Data Lake

For the destination, you might have a dataset pointing to a folder path in Blob Storage such as:

/raw/orders/
/staging/orders/
/curated/orders/

Datasets for files typically include the file format (CSV, JSON, Parquet, etc.), along with how columns map. If you’re writing Parquet, the benefits include better performance and schema handling. If you’re writing CSV, prepare for a certain level of chaos (commas love to hide).

Step Three: Build a Pipeline with Copy Activity (Basic ETL That Actually Works)

Now we build your first pipeline. Start a new pipeline in ADF. Add a Copy activity. Configure it using:

Source dataset
Sink dataset
Copy settings (batch size, data consistency, file naming)
Optional filters or query settings

Full Load vs Incremental Load

Most production ETL uses incremental loads. Full loads get expensive, slow, and annoying when source tables grow. ADF supports incremental patterns via techniques like:

Filtering by a “last modified” timestamp
Filtering by an ID greater than a stored watermark
Using partitioned reads based on date keys
For file sources: using file patterns and recurrence

For example, your Copy activity might read only rows where LastUpdated > pipelineStartTime - 1 day, then you deduplicate downstream. This avoids missing records during time drift, clock skew, or late-arriving data.

Yes, you’ll still deal with late-arriving data. No, it will not politely ask your permission first.

Use Parameters for Reusability

Instead of hardcoding folder paths and date filters, parameterize your pipeline. Create pipeline parameters like:

sourceTableName
sinkPath
startTime and endTime
watermark value

Azure Official Partner Then set dataset properties using these parameters. This turns your pipeline into a reusable tool rather than a one-off robot that only works for one table on one day.

Step Four: Add Transformations (Where ETL Becomes ETL and Not Just Transfer)

Copy activities move data. Transformations make it meaningful. In Azure Data Factory, you can transform in a few different ways. The main modern options are:

Mapping Data Flows
Using functions and expressions in activities (limited transformations)
Calling external compute (Azure Functions, Databricks, Synapse pipelines)

For many ETL scenarios, data flows are the sweet spot: visual, scalable, and powerful.

Start with a Mapping Data Flow

Create a new data flow in ADF. Add a source transformation (source dataset), then add transformations like:

Derived Column to create or modify columns
Filter to remove unwanted records
Join to combine datasets
Aggregate to compute summary metrics
Lookup when you need reference data enrichment
Azure Official Partner Alter Row to reshape rows (deletes/updates)

Then set a sink transformation to write output to your target dataset.

Example Transformation: Standardize Dates and Remove Junk

Imagine your source Orders table contains a column order_date as a string with multiple formats: “2026-05-01”, “05/01/2026”, and occasionally “2026/5/1”. Data lake chaos, courtesy of human input and system migrations. In your data flow, you can:

Use derived column logic to parse to a consistent timestamp
Filter out rows where parsing fails
Standardize currency codes
Drop unused columns

This is where your data becomes more like a well-behaved spreadsheet and less like an escape room.

Handle Schema Drift (Because Data Won’t Be Boring for You)

Schema drift is when new columns appear, types change, or fields become optional unexpectedly. If your pipeline assumes a strict schema, it will eventually break—usually right before a business review.

Strategies to handle drift include:

Using tolerant schema reads (where supported)
Explicitly mapping columns in data flows
Writing raw data as-is to a “raw” zone first, then transforming into a “curated” schema
Adding validation steps (for example: fail if critical columns are missing)

A practical pattern: land raw data without transformation, then transform into a stable model downstream. That way, the transformation logic can evolve without destroying your raw history.

Step Five: Orchestrate Multiple Activities and Pipelines

Once you have a copy step and a transform step, it’s time to orchestrate them.

Chaining Activities

In a pipeline, you can chain activities so that:

Copy activity loads data into a staging or raw folder
Data flow activity transforms and writes to curated tables
A final activity performs deduplication or runs a stored procedure

You set dependencies by connecting activity outputs to control flow. If the copy fails, you usually want to stop the downstream transformation instead of generating “transformed nothingness.”

Call Other Pipelines

For maintainability, you might create multiple pipelines:

A LoadRaw pipeline
A TransformCurated pipeline
An EnrichReferenceData pipeline

Then orchestrate them from a parent pipeline. This encourages reuse and keeps large pipelines from turning into a spaghetti junction. (Spaghetti is tasty, but pipelines should not be the same texture.)

Step Six: Scheduling and Triggers (When Your Pipeline Should Run)

Now you decide when the pipeline runs. ADF supports triggers such as:

Schedule trigger: run every hour/day/week
Tumbling window trigger: run based on time intervals and track windows automatically
Azure Official Partner Event-based triggers: run when new files arrive (commonly paired with event grid or storage events)

For file ingestion, event-based triggers are great: your pipeline reacts to new data rather than polling like a confused intern.

Choosing the Right Trigger Style

Here are a few practical considerations:

Time-based data (daily partitions): schedule or tumbling window triggers.
File-based ingestion: event-based triggers or recurrence with file discovery.
Manual backfills: run pipeline on demand with parameters.

Remember that triggers run independently of your optimism. If a trigger fires when prerequisites aren’t ready, your pipeline will fail. And you will become intimately familiar with logs.

Step Seven: Monitoring, Logging, and Error Handling (The Part Everyone Skips Until They Need It)

If you want a pipeline that survives contact with reality, treat monitoring and error handling as first-class features.

Use Activity Policies

ADF allows setting retry policies and timeouts. For example, if a copy activity fails due to transient network issues, you can retry a few times before giving up. This helps with flaky connections and temporary throttling.

Capture Operational Metrics

Consider writing metrics to a monitoring table or logging system. Common metrics include:

Rows copied
Rows transformed
Start and end times
Watermark values used
Number of rejected/invalid rows (if you validate)

This turns “it failed” into “it failed and here’s why it failed, with numbers.” Your future self will do a small victory dance.

Azure Official Partner Use Alerts

Azure Official Partner Hook pipeline failures to alerting mechanisms (email, webhooks, Teams, etc.). When you can, use Azure Monitor. If you don’t alert, you’re basically gambling that someone will notice a red status tile before the data is urgently needed.

Red status tiles are like smoke alarms. You don’t want them silent.

Security: Don’t Hand Your Credentials to the Universe

Data integration touches sensitive systems. Security should not be an afterthought patched together with duct tape and a prayer.

Use Managed Identity Where Possible

Managed identity reduces the risk of leaking secrets. It also makes rotation unnecessary. If you’re running on Azure, leaning into identity-based access is typically the healthier path.

Least Privilege for Linked Services

Give your linked service identity only the permissions required:

Read-only where you only extract
Write permissions only to the necessary target folders/tables
No broad “owner” roles unless absolutely unavoidable

Least privilege is like seatbelts. It’s not glamorous, but it’s the reason you’re still here.

Handle Secrets Properly

If you must store secrets, store them in a proper secret manager (Azure Key Vault is the usual choice). Avoid embedding passwords directly in pipeline definitions. In addition to being unsafe, it makes auditors sweat, and auditors already sweat enough on their own.

Performance Tips: Make Your Pipeline Fast Without Making It Fragile

Performance tuning is where pipelines go from “works” to “feels professional.” But tuning is also where you can accidentally add complexity that punishes you later.

Prefer Partitioned Reads for Large Tables

If your source is large, use partitioning (where supported) to read in parallel. For SQL sources, you can partition reads using ranges on a numeric or datetime column. For file sources, you can partition by folders or file patterns.

Choose the Right File Formats

If you control the sink format, use formats that support efficient analytics like Parquet. CSV is fine for quick tests, but for heavy workloads, it becomes a bloated jacket on a hot day.

Avoid Unnecessary Data Movement

Load only what you need. If you pull full tables just to filter down later, you’ll pay twice: once in transfer time and again in processing time. Instead, push filters earlier when possible.

A Complete ETL Pattern (Copy Raw, Transform Curated, Repeat Forever)

Let’s outline a pattern you can reuse across many ETL projects.

Pattern Overview

Raw zone: copy incoming data as-is (append-only or partitioned by date)
Staging zone: optionally normalize schemas and validate
Curated zone: transform into a stable analytics-ready schema
Serving layer: load into databases/warehouse or provide directly to consumers

Benefits of the Pattern

Recoverability: if transformations break, you still have raw data
Auditability: you can trace what arrived when
Evolution-friendly: transformations can be updated without losing history

It’s like having a receipt even after you’ve returned the item. Sure, you might not need it every day. But when you do, you’ll be very glad you kept it.

Common Pitfalls (And How to Pretend They Won’t Happen)

Here are some frequent issues teams encounter with ADF ETL pipelines. Spoiler: they happen. The trick is to notice them early, not after production.

Pipeline Runs Twice

Sometimes triggers or dependencies can cause unexpected reruns. Possible causes include:

Overlapping schedules
Tumbling window misconfiguration
Manual runs combined with schedules

Solution ideas: use idempotent writes, add deduplication, and carefully manage trigger schedules. For file-based ingestion, include unique identifiers in file naming and sink paths.

Schema Changes Break Transformations

When sources change, transformations may fail. Solutions: explicit mapping, validation rules, versioned datasets, and stable “curated” schemas.

Timezone Confusion

Time is the most dishonest datatype in the world. If you’re filtering by timestamps, you need clarity on timezone conversions and daylight saving time effects. Ideally, store and compare in UTC. When users demand local time, convert at the edges—not everywhere in the middle of your pipeline.

Credential Issues and Access Denied

When security is misconfigured, the pipeline fails with messages that sound like they were written by a poet. Make sure:

Managed identity has required permissions
Network access allows the integration runtime to connect
Key Vault secrets are accessible (and not in some forbidden realm)

Slow Runs Because of Too-Fine Granularity

If you process thousands of small files individually, overhead becomes your enemy. Consider combining files, using partitioned formats, or using batch patterns.

Testing and Development Workflow (How to Build Without Losing Your Mind)

Development for ETL is tricky because you need representative data and you need confidence that your transformations are correct.

Test with a Small Sample

Don’t test on the full dataset unless you enjoy waiting. Use filters, smaller time windows, or a test folder of sample files. In ADF, use parameters to control the time window so you can run quick iterations.

Use Dev/Test/Prod Environments

Azure Official Partner Create separate linked services or separate resource instances for environments. At minimum, separate development and production subscriptions. Otherwise, you’ll accidentally update prod when you meant to change dev. This is the kind of mistake that doesn’t come with a polite warning.

Validate Output Schema

After transformations, validate row counts, null rates, and key column types. For example, if a column “customer_id” unexpectedly becomes null for most rows, fail fast with a clear error message.

Extending Beyond Basic ETL: Data Flow vs Activities vs External Compute

Sometimes you need more power than copy and basic data flow transforms. Here’s a pragmatic view.

When to Use Data Flows

Use data flows when:

You want scalable transformations
Azure Official Partner You prefer visual mapping logic
You need joins, aggregates, and column-level transformations

When to Use Copy Activity

Use copy activity when transformations are minimal and you mostly need movement:

Ingesting raw data to a lake
Moving between compatible formats
Landing data for downstream processing

When to Call External Compute

Use Azure Functions, Databricks, or Synapse Spark when:

Transformations are extremely complex
You need advanced machine learning or custom libraries
You already have business logic implemented there

This isn’t a weakness of ADF; it’s modular architecture. Use the right tool for the job, not the tool you happen to know best.

Operational Readiness Checklist (Before You Ship)

When your pipeline is nearly done, run through a checklist. If you do this habitually, you’ll catch a surprising number of problems.

Logging: Are key metrics captured? Are failures understandable?
Retries: Are transient failures handled with appropriate retry policies?
Idempotency: If a run repeats, does it produce duplicate results?
Security: Are secrets stored properly and permissions least-privileged?
Schema stability: Is your curated schema enforced or validated?
Scheduling: Do triggers overlap or conflict?
Performance: Does it complete in the required SLA window?

If you want your pipeline to be loved rather than cursed, this is where the love begins.

Conclusion: Your ETL Pipeline Is a Living Thing

Building ETL pipelines with Azure Data Factory is less about pressing buttons and more about designing a workflow that can handle real-world surprises. Start with linked services and datasets, build copy activities to land data, use data flows for transformations, and orchestrate runs with triggers and parameterization. Then, invest in monitoring, error handling, and security so your pipeline can survive schema drift, network hiccups, and the occasional “why is this file empty?” mystery.

Once you have a working pattern—raw copy, curated transform, repeat—you’ll find that adding new sources and datasets becomes much easier. You’ll still get problems, sure. But they’ll be smaller, clearer, and easier to fix. And that, honestly, is the best kind of victory in data engineering.

Now go build something. Preferably something that doesn’t fail quietly and doesn’t require interpretive dance to debug.