I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style
x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant functional language, it's just a bit obtuse).
If I am careful about avoiding groupings, distincts or orderings, and only use a CMT in one following query/CMT, will Redshift dispense with the temporary table, and just treat it like a filter? Monitoring seems to suggest this to be the case.
For instance, will this query create two temp tables, or will the planmer say: "hey! no need to store any data…"?
with FilteredList as ( select col1, col2 from Table where col1 is not Null ), MoreFiltering as ( select col1, col2 from FilteredList where col2 is not Nill ) select col1+col2 as Summo from MoreFiltering;