Azure Data Factory Interview Questions: Scenarios, Answers & Prep

ADF is Azure's managed data integration and orchestration service — you build pipelines of activities that move and transform data without managing infrastructure. It covers ingestion (Copy activity across 100+ connectors), orchestration (scheduling, dependencies, retries), and transformation via Mapping Data Flows or by calling Databricks, Synapse, or stored procedures. In most Azure stacks it is the orchestration layer sitting over a lake or warehouse.

A linked service is the connection definition — endpoint plus authentication, such as a connection string, managed identity, or Key Vault reference. A dataset is a named reference to specific data inside that connection: a table, folder, or file path with its format settings. Activities consume datasets, and datasets bind to linked services.

Azure IR: fully managed compute for cloud-to-cloud copies and data flows. Self-hosted IR: an agent installed on a VM or on-prem machine to reach private networks, on-prem sources, or sources needing local drivers. Azure-SSIS IR: a managed cluster that lifts and shifts existing SSIS packages. The IR is the compute that actually executes data movement and dispatches activities.

Schedule triggers (cron-like wall-clock runs), tumbling window triggers (fixed-size, non-overlapping windows with state, per-window retry, dependencies, and backfill), storage event triggers (fire on blob created/deleted via Event Grid), and custom event triggers. Tumbling window is the only type that supports catch-up for past windows, which is why it anchors incremental-load designs.

Copy activity moves data from a source dataset to a sink dataset over an integration runtime, with optional column mapping, compression, and format conversion. It needs source and sink linked services/datasets and an IR that can reach both. It scales via Data Integration Units (DIUs) and parallel copies, and reports per-run throughput and duration in the monitoring blade.

Use self-hosted IR when the source is on-prem, inside a private VNet without private endpoints, behind a firewall, or needs a local driver (Oracle, SAP). For HA and scale, register up to four nodes against the same IR — they share credentials and load-balance jobs, with concurrency limits set per node. Keep it on a dedicated VM, never on the source database server itself.

Classic watermark pattern: a watermark table stores the last loaded value of a monotonically increasing column like ModifiedDate. The pipeline runs a Lookup for the old watermark and a Lookup for the current MAX from the source, Copy pulls WHERE ModifiedDate > old AND <= new, then a Stored Procedure activity updates the watermark — only after the copy succeeds, so failed runs rerun safely. For deletes, use SQL Server CDC or a soft-delete flag, since watermarks cannot see them.

Tumbling window is one-to-one with a pipeline, keeps per-window state, supports retry per window, backfill of past windows, self-dependency (window N waits for N-1), and passes trigger().outputs.windowStartTime/EndTime for slice-based loads. Schedule triggers are many-to-many, fire-and-forget, with no retry or backfill. For hourly incremental slices where a missed window must be reprocessed, tumbling window is the correct answer.

Use pipeline parameters plus parameterized datasets (schema, table, path as dataset parameters) and, where needed, parameterized linked services, referenced with dynamic content like @pipeline().parameters.tableName and @dataset().schemaName. A Lookup reads a control table, ForEach iterates it, and one generic Copy handles every table. Global parameters hold environment values that CI/CD overrides per environment.

Copy activity for pure movement — cheapest and fastest, no transformation beyond mapping and format. Mapping Data Flows for visual transforms (joins, aggregates, dedupe, SCD) on ADF-managed Spark — good for low-code teams, but budget the 4-5 minute cluster spin-up unless you set a TTL on the Azure IR. Databricks or Synapse notebooks when you need custom code, Delta Lake features, or the transform logic already lives there.

Read the copy monitoring breakdown first — it splits time across source, sink, and queue, telling you where the bottleneck is. Then tune: raise DIUs (up to 256) and parallel copies, enable source partitioning (physical partitions or dynamic range on a numeric/date column), use staged copy with PolyBase or the COPY statement into Synapse, prefer binary copy when no format conversion is needed, and check CPU/memory/bandwidth on any self-hosted IR node involved.

Set retry count and interval on activities for transient failures, and wire the red failure path to a notification step — a Logic App or Web activity posting to Teams or email. Remember that if the failure path succeeds, the pipeline can report success, so design status handling deliberately. Add Azure Monitor alerts on pipeline-run metrics for platform-level coverage, and use rerun-from-failed-activity in monitoring instead of rerunning whole pipelines.

A control table in Azure SQL holds per-table metadata: source and sink names, load type, watermark column, last watermark, active flag, and batch group. An orchestrator pipeline Looks up the control rows and a ForEach (with batch count for parallelism) invokes a parameterized child pipeline that branches full vs incremental and writes audit rows — row counts, duration, status — per run. The closing line interviewers want: onboarding a new table is an INSERT into the control table, not a new pipeline.

Attach the dev factory to Git, work in feature branches, and PR to main; deploy via ARM using either the classic adf_publish branch or, better, the @microsoft/azure-data-factory-utilities npm package to validate and export ARM in a build pipeline. Deploy to test/prod with ARM parameters overriding linked service URLs, Key Vault references, and global parameters. The classic breakage is skipping the pre/post-deployment PowerShell script that stops triggers before deployment and restarts them after — you also need its cleanup flag, or deleted resources persist in higher environments.

In Mapping Data Flows, enable Allow schema drift on source and sink so unmapped columns flow through, use rule-based mapping or byName()/byPosition() expressions instead of fixed mappings, and enable Infer drifted column types with care. In Copy activity, avoid hard-coded mappings or regenerate them from Get Metadata. For strict contracts do the opposite: validate the actual schema against the expected structure with Get Metadata and fail fast into the alert path.

Decouple arrival from processing: let the event trigger only log the file or drop a queue message, then process on a tumbling-window pipeline that batches everything landed in the window — list the folder, one Copy with a wildcard, one MERGE per window. Setting pipeline concurrency to 1 also serializes runs, but batching is the scalable answer. Name the real problem out loud: per-file triggering combined with a non-serialized sink.