Question 530
A Snowflake platform team receives the following requirement: A team tries to filter customers whose SUM of purchases exceeds a threshold using WHERE SUM(amount) and gets an error. Which design safeguard best addresses the observed risk?
Explanation
Correct Answer
This guardrail directly addresses aggregate having condition: an aggregate filter belongs after grouping rather than in the row-level WHERE phase. The prevention is: Distinguish row-level WHERE predicates from group-level HAVING conditions. A separate subsystem check would not prevent the specific recurrence.
Incorrect Answers
The window partitioning key does not represent the required account-level group. “Validate partition keys, order keys and frame semantics for analytic windows” would address that alternative cause, not the condition in this question: an aggregate filter belongs after grouping rather than in the row-level WHERE phase.
“Specify deterministic tie breakers when using window functions for latest-row selection” responds to this separate finding: the report lacks a partitioned ranking rule for choosing one row per entity. This question instead needs “Distinguish row-level WHERE predicates from group-level HAVING conditions”, so the alternative does not meet the stated condition.
The SQL window frame does not match the intended bounded moving calculation. “Document window-frame defaults and test opening-period edge cases” applies to that separate problem. The observed case is a team tries to filter customers whose SUM of purchases exceeds a threshold using WHERE SUM(amount) and gets an error; choosing this alternative would address the wrong failure mode.