A large table with 200 columns contains two years of historical data. When queried, the table is filtered on a single day. Below is the Query Profile: Using a size 2XL virtual warehouse, this query…

Question

Exhibit

Options

• AIncrease the size of the virtual warehouse.
• BIncrease the number of clusters in the virtual warehouse.
• CImplement the search optimization service on the table.
• DAdd a date column as a cluster key on the table.

• A
  3% (1)
• B
  18% (6)
• C
  9% (3)
• D
  70% (23)

Explanation

Adding a cluster key on the date column (D) directly solves the root cause: without clustering, Snowflake must scan all micro-partitions across two years of data to find a single day, causing massive partition pruning failure - hence the hour-long runtime. Cluster keys allow Snowflake to skip irrelevant micro-partitions, reducing the scan from the full table to only the partitions containing that date.

Why the distractors fail:

• (A) Larger warehouse - more compute power doesn't help when the bottleneck is I/O from scanning unnecessary micro-partitions; you'd just burn more credits scanning the same data faster at best.
• (B) More clusters - multi-cluster warehouses address concurrency (many users running queries simultaneously), not single-query scan performance.
• (C) Search optimization service - SOS helps with highly selective point lookups on high-cardinality columns (e.g., WHERE id = 12345), not range/equality filters on date columns in large analytical tables.

Memory tip: Think "cluster key = physical sort order on disk." If your WHERE clause filters on a column and the data isn't physically organized by that column, Snowflake reads everything. Match the cluster key to your most common filter column, and Snowflake skips the irrelevant partitions entirely.

No community discussion yet for this question.