Copy activities and copy jobs

Tuesday, October 27, was the day of the history load. Three years of StockLine sales, 41.2 million rows, had to travel from HF-SQL01 at head office into lh_raw before the Wednesday morning hub session, where Keiko planned to start checking totals. Ines had two tools for the job, the copy activity inside a pipeline and the newer copy job, and the afternoon was about knowing which to use and how to set each one up so the load finished in hours rather than days.

Connections and the on-premises data gateway

Both tools reach sources through connections. A connection stores where a source is and how to sign in to it, and is shared like any other Fabric object, with its own owners and users. Cloud sources, such as Azure SQL Database or an Azure storage account, are reached directly. Sources inside a private network, such as StockLine on HF-SQL01, are reached through an on-premises data gateway: a small Windows service installed on a server inside the network that makes outbound connections to Fabric, so nothing in the firewall has to open inbound. Harrow & Finch already ran a gateway cluster of two servers for Power BI, and Renata Kowal, the Fabric administrator, added a connection to the StockLine database on it, signed in with a read-only SQL login created by Walt Hadley.

For sources in an Azure virtual network that blocks public access, a virtual network data gateway does the same job without a server to maintain. Fabric can also reach some secured Azure sources through trusted workspace access and the workspace identity covered in MSAZ-310. Whatever the route, the connection decides what the copy can read, so a read-only login on the source is the first control on what the hub can ever take.

The copy activity

A copy activity has a source, a destination and settings for how to move data between them. The source can be a table, a query or a set of files. The destination can be a lakehouse table or folder, a warehouse table, a database, a storage account or one of many other stores. On the destination side, the table action chooses between appending to the table and overwriting it, and the activity can create the table if it does not exist. Mapping settings say which source column goes to which destination column; by default columns are matched by name, and types are converted where the connector supports it.

Splitting a big read into partitions

Read as one query, 41.2 million rows go through a single connection, one batch after another. Copy activities for SQL sources can instead split the read into partitions and read several at once. Physical partitions use the source table's own partitioning, which StockLine's table did not have. Dynamic range partitioning needs a column with evenly spread values, such as an increasing key, and a lower and upper bound; the copy divides the range into equal slices and reads several slices in parallel. The degree of copy parallelism caps how many are read at a time.

# How dynamic range partitioning splits the SaleId range into slices
lower, upper = 1, 41_200_000      # smallest and largest SaleId in the history
partitions = 8

width = (upper - lower + 1) // partitions
for p in range(partitions):
    start = lower + p * width
    end = upper if p == partitions - 1 else start + width - 1
    print(f"partition {p + 1}: SaleId {start:>10,} to {end:>10,}  ({end - start + 1:,} ids)")

partition 1: SaleId          1 to  5,150,000  (5,150,000 ids)
partition 2: SaleId  5,150,001 to 10,300,000  (5,150,000 ids)
partition 3: SaleId 10,300,001 to 15,450,000  (5,150,000 ids)
partition 4: SaleId 15,450,001 to 20,600,000  (5,150,000 ids)
partition 5: SaleId 20,600,001 to 25,750,000  (5,150,000 ids)
partition 6: SaleId 25,750,001 to 30,900,000  (5,150,000 ids)
partition 7: SaleId 30,900,001 to 36,050,000  (5,150,000 ids)
partition 8: SaleId 36,050,001 to 41,200,000  (5,150,000 ids)

Each slice in the output covers 5,150,000 SaleId values. Equal widths only mean equal work if the key is spread evenly; if StockLine had skipped from 20 million to 30 million after a system change, two slices would be nearly empty and the copy would be no faster than four. Ines checked first with a quick query counting rows per million SaleIds, which showed no gaps. She ran the load with eight partitions and a parallelism of four, so four slices were read at a time and the next began as soon as one finished.

Fault tolerance and logging

Real data contains surprises. A copy can stop at the first row it cannot convert, or it can skip incompatible rows and write them, with the reason, to a log folder for later review. Ines chose skipping with logging for the history load, because stopping 30 million rows in would have wasted hours. The log held 212 rows from one week in 2024 when a till upgrade had written negative quantities as text. Keiko and Walt looked at them the next morning, agreed the fix, and Ines loaded the 212 rows separately. Skipping without logging would have been a silent data loss; the log is what makes skipping safe.

Copy jobs

A copy job is a separate Fabric item built for one purpose: moving data from sources to destinations without building a pipeline. It walks through choosing a source, picking tables, choosing a destination and a copy mode, then runs once or on one or more schedules. In full copy mode each run copies everything. In incremental mode the first run copies everything and later runs copy only new or changed rows, using either a watermark column, such as a datetime or rowversion that increases when a row changes, or change data capture in the source, which also captures deletes. The copy job remembers where it got to, resumes from the last successful run after a failure, and can be reset to a full copy for the whole job or for one table.

For the products and stores tables, which change a few hundred rows a day, a copy job in incremental mode with merge on the primary key was all Ines needed: no pipeline, no watermark table to maintain, a schedule at 1:15 a.m., and a run history showing rows read and written. A pipeline can still run a copy job as one of its steps with the copy job activity, which lets the job take part in a larger sequence and in event triggers.

Copy activity or copy job?

Microsoft's guidance for data movement in Fabric sets the two side by side, along with mirroring, eventstreams and dataflows. In short, a copy job suits copying many tables with built-in incremental tracking and little configuration. A copy activity suits copies that are one step among many, need custom queries or dynamic settings, or must react to the outcome of other steps. Mirroring, in lesson 10, suits a continuously synchronised copy of a supported database with no schedule at all.

The StockLine sales increment stayed in the pipeline as a copy activity, because Ines wanted the custom range query, the row count check that follows it and the failure branch from lesson 2. The two choices sit happily together: the nightly pipeline starts at 1 a.m., the copy job for small tables at 1:15 a.m., and the cleaning notebook waits for both through a small check of the copy job's last run status.

One more setting deserves a mention because it surprises people. When a copy activity writes to a lakehouse table, the destination's table action and the activity's mapping decide what happens to columns the destination already has. Appending rows with a column the table lacks fails under Delta schema enforcement unless the table is set up to accept the new column, which is usually what you want: a new column in StockLine should be a deliberate change to the raw table, reviewed like any other, rather than something that slips in overnight.

Checking the result

A load is not finished when the activity turns green. Ines compared the row count the copy reported with a count on the source, 41,200,000 read against 41,199,788 written, a difference of exactly the 212 logged rows. Keiko's totals the next morning matched StockLine's own reports to the cent for every store but two, which led to the double-counting discovery described in MSAZ-310. Lesson 12 turns these checks into automatic tests.