The paper partially addresses this but Athena is a streaming engine, so it doesn't work in the same way as you might expect. The query planner has to take this

The paper partially addresses this but Athena is a streaming engine, so it doesn't work in the same way as you might expect. The query planner has to take this into account. So a normal planner might see the CTE and then "materialize" it by pushing rows somewhere in an "intermediate place", like memory so the tuples can then be used by the outer queries, sharing the single result. But Athena does not do this; it doesn't materialize the CTE in memory or anywhere first, it streams from producer operators to consumer operators directly, and can begin evaluating parts of the plan immediately. So a hash join is out completely. Just as a more general thought experiment, in a distributed-first database engine, a good question is "where do you materialize intermediate results" -- in memory, in storage somewhere? -- and it's complicated to answer. So it's not enough to just stick a query plan into some compiler and see the result. The query planner is in servitude to the actual storage architecture, which is much more important to understand for realizing practical optimizations. A good rule of thumb is that a lot of optimizations in any compiler, like these, on their own aren't very novel. You can sketch them on paper and even implement them well in an ideal system. But getting them to work reliably under the design constraints of your system today is the novel part.