Azure Data Factory Interview Questions

Quick answer

Most Azure Data Factory (ADF) interview questions come from five areas: core components (pipeline, activity, dataset, linked service, integration runtime), control flow and expressions, Copy activity and incremental loads, error handling and monitoring, and CI/CD with Git. Freshers are asked definitions and simple pipelines; engineers with 3+ years are asked scenario questions such as "load only changed data", "resume a failed pipeline" or "design a metadata-driven framework". Answer with the ADF components you would use, the edge cases and how you would prove the result.

Key takeaways

1. Know the five building blocks cold: pipeline, activity, dataset, linked service and integration runtime.
2. Expressions are tested live: be ready to write @pipeline().parameters, @activity().output and @concat with formatDateTime.
3. Incremental loading is the most common scenario: watermark, change tracking, CDC and last-modified file filters.
4. Experienced roles focus on reliability: retries, failure paths, rerun-safe loads, alerts and Log Analytics.
5. Senior roles add CI/CD and design: Git branches, ARM templates, environment parameters and framework design.

1. ADF interview questions at a glance

Short answer: This page has 140 questions in 11 topic groups. Freshers can start with sections 2 to 5; experienced engineers should focus on sections 6 to 12.

What interviewers check at each level:

2. ADF interview questions for freshers (basics)

Q1. What is Azure Data Factory?

Answer: Azure Data Factory (ADF) is Microsoft's cloud service for building, scheduling and monitoring data pipelines. It moves data between a wide range of sources and destinations and orchestrates transformations on other compute. You build pipelines visually in ADF Studio, and you pay only for what you run.

How it works:

• You connect to sources and sinks with linked services.
• You describe the data shape with datasets.
• You chain activities (Copy, Lookup, Data Flow, Databricks Notebook and more) inside a pipeline.
• Triggers start the pipeline, and the Monitor hub shows every run.

Example: A retail company copies daily sales from an on-premises SQL Server into ADLS Gen2, then calls a Databricks notebook to clean the data and load a Synapse or Fabric warehouse. ADF runs the whole chain at 1 AM every night and sends an alert if any step fails.

Interview tip: Call ADF an "orchestration and data integration service", and mention that Microsoft also offers Data Factory in Microsoft Fabric as the next generation, while Azure Data Factory itself is still fully supported.

Q2. What is the difference between ETL and ELT, and which does ADF support?

Answer: ETL transforms data before loading it into the target, while ELT loads raw data first and transforms it inside the target platform. ADF supports both patterns. In modern lakehouse projects, ELT is more common.

How it works:

• ETL in ADF: Copy data to a staging area, transform it with a mapping data flow, then write the clean result to the warehouse.
• ELT in ADF: Copy raw data as-is into ADLS Gen2 or a warehouse, then run SQL, a stored procedure or a Databricks notebook to transform it there.
• ADF acts as the orchestrator in both cases. The heavy work runs on Spark, SQL or Databricks.

Example: For ELT, a pipeline copies 50 raw tables from Oracle into the Bronze layer in ADLS Gen2. Then a Databricks Notebook activity builds Silver and Gold Delta tables. For ETL, a mapping data flow cleans customer addresses before writing them to Azure SQL Database.

Interview tip: Say that ELT keeps the raw copy for reprocessing and uses cheap, scalable compute, which is why most Azure lakehouse teams prefer it.

Q3. What are the main components of Azure Data Factory?

Answer: The main components are pipelines, activities, datasets, linked services, integration runtimes and triggers. Data flows, parameters, variables and global parameters add flexibility on top. Together they define what runs, on what data, where and when.

How it works:

Example: Pipeline pl_load_sales has a Copy activity. Its source dataset ds_sql_sales uses linked service ls_onprem_sql, which connects through a self-hosted IR. A schedule trigger starts it every night.

Interview tip: Interviewers like the analogy: the linked service is the "connection", the dataset is the "what", the activity is the "action" and the IR is the "where it runs".

Q4. What is the difference between a pipeline and an activity?

Answer: A pipeline is a container that groups related steps into one unit of work, while an activity is a single step inside that pipeline. You run, schedule and monitor pipelines; activities are what actually do the work.

How it works:

• A pipeline can hold many activities connected by dependency conditions (Succeeded, Failed, Completed, Skipped).
• Activities without dependencies between them run in parallel.
• A pipeline can call another pipeline with the Execute Pipeline activity.
• Parameters and variables are defined at pipeline level and used by its activities.

Example: Pipeline pl_daily_customer_load contains four activities: Lookup (get the last watermark), Copy (pull new rows), Stored Procedure (update the watermark) and Web (post a success message to Teams). Each activity has its own status, but the pipeline run shows the overall result.

Interview tip: Mention that pipeline status depends on how activities and their dependency paths finish, so a failed activity does not always mean a failed pipeline.

Q5. What is the difference between a dataset and a linked service?

Answer: A linked service holds the connection information for a data store or compute, while a dataset points to specific data inside that store, such as a table, file or folder. One linked service can serve many datasets.

How it works:

• Linked service: server name, database, account URL, authentication method and the integration runtime to use. It is like a connection string.
• Dataset: table name, file path, file format (CSV, Parquet, JSON) and optional schema. It references one linked service.
• Both can be parameterized, so one dataset can read any table or file.

Example:

{
  "name": "ds_adls_parquet",
  "properties": {
    "linkedServiceName": { "referenceName": "ls_adls_raw", "type": "LinkedServiceReference" },
    "type": "Parquet",
    "parameters": { "folder": { "type": "String" } },
    "typeProperties": { "location": { "type": "AzureBlobFSLocation",
      "fileSystem": "raw", "folderPath": { "value": "@dataset().folder", "type": "Expression" } } }
  }
}

Interview tip: A common mistake is saying a dataset stores data. It does not; it only describes where the data is and what shape it has.

Q6. What is an integration runtime in ADF?

Answer: An integration runtime (IR) is the compute infrastructure that ADF uses to move data, run data flows, dispatch activities to other services and run SSIS packages. There are three types: Azure IR, self-hosted IR and Azure-SSIS IR.

How it works:

• Azure IR: fully managed, serverless compute for cloud-to-cloud copies, data flows and activity dispatch. It can also run inside a managed virtual network.
• Self-hosted IR: software you install on a Windows machine in your network to reach on-premises or private data.
• Azure-SSIS IR: a managed cluster of Azure VMs that runs existing SSIS packages.
• The linked service's connectVia property decides which IR is used.

Example: A company copies from an on-premises SQL Server (self-hosted IR) into ADLS Gen2, then runs a mapping data flow on an Azure IR to clean the data, and runs a legacy SSIS package on an Azure-SSIS IR.

Interview tip: Explain that the IR is the bridge between activities and linked services, and that the IR's location decides where the data movement happens.

Q7. What is the difference between ADF and SSIS?

Answer: SSIS is an on-premises ETL tool that runs on SQL Server, while ADF is a cloud-native, serverless orchestration and data integration service. ADF scales on demand, connects to many cloud sources and is billed per use. ADF can also run existing SSIS packages on an Azure-SSIS IR.

How it works:

Example: A bank has 300 SSIS packages. In phase one, it lifts and shifts them to an Azure-SSIS IR with little change. In phase two, it rebuilds the most important ones as native ADF pipelines and data flows.

Interview tip: Mention the "lift and shift, then modernize" path; it shows you understand real migration projects.

Q8. What is the difference between ADF and Azure Databricks?

Answer: ADF is mainly an orchestration and data movement service, while Azure Databricks is a Spark-based platform for heavy transformation, analytics and machine learning. Most Azure projects use them together: ADF copies data and schedules work, and Databricks transforms it.

How it works:

• ADF offers connectors, triggers, monitoring and low-code mapping data flows.
• Databricks offers notebooks in PySpark, SQL and Scala, Delta Lake, Unity Catalog and fine control over Spark.
• ADF calls Databricks through the Databricks Notebook, Python or Jar activities and passes parameters to them.

Example: ADF copies CRM data into the Bronze layer every hour. Then a Databricks Notebook activity receives run_date as a parameter, merges data into Silver Delta tables and builds Gold aggregates for Power BI.

Interview tip: Avoid saying one replaces the other. Say "ADF orchestrates, Databricks transforms", and mention that simple transformations can stay in ADF data flows.

Q9. How is Azure Data Factory priced?

Answer: ADF uses pay-as-you-go pricing with several meters. You pay for orchestration (activity runs), data movement (DIU-hours for Copy on Azure IR), pipeline and external activity execution time, data flow compute (vCore-hours) and Data Factory operations such as read, write and monitoring. There is no fixed server cost.

How it works:

• Orchestration: charged per activity run, trigger run and debug run.
• Data movement: number of DIUs multiplied by copy duration.
• Data flows: compute type, core count and run time, including debug sessions.
• Azure-SSIS IR: charged by node size and how long the IR is running.
• The Monitor hub shows a consumption report for each pipeline run.

Example: A team noticed high cost from a pipeline that ran a ForEach over 2,000 tiny files every 5 minutes. Switching to a storage event trigger and batching files cut activity runs sharply.

Interview tip: Name the main meters and say you check the per-run consumption report and Azure Cost Management before tuning.

Q10. How do you monitor pipelines in ADF?

Answer: You monitor pipelines in the Monitor hub of ADF Studio, which lists pipeline runs, activity runs and trigger runs with status, duration, inputs, outputs and errors. For long-term history and alerts, you send diagnostic logs to Log Analytics and use Azure Monitor alerts.

How it works:

• Pipeline runs show statuses such as Succeeded, Failed, In Progress, Queued or Canceled.
• Click a run to see each activity's input, output and error details.
• ADF keeps pipeline run data for only 45 days, so route diagnostic logs to Log Analytics or a storage account to keep more.
• Azure Monitor alerts can email or text the team when failed pipeline runs exceed zero.

Example:

ADFPipelineRun
| where TimeGenerated > ago(1d)
| where Status == "Failed"
| project TimeGenerated, PipelineName, RunId, FailureType
| order by TimeGenerated desc

Interview tip: Mention the 45-day retention limit and Log Analytics; it separates beginners from people who have supported production pipelines.

Q11. What kinds of data sources does ADF support?

Answer: ADF supports a wide range of built-in connectors across Azure services, databases, files, NoSQL stores, generic protocols and SaaS apps. Common ones include Azure SQL Database, ADLS Gen2, Blob Storage, Synapse, Fabric Lakehouse, SQL Server, Oracle, SAP, Snowflake, Salesforce, REST, HTTP, SFTP and Amazon S3.

How it works:

• Azure stores: Blob, ADLS Gen2, Azure SQL, Cosmos DB, Synapse, Data Explorer.
• Databases: SQL Server, Oracle, MySQL, PostgreSQL, Teradata, DB2.
• Files and protocols: SFTP, FTP, file system, HDFS, HTTP, REST, OData, ODBC.
• SaaS: Salesforce, Dynamics 365, ServiceNow and more.
• Some connectors are source-only, some support Lookup or Get Metadata, and some are in preview. Each connector page lists its supported capabilities.
• Private sources need a self-hosted IR or managed private endpoints.

Example: A logistics company reads shipment files from a partner's SFTP server, order data from Oracle on-premises and tracking events from a REST API, and lands all three in ADLS Gen2 as Parquet.

Interview tip: Say you always check the connector's documentation page for supported activities, authentication types and preview status before designing a pipeline.

Q12. What is the Copy Data tool in ADF?

Answer: The Copy Data tool is a guided wizard in ADF Studio that builds a copy pipeline for you step by step. You pick a source, a destination, the tables or files and a schedule, and it creates the linked services, datasets, pipeline and trigger.

How it works:

• It supports a one-time run or a scheduled or tumbling window run.
• It can copy many tables at once and builds a ForEach-based pipeline for that.
• You can set column mapping, file format, and performance settings such as DIUs and parallel copies.
• The generated pipeline is normal ADF code, so you can edit it afterwards.

Example: A fresher must load 20 tables from Azure SQL Database into ADLS Gen2 as Parquet. The Copy Data tool builds the whole pipeline in a few minutes. The engineer then renames objects to the team's naming standard and adds parameters.

Interview tip: Say the wizard is great for quick starts and learning, but production pipelines should be parameterized, metadata-driven and stored in Git.

Q13. What is ADF Studio and what are its main hubs?

Answer: ADF Studio is the browser-based authoring and monitoring tool for Azure Data Factory. It has four main hubs: Home, Author, Monitor and Manage. You open it from the data factory page in the Azure portal by selecting "Launch Studio".

How it works:

• Home: quick links such as Ingest (Copy Data tool), Orchestrate and Transform data.
• Author: build pipelines, datasets, data flows and Power Query activities, and run Debug.
• Monitor: view pipeline, trigger and activity runs, rerun pipelines and set alerts.
• Manage: linked services, integration runtimes, Git configuration, triggers, global parameters, managed private endpoints and ARM template settings.

Example: A developer creates a linked service in Manage, builds and debugs a pipeline in Author, commits to a feature branch in Git, publishes after review, and checks the scheduled run in Monitor the next morning.

Interview tip: Mention that Debug runs do not need publishing, while triggers only fire for published pipelines.

Q14. What are data movement, data transformation and control activities?

Answer: ADF groups activities into three types. Data movement activities copy data, data transformation activities change data, and control activities manage the flow of the pipeline. Knowing the difference helps you design clean pipelines.

How it works:

Example: A pipeline uses Get Metadata (control) to list new files, ForEach (control) to loop over them, Copy (movement) to load each file, and a Databricks Notebook (transformation) to merge the data into Delta tables.

Interview tip: Mention that most transformation activities only dispatch work to external compute; ADF itself runs only mapping data flows and Power Query (wrangling) activities on its own Spark clusters.

Q15. What is a trigger in Azure Data Factory?

Answer: A trigger is an object that decides when a pipeline run starts. ADF supports schedule triggers, tumbling window triggers and event-based triggers (storage events and custom events). You can also run a pipeline on demand with "Trigger now", REST API, PowerShell or SDK.

How it works:

• Schedule trigger: runs on a wall-clock calendar, for example daily at 6 AM IST.
• Tumbling window trigger: runs for fixed, non-overlapping time windows and keeps state, so it supports backfill, retries and dependencies.
• Storage event trigger: runs when a blob is created or deleted.
• Custom event trigger: runs when an event arrives on an Event Grid custom topic.
• Triggers start firing only after you publish them.

Example: Finance data uses a schedule trigger at 2 AM. IoT files use a storage event trigger the moment each file lands. An hourly aggregate uses a tumbling window trigger so missed hours can be rerun.

Interview tip: Say that schedule triggers have a many-to-many relationship with pipelines, while a tumbling window trigger is linked to exactly one pipeline.

3. Pipelines, activities and control flow

Q16. What is the Lookup activity and what are its limits?

Answer: The Lookup activity reads data from a dataset, query or stored procedure and returns it as activity output for later activities. It can return only the first row or all rows. It returns at most 5,000 rows, and its output can be at most 4 MB, otherwise the activity fails.

How it works:

• With firstRowOnly true (the default), the output is under firstRow.
• With firstRowOnly false, the output has count and a value array, which you usually pass to a ForEach.
• If a query returns more than 5,000 rows, only the first 5,000 come back.
• The longest a Lookup can run before timing out is 24 hours.

Example: Read the last watermark and use it in a Copy query:

SELECT * FROM dbo.Orders
WHERE ModifiedDate > '@{activity('LookupWatermark').output.firstRow.LastValue}'

Interview tip: If asked how to handle more than 5,000 rows, describe a two-level pipeline: the outer pipeline loops over pages or batches and an inner pipeline does a smaller Lookup for each.

Q17. What is the Get Metadata activity and what fields can it return?

Answer: The Get Metadata activity returns information about a file, folder or table, such as its name, size, last modified time, child items, structure and whether it exists. You use it to make decisions or loops based on what is actually in storage.

How it works:

• Common fields: itemName, itemType, size, created, lastModified, childItems, contentMD5, structure, columnCount and exists.
• Field support depends on the connector; for example, childItems works only on folders.
• If you include exists, the activity does not fail when the object is missing; it returns exists: false.
• Returned metadata can be at most 4 MB. Wildcard filters are not supported.

Example: A pipeline checks for a file, then loops over the folder:

If Condition expression: @activity('GetFolder').output.exists
ForEach items: @activity('GetFolder').output.childItems
Inside ForEach file name: @item().name

Interview tip: Mention that modifiedDatetimeStart and modifiedDatetimeEnd filter childItems by last-modified time, but the activity still lists the whole folder, so avoid huge folders.

Q18. How does the ForEach activity work, and what is the difference between sequential and parallel?

Answer: ForEach loops over an array and runs its inner activities once per item, using @item() for the current value. In sequential mode it runs one item at a time. In parallel mode (the default) it runs many items at once, controlled by batchCount, which defaults to 20 and can be at most 50.

How it works:

• items must be an expression that returns a JSON array, such as Lookup output or Get Metadata childItems.
• isSequential: true keeps order and avoids conflicts.
• batchCount is an upper limit, not a guarantee of exactly that many parallel runs.
• One ForEach can iterate over up to 100,000 items.

Example:

{
  "name": "ForEachTable",
  "type": "ForEach",
  "typeProperties": {
    "isSequential": false,
    "batchCount": 10,
    "items": { "value": "@activity('LookupTables').output.value", "type": "Expression" }
  }
}

Interview tip: Explain that you lower batchCount to protect a busy source database, and use sequential mode when iterations write to the same file or table.

Q19. What is the difference between the If Condition and Switch activities?

Answer: If Condition runs one of two branches based on an expression that returns true or false. Switch evaluates an expression that returns a string and runs the matching case, or a default branch if nothing matches. Use If for yes/no logic and Switch for many possible values.

How it works:

• If Condition: properties expression, ifTrueActivities and ifFalseActivities.
• Switch: properties expression, cases (each with a string value and activities) and defaultActivities. A Switch can have at most 25 cases.
• Both can contain other activities, so you avoid many separate pipelines.

Example:

