Cloud / Azure Data Factory Interview questions

Azure Data Factory (ADF) is Microsoft's cloud-based, serverless data integration service. You build pipelines that move data between sources and destinations and transform it on the way, without provisioning or patching any servers.

It supports both ETL and ELT patterns, has a visual low-code authoring canvas, and ships with 90+ built-in connectors covering databases, files, SaaS apps and cloud storage.

With a self-hosted integration runtime the same pipeline can also reach on-premises and private-network systems, which is why ADF is a common pick for hybrid data movement.

An ADF solution is built from a handful of building blocks that reference each other. Knowing who depends on whom makes most other questions easier.

Parameters, variables and global parameters sit on top of these to make pipelines reusable.

A pipeline is a logical grouping of activities that together perform one task, for example ingest yesterday's files, clean them and load a warehouse table. You publish, trigger and monitor it as a single unit.

Activities inside it are chained with dependency conditions, and the pipeline can take parameters, hold variables and limit concurrent runs. Behind the canvas it is just JSON:

{
  "name": "CopySalesPipeline",
  "properties": {
    "parameters": { "runDate": { "type": "string" } },
    "activities": [
      { "name": "CopySales", "type": "Copy", "dependsOn": [] }
    ]
  }
}

An activity is one processing step inside a pipeline. Every activity has a name, a type, type-specific properties, optional dependencies and a policy block for timeout and retry.

ADF groups activities into three families:

A dataset is a named reference to the data an activity works with, such as a table, a folder path or a file pattern. It does not hold the data itself.

It points to a linked service for connectivity and describes format, location and optionally schema. Datasets can be parameterized, so a single dataset can serve many tables or folders.

A linked service holds the connection information ADF needs to reach an external resource. Think of it as a connection string with an identity attached.

There are two flavors: data store linked services (Azure SQL, Blob Storage, S3) and compute linked services (Databricks, HDInsight, Azure Batch). Each one references an integration runtime, and secrets can be pulled from Azure Key Vault instead of being stored in the factory.

{
  "name": "LS_AzureSql",
  "properties": {
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "connectionString": "Server=tcp:myserver.database.windows.net;Database=sales;"
    },
    "connectVia": { "referenceName": "AutoResolveIntegrationRuntime", "type": "IntegrationRuntimeReference" }
  }
}

The integration runtime (IR) is the compute infrastructure ADF uses to do the actual work. Pipelines only describe what to do; the IR is where it runs.

It covers four capabilities: data movement for Copy, data flow execution, activity dispatch to services like Databricks or SQL, and SSIS package execution.

Each linked service points to an IR. If you don't choose one, ADF uses the built-in AutoResolveIntegrationRuntime.

ADF offers three IR types, and the right one depends on where your data lives and what you want to run.

An Azure IR can optionally be placed in a managed virtual network for private connectivity.

A trigger starts a pipeline run without anyone clicking Run. ADF supports three trigger families, plus manual on-demand runs.

The Copy activity moves data from a source to a sink across any of the supported connectors. It is the workhorse of most ingestion pipelines.

Along the way it can convert file formats (for example CSV to Parquet), apply column mapping, compress output and skip incompatible rows when fault tolerance is on. It needs a source dataset, a sink dataset and an integration runtime.

{
  "name": "CopySales",
  "type": "Copy",
  "inputs":  [ { "referenceName": "ds_sales_csv", "type": "DatasetReference" } ],
  "outputs": [ { "referenceName": "ds_sales_parquet", "type": "DatasetReference" } ],
  "typeProperties": {
    "source": { "type": "DelimitedTextSource" },
    "sink":   { "type": "ParquetSink" }
  }
}

Mapping data flows let you design transformations visually and run them at scale. ADF translates the design into Spark code and runs it on a cluster it manages for you, so you never write or tune Spark yourself.

Common transformations include join, aggregate, derived column, conditional split, pivot, window, lookup, surrogate key and filter. They are called from a pipeline through the Data Flow activity, and the cluster size is controlled by the Azure IR settings.

The Lookup activity reads data from a dataset and returns it to the pipeline, either the first row only or the entire result as an array. It is mostly used to fetch configuration, such as a list of tables to process or a watermark value.

