ADF Pipeline Patterns That Scale: Parameterized Datasets, Metadata-Driven Ingestion, and Integration Runtime Sizing

The ADF instance I inherited had 340 pipelines. Three hundred and forty. One per source table, each created by right-clicking the previous one and choosing Clone. When the source system added a column, someone opened 340 pipelines and checked each one. When the retry policy needed changing, that was a two-week ticket.

This is the default failure mode of Data Factory, and it is not because the tool is bad. It is because the visual designer makes cloning a pipeline take eight seconds and makes parameterizing one take an afternoon. The incentive gradient points directly at the worst architecture.

The fix is well known and still rare: one pipeline, driven by a control table, parameterized end to end. I have done this migration three times. It takes about four weeks for a few-hundred-table estate and it turns “add a source” from a half-day of clicking into an INSERT statement.

This post is that pattern in full, plus the integration runtime sizing that decides whether it runs in 20 minutes or four hours. For where ADF sits relative to Synapse and Fabric, start with the Azure data engineering overview.

What ADF actually charges you for

You cannot design ADF well without knowing the meter, because the meter is unusual. Four things bill:

• Activity runs, at roughly $1 per 1,000 on Azure IR. Every Copy, Lookup, Set Variable, and If Condition counts.
• Data movement, at roughly $0.25 per DIU-hour. A Data Integration Unit is ADF’s abstract compute unit for Copy.
• Pipeline activity duration, a small hourly rate for activities that occupy the runtime.
• Data flow compute, per vCore-hour, on a cluster that has to start before anything happens.

The first one drives architecture. A ForEach over 200,000 rows calling one activity each is 200,000 activity runs — $200, per run, for orchestration alone, before any data moves. I have seen this bill arrive. The lesson is permanent: ADF is an orchestrator that bills per step, so steps must be coarse. Move sets, not rows.

The metadata-driven pattern

One control table describes every ingestion. One pipeline reads it. Adding a source is a row.

-- Control table. Lives in the same Azure SQL DB as your ADF metadata,
-- or in a dedicated 'config' schema someone actually owns.
CREATE TABLE config.ingest_source (
    source_id           INT IDENTITY(1,1) PRIMARY KEY,
    source_system       VARCHAR(50)  NOT NULL,   -- 'erp', 'crm', 'billing'
    source_schema       VARCHAR(128) NOT NULL,
    source_table        VARCHAR(128) NOT NULL,
    load_type           VARCHAR(20)  NOT NULL,   -- 'full' | 'incremental'
    watermark_column    VARCHAR(128) NULL,       -- required when incremental
    watermark_value     DATETIME2    NULL,       -- last successfully loaded high water
    partition_column    VARCHAR(128) NULL,       -- enables parallel source reads
    target_container    VARCHAR(64)  NOT NULL,   -- 'raw'
    target_path         VARCHAR(400) NOT NULL,   -- 'erp/sales_order'
    is_enabled          BIT          NOT NULL DEFAULT 1,
    load_group          TINYINT      NOT NULL DEFAULT 1,  -- batching / priority
    last_run_status     VARCHAR(20)  NULL,
    last_run_utc        DATETIME2    NULL,
    CONSTRAINT uq_source UNIQUE (source_system, source_schema, source_table)
);

-- The query the driver pipeline runs. Note the row cap: Lookup
-- returns at most 5,000 rows / 4 MB, so we page by load_group.
CREATE PROCEDURE config.get_ingest_batch @load_group TINYINT
AS
SELECT source_id, source_schema, source_table, load_type,
       watermark_column, watermark_value, partition_column,
       target_container, target_path
FROM   config.ingest_source
WHERE  is_enabled = 1 AND load_group = @load_group
ORDER  BY source_id;

Three columns in there earn their place. load_group exists purely because Lookup caps at 5,000 rows and 4 MB of output, so a large estate must be batched — and it doubles as a priority mechanism so the finance tables land before the clickstream. partition_column lets the Copy activity split a large source read into parallel queries. And watermark_value on the row means incremental state lives in one auditable place instead of scattered across pipeline variables.