If Condition: @greater(activity('LookupCount').output.firstRow.cnt, 0)
               True -> Copy new rows
               False -> Set Variable "No new data"

Switch on: @pipeline().parameters.sourceSystem
               Case "SAP" -> Execute pipeline pl_sap
               Case "ORACLE" -> Execute pipeline pl_oracle
               Default -> Fail activity "Unknown source"

Interview tip: Mention that Switch case values are strings, so convert numbers with string() before comparing.

Q20. How does the Until activity work?

Answer: The Until activity runs a set of activities in a loop until an expression becomes true or the loop times out. It works like a do-until loop: inner activities run first, then the condition is checked. The default timeout is seven days and the maximum is 90 days.

How it works:

• expression must return true or false.
• timeout uses d.hh:mm:ss or hh:mm:ss format.
• If an inner activity fails, the Until loop does not stop by itself; your condition must handle it.
• Add a Wait activity inside to avoid calling a service too often.

Example: Poll an API until a report export is ready:

Until expression: @equals(variables('status'), 'Completed')
Timeout: 0.02:00:00
Inside: Web (GET /exports/123/status)
         Set Variable status = @activity('CheckStatus').output.status
         Wait 60 seconds

Interview tip: Always say you set a sensible timeout and a Wait inside the loop; an Until without them can run for days and waste money.

Q21. What is the Wait activity and when do you use it?

Answer: The Wait activity pauses the pipeline for a set number of seconds before the next activity runs. You use it for polling loops, simple retry delays and giving an external system time to finish. Its main property is waitTimeInSeconds, which can be an expression.

How it works:

• It does not check any condition; it just waits.
• Inside an Until loop, it controls how often you poll.
• The value can come from a parameter or from a child pipeline's return value.
• For "wait until a file arrives", the Validation activity or a storage event trigger is usually better.

Example:

{
  "name": "WaitBeforeRetry",
  "type": "Wait",
  "typeProperties": {
    "waitTimeInSeconds": { "value": "@pipeline().parameters.retryDelaySec", "type": "Expression" }
  }
}

Interview tip: Say that you avoid long Wait activities as a scheduling trick, because a trigger or Validation activity is cleaner and easier to monitor.

Q22. What is the Execute Pipeline activity and what does "Wait on completion" do?

Answer: The Execute Pipeline activity lets one pipeline (the parent) call another pipeline (the child) and pass parameters to it. "Wait on completion" (waitOnCompletion, default true) makes the parent wait until the child finishes and take its success or failure.

How it works:

• With waitOnCompletion true, the activity's status matches the child run, so you can branch on it.
• With false, the parent fires the child and moves on immediately ("fire and forget").
• Array parameters are passed to the child as strings, so you may need json() in the child.
• Parent and child are separate runs; the child can read @pipeline()?.TriggeredByPipelineRunId.

Example:

{
  "name": "RunChild",
  "type": "ExecutePipeline",
  "typeProperties": {
    "pipeline": { "referenceName": "pl_load_table", "type": "PipelineReference" },
    "parameters": { "tableName": "@item().TableName" },
    "waitOnCompletion": true
  }
}

Interview tip: Mention that pipeline return values only work when "Wait on completion" is on.

Q23. What is the Fail activity?

Answer: The Fail activity deliberately fails a pipeline with a custom error message and error code. You use it when a business rule is broken even though no technical error happened, such as zero rows received or a bad file. Both message and errorCode are required and can use dynamic content.

How it works:

• When the Fail activity runs, it ends with status Failed and the error you set.
• That failure flows up to the pipeline run and to any parent Execute Pipeline activity.
• It is often placed in the False branch of an If Condition.
• Alerts and logs then show your clear message instead of a vague one.

Example:

{
  "name": "FailIfNoData",
  "type": "Fail",
  "typeProperties": {
    "message": "@concat('No rows found for ', pipeline().parameters.loadDate)",
    "errorCode": "DQ001"
  }
}

Interview tip: Say you use Fail inside try-catch designs so that a pipeline which caught an error can still be marked Failed for monitoring.

Q24. What is the difference between Set Variable and Append Variable, and why avoid them in a parallel ForEach?

Answer: Set Variable assigns a new value to a pipeline variable, while Append Variable adds one value to the end of an Array variable. Avoid Set Variable in a parallel ForEach because variables are scoped to the whole pipeline, not to each iteration, so parallel iterations overwrite each other.

How it works:

• Set Variable works with variables of type String, Boolean and Array.
• Set Variable cannot reference the variable it is setting, so use a temp variable to increment a counter.
• Append Variable is used to collect results from loop iterations.
• Set Variable can also set the special Pipeline return value.

Example: Increment a counter in two steps:

Set Variable temp = @string(add(int(variables('counter')), 1))
Set Variable counter = @variables('temp')

Interview tip: The safe fixes are a sequential ForEach, or moving the per-item logic into a child pipeline called by Execute Pipeline, where each run has its own variables.

Q25. What are activity dependency conditions in ADF?

Answer: Dependency conditions decide when an activity runs based on the result of the activity before it. The four conditions are Succeeded, Failed, Completed and Skipped. They let you build success paths, error paths and "always run" steps.

How it works:

• Succeeded (green): runs only if the previous activity succeeded.
• Failed (red): runs only if the previous activity failed.
• Completed (blue): runs whether the previous activity succeeded or failed.
• Skipped (grey): runs only if the previous activity did not run.
• If an activity depends on several activities, all conditions must be met (AND logic).

Example: Copy has two paths. On Succeeded, a Stored Procedure updates the watermark. On Failed, a Web activity sends an email with @activity('CopyOrders').error.message. On Completed, a Script activity writes an audit row for every run.

Interview tip: Mention that adding a Failed path can change the final pipeline status, and explain that you add a Fail activity if the run must still show as Failed.

Q26. What is the difference between the Script activity and the Stored Procedure activity?

Answer: The Stored Procedure activity calls an existing stored procedure in a SQL database. The Script activity runs one or more SQL statements (DDL or DML) directly and can return result sets, output parameters and logs. Use Stored Procedure for reusable logic, and Script for ad-hoc SQL or when you need results back.

How it works:

• Script activity: supports Azure SQL Database, Azure Synapse Analytics, SQL Server, Oracle, Snowflake and Azure Database for PostgreSQL. Each script block is Query or NonQuery.
• Script output can include resultSets, recordsAffected and output parameters, with result sets limited to 5,000 rows and 4 MB.
• Stored Procedure activity: passes parameters to the procedure but does not return its result set.

Example:

TRUNCATE TABLE stg.Orders;
INSERT INTO audit.LoadLog (PipelineName, RunId, LoadTime)
VALUES ('@{pipeline().Pipeline}', '@{pipeline().RunId}', SYSUTCDATETIME());

Interview tip: Say that the Script activity is billed as a pipeline activity, and that you keep business logic in stored procedures so it is versioned in the database project.

Q27. What is the difference between the Web and Webhook activities?

Answer: The Web activity calls a REST endpoint and continues when it gets a response. The Webhook activity calls an endpoint, passes a callBackUri, and then waits until the external service calls that URI back or the timeout is reached. Use Webhook for long-running external jobs.

How it works:

• Web: methods GET, POST, PUT, PATCH and DELETE. The response must be JSON, and the payload is limited to 4 MB. The default HTTP response timeout is 1 minute and can be raised to 10 minutes with httpRequestTimeout.
• Webhook: POST only. Its default timeout is 10 minutes. With reportStatusOnCallBack, the callback can mark the activity as failed.
• Both support managed identity authentication.

Example: A Web activity posts a success message to a Logic App, which sends an email. A Webhook activity starts an Azure Automation runbook that rebuilds a cube and calls back to ADF when done, so the pipeline waits for the real finish.

Interview tip: Mention that the Web activity also follows the HTTP 202 async pattern by polling the location header, unless you turn that off.

Q28. What is the Validation activity?

Answer: The Validation activity blocks the pipeline until a file or folder exists and meets your criteria, or until a timeout is reached. It is used to wait for an expected file before starting a load. The default timeout is 12 hours and the default check interval (sleep) is 10 seconds.

How it works:

• minimumSize checks that a file is at least a given number of bytes, which avoids empty files.
• childItems: true waits until a folder has at least one item; false waits until it is empty.
• If the timeout is reached, the activity fails, so you can alert on it.
• The dataset must support size or child item checks.

Example:

{
  "name": "WaitForSalesFile",
  "type": "Validation",
  "typeProperties": {
    "dataset": { "referenceName": "ds_sales_today", "type": "DatasetReference" },
    "timeout": "0.03:00:00",
    "sleep": 60,
    "minimumSize": 1024
  }
}

Interview tip: Compare it with a storage event trigger: Validation polls inside a running pipeline, while an event trigger starts a pipeline only when the file arrives.

Q29. Can you nest a ForEach inside another ForEach? What is the workaround?

Answer: No. ADF does not allow a ForEach or Until loop directly inside another ForEach or Until. The standard workaround is a two-level design: the outer pipeline has the first ForEach and calls an inner pipeline with Execute Pipeline, and the inner pipeline has the second loop.

How it works:

• The outer ForEach passes the current item (for example a database name) as a parameter.
• The inner pipeline runs its own Lookup and ForEach (for example over tables in that database).
• Each child run has its own variables, which also fixes the Set Variable problem in parallel loops.
• The same design gets around the 5,000-row Lookup limit and the 100,000-item ForEach limit.

Example:

pl_outer: Lookup databases -> ForEach db (batchCount 4)
              Execute Pipeline pl_inner (dbName = @item().DbName)
pl_inner: Lookup tables in @pipeline().parameters.dbName
              ForEach table (batchCount 8) -> Copy

Interview tip: Mention the multiplication effect: 4 outer times 8 inner can mean 32 parallel copies, so plan source load and IR capacity.

Q30. What is a pipeline return value and how do you use it?

Answer: A pipeline return value lets a child pipeline send key-value pairs back to the parent pipeline that called it. You set it in the child with a Set Variable activity using the "Pipeline return value" option. The parent reads it from the Execute Pipeline activity output.

How it works:

• The parent's Execute Pipeline activity must have "Wait on completion" enabled.
• Value types include String, Expression, Array, Boolean, Null, Int, Float and Object.
• The returned JSON can be up to 4 MB.
• If the parent references a key the child did not return, the parent fails, so return the same keys from every branch, using Null when needed.

Example: In the parent pipeline:

@activity('RunChildLoad').output.pipelineReturnValue.rowsLoaded
@activity('RunChildLoad').output.pipelineReturnValue.files[0]

Interview tip: Mention that before this feature, teams wrote results to a SQL table and read them back with Lookup; return values are simpler and faster.

4. Integration runtimes and linked services

Q31. What are the three types of integration runtime and when do you use each?

Answer: The three types are Azure IR, self-hosted IR and Azure-SSIS IR. Use Azure IR for cloud data and data flows, self-hosted IR for private or on-premises data, and Azure-SSIS IR to run SSIS packages. Many real factories use all three.

How it works:

• Data flows run only on an Azure IR.
• An Azure IR can also run inside a managed virtual network for private endpoint access.
• Synapse pipelines support Azure and self-hosted IRs, but not Azure-SSIS.

Example: An insurance company uses a self-hosted IR to pull policy data from on-premises Oracle, an Azure IR in a managed VNet to run data flows against a private storage account, and an Azure-SSIS IR for 40 legacy SSIS packages.

Interview tip: Interviewers often ask "where does a mapping data flow run?" The answer is always an Azure IR (Spark clusters managed by ADF).

Q32. When do you need a self-hosted integration runtime?

Answer: You need a self-hosted IR when ADF must reach data that has no public endpoint, such as an on-premises database, a file share, or a server inside a private Azure virtual network. You also need it for connectors that require your own driver, such as some ODBC or SAP sources.

How it works:

• You install the SHIR software on a Windows machine that can reach the source.
• It registers with your factory using an authentication key.
• It makes only outbound HTTPS (port 443) connections to Azure, so no inbound firewall ports are needed.
• Credentials are encrypted on the node using Windows DPAPI, or kept in Azure Key Vault.
• The Java Runtime Environment (JRE) is a dependency of the SHIR, so install it on the same machine.

Example: A manufacturing company has SQL Server in its Hyderabad data centre. The team installs a SHIR on a VM in the same network, opens outbound 443 to the ADF endpoints, and copies data nightly into ADLS Gen2.

Interview tip: Mention that you should not install the SHIR on the database server itself in production, because both compete for CPU and memory.

Q33. How do you make a self-hosted IR highly available and scalable?

Answer: You install the SHIR on more machines and register them to the same logical IR. A single self-hosted IR can have up to four nodes, which work in active-active mode. This removes the single point of failure and spreads concurrent jobs across nodes.

How it works:

• Scale out: add nodes (up to four) when CPU or memory is high on existing nodes.
• Scale up: raise the "concurrent jobs" limit per node when CPU and memory are free but jobs queue.
• Enable "Remote access to intranet" on the first node before adding more.
• Keep all nodes on the same SHIR version, ideally with auto-update on.
• One SHIR can be shared with other data factories in the same Microsoft Entra tenant as a linked IR.

Example: A team's single SHIR node caused nightly failures during Windows patching. They added a second node in another server rack and staggered patch windows, so jobs moved to the healthy node.

Interview tip: Say you watch SHIR CPU, memory and queue length in Azure Monitor before deciding to scale up or scale out.

Q34. What is the Azure IR location and how does auto-resolve work?

Answer: The Azure IR location is the region where the compute runs the copy or dispatch. The default "AutoResolveIntegrationRuntime" lets ADF pick the region automatically. For Copy, it tries to use the sink's region; for Lookup, Get Metadata, data flows and dispatch, it uses the factory's region.

How it works:

• If the sink region cannot be detected, the copy runs in the factory's region.
• You can create an Azure IR pinned to a specific region and point linked services to it with connectVia.
• Pinning a region helps with data residency rules and egress cost.
• In a managed virtual network, auto-resolve uses the factory's region.

Example: A factory in Central India copies from Blob in West Europe to ADLS Gen2 in West Europe. With auto-resolve, the copy runs in West Europe, near the data. For an EU data-residency rule, the team creates an Azure IR fixed to West Europe so data never leaves the EU.

Interview tip: Mention that you can see which IR location was used in the activity run's monitoring output.

Q35. What is a managed virtual network integration runtime?

Answer: A managed VNet IR is an Azure IR created inside a virtual network that Azure Data Factory manages for you. It connects to data stores through managed private endpoints, so traffic stays on the Microsoft backbone and data exfiltration risk is reduced. You do not need to plan subnets or manage the network yourself.

How it works:

• Enable managed VNet when creating the factory or the Azure IR. An existing global IR cannot be switched.
• Create managed private endpoints in the Manage hub; the resource owner must approve each one.
• Compute starts cold, so set a time to live (TTL) for Copy, pipeline and external activities to reuse warm compute.
• Custom DNS is not supported, and the managed VNet must be in the factory's region.

Example: A healthcare client disables public access on its storage account and Azure SQL Database. The team creates a managed VNet IR plus managed private endpoints to both, gets approval from the platform team, and the pipelines run privately.

Interview tip: Mention cold start time and TTL; interviewers ask why copies are slower in a managed VNet.

Q36. What is the Azure-SSIS integration runtime?

Answer: The Azure-SSIS IR is a fully managed cluster of Azure VMs that runs SQL Server Integration Services packages in the cloud. It lets you lift and shift existing SSIS work with little or no change. Packages can live in an SSISDB catalog hosted on Azure SQL Database or SQL Managed Instance.

How it works:

• You choose the node size (scale up) and number of nodes (scale out).
• You can join it to a virtual network to reach on-premises data.
• You keep using SSDT and SSMS to deploy and manage packages.
• ADF runs packages with the Execute SSIS Package activity.
• You pay while the IR is running, so start and stop it around the batch window.

Example: A company has 120 SSIS packages. It creates an Azure-SSIS IR in the same region as its SQL Managed Instance (which holds SSISDB), joins it to a VNet connected to on-premises, and schedules packages from ADF pipelines.

Interview tip: Say that you start the IR at the start of the batch and stop it at the end with Web activities calling the ADF REST API, to save cost.

Q37. What authentication methods can a linked service use?

Answer: It depends on the connector, but common methods are account key, SAS token, service principal, system-assigned managed identity, user-assigned managed identity, SQL authentication, Windows authentication and Basic or OAuth for REST. Managed identity is the preferred choice for Azure services because there is no secret to rotate.

How it works:

• Managed identity: grant the factory's identity an RBAC role, such as Storage Blob Data Contributor on ADLS Gen2.
• Service principal: use an app registration with a secret or certificate, ideally kept in Key Vault.
• Account key and SAS: simple but powerful; store them in Key Vault and rotate them.
• SQL or Windows auth: used for databases, often through a SHIR.

Example:

{
  "name": "ls_adls_raw",
  "properties": {
    "type": "AzureBlobFS",
    "typeProperties": { "url": "https://mydatalake.dfs.core.windows.net" }
  }
}

With no key in the definition, this ADLS Gen2 linked service uses the factory's system-assigned managed identity.

Interview tip: Say "managed identity first, Key Vault for everything else, never plain secrets in JSON or Git."

Q38. How do you use Azure Key Vault secrets in a linked service?

Answer: You store the password or connection string as a secret in Key Vault, give the factory's managed identity read access to secrets, create a Key Vault linked service, and then reference the secret by name in the data store's linked service. ADF reads the secret at runtime.

How it works:

• Grant access with the "Key Vault Secrets User" role, or an access policy with Get and List secret permissions.
• The Key Vault linked service has type AzureKeyVault and a baseUrl.
• The secret reference uses type AzureKeyVaultSecret with secretName and an optional secretVersion.
• Without secretVersion, ADF always reads the latest version, so rotation needs no redeploy.

Example:

"password": {
  "type": "AzureKeyVaultSecret",
  "secretName": "sql-onprem-password",
  "store": { "referenceName": "ls_keyvault", "type": "LinkedServiceReference" }
}

Interview tip: Mention that with Git integration, ADF does not save passwords to Git, which is one more reason to use Key Vault, and that each environment can point to its own vault.

