The Night
Dispatcher

The board is already right

Seven hours earlier, none of that data was in the same building — let alone the same shape. It was scattered across a dozen systems that don't know each other exist, run on different schedules, speak different formats, and fail in different ways. Something had to go and get it. Something had to decide the order, handle the ones that didn't show up, retry the ones that timed out, and finish before the sun did.

That something is Azure Data Factory. And the fastest way to understand what it is for is to look at the mess it was invented to replace.

Our company is Meridian Retail: 340 stores, 6 distribution centres, about 200 suppliers. Here is what has to arrive, every night, before Priya's coffee:

Six sources, one destination, one deadline. That sounds like six jobs. It is not — and the reason it is not is the whole reason this category of tool exists.

Why six sources is never six jobs

The instinct is to write a script per route. One to pull tills into the lake. One to pull stock. One for each supplier feed. Small, understandable, independent. It works beautifully — right up until it doesn't, and then it fails in a way that is very hard to climb back out of.

There are two problems, and the second one is the one that actually kills you.

The first is arithmetic. Sources multiply by destinations. The lake wants raw tills; the warehouse model wants cleaned tills; finance wants an aggregate; the ML forecaster wants its own slice. Every new destination re-crosses every existing source. Move the slider below and watch the line count go somewhere unpleasant.

The second problem is the one that gets you paged. Every one of those scripts invents its own answer to the same six questions — and invents it slightly differently.

• When do I run? A cron entry here, a Task Scheduler job there, a while true loop in a service somewhere nobody remembers deploying.
• What if the source isn't ready? Sleep 300 and hope. Or fail, and let the morning find out.
• What if I fail halfway? Re-run everything from the top, probably. Four hundred gigabytes, again.
• Who do I tell? A log file on a VM. Sometimes an email, from an address that stopped existing when someone left.
• What ran last night? Nobody knows without SSH-ing somewhere.
• Where did this number come from? Silence.

So the job to be done isn't "copy data." Copying is easy; every language has a library for it. The job is everything around the copying — the ordering, the timing, the waiting, the retrying, the alerting, the record of what happened and when. That's not plumbing. That's dispatch.

The dispatcher does not drive the trains

Here is the single most clarifying thing you can know about Azure Data Factory, and it is the thing most introductions bury: ADF is a dispatcher, not a lorry. Its job is to decide what moves, in what order, when, and what happens when something doesn't arrive. It is a control system first and a hauler second.

That distinction matters because it tells you what to expect. ADF does shift bulk data itself — its Copy activity is a genuinely fast, heavily parallel mover, and for most ingestion that's all you need. But when the work gets computational — joins across billions of rows, complex reshaping, machine learning — ADF does what a good dispatcher does: it hands the job to something built for it (Databricks, a SQL engine, an Azure Function) and goes back to watching the board. It can also run that transformation on Spark it manages for you, which we'll get to.

Six nouns run the whole product. Click through them — every one is either something you will configure on your first day or something you will be reading about at 3am on some later one.

The one everybody skips

Five of those six nouns are intuitive. The one that quietly decides whether your project succeeds is the integration runtime — because it is the answer to a question nobody thinks to ask until the security review: whose network is this actually happening on?

Meridian's till database is in a datacentre, behind a firewall, and the network team will not be opening an inbound port so that a cloud service can reach in. That is not obstruction; that is correct. So ADF doesn't reach in. You install a self-hosted integration runtime on a machine inside that network, and it dials out to Azure on 443. The connection is established from the trusted side. Nothing inbound is ever opened.

{
  "name": "OnPremTills",
  "properties": {
    "type": "SqlServer",
    // which runtime executes this — i.e. whose network
    "connectVia": { "referenceName": "shir-datacentre", "type": "IntegrationRuntimeReference" },
    "typeProperties": {
      "server":   "sql-tills-01.meridian.internal",
      "database": "TillsWarehouse",
      "authenticationType": "SQL",
      "userName": "svc_adf_reader",
      "password": {
        "type": "AzureKeyVaultSecret",
        "store": { "referenceName": "kv-meridian", "type": "LinkedServiceReference" },
        "secretName": "tills-reader-password"
      }
    }
  }
}

{
  "name": "TillsTable",
  "properties": {
    "type": "SqlServerTable",
    "linkedServiceName": { "referenceName": "OnPremTills", "type": "LinkedServiceReference" },
    // parameters are what stop you building 40 near-identical datasets
    "parameters": {
      "schemaName": { "type": "string" },
      "tableName":  { "type": "string" }
    },
    "typeProperties": {
      "schema": { "value": "@dataset().schemaName", "type": "Expression" },
      "table":  { "value": "@dataset().tableName",  "type": "Expression" }
    }
  }
}

