In this post we are going to cover mapping data flows in Azure Data Factory: what they are, what runs underneath, and how we build, tune and choose them. These are 7 real Azure Data Factory interview questions, asked the way they come up on a real project, and the last one is a scenario where your team must choose between a mapping data flow, a Databricks notebook and SQL for a new transformation.
A mapping data flow is a transformation you draw as a graph in ADF Studio instead of writing code. ADF turns that graph into Spark and runs it for you. Interviewers ask about it because it sits right between the pipeline and the heavy compute, and because the hard parts (SCD Type 2, alter row, cluster start-up, tuning) are exactly where real projects lose time.
Part 8 of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Question 1: A manager asks why we'd use mapping data flows instead of a stored procedure. What are mapping data flows, and what runs underneath?
- Question 2: You need to join, aggregate and add derived columns before loading. Which transformations do you use?
- Question 3: The dimension must keep history of changed customer addresses. How do you build SCD Type 2 in a data flow?
- Question 4: The sink must insert new rows, update changed rows and delete removed ones. How do alter row and sink settings work?
- Question 5: Every data flow run spends minutes starting the cluster. What are debug mode, cluster start-up and time to live, and how do you reduce the wait?
- Question 6: A data flow on a hundred million rows is slow. How do you tune partitioning and compute size?
- Scenario 7: Your team must choose between a mapping data flow, a Databricks notebook and SQL for a new transformation. How do you decide?
The short answer
Mapping data flows are visual transformations that ADF runs on managed Spark (Spark 3.4) on an Azure IR, billed per vCore-hour. Build SCD Type 2 from lookup, derived column, conditional split, alter row and sinks with key columns. Cut start-up with TTL on a custom Azure IR, and tune by reading stage times before you add cores.
Keep that in mind. Every question below is one piece of it.
Question 1: A manager asks why we'd use mapping data flows instead of a stored procedure. What are mapping data flows, and what runs underneath?
A mapping data flow is a visual transformation you design in ADF Studio. ADF turns it into Spark code and runs it on a cluster it manages for you, on Spark 3.4 today.
Let's walk through the pieces in order:
- First, you build the graph in the data flow designer: sources, transformations, sinks.
- Then a Data Flow activity in a pipeline starts it.
- The work runs on a managed Spark cluster on an Azure integration runtime, not a self-hosted one.
- It is billed per vCore-hour.
- The sinks can be SQL, the lake or Delta.
Now the manager's question. A stored procedure runs inside the database. If the data is already in Azure SQL or Synapse, that is often the cheapest and fastest answer, and I would say so. Data flows win when the data is in the lake: files, Parquet, Delta, several sources to join, or logic the team wants to keep as a visual graph.
| Mapping data flow | Stored procedure | |
|---|---|---|
| RUNS ON | managed Spark cluster, Azure IR only | the database engine |
| BILLED | vCore-hours | database compute |
| BEST WHEN | files in the lake, many sources | data already in SQL |
| START-UP | about 3 to 5 minutes cold | no cluster to start |
The honest cost is start-up: a cold cluster takes about 3 to 5 minutes before any data moves.
ADF is fully supported. Converting mapping data flows to Dataflow Gen2 in Fabric is still in preview, so don't promise a smooth move.
Question 2: You need to join, aggregate and add derived columns before loading. Which transformations do you use?
The core trio is join, derived column and aggregate, between a source and a sink. Let's use a real example: orders join customers, we add calculated columns, then we aggregate by region and month, and write to a SQL table.
Step by step:
- Join. It supports inner, left outer, right outer, full outer and custom cross joins. When one side is a small dimension, broadcast it: the small side goes to every worker, so the big side doesn't shuffle.
- Derived column. It creates new columns or changes existing ones with expressions, like a net amount or an order month.
- Aggregate. It groups and summarizes. Watch out here: aggregate drops any column that is not in the group by or in an aggregate. Carry those columns with
first(), or join them back later.
Every graph is saved as data flow script. Here is the script for this example:
orders, customers join(
orders@customer_id == customers@customer_id,
joinType:'left_outer', broadcast: 'auto') ~> Joined
Joined derive(
net_amount = quantity * unit_price - discount,
order_month = toString(order_date, 'yyyy-MM')
) ~> AddColumns
AddColumns aggregate(groupBy(region, order_month),
total_sales = sum(net_amount),
order_count = count()) ~> SalesByRegionKnow the neighbors too:
- Lookup - a left outer join that flags matches with
isMatch(). - Exists - keeps rows that do, or don't, exist in another stream.
- Conditional split - routes rows to different outputs.
- Select - renames and drops columns.
Review the data flow script, not the canvas. It is much easier to compare two versions of text than two pictures.
Question 3: The dimension must keep history of changed customer addresses. How do you build SCD Type 2 in a data flow?
There is no single SCD Type 2 transformation. We build it from lookup, derived column, conditional split, alter row and sinks.
Let's build it in order:
- Sources. Two of them: the staged customers and the current dimension rows.
- Derived column. Hash the tracked columns, like address and city, into one row hash.
- Lookup and conditional split. Look up the dimension on the business key, then split the rows three ways: no match is new, a different hash is changed, and unchanged rows are dropped.
Here is that first half as data flow script. Its last two lines start path one, which comes next:
Staged derive(row_hash = sha2(256, address, city))
~> HashRows
HashRows, DimCurrent lookup(
customer_id == dim_customer_id,
multiple: false, pickup: 'any',
broadcast: 'auto') ~> LookupDim
LookupDim split(!isMatch(), row_hash != dim_row_hash,
disjoint: false) ~> SplitRows@(newRows, changed, same)
changed derive(end_date = currentDate(),
is_current = false()) ~> SetExpiry
SetExpiry alterRow(updateIf(true())) ~> ExpireOldNow the changed customers take two paths:
- Path one, expire the old row. Set the end date and set is current to false, mark the row with alter row
updateIf, and send it to an update sink keyed on the surrogate key. - Path two, insert the new version. Union it with the new customers, add a surrogate key, a start date and is current true, and write it to an insert sink.
One tip on the surrogate key: seed the surrogate key transformation from the current maximum key, or let an identity column in the table generate it.
Debug data preview doesn't apply alter row and doesn't write to sinks. Test the writes with a pipeline debug run.
Question 4: The sink must insert new rows, update changed rows and delete removed ones. How do alter row and sink settings work?
It works in two parts. Alter row marks each row with a policy, and the sink applies those marks.
First, alter row. The policies are insertIf, updateIf, upsertIf and deleteIf. Two rules matter:
- The conditions are checked in order, and the first match wins.
- A row that matches none is treated as an insert. So filter out unchanged rows before alter row.
Compared alterRow(
deleteIf(is_deleted),
updateIf(is_changed),
insertIf(true())
) ~> MarkRowsThen the sink must allow each method you use:
| Sink setting | Use |
|---|---|
| Allow insert | on by default |
| Allow update, upsert, delete | tick each one you use |
| Key columns | needed for update, upsert, delete |
| Skip writing key columns | identity keys |
Insert is on by default. Tick update, upsert or delete as needed. Those three need key columns, usually the business key. With an identity key, use skip writing key columns.
Now the tricky part: deletes. Removed rows never arrive from the source, so there is nothing to mark. Use an exists transformation, set to does not exist, to find target rows that are missing from the source, and mark them deleteIf. Or, if the source gives you change data capture, use its delete flag.
Alter row works with database, REST and Cosmos DB sinks and the inline Delta sink, not CSV or Parquet files. And upsert fails on an identity or Synapse distribution column, so split it into update and insert paths.
Question 5: Every data flow run spends minutes starting the cluster. What are debug mode, cluster start-up and time to live, and how do you reduce the wait?
Each data flow activity run gets its own just in time Spark cluster, and a cold start generally takes 3 to 5 minutes. Chain three data flows on the default AutoResolve runtime, and you pay that start-up three times.
The fix is time to live (TTL). Here is how we set it up:
- Create a custom Azure integration runtime with a time to live.
- Point the data flow activities at that runtime.
- Now the first data flow starts the cluster, and the next data flows in sequence reuse the warm cluster. Start-up is paid once.
The default AutoResolve runtime can't do this.
Two limits to know. A cluster runs one job at a time, so parallel data flows each start their own cluster. And idle time is billed, up to the 4 hour maximum, so keep the TTL short.
Debug mode is a different thing. It is for developers: an interactive Spark session for data preview. On AutoResolve it is 8 cores with a 60 minute time to live, and it is billed while it is on. Debug preview uses row limits and never writes to sinks.
| Debug session | TTL on a custom Azure IR | |
|---|---|---|
| PURPOSE | build and preview interactively | warm cluster for sequential runs |
| DEFAULTS | 8 cores, 60 minute TTL on AutoResolve | not available on AutoResolve |
| MAXIMUM | 8 hours | 4 hours |
| WATCH OUT | billed while on; doesn't write to sinks | idle time billed; no help for parallel flows |
Switch debug off when you are done. A debug session left on all day costs real money.
Question 6: A data flow on a hundred million rows is slow. How do you tune partitioning and compute size?
I start in monitoring, not in settings. Read the stage durations of the run. The longest stage is the bottleneck, and often it is the source read or the sink write, not the logic.
Then I go through this list, in this order:
- Stage durations - find the slowest stage.
- Use current partitioning - on the optimize tab, keep it. It is the default and the recommendation, unless a join or aggregate has skewed the data. The other options are round robin, hash, dynamic range, fixed range and key.
- No single partition - avoid it, and avoid output to single file on a big sink, because it forces one partition.
- Broadcast - the small side of a join.
- Shuffle partitions - the default is 200. You can set 50 to 2,000, aiming at about 1.5 GB each. That won't fix skew.
- Scale compute last - start small.
Compute size really does come last:
| Compute size | Driver + worker cores |
|---|---|
| Small (default) | 4 + 4 |
| Medium | 8 + 8 |
| Large | 16 + 16 |
| Custom | up to 16 + 256 |
Small is the default. Start there and scale up, because more cores than partitions just sit idle.
Filter early in the source query, or load only the changed rows. Less data beats more cores.
Scenario 7: Your team must choose between a mapping data flow, a Databricks notebook and SQL for a new transformation. How do you decide?
I ask four questions: where does the data live, who maintains the code, how complex and big is the logic, and how often does it run.
SQL in the database, when the data is already there:
- If the data is already in Azure SQL or Synapse, push the work down: a staging table and a MERGE.
- Run it from a Stored Procedure or Script activity.
- No data leaves the engine, and it is usually the cheapest and fastest option.
Databricks, a notebook or job:
- Complex logic, big volume, a team that writes Python, or data in Delta with Unity Catalog.
- ADF still orchestrates it with a Notebook or Job activity, and the code lives in Git.
Mapping data flow, low code Spark, fits the middle:
- Moderate transforms, upserts and SCD on lake data.
- A low code team that doesn't want to manage clusters.
- The costs: start-up time, vCore-hours and limited custom code.
And the current angle. ADF is fully supported, new innovation lands in Fabric Data Factory, and mapping data flow conversion to Fabric is in preview. So for a long-lived transformation, weigh portability too.
Whatever you pick, write the decision and the reasons down for the team, so the next person knows why.
Key points to remember
- Visual transforms on managed Spark 3.4, Azure IR only
- Core trio: join, derived column, aggregate
- SCD 2: lookup, split, alter row, update and insert sinks
- Alter row needs allowed methods and key columns
- Start-up: TTL on a custom IR; tune from stage times
Part 9: Monitoring, Alerting and CI/CD
Part of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Microsoft Learn page for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.