Q39. What is a parameterized linked service and why use it?

Answer: A parameterized linked service accepts values such as server name or database name at runtime, so one linked service can connect to many databases. This cuts duplication and makes metadata-driven pipelines possible. Inside the definition, you reference a parameter with @{linkedService().paramName}.

How it works:

• Many common connectors support parameters directly in the UI; for others you edit the JSON.
• The dataset passes values to the linked service, and the pipeline passes values to the dataset.
• Do not parameterize the password itself; parameterize the Key Vault secret name instead.
• Avoid the "-" character in parameter names.

Example:

{
  "name": "ls_azuresql_generic",
  "properties": {
    "type": "AzureSqlDatabase",
    "parameters": { "DBName": { "type": "String" } },
    "typeProperties": {
      "connectionString": "Server=tcp:myserver.database.windows.net,1433;Database=@{linkedService().DBName};"
    }
  }
}

Interview tip: Explain the full chain: pipeline parameter goes to dataset parameter, which goes to linked service parameter. Many candidates miss the middle step.

Q40. How do you troubleshoot a linked service connection failure?

Answer: Start with "Test connection" in ADF Studio, read the exact error, then check network, authentication and IR health in that order. Most failures come from firewalls, wrong credentials, missing RBAC roles or an offline self-hosted IR.

How it works:

• Network: is the source reachable from the IR? Check firewall rules, private endpoints, DNS and port 1433 for SQL.
• Authentication: is the secret current in Key Vault? Does the managed identity have the right role?
• Self-hosted IR: is the node online and on a supported version? Check the Configuration Manager diagnostics and event logs.
• Managed VNet: is the managed private endpoint approved?
• For Azure SQL, check "Allow Azure services" or the private endpoint setup.

Example: A copy from Azure SQL failed with a login timeout after the database team turned off public access. The fix was to create a managed private endpoint, have it approved, and move the linked service to the managed VNet IR.

Interview tip: Describe your order of checks clearly; interviewers care more about a calm, step-by-step method than about one specific error code.

5. Triggers, parameters, variables and expressions

Q41. What is the difference between schedule, tumbling window, storage event and custom event triggers?

Answer: A schedule trigger runs on a calendar. A tumbling window trigger runs for fixed, back-to-back time windows and remembers the state of each window. A storage event trigger runs when a blob is created or deleted. A custom event trigger runs when an event arrives on an Event Grid custom topic.

How it works:

• Event triggers need the subscription registered with the Event Grid resource provider.
• For custom events, you create the Event Grid topic yourself; ADF does not create it.

Example: A bank uses schedule triggers for nightly loads, a tumbling window trigger for hourly card transaction windows, a storage event trigger for partner files, and a custom event trigger fired by an upstream app when month-end close completes.

Interview tip: Name the key tumbling window features: state, backfill, retry policy and dependencies.

Q42. How do tumbling window backfill and dependencies work?

Answer: If a tumbling window trigger's start time is in the past, ADF creates a run for every missed window and processes them from oldest to newest, which is called backfill. Dependencies make a window wait until a matching window of another tumbling window trigger, or the previous window of the same trigger, succeeds.

How it works:

• Frequency is Minute, Hour or Month, and the minimum window is 5 minutes.
• maxConcurrency (1 to 50) controls how many windows run at the same time.
• retryPolicy retries a failed window; the retry interval is at least 30 seconds.
• A self-dependency with a negative offset (for example -01:00:00) makes each hour wait for the previous hour.
• frequency and interval cannot be changed after publishing.

Example:

"dependsOn": [
  { "type": "SelfDependencyTumblingWindowTriggerReference",
    "offset": "-01:00:00", "size": "01:00:00" },
  { "type": "TumblingWindowTriggerDependencyReference",
    "referenceTrigger": { "referenceName": "tw_load_customers", "type": "TriggerReference" } }
]

Interview tip: Say that for a very long backfill, you do a one-time historical load first, because all past windows must finish before future ones run.

Q43. What fields does triggerBody() return?

Answer: For a storage event trigger, @triggerBody().fileName returns the file name and @triggerBody().folderPath returns the folder path, starting with the container name. For a custom event trigger, @triggerBody().event returns the event, with fields such as eventType, subject and data.

How it works:

• These values are available only in the trigger definition, so you map them to pipeline parameters.
• Inside the pipeline, you then use @pipeline().parameters.sourceFile.
• For custom events, @triggerBody().event.data.keyName reads a field from the free-form data payload. If the key is missing, the trigger run fails.
• In Synapse pipelines, use @trigger().outputs.body.fileName instead.

Example: Trigger parameter mapping:

"parameters": {
  "sourceFolder": "@triggerBody().folderPath",
  "sourceFile": "@triggerBody().fileName",
  "eventTime": "@trigger().startTime"
}

Interview tip: A common mistake is using triggerBody() directly inside an activity; interviewers expect you to say it must be mapped to a pipeline parameter first.

Q44. What is the difference between pipeline, dataset and linked service parameters?

Answer: All three let you pass values at runtime, but at different levels. Pipeline parameters are inputs to a pipeline run. Dataset parameters make one dataset point to any table or file. Linked service parameters make one connection reach any server or database.

How it works:

• Values flow downward: trigger to pipeline, pipeline to dataset, dataset to linked service.
• Parameters are read-only during a run.

Example: A trigger passes env = prod. The pipeline passes @item().TableName to dataset parameter tableName and @item().DbName to dataset parameter dbName. The dataset passes dbName to the linked service parameter DBName. One linked service and one dataset now serve 300 tables.

Interview tip: Show the full chain with an example; it proves you have built a metadata-driven framework.

Q45. What are global parameters in ADF?

Answer: Global parameters are constants defined once at factory level that any pipeline can use in expressions. They suit values shared across pipelines that change by environment, such as an environment name, a storage account URL or an email list. You reference them as @pipeline().globalParameters.name.

How it works:

• Create them in the Manage hub under Global parameters, with a name, type and value.
• To use them in a dataset or data flow, pass them through that object's parameters.
• In CI/CD, turn on "Include global parameters in ARM template" in the Manage hub's ARM template section, then override values per environment.
• That option works only in Git mode. Do not use "-" in the name; use "_".

Example:

Global parameter: alert_email = dataops-dev@company.com (Dev)
                   alert_email = dataops@company.com (Prod, via ARM override)
Web activity body: @concat('{"to":"', pipeline().globalParameters.alert_email, '"}')

Interview tip: Mention that global parameters are for non-secret values only; secrets still belong in Key Vault.

Q46. What system variables does ADF provide?

Answer: System variables are built-in values that ADF fills in at runtime. Pipeline-level ones include @pipeline().RunId, @pipeline().Pipeline, @pipeline().DataFactory, @pipeline().TriggerName, @pipeline().TriggerType and @pipeline().TriggerTime. Trigger-level ones include @trigger().scheduledTime, @trigger().startTime and, for tumbling windows, @trigger().outputs.windowStartTime and windowEndTime.

How it works:

• Pipeline-scope variables work anywhere in the pipeline JSON.
• Trigger-scope variables work only in the trigger definition, so map them to parameters.
• @pipeline()?.TriggeredByPipelineName and @pipeline()?.TriggeredByPipelineRunId return the parent pipeline when called by Execute Pipeline; otherwise they are null.
• Date and time variables return UTC in ISO 8601 format.

Example: An audit insert that uses system variables:

INSERT INTO audit.PipelineRun (RunId, PipelineName, TriggerName, StartTimeUtc)
VALUES ('@{pipeline().RunId}', '@{pipeline().Pipeline}',
        '@{pipeline().TriggerName}', '@{pipeline().TriggerTime}');

Interview tip: Mention that RunId is the best key for audit tables, because it links your log to the ADF monitor and Log Analytics.

Q47. What are the most common ADF expression functions?

Answer: The most used functions are concat, formatDateTime, utcnow, addDays, equals, if, coalesce, contains, split, replace, length, string, int and json. Every expression starts with @ and can be used in any property that supports dynamic content.

How it works:

• String: concat, replace, split, substring, toUpper, trim.
• Date: utcnow, formatDateTime, addDays, addHours, convertFromUtc, startOfDay.
• Logic: equals, greater, and, or, not, if.
• Collection: length, contains, first, last, union, createArray.
• Conversion: string, int, bool, json, array.
• Functions can be nested, and the dynamic content editor in ADF Studio lists every function, parameter, variable and activity output, so you can click to insert them instead of typing.

Example:

@concat('raw/', pipeline().parameters.source, '/', formatDateTime(utcnow(), 'yyyy/MM/dd'))
@if(equals(pipeline().parameters.loadType, 'full'), '1900-01-01', activity('GetWM').output.firstRow.LastValue)
@length(activity('GetFiles').output.childItems)

Interview tip: Interviewers often ask you to write an expression live, so practise them in the dynamic content editor until the syntax feels natural.

Q48. How do you create dynamic file names and folder paths with dates?

Answer: You build the path with formatDateTime and concat (or string interpolation) in a dataset parameter or the sink's file name. The date usually comes from utcnow(), a trigger time or a tumbling window start, so each run writes to its own date folder.

How it works:

• formatDateTime(timestamp, 'yyyy/MM/dd') returns text such as 2026/10/20.
• Use MM for month and mm for minutes; mixing them up is a classic bug.
• Prefer the trigger's scheduled time or window start over utcnow(), so reruns write to the same folder.
• Partition-style folders such as year=2026/month=10/day=20 help Spark and Synapse prune data.

Example:

Folder: @concat('sales/year=', formatDateTime(pipeline().parameters.runDate, 'yyyy'),
                '/month=', formatDateTime(pipeline().parameters.runDate, 'MM'),
                '/day=', formatDateTime(pipeline().parameters.runDate, 'dd'))
File: @concat('orders_', formatDateTime(pipeline().parameters.runDate, 'yyyyMMdd'), '.parquet')

Interview tip: Say that you pass the run date as a parameter instead of calling utcnow() inside the dataset, because it makes reruns and backfills idempotent.

Q49. What is string interpolation in ADF expressions?

Answer: String interpolation lets you embed expressions inside normal text with @{...}. ADF evaluates each block and inserts the result as a string. It is easier to read than long concat calls, especially in SQL queries and file paths.

How it works:

• A value starting with @ is a full expression; text with @{...} blocks is interpolated text.
• The result of interpolation is always a string.
• To write a literal at sign at the start of a value, escape it as @@.
• Quotes inside SQL still need care: wrap date values in single quotes in the SQL text.

Example:

SELECT *
FROM @{pipeline().parameters.schemaName}.@{pipeline().parameters.tableName}
WHERE LastModified > '@{activity('GetOldWatermark').output.firstRow.WatermarkValue}'
  AND LastModified <= '@{activity('GetNewWatermark').output.firstRow.NewWatermark}'

Interview tip: Mention that interpolation always returns a string, so when an activity expects an array, number or boolean, use a full @ expression instead.

Q50. How do you use coalesce and if in ADF expressions?

Answer: coalesce returns the first value that is not null, which is useful for default values. if(condition, valueIfTrue, valueIfFalse) returns one of two values based on a condition. Together with the ? safe-navigation operator, they prevent failures when a property is missing.

How it works:

• coalesce(a, b, c) checks values left to right and returns the first non-null one.
• if is an expression function, not an activity; it does not run activities.
• The ?. operator returns null instead of failing when a property does not exist.
• empty() checks for empty strings, arrays or objects.

Example:

