Azure Data Factory Overview

Core concepts

ADF has a set of building blocks. Understanding them before you build a pipeline saves a lot of confusion:

The most important activity types

ADF has over 30 built-in activity types. Here are the ones you will use most often:

Copy Activity — the workhorse. Moves data from source to sink (destination). Can handle column renaming, type conversion, and schema drift. Parallelises automatically using multiple Data Integration Units (DIUs). Supports incremental copy with watermark or last-modified-time filtering.

Mapping Data Flow — a code-free, visually-designed transformation canvas. You drag and drop transformations (join, aggregate, filter, pivot, window function, etc.) and ADF compiles them into Spark code that runs on a managed cluster. No Spark knowledge needed, but the generated code is Spark under the hood.

Notebook Activity — runs a Databricks notebook or Synapse Spark notebook as a pipeline step. Use this when your transformation logic is already written in Python/PySpark and you want to invoke it from a scheduled pipeline.

Stored Procedure Activity — calls a stored procedure on Azure SQL Database, Synapse dedicated pool, or SQL Server. Useful for post-load merge logic or refreshing materialised views.

Lookup Activity — reads a single value or row from a source. Commonly used to read a watermark timestamp from a control table before an incremental load.

ForEach Activity — iterates over an array and runs child activities for each item. Use it to process multiple files or tables in a loop without duplicating pipeline logic.

A real pipeline: incremental load with watermark

The incremental load pattern is the most common ADF use case. You maintain a watermark (a timestamp of the last successful load) and extract only records modified since that timestamp. Here is how it works in practice:

// Pipeline JSON (simplified) for incremental SQL → ADLS load
{
  "name": "IncrementalOrderLoad",
  "activities": [
    {
      "name": "GetWatermark",
      "type": "Lookup",
      "typeProperties": {
        "source": {
          "type": "AzureSqlSource",
          "sqlReaderQuery": "SELECT MAX(last_load_time) AS watermark FROM dbo.pipeline_control WHERE pipeline_name = 'OrderLoad'"
        },
        "dataset": { "referenceName": "ControlDB" }
      }
    },
    {
      "name": "CopyIncrementalOrders",
      "type": "Copy",
      "dependsOn": [{ "activity": "GetWatermark", "dependencyConditions": ["Succeeded"] }],
      "typeProperties": {
        "source": {
          "type": "AzureSqlSource",
          "sqlReaderQuery": {
            "value": "SELECT * FROM dbo.Orders WHERE modified_date > '@{activity('GetWatermark').output.firstRow.watermark}'",
            "type": "Expression"
          }
        },
        "sink": {
          "type": "ParquetSink",
          "storeSettings": {
            "type": "AzureBlobFSWriteSettings",
            "copyBehavior": "PreserveHierarchy"
          }
        }
      }
    },
    {
      "name": "UpdateWatermark",
      "type": "SqlServerStoredProcedure",
      "dependsOn": [{ "activity": "CopyIncrementalOrders", "dependencyConditions": ["Succeeded"] }],
      "typeProperties": {
        "storedProcedureName": "dbo.UpdateWatermark",
        "storedProcedureParameters": {
          "pipeline_name": { "value": "OrderLoad" },
          "new_watermark": { "value": "@{utcNow()}" }
        }
      }
    }
  ]
}

This pipeline reads the last watermark, copies only new/changed orders since that timestamp to ADLS as Parquet, then updates the watermark for the next run. It is resilient — if the copy fails, the watermark is not updated, so the next run re-attempts the same data range.

Integration Runtime: Azure-managed vs self-hosted

The Integration Runtime (IR) is the compute that executes your pipeline activities. There are three types:

Azure Integration Runtime — fully managed by Microsoft. No configuration needed. Can run Copy Activities and Data Flows within Azure. Cannot connect to on-premises or private network resources.

Self-Hosted Integration Runtime (SHIR) — software you install on a Windows server in your network (on-premises or a VM). Enables ADF to connect to SQL Server, Oracle, SAP, or any other system not publicly accessible from Azure. The SHIR connects outbound to ADF — no inbound firewall ports needed.

Azure-SSIS Integration Runtime — a managed cluster that runs legacy SSIS packages. Use this if you are migrating existing SSIS ETL to Azure without rewriting it.

# Create an ADF instance with Azure CLI
az datafactory create \
  --resource-group rg-data-platform \
  --factory-name adf-mycompany-prod \
  --location eastus

# Create an Azure Integration Runtime (default - always exists)
# No creation needed - Azure IR is built in

# Create a self-hosted Integration Runtime definition in ADF
az datafactory integration-runtime create \
  --resource-group rg-data-platform \
  --factory-name adf-mycompany-prod \
  --name SelfHostedIR \
  --type SelfHosted

# Get the authentication key to register the SHIR agent on your server
az datafactory integration-runtime list-auth-key \
  --resource-group rg-data-platform \
  --factory-name adf-mycompany-prod \
  --name SelfHostedIR

Triggers: how pipelines are started

ADF supports four trigger types:

• Schedule trigger — runs a pipeline on a cron schedule (e.g., daily at 2 AM UTC). Multiple pipelines can share one trigger.
• Tumbling window trigger — like a schedule trigger but with non-overlapping, fixed-size time windows. Each run receives the window start and end times as parameters. Handles backfill: if ADF was down, it runs all missed windows sequentially when it recovers.
• Storage event trigger — runs when a file is created or deleted in Azure Blob Storage or ADLS Gen2. Use this for event-driven ingestion: process a file as soon as it lands.
• Custom event trigger — runs in response to a custom event published to Azure Event Grid. Use this for complex event-driven orchestration scenarios.

ADF vs Synapse Pipelines: which to use

Synapse Pipelines is functionally identical to ADF — it uses the same underlying engine, the same connectors, and the same activity types. The choice is about context:

Use Synapse Pipelines when your data destination is primarily Synapse (dedicated pool, ADLS Gen2 backing a Synapse workspace). Being co-located with your pools lets you trigger Synapse notebooks, invoke stored procedures, and monitor everything in one place without cross-service configuration.

Use standalone Azure Data Factory when your pipelines serve multiple destinations that are not tied to a single Synapse workspace — for example, feeding data to Cosmos DB, multiple SQL databases, and a data lake simultaneously. ADF also has slightly better support for some enterprise features like Git integration and CI/CD at the factory level.