DEA-C02 Data Transformation Practice Question
A data engineer needs to filter the results of a complex analytical query based on the result of a window function. The query calculates a rolling average of sales per region and should only return rows where the current sale exceeds that average. Which SQL clause is most efficient for this transformation?
⚠ Common exam trap
Candidates often write complex nested subqueries or CTEs to filter window functions, unaware that the QUALIFY clause natively handles this efficiently in the same query block.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The QUALIFY clause
Filtering on window functions requires a mechanism that executes after the window calculations are performed. The QUALIFY clause allows engineers to filter results directly in the SELECT statement without nesting logic inside a subquery or a Common Table Expression. This significantly improves query readability and can lead to internal optimizations by the Snowflake query optimizer during the execution phase.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The WHERE clause
Why it's wrong here
The standard WHERE clause is evaluated before window functions are calculated in the logical query processing order. Attempting to reference a window function like AVG() OVER() directly in a WHERE clause will result in a compilation error. Engineers would need to use a subquery if they relied solely on this specific filtering mechanism.
- ✗
The HAVING clause
Why it's wrong here
The HAVING clause is designed to filter results after an aggregate GROUP BY operation has occurred. While it executes after some transformations, it cannot be used to filter based on window functions. It is restricted to columns in the GROUP BY clause or aggregate functions, making it unsuitable for requirements involving rolling window calculations.
- ✓
The QUALIFY clause
Why this is correct
The QUALIFY clause is specifically designed to filter the results of window functions after they have been computed. It functions similarly to how HAVING works for aggregates, providing a clean syntax to remove rows that do not meet criteria. This reduces code complexity by eliminating the need for wrapping the primary query in a subquery.
- ✗
The GROUP BY clause
Why it's wrong here
The GROUP BY clause is used to collapse multiple rows into summary rows based on shared values in specified columns. It does not provide filtering capabilities for window functions and instead changes the granularity of the data. Using it in this context would likely interfere with the intended rolling average calculation across the dataset.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.