@coalesce(pipeline().parameters.startDate, formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-dd'))
@if(empty(pipeline().parameters.tableList), 'ALL', pipeline().parameters.tableList)
@coalesce(activity('CallApi').output?.nextPageUrl, 'none')

Interview tip: Say that you give every optional pipeline parameter a sensible default with coalesce, so the same pipeline works for scheduled runs and manual reruns.

Q51. How do you pass values from one activity to another?

Answer: You reference the earlier activity's output with @activity('ActivityName').output, followed by the property you need. You can also store values in variables, or send them across pipelines with parameters and pipeline return values.

How it works:

• Lookup: @activity('Lookup1').output.firstRow.col or .output.value.
• Get Metadata: @activity('GetMeta').output.childItems.
• Copy: @activity('Copy1').output.rowsCopied, rowsRead, dataWritten.
• Errors: @activity('Copy1').error.message on a Failed path.
• The later activity must depend (directly or indirectly) on the earlier one, or the output is not available.
• Across pipelines: parameters go down; pipeline return values come back up.

Example:

Stored Procedure params after Copy succeeds:
  RowsRead = @activity('CopyOrders').output.rowsRead
  RowsCopied = @activity('CopyOrders').output.rowsCopied
  CopyDuration = @activity('CopyOrders').output.copyDuration

Interview tip: Mention that you open the activity's Output JSON in the Monitor hub to find the exact property path before writing the expression.

Q52. How do trigger start time and time zone work, especially for IST?

Answer: A schedule trigger has a start time, an optional end time, a recurrence and a time zone. You can pick "India Standard Time" so a daily trigger fires at 6:00 AM IST. ADF returns system date values in UTC, so convert them when you need IST in file names or queries.

How it works:

• For UTC, start and end times use the format with a trailing Z; for other time zones they have no Z.
• India Standard Time is UTC+5:30 and has no daylight saving, so run times never shift.
• For time zones with daylight saving, daily and longer schedules adjust automatically.
• A trigger only fires after it is published; the start time must be before the first run time.
• Pass @trigger().scheduledTime to the pipeline for the run date.

Example:

"recurrence": {
  "frequency": "Day", "interval": 1,
  "startTime": "2026-10-21T00:00:00",
  "timeZone": "India Standard Time",
  "schedule": { "hours": [6], "minutes": [0] }
}

Interview tip: Mention that the Monitor hub shows times in your browser's time zone, which often confuses teams that think the trigger fired at the wrong time.

Q53. How do you rerun a failed pipeline from the failed activity?

Answer: In the Monitor hub, open the failed pipeline run and choose "Rerun from failed activity". ADF skips activities that already succeeded and starts again from the activity that failed, timed out or was canceled. You can also choose "Rerun from activity" to start from any chosen point, or rerun the whole pipeline.

How it works:

• The rerun keeps the original parameters and appears grouped under the original run.
• Rerunning with new parameters is treated as a brand-new pipeline run.
• Loops and branches are re-evaluated: ForEach still loops over its items, and If or Switch still evaluate their conditions, but inner activities may be skipped by rerun rules.
• Tumbling window triggers let you rerun a single failed window.

Example: A pipeline loaded 40 tables, then failed on table 41 because of a timeout. The engineer fixed the source issue and clicked "Rerun from failed activity". Only the failed step and later steps ran, saving two hours.

Interview tip: Stress that reruns are safe only if each step is idempotent, for example using MERGE or delete-then-insert by date instead of plain inserts.

Q54. What is the difference between parameters and variables in ADF?

Answer: Parameters are inputs passed into a pipeline when it starts, and they cannot change during the run. Variables are values inside a pipeline that you can change during the run with Set Variable or Append Variable. Use parameters for configuration and variables for working values.

How it works:

• Variables are not thread safe inside a parallel ForEach.
• A child pipeline cannot read the parent's variables directly; pass them as parameters.

Example: loadDate is a parameter passed by the trigger. fileCount is a variable set after Get Metadata, and failedTables is an Array variable filled with Append Variable inside a sequential loop.

Interview tip: A one-line answer interviewers like: "parameters are read-only inputs; variables are read-write state inside one run."

Q55. How do you handle date arithmetic and time zone conversion in expressions?

Answer: Use functions such as addDays, addHours, startOfDay, startOfMonth and convertFromUtc inside expressions. They let you build "yesterday", "last month" or IST-based dates for incremental loads and folder names without hardcoding.

How it works:

• addDays(timestamp, -1, 'yyyy-MM-dd') returns yesterday's date in the given format.
• startOfDay and startOfMonth return midnight or the first day of the month.
• convertFromUtc(timestamp, 'India Standard Time', format) converts UTC to IST.
• dayOfWeek and dayOfMonth help with weekday or month-end logic in If Condition.
• Keep comparisons in UTC where possible, and convert only for display or file names.

Example:

Yesterday (IST): @formatDateTime(addDays(convertFromUtc(utcnow(), 'India Standard Time'), -1), 'yyyy-MM-dd')
Month start: @formatDateTime(startOfMonth(utcnow()), 'yyyy-MM-dd')
Is month end: @equals(dayOfMonth(addDays(utcnow(), 1)), 1)

Interview tip: Say that source systems in India often store IST while ADF works in UTC, and that ignoring this 5:30 gap is a common cause of missing or duplicate rows in incremental loads.

6. Copy activity and performance

Q56. Explain the anatomy of a Copy activity in Azure Data Factory.

Answer: A Copy activity reads data from one source dataset, optionally converts it, and writes it to one sink dataset, running on an integration runtime. It supports only one input and one output, so copying many tables means looping a parameterized Copy activity inside a ForEach.

How it works:

• Source tab: the source dataset plus options such as a query, partition option, wildcard path or additional columns.
• Sink tab: the sink dataset plus write behaviour (insert, upsert, stored procedure), pre-copy script and table option (auto create).
• Mapping tab: column mapping through a TabularTranslator.
• Settings tab: DIUs, degree of copy parallelism, staging, fault tolerance, logging and data consistency verification.
• At run time the service reads data, serializes or deserializes it, compresses or decompresses it, maps columns and writes it to the sink.

Example: A pipeline copies dbo.Orders from on-premises SQL Server through a self-hosted IR into ADLS Gen2 as Parquet. The source tab holds the query, the sink tab writes Parquet, and the settings tab turns on a session log.

Interview tip: Mention that the IR must reach both source and sink, and that one Copy activity cannot use two different self-hosted IRs.

Q57. What is a Data Integration Unit (DIU) and how does it affect cost?

Answer: A DIU is a unit of compute power (CPU, memory and network) that the Azure integration runtime gives to one Copy activity run. You can set between 4 and 256 DIUs, or leave it on Auto so the service picks a value based on your source, sink and data pattern.

How it works:

• DIUs apply only to the Azure IR. A self-hosted IR does not use DIUs; you scale it by adding nodes or raising concurrent jobs.
• The actual DIUs used can be lower than what you set, because the data pattern limits it. For example, copying a single file effectively uses 4 DIUs.
• Billing is: used DIUs x copy duration x price per DIU-hour.
• You see the real number in the activity output as usedDataIntegrationUnits.

Example: Copying 2,000 Parquet files from Amazon S3 to ADLS Gen2 runs slowly on Auto. You set "dataIntegrationUnits": 64, the copy finishes much faster, and the total cost stays similar because duration drops.

Interview tip: Say that more DIUs do not help a single-file copy or a throttled sink, so check the monitoring output before raising DIUs.

Q58. What is the difference between DIUs and parallel copies?

Answer: DIUs decide how much compute a copy gets, while parallel copies (parallelCopies, shown as "Degree of copy parallelism" in the UI) decide how many threads read from the source or write to the sink at the same time. They are independent settings, and by default the service chooses both automatically.

How it works:

• Between file stores, parallelism works at the file level. The run never uses more parallel copies than the number of files.
• From partition-enabled databases (SQL Server, Azure SQL, Oracle, Teradata and others) the default parallel copy is 4, and it cannot exceed the number of partitions.
• With MergeFiles copy behaviour, file-level parallelism is not used.
• Lowering parallelCopies is the standard way to reduce load on a busy source database.

Example: A Copy activity from Oracle uses dynamic range partitioning on ORDER_ID. You set degree of copy parallelism to 8 so eight queries pull eight ranges in parallel.

"typeProperties": {
  "source": { "type": "OracleSource", "partitionOption": "DynamicRange" },
  "sink": { "type": "ParquetSink" },
  "parallelCopies": 8
}

Interview tip: Explain that parallel copy without a source partition option often has no effect for database sources.

Q59. What is staged copy and when do you use it?

Answer: Staged copy first writes data to an interim Azure Blob or ADLS Gen2 location, then loads it from there into the sink. The Copy activity manages both steps and cleans up the staging data automatically.

How it works:

• You set enableStaging: true and give a stagingSettings linked service and path.
• It is used to load Azure Synapse through PolyBase or the COPY statement when the source is not directly supported, and for Snowflake, Amazon Redshift and HDFS scenarios.
• It helps when your firewall allows only ports 80 and 443. The SHIR writes to staging over HTTPS, so port 1433 does not need to be opened for Azure SQL or Synapse.
• It can compress data before it crosses a slow network link.
• ADF needs delete permission on the staging storage for cleanup.

Example: The sink settings for Oracle to Synapse with staging:

"enableStaging": true,
"stagingSettings": {
  "linkedServiceName": { "referenceName": "LS_Staging_ADLS", "type": "LinkedServiceReference" },
  "path": "staging/oracle"
}

Interview tip: Mention that staged copy is billed for both stages, so use it for a reason, not by default.

Q60. How do you load data into Azure Synapse dedicated SQL pool with the Copy activity?

Answer: The Copy activity can load Synapse using the COPY statement, PolyBase or bulk insert, and Microsoft recommends COPY or PolyBase for better performance. You pick one with allowCopyCommand or allowPolyBase; both cannot be true together.

How it works:

• Direct copy: if the source is Azure Blob or ADLS Gen2 and the format is supported (for example Parquet or delimited text), Synapse pulls the data itself.
• Staged copy: for any other source, ADF converts data into a compatible format in staging storage, then runs COPY or PolyBase.
• Bulk insert: the fallback, which is slower for large volumes.
• The sink also supports writeBehavior: Upsert with key columns, using an interim table.

Example: Loading Parquet files from ADLS Gen2 with the COPY statement:

"sink": {
  "type": "SqlDWSink",
  "allowCopyCommand": true,
  "tableOption": "autoCreate"
}

Interview tip: Interviewers like hearing that COPY needs fewer permissions and setup than PolyBase, so most new projects choose COPY.

Q61. How does fault tolerance work in the Copy activity?

Answer: By default, the Copy activity fails on the first bad row, but you can turn on fault tolerance to skip incompatible rows and log them. For binary file copies, you can instead skip missing, forbidden or inconsistent files.

How it works:

• enableSkipIncompatibleRow: true skips rows with type conversion errors, column count mismatches, or primary key violations on SQL Server, Azure SQL Database or Cosmos DB.
• logSettings writes a session log (CSV) to Blob or ADLS Gen2 with timestamp, level, operation name, the skipped item and the reason.
• The output shows rowsCopied, rowsSkipped and logFilePath.
• For binary copy, skipErrorFile has flags such as fileMissing, fileForbidden, dataInconsistency and invalidFileName.
• Skipping does not work when the sink uses upsert or a stored procedure.

Example: A CSV file with abc in an INT column. With skipping on, the good rows load, and the bad row lands in sessionlog/copyactivity-logs/... with the error "Cannot convert".

"enableSkipIncompatibleRow": true,
"logSettings": {
  "enableCopyActivityLog": true,
  "copyActivityLogSettings": { "logLevel": "Warning", "enableReliableLogging": false },
  "logLocationSettings": { "linkedServiceName": { "referenceName": "LS_ADLS", "type": "LinkedServiceReference" }, "path": "sessionlog/" }
}

Interview tip: Add that you then alert when rowsSkipped is above zero, so skipped data is never silent.

Q62. How do schema mapping and data type mapping work in the Copy activity?

Answer: The Copy activity maps source columns to sink columns by name by default, and you can define explicit mappings in the Mapping tab using a TabularTranslator. Data types are converted from the source type to an interim type and then to the sink type.

How it works:

• Default mapping: column names are matched case-sensitively; for text files without headers, columns are mapped by position.
• Explicit mapping: use it when names differ, when you want only some columns, or when you flatten JSON with a collection reference.
• Dynamic mapping: you can pass the whole translator JSON as a parameter, which is how metadata-driven frameworks handle hundreds of tables.
• Type conversion settings control things like date formats, culture and whether truncation is allowed.
• Auto create table creates a missing sink table in Azure SQL, SQL MI, Synapse or SQL Server.

Example: The source has cust_nm and the sink has CustomerName:

"translator": {
  "type": "TabularTranslator",
  "mappings": [
    { "source": { "name": "cust_nm" }, "sink": { "name": "CustomerName" } },
    { "source": { "name": "cust_id" }, "sink": { "name": "CustomerId" } }
  ]
}

Interview tip: Mention that storing mappings in a control table and passing them as @json(item().mapping) makes the framework reusable.

Q63. What are the copy behaviour options for file sinks?

Answer: The copyBehavior property on a file sink decides how files from a source folder are written: PreserveHierarchy (default), FlattenHierarchy or MergeFiles. It applies when the source is a file-based store.

How it works:

• The recursive setting on the source decides whether subfolders are read at all.
• MergeFiles cannot use file-level parallelism, so it is slower on large folders.

Example: A vendor drops 300 small CSV files per day in nested folders. To load one daily file into the Bronze layer, you set recursive to true and copyBehavior to MergeFiles, with the sink file name sales_@{formatDateTime(utcnow(),'yyyyMMdd')}.csv.

Interview tip: Say you use PreserveHierarchy for lake migrations, because it keeps lineage clear and allows resume from the last failed run.

Q64. When do you use a binary copy, and what extra features does it give?

Answer: Use a binary copy when you want to move files as-is without reading their content, for example zip files, images, PDFs or a whole lake migration. Both source and sink datasets must be Binary format, and the service skips parsing, which makes it fast.

How it works:

• Resume from last failed run: with binary copy and PreserveHierarchy between file stores, a retry or "rerun from failed activity" continues at file level instead of starting again.
• Delete after copy: deleteFilesAfterCompletion deletes each source file once it is copied, giving a simple "move" pattern.
• Fault tolerance: skip files that are deleted, forbidden or inconsistent during the copy.
• Data consistency verification: checks file size and checksum between source and sink.
• Preserve metadata and ACLs for lake-to-lake migrations.
• Row counts are not reported, because rows are never parsed.

Example: You migrate 10 TB of raw logs from Amazon S3 to ADLS Gen2. A failure at 70% is retried, and the copy resumes from the failed file instead of the first file.

Interview tip: Point out that you cannot apply column mapping or filter rows in a binary copy, because the content is never read.

Q65. How does the Copy activity handle compressed files?

Answer: The Copy activity can compress or decompress data on the fly, based on the compression setting in the source and sink datasets. You can read gzip CSV and write uncompressed rows to SQL, or unzip archives while copying.

How it works:

• Compression is set on the dataset, for example compressionCodec of gzip, bzip2, deflate, ZipDeflate, TarGZip, Tar, snappy or lz4, depending on the format.
• If both datasets are Binary with the same settings, files are copied as-is, which is fastest.
• For ZipDeflate sources, an option controls whether the zip file name is kept as a folder in the target.
• Staged copy has its own enableCompression setting, which compresses data before it moves over a slow hybrid link.
• Parquet files are already compressed internally (snappy by default), so extra gzip adds little.

Example: An on-premises file share holds sales_2026.zip. The source Binary dataset sets compression to ZipDeflate, the sink Binary dataset has no compression, so the files inside the zip land unzipped in ADLS Gen2.

Interview tip: Mention that you should prefer Parquet over gzip CSV in the lake, because gzip files cannot be split for parallel reads.

Q66. How do you do an upsert with the Copy activity?

Answer: For Azure SQL Database, SQL Server, SQL MI and Synapse sinks, set writeBehavior to Upsert and give the key columns. ADF loads the batch into an interim table and then merges it into the target table.

How it works:

• upsertSettings.keys lists the business keys. If you leave it empty, the primary key is used.
• For Azure SQL, useTempDB: true (the default) uses a global temporary table as the interim table. With false, a physical table is created in interimSchemaName, which needs create and drop permission.
• The older option is a stored procedure sink (sqlWriterStoredProcedureName) with a table type, which is invoked per batch and lets you write custom MERGE logic.
• Fault tolerance (skip rows) does not work with upsert.

Example: Upserting customers by CustomerId:

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

Interview tip: Mention that upsert does not handle deletes, so deletes need a separate step or a mapping data flow with Alter Row.

Q67. Give a performance tuning checklist for a slow Copy activity.

Answer: First read the copy monitoring details to find the stage that takes longest, then tune that stage. Common fixes are source partitioning, more DIUs or SHIR nodes, a better file format, and removing sink throttling.

How it works:

1. Check the bottleneck: queue time points to IR capacity; long "reading from source" points to the source; long "writing to sink" points to the sink.
2. Partition the source: use physical partitions or dynamic range for SQL Server, Azure SQL, Oracle, Teradata and others.
3. Scale compute: raise DIUs on Azure IR; on SHIR scale up concurrent jobs or scale out to more nodes.
4. Fix file layout: avoid millions of tiny files; prefer Parquet.
5. Sink side: use COPY or PolyBase for Synapse; scale up Azure SQL during loads; avoid heavy indexes during bulk load.
6. Location: run the Azure IR in the same region as the data.
7. Read the "Performance tuning tips" shown in the monitoring view.

Example: A large Oracle table copies slowly through one SHIR node, and the details view shows most time in "reading from source". Adding dynamic range partitioning on the primary key, a second SHIR node and Parquet output removes that bottleneck.

Interview tip: Say "I tune based on the stage durations, not by guessing", which is exactly what interviewers want.

Q68. Which Copy activity monitoring metrics do you check?

Answer: The Copy activity output and the details (eyeglasses) view show data read and written, rows copied and skipped, throughput, used DIUs, used parallel copies, the effective IR, and stage durations. These numbers tell you if the copy was correct and where time was spent.

How it works:

• Volume: dataRead, dataWritten, filesRead, filesWritten, rowsRead, rowsCopied, rowsSkipped.
• Speed: copyDuration in seconds and throughput in KBps (data read divided by duration).
• Resources: usedDataIntegrationUnits, usedParallelCopies, effectiveIntegrationRuntime, sourcePeakConnections, sinkPeakConnections.
• Stages: executionDetails shows queue and transfer durations, with reading-from-source and writing-to-sink details. durationInQueue shows waiting time before the copy started.
• perfRecommendation: built-in tuning tips such as sink throttling warnings.

Example: You validate a load by comparing rowsRead and rowsCopied with an expression, and log both to an audit table:

@activity('Copy_Orders').output.rowsCopied
@activity('Copy_Orders').output.rowsRead
@activity('Copy_Orders').output.copyDuration

Interview tip: Mention that Microsoft advises not to parse executionDetails in code, because its structure can change.

7. Mapping data flows

Q69. What are mapping data flows in Azure Data Factory?

Answer: Mapping data flows are visually designed data transformations that ADF runs on managed Apache Spark clusters. You build the logic with drag-and-drop transformations, and ADF converts it into Spark code, so you get scale without writing Spark yourself.

How it works:

• A data flow has one or more sources, a chain of transformations and one or more sinks.
• It runs from a pipeline through the Data Flow activity, on an Azure integration runtime (not on a self-hosted IR).
• Each run spins up a Spark cluster unless a warm cluster is available through time to live (TTL).
• You can parameterize data flows and pass values from the pipeline.
• Expressions use the data flow expression language, which is different from the pipeline expression language.

Example: A data flow reads raw orders from ADLS Gen2, removes duplicates, joins with the customer dimension, adds a LoadDate column with currentUTC(), and writes a Delta table in the Silver layer.

Interview tip: Stress that data flow expressions (toInteger(Amount)) and pipeline expressions (@pipeline().parameters.x) are two separate languages; many candidates mix them up.

Q70. When do you use a mapping data flow instead of a Copy activity?

Answer: Use the Copy activity to move data with little or no change, and use a mapping data flow when you need real transformations such as joins, aggregations, deduplication, SCD logic or row-level updates. Copy is cheaper and faster for plain movement.

How it works:

• ADF also has the Power Query activity (earlier called wrangling data flow), which runs Power Query M scripts on Spark for data prep.

Example: You first copy SQL Server tables from on-premises to ADLS Gen2 with a Copy activity, then run a data flow on the lake files to build cleaned Silver tables.

Interview tip: Say that data flows cannot read on-premises sources through a SHIR, so a "Copy to lake, then transform" pattern is normal.

Q71. List the main transformations in mapping data flows.

Answer: Mapping data flows offer schema modifiers, row modifiers, multi-input transformations and formatters. The ones asked most in interviews are Derived Column, Aggregate, Join, Lookup, Exists, Conditional Split, Window, Pivot, Unpivot, Flatten and Alter Row.

How it works:

Example: A typical Silver flow is Source, Filter (remove test orders), Derived Column (clean names), Lookup (add region), Aggregate (daily totals), Sink.

Interview tip: Name the category of each transformation; it shows you understand the design, not just the list.

Q72. What is the difference between Join, Lookup and Exists transformations?

Answer: Join combines two streams using inner, left outer, right outer, full outer or cross join. Lookup is a left outer join that adds columns from a reference stream. Exists only checks whether matching rows exist (or do not exist) and adds no columns.

How it works:

• Join: returns columns from both streams; you choose the join type and condition.
• Lookup: keeps every row from the primary stream; unmatched rows get NULLs. It has a "match multiple rows" option and you can use isMatch() to check if a row found a match.
• Exists: works like SQL WHERE EXISTS or NOT EXISTS. Only the left stream's columns pass through.
• For small reference data, the Broadcast option on the Optimize tab sends the small side to every node and avoids a shuffle.

Example: For new-customer detection, use Exists with "Doesn't exist" between the incoming customers stream and the existing dimension. Only brand-new customers pass to the insert sink.

Interview tip: Mention broadcast joins for small dimensions; it is the most common data flow performance fix.

Q73. How do you remove duplicates in a mapping data flow?

Answer: Two common ways: an Aggregate transformation grouped by the key with first() or last() for the other columns, or a Window transformation with rowNumber() followed by a Filter that keeps row 1. The Window approach gives you control over which duplicate to keep.

How it works:

• Window method: set "Over" to the business key, "Sort" to LastModified descending, add a column rn = rowNumber(), then Filter rn == 1.
• Aggregate method: group by the key and use a column pattern so all other columns take first($).
• Full-row duplicates: add a Derived Column with sha2(256, columns()) as a row hash and group by it.

Example: Keep the latest record per customer using the Window method:

Window: over = CustomerId
         sort = LastModified (descending)
         column rn = rowNumber()
Filter: rn == 1

Interview tip: Say that you sort by a reliable timestamp, otherwise "latest" is random when duplicates arrive in the same file.

Q74. Explain Conditional Split, Pivot, Unpivot and Flatten with use cases.

Answer: Conditional Split routes rows to different streams based on conditions. Pivot turns row values into columns, Unpivot turns columns into rows, and Flatten unrolls arrays inside JSON into separate rows.

How it works:

• Conditional Split: you can send rows to the first matching condition or to all matching conditions, plus a default stream. It is used for data quality routing.
• Pivot: group by columns, choose a pivot key and an aggregate. Pivoted column names are created dynamically, so downstream mapping often needs rule-based mapping.
• Unpivot: the reverse; useful when a source has one column per month.
• Flatten: choose the array to "unroll by"; each array element becomes a row while parent fields repeat.

Example: Splitting good and bad rows:

ConditionalSplit:
  BadRows : isNull(CustomerId) || Amount < 0
  GoodRows : (default)

Good rows go to the Silver table and bad rows go to a quarantine folder with the reason.

Interview tip: Mention a quarantine sink for bad rows; it shows real production thinking.

Q75. What does the Alter Row transformation do?

Answer: Alter Row marks each row with a policy (insert, update, upsert or delete) based on conditions, and the sink applies those actions. It is how data flows perform MERGE-style writes to databases and Delta tables.

How it works:

• Conditions use functions like insertIf(), updateIf(), upsertIf() and deleteIf(), evaluated in order.
• The sink must allow the matching methods (Allow insert, Allow update, Allow upsert, Allow delete) and define key columns.
• Supported sinks include Azure SQL Database, Synapse, Cosmos DB and Delta (inline dataset).
• For Delta sinks, selecting update methods makes ADF run a merge using the policies.
• Without Alter Row, the default policy is insert.

Example: Soft-deleted records in the source have IsDeleted = 1:

AlterRow:
  deleteIf(IsDeleted == 1)
  upsertIf(true())
Sink (Delta): keys = [OrderId], allow delete = true, allow upsert = true

Interview tip: Say the sink settings must match the Alter Row policies, otherwise those rows are silently ignored.

Q76. What is schema drift and how do data flows handle it?

Answer: Schema drift means source columns change over time: new columns appear, some disappear or types change. Data flows handle it with the "Allow schema drift" option on sources and sinks, plus pattern-based logic instead of hard-coded column names.

How it works:

• Source: with schema drift on, columns not in the dataset projection still flow through. They arrive as strings unless you tick Infer drifted column types.
• Sink: with schema drift and Auto-mapping on, all incoming columns are written, including new ones.
• Referencing drifted columns: use byName('col') or byPosition(n), column patterns in Derived Column or Aggregate, and rule-based mapping in Select or Sink.
• Map Drifted quick action in data preview creates a Derived Column such as toInteger(byName('movieId')).
• Drifted flows are late-binding, so drifted columns do not appear in design-time schema views.

Example: A vendor adds a discount_pct column. With drift enabled and Delta "Merge schema" on in the sink, the new column is added to the Silver table without a code change.

Interview tip: Mention that you still alert on schema changes; accepting drift silently can break reports downstream.

Q77. What is data flow debug mode and what is TTL?

Answer: Debug mode starts a live Spark cluster so you can preview data at every transformation while you build. TTL (time to live) keeps a cluster warm for a set time after a job, so the next data flow starts faster.

How it works:

• Turning on Data flow debug with the AutoResolve IR starts an 8-core general purpose cluster with a default 60-minute TTL.
• Each user's debug session gets its own cluster, and you pay for every hour it runs, including TTL time.
• Debug settings let you set row limits and sample files per source. Debug preview does not write to sinks.
• For pipeline runs, TTL is set on a custom Azure IR. TTL is not available on the default AutoResolve IR.
• TTL helps sequential data flows. For data flows running in parallel, one live cluster serves only one job at a time, so others still start new clusters.

Example: A pipeline runs five data flows one after another. With a custom Azure IR and a 10-minute TTL, only the first one pays the cold start; the other four reuse the warm cluster.

Interview tip: Say you switch debug off when done, since forgotten debug sessions are a common surprise on the bill.

Q78. How do you tune mapping data flow performance?

Answer: Size the cluster correctly, use the Optimize tab to control partitioning, broadcast small join sides, and keep TTL on for sequential runs. Always check the data flow monitoring view to find the slow stage first.

How it works:

• Cluster size: default is small (4 driver plus 4 worker cores). Options go up to 256 worker cores with 16 driver cores. Memory optimized compute helps heavy joins and aggregations.
• Optimize tab: "Use current partitioning" is recommended in most cases. Set partitioning (round robin, hash, dynamic range, key) only when data is skewed or before a heavy join.
• Shuffle partitions: a custom IR property; the default is 200, and Microsoft suggests roughly 1.5 GB per partition when raising it.
• Sink: avoid "single partition" output unless a single file is truly needed; it forces all data through one node.
• Source: push filters into the source query and enable source partitioning for SQL.

Example: A join of 300 million orders with 50,000 products was slow. Broadcasting the small product stream removed the shuffle, and memory optimized compute stopped the spills, so the run became much faster.

Interview tip: Mention that more cores stop helping once you have more cores than data partitions.

Q79. How do you implement SCD Type 1 and Type 2 in mapping data flows?

Answer: SCD Type 1 overwrites changed attributes, so you use Alter Row with upsert on the business key. SCD Type 2 keeps history, so you expire the current row and insert a new version, which needs two streams from the same changed data.

How it works (SCD Type 2):

1. Source new data and the current dimension (IsCurrent = 1).
2. Add a row hash with sha2(256, Name, City, Segment) on both sides.
3. Lookup on the business key; Conditional Split into New (no match) and Changed (hash differs).
4. Changed goes to New Branch: one path sets IsCurrent = 0, EndDate = currentDate() with Alter Row updateIf(true()); the other inserts the new version.
5. New and changed versions get a Surrogate Key, StartDate = currentDate(), IsCurrent = 1.

Example: Customer 101 moves from Pune to Hyderabad. The old Pune row gets EndDate = 2026-10-19 and IsCurrent = 0, and a new Hyderabad row is inserted with IsCurrent = 1.

Interview tip: Mention the row hash; comparing every column one by one is slow and error-prone.

Q80. How is mapping data flow cost calculated, and when would you choose Databricks instead?

Answer: Data flows are billed per vCore-hour of the Spark cluster, based on compute type and size, for the cluster's run time including start-up and TTL. Choose Databricks when logic is complex, needs code, needs notebooks for data science, or when the team already runs a lakehouse on Databricks.

How it works:

• Cost tips for data flows: small cluster first, TTL only for sequential runs, combine small data flows into one, and turn off debug.

Example: A team with 20 simple cleansing flows keeps them in ADF data flows. A second team with complex PySpark rules and streaming uses Databricks, and ADF only orchestrates the jobs.

Interview tip: Avoid saying one is always better; tie the choice to team skills, logic complexity and cost.

8. Incremental loads and change data

Q81. How do you build a watermark-based incremental load in ADF?

Answer: Store the last loaded value (the watermark) in a control table, read it with a Lookup, copy only rows newer than it, and then update the watermark with a Stored Procedure activity. This loads only new or changed rows instead of the full table.

How it works:

1. Lookup (old watermark): read LastValue from etl.Watermark for the table.
2. Lookup (new watermark): read MAX(LastModified) from the source first, so rows arriving during the copy wait for the next run.
3. Copy activity: the source query filters between the two values.
4. Stored Procedure: update the control table with the new watermark, only on the success path.

Example: The Copy source query built with string interpolation:

SELECT * FROM dbo.Orders
WHERE LastModified > '@{activity('LkpOld').output.firstRow.LastValue}'
  AND LastModified <= '@{activity('LkpNew').output.firstRow.NewValue}'

The final step calls etl.usp_UpdateWatermark with @{activity('LkpNew').output.firstRow.NewValue}.

Interview tip: Explain why you capture the new watermark before copying; using GETDATE() after the copy can miss rows that arrived mid-run.

Q82. How do you load only new or changed files from a data lake?

Answer: Use the "Filter by last modified" setting on the Copy activity source (modifiedDatetimeStart and modifiedDatetimeEnd), passing a time window from the trigger or a watermark. A file is selected when its last modified time is greater than or equal to the start and less than the end, in UTC.

How it works:

• If only the start is set, all files modified at or after it are picked.
• A tumbling window trigger gives clean, non-overlapping windows through @trigger().outputs.windowStartTime and @trigger().outputs.windowEndTime.
• On very large folders, the filter still has to list every file, so a date-partitioned folder path (yyyy/MM/dd) is faster.
• A storage event trigger is the other option: it runs per new file and passes @triggerBody().fileName and @triggerBody().folderPath.

Example: Copy source store settings in a tumbling window pipeline:

"storeSettings": {
  "type": "AzureBlobFSReadSettings",
  "recursive": true,
  "wildcardFileName": "*.csv",
  "modifiedDatetimeStart": "@{pipeline().parameters.WindowStart}",
  "modifiedDatetimeEnd": "@{pipeline().parameters.WindowEnd}"
}

Interview tip: Mention the start-inclusive, end-exclusive rule; it is why back-to-back windows never load the same file twice.

Q83. How do you use SQL Server change tracking with ADF?

Answer: Change tracking records which rows changed (insert, update, delete) since a version number, without storing old values. ADF stores the last synced version, queries CHANGETABLE(CHANGES ...) to get the changed keys, copies those rows, and then saves the new version.

How it works:

1. Enable change tracking on the database and on the table.
2. Lookup the last synced version from a control table, and the current version with CHANGE_TRACKING_CURRENT_VERSION().
3. Copy rows by joining CHANGETABLE to the base table. SYS_CHANGE_OPERATION tells you I, U or D.
4. Update the control table with the current version after a successful copy.

Example: The Copy source query:

SELECT CT.OrderId, CT.SYS_CHANGE_OPERATION, O.Amount, O.Status
FROM CHANGETABLE(CHANGES dbo.Orders, @{activity('LkpLastVersion').output.firstRow.LastVersion}) AS CT
LEFT JOIN dbo.Orders AS O ON O.OrderId = CT.OrderId;

The LEFT JOIN keeps deleted rows, which no longer exist in the base table.

Interview tip: Mention the retention period: if your pipeline is down longer than the change tracking retention, you must do a full reload.

Q84. How do you consume SQL Server CDC (change data capture) with ADF?

Answer: SQL CDC reads the transaction log and stores changed rows with their column values in change tables. In ADF you query the CDC functions for a log sequence number (LSN) range in a Copy activity, or use the change data capture option on a mapping data flow source for supported SQL sources.

How it works:

• Enable CDC with sys.sp_cdc_enable_db and sys.sp_cdc_enable_table.
• Map your time window to LSNs with sys.fn_cdc_map_time_to_lsn.
• Use cdc.fn_cdc_get_net_changes_<capture_instance> for the final state per key, or cdc.fn_cdc_get_all_changes_<capture_instance> for every change.
• The __$operation column gives 1 = delete, 2 = insert, 4 = update (after image).
• A mapping data flow source with CDC enabled keeps its own checkpoint between runs.

Example: A Copy source query for one window:

DECLARE @from binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than', '@{pipeline().parameters.WindowStart}');
DECLARE @to binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '@{pipeline().parameters.WindowEnd}');
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@from, @to, 'all');

