Azure Data Factory (ADF) Interview Questions

Azure Data Factory (ADF) interview questions change shape as the role gets more senior. Junior rounds ask what a linked service is. Senior rounds hand you a marker and a problem: "We have 12,000 tables to load every night. Design it." The interviewer is not checking whether you know ADF's vocabulary; they are checking whether you know where it breaks.

Most of those exercises are built on a handful of documented limits. This article works through six of them. For each: the problem as an interviewer would pose it, the trap, and a model answer. For the fundamentals and a wider question bank, see the ADF interview questions and answers.

Three of the exercises involve sizing, so here is a small calculator that applies the documented limits to assumed workloads. It does not run ADF; it shows the arithmetic you should be able to do on the whiteboard.

import math, random

# Documented limits (Microsoft Learn): Lookup returns at most 5,000 rows and 4 MB of output;
# ForEach runs at most 50 iterations in parallel (batchCount default 20) over at most 100,000 items;
# a tumbling window trigger's maxConcurrency is 1 to 50.
LOOKUP_ROWS, LOOKUP_BYTES = 5000, 4 * 1024 * 1024
FOREACH_MAX_BATCH, FOREACH_MAX_ITEMS = 50, 100_000

# Exercise 1: a control table lists 12,000 source tables
tables, bytes_per_row = 12_000, 350          # assumption: about 350 bytes of JSON per control row
print("E1 control table with", f"{tables:,}", "rows")
print(f"   one Lookup returns {min(tables, LOOKUP_ROWS):,} rows; {tables - LOOKUP_ROWS:,} tables silently skipped")
pages = math.ceil(tables / LOOKUP_ROWS)
print(f"   paged design: {pages} pages of up to {LOOKUP_ROWS:,} rows,",
      f"each about {LOOKUP_ROWS * bytes_per_row / 1024 / 1024:.2f} MB (limit 4 MB)")

# Exercise 4: backfill 90 days of hourly windows with a tumbling window trigger
windows, run_minutes = 90 * 24, 6            # assumption: each window's pipeline run takes 6 minutes
print(f"\nE4 backfill: {windows:,} hourly windows, {run_minutes} min per run")
for mc in (1, 10, 50):
    rounds = math.ceil(windows / mc)
    print(f"   maxConcurrency {mc:>2}: {rounds:>5} rounds -> at least {rounds * run_minutes / 60:6.1f} hours")

# Exercise 6: copy 300 tables in a parallel ForEach; most are small, a few are large
random.seed(7)
durations = [random.uniform(1, 4) for _ in range(290)] + [random.uniform(40, 70) for _ in range(10)]
total, longest = sum(durations), max(durations)
print(f"\nE6 300 copies, {total:,.0f} minutes of work in total, longest single copy {longest:.0f} minutes")
for batch in (1, 20, 50):
    lower = max(total / batch, longest)       # no schedule can beat either bound
    print(f"   batchCount {batch:>2}: wall clock at least {lower:6.1f} minutes")

E1 control table with 12,000 rows
   one Lookup returns 5,000 rows; 7,000 tables silently skipped
   paged design: 3 pages of up to 5,000 rows, each about 1.67 MB (limit 4 MB)

E4 backfill: 2,160 hourly windows, 6 min per run
   maxConcurrency  1:  2160 rounds -> at least  216.0 hours
   maxConcurrency 10:   216 rounds -> at least   21.6 hours
   maxConcurrency 50:    44 rounds -> at least    4.4 hours

E6 300 copies, 1,244 minutes of work in total, longest single copy 67 minutes
   batchCount  1: wall clock at least 1244.2 minutes
   batchCount 20: wall clock at least   66.9 minutes
   batchCount 50: wall clock at least   66.9 minutes

Exercise 1: "Load every table listed in our control table"

The setup. A control table lists 12,000 source tables with their schema, watermark column and target. Design a metadata-driven load.

The trap. The textbook answer is Lookup into ForEach. But a Lookup returns at most 5,000 rows; if the result has more, it returns the first 5,000, and its output is capped at 4 MB, beyond which the activity fails (Microsoft Learn: Lookup). In the calculator, 7,000 tables would be skipped with no error.

Model answer. Page the control table. An outer pipeline looks up the page numbers, loops over them and calls an inner pipeline that looks up one page of up to 5,000 rows and loops over its tables. This is the two-level pattern the documentation recommends. Mention the size limit too: at about 350 bytes per row, a full page is about 1.7 MB, safely under 4 MB. Wide rows with long JSON column mappings could break that, so keep the control rows lean. Strong candidates add a run-log table so a failed night can be restarted for just the failed tables. The metadata framework guide covers the control-table design itself.

