Can someone please help me with the best way to implement the above scenario. I need to fetch 50k rows from snowflake once a month and upload it as as csv file into target system. I'm able to fetch few 100s row at once and convert into csv and upload it to target. But i wanted to understand ways to do the same process for 50k rows. Thanks in advance.
You can use the select on the snowflake connector (or JDBC connector I'd imagine if snowflake allows JDBC connections), I would look at any settings that impact streaming for that.. like fetch size (this will avoid too many round trips as the default is set to 10 I believe) and the repeatable vs non repeatable streaming stuff (as I presume you only need to access the stream of rows once!) and then you can look at dataweave's streaming support: https://docs.mulesoft.com/dataweave/latest/dataweave-streaming
Essentially you'll want to do your transformation to CSV, set the deferred=true on the output directive and then call your write to remote CSV file operation on whichever connector you're using. That makes the dataweave do a "from one stream to another" deferred mode of operation, which should avoid unnecessary I/O..
I'd be doing tests locally with something like visual VM hooked up to the mule java process to make sure memory isn't blowing out or something - test with suitably large test cases to see what's going on, and you can try using the file connector in lieu of the FTP/SFTP or whatever you're going to use initially to see what's going on, then swap to the real connector and test again..
