Azure Data Factory Interview Questions and Answers Part 3 - Copy Activity and Performance

Azure Data Factory Interview Q&A · Part 3 of 10

In this post we are going to cover the copy activity and copy performance in Azure Data Factory: source, sink and mapping, DIUs and parallel copies, staged copy into Synapse, fault tolerance, sink options and source partitioning. These are 7 real Azure Data Factory interview questions, asked the way they come up on a real project, and the last one is a scenario where the nightly copy succeeded but the report still shows yesterday's numbers.

The copy activity is the workhorse of ADF. It reads data from one store and writes it to another, and most pipelines have at least one. Interviewers ask about it because the real problems show up here: a copy that takes three hours, a few bad rows failing the whole load, or a copy that succeeded and still moved nothing.

Part 3 of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Question 1: You need to load two hundred CSV files from ADLS into an Azure SQL table. How does the copy activity work, and what do source, sink and mapping do?
3. Question 2: A copy of fifty million rows takes three hours. What are data integration units and parallel copies, and how do you speed it up?
4. Question 3: Loading into Synapse dedicated SQL pool is slow row by row. What is staged copy, and when do you use the COPY statement or PolyBase?
5. Question 4: A few bad rows keep failing the whole copy. How does fault tolerance work, and what does the session log give you?
6. Question 5: The business wants new rows inserted and changed rows updated in the SQL table. What are your sink options: upsert, stored procedure, or a pre-copy script?
7. Question 6: The source table has a billion rows. How do you read it in parallel with source partitioning?
8. Scenario 7: The nightly copy succeeded, but the report shows yesterday's numbers. What do you check?

The short answer

Say this first

The copy activity moves data from a source to a sink on an integration runtime, with a column mapping in between. Speed comes from the right DIUs, parallel copies and source partitioning, and from COPY or PolyBase into Synapse. Use fault tolerance with a session log for bad rows, and upsert or staging plus MERGE for inserts and updates.

Keep that in mind. Every question below is one piece of it.

Question 1: You need to load two hundred CSV files from ADLS into an Azure SQL table. How does the copy activity work, and what do source, sink and mapping do?

The copy activity reads from a source and writes to a sink, on an integration runtime. Both stores here are in Azure, so the Azure integration runtime does the work.

Let's set up the three parts in order.

First, the source: a delimited text dataset on ADLS Gen2, with a wildcard, *.csv, so one copy picks up all 200 files. The wildcard goes in the activity source, not in the dataset.

copy-activity.json

"source": {
  "type": "DelimitedTextSource",
  "storeSettings": {
    "type": "AzureBlobFSReadSettings",
    "wildcardFolderPath": "sales/2026",
    "wildcardFileName": "*.csv"
  }
},
"sink": { "type": "AzureSqlSink" },

Then the sink: the Azure SQL table, dbo.Sales. By default it bulk inserts.

Then the mapping, which decides which source column lands in which sink column. The default maps by name, and it is case-sensitive. For control, use an explicit mapping. For CSV files without a header, map by ordinal, starting at 1.

mapping.json

"translator": {
  "type": "TabularTranslator",
  "mappings": [
    { "source": { "name": "cust_id" },
      "sink": { "name": "CustomerID" } }
  ]
}

I also add the file path as an additional column, with $$FILEPATH, so every row knows which file it came from.

The mistake I see

A ForEach running one copy per file. One copy with a wildcard is simpler, and it already copies files in parallel.

Question 2: A copy of fifty million rows takes three hours. What are data integration units and parallel copies, and how do you speed it up?

First, find where the time goes. Copy monitoring splits the duration into queue, pre-copy script and transfer, and transfer into listing source, reading from source and writing to sink. Fix the stage that dominates.

Then know the two settings. Data integration units are how much compute one copy gets on the Azure integration runtime: CPU, memory and network. Parallel copies is how many threads use that compute.

	Data integration units	Parallel copies
WHAT	how much compute: CPU, memory, network	how many threads use it
RANGE	up to 256 per copy run, or Auto	decided dynamically by default
WHERE	Azure IR only, not self-hosted	files: per file; databases: per partition

On a self-hosted runtime, DIUs don't apply. You scale the machine or add nodes. And for one big SQL table, parallel copies does little until the source is partitioned.

So here is my order:

• Partition the source, so parallel copies can help.
• Raise DIUs if reading or writing is slow.
• Same region: keep the runtime next to the data.
• Check the sink. An Azure SQL database at 100 percent DTU won't go faster with more threads.

copy-activity.json

"dataIntegrationUnits": 32,
"parallelCopies": 8,
"source": {
  "type": "AzureSqlSource",
  "partitionOption": "DynamicRange"
}

More is not always better

Too many parallel copies overload the source or the sink, and you pay for DIU hours. Start with Auto, then tune from the monitoring numbers.

Question 3: Loading into Synapse dedicated SQL pool is slow row by row. What is staged copy, and when do you use the COPY statement or PolyBase?

Bulk insert pushes batches of rows into the pool. That is the slow path. The fast path lets Synapse load the files itself, with the COPY statement or PolyBase. On the sink, you set allowCopyCommand or allowPolyBase, never both.

Direct COPY only works when the source is already in Blob or ADLS Gen2, in a compatible format. Otherwise, turn on staged copy. It works in three steps:

• ADF copies the source into staging storage that you provide.
• Synapse loads it from staging.
• ADF cleans up the staging files.

copy-activity.json

"enableStaging": true,
"stagingSettings": {
  "linkedServiceName": {
    "referenceName": "StagingADLS",
    "type": "LinkedServiceReference"
  },
  "path": "staging",
  "enableCompression": true
},
"sink": {
  "type": "SqlDWSink",
  "allowCopyCommand": true
}

Staging also helps when a firewall only allows port 443. The self-hosted runtime pushes to staging over HTTPS, so you don't open 1433. And on a slow hybrid network, turn on compression.

Now the requirements:

• Delete permission on the staging store, for the cleanup.
• Managed identity if staging sits behind a firewall or private endpoint, which rules out compression. Grant the Synapse identity access too.
• No data consistency verification with staged copy.

My default

Both are documented. In new work I default to the COPY statement, because it is simpler to set up.

Question 4: A few bad rows keep failing the whole copy. How does fault tolerance work, and what does the session log give you?

By default, one bad row fails the whole copy. Fault tolerance lets you skip incompatible rows instead, and the copy keeps going.

It covers three cases:

• A value that can't convert to the sink type, like text in an INT column.
• A column count mismatch.
• A primary key violation on a SQL Server, Azure SQL or Cosmos DB sink.

Then log what you skipped. The session log writes the skipped rows to Blob or ADLS Gen2, with the reason.

copy-activity.json

"enableSkipIncompatibleRow": true,
"logSettings": {
  "enableCopyActivityLog": true,
  "copyActivityLogSettings": {
    "logLevel": "Warning",
    "enableReliableLogging": false
  },
  "logLocationSettings": {
    "linkedServiceName": {
      "referenceName": "LogsADLS",
      "type": "LinkedServiceReference"
    },
    "path": "copyactivity-logs"
  }
}

The log level decides how much it writes:

logLevel	Logs
Warning (default)	skipped items only
Info	also every copied file

Reliable logging flushes after each file: safer, but slower.

The catch: fault tolerance doesn't work with the upsert or stored procedure sink options. And skipping is a decision, not a fix. Compare rows read with rows copied in the activity output, and alert when they differ.

On a real project

I send the log to the source team, they fix the data, and we reload. Otherwise you silently lose rows every night.

Question 5: The business wants new rows inserted and changed rows updated in the SQL table. What are your sink options: upsert, stored procedure, or a pre-copy script?

The simplest is the native upsert. Set the write behavior to upsert and give the key columns. ADF loads the data into an interim table, then updates the rows whose key exists and inserts the rest.

sink.json

"sink": {
  "type": "AzureSqlSink",
  "writeBehavior": "upsert",
  "upsertSettings": {
    "useTempDB": true,
    "keys": [ "OrderID" ]
  }
}

By default the interim table is in tempdb. Turn useTempDB off, and it uses a physical table in a schema you name, which needs create and drop table permission.

Let's compare the three options:

Option	How it runs	Use it for
Upsert	interim table, then update or insert	simple key-based merge
Stored procedure	once per batch, table type	custom row logic (slower)
Pre-copy script	once per copy, before the write	truncate or delete a slice

The stored procedure sink passes rows to your procedure as a table type. It is flexible, but it runs once per batch, not once per copy, and it is slower than bulk insert. Never put a truncate inside it.

The pre-copy script runs once, before the write. It is for cleanup, like truncating a staging table or deleting a slice you are reloading. It doesn't merge anything by itself.

My default for big tables

Bulk copy into a staging table with a truncate pre-copy script, then a Stored procedure activity runs one MERGE. It is set-based and easy to test.

Question 6: The source table has a billion rows. How do you read it in parallel with source partitioning?

Use the partition option on the SQL source. ADF splits the read into ranges, runs one query per partition, and parallel copies decides how many run at the same time.

There are two options:

• Physical partitions of table uses the table's own partitions.
• Dynamic range splits on a column you choose, an integer or date column like OrderID, between a lower and an upper bound.

copy-activity.json

"source": {
  "type": "AzureSqlSource",
  "partitionOption": "DynamicRange",
  "partitionSettings": {
    "partitionColumnName": "OrderID",
    "partitionLowerBound": "1",
    "partitionUpperBound": "1000000000"
  }
},
"parallelCopies": 8

The bounds trip people up. They only decide the partition stride. They don't filter rows: rows outside the bounds are still copied.

With a custom query, put the ?DfDynamicRangePartitionCondition hook in the WHERE clause, so ADF can inject each range.

source-query.sql

SELECT OrderID, CustomerID, Amount, OrderDate
FROM dbo.Orders
WHERE ?DfDynamicRangePartitionCondition
  AND OrderDate >= '2020-01-01'

Pick a column that is indexed and evenly spread, or one partition does all the work.

Mind the source

Eight parallel reads on a busy production database can hurt the application. Read a replica if you can, and run off-peak.

Scenario 7: The nightly copy succeeded, but the report shows yesterday's numbers. What do you check?

The run status says Succeeded, but users see yesterday's numbers. Let's go through it step by step.

• Step 1, what the copy actually moved. Open the activity output: rows read and rows copied, or files read. A copy that moved zero rows still shows Succeeded.
• Step 2, the filter. A watermark or last modified window in the wrong time zone, or a watermark that is already ahead, selects nothing new. utcNow and the last modified filter are both UTC.
• Step 3, the source. Did the wildcard match today's file? Case matters. And a file that lands after the pipeline starts is picked up tomorrow, so check when the upstream system really delivered it.
• Step 4, everything after the copy. Did the merge step run? Did an If Condition take the false branch, or a try-catch hide a failure? Was the report's semantic model refreshed?

Then add guardrails, so it can't pass silently again: a Validation activity that waits for the file, an If Condition on rows copied that runs a Fail activity when it is true, and an alert on zero-row loads.

if-condition.txt

@equals(activity('CopySales').output.rowsCopied, 0)

Key points to remember

• Copy = source + sink + mapping on an integration runtime
• DIUs = how much compute; parallel copies = how many threads
• Synapse: COPY statement or PolyBase, with staged copy when needed
• Skip incompatible rows + session log; not with upsert or procedure sinks
• Upsert, or staging + MERGE; partition big sources

Next in this series

Part 4: Incremental Loads and Change Data Capture

Part of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Microsoft Learn page for anything you quote in an interview.