Three ways to say "now"

A dispatcher's first decision is when. ADF gives you three fundamentally different answers, and choosing wrong is the single most common cause of the 03:00 phone call — because two of them look identical until the night something goes wrong.

The schedule trigger is a wall clock. Every day at 23:30, run this. Simple, familiar, and what most people reach for first.

The tumbling window trigger is a series of contiguous, non-overlapping time slices — 23:00–00:00, 00:00–01:00, and so on, forever, with no gaps. Each run knows which slice it owns, because ADF hands it WindowStart and WindowEnd as parameters.

The event trigger doesn't watch a clock at all. It watches a storage account, and fires when a blob appears. This is how you handle two hundred suppliers who upload whenever they feel like it: you stop polling and start reacting.

Run the night below. Let it play, then break it — knock out the middle of the night with an outage and watch what each lane does about the runs it missed.

That's the whole lesson, and it costs people real money. When the source came back at 03:00, the schedule trigger simply moved on. Its 02:30 run happened during the outage, failed, and that slice of the night is now gone — nothing will ever go back for it unless a human notices and does it by hand.

The tumbling window trigger walked back and ran the windows it had missed, in order. It could do that because it keeps state: it knows which windows exist, which have succeeded, and which haven't. That's also why it supports backfill in general — set the start date to last month and it will work through every window since, honouring however much concurrency you allow it.

So which one?

A useful rule: if the run is about a period of time, use a tumbling window. "Yesterday's sales", "last hour's transactions", "the 03:00–04:00 slice" — anything where a run owns a slice and a missed slice leaves a hole. Use a schedule trigger for work that is genuinely about the wall clock rather than a period — a nightly full refresh, a 07:00 report send — where missing one occurrence is an inconvenience, not a gap in a table.

{
  "name": "TillsHourly",
  "properties": {
    "type": "TumblingWindowTrigger",
    "typeProperties": {
      "frequency": "Hour",
      "interval": 1,
      "startTime": "2026-08-01T22:00:00Z",
      // how many missed windows may run at once when backfilling
      "maxConcurrency": 4,
      "retryPolicy": { "count": 3, "intervalInSeconds": 300 },
      // don't start this window until yesterday's same window succeeded
      "dependsOn": [{
        "type": "TumblingWindowTriggerDependencyReference",
        "referenceTrigger": { "referenceName": "TillsHourly", "type": "TriggerReference" },
        "offset": "-24:00:00", "size": "01:00:00"
      }]
    },
    "pipeline": {
      "pipelineReference": { "referenceName": "LoadTills", "type": "PipelineReference" },
      // ← THIS is the important part
      "parameters": {
        "windowStart": "@trigger().outputs.windowStartTime",
        "windowEnd":   "@trigger().outputs.windowEndTime"
      }
    }
  }
}

-- inside the Copy activity's source query
SELECT * FROM dbo.TillTransactions
WHERE ModifiedUtc >= '@{pipeline().parameters.windowStart}'
  AND ModifiedUtc <  '@{pipeline().parameters.windowEnd}';

Moving four hundred gigabytes before the sun comes up

The Copy activity is the workhorse, and it is a good deal cleverer than it looks. It has two dials worth understanding, and one mental model that will save you from turning them pointlessly.

The first dial is Data Integration Units — DIUs. A DIU is an abstract bundle of CPU, memory and network on the Azure integration runtime. You can request anywhere from 4 to 256 of them, and by default ADF picks a number for you based on what you're copying from and to. More DIUs means more machine pointed at the problem.

The second is degree of parallelism — parallelCopies — the number of concurrent threads reading from the source and writing to the sink. For a folder of two thousand supplier CSVs, that's how many files move at once. For a partitioned table, it's how many partitions stream in parallel.

Play with it. Try to make it fast, then try to work out which lever actually did it.

The un-partitioned table is the one that teaches the lesson. A single table with no partition column is a single stream — one reader, one connection, one queue. You can request 256 DIUs and 48 parallel copies and the source will still hand you one row at a time in one order. The fix is not a bigger dial; it's giving the source something to parallelise on — a partition column, a date range, a set of key ranges.

