Data Partitioning Strategies in ADF Copy Activity

By default, a Copy activity reads a database table through a single query on a single connection. For a few million rows that is fine. For a few hundred million it is the reason a load takes all night while the database and the network sit mostly idle. Partitioning splits the read into ranges that run in parallel. These are the data partitioning strategies the Copy activity supports, and the choices that make them work or fail.

How a partitioned copy works

For SQL Server and similar sources, the Source tab of the Copy activity has a partition option. When it is enabled, the activity runs several queries against the source at once, each fetching one slice of the table (Microsoft Learn: SQL Server connector). The number of queries is the activity's parallel copies setting: set it to four and the service generates and runs four queries.

Microsoft's documentation applies the same design to a list of stores that includes Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, SQL Server, Oracle, Teradata, Netezza and Azure Database for PostgreSQL. The details below use the SQL Server connector's wording.

The three options

Microsoft's guidance is direct: if the table has built-in partitions, use them, because performance is better. Dynamic range is the general-purpose option for everything else.

Dynamic range in detail

Three settings define it:

• Partition column. An integer or date and time column. If you leave it empty, the table's index or primary key is detected and used.
• Lower and upper bound. Optional. If omitted, the activity detects the values itself, which the documentation warns can take a long time.
• A query, optionally. With a custom query, you include a placeholder where the service inserts each partition's range.

With a custom query the source looks like this:

{
  "source": {
    "type": "SqlSource",
    "query": "SELECT OrderId, CustomerId, OrderDate, Amount FROM dbo.Orders WHERE ?DfDynamicRangePartitionCondition AND Status = 'Closed'",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
      "partitionColumnName": "OrderId",
      "partitionLowerBound": "1000000",
      "partitionUpperBound": "9000000"
    }
  }
}

The bounds are the most misread setting in the activity. Microsoft states it twice: they decide the partition stride, they are not a filter, and all rows in the table are partitioned and copied. The documentation's example makes the mechanism clear. A column with values 1 to 100, a lower bound of 20, an upper bound of 80 and four parallel copies produces four partitions: 20 and below, 21 to 50, 51 to 80, and 81 and above. The space between the bounds is cut into equal strides, and an open-ended partition at each end catches everything else.

Bounds and columns, measured

The script below is a small model of that rule. It first reproduces Microsoft's example, then splits January 2024 yellow taxi trips (NYC TLC: trip record data) into eight ranges on three different columns. It is a model for seeing the effect, not Data Factory's code.

import numpy as np
import pandas as pd


def boundaries(lower, upper, n):
    """n range partitions as in Microsoft's example: n-2 equal strides between the bounds, one open partition at each end."""
    stride = (upper - lower) / (n - 2)
    return [lower + i * stride for i in range(n - 1)]


def rows_per_partition(values, lower, upper, n):
    return np.bincount(np.searchsorted(boundaries(lower, upper, n), values, side="left"), minlength=n)


def even_bounds(lo, hi, n):
    """Bounds that cut the range lo..hi into n partitions of equal width."""
    width = (hi - lo) / n
    return lo + width, hi - width


# 1. Microsoft's example: IDs 1 to 100, lower bound 20, upper bound 80, four parallel copies
print("boundaries", boundaries(20, 80, 4), "rows", rows_per_partition(np.arange(1, 101), 20, 80, 4).tolist())

# 2. Real data: 2,964,624 taxi trips split into 8 partitions on three different columns
trips = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["tpep_pickup_datetime", "PULocationID"])
N = 8
trip_id = np.arange(len(trips))
zone = trips["PULocationID"].to_numpy()
pickup = trips["tpep_pickup_datetime"].astype("int64").to_numpy()          # microseconds
micros = lambda text: pd.Timestamp(text).value // 1000

cases = {
    "trip_id, a unique key":                 (trip_id, *even_bounds(0, len(trips), N)),
    "PULocationID, 1 to 265":                (zone, *even_bounds(1, 265, N)),
    "pickup time, column min to max":        (pickup, *even_bounds(pickup.min(), pickup.max(), N)),
    "pickup time, 1 Jan to 1 Feb 2024":      (pickup, *even_bounds(micros("2024-01-01"), micros("2024-02-01"), N)),
}
print(f"\npickup time ranges from {trips['tpep_pickup_datetime'].min()} to {trips['tpep_pickup_datetime'].max()}\n")
for name, (values, lower, upper) in cases.items():
    rows = rows_per_partition(values, lower, upper, N)
    assert rows.sum() == len(trips)                                         # bounds never drop rows
    print(f"{name:34} largest {rows.max() / len(trips):6.1%}   " + " ".join(f"{r:>9,}" for r in rows))

boundaries [20.0, 50.0, 80.0] rows [20, 30, 30, 20]

pickup time ranges from 2002-12-31 22:59:39 to 2024-02-01 00:01:15