Output is limited to 5,000 rows and 4 MB, so it is meant for small control data, not bulk movement. You read the result in later activities with expressions:

@activity('LookupConfig').output.firstRow.TableName
@activity('LookupConfig').output.value

The second form (an array) is what you normally feed into a ForEach.

ForEach loops over an array and runs a set of inner activities once per element. The array usually comes from a Lookup, Get Metadata (childItems) or a parameter.

Inside the loop, the current element is read with @item(). By default iterations run in parallel; the batch count controls how many (default 20, maximum 50) and the Sequential checkbox forces one at a time. A ForEach cannot be nested directly inside another ForEach, so use Execute Pipeline for a second level.

Get Metadata returns information about a file, folder or table without reading its contents. Useful fields include childItems (list of files and subfolders), exists, lastModified, size, itemType and structure.

A typical pattern is listing a folder with childItems, filtering it, then looping with ForEach. Another is checking exists inside an If Condition so the pipeline only copies when the file has actually landed.

Execute Pipeline calls another pipeline from within a pipeline and can pass parameter values to it. That makes it the way to build reusable child pipelines and break large workflows into manageable pieces.

With Wait on completion ticked, the parent waits and picks up the child's result; unticked, it fires the child and moves on. It is also the standard workaround for the no-nested-ForEach restriction.

Parameters are defined on the pipeline (name, type, optional default) and receive their values when the run starts, from a trigger, a manual run or an Execute Pipeline activity.

Inside the pipeline you read them as @pipeline().parameters.paramName. Supported types include String, Int, Float, Bool, Array, Object and SecureString. They are read-only during the run; if you need a value that changes, use a variable. Triggers can feed system values in, for example:

runDate = @formatDateTime(trigger().scheduledTime, 'yyyy-MM-dd')

A debug run executes the pipeline as it currently exists in your draft, with no need to publish or attach a trigger. It is the fast way to test changes during authoring.

It does real work: data is copied, tables are written and compute is billed. You can use Debug until on an activity to run only up to and including that step. Mapping data flows need a separate data flow debug session, which spins up its own cluster.

The Monitor hub in ADF Studio shows pipeline runs, trigger runs, activity runs and integration runtime health. You can filter by status or time, drill into any activity's input and output, and rerun from the failed activity instead of starting over.

For alerting and long-term history, send diagnostic logs to Log Analytics or a storage account and build Azure Monitor alerts on failed runs. The studio itself keeps run history for only 45 days.

The Web activity calls a REST endpoint from a pipeline using GET, POST, PUT or DELETE with custom headers and a body. The response lands in the activity output for later steps to use.

It supports Basic, client certificate, service principal and managed identity authentication. Typical uses are kicking off a Logic App, calling an Azure Resource Manager API (for example scaling a database) or refreshing a Power BI dataset. If the called service needs to call back when it finishes, use the Webhook activity instead.

Control flow activities decide how and when other activities run. They don't move or transform data themselves.

Both hold values, but they behave very differently, and mixing them up is a classic interview trap.

Be careful with variables inside a parallel ForEach: all iterations share one pipeline-scoped variable, so they overwrite each other. Use sequential mode or pass values to child pipelines instead.

SSIS is an on-premises ETL engine that ships with SQL Server. ADF is a managed cloud orchestration service. They overlap, but their strengths differ.

The two also work together: existing packages can run unchanged in ADF through an Azure-SSIS IR.

A schedule trigger fires on wall-clock time and has no memory of past windows. A tumbling window trigger fires for a series of fixed-size, non-overlapping, contiguous time slices and tracks the state of each.

Pick tumbling window when each slice must be processed exactly once and failures must be reprocessable.

You install the IR software on a machine inside the private network and register it with an authentication key generated in ADF. From then on the node polls ADF for work, so no inbound firewall rule is needed.

It only makes outbound HTTPS (port 443) connections to the ADF service, then talks to local sources over the internal network. Credentials entered for it are encrypted with Windows DPAPI on the node.

For availability and throughput you can register up to four nodes under one IR, and share an IR with other factories through a linked IR.