{
  "name": "CopyTillsToLake",
  "type": "Copy",
  "policy": { "retry": 3, "retryIntervalInSeconds": 120, "timeout": "02:00:00" },
  "typeProperties": {
    "source": {
      "type": "SqlServerSource",
      "sqlReaderQuery": {
        "value": "SELECT * FROM dbo.TillTransactions WHERE ModifiedUtc >= '@{pipeline().parameters.windowStart}' AND ModifiedUtc < '@{pipeline().parameters.windowEnd}'",
        "type": "Expression"
      },
      // give the source something to parallelise on
      "partitionOption": "PhysicalPartitionsOfTable"
    },
    "sink": {
      "type": "ParquetSink",
      "storeSettings": { "type": "AzureBlobFSWriteSettings" }
    },
    // omit these two and ADF chooses for you — which is usually right
    "dataIntegrationUnits": 32,
    "parallelCopies": 8,

    // two-hop through blob, compressed — worth it over a slow link
    "enableStaging": true,
    "stagingSettings": {
      "linkedServiceName": { "referenceName": "stgMeridian", "type": "LinkedServiceReference" },
      "path": "adf-staging",
      "enableCompression": true
    }
  }
}

One pipeline that loads two hundred tables

Here is where teams either discover the pattern that makes ADF scale, or quietly build themselves a maintenance nightmare that takes two years to admit to.

The nightmare version is reasonable-looking: one pipeline per table. Table one gets a pipeline, a dataset, a trigger. So does table two. By table two hundred you have six hundred objects, no two quite alike, and a change to the retry policy is a fortnight of clicking.

The pattern that works is metadata-driven: one pipeline, one parameterised dataset, and a control table that lists what to load. Want to add a table? Insert a row. The pipeline doesn't change. Neither does the trigger. Nothing gets clicked.

Below is that pipeline, running. The Lookup reads the control table; the ForEach fans out across every table it found. Change the batch count and watch the wall-clock time move.

Notice the trade in the batch count. One at a time is gentle and slow. Twenty at a time is fast right up until your source database notices twenty concurrent extract queries and starts refusing connections — or your on-prem IR machine runs out of memory. The batch count is not a performance setting; it is a politeness setting, and the correct value is whatever the system on the other end can tolerate at 02:00 without paging its own owner.

When copying isn't enough

Everything so far has been movement. Real pipelines also reshape — join tills to products, deduplicate supplier rows, pivot a week into columns. ADF gives you two honest options.

Mapping data flows let you draw the transformation — join, aggregate, derive, pivot — and ADF compiles it to Spark and runs it on a cluster it manages. You never write Scala and you never manage a cluster. The costs to know: a cold cluster takes a few minutes to start before any of your data moves, and clusters are billed per vCore-hour with a floor of 8 vCores. That startup is why a pipeline with six sequential data flows can spend more time booting than working — and why the integration runtime's time-to-live setting exists, keeping a cluster warm between sequential runs so the second flow doesn't pay the startup toll again.

Or delegate. If the transformation already lives somewhere — a stored procedure your DBAs trust, a Databricks notebook your data scientists own, a Function that calls a pricing API — ADF calls it, waits, and handles the failure. That's the dispatcher doing its actual job. A pipeline made mostly of "go ask that system to do the thing it's good at" is not a cop-out; it's frequently the correct architecture.

CREATE TABLE ctl.LoadRegistry (
  LoadId          INT IDENTITY PRIMARY KEY,
  SourceSchema    SYSNAME      NOT NULL,
  SourceTable     SYSNAME      NOT NULL,
  SinkPath        NVARCHAR(400) NOT NULL,
  WatermarkColumn SYSNAME      NOT NULL,
  LastWatermark   DATETIME2    NOT NULL,   -- how far we got last time
  BusinessKey     NVARCHAR(200) NOT NULL,   -- what makes a row unique
  IsEnabled       BIT          NOT NULL DEFAULT 1,
  LoadGroup       TINYINT      NOT NULL DEFAULT 1    -- lets you stagger the night
);

{
  "name": "LoadEverything",
  "properties": {
    "parameters": { "loadGroup": { "type": "int", "defaultValue": 1 } },
    "activities": [
      {
        "name": "GetTableList",
        "type": "Lookup",
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT * FROM ctl.LoadRegistry WHERE IsEnabled = 1 AND LoadGroup = @{pipeline().parameters.loadGroup}"
          },
          "firstRowOnly": false       // ← forget this and you get exactly one table
        }
      },
      {
        "name": "ForEachTable",
        "type": "ForEach",
        "dependsOn": [{ "activity": "GetTableList", "dependencyConditions": ["Succeeded"] }],
        "typeProperties": {
          "items": { "value": "@activity('GetTableList').output.value", "type": "Expression" },
          "isSequential": false,
          "batchCount": 6,             // be kind to the source
          "activities": [
            {
              "name": "CopyOneTable",
              "type": "ExecutePipeline",
              "typeProperties": {
                "pipeline": { "referenceName": "LoadOneTable", "type": "PipelineReference" },
                "waitOnCompletion": true,
                "parameters": {
                  "schemaName":      "@item().SourceSchema",
                  "tableName":       "@item().SourceTable",
                  "watermarkColumn": "@item().WatermarkColumn",
                  "lastWatermark":   "@item().LastWatermark",
                  "businessKey":     "@item().BusinessKey",
                  "sinkPath":        "@item().SinkPath"
                }
              }
            }
          ]
        }
      }
    ]
  }
}

