page120
103
© Andrew Carruthers 2024
A. Carruthers, Tuning the Snowflake Data Cloud, https://doi.org/10.1007/979-8-8688-0379-6_4
CHAPTER 4
Micro-partitions
You may recall from previous chapters that I discussed micro-partition pruning, but I did
not explain what micro-partitions are, as they deserve a chapter of their own. This is that
chapter.
At first sight, micro-partitions appear to be a simple subject to discuss; they are not.
As you are about to discover, micro-partitions rapidly increases the level of complexity
of the discussion. There are loose ends and references that resolve themselves in later
chapters (please forgive the interdependencies; they are an unfortunate consequence
of attempting to tell each story as simply as possible, while piquing your interest in
later chapters). This chapter reveals the lengths Snowflake has undertaken to hide the
underlying complexity of micro-partitions. You are about to scratch the surface and
take a peek.
In Chapter 2, I discussed query optimization in detail; the query optimizer seeks to
reduce the cost of queries by determining the optimal execution path. In this chapter,
I discuss the micro-partition features to both derive optimal data management and
control storage costs.
Performance tuning must consider both execution runtime and storage costs,
because both aspects are inextricably related.
I will begin by explaining some foundational information that many of us who have
been involved in information technology take for granted. Recent conversations with
junior colleagues indicate an absence of basic information, so this section is intended to
close those knowledge gaps.
The next chapter will cover cluster keys, micro-partition pruning, and optimal design
patterns supporting Snowflake best practices for data warehousing.
In the book Building the Snowflake Data Cloud, I discussed both micro-partitions
and query optimizer basics. The micro-partition story has not changed since I wrote that