[BUG] Snowflake connector not partition aware

[jesusfraire]

Type of Connector

Certified Connector

Name of Connector

Snowflake

Describe the bug

When retrieving more than 1k records of data the connector only returns the data contained within the first partition created by the Snowflake API. The rest of the records in subsequent partitions are not returned and ignored by the connector.

Is this a security bug?

No, this is not a security bug

What is the severity of this bug?

Severity 1 - Connector is broken or there's a very serious issue

To Reproduce

Create an instance of the connector in Power Automate Query a Snowflake table with more than 1k records Count records returned to Power Automate and Power Apps Records will be less than total contained in Snowflake table

Expected behavior

the connector should traverse all partitions that are created by the Snowflake API so that the records returned match the number of records contained in the table.

Environment summary

Power Apps Power Automate Snowflake

Additional context

No additional context

[TerhoAntila]

Hi, just bumped into this problem as well. You can workaround this by using the "Check the Status and Get Results" action, which allows you to define the partition number you want the results from (indexed from zero).

I ended up creating a do until loop, to iterate over partition count. Unfortunately this is a lot slower than fetching all the results within one statement, but I guess this is what we have to live with for time being 😕 Hopefully Snowflake is able to fix this asap...

And also - I needed to set a default role for myself in Snowflake end as the "Check the Status and Get Results" action does not provide a way to send the role 🤪

[GabrielNova24]

@TerhoAntila I am facing the same issue, thanks for your comment. I am doing the same loop but i am facing some issues due to the "append to array" action in power automate doesn't work with nested arrays, how did you solve it?; I am trying one solution flattening the array using select or even parse Jon, but I need to do an interaction for each item of the array, and that is extremely slow

[jesusfraire]

I was able to also resolve the issue using a loop. here's what worked for me

1. First execute a Snowflake query that returns the record count of your query using Submit SQL Statement Operation
2. Submit your query to retrieve the records using Submit SQL Statement Operation
3. Store the results (data) attribute from step 2 in a string variable
4. Split the string from step 3 into an Array variable by ',]'
5. Count the number of records in the array from step 4
6. Create a condition to determine if the array record count = total expected record count you got in step 1
7. If it is less then kick off a Do Until that takes the statement handle property from step 2 and executes a Check the Status and Get Results Operation to retrieve the next record set from the Snowflake API Queue This should run until all records are processed, but beware i have seen extremely slow performance in Power Apps with record sets >10k records. Also note, i did not use the Convert results set rows from array to objects operation. i just grab the data from the Execute sql statements and Check status ops and append them to a string variable. i then return the string to Power Apps and process it into a collection using ForAll and Concats. Hope that helps.

[GabrielNova24]

thank you so much @jesusfraire it really help! I did a similar check, and i did and append in string variable plus manipulation with split, and then back to powerapps with Json(stringmanipulated). It work!

[Satya11221]

Hello @jesusfraire or @GabrielNova24

I am also facing the same issue in loading the records from Snowflake that are more than 1k, but am not expert in power automate so hard to understand the steps you have mentioned so it would be really great full if you can help me by providing the screen shots for the steps from 3 as I am struggling to resolve this, please!

Thanks in advance!

[Edpobj]

Hello @jesusfraire or @GabrielNova24

I am also facing the same issue in loading the records from Snowflake that are more than 1k, but am not expert in power automate so hard to understand the steps you have mentioned so it would be really great full if you can help me by providing the screen shots for the steps from 3 as I am struggling to resolve this, please!

Thanks in advance!

Please @jesusfraire or @GabrielNova24, could you help us to understand a little bit more about this loop?I'm particularly stuck on stage 7.

[jesusfraire]

Hi all for Step 3 it is simply assigning the returned data attribute from the Submit Snowflake Query task to a string variable. recordsString = @{outputs('Submit SQL Statement for Execution')?['body/data']}

You can then iterate over this object to count the number of records present in the results and determine if it is equal to or less than the total expected records calculated in step 1 snowflakeResultsArray = @{split(variables('recordsString '),'],')} returnedRecordCount=@{add(length(variables('snowflakeResultsArray')),1)}

If expectedRecordCount > returnedRecordCount Do until returnedRecordCount=expectedRecordCount Check Status and Get Results (Task) Append to String recordsString = @{outputs('Check_the_Status_and_Get_Results')?['body/data']} Update Record Count returnedRecordCount= @{length(split(variables('recordsString'),'],'))} Update an index variable (use this to access the next Snowflake data partition in the Do While

[Hasse89]

@jesusfraire Could you include a screenshot of your flow? I'm having some trouble understanding how you have set up the "Do until" part of the flow, and how you index the result to the variable.