Is there any way to set variables to before executing snowflake statement

Day 7 was one that I wasn’t sure I’d be able to do in SnapLogic because of the solution I did via Java earlier. But after some time, I was able to reason through the problem kind of going step-by-step to get to the final data necessary to complete both parts. This was defined during a twitch livestream that I watched last night by the creator as a “recursion” problem, and that was the red flag that indicated to me that I might not be able to complete it in the IIP.

There are some aspects of recursion, but I also had a way to do this by splitting things from 1) changing directories to get all possible 2) directory listings, then 3) adding files to all directories under the current directory. By building up those full directory paths AND including all directories potentially impacted by the directories at the same time, you kill two birds with one stone (you also make backwards traversal of directories somewhat easier). I still needed one sub-pipeline for the looping that required state, which included building up those “current directories” because you need the previous directory to build the current one. I was able to reason around the final calculation of directory size initially with a sub-pipeline, but the way that I was doing it lead me to the final solution doing an aggregate with group by which was a cleaner (and faster) solution.

I think this is a really good example problem where you might call for one way of programming/processing design when you first look at a problem, but you’re very much able to break the problem a bit further to better support a more linear approach (rather than recursive approach) to a solution. Very cool problem to work with, and kind of cool to see the morphing when it comes to analyzing the pipeline design. As I was typing this post up, is where I actually made the realization that I could remove a second sub-pipeline that again wrote state to a file.

Screenshots:

SLP Files:
day07_2022_12_07 (1).slp (54.0 KB)
day07_get_current_directory_2022_12_07.slp (19.0 KB)