Interview tip: Contrast it with change tracking: CDC gives the changed column values, change tracking gives only the keys and the operation.

Q85. What is the Change Data Capture resource in ADF?

Answer: It is a top-level ADF resource (next to pipelines and data flows) that continuously moves changed data from a source to a target through a guided setup, without building pipelines or triggers. Microsoft Learn still marks it as public preview, so check its status before you depend on it in production.

How it works:

• Sources include Azure SQL Database, SQL Managed Instance, SQL Server, Azure Cosmos DB (SQL API), Snowflake, and file formats such as Parquet, Delimited Text, JSON, Avro, ORC and XML.
• Targets include Azure SQL Database, SQL Managed Instance, Azure Synapse Analytics, Delta and file formats.
• While it runs, it is billed as 4 vCores of General Purpose data flow compute.
• Known limits: no self-hosted IR, no complex types, and each source or target can be used only once in the mappings.
• Synapse as a target needs staging, and the load uses COPY INTO.

Example: A team needs near real-time sync of 15 Azure SQL tables into Delta tables in ADLS Gen2. They create one CDC resource, map the tables and start it, with no pipeline code to maintain.

Interview tip: Mention the SHIR limitation; on-premises sources behind a firewall still need a pipeline-based design.

Q86. Why is a tumbling window trigger good for incremental loads?

Answer: A tumbling window trigger fires for fixed, non-overlapping, back-to-back time windows and passes each window's start and end time to the pipeline. Each window keeps its own state, so you can retry, rerun or backfill one specific window.

How it works:

• Pass @trigger().outputs.windowStartTime and @trigger().outputs.windowEndTime as pipeline parameters.
• Set a start date in the past to backfill history automatically, window by window.
• maxConcurrency controls how many windows run in parallel during a backfill.
• A retry policy and dependencies on other tumbling window triggers (including a self-dependency) keep loads in order.
• A tumbling window trigger is linked to a single pipeline.

Example: An hourly trigger with a start date of 1 October 2026 creates one run for each hour since then. The pipeline loads only rows where LastModified >= WindowStart AND LastModified < WindowEnd, and a failed hour can be rerun on its own.

Interview tip: Say that a schedule trigger keeps no window state, so it cannot backfill or rerun one window the same way.

Q87. How do you detect deletes in an incremental load?

Answer: A watermark on LastModified never sees hard deletes, so you need another method: change tracking or CDC, soft-delete flags in the source, or a periodic key comparison between source and target.

How it works:

• CDC or change tracking: deletes arrive as operation D, or __$operation = 1. This is the cleanest option.
• Soft deletes: the source app sets IsDeleted = 1 and updates LastModified, so the normal watermark picks it up.
• Key comparison: copy only the primary keys from the source (cheap), then find target keys that are missing. In a data flow, use Exists with "Doesn't exist" and Alter Row deleteIf(true()).
• Full reload for small tables: truncate and reload can be simpler than tracking deletes.

Example: A nightly job copies all OrderId values from the source into stg.OrderKeys, then runs:

UPDATE t SET t.IsDeleted = 1, t.DeletedOn = SYSUTCDATETIME()
FROM dw.Orders AS t
WHERE NOT EXISTS (SELECT 1 FROM stg.OrderKeys AS s WHERE s.OrderId = t.OrderId);

Interview tip: Say you prefer soft deletes in the target, so reports and audits can still see what was removed and when.

Q88. What is the difference between an upsert and a merge into Delta Lake from ADF?

Answer: An upsert inserts new keys and updates existing ones; a merge can also delete and apply different rules per row. In ADF, the Copy activity supports upsert into SQL sinks, while a merge into Delta is done with a mapping data flow (Alter Row plus a Delta sink) or a Databricks notebook running MERGE INTO.

How it works:

• Copy upsert: writeBehavior: upsert with key columns, for Azure SQL, SQL Server, SQL MI and Synapse.
• Data flow to Delta: Delta is an inline dataset. When you select update methods and key columns, ADF merges using the Alter Row policies. Options include merge schema, vacuum, optimized write and auto compact.
• Databricks: the most flexible option, with full Delta SQL, schema evolution and Unity Catalog governance.

Example: The Databricks notebook that ADF calls:

MERGE INTO silver.orders AS t
USING bronze_orders_updates AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.is_deleted = 1 THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Interview tip: Mention deduplicating the source by key before a merge; duplicate source keys make a Delta MERGE fail.

Q89. How do you make ADF pipelines idempotent so reruns do not create duplicates?

Answer: An idempotent pipeline gives the same result no matter how many times you run it for the same window. You get this by writing to fixed, window-based locations, deleting or overwriting the window before loading, or using upsert or merge on business keys.

How it works:

• Files: write to a path built from the window, such as sales/2026/10/20/, and overwrite it on rerun, instead of appending files with a run timestamp in the name.
• SQL append loads: use a pre-copy script that first deletes the same window.
• Keyed tables: use upsert or MERGE rather than plain insert.
• Watermarks: update the watermark only after a successful load.
• Audit: store the pipeline run ID with each row, so you can trace and clean up a bad load.

Example: A pre-copy script on the Azure SQL sink:

DELETE FROM dw.DailySales
WHERE SalesDate = '@{formatDateTime(pipeline().parameters.WindowStart, 'yyyy-MM-dd')}';

Interview tip: Say "every load must be safe to rerun" early in any scenario answer; it is one of the strongest signals of real experience.

Q90. How do you handle late-arriving data in ADF incremental loads?

Answer: Late data is a record that arrives after its time window was already processed. You handle it by filtering on load time instead of event time, by re-reading a look-back window with upserts, or by reprocessing the affected windows.

How it works:

• Watermark on load time: filter on LoadedAt or the file's last modified time, not on the business date, so nothing is missed.
• Look-back window: each run re-reads the last few hours or days and upserts, so late rows are merged in. This is safe only with idempotent writes.
• Tumbling window delay: the trigger's delay property starts each window later, giving data time to arrive.
• Rerun windows: rerun specific tumbling windows when a source team reports late data.
• Late-arriving dimensions: insert a placeholder "unknown" member and update it when the real record arrives.

Example: A retail chain's store systems upload files several hours late. The hourly pipeline selects files by last modified time, and a nightly job re-aggregates the previous day's sales with MERGE, so the daily totals end up correct.

Interview tip: Always ask "is the watermark based on event time or load time?"; that one question shows senior thinking.

9. Error handling, monitoring and alerts

Q91. How do activity retry and timeout settings work in ADF?

Answer: Execution activities have a policy with timeout, retry and retryIntervalInSeconds. By default, the timeout is 12 hours, retry is 0 and the retry interval is 30 seconds, so you should set them for every important activity.

How it works:

• Retry handles short-lived problems such as network blips, throttling or a busy database.
• Timeout stops an activity that hangs, so the pipeline fails fast instead of blocking the next schedule.
• Policies apply to execution activities (Copy, Lookup, Stored Procedure, Data Flow, Web and others), not to control activities like ForEach or If Condition.
• secureInput and secureOutput hide sensitive values from monitoring logs.
• A binary Copy activity retry can resume from the file that failed.

Example: A typical Copy activity policy (timeout format is d.hh:mm:ss):

"policy": {
  "timeout": "0.02:00:00",
  "retry": 2,
  "retryIntervalInSeconds": 120,
  "secureOutput": false
}

Interview tip: Mention that retries must be paired with idempotent writes; otherwise a retry can load the same data twice.

Q92. How do you implement a try-catch pattern in an ADF pipeline?

Answer: Connect an error-handling activity to the main activity's Upon Failure path only. If the main activity fails, the handler runs, and if the handler succeeds, the pipeline itself shows Succeeded.

How it works:

• ADF has four dependency paths: Upon Success, Upon Failure, Upon Completion and Upon Skip.
• Try-Catch: only an Upon Failure path. The pipeline shows success in both cases.
• Do-If-Else: both Upon Success and Upon Failure paths. The pipeline shows failure when the main activity fails, even if the handler worked.
• Do-If-Skip-Else: Upon Success and Upon Failure, plus a dummy activity on Upon Skip. The pipeline shows success.
• Upon Completion cannot be combined with Upon Success or Upon Failure from the same activity.

Example: Copy_Sales has a Stored Procedure usp_LogError on its Upon Failure path. It logs @activity('Copy_Sales').error.message, and then a Fail activity re-raises the error so the run is marked Failed and the alerts fire.

Interview tip: Point out the trap: a pure try-catch hides failures from monitoring, so add a Fail activity after logging when the run must show as failed.