Keep passwords and keys out of the factory by pointing linked services at Azure Key Vault.

1. Create a Key Vault and add the secret.
2. Grant the factory's managed identity Get (and List) permission on secrets, via access policy or the Key Vault Secrets User role.
3. Create a Key Vault linked service in ADF.
4. In the real linked service, choose Azure Key Vault for the password field and give the secret name.

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

The secret value never appears in the factory definition or in Git.

The classic approach is the watermark pattern: remember the highest modified value you loaded last time and only pull rows newer than that.

flowchart LR
A["Lookup old watermark"] --> C["Copy rows between old and new"]
B["Lookup new watermark MAX of modified column"] --> C
C --> D["Stored procedure updates watermark table"]

SELECT * FROM dbo.Orders
WHERE LastModified > '@{activity('OldWM').output.firstRow.Value}'
  AND LastModified <= &#x27;@{activity(&#x27;NewWM&#x27;).output.firstRow.Value}&#x27;

Read the new watermark before copying so rows arriving mid-run are caught next time. Alternatives are SQL change tracking or CDC, the built-in CDC resource, and for files the last modified filter on the Copy source.

Every activity carries a policy with three settings: timeout (default 12 hours), retry count (default 0) and retry interval (default 30 seconds). Turn on retries for steps that fail transiently, such as network blips or throttled sources.

Retries re-run the whole activity, so the load must be idempotent, otherwise you create duplicates. Tumbling window triggers add their own retry policy per window, and after a failure you can rerun from the failed activity from the Monitor hub.

Activities don't share memory. You read the output of an earlier activity with an expression, and ADF makes it available once that activity has completed.

@activity('Copy1').output.rowsCopied
@activity('Lookup1').output.firstRow.Id
@concat('sales_', formatDateTime(utcnow(), 'yyyyMMdd'), '.csv')
SELECT * FROM t WHERE id = @{activity('Lookup1').output.firstRow.Id}

Use @{ } string interpolation to embed values inside longer text such as SQL, and the built-in functions (concat, formatDateTime, coalesce, if) for logic. The activity must be an upstream dependency, otherwise the reference fails.

Build one generic copy and drive it from a list instead of creating a Copy activity per table.

flowchart LR
A["Lookup list of tables"] --> B["ForEach over output.value"]
B --> C["Copy with parameterized datasets"]

1. Lookup returns schema and table names from a control table or INFORMATION_SCHEMA.
2. ForEach iterates over @activity('GetTables').output.value.
3. Inside, the Copy uses datasets with schema and table parameters set to @item().SchemaName and @item().TableName.

Add more rows to the list and the same pipeline copies them, with no redesign.

Copy speed is governed by the source, the sink, the network and the compute you give it. Start with the knobs ADF exposes:

• Data Integration Units (DIUs): serverless compute power for cloud copies, 4 to 256, Auto by default.
• Degree of copy parallelism: how many parallel threads read and write.
• Partition options: split large tables by range or physical partition so reads run in parallel.
• Staged copy: land data in Blob/ADLS first, then bulk load with PolyBase or COPY into Synapse.
• Locality: keep the IR region close to the data, and scale out self-hosted IR nodes.

Raising DIUs only helps if the source and sink can actually deliver faster.

You provision an Azure-SSIS integration runtime, a managed cluster of Azure VMs that runs the SSIS engine, then execute packages with the Execute SSIS Package activity.

1. Create an Azure SQL Database or Managed Instance to host the SSISDB catalog (or use a package store).
2. Create and start the Azure-SSIS IR, choosing node size and count.
3. Deploy the SSIS project from SSDT or SSMS.
4. Add the Execute SSIS Package activity to a pipeline and point it at the package.

To reach on-premises sources, add a self-hosted IR as a proxy. The Azure-SSIS IR bills while it runs, so stop it when idle.

ADF supports Azure DevOps Git and GitHub. You connect a repo, choose a collaboration branch (usually main) and work in feature branches, merging through pull requests.

Clicking Publish from the collaboration branch generates ARM templates into the adf_publish branch, which release pipelines use to deploy to other environments.

