Azure Data Factory Interview Questions

"I think of it in layers. A linked service is like a connection string: it says which storage account or database to reach and how to log in. A dataset sits on top of a linked service and points at the actual data, say one SQL table or a folder of Parquet files. Activities are the steps, like a Copy, a Lookup or a Databricks notebook, and a pipeline is the group of activities plus the order they run in. The integration runtime is the compute that actually moves the bytes or hands work to another service, and it can be Azure-hosted or self-hosted inside a private network. Finally, a trigger starts the pipeline on a schedule, on a time window or when a file lands. So a nightly load is two linked services, two datasets, one copy activity in a pipeline, and a schedule trigger."

"Lookup reads data. I point it at a dataset or give it a query, and it returns either the first row or the whole result, which later activities read from its output. It's meant for small results, because it has a row and size limit, so it's for control data like the last watermark or a list of tables to loop over, not for moving data. Get Metadata reads facts about data instead of the data itself. I ask for fields like exists, childItems, lastModified or structure. So if I need to list the files in a landing folder, or check today's file is there before a copy starts, Get Metadata is the tool. A pattern I use a lot is Get Metadata to list the files, a Filter activity to keep the ones I want, then a ForEach over what's left."

"ForEach takes an array, usually from a Lookup or a Get Metadata output, and runs the activities inside it once per item, with @item() giving me the current element. By default iterations run in parallel, and batch count sets how many run at once. If I tick sequential, they run one by one. Two things bite people in parallel mode. First, variables belong to the whole pipeline, so if each iteration does a Set Variable, they overwrite each other and later steps read random values. I avoid that by using @item() directly or by calling a child pipeline with Execute Pipeline, which gets its own parameters. Second, you can't put a ForEach directly inside another ForEach, so the inner loop goes into a child pipeline too. I also keep batch count low enough that the source database isn't hit by dozens of queries at once."

"The integration runtime is the compute Data Factory uses to run activities. The Azure integration runtime is fully managed by Microsoft. It handles copies between cloud stores it can reach, runs mapping data flows, and dispatches work to other services like a Databricks notebook or a stored procedure. The self-hosted integration runtime is software I install on a machine inside my own network, on-premises or in a private virtual network, so the factory can reach data that isn't exposed to the internet. It only makes outbound connections, so no inbound firewall ports are needed. The Azure-SSIS integration runtime is a managed cluster built to run existing SSIS packages in the cloud. Which runtime a copy uses is set on the linked service with connect via. If I don't set it, the default Azure runtime is used."

"I'd use a self-hosted integration runtime. First I create it in the factory, which gives me an authentication key. Then I install the runtime on a Windows machine inside the network that can reach the SQL Server, and register it with that key. The part the network team likes is that it only needs outbound HTTPS to Azure, so nothing has to be opened inbound. Then I create a SQL Server linked service that connects via that runtime, and I keep the password in Key Vault instead of typing it into the linked service. For production I'd install it on at least two machines registered to the same runtime, so a patch or a reboot doesn't stop the loads, and the extra node also shares the work. I'd also monitor that machine's CPU and memory, because it becomes the throughput limit."

"The Azure-SSIS integration runtime is a managed cluster that runs SQL Server Integration Services packages in Azure. It's the lift-and-shift option. A team with hundreds of existing packages can deploy them largely as they are, into an SSIS catalog hosted in Azure SQL Database or SQL Managed Instance, or from files, and run them with the Execute SSIS Package activity inside a pipeline. I'd pick it when there are many packages, they work, and rewriting them would take months for little gain. The trade-offs: you pay while the cluster is running, so teams usually start it before the batch window and stop it afterwards from a pipeline. And if packages read on-premises sources, you need to join it to a virtual network or route through a self-hosted runtime as a proxy. For new work I'd build native pipelines instead."

"A schedule trigger fires on a wall-clock plan, like every day at two in the morning. It's fire and forget: it doesn't track whether earlier runs succeeded, and one schedule can start several pipelines. A tumbling window trigger slices time into fixed, back-to-back windows that don't overlap, and it keeps state for each window. That gives me things a schedule doesn't: I can set a start date in the past to backfill, each window has its own retry policy, I can limit how many windows run at once, it can depend on another tumbling window trigger, and it passes the window start and end to the pipeline. The catch is that it's tied to a single pipeline. A storage event trigger fires when a blob is created or deleted, through Event Grid, so it suits loads that should start as soon as a file lands."

"In the trigger definition I map two pipeline parameters to the trigger outputs, windowStartTime and windowEndTime. The pipeline doesn't need to know it was started by a trigger; it just receives a start and an end. In the copy source query I use string interpolation to drop those values into the WHERE clause. The important detail is the boundaries: greater than or equal to the start, strictly less than the end. That way a row stamped exactly on the hour belongs to one window only. Because each window is fixed, rerunning an old window reads exactly the same rows. So I make the sink match that, either writing to a folder named after the window and overwriting it, or merging on the key, so a retry or a backfill never doubles the data."