Q93. How does ADF decide whether a pipeline run succeeded or failed?

Answer: ADF looks at the leaf activities, meaning activities with nothing after them. If a leaf activity was skipped, it evaluates that activity's parent instead. The pipeline succeeds only if all evaluated nodes succeeded.

How it works:

• In a try-catch, when the main activity succeeds, the failure handler is skipped, so ADF checks its parent (which succeeded), and the pipeline succeeds.
• When the main activity fails and the handler succeeds, the leaf (the handler) succeeded, so the pipeline succeeds.
• In Do-If-Else, when the main activity fails, the success branch is skipped and its parent failed, so the pipeline fails.
• Pipeline statuses in Monitor are Queued, In Progress, Succeeded, Failed and Canceled.

Example: Outcome by design pattern:

Interview tip: This "leaf activity" rule is a favourite trick question; explaining it with this table usually earns full marks.

Q94. What is the Fail activity and how do you raise custom errors?

Answer: The Fail activity deliberately fails the pipeline with your own error message and error code. Use it when a business rule breaks, or to re-raise an error after logging it in a try-catch.

How it works:

• It has two properties, message and errorCode, and both accept dynamic content.
• The pipeline run becomes Failed, and the custom message appears in Monitor, alerts and Log Analytics.
• It is usually placed inside an If Condition (for example, zero rows copied) or at the end of a failure path.
• It needs no linked service or compute.

Example: Fail when the source has no rows:

{
  "name": "Fail_NoData",
  "type": "Fail",
  "typeProperties": {
    "message": "@concat('No rows copied for ', pipeline().parameters.TableName)",
    "errorCode": "DQ_EMPTY_SOURCE"
  }
}

Interview tip: Mention clear, consistent error codes, because support teams and alert rules can filter on them.

Q95. How do you log pipeline runs to a SQL audit table?

Answer: Add Stored Procedure or Script activities at the start and end of each pipeline that write run details (pipeline name, run ID, status, row counts, errors and timings) to an audit table. This keeps history beyond ADF's 45-day run retention and supports SLA reports.

How it works:

• Start step: insert a row with @pipeline().Pipeline, @pipeline().RunId, @pipeline().TriggerTime and status "Running".
• Success path: update the row with rowsRead, rowsCopied and copyDuration from the Copy output.
• Failure path: update the row with @activity('Copy_Table').error.message and status "Failed".
• Use the Upon Completion path or the generic error-handling pattern so logging always runs.

Example: Stored procedure parameters on the success step:

PipelineName : @pipeline().Pipeline
RunId : @pipeline().RunId
TableName : @item().TableName
RowsCopied : @activity('Copy_Table').output.rowsCopied
Status : Succeeded

Interview tip: Add that the audit table sits next to the control table, so the framework can read past runs and decide what to rerun.

Q96. How do you set up alerts and metrics for ADF in Azure Monitor?

Answer: ADF sends platform metrics to Azure Monitor automatically, and you create alert rules on them, such as "Failed pipeline runs greater than 0". The alert uses an action group to send email, SMS, push or voice notifications, or to call a webhook, Logic App or Azure Function.

How it works:

• Metrics include succeeded and failed pipeline, activity and trigger runs, integration runtime CPU, memory and queue, SSIS metrics, and factory size and entity count.
• Alert logic can be filtered by pipeline name, activity type, activity name or failure type.
• Metric alerts are near real-time; log alerts run KQL queries on Log Analytics on a schedule.
• You can create alerts from Data Factory Studio (Monitor, then Alerts and metrics) or from the Azure portal.

Example: A typical set of production alerts:

Interview tip: Mention action groups; it shows you know how alerts actually reach the on-call team.

Q97. How do you query ADF runs in Log Analytics with KQL?

Answer: Create a diagnostic setting on the factory that sends the PipelineRuns, ActivityRuns and TriggerRuns log categories to a Log Analytics workspace in Resource specific mode. The data lands in tables such as ADFPipelineRun, ADFActivityRun and ADFTriggerRun, which you query with KQL.

How it works:

• Several factories can send logs to one workspace for a central view.
• Useful columns include PipelineName, RunId, Status, Start, End, FailureType, ErrorCode and ErrorMessage.
• A run writes a row for each status change (Queued, InProgress, then final), so filter on final statuses.
• The same queries feed workbooks, Grafana, Power BI and log alerts.

Example: Failed activities in the last 24 hours with their error text:

ADFActivityRun
| where TimeGenerated > ago(24h)
| where Status == "Failed"
| project TimeGenerated, PipelineName, ActivityName, ActivityType, ErrorCode, ErrorMessage
| order by TimeGenerated desc

Interview tip: Mention Resource specific mode; the older AzureDiagnostics mode puts everything into one wide table that is harder to query.

Q98. How long does ADF keep pipeline run history, and how do you keep it longer?

Answer: Data Factory stores pipeline run data for only 45 days. To keep it longer, route diagnostic logs through Azure Monitor diagnostic settings to a Log Analytics workspace, a storage account or Event Hubs.

How it works:

• Log Analytics: best for queries, alerts and dashboards; retention is set on the workspace.
• Storage account: a low-cost long-term archive for audit and compliance.
• Event Hubs: streams logs to a SIEM or another monitoring tool.
• Your own SQL audit table adds business details such as row counts and source tables.
• The storage account or event hub can be in another subscription if you have RBAC access to both.

Example: A bank must keep job run evidence for several years. The team sends ADF logs to Log Analytics for active querying and to an archive storage account for long-term retention.

Interview tip: If asked "where did last quarter's failed run go?", say it is gone from ADF Monitor after 45 days unless diagnostic settings were configured.

Q99. What rerun options does ADF provide for failed pipelines?

Answer: In the Monitor hub you can Rerun the whole pipeline, Rerun from failed activity, or Rerun from activity (start from any activity you pick). You can also rerun with new parameters, which creates a new run rather than a rerun of the old one.

How it works:

• Rerun from failed activity restarts at the activity that failed, timed out or was canceled; earlier successful activities are skipped.
• ForEach and Until loops run again, but their inner activities may be skipped based on rerun rules.
• Execute Pipeline triggers the child again, and its inner activities follow the same rules.
• A binary Copy activity rerun can resume from the failed file.
• The run list groups runs as Original, Rerun and Rerun (Latest).
• For tumbling window triggers, you can rerun a specific window.

Example: A 10-step pipeline fails at step 7 because the target database was paused. After resuming the database, you choose "Rerun from failed activity", and steps 1 to 6 are not repeated.

Interview tip: Add that this is safe only if each step is idempotent; otherwise partial writes from step 7 can duplicate.

Q100. How do you send an email when an ADF pipeline fails?

Answer: ADF has no built-in email activity, so the common pattern is a Web activity on the failure path that posts error details to an Azure Logic App, which sends the email through the Office 365 Outlook or another mail connector. Azure Monitor alerts with an action group are the no-code alternative.

How it works:

• Create a Logic App with the "When an HTTP request is received" trigger and a send-email action.
• In ADF, add a Web activity (POST) on the Upon Failure path, with a JSON body of run details.
• Keep the Logic App URL in Key Vault or a global parameter, because it contains a signature that works like a password.
• For Microsoft Teams or Slack, the same Web activity can post to a workflow or incoming webhook.

Example: The Web activity body:

{
  "factory": "@{pipeline().DataFactory}",
  "pipeline": "@{pipeline().Pipeline}",
  "runId": "@{pipeline().RunId}",
  "error": "@{activity('Copy_Sales').error.message}"
}

Interview tip: Say you include the run ID and the error message in the email, so the on-call engineer can open the exact run quickly.

10. Security and networking

Q101. What is a managed identity in ADF, and how do system-assigned and user-assigned identities differ?

Answer: A managed identity is a Microsoft Entra ID identity for the data factory that lets it get tokens and access Azure resources without stored passwords or keys. A system-assigned identity is tied to one factory and deleted with it; a user-assigned identity is a separate Azure resource that you can attach to many factories.

How it works:

• The system-assigned identity is created automatically when you create a factory in the Azure portal or with PowerShell.
• You grant it roles on the target, for example Storage Blob Data Reader on ADLS Gen2, or a contained database user in Azure SQL.
• A user-assigned identity must first be added as a credential in the factory before linked services can use it.
• The trusted service bypass for Azure Storage and Key Vault works only with the system-assigned identity.
• Enabling customer-managed keys while creating a factory needs a user-assigned identity.

Example: Giving the factory identity access to Azure SQL:

CREATE USER [adf-prod-india] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [adf-prod-india];
ALTER ROLE db_datawriter ADD MEMBER [adf-prod-india];

Interview tip: Mention user-assigned identities for DR or rebuilt factories, since their permissions survive when a factory is recreated.

Q102. How do you use Azure Key Vault with ADF?

Answer: Create a Key Vault linked service that uses the factory's managed identity, then make other linked services reference secrets from it instead of storing passwords in ADF. Secrets are read at run time, so rotating a password in Key Vault needs no change in ADF.

How it works:

• Grant the factory identity the Key Vault Secrets User role (RBAC model) or Get and List secret permissions (access policy model).
• In a linked service, choose "Azure Key Vault" for the password or the full connection string, and give the secret name.
• Leave the secret version empty to always use the latest version.
• The secret name can be parameterized, so one linked service can serve many databases.
• If a Web activity reads a secret, set secureOutput so the value is not logged.

Example: A SQL Server linked service password from Key Vault:

"password": {
  "type": "AzureKeyVaultSecret",
  "store": { "referenceName": "LS_KeyVault", "type": "LinkedServiceReference" },
  "secretName": "onprem-sql-etl-password"
}

Interview tip: Stress that Key Vault keeps secrets out of Git and ARM templates, which is a basic CI/CD security requirement.

Q103. Which RBAC roles do you need to work with Azure Data Factory?

Answer: The ADF-specific built-in role is Data Factory Contributor, which lets users create, edit and delete factories and their child resources such as pipelines, datasets, linked services, triggers and integration runtimes. The general Contributor role also covers this, and Reader lets someone view and monitor without editing.

How it works:

• Creating a factory needs the Contributor or Owner role, or admin rights, on the subscription.
• Data Factory Contributor at resource group level also allows ARM template deployments, which is how publishing works.
• Git permissions are separate: a Reader with repo access can edit and commit in Git mode but cannot publish.
• Custom roles cover special needs, such as monitor-only users, or users who may only test connections and preview data.
• The factory's managed identity needs its own data-plane roles on each data store; those are not ADF roles.

Example: A typical role plan:

Interview tip: Mention least privilege in production: developers rarely get write access to the prod factory, because deployments go through CI/CD.

Q104. Explain managed virtual network, managed private endpoints and private endpoints in ADF.

Answer: A managed virtual network runs the Azure IR inside a Microsoft-managed VNet, and managed private endpoints from that VNet connect privately to your data stores. A private endpoint for the factory itself is different: it lets your own network reach ADF (the SHIR command channel or the Studio portal) privately.

How it works:

• Managed VNet IR: enabled at factory creation or when you create an Azure IR. An existing Azure IR cannot be switched to or from a managed VNet.
• Managed private endpoints: created in the Manage hub; the data store owner must approve the connection before traffic flows. They help prevent data exfiltration.
• Interactive authoring (test connection, data preview) on a managed VNet IR has a default TTL of 60 minutes.
• Compute TTL for copy, pipeline and external activities reduces cold-start time on managed VNet IRs.
• Factory private endpoint: sub-resource datafactory for SHIR communication, and portal for authoring and monitoring.
• Custom DNS is not supported inside the managed VNet.

Example: A storage account and an Azure SQL database have public access disabled. You create a managed VNet IR, add managed private endpoints for both, the owners approve them, and the Copy activities run without any public traffic.

Interview tip: Clearly separate "ADF reaching data privately" (managed private endpoints) from "users and SHIR reaching ADF privately" (factory private endpoint).

Q105. What firewall and port settings does a self-hosted integration runtime need?

Answer: A self-hosted IR needs only outbound HTTPS on port 443 to Azure, and no inbound ports on the corporate firewall. It also needs network access to the local data sources, plus outbound port 1433 only if it writes directly to Azure SQL Database or Synapse.

How it works:

• {datafactory}.{region}.datafactory.azure.net on 443: the command channel to the factory.
• *.servicebus.windows.net on 443: interactive authoring (test connection, preview).
• download.microsoft.com on 443: auto-update, unless auto-update is turned off.
• *.core.windows.net on 443: the staging storage account for staged copy.
• *.database.windows.net on 1433: only for direct loads to Azure SQL or Synapse; staged copy avoids it.
• Inbound 8060 only on the machine's Windows firewall, for the credential manager and the encryption cmdlet.
• Add the SHIR machine's IP address to the data store firewalls where needed.

Example: A bank blocks outbound port 1433. The team turns on staged copy, so the SHIR uploads to Blob storage over 443 and Synapse loads from staging with the COPY statement.

Interview tip: Say "outbound 443 only, nothing inbound on the corporate firewall"; it is the short answer security teams want to hear.

Q106. How does ADF protect data in transit and at rest?

Answer: Data in transit between ADF and cloud data stores travels over HTTPS or TLS when the store supports it, and the SHIR talks to Azure over HTTPS/TLS. ADF is not a data store; what it keeps (entity definitions, linked service credentials and data cached during runs) is encrypted at rest, by default with a Microsoft-managed key.

How it works:

• Credentials in ADF: encrypted with Microsoft-managed certificates, or kept in Azure Key Vault instead.
• On the SHIR: credentials stored locally are encrypted with Windows DPAPI.
• Data stores: turn on their own encryption at rest, such as Storage Service Encryption or TDE on Azure SQL and Synapse.
• Network: add a site-to-site VPN or ExpressRoute between on-premises and Azure for a private channel.
• Factory metadata: add a customer-managed key (BYOK) for extra control.

Example: A healthcare pipeline copies patient files from on-premises over ExpressRoute through a SHIR, uses Key Vault for credentials, writes to ADLS Gen2 with encryption at rest, and the factory itself uses a customer-managed key.

Interview tip: Say clearly that ADF moves and orchestrates data but does not store it, so most encryption at rest is on the source and the sink.

Q107. What are customer-managed keys (CMK) in ADF and what are the rules?

Answer: A customer-managed key is your own RSA key in Azure Key Vault that ADF uses, together with its own system key, to encrypt factory data at rest. It gives you control over key rotation and revoking access, which many regulated companies require.

How it works:

• Key Vault must have soft delete and purge protection enabled, and must be in the same Entra tenant and region as the factory.
• The factory identity needs Get, Wrap Key and Unwrap Key permissions (or the Key Vault Crypto Service Encryption User and Key Vault Secrets User roles).
• After creation: only on an empty factory (no pipelines, datasets, linked services or data flows), using the system-assigned identity. This route does not work for managed VNet factories.
• During creation: in the Azure portal, using a user-assigned identity.
• Once enabled, CMK cannot be removed.
• If you leave the key version out of the key URL, ADF always uses the latest version.

Example: A fintech creates a new production factory with a user-assigned identity, enables CMK pointing to https://kv-prod.vault.azure.net/keys/adf-cmk/, and only then deploys pipelines through CI/CD.

Interview tip: Mention the "empty factory" rule; teams that try to add CMK later often have to rebuild the factory.

Q108. How do you restrict public access to an Azure Data Factory?

Answer: Combine private endpoints for the factory, private-only SHIR connectivity, a managed VNet with managed private endpoints for data stores, and strict RBAC. Together these keep authoring, control traffic and data movement off the public internet.

How it works:

• Factory private endpoints: datafactory for SHIR command traffic and portal for Studio access from your VNet, with private DNS zones privatelink.datafactory.azure.net and privatelink.adf.azure.com.
• Public network access setting: on the factory's Networking page you choose public or private endpoint for SHIR connectivity. Disabling public access applies only to the self-hosted IR, not to the Azure IR or the Azure-SSIS IR.
• Data stores: disable their public access and connect through managed private endpoints or a SHIR inside the VNet.
• Credentials: keep all secrets in Key Vault when public access is blocked.
• Governance: use Azure Policy to enforce these settings on every new factory.

Example: An insurance company runs its SHIR on a VM in a spoke VNet, creates a datafactory private endpoint, switches SHIR connectivity to private, and uses managed private endpoints for ADLS Gen2 and Azure SQL.

Interview tip: Mention that a portal private endpoint alone does not stop others from opening the portal over the public network; RBAC and network rules are still needed.

11. CI/CD, Git and DevOps

Q109. How does Git integration work in Azure Data Factory?

Answer: Git integration connects your development factory to an Azure Repos or GitHub repository, so every pipeline, dataset, linked service and trigger is saved as a JSON file in Git. You work in feature branches, merge into the collaboration branch through a pull request, and only then publish. By default, publishing writes the ARM templates to a branch called adf_publish.

How it works:

• Collaboration branch (default main): holds the reviewed JSON of all factory resources. You can publish only from this branch.
• Feature branches: each developer saves and debugs changes here without touching the live factory.
• Publish branch (default adf_publish): holds the generated ARM templates. You can change its name with a publish_config.json file in the root folder.
• Only the dev factory is linked to Git. Test and prod are updated only by CI/CD.

Example: I create feature/sales-incremental, build the pipeline, debug it, raise a pull request to main, and after approval the lead selects Publish.

Interview tip: Say clearly that the collaboration branch is not what is running; the live factory changes only after Publish or a deployment.

Q110. What happens when you click Publish, and what files are in the ARM template export?

Answer: Publish takes the JSON in the collaboration branch, validates it, deploys it to the live dev factory and generates ARM templates into the publish branch. Those templates are what you deploy to test and production.

How it works:

• ARMTemplateForFactory.json contains every resource in the factory.
• ARMTemplateParametersForFactory.json contains the parameters, such as connection strings, URLs and global parameters, that change per environment.
• A linkedTemplates folder splits large factories into smaller linked templates.
• An ARM template can hold at most 256 parameters. For large factories you trim the list with a custom parameter file or split the factory.

