ADF / Fabric pipeline Lookup activity fails or silently truncates: the 5,000-row and 4 MB limits and the patterns that replace it

#Symptoms

• A metadata-driven pipeline (Lookup a control table → ForEach → Copy) suddenly stops loading some tables, with no failure. The control table grew past 5,000 rows.
• The Lookup activity fails with an error that its output exceeded the maximum size.
• A ForEach that used to run 300 iterations now runs exactly 5,000 and stops.

#The limits

The Lookup activity is meant for small results: config rows, watermarks, a list of tables. Documented limits (check the current ADF limits page; values can change):

The row limit is the dangerous one, because it truncates silently.

#Step 1: Detect truncation

Check the Lookup output in the run's activity output:

{
  "count": 5000,
  "value": [ ... ]
}

A count of exactly 5,000 means you're probably truncated. Compare with the real row count:

SELECT COUNT(*) FROM etl.control_tables WHERE is_enabled = 1;

Add a guard in the pipeline so this fails loudly instead of quietly. After the Lookup, an If Condition with:

@greaterOrEquals(activity('LookupTables').output.count, 5000)

and a Fail activity in the True branch with message "Lookup returned 5000 rows; control table has grown past the Lookup limit".

#Step 2: Reduce what you return

Often the rows are fine, but each row is wide (long JSON configs, column lists). Return only what the ForEach needs:

-- Instead of SELECT *
SELECT table_id, source_schema, source_table, target_path
FROM   etl.control_tables
WHERE  is_enabled = 1 AND load_group = '@{pipeline().parameters.LoadGroup}';

Fetch the detailed config per table inside the loop (a second, firstRowOnly: true Lookup by table_id). That keeps the main Lookup well under 4 MB.

#Step 3: Split work into batches (most common fix)

Add a batch_id (or use table_id % N) to the control table and run one Lookup per batch from a parent loop.

-- Batch list (small: one row per batch)
SELECT DISTINCT batch_id
FROM   etl.control_tables
WHERE  is_enabled = 1;

Parent pipeline:

Lookup "GetBatches" (firstRowOnly = false)   -- returns e.g. 12 rows
└── ForEach batch  (sequential or limited batch count)
     └── Execute Pipeline "LoadBatch" (parameter: BatchId = @item().batch_id)

Child pipeline LoadBatch:

SELECT table_id, source_schema, source_table, target_path
FROM   etl.control_tables
WHERE  is_enabled = 1 AND batch_id = @{pipeline().parameters.BatchId};

Keep each batch well under 5,000 rows (for example ≤ 1,000) to leave room for growth.

Assign batches in SQL:

UPDATE etl.control_tables
SET    batch_id = (table_id % 20) + 1
WHERE  batch_id IS NULL;

Tip

A nested ForEach isn't allowed directly in ADF. The Execute Pipeline activity is the standard way to get a "loop inside a loop".

#Step 4: Paging with Until (when batches don't fit your model)

Page through the table with an offset variable:

SELECT table_id, source_schema, source_table
FROM   etl.control_tables
WHERE  is_enabled = 1
ORDER  BY table_id
OFFSET @{variables('Offset')} ROWS FETCH NEXT 1000 ROWS ONLY;

Pipeline outline:

Set variable Offset = 0
Until  @less(activity('LookupPage').output.count, 1000)
  ├── Lookup "LookupPage"
  ├── Execute Pipeline "ProcessPage" (pass the page as an array parameter)
  └── Set variable Offset = @string(add(int(variables('Offset')), 1000))

ADF variables are strings, arrays or booleans in older versions (integer support was added later), hence the int()/string() conversion. You also can't reference a variable in its own Set Variable activity, so use a temporary variable to hold the new value, then copy it back.

#Step 5: Watch ForEach limits too

• ForEach handles up to 100,000 items, and runs up to 50 iterations in parallel (batchCount, default 20). Check the current limits page.
• Large parallel counts can overload the source database or the self-hosted integration runtime. Throttle with batchCount.
• Every activity run is billed. Thousands of tiny copy runs cost more than fewer, bigger ones; consider grouping small tables into one copy where possible.

#Common errors and what they mean

#Prevent it next time

• Add the 5,000-row guard to every Lookup that returns lists, even small ones today.
• Design control tables with batch_id from the start.
• Monitor control table growth (row count per load group) and alert well before the limit.
• Log, per run, how many tables were expected vs processed, and fail the run if they differ.