How to retrieve large datasets (~80k rows) with the new Snowflake Connector? (200-row limit issue)

Help Studio
1

Hi everyone,

I’m working on a use case where I need to fetch approximately 80,000 rows using a Snowflake query, and I’m running into some issues transitioning between the old and new connector packages.

  • Deprecated Connector: I am currently using the deprecated package. It successfully pulls the data 90% of the time, but throws a timeout error the other 10%. I am curious why this package was deprecated rather than updated, as it generally handles the data volume well.

    image
    image952×373 34.5 KB

  • New Connector: When I try to migrate to the new Snowflake connector, the output is limited to only 200 rows.

    image
    image1463×248 35.8 KB

Since the new connector doesn’t seem to return the full dataset, how can I use it for large data calls? Are there pagination features, specific configuration settings, or alternative approaches I should be using to grab all 80k rows?

Any insights from anyone experienced with Snowflake integrations would be greatly appreciated!

2

Hi Tejas,

With the new Snowflake Integration Service connector, you can use the Native Search activity for large result sets.

I noticed in the activity there is a Limit property. The default/low limit may be why you’re seeing only 200 records. Try setting the Limit to a value higher than the expected result count, for example 80,000 or 150,000, and check the output DataTable.

For example:

  • Native Search → Limit: 80000
  • Search Response: output DataTable

If you are still facing timeouts with ~80k records, I would recommend splitting the query into batches using a suitable filter/key (for example, date range or ID range) rather than retrieving everything in a single call.

Also, since the older Snowflake package is deprecated, I would prefer migrating to the Integration Service connector rather than building a new solution around the deprecated package.

Hope this helps!

3

Hi @tejaskumar.darji

The new connector has a 200-row limit per request. For 80k rows, you can use LIMIT and OFFSET and fetch the data in batches inside a loop.

For example, fetch 200 rows at a time and increase the OFFSET until all records are retrieved.