Example: After Publish, the adf_publish branch has a folder named after the factory with both JSON files. My release pipeline picks them up and deploys to the UAT factory with a UAT parameter file.

Interview tip: Mention that you can also select Export ARM template from a feature branch to test a template without publishing anything.

Q111. What is automated publishing with the npm package?

Answer: Automated publishing uses the @microsoft/azure-data-factory-utilities npm package to validate the factory and generate ARM templates inside a build pipeline, so nobody has to click Publish in the Studio. It is the modern way to do ADF CI/CD.

How it works:

• npm run build validate <rootFolder> <factoryId> runs the same checks as Validate all.
• npm run build export <rootFolder> <factoryId> [outputFolder] validates and then writes the ARM template.
• The build publishes the template as a pipeline artifact, and the release pipeline deploys that artifact instead of reading adf_publish.
• Microsoft recommends Node.js 20.x for the package.

Example: This command runs inside an Azure DevOps build on every merge to main.

npm run build export $(Build.Repository.LocalPath)/adf /subscriptions/<subId>/resourceGroups/rg-data-dev/providers/Microsoft.DataFactory/factories/adf-sales-dev "ArmTemplate"

Interview tip: Add that you can disable the Publish button in the Git settings so a manual publish never overwrites the automated one.

Q112. How do you handle environment-specific values such as server names and Key Vault URLs?

Answer: You keep environment values out of the pipeline logic and pass them as ARM template parameters, with one parameter file or set of release variables per environment. For secrets, you use a separate Key Vault per environment with the same secret names.

How it works:

• By default, ADF parameterizes common linked service properties such as server, database, url and baseUrl, and all secure strings.
• To add or remove parameters, you edit arm-template-parameters-definition.json in the root folder (Manage hub > ARM template). In this file, = keeps the current value as the default, - drops the default, and | is for Key Vault secrets.
• If the dev and prod Key Vaults use the same secret names, only the Key Vault URL changes between environments.

Example: In the release stage for prod, I override LS_KeyVault_properties_typeProperties_baseUrl with https://kv-sales-prod.vault.azure.net/. Every connection string then resolves from the prod vault.

Interview tip: Never commit encryptedCredential values in linked service JSON, because they only work in the source factory. Use Key Vault or managed identity instead.

Q113. Why do you need pre- and post-deployment scripts in ADF CI/CD?

Answer: You cannot update a trigger while it is running, so the release must stop triggers before the ARM deployment and start them again afterwards. Microsoft provides a PowerShell script that does this and also deletes resources that were removed from the template.

How it works:

• Run PrePostDeploymentScript.Ver2.ps1 with an Azure PowerShell task, using PowerShell Core.
• The Ver2 script stops and starts only the triggers that changed, not every trigger in the factory.
• Pre-deployment arguments use -predeployment $true -deleteDeployment $false.
• Post-deployment arguments use -predeployment $false -deleteDeployment $true.

Example: These are the pre-deployment arguments in my release task.

-armTemplate "$(System.DefaultWorkingDirectory)/ArmTemplate/ARMTemplateForFactory.json"
-ResourceGroupName rg-data-prod -DataFactoryName adf-sales-prod
-predeployment $true -deleteDeployment $false

Interview tip: Interviewers like it when you say that ARM deployments are incremental, so without the post-deployment cleanup a pipeline deleted in dev stays in prod.

Q114. How are global parameters handled in CI/CD?

Answer: Turn on Include global parameters in ARM template in Manage hub > ARM template. The global parameters then become normal ARM parameters that you override for each environment. Microsoft recommends this newer option because it doesn't need an extra PowerShell step.

How it works:

• Pipelines read a global parameter with @pipeline().globalParameters.<name>.
• The setting works only in Git mode, not in live mode.
• An older option under the Global parameters page, which needed a separate PowerShell script, still works, but it is not the recommended path.
• Global parameter names can't contain -. Use _ instead.

Example: The global parameter env_name is dev in dev and prod in prod. A pipeline uses an If Condition on @equals(pipeline().globalParameters.env_name, 'prod') to send alerts only from production. Microsoft calls this pattern a feature flag.

Interview tip: Say that global parameters suit values that are the same for every pipeline, like the environment name, storage account or alert email. Values that differ per pipeline should stay pipeline parameters.

Q115. Can you deploy only the changed pipelines to production?

Answer: No. ADF does not support cherry-picking commits or selectively publishing resources, because pipelines, datasets, linked services and triggers depend on each other. Every deployment ships the whole factory template. For an urgent fix, you use the hotfix process.

How it works:

• The full template deploys every time. Unchanged pipelines are redeployed with the same definition, so they end up exactly as before.
• The Ver2 pre/post script restarts only the triggers that changed.
• Hotfix: find the collaboration branch commit that is in prod, create a hotfix branch from it, fix and test, select Export ARM Template, deploy that template to test and prod, then merge the fix back into the main branch.

Example: Prod has a bug, but main already contains half-finished work for next sprint. I branch from the commit that is in prod, fix one expression, export the template and release it. Then I merge the fix into main so the bug doesn't come back.

Interview tip: If the interviewer asks "how do you deploy one pipeline", say that you don't, and explain the hotfix branch. That answer shows real project experience.

Q116. What branching strategy do you follow for ADF?

Answer: I use short-lived feature branches off the collaboration branch, pull requests with mandatory review, and one build that produces the ARM artifact for every environment. Test and prod factories are not linked to Git, and only the release pipeline changes them.

How it works:

• main is the collaboration branch, and direct pushes to it are blocked by a branch policy.
• Developers create feature/<ticket> branches in ADF Studio and debug there.
• When a pull request merges, the build validates and exports the ARM template (npm package).
• The release deploys to Dev, Test (with approval) and Prod (with approval), each stage with its own parameters.
• Hotfix branches are cut from the prod commit when needed.

Example: In my last project, five engineers worked on one factory. Each pull request needed one reviewer and a passing validate build. A broken dataset reference was caught in the pull request instead of in production.

Interview tip: Mention that you avoid long-lived environment branches (dev/test/prod branches). With one artifact promoted through stages, the code you tested is the code you ship.

12. Scenario-based ADF interview questions (by experience)

For 2–3 years experience

Q117. You need to copy 500 tables from SQL Server to ADLS Gen2. How do you design it?

Answer: I build one metadata-driven pipeline instead of 500 Copy activities. A control table lists each table, and Lookup, ForEach and one parameterized Copy activity do the work.

How it works:

• Control table: schema name, table name, load type, watermark column, target folder and an is_active flag.
• Lookup reads the active rows (500 rows is well under the 5,000-row Lookup limit).
• ForEach runs in parallel with batchCount set to what the source can handle. The maximum is 50.
• Inside the loop, an Execute Pipeline activity calls a child pipeline with a parameterized dataset (@item().schema_name, @item().table_name), so each table can have its own logging and variables.
• The child pipeline writes Parquet to raw/<schema>/<table>/ and logs the row counts to an audit table.

Example: This is the source query expression inside the child pipeline.

@concat('SELECT ', pipeline().parameters.column_list, ' FROM ', pipeline().parameters.schema_name, '.', pipeline().parameters.table_name)

How I prove it works: I compare the source COUNT with rowsCopied in the audit table for every table, and I check that the number of audit rows is 500.

Interview tip: Say that adding table 501 means inserting one row in the control table, with no code change and no deployment.

Q118. How do you process only the new files that land in a folder?

Answer: I filter files by last-modified time in the Copy activity, using a watermark stored in a control table, or I use a storage event trigger so each new file starts its own run. Both stop me from reprocessing old files.

How it works:

• Time filter: the Copy source (or Get Metadata) accepts modifiedDatetimeStart and modifiedDatetimeEnd. I set the start to the last successful watermark and the end to the pipeline trigger time, then save the new watermark after success.
• Event-based: a storage event trigger fires on BlobCreated and passes @triggerBody().folderPath and @triggerBody().fileName to pipeline parameters.
• Archive pattern: after a successful load, I move processed files to an archive/ folder (Copy, then Delete activity).

Example: I set the window end to @pipeline().TriggerTime and not utcnow(). A rerun then gets the same window and doesn't pick up a different set of files.

How I prove it works: I drop three files, run the pipeline, then drop one more file and run it again. The second run's Copy output must show filesRead = 1.

Interview tip: Mention the edge case of a file that is still being written. Filter on the file name or wait for a _SUCCESS marker file before you read it.

Q119. A pipeline that loads 50 tables fails at table 32. How do you resume without reloading everything?

Answer: First I use Rerun from failed activity in the Monitor hub for a quick recovery. For a lasting fix, I design the pipeline so each table records its status in a control table and a rerun skips the tables that already succeeded.

How it works:

• Rerun from failed activity restarts at the failed activity and doesn't repeat the activities that already succeeded in that run.
• In a metadata-driven design, the child pipeline writes status = 'Succeeded' and the run date per table to an audit table.
• The parent Lookup query selects only tables not yet loaded for today's batch, so any rerun, manual or scheduled, is automatically a resume.
• Each table load is idempotent: overwrite the date folder or use upsert, so a half-written table is safe to load again.

Example: This is the Lookup query for the batch.

SELECT schema_name, table_name
FROM ctl.load_config c
WHERE is_active = 1
  AND NOT EXISTS (SELECT 1 FROM ctl.load_audit a
                  WHERE a.table_name = c.table_name
                    AND a.batch_date = CAST(GETDATE() AS date)
                    AND a.status = 'Succeeded');

Interview tip: The interviewer wants to hear "checkpoint plus idempotent loads", not just "I click rerun".

Q120. Files arrive at random times during the day. How do you load them?

Answer: I use a storage event trigger instead of a fixed schedule, so the pipeline runs as soon as each file lands in ADLS Gen2. The trigger passes the file path to the pipeline, and the pipeline loads only that file.

How it works:

• A storage event trigger uses Azure Event Grid. The subscription must be registered with the Event Grid resource provider.
• It works with ADLS Gen2 and general-purpose v2 storage accounts. Event Grid allows at most 500 storage event triggers per storage account.
• I filter with blobPathBeginsWith (for example /landing/blobs/sales/) and blobPathEndsWith (.csv), and I keep ignore empty blobs on.
• Pipeline parameters receive @triggerBody().folderPath and @triggerBody().fileName.

Example: A vendor uploads orders_0930.csv at 9:30 and orders_1415.csv at 14:15. Each file starts its own run, which copies it to Bronze and writes an audit row.

How I prove it works: I upload a test file and check that the trigger run appears in Monitor with the right file name. I also upload a .txt file and confirm it doesn't fire the trigger.

Interview tip: Mention that a data flow sink writing into the same watched folder can fire the trigger by accident. Keep landing and output folders separate.

Q121. How do you write output to a folder for each day, such as sales/2026/10/20/?

Answer: I parameterize the sink dataset's folder path and build the date with formatDateTime(). I base the date on the trigger time, not on the current clock, so reruns write to the same folder.

How it works:

• The dataset has a folderPath parameter, and the Copy activity passes an expression to it.
• For a schedule trigger, I pass @trigger().scheduledTime into a pipeline parameter run_date.
• For IST folders I use convertFromUtc(), because ADF times are in UTC by default.
• For a tumbling window trigger, I use @trigger().outputs.windowStartTime so a backfill writes each day to its own folder.

Example: This sink folder expression gives sales/2026/10/20.

@concat('sales/', formatDateTime(pipeline().parameters.run_date, 'yyyy/MM/dd'))

This version converts to Indian time first.

@concat('sales/', convertFromUtc(pipeline().parameters.run_date, 'India Standard Time', 'yyyy/MM/dd'))

How I prove it works: I trigger a debug run with run_date set to a past date and check that the files land in that day's folder.

Interview tip: Watch the format letters. Lowercase mm means minutes, so yyyy/mm/dd is a common bug that interviewers like to catch.

Q122. How do you send an email with the actual error message when a pipeline fails?

Answer: I connect a Web activity to the On Failure path of the main activity. It calls a Logic App, which sends the email. The request body carries the error with @activity('Copy_Sales').error.message plus the pipeline name and run ID.

How it works:

• The Logic App has an HTTP request trigger and a send email action (Office 365 Outlook or another mail connector).
• The ADF Web activity uses POST with a JSON body built from system variables.
• If several activities can fail, I wrap the work in a child pipeline. The parent then catches any failure once and reads the error from the Execute Pipeline activity.
• Azure Monitor alerts on the failed runs metric give a second, simpler alert.

Example: This is the Web activity body.

{
  "pipeline": "@{pipeline().Pipeline}",
  "runId": "@{pipeline().RunId}",
  "factory": "@{pipeline().DataFactory}",
  "error": "@{activity('Copy_Sales').error.message}"
}

How I prove it works: I point the source at a table that doesn't exist, run the pipeline and check that the email arrives with the "invalid object name" text.

Interview tip: Mention that after the failure handler succeeds the pipeline can show as succeeded. Add a Fail activity at the end if the run must still show as failed.

Q123. After a rerun, the target table has duplicate rows. How do you fix it and prevent it?

Answer: Duplicates mean the load is not idempotent, because it appends every time it runs. I first clean the data with a window-function delete, and then I change the load to upsert, MERGE or delete-and-reload for the same window.

How it works:

• Delete then insert: the Copy activity sink preCopyScript deletes the rows for the run's date before inserting.
• Upsert: set the Azure SQL sink's write behaviour to upsert with key columns, or land the data in a staging table and run a MERGE stored procedure.
• Files: overwrite the date partition folder instead of adding new file names.
• Use the trigger time or window, not utcnow(), so a rerun covers exactly the same data.

Example: This one-time cleanup keeps the latest row per key.

WITH d AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY order_id
         ORDER BY load_ts DESC) AS rn
  FROM dbo.orders)
DELETE FROM d WHERE rn > 1;

How I prove it works: I run the same window twice and check that COUNT(*) and COUNT(DISTINCT order_id) match.

Interview tip: Say the word "idempotent" and explain it: running the same load twice gives the same result as running it once.

Q124. How do you start a pipeline only after another pipeline, or a pipeline in another factory, finishes?

Answer: In the same factory, I use a parent pipeline with Execute Pipeline activities and Wait on completion turned on, or tumbling window trigger dependencies. Execute Pipeline can't call a pipeline in another factory, so for that I call the ADF REST API from a Web activity and poll the run status.

How it works:

• Same factory: Execute Pipeline A, then Execute Pipeline B on Succeeded.
• Tumbling windows: trigger B can depend on trigger A's window with an offset. A trigger can depend on up to five other triggers.
• Cross-factory: a Web activity POSTs to the createRun endpoint using the factory's managed identity, which needs the Data Factory Contributor role on the target factory. An Until loop then checks the run status until it is no longer InProgress or Queued.

Example: The Web activity calls this URL with the method POST and the resource https://management.azure.com/.

https://management.azure.com/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.DataFactory/factories/adf-finance/pipelines/PL_Close/createRun?api-version=2018-06-01

How I prove it works: I make A fail on purpose and confirm that B never starts.

Interview tip: Don't suggest scheduling B "30 minutes after A". Interviewers mark time-based chaining as fragile.

For 4–6 years experience

Q125. The source team adds and renames columns without notice. How do you handle schema changes?

Answer: I land raw data in a self-describing format (Parquet or Delta) without a fixed mapping, detect drift before the curated layer, and handle new columns automatically while failing loudly on breaking changes such as renames or type changes.

How it works:

• Bronze: the Copy activity has no explicit column mapping, so new columns flow into Parquet as they are.
• Detection: Get Metadata with the structure field returns the source columns. I compare them with the expected schema stored in a control table and log the differences.
• Mapping data flows: turn on Allow schema drift and use rule-based mapping, so new columns pass through.
• Delta in Databricks: mergeSchema adds new columns. A rename or type change raises a Fail activity with a clear message.

Example: The vendor adds discount_pct. Bronze and Silver pick it up automatically. When they rename cust_id to customer_id, the drift check fails the run with "Column cust_id missing" before bad data reaches Gold.

How I prove it works: In test, I add a column and rename another, then confirm that the first passes and the second stops the run with an alert.

Interview tip: Separate additive changes (allow them) from breaking changes (stop and alert). Interviewers want to see that difference.

Q126. A daily load must finish by 6 AM but now ends at 7:30 AM. What do you do?

Answer: I measure first, then fix the biggest bottleneck. The Copy activity's monitoring details show where time goes, such as queue time, time to first byte and transfer, and the pipeline Gantt view shows what runs one after another when it could run in parallel.

How it works:

• Queue time high: the self-hosted IR is overloaded. Add nodes (up to four) or raise the concurrent jobs per node.
• Transfer slow: raise DIUs and parallel copies, and use source partition options (physical partitions or dynamic range) for big SQL tables.
• Synapse sink: use staged copy with COPY or PolyBase instead of row inserts.
• Design: move independent tables into a parallel ForEach, and switch full loads to incremental loads.
• Data flows: set a TTL on the Azure IR so later data flows reuse a warm cluster.

Example: In one project, a 900-million-row fact table was read with no partitioning. Switching to dynamic range partitioning on order_id, and running it alongside the dimensions, brought the finish time from 7:30 to 5:10.

How I prove it works: I compare the run durations for a week before and after the change, and I set an alert if the run is not complete by 5:45.

Interview tip: Begin with "I would check the monitoring details" before you list settings. Interviewers want diagnosis before tuning.

Q127. How do you move 2 TB of data from on-premises SQL Server and file shares to Azure?

Answer: I use a self-hosted integration runtime with several nodes, partition the large tables, and run the copies in parallel. I check the network bandwidth first, because if 2 TB can't move within the window over the link, an offline option like Azure Data Box is better.

How it works:

• SHIR: install on two to four machines close to the source, for high availability and throughput. They only need outbound HTTPS on port 443.
• Tables: use physical or dynamic range partitioning on the source and write Parquet to ADLS Gen2.
• Files: use binary copy to keep the files as they are, and turn on data consistency verification.
• Load in two parts: a one-time historical load in batches by table, then incremental loads using watermarks until cutover.
• Throttle with ForEach batchCount so the source stays usable during business hours.

