Data Flow Debug and Performance in ADF

A data flow that takes forty minutes is rarely slow everywhere. It is slow in one place: waiting for a cluster, reading, one transformation, or the write. Tuning is mostly a matter of finding which, and data flow debug mode and the monitoring view are the tools Azure Data Factory gives you for it. This guide covers both, and the handful of settings that make the difference.

What debug mode is

Switching on Data Flow Debug starts a Spark cluster and keeps it running so that the Data preview tab can show real rows after each transformation (Microsoft Learn: debug mode). The facts that matter:

• It is billed while it runs. Microsoft states that you are charged for every hour a debug session is executing, including its time to live.
• One cluster per session. Every session a user starts from the browser is a new session with its own cluster. Five developers debugging are five clusters.
• Default size and lifetime. With the auto-resolve integration runtime, the session gets eight cores of general compute and a default 60-minute time to live. Turn the switch off when you finish.
• Preview reads a limited number of rows. The row limit is set in Debug settings. For file sources the limit applies to the rows shown, not the rows read, so point the source at a small sample file while developing.
• Up to 1,000 preview rows can be exported to CSV.

Debug settings also let you supply parameter values and substitute a sample file or table for a source, so the flow's logic stays untouched while you work on a subset.

What debug does not check

Three gaps cause most "it worked in debug" surprises.

The sink. Data preview does not write to the sink, so the sink's drivers, permissions and settings are not exercised. To check the write, run the flow from a pipeline with the pipeline's Debug button.

The production runtime. A pipeline debug run uses the debug session's cluster, not the integration runtime named in the Data Flow activity (Microsoft Learn: Data Flow activity). Timings from debug say little about the scheduled run.

Joins on sampled data. With a row limit you cannot predict which keys are read from each side, so a correct join may show no matches. Microsoft's advice is to check joins, lookups and exists against a small file of known data.

Row policies set by Alter row are also not applied in a debug session; the transformation reference explains that step.

Where the time goes

Microsoft names four possible bottlenecks (Microsoft Learn: performance and tuning guide):

1. Cluster start-up, generally 3 to 5 minutes for each job's isolated cluster.
2. Reading from the source.
3. Transformation time.
4. Writing to the sink.

The monitoring view shows which. Open a pipeline run, find the Data Flow activity and select the eyeglasses icon. Start-up time is shown for the run, and each sink lists the duration of the transformation stages that fed it and the time spent writing. The largest number is the bottleneck. Selecting a transformation also shows how its rows were spread across partitions.

Cluster size and time to live

A data flow's cluster is defined by the integration runtime used. Sizes run from 4 worker cores with 4 driver cores, the default, to 256 worker cores with 16 driver cores (Microsoft Learn: integration runtime performance).

Billing is in vCore-hours, so a larger cluster costs more per minute and should finish sooner. There is a ceiling: with more cores than partitions of data, extra cores do nothing. Microsoft's advice is to start small and scale up. For production, the Data Flow activity documentation gives 8 plus 8 cores of general purpose compute with a 10-minute time to live as the minimum recommendation for most workloads.

Time to live keeps a cluster alive after a job, so the next data flow on the same runtime skips the start-up. It needs a runtime you create; it is not available on the default auto-resolve runtime. See integration runtimes for how they are defined.

Partitioning, and why skew matters

Spark processes data in partitions, one task each. Every transformation has an Optimize tab that can repartition its output. The options are round robin, hash, dynamic range, fixed range and key, and the default, use current partitioning, is what Microsoft recommends in most cases, because repartitioning takes time and can offset what the optimizer does on its own.

When you do choose a scheme, the column decides the outcome. The script below takes January 2024 yellow taxi trips (NYC TLC: trip record data) and counts how many rows land in each partition under four choices. It is a local calculation with pandas, using a simple hash in place of Spark's, not a data flow run.

import zlib
import numpy as np
import pandas as pd

trips = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["PULocationID", "payment_type"])
trips["trip_id"] = np.arange(len(trips))
N = 200          # partitions; 200 is the default number of shuffle partitions


def hashed(column):
    # crc32 of the key, modulo the partition count: a stand-in for Spark's hash partitioner
    codes = {k: zlib.crc32(str(k).encode()) % N for k in trips[column].unique()}
    return trips[column].map(codes).value_counts().reindex(range(N), fill_value=0)


schemes = {
    "round robin":               pd.Series(np.bincount(trips["trip_id"] % N, minlength=N)),
    "hash on trip_id (unique)":  hashed("trip_id"),
    "hash on PULocationID":      hashed("PULocationID"),
    "key on payment_type":       trips["payment_type"].value_counts(),      # one partition per distinct value
}

