Azure Data Factory Interview Questions & Answers

Azure Data Factory is a fully managed, serverless data integration service used to build ETL and ELT pipelines. You use it to move data from on-premises and cloud sources into a lake or warehouse, orchestrate transformations in Databricks, Synapse or SQL, and schedule and monitor those workflows without managing servers.

Trainer tip: Say 'orchestration first, transformation second' — ADF's core strength is orchestration; heavy transformation usually happens in Databricks or SQL.

A linked service is the connection string to a source or sink. A dataset is a named view of the data inside that connection (a table, folder or file pattern). An activity is a single unit of work such as Copy, Lookup or Notebook. A pipeline is a logical grouping of activities with dependencies and control flow.

Azure IR for cloud-to-cloud movement and data flow execution; Self-hosted IR for on-premises or private-network sources, installed on a VM inside your network; and Azure-SSIS IR for lifting and shifting existing SSIS packages into Azure.

Trainer tip: Interviewers often follow up with 'how do you make self-hosted IR highly available?' — answer: register up to four nodes on the same self-hosted IR.

Data movement (Copy activity), data transformation (Mapping Data Flow, Databricks Notebook, Stored Procedure, HDInsight, Synapse) and control flow (ForEach, If Condition, Switch, Until, Wait, Lookup, Get Metadata, Execute Pipeline, Web, Set/Append Variable, Filter, Validation).

A linked service defines where and how to connect (server, auth, credentials). A dataset defines what to read or write within that connection. One linked service is typically reused by many datasets.

V2 added control flow (loops, branching, conditions), parameterization, triggers beyond simple scheduling, Mapping Data Flows for code-free transformation, SSIS package execution and integrated CI/CD with Git. All new work uses v2.

Yes. ADF integrates with Azure DevOps Git or GitHub. You develop in a feature branch, publish from the collaboration branch which generates ARM templates in the adf_publish branch, and deploy those templates to higher environments with parameter overrides.

By pipeline activity runs, data movement in Data Integration Units per hour, data flow cluster execution time by core-hours, plus operations such as monitoring and debugging. Idle pipelines cost almost nothing because the service is serverless.

The Copy activity reads from the source through the integration runtime, optionally serialises or deserialises formats, applies column mapping and type conversion, and writes to the sink. Throughput scales with Data Integration Units and the degree of copy parallelism you configure.

Increase DIUs, raise parallel copies, partition the source (by date, key range or physical partition), enable staged copy through Blob or ADLS when the sink needs it (for example PolyBase or COPY INTO for Synapse), choose a columnar format like Parquet, and place the integration runtime in the same region as the data.

Trainer tip: Always mention region proximity — many candidates forget it and it is often the biggest single win.

Staged copy writes data to an interim Blob or ADLS location before loading the sink. It is needed when the sink loads fastest from storage (Synapse PolyBase/COPY INTO), when the source cannot connect directly to the sink, or when data must cross a firewall boundary.

Use a watermark pattern: store the last loaded value (a timestamp or an incrementing key) in a control table, use a Lookup activity to read it, filter the source query with it, copy the delta, then update the watermark with a Stored Procedure activity. For SQL sources you can also use Change Data Capture or change tracking.

Trainer tip: Draw the Lookup → Copy → Stored Procedure sequence on the whiteboard; interviewers score this question on structure, not wording.

Keep a metadata table listing source and target tables. Use a Lookup activity to read that list, pass it to a ForEach activity, and inside the loop run a parameterized Copy activity where the dataset's table name comes from the loop item. This is the standard metadata-driven framework.

Get Metadata returns properties about a file or folder such as existence, item name, size, last modified and child items. Lookup returns actual rows from a dataset or a query, either the first row or the full result set, and is commonly used to read config or watermark values.

Enable fault tolerance on the Copy activity to skip incompatible rows and log them to storage, use auto-mapping instead of explicit mapping where columns vary, and use Mapping Data Flows with schema drift enabled and byName expressions when structure genuinely changes over time.

Delimited text, JSON, Avro, ORC, Parquet, XML and binary. Parquet is the default choice for analytics because it is columnar and compressed; Avro suits row-based streaming ingestion; delimited text is used mainly for interop with legacy systems.

A visually designed, code-free transformation that ADF compiles into Spark and executes on a managed Databricks-backed cluster. You build sources, transformations and sinks on a canvas and ADF handles cluster provisioning.

Mapping Data Flow is for production-scale transformation logic executed on Spark. Wrangling Data Flow uses Power Query for exploratory, spreadsheet-style data preparation and is aimed at analysts rather than engineers.

Source, Select, Derived Column, Filter, Aggregate, Join, Lookup, Exists, Union, Conditional Split, Pivot/Unpivot, Surrogate Key, Window, Rank, Alter Row and Sink. Alter Row plus an upsert-enabled sink is how you implement inserts, updates and deletes.

Read the source and the existing dimension, join on the business key, use a Derived Column to compute a hash of tracked attributes, use Conditional Split to route new versus changed rows, use Alter Row to expire the current record (set end date and active flag) and insert the new version with a Surrogate Key transformation.

Trainer tip: SCD2 is the single most asked data-flow scenario — rehearse it until you can explain it in ninety seconds.

Debug mode spins up a live Spark cluster so you can preview data at each transformation. It bills for cluster uptime while enabled, so you set a short time-to-live and turn it off when you finish developing.

Right-size the compute type and core count, set sensible partitioning (hash or round robin) instead of leaving defaults, push filters and projections as early as possible, avoid unnecessary sorts and broadcast only genuinely small sides of a join, and reuse a cluster across activities with a time-to-live setting.

Schedule triggers (wall-clock recurrence), tumbling window triggers (fixed, non-overlapping, stateful windows that support backfill and dependency), event-based triggers (blob created or deleted, and custom events via Event Grid) and manual or REST-invoked runs.

A schedule trigger simply fires at set times and has no memory of past runs. A tumbling window trigger maintains state, supports retries, backfill of historical windows, concurrency limits and dependencies between windows — which makes it the right choice for time-sliced incremental loads.

Parameters are set at the start of a pipeline run and are read-only during execution. Variables can be created and changed inside the run using Set Variable and Append Variable, typically to accumulate values inside a loop.

Define parameters on the linked service (for example server name or database) and supply values from the dataset or pipeline at runtime. Combined with a metadata table this lets one pipeline serve many source systems and environments.

Reference the previous activity's output with an expression such as @activity('LookupConfig').output.firstRow.WatermarkValue, or store the value in a variable with Set Variable and use it downstream.

ForEach runs in parallel by default up to its batch count; tick Sequential to force ordering. At pipeline level you control overlap with the concurrency setting, and at trigger level with the tumbling window concurrency and dependency options.

Through the Monitor tab for pipeline, activity and trigger runs, Azure Monitor and Log Analytics for diagnostic logs and KQL queries, metric alerts on failed runs, and Azure Alerts wired to email, Teams or a webhook for on-call notification.

Use the failure, success, completion and skipped dependency conditions to route control flow, add a Web or Logic App activity on failure to send a notification, log failures to a SQL audit table with a Stored Procedure activity, and set retry count and retry interval on activities for transient errors.

Trainer tip: Mention that a pipeline with only failure-path activities can still report success — you often need Set Variable plus an If Condition to force the run to fail properly.

In Monitor, choose 'Rerun from failed activity', which resumes from the failing point rather than the beginning. Tumbling window triggers additionally allow rerunning specific windows.

Run history is retained for 45 days in the service. For longer retention, enable diagnostic settings to send logs to Log Analytics or a storage account.

A debug run executes the unpublished version of the pipeline in the authoring session and appears under Debug in Monitor. A triggered run executes the published version and appears under Pipeline runs.

Store secrets in Azure Key Vault, add a Key Vault linked service, and reference the secret by name in other linked services. Grant the data factory's managed identity get and list permissions on the vault so no password is ever stored in JSON or Git.

Each factory gets a system-assigned Microsoft Entra identity. You grant that identity roles on storage, SQL or Key Vault so authentication happens without secrets, which removes credential rotation and leakage risk.

Use a managed virtual network with managed private endpoints for supported PaaS sources, or a self-hosted integration runtime installed inside the private network or on-premises for everything else.

Data in transit uses TLS; data at rest in linked stores uses those services' encryption. Factory metadata is encrypted by Microsoft-managed keys by default and can use customer-managed keys stored in Key Vault.

Create a storage event trigger on blob created for the landing container, pass the trigger's folder path and file name into the pipeline as parameters, copy just that file, then move or archive it to a processed folder. Log processed file names to a control table so a duplicate event cannot reload the same file.

Build a metadata-driven framework: a control table lists schema, table, watermark column and load type; a Lookup reads it; a ForEach with a sensible batch count loops the list; a parameterized Copy activity writes Parquet into a partitioned folder path; a Stored Procedure updates the watermark. Connect through a self-hosted integration runtime with multiple nodes.

Open the Copy activity details to see throughput, DIU usage and which stage dominated (reading, transfer or writing). Check whether source volume grew, whether the source query lost an index or partition filter, whether the self-hosted IR node is CPU or memory bound, and whether the sink is throttling. Then act: repartition, raise DIUs, tune the query or stage the load.

Develop in a Git branch, raise a pull request into the collaboration branch, publish to generate the ARM template, and deploy through an Azure DevOps release pipeline with environment-specific parameter files for linked services, and stop and restart triggers around the deployment.

Use a data flow with a Conditional Split on the validation rules: valid rows go to the curated sink, invalid rows go to a reject sink with a reason column, and a downstream Web activity or Logic App emails the reject count and file location to the data steward.

Set pipeline concurrency to 1, or on a tumbling window trigger set max concurrency and self-dependency so a window only starts after the previous one succeeds.

Data flows suit standard, visually expressible transformations and teams that prefer low code. Databricks suits complex logic, reusable libraries, unit-tested code, machine learning workloads and very large volumes where fine control over Spark matters.

Use the Databricks Notebook activity's base parameters, read them inside the notebook with dbutils.widgets.get, and return values to ADF with dbutils.notebook.exit, which appears in the activity output as runOutput.

Delta Lake adds ACID transactions, schema enforcement and evolution, time travel and efficient upserts (MERGE) on top of Parquet in the lake — which makes incremental and idempotent loads reliable instead of best-effort.

Bronze holds raw ingested data as received, silver holds cleaned, conformed and deduplicated data, and gold holds business-level aggregates and dimensional models serving BI. ADF typically orchestrates the movement between the layers.

Land the data as Parquet in ADLS, then use COPY INTO or PolyBase through a staged Copy activity rather than row-by-row inserts, choose an appropriate distribution (hash on a high-cardinality join key) and create statistics after the load.

Synapse Pipelines is essentially the same engine hosted inside Synapse workspaces, and Fabric Data Factory brings the same concepts into Fabric with dataflows and pipelines. Skills transfer almost one to one, which is worth saying in an interview.