SnowPro Advanced Administrator ADA-C02

Question

A retailer has a TRANSACTIONS table (100M rows, 1.2 TB) clustered by the STORE_ID column (varchar(50)). The vast majority of analysis on this table is grouped by STORE_ID to evaluate store performance.

The retailer operates 1000 stores, but 20 stores account for most sales. The Administrator observes that most queries currently have poor pruning, processing large numbers of bytes even for simple queries.

Why is this happening?

Explanation

Clustering-based pruning is most effective when key values are sufficiently and relatively evenly distributed. Because 20 stores contain most transactions, each dominant STORE_ID spans many micro-partitions; queries involving those stores cannot eliminate many partitions and consequently process large amounts of data. Snowflake identifies cardinality as important for clustering-key selection and illustrates ideal pruning under a uniform distribution.

Learn more

SnowPro Advanced Administrator ADA-C02

AstroTutor

Notes

Readiness

QuestionQ32

Community Discussion