Example: I split the 2 TB into 40 batches from a control table and ran them over three nights. Each batch wrote counts to an audit table.

How I prove it works: I reconcile row counts and sums of key amount columns between source and target for every table, and for files I compare file counts and sizes.

Interview tip: Mention the bandwidth calculation and the reconciliation report. Seniors don't just copy data; they prove that all of it arrived.

Q128. How do you design an SCD Type 2 load for a customer dimension?

Answer: I keep history by closing the old row and inserting a new one whenever a tracked attribute changes. Each row carries effective_from, effective_to and an is_current flag. In ADF this can be a mapping data flow, but on a lakehouse I usually let ADF orchestrate and do the MERGE in Delta or SQL.

How it works:

• Land the changed customers in staging using a watermark.
• Compare a hash of the tracked columns between staging and the current dimension rows.
• For changed keys, expire the current row (set effective_to, is_current = 0) and insert the new version.
• Insert brand-new customers. Leave unchanged rows alone.
• In a data flow: Derived Column (hash), Lookup or Exists, Conditional Split and Alter Row, with a sink that allows update and insert.

Example: Step 1 of the SQL version expires the changed rows. Step 2 inserts the new versions.

UPDATE d SET d.effective_to = s.load_ts, d.is_current = 0
FROM dim.customer d
JOIN stg.customer s ON s.customer_id = d.customer_id
WHERE d.is_current = 1 AND d.row_hash <> s.row_hash;

How I prove it works: Each customer must have exactly one row with is_current = 1, and its date ranges must not overlap.

Interview tip: Mention the hash comparison and the "one current row per key" test. They show you have run SCD2 in production.

Q129. How do you read a REST API that returns data in pages?

Answer: I use the REST connector in a Copy activity with pagination rules, so ADF requests the next page by itself. When the API needs complex logic, I fall back to an Until loop with Web activities.

How it works:

• Next link in the body: "AbsoluteUrl": "$.paging.next". ADF follows it until the value is null.
• Offset or page number: put {offset} in the URL and use "QueryParameters.{offset}": "RANGE:0::1000" with an end condition such as "EndCondition:$.data": "Empty".
• MaxRequestNumber caps the number of pages, and requestInterval adds a wait between requests to stay inside rate limits.
• ADF also stops when the API returns HTTP 204.
• Pagination isn't supported when the top-level JSON is an array.

Example: This rule follows a nextLink in the response body.

"paginationRules": {
  "AbsoluteUrl": "$.nextLink"
}

How I prove it works: I compare the total record count the API reports with rowsCopied, and I test with a page size of 10 so pagination runs many times.

Interview tip: Mention rate limits and secrets: keep the token in Key Vault and add requestInterval before the API starts returning HTTP 429.

Q130. Your parallel load is overloading the source database. How do you throttle it?

Answer: I limit concurrency at every level that can multiply load: the ForEach batchCount, the Copy activity's parallel copies and max concurrent connections, the pipeline's concurrency setting and the self-hosted IR's concurrent jobs. I schedule heavy tables outside business hours.

How it works:

• ForEach: lower batchCount (default 20, max 50) from 20 to, say, 4.
• Copy: set parallelCopies and the source maxConcurrentConnections. Partitioned reads open several sessions per table, so I count those too.
• Pipeline concurrency: stops overlapping runs of the same pipeline from stacking up.
• Priority groups: a load_group column in the control table lets big tables run in a separate, smaller loop.
• Read from a replica if one exists.

Example: We had 50 tables, each with eight partitions, running in parallel. That opened hundreds of sessions on the OLTP server. Setting batchCount to 5 and partitions to 4 kept the DBA happy and still finished on time.

How I prove it works: I watch active sessions and CPU on the source (with the DBA) during the run, and I compare the run duration before and after.

Interview tip: Multiply it out loud: batchCount × partitions × retries = sessions. It shows you understand how load adds up.

Q131. A downstream API is down and hundreds of activities keep retrying. How do you prevent a retry storm?

Answer: I keep retries small with longer intervals, limit how many runs can hit the same dependency at once, and add a simple circuit breaker that stops new work when the dependency is unhealthy. Retries should fix short blips, not hammer a system that is down.

How it works:

• Activity retry policy: for example 2 retries with a 300-second interval, not 10 retries with 30 seconds. ADF retries at a fixed interval.
• Don't stack retries: if a tumbling window trigger has a retry policy and its activities also retry, the attempts multiply.
• Concurrency: use ForEach batchCount, pipeline concurrency and tumbling window maxConcurrency to cap parallel calls.
• Circuit breaker: a first Web activity calls a health endpoint. If it fails, an If Condition skips the loop and a Fail activity raises one clear alert.

Example: A CRM API went down at night. Before the fix, 200 iterations × 5 retries made 1,000 calls and the vendor blocked our IP. After the fix, one health check failed, one alert went out and the batch reran cleanly in the morning.

How I prove it works: In test, I point the health check at a dead URL and confirm that no Copy activities start.

Interview tip: Say that you tune retries per dependency. A database deadlock and a dead API need different retry settings.

Q132. How do you deploy one ADF solution to Dev, Test and Prod?

Answer: Only the dev factory is connected to Git. A build turns main into an ARM template artifact, and a multi-stage release deploys that same artifact to Test and Prod with environment parameters, approvals and trigger stop/start scripts.

How it works:

• Build: the npm package runs validate and export on each merge.
• Release stages: each stage has its own variable group or parameter file (server names, Key Vault URL, global parameters).
• Per environment: a separate Key Vault with the same secret names, and each factory's managed identity granted access to its own data stores.
• Self-hosted IR: the same IR name in every environment, or a linked IR shared from a central factory.
• Pre/post script stops and starts triggers, and approvals gate Test and Prod.

Example: The prod stage overrides LS_KeyVault_baseUrl, env_name = prod and the trigger start time, waits for the release manager's approval, then deploys with AzureResourceManagerTemplateDeployment@3.

How I prove it works: After each deployment, a smoke-test pipeline runs a small Copy activity against each linked service and fails the release if any connection breaks.

Interview tip: Mention that the same artifact moves through every stage. Rebuilding for each environment is a red flag.

For 8–10+ years / lead and architect

Q133. Design an ingestion framework for 100 different sources.

Answer: I build a metadata-driven framework: a small set of generic, parameterized pipelines per source type, driven by control tables, with standard logging, watermarks and alerts. Onboarding a new source should mean adding metadata rows, not building new pipelines.

How it works:

• Control tables: source system, connection type, Key Vault secret name, object name, load type (full, incremental, CDC), watermark column, target path, schedule group, priority and an active flag.
• Linked services: one parameterized linked service per technology (SQL Server, Oracle, REST, SFTP), with secrets read from Key Vault by name.
• Orchestration: a master pipeline runs Lookup, then ForEach by group. Inside the loop, Execute Pipeline calls a router pipeline that uses a Switch on source type to call the right child pipeline.
• Common services: an audit table (rows, duration, status), watermark update, failure alerts and Fail activities with clear messages.
• Governance: naming standards, Git, CI/CD and a runbook.

Example: A new Oracle source took one day to onboard: insert 40 metadata rows, add a secret to Key Vault and test in dev. No new pipeline was needed.

How I prove it works: A daily report reconciles source counts with landed counts for every object, and a dashboard shows freshness for each source.

Interview tip: Talk about operability: logging, rerun and onboarding time. Architects are judged on the day-two experience, not just the first load.

Q134. How do you orchestrate Databricks with ADF?

Answer: ADF handles scheduling, dependencies, data movement and alerts. Databricks handles heavy transformation. ADF copies raw data to Bronze, then runs Databricks notebooks or jobs for Silver and Gold, passing parameters and reading results back.

How it works:

• Notebook activity: pass baseParameters such as the run date. The notebook returns a value with dbutils.notebook.exit(), which ADF reads as @activity('NB_Silver').output.runOutput.
• Job activity: runs an existing Databricks job (including serverless) by job ID with jobParameters. This keeps the job logic in Databricks.
• Compute: new job clusters or pools for scheduled work, never an all-purpose interactive cluster in production.
• Authentication: the factory's managed identity to the workspace, not personal access tokens.

Example: The pipeline copies orders to Bronze, then the Silver notebook returns {"rows": 120034}. An If Condition checks that the count is above zero before the Gold job runs.

How I prove it works: Each step writes to the audit table with both the ADF run ID and the Databricks run URL, so one failure can be traced end to end.

Interview tip: Explain who owns what. If transformation logic is split across ADF data flows and notebooks without a rule, support becomes hard.

Q135. The ADF bill doubled. How do you cut cost?

Answer: I find out where the money goes first. ADF charges for orchestration (activity runs), data movement (DIU-hours), data flow cluster time and self-hosted IR or external activity time. Then I fix the biggest item, which is usually data flows or too many small activity runs.

How it works:

• Data flows: right-size the core count, shorten the TTL when there is no next job to reuse the cluster, and move simple logic to a Copy activity or SQL.
• Copy: avoid setting very high DIUs on small copies, and merge thousands of tiny files.
• Orchestration: a ForEach over 10,000 files with five activities each is 50,000 activity runs. Process folders, not single files.
• Full loads to incremental: move less data.
• Debug sessions: turn them off when not in use.
• Tags and Cost Management: split cost by factory and team.

Example: One team ran a data flow every 15 minutes just to rename columns. Moving it to a Copy activity with a column mapping removed most of the data flow spend.

How I prove it works: I compare the Cost Management daily cost for the factory over two weeks before and after, filtered by meter.

Interview tip: Name the four meters. Most candidates just say "reduce DIUs".

Q136. How do you secure PII data moving through ADF?

Answer: I protect PII in four places: identity, network, data and logs. That means managed identities and Key Vault for access, private endpoints for the network path, masking or hashing before analytics layers, and secure input/output so values don't appear in run logs.

How it works:

• Identity: the factory's managed identity has least-privilege RBAC on storage and databases. Secrets live in Key Vault, never in JSON.
• Network: a managed virtual network IR with managed private endpoints, and public network access turned off on storage and SQL.
• Data: hash or mask PII columns (for example sha2(256, pan_number) in a data flow) before Silver. Restricted zones have their own ACLs.
• Logs: turn on Secure input and Secure output on activities that handle sensitive values, so the Monitor view doesn't show them.
• Governance: classify with Microsoft Purview, and keep raw PII only for as long as required.

Example: For Aadhaar and PAN data, raw files land in a restricted container only the ingestion identity can read. Analysts see only hashed keys in Gold.

How I prove it works: I log in as an analyst and confirm I can't read the raw container, and I check run outputs in Monitor for exposed values.

Interview tip: Mention secure input/output. Most candidates forget that activity logs can leak data.

Q137. How do you monitor SLAs across many pipelines?

Answer: I send ADF diagnostic logs to Log Analytics, define an SLA per pipeline, and alert both on failures and on runs that are late or still running. Failure alerts alone miss the worst case: a pipeline that never started.

How it works:

• Diagnostic settings send pipeline, activity and trigger runs to a Log Analytics workspace, in resource-specific tables (ADFPipelineRun, ADFActivityRun, ADFTriggerRun). This also keeps history beyond the 45 days that ADF itself keeps.
• Elapsed time metric: in the pipeline settings, set the expected duration, and ADF emits a metric when a run goes over it.
• Log alerts: a KQL query checks whether the critical pipeline succeeded before the SLA time.
• Dashboards: an Azure Workbook or Power BI view of freshness by pipeline.

Example: This alert query runs at 06:05 IST and fires if it returns no rows.

ADFPipelineRun
| where PipelineName == "PL_Daily_Sales"
| where Status == "Succeeded"
| where End > startofday(now())
| project PipelineName, Start, End, RunId

How I prove it works: I disable the trigger in test and confirm that the "missing run" alert fires.

Interview tip: Say "alert on absence, not just failure". That one line separates leads from mid-level engineers.

Q138. How do you provide audit and lineage for ADF pipelines?

Answer: I combine an operational audit table written by every pipeline with Microsoft Purview lineage for data flow across systems. The audit table answers "what ran, when and how many rows". Purview answers "where did this table come from".

How it works:

• Audit table: a Stored Procedure activity at the end of each child pipeline logs the run ID, pipeline, source, target, rowsRead, rowsCopied, duration, status and error.
• Purview: connect the factory to Purview to push lineage from Copy, Data Flow and Execute SSIS Package activities. Data flow lineage is at source and sink level only, and Copy lineage doesn't cover query or stored procedure sources.
• Change audit: Git history shows who changed a pipeline, and the Azure activity log shows who triggered or changed resources.
• Retention: keep logs in Log Analytics or storage for as long as audit rules require.

Example: This is the parameter value passed to the audit procedure.

@activity('Copy_Orders').output.rowsCopied

How I prove it works: For any Gold table, I can show the Purview lineage graph and the audit rows for yesterday's load in under five minutes.

Interview tip: Mention the Purview limits honestly. Dynamic metadata-driven pipelines often need the audit table to fill lineage gaps.

Q139. How do you plan disaster recovery for Azure Data Factory?

Answer: ADF is deployed in one region. Microsoft-managed failover to the paired region is best effort, isn't available in every region, and may come after a significant delay. For a real RTO, I keep everything in Git and can redeploy the factory to a secondary region through CI/CD.

How it works:

• Metadata: Git plus the ARM artifact let me create a factory in the secondary region within minutes, with secondary parameters.
• Integration runtimes: an Azure IR set to auto-resolve can use another region. A self-hosted IR needs nodes in the DR site, or Azure Site Recovery if it runs on Azure VMs. An Azure-SSIS IR needs a geo-replicated SSISDB.
• Data stores: storage, SQL and Key Vault need their own geo-redundancy. Without them, a DR factory is of no use.
• Triggers: stopped in the DR factory until failover, so both factories don't run the same loads.
• Tumbling window state can be lost in an outage, so I plan to rerun those windows.

Example: Our runbook was to deploy the release to a pre-created resource group in the secondary region, start the triggers and rerun from yesterday's watermark. We tested it every quarter.

How I prove it works: A DR drill measures the real recovery time against the target.

Interview tip: Say that the data stores decide your DR, not just the factory. A factory without its data can't run anything.

Q140. Your company asks whether to move from ADF to Fabric Data Factory. How do you decide?

Answer: ADF is not retired and remains supported, so this is a business decision, not an emergency. I recommend Fabric when the team is already moving to OneLake, Lakehouse and Power BI in Fabric, and I stay on ADF when the solution depends on features without full parity, such as SSIS, complex managed VNet setups or heavy mapping data flows.

How it works:

• Assess: use the built-in upgrade assessment, which tags each pipeline as Ready, Needs review, Coming soon or Not compatible.
• Key differences: linked services become connections, datasets move inline into activities, global parameters become variable libraries, a self-hosted IR becomes an on-premises data gateway, and Execute Pipeline becomes Invoke Pipeline.
• Mapping data flows can move to MDF transforms in Dataflow Gen2, which are in preview.
• Transition: mount the existing ADF as an item in a Fabric workspace for visibility, then move low-risk pipelines first.
• Cost: compare Fabric capacity cost against ADF pay-per-use for your actual run pattern.

Example: For a Power BI-heavy retail client, we moved 60 Copy pipelines to Fabric in phases and left the SSIS packages running on ADF, called from Fabric.

How I prove it works: I run both versions in parallel for two weeks and compare row counts, durations and cost before cutting over.

Interview tip: Avoid "ADF is dead, move everything". Interviewers want a phased plan based on parity, with a rollback path.

13. ADF vs Databricks vs Fabric Data Factory: what interviewers expect

Short answer: Use ADF to orchestrate and copy data, Databricks for heavy Spark transformations, and Fabric Data Factory when the company is moving to Microsoft Fabric. Interviewers want to hear that you choose by workload, not by habit. For the Spark side, see our 150 PySpark and Databricks interview questions.

A common real-world answer: "ADF triggers and copies data into ADLS Gen2, calls a Databricks job for transformation, and Power BI reads the Gold tables." Learn more in our Azure data engineer roadmap.

14. How to prepare for an ADF interview in 7 days

Short answer: Rebuild two real pipelines yourself (our Azure Data Factory tutorial walks you through five), practise expressions out loud and prepare three scenario stories from your own work.

1. Day 1: Revise components and integration runtimes (Q1–Q15, Q31–Q40).
2. Day 2: Build a Lookup + ForEach + Copy pipeline with parameters (Q16–Q30).
3. Day 3: Write 10 expressions from memory: dates, file names, activity outputs (Q41–Q55).
4. Day 4: Build a watermark incremental load and rerun it twice (Q81–Q90).
5. Day 5: Add retries, a failure path, an email alert and a Log Analytics query (Q91–Q100).
6. Day 6: Connect the factory to Git and export an ARM template (Q109–Q116).
7. Day 7: Practise the scenario answers aloud in 2 minutes each (Q117–Q140).

For hands-on practice, pick a few pipelines from our 90 Azure data engineer projects, and for Databricks, SQL and PySpark rounds read our 200 Azure data engineer interview questions.

15. How Brolly Academy helps

• Azure Data Factory Training in Hyderabad: hands-on ADF pipelines, data flows, triggers and CI/CD.
• Azure Data Engineer Training in Hyderabad: ADF, Databricks, PySpark, SQL, Synapse and Fabric in 2–2.5 months, from ₹9,999.
• Mock interviews with ADF scenario questions and feedback on your answers.
• Classroom, online and hybrid batches in English and Telugu, with a free demo.
• Explore all courses at Brolly Academy.

Frequently asked questions

Sources and further reading

• Microsoft Learn — Azure Data Factory documentation
• Microsoft Learn — Pipelines and activities in Azure Data Factory
• Microsoft Learn — Integration runtime in Azure Data Factory
• Microsoft Learn — Expressions and functions in Azure Data Factory
• Microsoft Learn — Copy activity performance and scalability guide
• Microsoft Learn — Source control in Azure Data Factory
• Microsoft Learn — Continuous integration and delivery in Azure Data Factory
• Microsoft Learn — Monitor Azure Data Factory
• Wikipedia — Extract, transform, load