The night it went wrong

It always goes wrong somewhere. A supplier ships a CSV with an unescaped comma in a product description. The on-prem SQL box starts its backup window and stops answering. A carrier API decides you've asked enough questions for one night and starts returning 429.

None of this is exceptional. All of it is Tuesday. The measure of a pipeline is not whether it fails — it's what the failure costs you.

ADF gives you three tools, and then asks you one question that it cannot answer on your behalf.

The tools: retry policies per activity, because most 3am failures are transient and a second attempt four minutes later just works. Failure paths, because activity dependencies aren't only "then" — they're Succeeded, Failed, Completed and Skipped, so you can wire a real handler that logs the error, writes the bad file to a quarantine folder, and raises an alert. And rerun from the failed activity, so a pipeline that died on step nine of eleven doesn't re-copy four hundred gigabytes to get back to where it was.

That property is idempotency, and it is not a setting. It's a design decision you make in how you write to the sink. Get it right and reruns are boring. Get it wrong and every incident has a second, worse incident hiding inside it — the cleanup.

Break the night below, both ways.

Same failure. Same rerun. Two completely different mornings — and the difference was decided weeks earlier, by whoever chose how the sink gets written.

The append version is not stupid; it's just optimistic. It assumes every run happens exactly once, which is true until the first time it isn't. The upsert version makes a stronger promise: running me twice is the same as running me once. That promise is what lets you retry automatically, backfill freely, and rerun at 03:00 without first having to reason about what's already in the table.

And tell someone

A failure nobody hears about is just a slower, more expensive kind of data loss. ADF emits metrics and run logs to Azure Monitor, so the useful setup is small and worth doing on day one: alert on pipeline failed, alert on pipeline elapsed time above the usual (a run that used to take twenty minutes and now takes ninety is a real signal that nothing else catches), and route both to the place people actually look. Then keep the run history somewhere that outlives ADF's own retention, because the question "what did this pipeline do six weeks ago?" arrives eventually and it always arrives urgently.

{
  "name": "CopySupplierFile",
  "type": "Copy",
  // most 3am failures are transient — try again before waking anyone
  "policy": { "retry": 3, "retryIntervalInSeconds": 240, "timeout": "01:00:00" }
},
{
  "name": "QuarantineBadFile",
  "type": "Copy",
  // runs ONLY if the copy above exhausted its retries and failed
  "dependsOn": [{ "activity": "CopySupplierFile", "dependencyConditions": ["Failed"] }]
},
{
  "name": "RecordOutcome",
  "type": "SqlServerStoredProcedure",
  // runs either way — this is how the control table learns what happened
  "dependsOn": [{ "activity": "CopySupplierFile", "dependencyConditions": ["Completed"] }]
}

CREATE PROCEDURE ctl.MergeTills
    @WindowStart DATETIME2,
    @WindowEnd   DATETIME2
AS
BEGIN
  SET XACT_ABORT ON;
  BEGIN TRAN;

  MERGE dwh.TillTransactions AS tgt
  USING (SELECT * FROM stg.TillTransactions
         WHERE ModifiedUtc >= @WindowStart AND ModifiedUtc < @WindowEnd) AS src
     ON tgt.StoreId = src.StoreId              -- the business key,
    AND tgt.TillId  = src.TillId               -- not an identity column
    AND tgt.TxnNumber = src.TxnNumber
  WHEN MATCHED THEN UPDATE SET
        tgt.Amount = src.Amount, tgt.ModifiedUtc = src.ModifiedUtc
  WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...);

  -- only advance the watermark once the merge has committed
  UPDATE ctl.LoadRegistry
     SET LastWatermark = @WindowEnd
   WHERE SourceTable = 'TillTransactions';

  COMMIT;
END

Why the board is right

Because at 22:00 a trigger fired. Because a pipeline read a list rather than hard-coding one. Because an agent inside the datacentre dialled out instead of a firewall being opened. Because each run owned a slice of time and knew it. Because when the supplier file was malformed, three retries happened, a quarantine folder filled, an alert went out, and the other hundred and ninety-nine files carried on. Because when someone reran the failed step at 03:10, the merge replaced rather than doubled.

And because none of that was invented that night. It was decided once, written down, and applied to every route in the yard.

If you're starting on Monday