ARA-C01 · Question #132
A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below: Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER B
The correct answer is D. C2, C1, C3. Snowflake recommends two rules for multi-column clustering keys: (1) include columns most commonly used in filter (WHERE) and join conditions, not columns used only in GROUP BY or ORDER BY; and (2) order the selected columns from lowest cardinality to highest cardinality. C4 and
Question
A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below:
Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses. Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries. The Architect must design a clustering key for this table to improve the query performance. Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?
Exhibit
Options
- AC5, C4, C2
- BC3, C4, C5
- CC1, C3, C2
- DC2, C1, C3
How the community answered
(61 responses)- A3% (2)
- B2% (1)
- C11% (7)
- D84% (51)
Explanation
Snowflake recommends two rules for multi-column clustering keys: (1) include columns most commonly used in filter (WHERE) and join conditions, not columns used only in GROUP BY or ORDER BY; and (2) order the selected columns from lowest cardinality to highest cardinality. C4 and C5, used only in GROUP BY/ORDER BY, are therefore excluded. Among the filter/join columns C1, C2, and C3, the cardinality distribution shown in the question indicates C2 has the lowest cardinality, C1 has medium cardinality, and C3 has the highest cardinality. This produces the correct ordering: C2, C1, C3. Ordering from low to high cardinality maximizes the pruning efficiency of micro-partitions.
Topics
Community Discussion
No community discussion yet for this question.