Azure Data Factory Pipeline Design: Patterns for Production-Grade ETL

Azure Data Factory is Microsoft's cloud-based data integration service — a managed ETL/ELT platform for building, scheduling, and monitoring data pipelines at enterprise scale. For organisations on Azure, ADF is typically the first consideration for data movement: from on-premises systems to Azure, between Azure services, and from cloud sources into Azure data stores.

This guide covers ADF architecture, the key design decisions that determine pipeline reliability, and the operational patterns that distinguish a maintainable ADF environment from a tangle of hundreds of interdependent pipelines.

ADF Architecture Overview

Azure Data Factory is built around four core concepts:

**Linked Services** are connections — the credentials and connection strings that allow ADF to talk to a data source or destination. A linked service for Azure SQL Database holds the server name, database name, and authentication method. A linked service for Salesforce holds the API credentials. Linked services are defined once and reused across pipelines.

**Datasets** describe the data structure within a linked service — a specific table, file, or folder. A dataset for an Azure SQL table specifies which table and optionally which columns. A dataset for an Azure Blob Storage file specifies the container, folder path, and file format. Datasets are the inputs and outputs of activities.

**Activities** are the operations in a pipeline: Copy Data (moving data from source to sink), Data Flow (transforming data using a visual ETL designer), Execute Pipeline (calling another pipeline), Web (calling an HTTP endpoint), Stored Procedure (running a SQL stored procedure), and many more. Activities have inputs (datasets or literal values), outputs (datasets or variables), and configuration.

**Pipelines** are sequences of activities. Activities can be chained (run sequentially), run in parallel, or conditioned on the success or failure of preceding activities. Pipelines are parameterised — they accept input parameters that control their behaviour, making them reusable across different tables, files, or environments.

**Integration Runtimes** are the compute infrastructure that executes activities. Azure Integration Runtime runs in the cloud; Self-Hosted Integration Runtime runs on a machine in your network, allowing ADF to access on-premises data sources that are not exposed to the internet.

**Triggers** start pipeline runs: scheduled triggers (run at a defined time), tumbling window triggers (run at regular intervals with non-overlapping windows), storage event triggers (run when a file appears in Blob Storage), and custom event triggers.

Copy Activity vs Data Flow

The most important architectural decision in ADF is choosing between Copy Activity and Data Flow for each transformation requirement.

**Copy Activity** is optimised for moving data with minimal transformation: read from source, write to sink, with optional column mapping, type conversion, and basic filtering. It is fast, cheap, and simple. Use it for: ingesting raw data from source systems, moving files between storage accounts, loading data into staging tables, any scenario where you are primarily moving data with at most light transformation.

**Data Flow** is ADF's visual ETL designer — a transformation engine that uses Spark under the hood. It supports complex transformations: joins, aggregations, pivots, lookups, conditional splits, derived columns with complex expressions. Data flows are compiled to Spark jobs and run on a managed Spark cluster. They are slower to start (3–5 minutes for cluster spin-up by default) and more expensive than copy activity.

The common mistake is using Data Flow for transformations that Copy Activity can handle, paying Spark cluster costs unnecessarily. The other common mistake is using Copy Activity for complex transformations, creating unmaintainable expression chains in the column mapping. The right heuristic: if the transformation can be expressed cleanly in a copy activity column mapping, use copy activity. If it requires joins, aggregations, or conditional logic, use data flow.

For complex transformation logic, consider pushing the transformation to the destination: copy raw data with Copy Activity, then run a stored procedure or a dbt model to transform it. This separates ingestion from transformation, makes both simpler, and allows you to use SQL or dbt where they are more maintainable than ADF Data Flow expressions.

Incremental Loading Patterns

Full loads — truncate the destination, reload everything from source — are simple and reliable for small tables. For large tables, full loads are too slow and too expensive. Incremental loading requires more design but is necessary at scale.

**Watermark-based incremental loading**: the most common pattern. Track the maximum value of an incrementing column (a timestamp or an ID) that was loaded in the previous run. On each run, load only rows where the column value exceeds the watermark. Store the watermark in a control table. Update the watermark at the end of a successful run.

