ADF Data Flow Transformations: A Complete Reference

Microsoft's documentation lists 26 transformations for mapping data flows in one long table (Microsoft Learn: transformation overview). That is a convenient layout for looking one up and a poor one for learning them. This reference groups the data flow transformations by the job you are trying to do, gives the SQL equivalent of each, and records the documented detail most likely to cost you an afternoon.

If data flows are new to you, start with mapping data flows in ADF.

The two ends: Source and Sink

Every flow needs at least one of each.

Source reads from a dataset or from an inline definition. Its Projection tab holds the columns and types, and for untyped files such as CSV that is where you set them. Options control sampling, schema drift and whether the run fails if the data does not match the projection.

Sink writes the result. For database sinks the update method is insert by default; updates, upserts and deletes need an Alter row step first. A sink can also be a cache, held in Spark memory and read back by an expression elsewhere in the same flow.

Shaping columns

Details worth knowing:

• Aggregate drops what you do not name. Columns that are neither grouped nor aggregated do not flow through. Microsoft's two remedies are to carry them with a function such as first() or last(), or to rejoin them afterwards (Microsoft Learn: Aggregate). The same technique, grouping on the key and taking first() of everything else, is the documented way to remove duplicates.
• Pivot can hide its own output. If you do not list the pivot key values, the columns are created at run time, so they do not appear in Inspect and cannot be referenced by name downstream.
• Window is not the tool for a whole-table rank. For a rank or row number across the entire dataset, Microsoft recommends the Rank and Surrogate key transformations, which perform better (Microsoft Learn: optimizing transformations). Window is for calculations within groups, the subject of window functions in SQL.
• A surrogate key always starts where you tell it. To continue from the highest key already in the table, read that maximum, hold it in a cache sink, and add it in a derived column.
• Select with rules drops the rest. In rule-based mapping, a column that matches no rule is removed.

Filtering and marking rows

Sort is fragile. A data flow runs on Spark across partitions, and any later step that repartitions the data can discard the order. The documented advice is to keep the sort as close to the sink as possible (Microsoft Learn: Sort). Sorting before a join, a habit from SSIS, is unnecessary and is not recommended.

Alter row marks; the sink acts. Conditions are checked in order and the first one that matches decides the row's policy. Three conditions must hold for anything to happen (Microsoft Learn: Alter row):

1. The sink is a database, REST or Azure Cosmos DB sink.
2. The sink's settings allow that policy, and name the key columns for updates, upserts and deletes.
3. The flow is run from a pipeline. In a debug session the preview shows the marks, and no rows are changed.

Microsoft's own example, with a column computed earlier that holds the intended action:

SpecifyUpsertConditions alterRow(insertIf(alterRowCondition == 'insert'),
    updateIf(alterRowCondition == 'update'),
    deleteIf(alterRowCondition == 'delete')) ~> AlterRow

Assert has three kinds of rule. Expect true, for a condition every row must meet; expect unique, for a column or expression; and expect exists, which checks a row against a second stream. A failed row can be routed away downstream using isError() and hasError(), or the whole flow can be set to fail.

Combining and splitting streams

These are the transformations that change the number of rows, and where most wrong results come from. The script below runs the SQL equivalent of each on the Artist and Album tables of the Chinook sample database (Chinook on GitHub). It is SQLite, not Data Factory; the point is the counts.

import sqlite3

db = sqlite3.connect("chinook.sqlite")
count = lambda sql: db.execute(f"SELECT COUNT(*) FROM ({sql})").fetchone()[0]

print(f"{'left stream: Artist':46} {count('SELECT * FROM Artist'):6}")
print(f"{'right stream: Album':46} {count('SELECT * FROM Album'):6}")
print()

on = "ON a.ArtistId = b.ArtistId"
streams = {
    "Join, inner":                    f"SELECT * FROM Artist a JOIN Album b {on}",
    "Join, left outer":               f"SELECT * FROM Artist a LEFT JOIN Album b {on}",
    "Join, right outer":              f"SELECT * FROM Artist a RIGHT JOIN Album b {on}",
    "Join, full outer":               f"SELECT * FROM Artist a FULL OUTER JOIN Album b {on}",
    "Join, cross (no condition)":      "SELECT * FROM Artist a CROSS JOIN Album b",
    "Exists":                          "SELECT * FROM Artist a WHERE EXISTS (SELECT 1 FROM Album b WHERE b.ArtistId = a.ArtistId)",
    "Exists, type 'doesn't exist'":    "SELECT * FROM Artist a WHERE NOT EXISTS (SELECT 1 FROM Album b WHERE b.ArtistId = a.ArtistId)",
    "Lookup, match multiple rows on": f"SELECT * FROM Artist a LEFT JOIN Album b {on}",
    "Lookup, one match per row":       "SELECT a.*, (SELECT MIN(b.Title) FROM Album b WHERE b.ArtistId = a.ArtistId) AS Title FROM Artist a",
    "Union":                           "SELECT Name FROM Artist UNION ALL SELECT Title FROM Album",
}
for name, sql in streams.items():
    print(f"{name:46} {count(sql):6}")

