Azure Data Factory Interview Questions: Scenarios, Answers & Prep

ADF is Azure's managed, serverless data integration and orchestration service: you compose pipelines of activities that move and transform data without provisioning infrastructure, paying per activity run. It covers ingestion (Copy activity with 100+ connectors), orchestration (triggers, dependencies, retries, alerting), and transformation — visually with Mapping Data Flows, which compile to Spark, or by delegating to Databricks, Synapse, or stored procedures. In a typical Azure stack it is the orchestration layer over a lake and warehouse, and the framing interviewers look for is ELT coordinator rather than compute engine: ADF only executes transforms itself when Data Flows run; everything else it dispatches. The same engine also surfaces as Synapse pipelines and Fabric Data Factory.

A linked service is the connection definition: endpoint plus authentication — connection string, service principal, managed identity, or a Key Vault secret reference. A dataset is a named pointer to specific data inside that connection: a table, folder, or file path together with format settings such as Parquet, CSV, or JSON. Activities consume datasets, and every dataset binds to exactly one linked service. The follow-up interviewers probe: both are parameterizable, so one parameterized linked service and one generic dataset can serve hundreds of tables — the foundation of metadata-driven frameworks. Keep the boundary straight: auth and integration-runtime binding live on the linked service, format and schema live on the dataset — mixing those up is the classic slip.

Azure IR is fully managed compute for cloud-to-cloud copies and Mapping Data Flows; it can auto-resolve its region or be pinned, and the managed VNet flavour adds private endpoints. Self-hosted IR is an agent you install on a VM or on-prem machine to reach private networks, firewalled sources, or systems needing local drivers such as Oracle or SAP — it makes outbound-only 443 calls, so no inbound firewall holes. Azure-SSIS IR is a managed cluster that lifts and shifts existing SSIS packages with SSISDB. The IR is where data movement actually executes, so name the nuances: Data Flows run only on Azure IR, never self-hosted, and for external compute like Databricks the IR merely dispatches the call — the heavy lifting happens on the other side.

Four types. Schedule triggers fire on wall-clock cadence, are many-to-many with pipelines, and keep no state — an occurrence missed while the trigger is stopped is simply skipped. Tumbling window triggers run fixed-size, non-overlapping windows with state: per-window retry, backfill of past windows, self-dependency (window N waits for N-1), dependencies on other tumbling triggers, and they pass trigger().outputs.windowStartTime/EndTime into the pipeline. Storage event triggers fire on blob created/deleted via Event Grid — the trap being that the Event Grid resource provider must be registered on the subscription. Custom event triggers listen to your own Event Grid topics. Tumbling window is the only type with catch-up, which is why it anchors incremental slice loads.

Copy activity moves data from a source dataset to a sink dataset over an integration runtime that must have network reach to both ends, with optional column mapping, format conversion, and compression. On Azure IR it scales via Data Integration Units (up to 256) and parallelCopies; on self-hosted IR, DIUs don't apply — node CPU, memory, and the concurrent-jobs limit govern throughput, a distinction interviewers probe. It also offers fault tolerance (skip and log incompatible rows instead of failing) and staged copy through Blob storage. Binary copy is the fast path when no parsing is needed, but it forbids mapping and format change. The monitoring blade breaks each run into queue, read, and write time — always your starting point for tuning.

Use self-hosted IR when the source is on-prem, inside a private VNet you cannot reach with managed private endpoints, behind a firewall, or needing a local driver such as Oracle or SAP. For HA, register up to four nodes against the same IR key: they share encrypted credentials, load-balance jobs, and you cap concurrent jobs per node. One node is a single point of failure — jobs queue until it returns — so two nodes is the practical minimum for production. The agent makes outbound-only 443 connections, so no inbound firewall rules are needed. Two traps worth naming: never install it on the source database server, because copies will steal CPU from the workload you are reading, and it cannot execute Mapping Data Flows — those only run on Azure IR.

Classic watermark pattern. A watermark table stores the last loaded value of a monotonically increasing column such as ModifiedDate. The pipeline: Lookup the stored watermark, Lookup the current MAX(ModifiedDate) from the source, Copy with WHERE ModifiedDate > old AND <= new, then a Stored Procedure activity updates the watermark only after the copy succeeds, so failed runs rerun safely. Capture MAX up front rather than filtering on GETDATE() — otherwise rows written mid-run fall into a gap. Make the load idempotent (MERGE or delete-then-insert on the key range) so a retry cannot duplicate. Pre-empt two probes: watermarks never see deletes — you need CDC, Change Tracking, or soft-delete flags for those — and the watermark column must be indexed and trustworthy, or the pattern falls over.

Tumbling window is one-to-one with a pipeline and stateful: per-window retry, backfill of historical windows, self-dependency so window N waits for N-1, dependencies on other tumbling triggers, a concurrency cap, and trigger().outputs.windowStartTime/EndTime handed to the pipeline for slice boundaries. Schedule triggers are many-to-many, stateless, fire-and-forget — no retry, no backfill, and occurrences missed while the trigger is stopped are skipped, not queued; tumbling windows are queued and executed when possible. So the choice matters exactly when a missed interval must be reprocessed: hourly incremental slices belong on tumbling window, while a simple kick-off-the-nightly-batch-at-02:00 is fine on schedule. Treating them as interchangeable clocks gets marked down.

Push everything through parameters: pipeline parameters, dataset parameters (schema, table, path), and parameterized linked services where the server itself varies, referenced with dynamic content such as @pipeline().parameters.tableName and @dataset().schemaName. A Lookup reads a control table, ForEach iterates it, and one generic Copy handles every table; global parameters carry environment values that CI/CD overrides per stage. Know the limits interviewers probe: Lookup returns at most 5,000 rows / 4 MB, so paginate a bigger control table; ForEach parallelism caps at 50 via batchCount; and ForEach cannot nest — the workaround is an Execute Pipeline child holding the inner loop. Parameters are immutable at runtime; use variables with Set Variable when a value must change mid-run.

Copy activity for pure movement — cheapest and fastest, nothing beyond mapping, format conversion, and compression. Mapping Data Flows for visual transforms (joins, aggregates, dedupe, SCD) on ADF-managed Spark: good for low-code teams, but budget the several-minute cluster spin-up per run unless you set a TTL on the integration runtime, and an idle debug session keeps billing until it expires. Databricks or Synapse notebooks when you need custom code, unit tests, Delta Lake features, streaming, or the logic already lives in a Spark estate — ADF then just orchestrates. The trade-off to state plainly: Data Flows compile to Spark anyway, so the real choice is who owns the logic — a drag-and-drop surface locked inside ADF, or versioned code your engineers can test and reuse outside it.

Start with the copy monitoring breakdown — it splits the run into queue, source read, and sink write time, so you tune the actual bottleneck instead of guessing. Then, in order: raise DIUs (up to 256, Azure IR only) and parallelCopies; enable source partitioning — physical partitions or a dynamic range over a numeric/date column; use staged copy with PolyBase or the COPY statement when loading Synapse; prefer binary passthrough when no format conversion is needed. File shape matters: thousands of tiny files destroy throughput, so compact or batch them. If a self-hosted IR is involved, check node CPU, memory, bandwidth, and the concurrent-jobs setting — DIUs do not apply there. The trap: paying for more DIUs when the breakdown shows the sink, say a single Azure SQL DB, is the ceiling.

Layer it. Activity-level retry with an interval absorbs transient failures. Dependency conditions (success, failure, completion, skipped) give you try-catch-finally shapes: wire the failure path to a Web or Logic App activity that posts to Teams or email with @activity('X').error.message. Now the classic trap: a pipeline's status comes from its leaf activities, so if the failure-handler itself succeeds, the run can report Succeeded — end the error branch with a Fail activity to re-raise with your own message and error code. Add Azure Monitor alerts on pipeline-run metrics and ship diagnostics to Log Analytics for coverage and history beyond ADF's 45-day retention. For recovery, rerun from the failed activity in the monitoring blade rather than replaying the whole pipeline.

A control table in Azure SQL holds per-table metadata: source and sink identifiers, load type, watermark column and last value, active flag, and a batch group for ordering. An orchestrator Looks up active rows and a ForEach (batchCount for parallelism, capped at 50) invokes a parameterized child pipeline per row; the child branches full vs incremental (Switch or If), runs a generic Copy against parameterized datasets, updates the watermark only on success, and writes an audit row with counts, duration, and status. Respect the Lookup 5,000-row cap by paginating, and watch sink contention when many tables land in one database at once. The line interviewers want: onboarding table 201 is an INSERT, not a new pipeline — ADF's built-in metadata-driven copy task can scaffold this.

In Mapping Data Flows, enable Allow schema drift on source and sink so unmapped columns flow through, replace fixed mappings with rule-based mapping or byName()/byPosition() expressions, and enable Infer drifted column types cautiously — it can guess wrong on sparse data. In Copy activity, avoid hard-coded mappings, or rebuild them dynamically from Get Metadata output. For strict contracts, do the opposite: validate the actual structure from Get Metadata against the expected schema and fail fast into the alert path with a Fail activity. Pre-empt two probes: a rename looks identical to a drop-plus-add, so no engine can infer intent — that needs a mapping layer or a source-team contract — and drifted columns are invisible at design time, so fixed downstream column references silently break.

Decouple arrival from processing. Let the event trigger do the minimum — log the file into a control table or drop a queue message — then process on a tumbling-window pipeline that batches everything landed in the window: list the folder, one wildcard Copy, one MERGE per window instead of thousands. Setting pipeline concurrency to 1 also serializes runs, but it just queues the burst and latency balloons — batching is the scalable fix. Two mark-earning nuances: Event Grid delivery is at-least-once, so the same blob can fire twice — dedupe on filename in the log table for idempotency — and thousands of per-file runs also burn activity-run cost and factory limits, not just the sink. Name the root cause: per-file triggering plus a non-serialized sink was the flaw, not the MERGE itself.