Query Folding - Snowflake

[AndersASorensen]

Hi,

I am trying to investigate what folds and whats doesnt with this connection, and I noticed something I havent seen before.

When doing some simple filtering I can see in Snowflake query history that is folds, and I also see some simple transformation that doesnt fold. However, even if I put some transformation that doest fold BEFORE some simple filtering, I noticed that it still manages to fold the filter part....even though it comes after steps that does not fold.

Anyone else come across this and can share some info and/or links to documentation?

Regards,

Anders

[BA_Pete · Accepted solution]

Hi Anders,

When Power Query creates the native query it evaluates your query in totality and optimises it as a whole. This means, for example, that you can add a complex logic column that doesn't fold, then remove some other columns (an operation that does fold) afterwards. If your complex logic column uses data from the columns that you didn't remove in the second step then PQ will optimise this query by removing the columns first, then adding the complex column afterwards.

Similarly, if you remove your complex logic column later in your query without having used its result, when PQ evaluates the query as a whole it won't even write the creation of the column in, as it can see it's not used or retained, thus allowing the query to fold in the backend even though it may display in the step list as unfoldable.

It's always good practice to put your foldable steps first for logic and clarity, but PQ will re-evaluate and optimise the query as a whole anyway.

Pete