Microsoft Fabric Copy Job: Failure Modes Beginners Hit in Production

The 28,700-view Fabric Community tutorial frames Copy Job as 'Pipeline minus the complexity.' That framing is misleading and explains most of the support threads in the Copy job board. A Pipeline Copy activity is a single stateless run: you pass it a query or a folder path, it moves bytes, it exits. A Copy Job is a stateful item with its own metadata store. It tracks the last watermark value, the last change tracking version, and the last successful sync timestamp per source object. That state lives in the workspace, not in your control table.

This matters the moment you need to reprocess. With a Pipeline you change the parameter and rerun. With a Copy Job the wizard offers 'Reset' but does not document that resetting an Incremental copy job in CDC mode requires the source SQL Server to still have the original change_tracking_min_valid_version available. If retention has passed, reset does not raise an error — it silently switches to a full snapshot on next run, which can mean a 4 TB re-pull at 2am. The job status reads Succeeded.

The second behavioral difference: Copy Job runs are not Pipeline activities. They do not show up in the same monitoring hub view by default, they emit different telemetry to the Fabric Capacity Metrics app, and onelake_billable_storage costs are attributed to the Copy Job item rather than the Pipeline that triggered it. Teams running chargeback against pipelines find Copy Job consumption invisible until they filter by item type.

The wizard asks you to pick an incremental column. It accepts any column with an ordered type. It does not validate that the column is monotonic, indexed, or updated on row changes.

Three common mistakes:

1. Picking ModifiedDate on a SQL Server table where the application updates the row but a trigger or ORM does not touch ModifiedDate. The Copy Job filters WHERE ModifiedDate > @lastWatermark and misses every UPDATE. INSERTs land correctly, so the job looks healthy until reconciliation a week later.

1. Picking an IDENTITY column on a table with READ COMMITTED SNAPSHOT isolation. Long-running transactions can commit IDs out of order. The Copy Job advances the watermark to MAX(id) at run time, then on the next run filters WHERE id > @watermark and skips rows that committed late.

1. Picking a DATETIME (not DATETIME2) column. The 3.33ms rounding means rows inserted within the same millisecond bucket as the watermark are either duplicated or dropped depending on whether the comparison uses > or >=. Copy Job uses >, so they are dropped.

The fix is not in the Copy Job UI. You need a rowversion column, a CDC-enabled source, or you need to use Change tracking mode (which requires sysadmin or db_owner on SQL Server to enable, plus a CHANGE_TRACKING_MIN_VALID_VERSION large enough to cover your worst-case outage window). For Fabric Warehouse and Lakehouse sources, neither CDC nor CT exists yet — you are stuck with watermark mode and need a column you control.

For Lakehouse Delta sinks, Copy Job offers Append, Overwrite, and Merge. Merge is the default for incremental jobs and does what you expect logically: upsert by key. What the documentation omits is the partition rewrite cost.

Delta MERGE on a partitioned table rewrites every partition that contains a matched row. If your incremental batch contains 1,000 updated rows spread across 400 daily partitions, the job rewrites 400 partition files. On a table with 200 GB per partition, a 1,000-row update pulls 80 TB of compute through your F64 capacity. The Copy Job run duration goes from 'expected 90 seconds' to 'consumed 6 CU-hours' and triggers throttling on the next Power BI refresh that shares the capacity.

Three mitigations the wizard does not surface:

• Set the partition column on the sink to match the natural arrival pattern of your watermark. If the watermark is ModifiedDate, partition by ModifiedDate truncated to month, not by a business dimension like Region.
• Enable Deletion Vectors on the Lakehouse table (ALTER TABLE name SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true')). This converts MERGE updates from full rewrite to soft-delete-plus-append until the next OPTIMIZE.
• For append-only sources, change the Copy Job sink behavior from Merge to Append and handle deduplication downstream in a notebook. Merge is rarely the right choice when the source guarantees no updates.

Copy Job runs against Fabric Capacity. When the capacity is overloaded, the underlying Data Movement service returns HTTP 429 with a Retry-After header. Copy Job's default retry policy is 3 attempts with exponential backoff starting at 30 seconds.

The failure mode: a 4-hour SQL Server source extraction starts at 02:00. At 03:30, the F64 capacity hits 110% utilization because a Power BI refresh kicked off. The Copy Job receives 429 on its next API call. It retries at 03:30:30, 03:31:30, 03:33:30. All three retries hit 429 because the Power BI refresh is still running. The Copy Job marks the run Failed with error code DataMovementOperationFailed and a generic 'request was throttled' message. There is no UserErrorRetryableThrottlingError code surfaced to the run output — you have to query the activity log via the Fabric REST API to see the throttle attribution.

What makes this worse: in incremental mode, a failed run does not advance the watermark. Good. But it also does not roll back the rows already written to the Delta sink. So the next successful run starts from the original watermark and re-merges the rows already present. With idempotent merge keys this is fine. With append behavior or with non-unique merge keys (the wizard does not enforce uniqueness), you get duplicates.

Monitor the FabricCapacityMetrics InteractiveRequest_Throttled and BackgroundRequest_Throttled metrics during your Copy Job windows. If either is non-zero, your Copy Job is at risk regardless of what its run history shows.

This is the gap MetricSign closes for Fabric Pipelines and Copy Jobs: the platform groups throttle-related failures across capacity-sharing items and surfaces the root cause as 'capacity contention with [specific Power BI dataset]' rather than three separate generic 429 alerts. When a Copy Job's watermark fails to advance, MetricSign emits a refresh_delayed signal against downstream Lakehouse tables before the BI team notices stale data.

Copy Job has a 'Schema mapping' step in the wizard. Beginners click Auto-map and move on. Auto-map captures the source schema at job creation time and pins it. When the source DBA adds a column three months later, Copy Job does one of two things depending on the source connector:

• For SQL Server, Snowflake, and Postgres connectors: the new column is silently ignored. The job succeeds. The Lakehouse table never gets the column.
• For Parquet, CSV, and JSON file connectors: the job fails with ErrorCode=DelimitedTextColumnNameNotAllowNull or ErrorCode=ParquetSchemaMismatch depending on format. The error message points at the file, not at the schema mapping configuration.

The fix in both cases is to enable 'Allow schema drift' in the advanced settings of the sink. This is off by default. Once enabled, new source columns are added to the Delta sink as nullable. Removed source columns are kept in the sink (Copy Job does not drop columns). Type changes on existing columns still fail — there is no automatic widening from INT to BIGINT.

For production reliability, pair schema drift with a column-level contract check before the Copy Job runs. A Notebook activity that queries INFORMATION_SCHEMA.COLUMNS on the source and compares to the expected manifest takes 4 seconds and prevents the silent column-drop scenario.