Exercise 2: "Count the rows loaded across the loop"

The setup. The ForEach copies tables in parallel. After each copy, a Set Variable activity adds the row count to a running total for an audit email.

The trap. Pipeline variables are global to the pipeline, not scoped to the loop, so the documentation says Set Variable cannot be used inside a parallel ForEach (Microsoft Learn: ForEach). Parallel iterations would overwrite each other.

Model answer. Do not accumulate in a variable. Have each iteration write its row count to an audit table, then sum it in one query after the loop. If you must collect values in the pipeline, Append Variable into an array is the documented way to aggregate ForEach outputs; or run the loop sequentially, which is slower. Mentioning the audit table shows production thinking: it also gives you history.

Exercise 3: "For each source system, load each of its tables"

The setup. Five source systems, each with its own list of tables. The candidate sketches a ForEach inside a ForEach.

The trap. A ForEach cannot be nested inside another ForEach or an Until loop. The same documentation also caps a single ForEach at 100,000 items.

Model answer. The outer pipeline loops over source systems and calls a child pipeline through Execute Pipeline, passing the system as a parameter; the child loops over that system's tables. Point out the side benefits: each child run is visible separately in monitoring, can be rerun on its own, and has its own variables, which also solves Exercise 2.

Exercise 4: "Backfill 90 days of hourly data"

The setup. A new hourly pipeline must also process the last 90 days. The candidate proposes a tumbling window trigger with a start time 90 days ago.

The trap. It works, but it is slow and blocks the present. A start time in the past generates backfill runs from the oldest window to the newest, and all of them run before future windows; concurrency is capped by maxConcurrency, between 1 and 50 (Microsoft Learn: tumbling window triggers). In the calculator, 2,160 windows of six minutes each take at least 21.6 hours at a concurrency of 10, and 4.4 hours even at 50, with today's data waiting behind them.

Model answer. Follow the documentation's own advice: do an initial historical load as a separate bulk job, then start the tumbling window trigger from the cut-over point. Also check that the source can sustain 50 concurrent queries before raising maxConcurrency, and remember that frequency and interval cannot be changed after the trigger is published.

Exercise 5: "The hourly sales load must wait for the hourly customer load"

The setup. Two hourly loads, sales and customers. Sales for an hour must not run until customers for that hour have loaded, and it must not run before its own previous hour has finished.

The trap. Candidates reach for a schedule trigger and a Wait activity, or a polling loop. Neither guarantees correctness when one hour fails.

Model answer. Use tumbling window triggers with dependencies: the sales trigger depends on the customer trigger for the same window, and on itself with a negative offset of one hour, which the documentation calls a self-dependency. Add a trigger retry policy for transient failures. When an upstream window fails and is rerun, the downstream windows wait on it rather than processing incomplete data. The triggers guide compares the trigger types.

Exercise 6: "The nightly copy of 300 tables is too slow. Speed it up."

The setup. A ForEach copies 300 tables with the default batch count. The run takes over an hour.

The trap. The reflex answer is to raise batchCount from its default of 20 to the maximum of 50. But batch count is only an upper limit, and here it does not matter. In the calculator, 290 small tables and 10 large ones add up to 1,244 minutes of work; at a batch count of 20 or 50, the run still takes at least 67 minutes, because one large table takes 67 minutes on its own.

Model answer. Ask for the run durations before changing anything. Then work on the long tail: load the largest tables incrementally instead of in full, use the Copy activity's partitioned reads for them, and start them first, in their own loop, so they do not end up starting last. Only then consider the batch count, and check that the source database can handle more parallel reads. Interviewers like this answer because it starts with measurement.

How to handle a design round

• Restate the problem with numbers: how many tables, how big, how often, how late can it finish.
• Name the limits before drawing: Lookup 5,000 rows and 4 MB, ForEach 50 in parallel, no nested loops.
• Design for failure: restart from the failed item, not from the beginning.
• Show the arithmetic: two lines of sizing beat a confident guess.

The ForEach, If and Until guide covers the loop activities in depth.

ADF design, orchestration and interview preparation are part of the Data Engineering course.

Sources

• Lookup activity (Microsoft Learn) — Microsoft
• ForEach activity (Microsoft Learn) — Microsoft
• Create tumbling window triggers (Microsoft Learn) — Microsoft