ADA-C01 · Question #68
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are grouped by STORE_ID to look at…
The correct answer is C. Sales across stores are not uniformly distributed. Clustering keys are most effective when the data is evenly distributed across the key values. If the data is skewed, such as in this case where most sales come from only 20 stores out of 1000, then the micro-partitions will not be well-clustered and the pruning will be poor…
Question
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are grouped by STORE_ID to look at store performance. There are 1000 stores operated by the retailer but most sales come from only 20 stores. The Administrator notes that most queries are currently experiencing poor pruning, with large amounts of bytes processed by even simple queries. Why is this occurring?
Options
- AThe STORE_ID should be numeric.
- BThe table is not big enough to take advantage of the clustering key.
- CSales across stores are not uniformly distributed.
- DThe cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a
How the community answered
(44 responses)- A7% (3)
- B18% (8)
- C70% (31)
- D5% (2)
Explanation
Clustering keys are most effective when the data is evenly distributed across the key values. If the data is skewed, such as in this case where most sales come from only 20 stores out of 1000, then the micro-partitions will not be well-clustered and the pruning will be poor. This means that more bytes will be scanned by queries, even if they filter by STORE_ID.
Topics
Community Discussion
No community discussion yet for this question.