Intermittent Issue with Lookup Activity in ADF Fetching Data from Snowflake

Question

I'm encountering an intermittent issue with the Lookup activity in Azure Data Factory (ADF) when working with a Snowflake dataset. My setup involves an admin table in Snowflake where I store details of multiple tables. When I try to fetch all rows using the Lookup activity, sometimes it retrieves all rows as expected, but other times it returns no rows (0).

When I execute the same query directly on Snowflake, I get the expected results, but not when running it through the Lookup activity in ADF.

I'm not using the "First Row Only" option, as I need to fetch multiple rows.

For reference, I have added a screenshot that illustrates the issue.

Using the below query, I get 0 rows sometimes, and sometimes as expected.

AFTER RE RUN:

Has anyone faced a similar issue? What could be the possible causes, and is there a way to ensure consistent results when querying Snowflake from ADF?

Any suggestions or best practices to resolve this would be greatly appreciated!

Answer 1

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 * FROM <table_name> WHERE <condition> OPTION(NO_CACHE)

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.

Answer 2

@Ankit Mishra

Thank you for sharing the details of the intermittent issue with the Lookup activity in ADF when fetching data from Snowflake. It seems like the query is sometimes returning unexpected results, specifically zero rows.

Here are some potential causes and best practices to ensure more consistent results.

Query Execution Context - Ensure that the query being executed in the Lookup activity is the same as the one you run directly in Snowflake. Any differences in the execution context, such as user permissions or session settings, could lead to different results.

Data Caching - ADF may cache results from previous executions. If the underlying data in Snowflake changes between executions, it could lead to inconsistencies. Consider adding a mechanism to clear or manage cache if applicable.

Timeouts and Limits - The Lookup activity has a maximum output size of 4 MB and can return up to 5000 rows. If your query is close to these limits, it may fail to return results consistently. Make sure your query is optimized to stay within these constraints.

Error Handling - Implement error handling in your ADF pipeline to log and manage any failures that occur during the Lookup activity. This can help you identify patterns or specific conditions under which the issue arises.

Connection Configuration - Review the connection settings in ADF. Ensure that the connection to Snowflake is stable and that there are no intermittent connectivity issues. You might want to test the connection from ADF to Snowflake to confirm it’s working as expected.

If these steps do not resolve the issue, please share details about your Snowflake connection configuration in ADF, the query used, and any error messages or logs. We can further analyze and provide more targeted guidance.

For more details, please refer to the below Microsoft documentations.

Limitations and workarounds

Troubleshoot connector and format issues in mapping data flows in Azure Data Factory

I hope this information helps.

Thank you.