print(f"{len(trips):,} rows, {trips['PULocationID'].nunique()} pickup zones, {trips['payment_type'].nunique()} payment types\n")
print(f"{'scheme':26} {'partitions':>10} {'empty':>6} {'largest':>10} {'share of rows':>14} {'largest/average':>16}")
for name, sizes in schemes.items():
    print(f"{name:26} {len(sizes):10} {(sizes == 0).sum():6} {sizes.max():10,} {sizes.max() / len(trips):14.1%} "
          f"{sizes.max() / sizes.mean():16.1f}")

by_zone = hashed("PULocationID").sort_values(ascending=False)
print(f"\nhash on PULocationID: the 10 largest partitions hold {by_zone.head(10).sum() / len(trips):.0%} of the rows")

2,964,624 rows, 260 pickup zones, 5 payment types

scheme                     partitions  empty    largest  share of rows  largest/average
round robin                       200      0     14,824           0.5%              1.0
hash on trip_id (unique)          200      0     15,211           0.5%              1.0
hash on PULocationID              200     51    146,053           4.9%              9.9
key on payment_type                 5      0  2,319,046          78.2%              3.9

hash on PULocationID: the 10 largest partitions hold 40% of the rows

Round robin and a hash on a unique column give 200 near-equal partitions. A hash on the pickup zone does not: trips are concentrated in a few zones, so 51 partitions are empty, the largest is about ten times the average, and ten partitions hold 40% of the rows. Key partitioning on payment type is worse, with 78% of all rows in one partition.

A stage finishes when its largest partition finishes. With the zone hash, one task does ten times the average work while most cores sit idle, and no cluster size fixes that. This is skew, and the monitoring view reports it for each transformation as skewness and kurtosis, with a partition chart. Microsoft's simple test is whether one bar is larger than the rest (Microsoft Learn: optimizing transformations).

Two further rules from the documentation:

• Single partition is strongly discouraged. It forces all data through one task.
• On read, expect a partition for about every 128 MB. The exception is Azure SQL Database, where source partitioning opens parallel connections; four or five partitions is generally ideal, because the database's I/O is the limit (Microsoft Learn: optimizing sources).

Partitioning and bucketing in Spark covers the engine underneath.

Joins and broadcasting

In a join, lookup or exists, if one stream is small enough to fit in a worker's memory, Spark can send it to every node and avoid reshuffling the large stream. This is broadcasting, and by default the engine decides for itself. Two adjustments are documented:

• Fixed, when you know which side is small.
• Off, when broadcast timeouts occur, typically because a source query is slow to return. The flow runs more slowly and completes.

A join whose condition uses literal values instead of columns from both sides runs as a cross join, a full product that is filtered afterwards. Keep column references on both sides. Spark performance tuning explains shuffles and broadcast joins in general.

Sinks

The write is often the slowest stage, and the documented causes are specific (Microsoft Learn: sink performance):

• Output to single file merges everything into one partition. It is discouraged without a business reason. Default file naming, one file per partition, writes fastest.
• Database indexes. Disabling indexes before a large load and rebuilding afterwards, in the sink's pre and post SQL scripts, can greatly improve write speed. Queries against the table are unlikely to succeed in between, so this belongs in a quiet window.
• Azure Synapse Analytics. Enable staging, so the load is done in bulk and not row by row.
• Error row handling adds a small cost, which Microsoft puts in the range of 5%.

Pipeline design

How data flows are arranged in pipelines decides how many clusters start (Microsoft Learn: optimizing pipelines).

One more setting: logging. Verbose is the default and records activity for every partition. If you do not need that detail on every run, Basic or None reduces the overhead.

A tuning checklist

1. Read the monitoring view before changing anything. Which of the four is it?
2. Start-up dominating? Use a runtime with a time to live for sequential flows.
3. Source slow? Push filters into the source query; partition an Azure SQL source.
4. One transformation slow? Look at its partition chart for skew, and at the join's broadcast setting.
5. Sink slow? Check for single-file output, indexes, and staging.
6. Only then increase the cluster size, one step, and measure again.

Azure Data Factory and Spark performance tuning are covered in the Data Engineering course.

Sources

• Mapping data flow Debug Mode (Microsoft Learn) — Microsoft Learn
• Mapping data flows performance and tuning guide (Microsoft Learn) — Microsoft Learn
• Integration Runtime Performance (Microsoft Learn) — Microsoft Learn
• Optimizing performance of transformations in mapping data flow (Microsoft Learn) — Microsoft Learn
• Optimizing source performance in mapping data flow (Microsoft Learn) — Microsoft Learn
• Sink performance and best practices in mapping data flow (Microsoft Learn) — Microsoft Learn
• Optimizing pipeline performance in mapping data flow (Microsoft Learn) — Microsoft Learn
• Data Flow activity (Microsoft Learn) — Microsoft Learn
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission