Parameterizing Pipelines in Azure Data Factory

The first pipeline anyone builds in Azure Data Factory copies one named file into one named table. The second copies another. By the tenth, the factory holds ten pipelines and twenty datasets that differ by a single string, and every change has to be made ten times.

Parameters are the way out. A parameterised pipeline says "copy the table I am given to the folder I am given", and the names arrive at run time. This guide covers where parameters live, how values pass between objects, and how far to take it.

Four places a parameter can live

Pipeline parameters have a type, String, Int, Float, Bool, Array, Object or SecureString, and an optional default. One property defines them: they cannot be modified during a run (Microsoft Learn: parameters and variables). A parameter is an input. For a value that changes while the pipeline runs, use a variable; global parameters and variables covers the difference.

How a value travels

Nothing is global by accident. Each object sees only its own parameters, and a value must be handed down explicitly:

1. A trigger, a parent pipeline or a person supplies values for the pipeline's parameters.
2. An activity in the pipeline references a dataset and supplies values for the dataset's parameters, usually from the pipeline's.
3. The dataset references a linked service and supplies values for the linked service's parameters.

That chain is the whole mechanism. When a parameterised pipeline fails with an empty path or table name, the cause is almost always a hop where the value was not passed on.

A generic copy

The JSON below is written from the documentation and has not been deployed. It defines two generic datasets and one pipeline that can copy any table to a dated folder.

{
  "name": "SqlTableGeneric",
  "properties": {
    "type": "AzureSqlTable",
    "linkedServiceName": { "referenceName": "SalesDatabase", "type": "LinkedServiceReference" },
    "parameters": {
      "schemaName": { "type": "String" },
      "tableName": { "type": "String" }
    },
    "typeProperties": {
      "schema": { "value": "@dataset().schemaName", "type": "Expression" },
      "table": { "value": "@dataset().tableName", "type": "Expression" }
    }
  }
}

{
  "name": "LakeParquetGeneric",
  "properties": {
    "type": "Parquet",
    "linkedServiceName": { "referenceName": "LakeStorage", "type": "LinkedServiceReference" },
    "parameters": {
      "folder": { "type": "String" }
    },
    "typeProperties": {
      "location": {
        "type": "AzureBlobFSLocation",
        "fileSystem": "raw",
        "folderPath": { "value": "@dataset().folder", "type": "Expression" }
      }
    }
  }
}

{
  "name": "CopyTableToLake",
  "properties": {
    "parameters": {
      "schemaName": { "type": "String", "defaultValue": "dbo" },
      "tableName": { "type": "String" },
      "loadDate": { "type": "String" }
    },
    "activities": [
      {
        "name": "CopyTable",
        "type": "Copy",
        "inputs": [
          {
            "referenceName": "SqlTableGeneric",
            "type": "DatasetReference",
            "parameters": {
              "schemaName": "@pipeline().parameters.schemaName",
              "tableName": "@pipeline().parameters.tableName"
            }
          }
        ],
        "outputs": [
          {
            "referenceName": "LakeParquetGeneric",
            "type": "DatasetReference",
            "parameters": {
              "folder": "@concat('sales/', pipeline().parameters.tableName, '/load_date=', pipeline().parameters.loadDate)"
            }
          }
        ],
        "typeProperties": {
          "source": { "type": "AzureSqlSource" },
          "sink": { "type": "ParquetSink" }
        }
      }
    ]
  }
}

Follow one value. The pipeline receives tableName. In inputs it hands @pipeline().parameters.tableName to the dataset's tableName parameter. Inside the dataset, @dataset().tableName fills the table property. For the sink, the pipeline builds a folder path with concat from the table name and the load date and passes it to the Parquet dataset.

Two datasets and one pipeline now copy every table in the database.

The expression syntax, briefly

Any JSON string value can be an expression, evaluated at run time, if it begins with @ (Microsoft Learn: expressions and functions). Two forms are used above:

• A whole-value expression: @pipeline().parameters.tableName. The result keeps its type.
• String interpolation: "sales/@{pipeline().parameters.tableName}". Expressions inside @{ } are embedded in text, and the result is always a string.

ADF expressions and functions covers the functions.

Supplying the values

A parameterised pipeline is fed in one of four ways:

• Manually, in the debug or trigger-now dialog. Useful for a rerun of one table.
• From a trigger, which can pass fixed values or its own properties, such as a window's start time as the load date.
• From a parent pipeline, with the Execute Pipeline activity. A common shape is a parent that loops over a list and calls the generic child once per table.
• From a control table, read with a Lookup activity and iterated with ForEach.

The last is the step from parameterised to metadata-driven: the list of tables lives in data, not in pipeline definitions, and adding a table means inserting a row. Metadata-driven pipelines builds that out.

Linked services and environments

Linked services take parameters too, which lets one definition serve many databases on a server. Use them for things like a database name. Do not use them for secrets: Microsoft's guidance is to keep passwords in Key Vault and, if they must vary, to parameterise the secret name (Microsoft Learn: parameterize linked services).

For values that differ between development and production, such as a storage account name or an alert address, global parameters are the right home. They are constants across the factory and can be overridden per environment during deployment.

How far to take it

Parameterising has a cost: a fully generic pipeline is harder to read, and the monitoring view shows fifty runs of CopyTableToLake that can only be told apart by their parameter values.

• Parameterise what varies between runs: table, path, date, source system.
• Keep fixed what defines the pipeline's purpose. A pipeline whose every property is an expression does nothing in particular.
• Give parameters defaults where a sensible one exists, and none where a wrong default would be dangerous.
• Validate early. An If Condition or a Fail activity at the top of the pipeline, checking that required parameters are not empty, turns a confusing downstream error into a clear one.
• Name runs. Put the table name into a user property or annotation so that monitoring is readable.
• Do not parameterise away structure. Two tables that need different handling deserve two pipelines, not one pipeline with a flag for each difference.

Mistakes to avoid

• Hard-coded names left inside "generic" datasets.
• A parameter defined and never passed, leaving it empty at run time.
• Using a parameter where a variable is needed. Parameters cannot change during a run.
• String-building SQL from parameters without care. A table name supplied from outside ends up inside a query; restrict such values to a known list.
• Secrets as parameters. Ordinary string parameter values are visible in run history.

For where this sits in the learning order, see the Azure Data Factory roadmap.

Azure Data Factory and reusable pipeline design are covered in the Data Engineering course.

Sources

• Pipeline parameters and variables in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Expressions and functions in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Parameterize linked services in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Global parameters in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Execute Pipeline activity in Azure Data Factory (Microsoft Learn) — Microsoft Learn