Snowflake connector 200-row cap in UiPath Integration Service: how to paginate or go async

Description and use

When using UiPath’s new Snowflake connector, a synchronous query returns at most 200 rows regardless of the SQL LIMIT. The solution is to paginate with LIMIT/OFFSET or use an asynchronous query, which also caps at 200 rows but can be paged sequentially.

Problem

When using UiPath’s new Snowflake connector, Execute Select Query Synchronously returns at most 200 rows even if the SQL LIMIT is higher, causing large result sets to be truncated.

Outcome

By paging with LIMIT/OFFSET or using asynchronous execution, workflows can retrieve datasets of any size from Snowflake while respecting the 200-row cap of the connector.

Workflow

• Write a stable SELECT query with ORDER BY on a unique key and LIMIT 200 OFFSET 0.
• Generate the output schema from the literal query; then replace the offset with a variable.
• In a Do While loop, execute Execute Select Query Synchronously, append the returned rows to a collection, and increment the offset by 200.
• Stop when a page returns fewer than 200 rows.
• (If the query takes >30s) use Execute Any Query Asynchronously to get a Query ID, poll status with Get Asynchronous Query Execution Status, and when SUCCESS, retrieve results with Get Asynchronous Query Execution Results, paging with its Offset and Limit properties.

Evidence

Execute Select Query Synchronously returns max 200 records even with a higher LIMIT; use LIMIT/OFFSET in the Select Query inside a Do While

Source

Always paginate large result sets with LIMIT and OFFSET

Source

Use Execute Any Query Asynchronously for queries over 30 seconds; non-SELECT commands only via the async activity

Source

Without ORDER BY, LIMIT results are non-deterministic; keep ORDER BY and LIMIT at the same query level

Source