The Night
Dispatcher

The board is already right

Seven hours earlier, none of this data was in the same building, let alone in the same format. It was spread across a dozen systems that do not know each other, run on different schedules, use different formats and fail in different ways. Something had to go and fetch it. Something had to decide the order, handle the files that never arrived, retry the ones that timed out, and finish before sunrise.

That something is Azure Data Factory. And the quickest way to understand what it does is to look at the mess it was built to replace.

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

Six sources, one destination, one deadline. It sounds like six jobs. It is not, and the reason why is the whole reason this kind of tool exists.

Why six sources is never six jobs

The natural first idea is to write one script per route. One to copy billing data into the lake. One for stock. One for each supplier feed. Small, easy to understand, independent. It works well — until one day it does not, and then it fails in a way that is very hard to recover from.

There are two problems, and the second one is the one that really hurts.

The first is simple maths. Sources multiply with destinations. The lake wants raw billing data; the warehouse model wants cleaned data; finance wants a summary; the ML forecast wants its own slice. Every new destination has to connect to every existing source again. Move the slider below and watch the number of connections grow.

The second problem is the one that wakes you up at night. Every one of those scripts invents its own answer to the same six questions, each a little differently.

• When do I run? A cron job here, a Windows Task Scheduler job there, a while true loop in some service nobody remembers deploying.
• What if the source is not ready? Sleep for 300 seconds and hope. Or fail, and let the morning team find out.
• What if I fail halfway? Run everything again from the start, probably. Four hundred gigabytes, all over again.
• Whom do I inform? A log file on some VM. Sometimes an email, from an address that stopped existing when someone left the company.
• What ran last night? Nobody knows without logging in to some server.
• Where did this number come from? No answer.

So the real job is not "copy data". Copying is easy; every language has a library for it. The real job is everything around the copying: the order, the timing, the waiting, the retrying, the alerts, and the record of what happened and when. That is not plumbing. That is dispatch.

The dispatcher does not drive the trains

Here is the most useful thing to know about Azure Data Factory, and most introductions hide it: ADF is a dispatcher, not a truck. Its job is to decide what moves, in what order and when, and what happens when something does not arrive. It is a control system first and a mover second.

This matters because it tells you what to expect. ADF does move bulk data itself; its Copy activity is fast and runs many transfers in parallel, and for most data loading that is all you need. But when the work needs real computing, like joins across billions of rows, complex reshaping or machine learning, ADF does what a good dispatcher does: it gives 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 work on a Spark cluster it manages for you, which we will see later.

Six nouns run the whole product. Click through them. Each one is either something you will set up on day one, or something you will be reading about at 3 a.m. on some later day.

The one everybody skips

Five of the six nouns are easy to understand. The one that decides whether your project succeeds is the integration runtime, because it answers a question nobody asks until the security review: whose network is this happening on?

Meridian's billing database is in a datacentre, behind a firewall, and the network team will not open an incoming port so that a cloud service can reach in. That is not them being difficult; that is correct. So ADF does not reach in. You install a self-hosted integration runtime on a machine inside that network, and it connects out to Azure on port 443. The connection starts from the trusted side. No incoming port 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 very different answers, and picking the wrong one is the most common reason for the 03:00 phone call, because two of them look the same until the night something goes wrong.

The schedule trigger is a wall clock. Every day at 23:30, run this. Simple, familiar, and the one everyone picks first.

The tumbling window trigger cuts time into back-to-back slices that never overlap: 23:00–00:00, 00:00–01:00, and so on, with no gaps. Each run knows which slice belongs to it, because ADF passes WindowStart and WindowEnd as parameters.

The event trigger does not watch a clock at all. It watches a storage account, and fires when a file appears. This is how you handle two hundred suppliers who upload whenever they like: you stop checking again and again, and start reacting.

Run the night below. Let it play, then break it: cause an outage in the middle of the night and watch what each lane does about the runs it missed.

That is the whole lesson, and people lose real money over it. When the source came back at 03:00, the schedule trigger just moved on. Its 02:30 run happened during the outage and failed, and that slice of the night is now gone. Nothing will go back for it unless a person notices and does it by hand.

The tumbling window trigger went back and ran the windows it missed, in order. It can do this because it remembers: it knows which windows exist, which succeeded and which did not. That is also why it supports backfill: set the start date to last month and it will work through every window since then, running as many at a time as you allow.

So which one?

A simple 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 run leaves a hole. Use a schedule trigger for work that is really about the clock and not a period, like a nightly full refresh or a 07:00 report, where missing one run is an inconvenience and 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 does most of the heavy lifting, and it is smarter than it looks. It has two settings worth understanding, and one way of thinking that will stop you changing them for no reason.

