page45
26
No query optimizer is able to guess at the intended data set outcome. As the saying
goes, garbage in, garbage out, and you are responsible for ensuring the quality of your
submitted SQL statements.
Finally, I must pay tribute to both Jiaqi Yan, principal software engineer, and
Minzhen Yang, principal engineer and tech lead, both at Snowflake, for their
comprehensive explanation of the Snowflake query optimizer at Snowflake Summit
2023. I have derived some information for this chapter from their presentation as
well as embellished it with my own understanding and knowledge. Any omissions,
misrepresentation, or errors are mine alone.
Query Lifecycle
At a superficial level, we submit queries, and sometime later we receive results.
Simple. Or is it? The book you are reading should automatically answer the question of
simplicity. Ideally this book would not be necessary, because the developers try very
hard indeed to remove complexity from Snowflake.
The absence of levers and switches to influence system behavior and
application performance is a key indicator of how successful the developers have
been. Understanding the query optimizer implementation unlocks pathways to
delivering SQL.
Wherever possible, simplify your SQL; do not write convoluted or hard-to-
follow code.
Figure 2-1 shows an overview of the query lifecycle, which is explained further in the
corresponding bullet points.
Chapter 2
the Query Optimizer