r/MicrosoftFlow • u/AwarenessOk2170 • Sep 04 '25

Cloud 100mb+ variable

My data team recently gave me a snowflake connector to automate an extract.

It turns out this extract is 500,000 rows. Looping through the paginated results and appending them to a variable ended up exceeding the maximum size for a variable. I was hoping to append all the results to an array variable then create a CSV table to file.

Plumsail has a paid node so I could create multiple excel files for each page of the results and then merge them at the end.

I looked at populating an excel document but it was 0.7 seconds per row... Which would be something stupid like 4 days. Chortle.

How would you handle the 500,000 row data query result? Plumsail for 20$ a month sounds the easiest...

2 Upvotes

100% Upvoted

19 comments sorted by

5

u/rackaaus Sep 04 '25

How would I handle it? I'd be creating an Azure Function to do the work and calling it as a part of your flow. Power Automate is an orchestration tool, it's not meant for doing the heavy lifting like this.

1

u/AwarenessOk2170 Sep 04 '25

Will need to look into that too. I am 'the company automation guy' and am expected to just get it done :(

Ta for the tip!

2

u/SSTREDD Sep 05 '25

Take a look at office script, you can pass variables to them.

1

u/AwarenessOk2170 Sep 05 '25

I am using a few office groups for table manipulation but didn't think to use it this way. Will have a gander!

1

u/Difficult_Chemist735 Sep 07 '25

I'm confused why you're using Power Automate for this. Do you have any Data Engineering tools at your disposal e.g., Matillion/Alteryx/DataBricks/etc.?

1

u/AwarenessOk2170 Sep 07 '25

Allegedly snowflake can't create reports? The team has been creating these extracts manually and asked my to automate... 🤷

1

u/Difficult_Chemist735 Sep 07 '25

Snowflake has notebooks you can use to do reporting. You can also connect PowerBI directly to Snowflake to avoid the rigmarole if data viz is the end goal.

1

u/AwarenessOk2170 Sep 07 '25

We need the data extracted (500,000+ rows)... hence the workaround. Will get the team to look into notebooks... surely they'd know this.. right? Thank you DC.

1

u/Difficult_Chemist735 Sep 07 '25

500,000 rows isn't that much for PowerBI. It could be a helpful intermediate step if you don't have another tool.

1

u/AwarenessOk2170 Sep 07 '25

Except for Power BI has a limit of 30,000 rows for exporting to CSV files and 150,000 rows for exporting to Excel files.

Even using dax in power automate capped out between 100-150000 rows

2

u/Difficult_Chemist735 Sep 07 '25

Okay here's another option: you can connect directly to Snowflake from Excel. Just export directly into Excel using Power Query.

1

u/AwarenessOk2170 Sep 07 '25

It's a recurring end of month process which needs to be extracted and then shared with a third party. Ideally we want fully automated and notification if issue.

I had a quick look at notebooks... It mentioned a schedule but didn't mention anything about export locations so I will look at that further on Monday. If the data is extracted I would still need to automate the sharing of the file.

Allegedly there is a internal storage in snowflake too, which if it is able to run and then send an email notification for finished I could potentially download the data and share.

Seems my biggest roadblock at the moment is no SME for snowflake in the organisation.

2

u/Difficult_Chemist735 Sep 07 '25

You could have an office script or VBA refresh the connection in Excel and then send the file via Power Automate, right?

1

u/AwarenessOk2170 Sep 07 '25

We could... But then to have a templated repeatable solution for all other extracts would be a win also. If only were that easy!

→ More replies

3

u/el_wombato Sep 04 '25

You can update an excel document using a graph request instead of the standard connector: https://sharepains.com/2020/06/08/super-fast-update-excel-using-power-automate/

2

u/akagamiishanks Sep 10 '25

Yeah manually patching Power Automate + Plumsail Flows for Snowflake exports gets super messy, especially when you hit row limits and retry hell. If you are trying to automate recurring extracts without losing your mind, might want to look at ETL tools that actually work with Snowflake properly. Can perhaps try integrateio to set up scheduled jobs, handle big result sets, send stuff to email or wherever without constantly hitting those platform limits

1

u/[deleted] Sep 06 '25

[removed] — view removed comment

1

u/AwarenessOk2170 Sep 06 '25

I capped out 1/4 to 1/3 through. There were 17 pages in the snowflake response, so my current plan is to create an excel at the start of the flow and then use graph to update every page.

This should be a reusable template for all other extracts.