The pipeline that consumes it has four activities:

{
  "name": "pl_ingest_driver",
  "properties": {
    "parameters": {
      "load_group": { "type": "int", "defaultValue": 1 }
    },
    "activities": [
      {
        "name": "LookupSources",
        "type": "Lookup",
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderStoredProcedureName": "config.get_ingest_batch",
            "storedProcedureParameters": {
              "load_group": { "value": { "value": "@pipeline().parameters.load_group", "type": "Expression" } }
            }
          },
          "dataset": { "referenceName": "ds_control_db", "type": "DatasetReference" },
          "firstRowOnly": false
        }
      },
      {
        "name": "ForEachSource",
        "type": "ForEach",
        "dependsOn": [{ "activity": "LookupSources", "dependencyConditions": ["Succeeded"] }],
        "typeProperties": {
          "items": { "value": "@activity('LookupSources').output.value", "type": "Expression" },
          "isSequential": false,
          "batchCount": 20,
          "activities": [
            {
              "name": "ExecuteIngestChild",
              "type": "ExecutePipeline",
              "typeProperties": {
                "pipeline": { "referenceName": "pl_ingest_table", "type": "PipelineReference" },
                "waitOnCompletion": true,
                "parameters": {
                  "source_id": { "value": "@item().source_id", "type": "Expression" },
                  "source_schema": { "value": "@item().source_schema", "type": "Expression" },
                  "source_table": { "value": "@item().source_table", "type": "Expression" },
                  "load_type": { "value": "@item().load_type", "type": "Expression" },
                  "watermark_column": { "value": "@item().watermark_column", "type": "Expression" },
                  "watermark_value": { "value": "@item().watermark_value", "type": "Expression" },
                  "target_path": { "value": "@item().target_path", "type": "Expression" }
                }
              }
            }
          ]
        }
      }
    ],
    "annotations": ["metadata-driven", "ingestion"]
  }
}

batchCount is the parallelism knob and it maxes at 50. Do not set it to 50 on day one. Every parallel branch is a concurrent connection to the source system, and the fastest way to get your ingestion project cancelled is to take down the ERP database on a Tuesday morning. I start at 8, watch source-side wait stats, and climb.

isSequential: false with waitOnCompletion: true is the combination you want: children run in parallel, the driver still knows when they are all done.

flowchart TD
  ctl["config.ingest_source"] --> lk["Lookup: batch by load_group"]
  lk --> fe["ForEach, batchCount 8-20"]
  fe --> child["pl_ingest_table (parameterized)"]
  child --> copy["Copy: source to ADLS Parquet"]
  copy --> wm["Stored proc: update watermark"]
  copy --> fail["On failure: log + continue"]
  wm --> done["raw/{system}/{table}/ingest_date=..."]

One driver, one child, one row per source. Adding a table is an INSERT, not a deployment.

Parameterized datasets are what make it possible

The pattern collapses without parameterized datasets, and this is where most attempts stall. A dataset in ADF should describe a shape, not a table. You need roughly four datasets for an entire estate:

• ds_sql_generic — parameters: schema, table. Points at a parameterized linked service if you have multiple source databases.
• ds_adls_parquet — parameters: container, folder path, file name.
• ds_adls_delimited — same, for the sources that insist on CSV.
• ds_control_db — the config database.

{
  "name": "ds_adls_parquet",
  "properties": {
    "linkedServiceName": { "referenceName": "ls_adls_raw", "type": "LinkedServiceReference" },
    "parameters": {
      "container": { "type": "string" },
      "folder_path": { "type": "string" },
      "file_name": { "type": "string", "defaultValue": "part.parquet" }
    },
    "type": "Parquet",
    "typeProperties": {
      "location": {
        "type": "AzureBlobFSLocation",
        "fileSystem": { "value": "@dataset().container", "type": "Expression" },
        "folderPath": { "value": "@dataset().folder_path", "type": "Expression" },
        "fileName": { "value": "@dataset().file_name", "type": "Expression" }
      },
      "compressionCodec": "snappy"
    },
    "schema": []
  }
}