Without Git, you author in live mode: there is no version history, no branches, and every save is published straight to the service.

The usual flow takes the generated ARM templates and promotes them through dev, test and prod with a pipeline in Azure DevOps or GitHub Actions.

1. Build: take the ARM template from adf_publish, or generate and validate it from the collaboration branch using the @microsoft/azure-data-factory-utilities npm package.
2. Stop active triggers in the target factory.
3. Deploy the ARM template with an override parameters file per environment (server names, Key Vault URLs, storage accounts).
4. Clean up deleted resources, then restart the triggers.

Global parameters and Key Vault references keep environment differences out of the pipeline logic.

The batch count sets how many ForEach iterations may run in parallel. The default is 20 and the maximum is 50. Ticking Sequential overrides it and runs one at a time.

More parallelism means faster loops but also more simultaneous load on your source and sink, which can cause throttling or lock contention. Iterations also finish in no guaranteed order, so never rely on sequence when running in parallel.

Schema drift means source columns appear, disappear or change without notice. Mapping data flows handle it by allowing schema drift on the source and sink, so unknown columns flow through instead of breaking the run.

To act on those columns without naming them, use rule-based mapping and column patterns, for example:

Match:    type == 'string'
Output:   $$ -> trim($$)
byName('CustomerId')   // read a column that is not in the defined schema

Turn on infer drifted column types if you need typed values downstream.

A linked service answers how do I connect? and a dataset answers which data do I use there?

In mapping data flows you can skip datasets using inline source definitions.

Use Copy when the job is moving data, perhaps with format conversion or column mapping. Use a mapping data flow when you need real transformation logic in the middle.

A common design uses Copy to land raw data, then a data flow to shape it.

Every data factory gets a system-assigned managed identity automatically, named after the factory. You grant that identity access to targets and then choose managed identity authentication in the linked service, so no password exists at all.

For storage, assign a role such as Storage Blob Data Reader. For Azure SQL, create a user from the factory identity:

CREATE USER [my-adf-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-adf-name];

User-assigned managed identities are also supported on linked services when you need one identity shared across factories.

A managed virtual network is an isolated network that ADF creates and manages for an Azure integration runtime. Copy and data flow compute run inside it, so traffic to your data stores can stay off the public internet.

Managed private endpoints are the private connections from that network to targets such as Storage, SQL or Key Vault. Each request must be approved on the target resource by its owner before it works. The result is stronger protection against data exfiltration, at the cost of a slightly slower IR start.

Don't guess; the Copy output tells you where the time went. Open the activity run details and look at throughput, DIUs used, parallel copies and the duration breakdown (queue, time to first byte, reading from source, writing to sink).

1. Find the slow phase. A long queue means resource contention; long source reads point to the source.
2. Check the source and sink for throttling, missing indexes or small-file overhead.
3. Add partition options and raise parallel copies for large tables.
4. Raise DIUs, or add self-hosted IR nodes, only if the compute is the limit.
5. Consider staged copy for Synapse or Snowflake sinks.

If the source is the bottleneck, more DIUs will not help.

Work from the machine outwards. An offline IR usually means the node can't reach ADF, not that ADF is down.

1. Confirm the Integration Runtime Service (DIAHostService) is running on the node.
2. Check outbound HTTPS 443 to the ADF endpoints through firewall and proxy rules.
3. Open the Integration Runtime Configuration Manager and check registration and the authentication key.
4. Review Event Viewer logs under Integration Runtime for errors.
5. Make sure the machine isn't asleep, out of memory, or mid auto-update.

With multiple nodes registered, one failing node won't stop the IR.

Each mapping data flow run needs a Spark cluster. When a run starts and none is warm, ADF spins one up, which typically takes a few minutes. That is the cold start people notice.

Time to live (TTL) on the Azure IR keeps the cluster alive for a set time after the last run, so back-to-back data flows start in seconds. The trade-off is that you pay for the idle warm time.

Compute type and core count are set on the same IR. Set a TTL for pipelines with many sequential data flows; leave it at zero for one-off nightly runs.