trip_id, a unique key              largest  12.5%     370,579   370,578   370,578   370,578   370,578   370,578   370,578   370,577
PULocationID, 1 to 265             largest  31.3%      36,623   163,731   302,289   305,819   926,999   196,654   239,438   793,071
pickup time, column min to max     largest 100.0%           2         0         3         0         0         0         0 2,964,619
pickup time, 1 Jan to 1 Feb 2024   largest  15.0%     326,420   338,063   364,978   361,273   400,641   364,606   443,903   364,740

The first line matches the documentation: boundaries at 20, 50 and 80, and 20, 30, 30 and 20 rows.

The four cases on real data:

• A unique key gives eight equal partitions, 12.5% each. This is the ideal, and it is why Microsoft's first best practice is to choose a distinctive column, such as the primary key.
• The zone ID is skewed. Equal ranges of the ID do not hold equal numbers of rows. The largest partition has 31.3% of the trips, about 927,000 rows, and the smallest has under 37,000. Eight parallel queries would finish at very different times, and the copy lasts as long as the slowest.
• The pickup time, bounded by the column's minimum and maximum, puts 2,964,619 of 2,964,624 rows in one partition. The file is a January extract that contains a handful of stray timestamps, back to 2002. Stretching eight strides across twenty-one years leaves all the real data in the last one. The copy is partitioned in name and serial in practice.
• The same column with bounds of 1 January and 1 February gives eight partitions of 11% to 15%. Nothing was filtered. The stray rows simply fell into the open partitions at the ends.

That third case is the practical argument for setting the bounds yourself. Detected bounds follow the data's extremes, and extremes are where bad data lives.

Choosing the partition column

From the documentation's best practices and the measurements above:

1. Prefer a unique or near-unique column, such as the primary key, to avoid skew.
2. Make sure it is indexed. Each partition is a range query; without an index, every one of them scans the table.
3. For a date column, set the bounds to the range where the data really is.
4. Check the distribution first. A quick count by range at the source shows whether the partitions will be even.
5. If the table is physically partitioned, use that option. The documentation includes a query against the system catalog views that reports whether it is.

How many partitions

The degree of parallelism is the parallel copies setting. Microsoft's recommendation for partitioned loads is the number of DIUs, or the number of self-hosted integration runtime nodes, multiplied by 2 to 4, with a warning that too large a number can hurt performance. A partitioned source can use up to 4 DIUs per partition on the Azure integration runtime (Microsoft Learn: performance features).

Two limits sit outside Data Factory. Every partition is a concurrent query on the source, so eight partitions are eight times the read load on a database that other people are using. And each partition is a separate query: on a table that is being written to during the copy, the slices are not guaranteed to come from one consistent moment. Copy from a quiet period, a snapshot or a read replica when that matters.

ADF performance tuning covers DIUs and parallel copies in full.

The sink side: a folder, not a file

A partitioned read only helps if the write can keep up. When the sink is a file store, Microsoft recommends writing to a folder as multiple files, by giving only the folder name, and says performance is better than writing a single file. Asking for one output file funnels the parallel partitions back through one writer.

Partitioning when the source is files

File sources have no partition option, because files are already the unit of parallelism.

• Parallel copy works at the file level. The number of parallel copies in use is never more than the number of files, so one enormous file copies on one thread and a thousand moderate files copy in parallel.
• Merging files removes it. With the merge copy behaviour, file-level parallelism is not available.
• Folder layout is your partitioning. Files organised by date in the path, such as a folder per day, let a pipeline copy exactly one period by path.
• Wildcards and last-modified filters are costly on large folders. They make the activity list every file under the folder and filter afterwards. A date-partitioned path or a prefix avoids the listing (Microsoft Learn: troubleshoot copy performance).

When one activity is not enough, split the work across several: a Lookup or Get Metadata lists tables or folders, and a ForEach runs a Copy for each. Control flow in ADF covers the loop, and incremental data loading covers loading by period.

Partitioning in a copy and partitioning in Spark share an idea, even slices processed in parallel, and a failure mode, skew. Partitioning and bucketing in Spark covers the Spark side.

Mistakes to avoid

• Treating the bounds as a filter. They are not. To restrict rows, use a WHERE clause in the query.
• Partitioning on a skewed or low-cardinality column.
• Leaving bounds to detection on a column with outliers.
• Enabling a partition option and leaving parallel copies at 1, or setting it far above what the source can serve.
• Partitioning the read and writing a single file.
• Partitioning a small table. The overhead of several queries outweighs the gain.

Azure Data Factory and scalable data ingestion are covered in the Data Engineering course.

Sources

• Copy and transform data to and from SQL Server (Microsoft Learn) — Microsoft Learn
• Copy activity performance optimization features (Microsoft Learn) — Microsoft Learn
• Troubleshoot copy activity performance (Microsoft Learn) — Microsoft Learn
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission