Azure Data Factory Interview Questions and Answers

Interviewers rarely want a definition of Azure Data Factory. They want to know whether you have run it: whether you know what breaks, what is limited, and why you would choose one option over another. The Azure Data Factory interview questions below are grouped by topic, and each answer aims at that second kind of knowledge. Where an answer states a limit or a default, it comes from Microsoft's documentation and is linked.

Learn the reasoning, not the wording. A memorised answer falls apart at the first follow-up.

Fundamentals

1. What are the main components of a data factory?

A pipeline is a logical grouping of activities that together perform a task (Microsoft Learn: pipelines and activities). Activities are the steps. A linked service holds how to connect to a system; a dataset describes a particular piece of data in it, a table or a folder. An integration runtime is the compute that does the work. A trigger decides when a pipeline runs.

Follow-up to expect: "What is the difference between a linked service and a dataset?" The linked service is the connection; the dataset is what you point at through it. Many datasets share one linked service.

2. What are the types of integration runtime, and when do you use each?

Three (Microsoft Learn: integration runtime). The Azure runtime is managed compute for cloud data stores and for data flows. The self-hosted runtime is software you install inside a private network, to reach on-premises or otherwise unreachable sources. The Azure-SSIS runtime runs existing SSIS packages. Integration runtimes in ADF covers each.

Follow-up to expect: "Does the self-hosted runtime need inbound ports?" No. It makes outbound connections and polls for work.

3. What is the difference between a pipeline and a data flow?

A pipeline orchestrates: it sequences activities and moves data. A data flow transforms: it is a visually designed transformation that runs on a Spark cluster, started by a pipeline through the Data Flow activity. A Copy activity moves data without reshaping it; a data flow joins, aggregates and derives.

The Copy activity

4. How do you make a slow Copy activity faster?

Start by finding the bottleneck, in the copy's monitoring view, which breaks the run into queue, listing, reading and writing. Then act on that stage. The dials are Data Integration Units, between 4 and 256 on the Azure runtime, parallel copies, a partition option on database sources, and staged copy (Microsoft Learn: copy performance features).

The strong answer includes the caveat: raising DIUs does nothing when the source, the sink or the network is the limit, and it raises the bill, since the charge is DIUs used multiplied by duration. ADF performance tuning works through this.

5. What is staged copy, and when would you use it?

The copy lands data in interim Blob or Data Lake storage, then loads the sink from there. Use it to bulk load a warehouse from a source the bulk loader cannot read, to avoid opening a database port when a self-hosted runtime can reach storage over HTTPS, or to compress data before it crosses a slow link.

6. How do you copy a very large table?

Partition the read. With a partition option on the source, the Copy activity runs several range queries in parallel, as many as the parallel copies setting. Choose physical partitions if the table has them, or a dynamic range on a well-distributed column such as the primary key. Write to a folder of files, not one file.

Control flow

7. What are the limits of the Lookup activity?

It returns at most 5,000 rows and 4 MB, and by default returns only the first row (Microsoft Learn: Lookup activity). To feed a ForEach you set first row only to false. For more than 5,000 items, page through the list with an outer loop.

8. How does ForEach run, and what can go wrong?

Sequentially, or in parallel up to a batch count of 50 (Microsoft Learn: ForEach activity). Two things catch people. A ForEach cannot contain another ForEach; nested loops need a child pipeline called with Execute Pipeline. And variables are scoped to the pipeline, not to an iteration, so Set Variable inside a parallel ForEach gives unpredictable values.

9. How many activities can a pipeline have?

120, including activities inside containers, and 50 parameters (Microsoft Learn: Azure service limits). The better answer adds that a pipeline near that size should have been split into child pipelines long before.

Triggers

10. Compare the trigger types.

A schedule trigger runs at clock times and can start many pipelines. A tumbling window trigger fires once per fixed, non-overlapping window, passes the window's start and end to the pipeline, and supports backfill, retries, concurrency limits and dependencies between triggers; it is tied to one pipeline. An event trigger starts a pipeline when a file arrives or a custom event is published (Microsoft Learn: pipeline execution and triggers).

Follow-up to expect: "My schedule trigger run shows succeeded but the data is missing. Why?" Because a schedule trigger run is marked successful as soon as the pipeline run starts. It says nothing about how the pipeline ended. Monitor pipeline runs.

11. How would you reload the last thirty days?

With a tumbling window trigger whose start date is thirty days back: it generates a run for each past window. With a schedule trigger there is no backfill, so the alternative is a parameterized pipeline run in a loop over the dates. Either way the load must be safe to rerun.

Incremental loading

12. How do you load only new and changed rows?

The watermark pattern (Microsoft Learn: incremental copy tutorial). One Lookup reads the stored watermark. A second reads the current maximum of the watermark column. A Copy activity takes the rows between the two. A final step saves the new watermark, only after the copy succeeds.

SELECT * FROM dbo.Orders
WHERE LastModified >  '@{activity('LookupOldWatermark').output.firstRow.WatermarkValue}'
  AND LastModified <= '@{activity('LookupNewWatermark').output.firstRow.NewWatermarkValue}'

Follow-up to expect: "What does that miss?" Deleted rows, which have nothing to compare; rows inserted with an old timestamp; and updates that did not touch the watermark column. Mentioning these unprompted is what separates candidates. Incremental data loading covers the mitigations.

13. Why read the new watermark before copying, instead of after?

To fix the upper bound of the window. Rows that arrive during the copy fall above it and are picked up next time. Reading the maximum afterwards could skip rows that arrived mid-copy.

Errors and reliability

14. How does Data Factory decide whether a pipeline failed?

By the outcome of the last activities on each path, not by whether any activity failed. An activity can depend on another with Succeeded, Failed, Skipped or Completed. If a failure is handled by an activity on the Failed path and that activity succeeds, the pipeline is reported as succeeded. To log an error and still fail the run, end the failure path with a Fail activity. Handling errors and retries shows the patterns.

15. What are the default retry and time-out settings?

No retries, a 30-second retry interval, and a time-out of 12 hours. In production you set both deliberately: a short time-out so a hung activity is noticed, and retries only for transient errors on loads that are safe to repeat.

16. How do you make a pipeline safe to rerun?

Make each load replace or merge. Clear the target slice with a pre-copy script, upsert on a key, or overwrite a dated folder. Pass the period as a parameter from the trigger, so a rerun processes the same period. Move watermarks last.

Security and deployment

17. How should a pipeline authenticate to data stores?

With the factory's managed identity wherever the store supports it, granted the narrowest role that works: read roles on sources, write roles on sinks. For systems that need a secret, keep it in Azure Key Vault and reference it from the linked service. Never in a parameter, which is logged with the run. See managed identities in Azure Data Factory.

18. How do you move a factory from development to production?

Git is connected to the development factory only. Changes go through a pull request into the collaboration branch, which produces a Resource Manager template; a release pipeline deploys that template to test and production with environment-specific parameters, stopping triggers before and restarting them after (Microsoft Learn: continuous integration and delivery). Nobody edits production by hand. CI/CD for Azure Data Factory has the detail.

19. How long is run history kept, and what do you do about it?

45 days (Microsoft Learn: monitor Azure Data Factory). A diagnostic setting sends run logs to Log Analytics for querying and alerting, or to storage for long retention. It has to be set up in advance; logs are not collected retroactively.

Scenario questions

These have no single right answer. Interviewers listen for how you narrow the problem.

20. A pipeline that took ten minutes now takes two hours. What do you do?

Compare a fast run and a slow run in monitoring, activity by activity. Find which activity grew. If it is a Copy, look at its stages: a longer queue points to the runtime, a longer read to the source, a longer write to the sink. Ask what changed: data volume, a schema or index change at the source, a new process competing for the database. Only then change a setting.

21. After a rerun, the target table has duplicates. Why, and how do you fix it?

The load appends, and the rerun loaded the same rows again. The repair is to deduplicate on the business key. The fix is to change the load to an upsert or to delete-then-insert for the period, so the next rerun is harmless.

22. You need to ingest two hundred tables from an on-premises SQL Server. How would you design it?

A self-hosted integration runtime, with a second node for availability. One parameterized pipeline driven by a control table, not two hundred pipelines. Full load for small tables, watermark-based incremental for large ones, chosen per row of the control table. Parallelism limited so the source is not overwhelmed. A log table with rows read and written per table, and an alert on failure.

23. What do you know about Data Factory in Microsoft Fabric?

An honest, current answer is valued here. Microsoft describes Fabric Data Factory as the next generation of Azure Data Factory. Pipelines, activities and expressions carry over; datasets and integration runtimes do not exist there in the same form. If you have used only Azure Data Factory, say so, and say what you expect to transfer.

How to prepare

• Build one pipeline of each kind: a parameterized copy, an incremental load, an event-triggered load, a failure path. Questions become memories.
• Break things on purpose. Stop the self-hosted runtime, send a malformed file, rerun a load. The best interview answers begin "when that happened to me".
• Know five limits by heart: 120 activities, 5,000 Lookup rows, 50 parallel iterations, 12-hour default time-out, 45 days of history.
• Practise saying what you do not know. "I have not used that; here is how I would find out" is a good answer.

For the order in which to learn the topics, see the Azure Data Factory roadmap.

Azure Data Factory and data pipeline design are covered in the Data Engineering course.

Sources

• Pipelines and activities in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Integration runtime in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Copy activity performance optimization features (Microsoft Learn) — Microsoft Learn
• ForEach activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Lookup activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Pipeline execution and triggers in Azure Data Factory or Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Incrementally load data from Azure SQL Database to Azure Blob storage using the Azure portal (Microsoft Learn) — Microsoft Learn
• Monitor Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Continuous integration and delivery in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Azure subscription and service limits, quotas, and constraints (Microsoft Learn) — Microsoft Learn