Lookup activity issue from Snowflake connector in Azure data factory

Question

Hello Team,

I am facing an issue from lookup activity, I have a pipeline which is having a snowflake connector in the lookup activity and trying to fetch the records from the table. but lookup activity not getting any output when we check in the snowflake query history it shows that query return the results but not showing in ADF. Can you please help me to solve this isssue.

Answer 1

I think this is a frequent issue with the Snowflake connector in ADF.

The query might not always return consistent results due to timing issues, such as transactions still being in progress or uncommitted data.

You need to verify that your query reads committed data so you can use the WITH(NOLOCK) equivalent for Snowflake by appending WITH READ COMMITTED to your query, if applicable of course.

Snowflake has resource limits, and if multiple queries or jobs are hitting the database simultaneously, the query used in ADF might get throttled or deprioritized.

Another detail, ADF might cache old query results in some cases, causing it to return outdated or empty data intermittently.

Try to add a timestamp or a NO_CACHE clause to your query to ensure ADF always fetches the latest data from Snowflake:

SELECT

I recommend that you turn on verbose logging in the pipeline run to capture detailed information about the Lookup activity, including the query executed and the connection status.

https://learn.microsoft.com/en-us/answers/questions/2128409/intermittent-issue-with-lookup-activity-in-adf-fet

https://stackoverflow.com/questions/79013977/adf-lookup-activity-sometimes-not-returning-value-after-changing-to-the-new-snow

In the documentation :

• The Lookup activity can return up to 5000 rows; if the result set contains more records, the first 5000 rows will be returned.
• The Lookup activity output supports up to 4 MB in size, activity will fail if the size exceeds the limit.
• The longest duration for Lookup activity before timeout is 24 hours.