The first setting is Data Integration Units, or DIUs. A DIU is a bundle of CPU, memory and network on the Azure integration runtime. You can ask for anywhere from 4 to 256, and by default ADF picks a number for you based on the source and destination. More DIUs means more machine power on the job.

The second is parallel copies (parallelCopies): how many threads read from the source and write to the destination at the same time. For a folder of two thousand supplier CSVs, it is how many files move at once. For a partitioned table, it is how many partitions move together.

Try it. Make it fast, then work out which setting actually did it.

The table without partitions 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 ask for 256 DIUs and 48 parallel copies and the source will still give you one row at a time, in one order. The fix is not a bigger setting. It is giving the source something to split the work on: a partition column, a date range, or 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

This is where teams either find the pattern that makes ADF scale, or build a maintenance headache that takes two years to admit.

The headache version looks sensible: one pipeline per table. Table one gets a pipeline, a dataset and a trigger. So does table two. By table two hundred you have six hundred objects, no two exactly alike, and changing the retry policy means two weeks 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 does not change. Neither does the trigger. Nothing needs clicking.

Below is that pipeline, running. The Lookup reads the control table; the ForEach spreads out across every table it found. Change the batch count and watch the total time change.

Look at the trade-off in the batch count. One at a time is gentle and slow. Twenty at a time is fast, until your source database notices twenty extract queries at once and starts refusing connections, or your on-premises runtime machine runs out of memory. The batch count is really a politeness setting, not a speed setting, and the right value is whatever the system on the other side can handle at 02:00 without waking up its own team.

When copying isn't enough

Everything so far has been about moving data. Real pipelines also reshape it: join billing data to products, remove duplicate supplier rows, turn a week into columns. ADF gives you two honest options.

Mapping data flows let you draw the transformation (join, total, add columns, pivot), and ADF turns it into Spark code and runs it on a cluster it manages. You never write Scala and never manage a cluster. Two costs to know: a cold cluster takes a few minutes to start before any data moves, and clusters are charged per vCore-hour, with a minimum of 8 vCores. That start-up time is why a pipeline with six data flows one after another can spend more time starting than working, and why the integration runtime has a time-to-live setting, which keeps the cluster warm between runs so the second flow does not pay the start-up cost again.

Or hand it over. If the transformation already lives somewhere, like a stored procedure your DBAs trust, a Databricks notebook your data scientists own, or a Function that calls a pricing API, ADF calls it, waits, and handles any failure. That is the dispatcher doing its real job. A pipeline made mostly of "ask that system to do what it is good at" is not a shortcut; very often it is the right design.

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

Something always goes wrong. A supplier sends a CSV with an extra comma inside a product description. The on-premises SQL server starts its backup and stops responding. A courier API decides you have asked enough questions for one night and starts returning 429.

None of this is unusual. It is just a normal Tuesday. A pipeline is judged not by whether it fails, but by how much the failure costs you.

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

The tools: retry policies for each activity, because most 3 a.m. failures are temporary and a second try four minutes later just works. Failure paths, because activity links are not only "then": they can be Succeeded, Failed, Completed or Skipped, so you can connect a proper handler that logs the error, moves the bad file to a quarantine folder and sends an alert. And rerun from the failed activity, so a pipeline that died at step nine of eleven does not copy four hundred gigabytes again to get back there.

That property is called idempotency, which simply means running it twice gives the same result as running it once. It is not a setting. It is a design decision about how you write to the destination. Get it right and reruns are boring. Get it wrong and every incident hides a second, worse incident inside it: the clean-up.

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 data is written to the destination.

The append version is not stupid, just hopeful. It assumes every run happens exactly once, which is true until the first time it is not. 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 working out what is already in the table.

And tell someone

A failure nobody hears about is just a slower, costlier way of losing data. ADF sends metrics and run logs to Azure Monitor, so a small, useful setup is worth doing on day one: alert when a pipeline fails, alert when a pipeline takes longer than usual (a run that used to take twenty minutes and now takes ninety is a real warning that nothing else will catch), and send both to where people actually look. Also keep the run history somewhere that lasts longer than ADF's own retention, because sooner or later someone asks "what did this pipeline do six weeks ago?", and they always ask 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 instead of hard-coding one. Because an agent inside the datacentre connected out, instead of someone opening the firewall. Because each run owned a slice of time and knew it. Because when the supplier file was broken, three retries happened, a quarantine folder filled up, 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 rows instead of doubling them.

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