page121
104
book, and some of the content presented here will therefore be familiar. However, with a
deeper understanding of optimizer behavior and subsequent hands-on experience, I will
offer new insights into how micro-partitions affect both performance and storage cost.
The
query_id
values used throughout this book will vary when parent SQL
statements are executed against your Snowflake account.
For those with a legacy RDBMS background, I should make clear that enforced
constraints are absent within Snowflake standard tables. By default, Snowflake allows
constraints, primary keys, and foreign keys to be declared. Note that they inform the
query optimizer, but the only enforced constraint is
NOT NULL
.
I am aware that some legacy RDBMSs use primary key tracking or change data
capture to implement data distribution. The absence of enforced primary keys precludes
a similar approach for Snowflake where immutable micro-partitions implement data
distribution capabilities.
However, the forthcoming Unistore and hybrid tables change the Snowflake
approach, at least for hybrid tables. The closest comparable feature to a primary key is a
cluster key, which I will discuss in the next chapter.
There is a lot of information to cover, so let’s start investigating micro-partitions!
Setup
First you will declare the session variables used throughout this chapter. Note that you
may need to rerun these declarations when your browser session is opened again.
SET tpc_owner_role = 'tpc_owner_role';
SET tpc_warehouse_XS = 'tpc_wh_xsmall';
SET tpc_warehouse_S = 'tpc_wh_small';
SET tpc_warehouse_M = 'tpc_wh_medium';
SET tpc_warehouse_L = 'tpc_wh_large';
SET tpc_warehouse_XL = 'tpc_wh_xlarge';
SET tpc_database = 'tpc';
SET tpc_owner_schema = 'tpc.tpc_owner';
ChaPTer 4
MiCro-ParTiTionS