Micro partitions and Clustering Flashcards
What is Microparitioning?
It is a way how SF physically stores the data. Incoming source data is divided into smaller chunks, their metadata collected and stored.
Why there is a need for micropartitioning?
For query efficiency. This avoids full table scan by using query pruning. SF collects some metadata on micro partitions. So when a query runs, based on the metadata it prunes away some partitions, so full table scan is avoided.
What is the size of micro partition?
50 to 500MB before compression. Data is always stored as compressed format in SF.
What is query pruning?
It is simply a concept where unnecessary partitions are avoided to be scanned for that query. It is based on the metadata collected while creating micro partitions.
What metadata is collected on micropartitions?
Range of values.
distinct values.
additional properties that SF collects for query performance which is not revealed
Micro partitions are collected by users? Can it be changed by users?
It is not created by users. SF automatically does it as data is coming in. It cannot be changed by users.
Users can do clustering from their end which will improve performance.
What is clustering depth?
Once micro partitions are created, SF also collects metadata on how many micro partitions overlap… This overlapping is called clustering depth. Lower the depth it is good.
What is the clustering depth of empty table?
0
What are the disadvantages of static partitioning done in traditional databases which Microparitioning takes care of ?
- maintenance (as data gets updated, static partitioning can go outdated)
- Data skew if partition columns are not uniformly distributed.
Is micro partitions logical storage or physical storage?
They are physical storage - how tables are physically stored at the back end - data is stored in columnar format.
When do you need clustering/setting up cluster keys?
When clustering depth is large on micropartitions, (because ordering at the time of the insert was not perfect)
when lot of DMLs has happened on the tables and existing micropartitions are not causing efficient query pruning etc.
Clustering can be done on what object types?
Tables and materialized views
Clustering is recommended for what tables?
large tables where queries are running slower.
large tables where DML is happening frequently.
generally tables in Terabyte range
How do you choose columns for clustering?
Clustering will create new micropartitions (MPs) so it will add to storage cost so you have to be careful about how many columns and what columns you choose.
- 3 to 4 columns is ok.
- Choose columns used in filters and then used in joins.
Choose columns
- that have lot of distinct values so many MPs can be ignored using query pruning
- as well as it should not be so unique that MPs cannot group them.
What columns should not be chosen for clustering?
Extremely high cardinal (most unique values) like id columns should not be chosen….because it cannot be grouped together for creating a cluster.
Extremely low cardinal columns (less unique values) like gender should not be chosen as well….because query pruning cannot be done efficiently.
For high cardinal columns like timestamp, you can do expressions like to_date conversion to do efficient clustering.
