Performance Tuning in Azure Data Pipelines

A pipeline that takes 8 hours to run every night leaves little margin for failures and reruns. Performance tuning brings that runtime down to 45 minutes — giving your team confidence and your business fresher data. This topic covers the most impactful techniques for tuning Azure data pipelines across ADF, Databricks, and Synapse.

Why Pipelines Run Slowly — The Common Root Causes

Before tuning anything, diagnose the actual bottleneck. A pipeline that seems slow overall is usually slow because of one specific stage. Tuning everything else without fixing that stage produces little improvement.

The four most common causes of slow pipelines:

  • Data skew: One partition or node handles far more data than others
  • Insufficient compute: The cluster or service tier is undersized for the data volume
  • Unnecessary data movement: Reading all columns when only a few are needed; reading all rows when most are unchanged
  • Suboptimal file formats or sizes: Too many small files; uncompressed data; row-oriented formats used for analytical queries

Performance Tuning in Azure Data Factory

Parallelism in Copy Activity

The Copy Activity has a Degree of Copy Parallelism setting (also called DIU — Data Integration Units). Increasing this setting allocates more compute power to the copy operation. The default is auto. For large file copies, manually setting a higher DIU (8, 16, 32) dramatically increases throughput.

Additionally, the Copy Activity supports Parallel Copy — the number of threads reading from the source simultaneously. For partitioned sources like Azure SQL, setting this to the number of table partitions means multiple threads read different partitions at the same time.

Staging Copy for Large Transfers

When copying data between two systems that cannot transfer directly at high speed, ADF supports staged copy through ADLS Gen2. Data copies from source to staging storage first, then from staging to the destination. This uses the Azure backbone for both hops rather than routing through the integration runtime, which is faster for large volumes.

Filtering Early

In Data Flows, apply Filter and Select transformations as early as possible — immediately after the Source. Dropping unwanted rows and columns early means all subsequent transformations work on a smaller dataset. This is one of the highest-impact, lowest-effort optimizations available.

Data Flow Partitioning

Mapping Data Flows run on Spark. In the Optimize tab of each transformation, you can set the partitioning strategy. For most transformations, use Use Current Partitioning. After a Join or Aggregate transformation that causes a shuffle, use Set Partitioning to repartition the data optimally for the next step.

Performance Tuning in Azure Databricks

The Small Files Problem

When a Spark job writes data, it creates one output file per partition. If a job has 200 partitions but each contains only a few kilobytes of data, you get 200 tiny files. Reading 200 tiny files later is dramatically slower than reading 5 well-sized files of the same total size — because each file open is a separate storage operation.

The ideal Parquet or Delta file size is between 128 MB and 1 GB. Use coalesce() or repartition() before writing to control the output file count.

# Too many small files — don't do this
df.write.format("delta").save(output_path)

# Better — coalesce to a reasonable number of files
df.coalesce(10).write.format("delta").save(output_path)

# Repartition by a column — useful when downstream reads filter by that column
df.repartition("year", "month").write.format("delta") \
    .partitionBy("year", "month") \
    .save(output_path)

Delta Table OPTIMIZE and ZORDER

Over time, Delta tables accumulate many small files from incremental writes. The OPTIMIZE command compacts them into larger files. ZORDER co-locates related rows in the same files — so queries filtering on a specific column skip most files entirely.

-- Compact small files in a Delta table
OPTIMIZE silver.sales

-- ZORDER by columns commonly used in WHERE clauses
OPTIMIZE silver.sales ZORDER BY (region, order_date)

Run OPTIMIZE regularly on frequently updated Delta tables — once a day or once a week depending on write volume.

Broadcast Join — Eliminating Shuffle for Small Tables

When joining a large table with a small table in Spark, a regular join shuffles data across the network — which is expensive. A Broadcast Join sends a copy of the small table to every node in the cluster. Each node joins its portion of the large table locally, with no network shuffle.

from pyspark.sql.functions import broadcast

# Broadcast the small dimension table to avoid shuffle
df_joined = df_sales.join(
    broadcast(df_product),
    on="product_id",
    how="left"
)

Use broadcast joins when one table is under 200 MB (the default threshold). For larger tables, let Spark choose the join strategy automatically or use sort-merge join.

Caching Frequently Reused DataFrames

If the same DataFrame is used multiple times in a notebook — such as a cleaned base dataset that feeds three different aggregation branches — cache it in memory. Without caching, Spark recomputes it from scratch each time it is referenced.

# Cache a DataFrame that is used multiple times downstream
df_clean = df_raw.filter(...).withColumn(...).cache()

# Force the cache to materialize now
df_clean.count()

# Use df_clean multiple times without recomputation
df_by_region = df_clean.groupBy("region").agg(...)
df_by_product = df_clean.groupBy("product_id").agg(...)

Right-Sizing the Cluster

A cluster that is too small runs slowly because data spills to disk when memory fills up. A cluster that is too large wastes money. Use Databricks cluster metrics to observe CPU utilization, memory usage, and storage spill. If spill to disk is occurring, increase the memory per node or add more nodes. If CPU utilization stays below 30% throughout the job, reduce cluster size.

Performance Tuning in Azure Synapse Dedicated SQL Pool

Statistics — Helping the Query Optimizer

The Synapse query optimizer decides how to execute each query. It uses statistics — information about the data distribution in each column — to make smart decisions. When statistics are stale or missing, the optimizer makes poor decisions, leading to slow queries.

-- Create statistics on columns used in WHERE and JOIN clauses
CREATE STATISTICS stats_order_date ON fact_sales (order_date);
CREATE STATISTICS stats_customer_id ON fact_sales (customer_id);

-- Update statistics after loading large amounts of new data
UPDATE STATISTICS fact_sales;

Materialized Views

A Materialized View stores the result of a query physically, like a table. When users run queries that match the materialized view's pattern, Synapse redirects them to the pre-computed result instead of recomputing from scratch. Complex aggregations that took 3 minutes run in 5 seconds.

Result Set Caching

Synapse caches the exact result of a query. If the same query runs again and the underlying data has not changed, Synapse returns the cached result instantly — zero compute consumed. This significantly reduces cost for dashboards that repeatedly run the same queries.

-- Enable result set caching at the database level
ALTER DATABASE mysynapse SET RESULT_SET_CACHING ON;

Key Points

  • Profile before tuning — find the actual bottleneck rather than optimizing randomly
  • Apply Filter and Select as early as possible in ADF Data Flows to reduce processing volume
  • Compact Delta tables with OPTIMIZE and ZORDER regularly to fix the small files problem
  • Use Broadcast Joins in Spark when one side of the join is under 200 MB to eliminate expensive shuffle operations
  • Cache DataFrames that are reused multiple times in the same notebook
  • Keep Synapse statistics up to date — stale statistics cause the query optimizer to make poor execution plan choices
Previous lessons
Back to courses
Next lessons

Leave a Comment