print()
print("Conditional split of Artist on album count, first matching condition wins:")
split = db.execute("""
    SELECT CASE WHEN n = 0 THEN 'noAlbums' WHEN n = 1 THEN 'oneAlbum' ELSE 'severalAlbums' END AS stream, COUNT(*)
    FROM (SELECT a.ArtistId, COUNT(b.AlbumId) AS n FROM Artist a LEFT JOIN Album b ON a.ArtistId = b.ArtistId GROUP BY a.ArtistId)
    GROUP BY stream ORDER BY stream
""").fetchall()
for stream, rows in split:
    print(f"  {stream:44} {rows:6}")
print(f"  {'total':44} {sum(r for _, r in split):6}")

left stream: Artist                               275
right stream: Album                               347

Join, inner                                       347
Join, left outer                                  418
Join, right outer                                 347
Join, full outer                                  418
Join, cross (no condition)                      95425
Exists                                            204
Exists, type 'doesn't exist'                       71
Lookup, match multiple rows on                    418
Lookup, one match per row                         275
Union                                             622

Conditional split of Artist on album count, first matching condition wins:
  noAlbums                                         71
  oneAlbum                                        148
  severalAlbums                                    56
  total                                           275

What the counts say:

• Inner join gives 347, left outer 418. The difference, 71, is artists with no album. An inner join silently drops them.
• Exists gives 204, not 347. Exists filters the left stream and adds nothing, so each artist appears once however many albums match. Its opposite gives the 71. This is the transformation for "which rows are new?" and "which rows have disappeared?".
• Lookup depends on one switch. With match multiple rows on, it behaves as a left outer join: 418. Off, it returns one row per input row: 275. Leaving it on when the other side has duplicates is how a fact table quietly grows.
• A join with no condition gives 95,425 rows, every pairing of 275 and 347.
• Conditional split accounts for every row: 71, 148 and 56 make 275. That holds because each row goes to the first condition it matches. Set to all matching conditions, a row can go to several streams.

Further details from the documentation:

• Five join types are supported: inner, left outer, right outer, full outer and custom cross join. Conditions other than equality, and conditions with OR, need the custom cross join (Microsoft Learn: Join).
• After a lookup, a function reports whether each row matched: isMatch(), which a conditional split can use to separate the unmatched rows. A lookup on a condition other than equality needs fixed broadcasting set on the Optimize tab (Microsoft Learn: Lookup).
• New branch appears only when something already follows the transformation you are branching from.

Nested data

Flatten is the one used daily, for JSON from APIs. One behaviour to remember: a null or empty array still produces one output row, with nulls in the unrolled columns. Unrolling several arrays in one Flatten produces every combination of their values, which is rarely what was intended.

Reuse and calling out

• Flowlet packages a group of transformations with defined inputs and outputs, so the same cleaning logic can be used in many flows.
• External call sends each row to a REST endpoint and adds the response to the stream. Row-by-row calls are slow at volume; use it for small streams.

Which transformation for which job

The last row is a pattern in its own right, covered in slowly changing dimensions in data flows.

A note for anyone planning a move to Microsoft Fabric: Microsoft lists Alter row, Assert, Window and Stringify as not supported in Dataflow Gen2, the Power Query based dataflow in Fabric (Microsoft Learn: Fabric guide for mapping data flow users). Flows that depend on them need a different design there, or the mapping data flow transforms that the same guide describes as available in Fabric in preview.

For how joins and partitioning affect run time, see data flow debug and performance.

Azure Data Factory and data transformation with Spark are covered in the Data Engineering course.

Sources

• Mapping data flow transformation overview (Microsoft Learn) — Microsoft Learn
• Aggregate transformation in mapping data flow (Microsoft Learn) — Microsoft Learn
• Join transformation in mapping data flow (Microsoft Learn) — Microsoft Learn
• Lookup transformations in mapping data flow (Microsoft Learn) — Microsoft Learn
• Alter row transformation in mapping data flow (Microsoft Learn) — Microsoft Learn
• Sort transformation in mapping data flow (Microsoft Learn) — Microsoft Learn
• Optimizing performance of transformations in mapping data flow (Microsoft Learn) — Microsoft Learn
• A guide to Fabric Dataflows for Azure Data Factory Mapping Data Flow users (Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub