Introduction: A Common but Confusing Lookup Failure
Have you ever used a SQL query inside a Lookup activity that runs for more than 120 minutes in Azure Data Factory or Fabric pipelines?
If yes, you may have seen the Lookup activity fail with a query timeout error, even though the Activity Timeout under the General section is set to the default 12 hours.
At first glance, this looks confusing and even misleading. Let’s understand why this happens and how to fix it correctly.
Reproducing the Issue in a Simple Scenario
To see this behavior in action lets first produce this error using the ADF.
➤ Add a Lookup activity to an ADF pipeline and Keep the Timeout under the General tab unchanged (default 12 hours).
➤ Configure it with a SQL query that runs for more than 120 minutes. In our case just running the WaitFor DELAY for more than 120 minits
➤ Trigger the pipeline, and wait for it to complete. After roughly 120 minutes, the pipeline execution fails with a timeout error even though the activity itself is allowed to run much longer.
Why Does This Happen? (The Real Reason)
The confusion comes from the fact that Lookup activity has two different timeout settings, and they serve very different purposes.
➤ Activity Timeout: This Is under the General Tab and by default set to 12 Hourr. This controls the Overall exectution time of the activity and applies to the activity lifecycle not to the SQL Query.
➤ Query Timeout: This is under the Settings tab and its bydefault setting is 120 minutes. This controls how long the SQL query is allowed to run and if the query does not return results within this time, the connection is closed and the activity fails. This means Even if your activity timeout is 12 hours, the Lookup activity will still fail if the Query Timeout is exceeded.
How to Fix It
now, you understood the difference and If you know that your query can legitimately run longer than 120 minutes, the solution is straightforward:
➤Go to the Settings tab of the Lookup activity
➤Locate the Query Timeout property
➤Increase the value based on your expected execution time, in this demonstration adjusted to 180 minutes
So, the Query timout adjusted lets trigger the pipeline and see the magic of execution.
💥 Boom.
The pipeline finally crossed the finish line.
Recommended best practice for long-running queries:
➤ Run the query directly on the database server
➤ Measure the actual execution duration
➤ Add a buffer time to account for parallel workloads
➤ Set the Query Timeout based on this estimated duration
This approach helps avoid unnecessary failures and keeps pipeline executions predictable.
Hope you have learned something new today. If you have any suggestions or Questions leave them under comments