Note "schema": []. Leave dataset schemas empty for metadata-driven ingestion. An imported schema pins the dataset to one table and defeats the whole design. The Copy activity will handle schema drift as long as you do not give it a mapping to argue with.

Linked services take parameters too, which is how one linked service serves twelve source databases. Combine that with Key Vault references for credentials and a managed identity for storage, and you get an estate where promoting to production is a matter of ARM parameters rather than reconfiguration.

Integration runtime sizing

Three runtime types, three completely different sizing problems.

Azure IR (data movement). Sized in DIUs, from 2 to 256, with auto as the default. Auto is usually conservative. For a large table copy from SQL to ADLS I set DIUs explicitly — 32 is a reasonable starting point for a multi-gigabyte table — and pair it with parallelCopies. The two work together: DIUs are the compute allocated, parallel copies are how many concurrent readers exist. Raising DIUs with parallelCopies: 1 on a single unpartitioned source does very little, which is why people conclude “DIUs do not help.”

Also set the region. An Azure IR in the wrong region moves data across regions at egress prices and at the mercy of the WAN. Auto resolve picks based on the sink, which is usually right and occasionally very wrong.

Self-hosted IR. This is a Windows service on a VM you own, and it is a real capacity decision. Defaults allow a limited number of concurrent jobs based on cores; a 4-core VM will serialize your “parallel” ForEach and you will spend a week blaming ADF. My baseline: 8 vCPU, 32 GB, two nodes for high availability, and monitoring on the concurrent jobs metric. Two nodes is not optional in production — a single node means Windows Update is an outage.

Data flow IR. Mapping Data Flows run on Spark clusters that take four to five minutes to start. That startup is the single most common complaint about ADF latency, and it is entirely solvable with a TTL on the integration runtime: keep the cluster warm for 10 to 20 minutes so a pipeline with six data flows starts one cluster instead of six. Compute type matters too — General Purpose for most work, Memory Optimized when you are joining large sets and spilling.

{
  "name": "ir_dataflow_general",
  "properties": {
    "type": "Managed",
    "typeProperties": {
      "computeProperties": {
        "location": "West Europe",
        "dataFlowProperties": {
          "computeType": "General",
          "coreCount": 16,
          "timeToLive": 15,
          "cleanup": false
        }
      }
    }
  }
}

timeToLive: 15 on that runtime has cut end-to-end pipeline duration by 30 to 40 minutes on every estate I have applied it to. It is the highest-return single setting in Data Factory.

When Mapping Data Flows earn the cluster

Honest answer: less often than Microsoft’s documentation implies.

Data flows are Spark with a visual designer. They are genuinely good at schema drift handling, derived columns across many sources, and giving a non-coding team member a way to build a transformation. They are expensive per run because of the cluster, awkward to version-control meaningfully in Git, and painful to unit test.

My rule: ADF moves data, something else transforms it. Copy into ADLS as Parquet, then transform in Databricks notebooks or in SQL with dbt against Synapse or Fabric. That keeps transformation logic in text files that diff cleanly and test properly, and it keeps ADF doing the thing it is genuinely best at — connecting to 100-plus sources without you writing a driver.

I make exceptions for two cases. Sources with genuinely unpredictable schemas, where the drift handling saves real work. And organizations where the data team is analysts with no engineering support, where a visual tool they can maintain beats elegant code they cannot.

Error handling that survives an on-call rotation

The default ADF failure experience is bad: a red X in the monitor blade, an error message truncated at the interesting part, and no notion of which of your 200 tables failed.

What I build into every child pipeline:

• Failure paths, not just failure states. An On Failure dependency from Copy into a stored procedure that writes to config.ingest_log with the source_id, error message, and run ID. The child pipeline then fails, so the run status is honest.
• Retries at the activity level. Three retries, 60-second interval, on anything that touches a network. Transient source timeouts are the most common failure by an order of magnitude.
• ForEach that does not abort the batch. By default one failing child fails the ForEach. Wrap the child in a pipeline that catches and logs, so 199 tables load while one fails, and the driver reports partial success.
• A row-count reconciliation step. Source count versus sink count per load, written to the log table. This has caught more real problems than every other check combined — the data quality patterns here translate directly.

Then build one Power BI page over the log table. Not the ADF monitor blade — a table showing every source, its last run, its row count, and its delta from yesterday. That page is what makes an ingestion platform operable by someone who did not build it.

Pitfalls

ForEach at row granularity. Each iteration is at least one billed activity run. Iterate over tables and files, never over rows. If you are tempted, the work belongs in a data flow or a notebook.

Ignoring the Lookup 5,000-row / 4 MB cap. Silent truncation on a metadata table that grows past the limit means sources stop loading with no error. Batch by group and alert when a group approaches the cap.

Importing schemas into datasets. It feels like good hygiene and it hard-codes the dataset to one table, which kills reuse and breaks on schema drift. Leave them empty for generic datasets.

Self-hosted IR on an undersized VM. Concurrent job slots scale with cores. A 4-core node quietly serializes everything, and the symptom looks like slow copies rather than a queue.

No TTL on the data flow runtime. Four to five minutes of cluster startup, per data flow, every run. Fifteen minutes of TTL costs a fraction of what the startup time costs.

Triggers configured per pipeline in the portal. Publish from a feature branch and you can end up with duplicate triggers firing the same load twice. Deploy triggers through ARM templates and keep a single source of truth in Git.

FAQ

Should new projects still use ADF, or go straight to Fabric?

Data Factory remains the strongest ingestion tool in the Microsoft stack, and Fabric’s Data Factory experience is the same engine with a different wrapper. Build ingestion in ADF with a metadata-driven design and it ports with modest effort — see the Synapse to Fabric migration post for how the pieces map.

How many tables can one metadata-driven pipeline handle?

I have run 800 sources through one driver, batched into groups of a few hundred. The constraints are the Lookup cap, source system concurrency, and your patience with a single monitor view. Beyond about a thousand, split by source system so failures are isolated.

Where should the watermark live?

In the control table, updated by a stored procedure after a successful load, in the same transaction as the log row if you can. Pipeline variables do not survive a failure, and storing watermarks in file names is a trap you only fall into once.

Is Copy activity fast enough for large tables?

For anything under a few hundred gigabytes, yes, if you partition the source read and set DIUs deliberately. Beyond that, the source database is almost always the bottleneck, not ADF. Look at partitioned reads on an indexed column before you raise DIUs.

How do I do CI/CD properly?

Git integration on the development factory only, publish to the adf_publish branch, and deploy the generated ARM template to test and production with parameter overrides. Never let anyone author in a non-development factory. It is a rigid workflow, and fighting it costs more than accepting it.

What about ingesting from on-premises SQL Server?

Self-hosted IR, two nodes, on VMs close to the source. Expect the network between the source and Azure to be the limiting factor, and use staged copy through a storage account when the sink is a warehouse that supports bulk load.

What this means for your pipelines

The metadata-driven pattern is not clever, it is just the version of Data Factory that does not collapse under its own weight. One control table, one driver pipeline, one child pipeline, four generic datasets. Everything about a source lives in a row you can query, audit, and change without a deployment. When someone asks “what are we ingesting and when did it last succeed,” the answer is a SELECT, not an archaeology project.

Sizing is the other half. Set an explicit DIU count on the copies that matter, size the self-hosted IR for real concurrency with two nodes, and put a TTL on the data flow runtime today. Those three changes routinely take an hour and cut both the duration and the bill.

And keep the boundary clean: ADF moves data, and something with text files and tests transforms it. That is what makes the platform decision reversible when Fabric, Databricks, or whatever comes next changes the answer for the transformation layer. The ingestion metadata you built will still be a table, and tables migrate.