Mapping Data Flow Transformations Azure Data Factory and Azure Synapse

Mapping data flow is an inbuilt data transformation tool in Azure data factory. It provides data transformation and visualization interface where no or very limited coding is needed.

There are various transformation categories such as schema modifiers, row modifier, multiple inputs and outputs, formatters, flowlets and destination or sink. These categories are having number of transformation to pefrom data transformation. Let’s see available transformation in each category and when to use them.

Schema Modifiers

Name	When to use
Select	To select needed column, add column alias, remove and reorder columns
Aggregate	To derive COUNT, MIN, MAX and SUM for a numeric column
Cast	To modify column data type with type checking
Derived Column	To add new column or modify existing column using expression language
Pivot	To transform output of an aggregation where distinct row value becomes column header and aggregate value becomes row
Rank	To generate order ranking based on sort conditions defined
External Call	To call external rest or third party APIs, get the results and use with other data sources
Surrogate Key	To add incremental unique key value for each row data.
Unpivot	To transform columns into rows values

---

Row Modifiers

Name	When to use
Alter row	To apply condition policy as an expression to Insert, Update, Delete and Upsert on rows
Assert	To apply assert rules for each row to assess data quality and data validation
Filter	To filter the rows based on a condition
Sort	To sort the data stream upon a set columns in Ascending or Descending order

---

Multiple Inputs and Outputs

Name	When to use
Conditional Split	To split the input data stream into multiple data streams based upon matching conditions
Exists	To check if data exists in data stream. This behaves as SQL EXISTS and NOT EXISTS
Join	To combine two input streams based on join conditions on columns. Data flows supports Inner Join, Left Outer, Right Outer, Full Outer and Custom Cross Join
Lookup	To reference data from input stream as per the match on conditions defined.
New Branch	To perform multiple sets of operations and transformations on the same data stream.
Union	To generate single data stream from multiple data streams

---

Formatters

Name	When to use
Flatten	To transform array values of hierarchical structures ex. JSON and unroll into individual rows
Parse	Parse text columns that are strings of JSON, delimited text, or XML formatted text.
Stringify	To transform complex objects or data types into string

---

Flowlets

Name	When to use
Flowlet	To create custom re-usable transformation logic to use in data flows

Consulsion

So, here is the list available transformations in the mapping data flow. Apart form the list, there are two the most transformations those are Source and Sink. Source transformation is an input for data flow and Sink transformation is an output for data flow. These transformations are the building blocks for the supported data formats in Azure Data Factory and Azure Synapse.

If you have any suggestions/feedback, please put them in the comment box.

Happy Learning 🙂