Snowflake SnowPro Core Practice Test 82
Question 810
For an original SnowPro Core educational scenario, consider: A report wants moving averages per product but developers attempt one global GROUP BY that collapses every detailed row. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Use an unordered ROW_NUMBER result as a guaranteed chronological sequence.
- Check: Test row_number or moving-average output for customers with multiple timestamps and ties.
- Check: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
- Change: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Change: Use appropriate window functions with PARTITION BY and ordered frames for row-level ranking or rolling calculations.
Explanation
Correct Answer
B:Window calculations evaluate over logical row partitions without necessarily collapsing rows as a GROUP BY does. Acceptance: Each group has the intended ranked or rolling values while original row-level detail is preserved.
E:Window calculations evaluate over logical row partitions without necessarily collapsing rows as a GROUP BY does. Evidence: Test row_number or moving-average output for customers with multiple timestamps and ties.
Incorrect Answers
A:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. Window calculations evaluate over logical row partitions without necessarily collapsing rows as a GROUP BY does.
C:Check: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. Window calculations evaluate over logical row partitions without necessarily collapsing rows as a GROUP BY does.
D:Change: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. Window calculations evaluate over logical row partitions without necessarily collapsing rows as a GROUP BY does.
Question 811
A Snowflake engineer is examining this platform situation: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output. A proposed shortcut is: Place ROW_NUMBER directly in WHERE and assume SQL evaluation order will resolve it. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs.
- Use suitable TRY_TO_NUMBER or TRY_TO_DATE style conversions with separate bad-record detection and remediation.
- Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
- Place ROW_NUMBER directly in WHERE and assume SQL evaluation order will resolve it.
Explanation
Correct Answer
C:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Applied case: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (Dynamic table target lag and refresh economics) rather than the documented QUALIFY filtering window expressions decision.
B:Use suitable TRY_TO_NUMBER or TRY_TO_DATE style conversions with separate bad-record detection and remediation. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
Question 812
A Snowflake implementation review finds: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output. The team proposes: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
- FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations.
- Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs.
- Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals.
Explanation
Correct Answer
A:Relevant validation before approval: Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps. Applied case: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output.
Incorrect Answers
B:FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
C:Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 813
The data platform team has not yet approved changes. Current evidence indicates: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps.
- Inspect query profile scanned partitions, predicate form and representative date distribution.
- Test source-key duplicates, matched row counts and idempotent replay of the same batch.
- Inspect INPUT, INDEX, VALUE, missing paths and resulting expanded row counts.
Explanation
Correct Answer
A:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Confirm afterward that: Exactly the intended ranked records remain with deterministic tie rules.
Incorrect Answers
B:Inspect query profile scanned partitions, predicate form and representative date distribution. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
C:Test source-key duplicates, matched row counts and idempotent replay of the same batch. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:Inspect INPUT, INDEX, VALUE, missing paths and resulting expanded row counts. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 814
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output. The service owner needs positive and negative results and evidence that legitimate data workflows still function. Which acceptance result demonstrates correct platform behavior without disrupting authorized workflows? Select ONE best answer.
- The query reads fewer relevant partitions while returning identical filtered rows.
- The chosen configuration meets deadlines while improving or justifying cost per delivered result.
- Exactly the intended ranked records remain with deterministic tie rules.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
Explanation
Correct Answer
C:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. The result must be validated using the documented dataset or access context.
Incorrect Answers
A:The query reads fewer relevant partitions while returning identical filtered rows. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
B:The chosen configuration meets deadlines while improving or justifying cost per delivered result. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:Repeat eligible queries can avoid recomputing results without returning stale data after changes. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 815
An implementation sign-off request identifies the following problem: A customer report needs only the most recent order from each account, but WHERE cannot directly filter ROW_NUMBER output. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- Change: Evaluate supported Query Acceleration Service on eligible queries using its estimate and cost controls. Verify: Inspect query eligibility and acceleration history plus incremental service credits for representative workloads.
- Change: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY. Verify: Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps.
- Change: Express supported filters and transformations as Snowpark operations and use governed execution runtimes for Python-specific steps. Verify: Compare generated SQL plans, data transfer volumes and managed package availability for a sample transform.
- Change: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness. Verify: Test source-key duplicates, matched row counts and idempotent replay of the same batch.
Explanation
Correct Answer
B:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. The change is successful only when: Exactly the intended ranked records remain with deterministic tie rules.
Incorrect Answers
A:Change: Evaluate supported Query Acceleration Service on eligible queries using its estimate and cost controls. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
C:Change: Express supported filters and transformations as Snowpark operations and use governed execution runtimes for Python-specific steps. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:Change: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 816
A Snowflake engineer is examining this platform situation: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query. A proposed shortcut is: Place ROW_NUMBER directly in WHERE and assume SQL evaluation order will resolve it. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing.
- Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
- Place ROW_NUMBER directly in WHERE and assume SQL evaluation order will resolve it.
Explanation
Correct Answer
C:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. This targeted change addresses the stated technical or governance gap without discarding necessary controls.
Incorrect Answers
A:Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
B:Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Use this instead: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
Question 817
A Snowflake implementation review finds: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query. The team proposes: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals.
- QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
- CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
- Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Explanation
Correct Answer
B:Relevant validation before approval: Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps. Demonstrable success requires: Exactly the intended ranked records remain with deterministic tie rules.
Incorrect Answers
A:Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals. A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query.
C:This option addresses a different Snowflake technical topic (CTAS and transient transformation tables) rather than the documented QUALIFY filtering window expressions decision.
D:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query.
Question 818
The data platform team has not yet approved changes. Current evidence indicates: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Count conversion failures, review source examples and reconcile accepted versus quarantined records.
- Compare query IDs, duration, queue time, bytes scanned, spill and credits over equivalent workloads.
- Compare task throughput, credits per successful run, latency and error rates before and after each change.
- Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps.
Explanation
Correct Answer
D:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Applied case: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query.
Incorrect Answers
A:Count conversion failures, review source examples and reconcile accepted versus quarantined records. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
B:Compare query IDs, duration, queue time, bytes scanned, spill and credits over equivalent workloads. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
C:Compare task throughput, credits per successful run, latency and error rates before and after each change. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 819
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query. The service owner needs positive and negative results and evidence that legitimate data workflows still function. Which acceptance result demonstrates correct platform behavior without disrupting authorized workflows? Select ONE best answer.
- Exactly the intended ranked records remain with deterministic tie rules.
- Valid values are typed correctly and bad data is isolated rather than silently misreported.
- Measured eligible queries improve sufficiently to justify serverless charges.
- The target reflects one authorized current record per business key after updates and inserts.
Explanation
Correct Answer
A:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. The approved implementation step was: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
Incorrect Answers
B:Valid values are typed correctly and bad data is isolated rather than silently misreported. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
C:Measured eligible queries improve sufficiently to justify serverless charges. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
D:The target reflects one authorized current record per business key after updates and inserts. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
Question 820
For an original SnowPro Core educational scenario, consider: A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Change: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
- Check: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration.
- Change: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
- Check: Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps.
- Place ROW_NUMBER directly in WHERE and assume SQL evaluation order will resolve it.
Explanation
Correct Answer
C:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Acceptance: Exactly the intended ranked records remain with deterministic tie rules.
D:QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering. Required intervention: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
Incorrect Answers
A:Change: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. A developer applies HAVING to non-aggregate window rank and gets an invalid or misleading query.
B:Check: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
E:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. QUALIFY filters query results after window functions are evaluated and is distinct from ordinary WHERE filtering.
