How to use the Lookup activity in Azure Data Factory

The Lookup activity in Azure Data Factory lets you fetch values from a table or file and use them to control the rest of the pipeline — for example, reading the date of the last load or the list of tables to process. Instead of hard-coding values inside the activities, the pipeline decides what to do based on the data itself. Mastering this activity is what separates a rigid pipeline from one that adapts to the data on its own.

Prerequisites

• An existing Azure Data Factory instance.
• A Linked Service and a Dataset pointing to the source (for example, Azure SQL Database).
• A control table with data to read (e.g., load dates or a list of tables).
• Permissions to create and publish pipelines in ADF Studio.

Step 1: Prepare the source table

The Lookup activity always reads from a Dataset. In this example we will use a small control table in Azure SQL Database that stores the list of tables to load — a very common pattern in ETL scenarios. If the table does not exist yet, create and populate it with a simple query:

CREATE TABLE dbo.LoadList (
    SchemaName NVARCHAR(50),
    TableName  NVARCHAR(100),
    Enabled    BIT
);

INSERT INTO dbo.LoadList VALUES
('dbo','Clientes',1),
('dbo','Encomendas',1),
('dbo','Produtos',0);

We end up with two active tables (Enabled = 1) and one disabled. This lets us show, later on, how to return only the rows that matter.

Step 2: Add the Lookup activity to the pipeline

In ADF Studio, open the Author tab and create a new pipeline with the + button. In the Activities pane on the left, expand the General category and look for Lookup. Drag the activity onto the canvas and give it a clear name, such as LookupTables. Lookup lives in the General category because it does not move data — it only reads it to make decisions.

Step 3: Configure the source and the query

Select the Lookup activity and open the Settings tab. In Source dataset, choose the Dataset that points to Azure SQL Database. Next, in the Use query field, select Query instead of Table — this way you control exactly which columns and rows are returned, which is more efficient than reading the whole table:

SELECT SchemaName, TableName
FROM dbo.LoadList
WHERE Enabled = 1;

Finally, turn off the First row only option. When it is on, Lookup returns only the first row, which is ideal for reading a single value (such as a date). When it is off, it returns all rows as an array — exactly what we need to iterate over several tables.

The Lookup activity returns a maximum of 5000 rows and 4 MB of data. For larger volumes, filter in the query or split the processing into batches.

Step 4: Use the result in other activities

The Lookup result is available as an expression. To read a single value (with First row only on) you would use:

@activity('LookupTables').output.firstRow.TableName

Since we turned that option off, we have an array in output.value and the count in output.count. Add a ForEach activity next and, in the Items field, enter:

@activity('LookupTables').output.value

Inside the ForEach, each iteration exposes the current row. To access the table name for that iteration, use @item().TableName. Note that the name in quotes in @activity('LookupTables') must match exactly the name you gave the activity, otherwise the expression fails validation.

Verify the result

Click Debug to run the pipeline without publishing. In the Output tab, hover over the LookupTables activity and click the output icon: you should see a JSON object with count equal to 2 and a value array containing the two active tables. If the ForEach runs twice, everything is working.

Conclusion

With the Lookup activity you now feed the pipeline from real data instead of fixed values — the first step toward truly dynamic, reusable pipelines. As a next step, try turning First row only on to read a watermark date and run incremental loads. Which value from your control table would you like to stop hard-coding in the pipeline?