"I'd use a storage event trigger on blob created, filtered with a path prefix for the container and folder, and a file ending like .csv, so a stray log file doesn't start a run. The trigger passes the folder path and file name, which I map to pipeline parameters with triggerBody().folderPath and triggerBody().fileName, so the pipeline processes exactly that file. It works through Event Grid, so the Event Grid resource provider has to be registered on the subscription. What goes wrong in practice: the partner uploads twenty files and I get twenty runs, which may need a concurrency limit or a design that processes a batch. Some partners write under a temporary name and rename, or send an empty marker file, so I filter on the final name. And if a file is re-uploaded, the pipeline runs again, so the load has to be safe to repeat."

"I start in the monitoring details of that copy run, because it breaks the time into stages: queue time, time to first byte, reading from the source and writing to the sink. Long queue time usually means the self-hosted runtime is busy, so I check its CPU, memory and how many jobs it's running at once. A long time to first byte points at the source query, maybe a missing index or a table that grew a lot. If reading is steady but slow, I use the partition option on the source, physical partitions or a dynamic range on an indexed column, so several connections read in parallel, and set parallel copies to match. If the runtime machine is the limit, I scale it up or add nodes. For cloud-to-cloud copies on the Azure runtime, more data integration units help. And I compare with an older fast run to see which number changed."

"A mapping data flow is Data Factory's visual way to build transformations: joins, aggregates, derived columns, lookups, conditional splits and so on, drawn as a graph instead of written as code. Behind the scenes it's turned into Spark code and run on a Spark cluster that Data Factory manages for me, sized by the Azure integration runtime settings like compute type and core count. I never manage the cluster myself. That tells you when to use it. It's good for moderate transformations by a team that wants to stay low-code inside the factory. For a simple move with no changes, a copy activity is cheaper and faster. For very complex logic, or code the team wants to unit test, I'd rather write a Databricks notebook. And because a cluster has to start, a data flow has start-up time even for tiny inputs."

"The key piece is the Alter Row transformation just before the sink. It tags each row with a policy based on a condition: insert if, update if, upsert if or delete if. For a plain merge I add an Alter Row with upsert if set to true(). Then on the sink, which has to be one that supports it, like Azure SQL, Synapse or a Delta table, I tick allow upsert and choose the key columns it matches on. Without Alter Row, the sink only inserts. If I care about which rows really changed, I first join to the target, compare a hash of the business columns, and pass through only rows that are new or different, so unchanged rows aren't rewritten every run. The same idea stretches to keeping history: expire the current row and insert a new version."

"For a dedicated SQL pool sink the copy activity gives me three load methods: the COPY statement, PolyBase and bulk insert. Bulk insert pushes batches of rows through the pool's front end, so it's fine for small tables but slow for big ones. COPY and PolyBase let the pool's compute nodes read the files from storage in parallel, which is far faster, and I default to COPY because it's the recommended option and more flexible about file formats. If the source is already Parquet or delimited text in the lake, the pool can load it directly. If it isn't, say an on-premises SQL table, I turn on staged copy. The copy writes to a staging storage account first, the pool loads from there, and the staging files are cleaned up afterwards. I usually land the data in a staging table with a truncate in the pre-copy script, then a stored procedure merges it into the real table."

"I use the Databricks Notebook activity with an Azure Databricks linked service. On the linked service I choose the compute: a new job cluster created for the run, an existing interactive cluster, or an instance pool. For authentication I prefer managed identity, or a token kept in Key Vault. On the activity I set base parameters, which arrive in the notebook as widgets, so inside the notebook I read them with dbutils.widgets.get. To send something back, the notebook ends with dbutils.notebook.exit and a string, often a small JSON with row counts. In the pipeline, the next activity reads it from the notebook activity's output under runOutput. For scheduled production runs I use job clusters, because they're cheaper and isolated, and keep interactive clusters for development, where fast start-up matters more."

"I treat Data Factory mainly as the orchestrator and the mover. Copying, file handling, scheduling, retries, dependencies and alerts all sit well in ADF. Light transformations, like renaming columns, filtering or a simple join, I can do in a mapping data flow or push down as SQL through a stored procedure or a script activity. Once the logic gets heavy, many joins across big tables, complex business rules, or anything the team wants in version-controlled code with unit tests, I move it into Databricks notebooks or jobs, and ADF just calls them with parameters and waits. If the data already lives in a Synapse dedicated SQL pool, running the transformation there as SQL keeps the work next to the data and avoids moving it out and back. My rule is simple: move data with ADF, transform it where the compute and the team's skills already are."

