Question 160
A Snowflake administrator investigates the following production situation: A giant events table receives repeated range filters on event_date, yet partition pruning degrades after random backfills. Which preventive control would most directly reduce recurrence?
Explanation
Correct Answer
This guardrail directly addresses clustering key for range filters: the physical clustering order does not favor the frequent selective range predicate. The prevention is: Enable clustering only when workload benefit outweighs data maintenance overhead. A separate subsystem check would not prevent the specific recurrence.
Incorrect Answers
The predicate obscures a simple selective filter and may increase scanned work. “Store values in appropriate types and avoid avoidable functions on selective predicates” would address that alternative cause, not the condition in this question: the physical clustering order does not favor the frequent selective range predicate.
“Preserve native date and number types for filters, order and top-k queries” responds to this separate finding: the query does unnecessary ordering work and can lose proper timestamp ordering. This question instead needs “Enable clustering only when workload benefit outweighs data maintenance overhead”, so the alternative does not meet the stated condition.
Recomputing the same derived aggregate wastes work that a maintained summary may avoid. “Review materialized-view refresh and storage costs before provisioning summaries” applies to that separate problem. The observed case is a giant events table receives repeated range filters on event_date, yet partition pruning degrades after random backfills; choosing this alternative would address the wrong failure mode.