Implementation in ADF: a Lookup activity reads the current watermark from the control table. The Copy Activity or Data Flow uses the watermark as a query parameter (passing it as a pipeline parameter to a parameterised dataset). A Stored Procedure activity updates the watermark after successful loading.

**Change Data Capture (CDC)**: for SQL Server sources, ADF supports SQL Server CDC, which reads the CDC log tables that track every insert, update, and delete. CDC captures all changes including updates and deletes that a watermark approach misses (a watermark on updated_at handles updates; it does not handle deletes). For true change tracking, CDC is more reliable.

**File-based incremental loading**: for file sources, new files appear in a folder as they are generated. A storage event trigger fires when a new file arrives, and a pipeline processes it. ADF tracks which files have been processed using a metadata database or by moving processed files to an archive folder.

Pipeline Design Principles

**Parameterise everything.** Hard-coded table names, file paths, and connection details make pipelines impossible to reuse and environments impossible to maintain. Every value that might vary between environments (dev/test/prod) or between invocations (the table being loaded, the date range) should be a parameter. Linked service credentials should reference Key Vault secrets, not be stored inline.

**Use the metadata-driven pattern for bulk loading.** Rather than creating one pipeline per source table, build a generic pipeline that accepts table name, source query, and destination table as parameters, then drive it from a control table. A ForEach loop reads the control table and calls the parameterised pipeline for each row. This pattern — sometimes called a metadata-driven framework — scales to hundreds of tables without hundreds of pipeline definitions.

**Keep pipelines focused.** A pipeline that ingests from a source, transforms the data, and sends a notification email is three concerns in one. Break pipelines by function: an ingestion pipeline loads raw data; a transformation pipeline processes it; an orchestration pipeline calls both and handles sequencing and notifications. Smaller pipelines are easier to test, debug, and rerun after failures.

**Handle partial failures explicitly.** ADF's error handling is activity-level: on success, on failure, on completion (either outcome). Design pipelines to handle partial failures: if loading table A succeeds and table B fails, the pipeline should record the failure, attempt retry, and alert — not silently fail or skip the remaining tables. Use pipeline variables to track success/failure per table and emit a summary at the end.

Monitoring and Alerting

ADF's built-in monitoring shows pipeline run history, activity run details, and trigger history. For production pipelines, built-in monitoring is not enough — you need proactive alerting.

**Azure Monitor integration**: ADF emits diagnostic logs and metrics to Azure Monitor. Set up alert rules for: pipeline failure (any pipeline in a factory fails), activity failure rate above threshold, pipeline duration exceeds expected range.

**Pipeline run duration tracking**: baseline how long each pipeline should take. A pipeline that normally runs in 5 minutes taking 45 minutes indicates a performance problem (table statistics out of date, over-provisioned cluster, source system slowness) — alert before it becomes an outage.

**Data freshness monitoring**: after a pipeline runs, validate that the destination data was actually updated. A pipeline that ran successfully but loaded zero rows is a data quality problem, not a technical failure. Include a row count validation at the end of each pipeline and alert if the count is unexpectedly low.

Integration Runtime Considerations

The Self-Hosted Integration Runtime (SHIR) is necessary for on-premises data sources. It deserves careful operational attention:

**High availability**: a single SHIR node is a single point of failure. For production workloads, run at least two SHIR nodes. ADF load-balances across available nodes automatically.

**Resource sizing**: SHIR runs on a machine you manage. Undersized machines cause slow pipelines and failed activities. Monitor CPU, memory, and network utilisation on SHIR nodes and right-size accordingly. For high-volume pipelines, dedicated SHIR hosts beat shared infrastructure.

**Updates**: Microsoft releases SHIR updates regularly. Automatic update is the default — but if your SHIR has custom drivers installed, automatic updates can break things. Consider manual update control and test updates in a non-production environment first.

For Azure data platform architecture including ADF pipeline design and optimisation, our Azure cloud engineering practice builds enterprise-scale data pipelines — contact us to discuss your data integration requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.