"Parameters are inputs. Whoever starts the run, a trigger, a parent pipeline or me running it by hand, sets them at the start, and they can't change during the run. I use them for things like a table name or a run date. Variables are working values inside a run. I change them with Set Variable or Append Variable, for example to build up a list of failed tables or hold a flag, and they're scoped to the whole pipeline. Global parameters live at the factory level and every pipeline can read them, so they suit constants like an environment name or a base storage path. They're also handy in CI/CD, because I can override them per environment at deployment. One thing to remember: a child pipeline can't see its parent's variables, so anything it needs has to be passed in as a parameter."

"Anything that starts with an @ sign is evaluated as an expression. I'd pass the run date in as a pipeline parameter, usually from the trigger, rather than calling utcNow inside the pipeline, because then a rerun for an old date builds the old path instead of today's. Then I use formatDateTime with the format yyyy/MM/dd, which gives the year, month and day with slashes between them. I can wrap the whole thing in concat, or use string interpolation, where I write the plain text and put only the expression inside @{ and }. Interpolation reads better when there's more text than logic. Month is capital MM, and lowercase mm is minutes. Mixing them up is the classic bug, and it only shows up as oddly named folders."

"I keep a small control table with one row per source table and its last watermark, usually a modified timestamp or an increasing ID. The pipeline starts with a Lookup that reads the old watermark. A second Lookup gets the current maximum from the source, so this run has a fixed upper bound. Then a Copy activity reads only rows where the column is greater than the old watermark and less than or equal to the new one. Only after the copy succeeds does a Stored Procedure activity write the new value back to the control table. That order matters: if the copy fails, the watermark doesn't move, and the next run picks up the same rows again. On the sink side I merge on the key rather than blindly append, so a repeated run can't create duplicates."

"I'd make it metadata-driven. A control table holds one row per table: schema, name, load type, watermark column, last watermark, target folder and an enabled flag. A parent pipeline does a Lookup on that table and feeds the rows into a ForEach with a batch count the source can handle. Inside, an Execute Pipeline calls a child pipeline with the row as parameters, and the child uses a Switch on load type to run either a full copy or the watermark pattern. The datasets and linked services are parameterised, so one source dataset and one sink dataset serve every table. Each child run writes start time, row counts and status to a log table, so I can see which table failed and rerun just that one. Adding a table becomes an insert into the control table, not a deployment."

"First I'd get the exact order IDs and check them in the source: their modified timestamps and, if I can, when they were actually committed. Then I'd compare with the watermark values each run used, which I log. The usual cause is a row committed late with an older timestamp. A long transaction stamps the row at ten past, but only commits after my run already took a maximum of quarter past, so the next run starts above it and the row is skipped for good. Other causes are a writer that doesn't update the modified column, a time zone mix-up between the source and the control table, or the watermark moving before the copy finished. To fix the gap, I reload the affected window. To prevent it, I re-read a short overlap behind the watermark and merge on the key so repeats are harmless, or move to change tracking or CDC."

"Each arrow between activities has a condition. On success runs the next step only if the previous one succeeded, on failure only if it failed, on completion either way, and on skipped when the previous activity never ran. So try-catch is an activity with a failure path to a handler that logs the error or sends an alert. The subtle part is the pipeline's final status. Data Factory looks at the leaf activities, the ones at the end of each path. If my only follow-up is the failure handler and it succeeds, the pipeline shows as succeeded, even though the real work failed. That hides the failure from monitoring and from any parent pipeline. So after logging I add a Fail activity with a clear message and error code, which marks the run as failed on purpose. For transient errors I also set retry count and interval on the activity."

"Day to day I use the Monitor hub, which shows pipeline runs, activity runs and trigger runs, with inputs, outputs and error messages for each activity. From there I can rerun a pipeline from the failed activity instead of from the start. For alerts, I create an Azure Monitor alert on the failed pipeline runs metric, filtered to the pipelines that matter, and point it at an action group that emails the team or notifies whoever is on call. The Monitor hub only keeps run history for a limited time, so I also send diagnostic logs to a Log Analytics workspace. That gives me longer history and lets me query failures and durations across pipelines, which is how I spot a job slowly getting longer before it breaks. For business checks, like zero rows copied, I log row counts myself and alert on those."

"I'd start with cost analysis for the factory, broken down by meter, because Data Factory bills separately for orchestration, data movement, data flows and external activities, and the meter tells me which kind of work grew. Then in the Monitor hub I'd open the consumption details of the new pipeline runs to find the few heaviest ones. The usual culprits are a trigger firing far more often than intended, like a storage event trigger running once per small file; data flows with a long time to live keeping clusters alive between runs; copies using more data integration units than they need; or a ForEach doing thousands of tiny activity runs where one bulk copy would do. Once I know which, the fixes are usually simple: batch the files, shorten the time to live, cap the data integration units or merge activities. Then I'd report the cause and the saving back."