Use an event-based trigger. It listens to Azure Event Grid for blob events on a storage account, so the pipeline starts as soon as a file arrives.

1. Register the Microsoft.EventGrid resource provider on the subscription.
2. Create the trigger, choose the storage account, container and Blob created event.
3. Add optional begins with / ends with filters, for example .csv.
4. Map trigger values to pipeline parameters.

folderPath = @triggerBody().folderPath
fileName   = @triggerBody().fileName

Custom Event Grid topics are also supported for non-storage events.

A tumbling window trigger can depend on another tumbling window trigger, or on itself. The dependent window only starts once the window it depends on has finished successfully.

• Offset: which upstream window to wait for, relative to the current one (for example the previous day).
• Size: how many upstream windows must be complete.
• Self-dependency: a window waits for the previous window of the same trigger, enforcing strict order.

This lets you chain staging, transform and reporting pipelines on matching time slices without writing custom polling logic. Combine it with max concurrency to control how many windows run at once.

Each dependency line between activities has one of four conditions: Succeeded, Failed, Skipped, Completed. Connecting activities with different conditions builds try/catch behaviour.

On a Failed path, log the error with a Stored Procedure or Web call using @activity('Copy1').error.message. The pipeline status is judged from its leaf activities, so a handled failure can still report Succeeded; add a Fail activity if the run must show as failed.

ADF bills for orchestration (activity runs), data movement (DIU-hours) and data flow (vCore-hours), plus any running Azure-SSIS IR. Most savings come from those three meters.

• Use Copy instead of a data flow when no transformation is needed.
• Tune DIUs and avoid over-provisioning small copies.
• Set data flow TTL carefully and pick the smallest core count that meets the SLA.
• Stop the Azure-SSIS IR when idle.
• Avoid tight Until + Wait polling loops, because each iteration counts as billed activity runs.
• Filter and compress at the source to move less data.

Both use the same pipeline engine, activities and connectors. The difference is the surroundings.

Skills transfer almost 1:1 between the two.

When a trigger fires or you click Run, ADF creates a pipeline run with a unique run ID, binds parameter values and starts scheduling activities in the order set by their dependencies.

sequenceDiagram
participant T as Trigger
participant S as ADF Service
participant IR as Integration Runtime
participant D as Data Stores
T->>S: Start pipeline run
S->>S: Assign run ID and evaluate parameters
S->>IR: Dispatch Copy / Data Flow activity
IR->>D: Read source, write sink
IR-->>S: Status and output
S->>S: Evaluate dependsOn, run next activity
S-->>T: Run status to Monitor hub

Control activities such as If Condition and ForEach are handled by the ADF service itself, while Copy, data flow and external activities are dispatched to the IR. When all leaf activities finish, the run is marked Succeeded, Failed or Cancelled.

When a Copy starts, ADF picks the integration runtime from the linked service, then the IR connects to the source and sink using the connector settings.

flowchart LR
A["Source connector"] --> B["Partitioned parallel reads"]
B --> C["In-memory buffer and format conversion"]
C --> D["Optional staging in Blob/ADLS"]
C --> E["Sink connector writes"]
D --> E

Parallelism comes from the DIUs and the parallel copies setting, which split work across threads or partitions. Data is streamed through buffers, converted or compressed on the way, and optionally staged for bulk loads such as PolyBase. With fault tolerance on, incompatible rows are skipped and logged instead of failing the copy.

A metadata-driven framework replaces dozens of near-identical pipelines with one generic pipeline controlled by a configuration table.

CREATE TABLE etl.Control (
  Id INT, SourceSchema VARCHAR(50), SourceTable VARCHAR(100),
  SinkPath VARCHAR(200), LoadType VARCHAR(20),   -- full / incremental
  WatermarkColumn VARCHAR(100), IsActive BIT);

1. Lookup reads the active rows from the control table.
2. ForEach loops over them, with batch count tuned to the source.
3. A Switch on LoadType routes to the full or incremental copy.
4. Datasets and linked services are parameterized from @item().
5. A stored procedure logs each run's status, row count and errors to an audit table.

Adding a new source becomes an insert into the control table, not a new pipeline. Restartability comes from the audit table.