Snowflake Exam – Questions & Answers
How are VARIANT columns stored? ✔️Ans - As a separate physical columns
Are common and/or frequent fields/paths in VARIANT columns stored as
separate physical columns? ✔️Ans - Yes
For data that is mostly regular and uses only native JSON types (strings and
numbers, not timestamps), are both storage and query performance between
operations on relational data and data in VARIANT similar? ✔️Ans - Yes
For JSON column dates/timestamps, can operations on these be slower when
stored in VARIANT and also use more space for storage? ✔️Ans - Yes
What are micropartitions? ✔️Ans - Continuous units of storage
Are groups of rows in tables mapped into individual micro-partitions? ✔️Ans
- Yes
Are micro-partitions organized in columnar fashion? ✔️Ans - Yes
Do micro-partitions allow for extremely granular pruning of very large tables?
✔️Ans - Yes
Do users need to manage micro-partitions? ✔️Ans - No
Are columns individually compressed within micro-partitions? ✔️Ans - Yes
Because of micro-partitions, is DELETE ALL ROWS from a table a metadata-
only operation? ✔️Ans - Yes
Does DF prune micro-partitions based on a predicate with a subquery?
✔️Ans - No
What are the following functions for? SYSTEM$CLUSTERING_DEPTH and
SYSTEM$CLUSTERING_INFORMATION ✔️Ans - View/Monitor clustering
What is clustering? ✔️Ans - Sort/ordering of data/micro-partitions
, TRUE or FALSE: the smaller the average depth, the better clustered the table
is with regards to the specified columns ✔️Ans - TRUE
What can be clustering depth used for? ✔️Ans - a) Monitoring the clustering
"health" of a large table, particularly over time as DML is performed on the
table
b) Determining whether a large table would benefit from explicitly defining a
clustering key.
What is a clustered table? ✔️Ans - Table with a clustering key
Can both tables and views be clustered? ✔️Ans - Yes
What tables are good candidates for clustering? ✔️Ans - Multi TB tables.
What is a clustering key? ✔️Ans - It is a subset of columns in a table (or
expressions on a table) that are explicitly designated to co-locate the data in
the table in the same micro-partitions
Which indicators tell that a table may need to be clustered? ✔️Ans - a)
Queries on the table are running slower than expected or have noticeably
degraded over time
b) The clustering depth for the table is large.
Does clustering consume credits? ✔️Ans - Yes
In general, when do queries benefit from clustering? ✔️Ans - When the
queries filter or sort on the clustering key - WHERE, ORDER BY, GROUP, some
joins
What is the best practice for designing clustering keys? ✔️Ans - Not using
more than 3-4 columns or expressions per key
What are not good candidates for clustering keys? ✔️Ans - Column with
very low cardinality and column with very high cardinality
How are VARIANT columns stored? ✔️Ans - As a separate physical columns
Are common and/or frequent fields/paths in VARIANT columns stored as
separate physical columns? ✔️Ans - Yes
For data that is mostly regular and uses only native JSON types (strings and
numbers, not timestamps), are both storage and query performance between
operations on relational data and data in VARIANT similar? ✔️Ans - Yes
For JSON column dates/timestamps, can operations on these be slower when
stored in VARIANT and also use more space for storage? ✔️Ans - Yes
What are micropartitions? ✔️Ans - Continuous units of storage
Are groups of rows in tables mapped into individual micro-partitions? ✔️Ans
- Yes
Are micro-partitions organized in columnar fashion? ✔️Ans - Yes
Do micro-partitions allow for extremely granular pruning of very large tables?
✔️Ans - Yes
Do users need to manage micro-partitions? ✔️Ans - No
Are columns individually compressed within micro-partitions? ✔️Ans - Yes
Because of micro-partitions, is DELETE ALL ROWS from a table a metadata-
only operation? ✔️Ans - Yes
Does DF prune micro-partitions based on a predicate with a subquery?
✔️Ans - No
What are the following functions for? SYSTEM$CLUSTERING_DEPTH and
SYSTEM$CLUSTERING_INFORMATION ✔️Ans - View/Monitor clustering
What is clustering? ✔️Ans - Sort/ordering of data/micro-partitions
, TRUE or FALSE: the smaller the average depth, the better clustered the table
is with regards to the specified columns ✔️Ans - TRUE
What can be clustering depth used for? ✔️Ans - a) Monitoring the clustering
"health" of a large table, particularly over time as DML is performed on the
table
b) Determining whether a large table would benefit from explicitly defining a
clustering key.
What is a clustered table? ✔️Ans - Table with a clustering key
Can both tables and views be clustered? ✔️Ans - Yes
What tables are good candidates for clustering? ✔️Ans - Multi TB tables.
What is a clustering key? ✔️Ans - It is a subset of columns in a table (or
expressions on a table) that are explicitly designated to co-locate the data in
the table in the same micro-partitions
Which indicators tell that a table may need to be clustered? ✔️Ans - a)
Queries on the table are running slower than expected or have noticeably
degraded over time
b) The clustering depth for the table is large.
Does clustering consume credits? ✔️Ans - Yes
In general, when do queries benefit from clustering? ✔️Ans - When the
queries filter or sort on the clustering key - WHERE, ORDER BY, GROUP, some
joins
What is the best practice for designing clustering keys? ✔️Ans - Not using
more than 3-4 columns or expressions per key
What are not good candidates for clustering keys? ✔️Ans - Column with
very low cardinality and column with very high cardinality