"Only the dev factory is connected to Git. Developers work in feature branches and raise pull requests into the collaboration branch, and that's where review happens. The thing we deploy is an ARM template of the whole factory. The older way was pressing Publish in dev, which writes the templates to the adf_publish branch. The better way now is automated publishing: a build pipeline uses Microsoft's npm package for Data Factory to validate the code and generate the template from the collaboration branch, so nobody has to click Publish. A release pipeline then deploys that template to test and production, overriding parameters per environment, like linked service URLs and Key Vault names. Before deploying I stop the active triggers and afterwards start them again, using the pre- and post-deployment script Microsoft provides. Test and production are never edited by hand."

"I'd use the factory's managed identity. Every factory can have a system-assigned identity in Microsoft Entra ID, and I can attach a user-assigned one too. For the storage account I give that identity a data role, like Storage Blob Data Contributor, scoped to the account or the container, and pick managed identity as the authentication type on the linked service. For Azure SQL I create a contained database user for the factory from Entra ID and grant it only the roles it needs. Then there are no secrets at all. For sources that can't use Entra ID, like a partner's database with a username and password, I store the secret in Key Vault, give the factory's identity permission to read secrets, and have the linked service reference the secret by name. Rotating the password then means updating Key Vault, not redeploying the factory."

"There are a few routes, and I'd pick based on where the data is and how strict the team is. The cleanest for cloud sources is the managed virtual network. I create an Azure integration runtime inside Data Factory's managed virtual network, then add a managed private endpoint to the storage account. The storage owner has to approve that endpoint, and after that the traffic goes over a private link instead of the public internet. The second route is a self-hosted integration runtime on a virtual machine inside a virtual network that already has a private endpoint to the storage. The third is the storage firewall exception for trusted Microsoft services, combined with managed identity authentication, which some security teams accept and some don't. I'd also mention that the factory itself can have private endpoints, so authoring and control traffic stay private too."

"First, I'd thank them, because they kept the business running, and I'd confirm exactly what they changed, ideally by comparing the production pipeline's JSON with what's in the collaboration branch. Then I'd get the same fix into Git straight away through a normal pull request in dev, so the next release carries it instead of wiping it. Before that release goes out, I'd check the fix is actually in the built template. Then we'd talk as a team about why a direct edit felt necessary. Usually it means the release path is too slow for an emergency, so I'd suggest a short hotfix route: a branch from what's deployed, a quick review, and a fast-tracked release. Longer term, I'd limit who can edit production so the pipeline is the only way in, but only once that fast path exists."

"At my last company a nightly pipeline loading sales from an on-premises system started failing every few nights with a timeout on the copy activity. The run details showed the time wasn't spent reading data; the copy sat in the queue for ages. That pointed at the self-hosted runtime, not the source. When I checked the machine, three other teams had started using the same runtime, and at two in the morning everyone's jobs landed at once, so CPU sat near full. Short term I moved our start time and lowered the number of jobs the node would run at once. Longer term we added a second node, moved the heaviest team onto their own runtime, and set a CPU alert on the machines. I also added a failure alert with the activity error in the message, because the first time we heard about it was from an analyst."

"In my last role we moved about forty SSIS packages and a pile of scheduled scripts onto Data Factory. We sorted them into three groups: simple copies we rebuilt as metadata-driven copy pipelines, packages with heavy logic we ran unchanged on the Azure-SSIS runtime at first, and scripts we rewrote as stored procedures called from pipelines. The hard part wasn't the tooling. It was hidden behaviour: packages that quietly trimmed spaces, turned empty strings into nulls, or relied on the server's local time zone. Our first parallel run showed small mismatches in a handful of tables, and each one traced back to one of those habits. So we ran old and new side by side for two weeks, compared row counts and totals per table every day, and only switched off an old job once its table matched several days in a row."

"When I joined my last team, people published straight from the dev factory, then someone exported the template by hand and imported it into production. Twice in a month a half-finished pipeline went live because two developers shared one branch, and once a production linked service ended up pointing at the dev database. I proposed a simple setup: feature branches with pull requests, automated template builds from the main branch, and a release pipeline that deployed to test and then production with per-environment parameters and an approval before production. I added the script that stops triggers before deployment and restarts them after. I paired with each developer on their first pull request so it didn't feel like red tape. After that, nobody deployed by hand, and the wrong-database mistake couldn't happen because every environment's connections came from its own parameter file."