ADF Expressions and Functions: A Practical Guide

A pipeline with every value typed in does one thing. A pipeline that works out today's folder, picks its table from a parameter and decides whether to run a branch is using the expression language. It is small, a little awkward, and essential: most dynamic Azure Data Factory pipelines rely on a small set of its functions.

The examples of individual functions below, with their results, are the ones published in Microsoft's reference. The composed patterns are written from that reference and were not evaluated in a factory.

When is a value an expression?

In a pipeline's JSON, a string value is either a literal or an expression evaluated at run time. The rule is the first character (Microsoft Learn: expressions and functions):

A value starting with @ is an expression. To get a literal at-sign at the start of a string, double it.

Two forms, one important difference

There are two ways to use an expression, and they do not return the same type. Microsoft's own example makes the point, with a parameter myNumber set to 42:

• A whole-value expression starts with @ and keeps the type of its result: a number stays a number, an array stays an array.
• String interpolation wraps expressions in @{ } inside text. The result is always a string.

The difference bites when a property expects a number, a boolean or an array. Passing "@{variables('count')}" where an integer is needed delivers a string. Use the whole-value form whenever the type matters, and interpolation for building text such as paths and queries.

What you can reference

The system variables are listed in their own reference (Microsoft Learn: system variables). Two details from it: trigger date and time values are in UTC, in ISO 8601 format; and TriggerTime is when the trigger actually fired, which can differ slightly from when it was scheduled.

activity('Name').output is the one you will use most. A Lookup's result is activity('GetWatermark').output.firstRow.value; a Copy's row count is activity('CopyOrders').output.rowsCopied. The activity must have run before the expression is evaluated, which means a dependency between them.

The six function families

The reference groups functions into string, collection, logical, conversion, math and date functions. A working subset, with Microsoft's documented examples:

String

concat('Hello', 'World')                      returns "HelloWorld"
substring('hello world', 6, 5)                returns "world"
replace('the old string', 'old', 'new')       returns "the new string"
split('a_b_c', '_')                           returns ["a","b","c"]
toLower('Hello World')                        returns "hello world"
trim(' Hello World ')                         returns "Hello World"

Logical and comparison

if(equals(1, 1), 'yes', 'no')                 returns "yes"
equals('abc', 'abcd')                         returns false
greater(10, 5)                                returns true

Also and, or, not, less, greaterOrEquals and lessOrEquals. Conditions are written as nested function calls, not with operators such as == or &&.

Conversion

int('10')                                     returns 10
string(10)                                    returns "10"
coalesce(null, 'hello', 'world')              returns "hello"

Also json, bool, float, createArray and base64.

Collection

contains('hello world', 'world')              returns true
join(createArray('a', 'b', 'c'), '.')         returns "a.b.c"
last(createArray(0, 1, 2, 3))                 returns 3
empty('')                                     returns true

Also length, first, union, intersection, skip and take.

Date

formatDateTime('03/15/2018 12:00:00', 'yyyy-MM-ddTHH:mm:ss')   returns "2018-03-15T12:00:00"
startOfMonth('2018-03-15T13:30:30Z')                           returns "2018-03-01T00:00:00.0000000Z"
addToTime('2018-01-01T00:00:00Z', 1, 'Day')                    returns "2018-01-02T00:00:00.0000000Z"
convertFromUtc('2018-01-01T08:00:00.0000000Z', 'Pacific Standard Time')   returns "2018-01-01T00:00:00Z"
dayOfWeek('2018-03-15T13:27:36Z')                              returns 3

Also utcNow, addDays, addHours, startOfDay, convertTimeZone and ticks.

Math: add, sub, mul, div, mod, min, max.

Patterns that cover most pipelines

These are composed from the functions above. They are not evaluated here.

A dated folder path

@concat('raw/orders/', formatDateTime(pipeline().parameters.loadDate, 'yyyy/MM/dd'))

Yesterday's date as a string

@formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')

A query built from a watermark

SELECT * FROM dbo.Orders
WHERE ModifiedAt > '@{activity('GetOldWatermark').output.firstRow.WatermarkValue}'
  AND ModifiedAt <= '@{activity('GetNewWatermark').output.firstRow.NewWatermark}'

A default when a value is missing

@coalesce(pipeline().parameters.schemaName, 'dbo')

A condition for an If activity

@greater(activity('CopyOrders').output.rowsCopied, 0)

A value from the current loop item

@concat(item().SchemaName, '.', item().TableName)

Rules that prevent most errors

• Strings use single quotes inside expressions. Double quotes belong to the JSON around them.
• To put a single quote in a string, double it.
• Copy property names exactly as they appear in an activity's output; a misspelt or wrongly cased property fails at run time.
• Dates are strings. There is no date type in the pipeline language. Date functions take and return ISO 8601 text, in UTC unless you convert.
• The current time is taken when the expression runs, not when the pipeline was scheduled, so utcNow() changes between attempts. For a load date that must be stable across retries and reruns, use a parameter, the trigger's scheduled time or a window boundary. This is the single most important habit for rerunnable pipelines.
• Null handling is explicit. Use coalesce, or the ? operator when a property may be absent, as in @pipeline()?.TriggeredByPipelineName.
• Keep them short. An expression that does not fit on a line belongs in a variable, set once and named, or in the source query.

Where expressions are not the answer

The expression language manipulates small values: names, dates, flags, counts. It is not for transforming data. If you find yourself reshaping rows inside a ForEach with nested functions, the work belongs in a query, a data flow or a notebook. Note also that mapping data flows have their own, different expression language for column-level logic; what is described here is the pipeline language.

For the main place these are used, see parameterising pipelines; for where values are stored during a run, global parameters and variables; for conditions and loops, control flow in ADF; and for window boundaries, tumbling window triggers.

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

Sources

• Expressions and functions in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• System variables supported by Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Pipeline parameters and variables in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Global parameters in Azure Data Factory (Microsoft Learn) — Microsoft Learn