Snowflake SnowPro Core Practice Test 71
Question 700
For an original SnowPro Core educational scenario, consider: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination. Select THREE compatible elements: a proper correction, a targeted diagnostic and an acceptance result.
- Result: The query reads fewer relevant partitions while returning identical filtered rows.
- Change: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
- Result: Each group has the intended ranked or rolling values while original row-level detail is preserved.
- Check: Count conversion failures, review source examples and reconcile accepted versus quarantined records.
- Change: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Check: Inspect query profile scanned partitions, predicate form and representative date distribution.
Explanation
Correct Answer
A:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Required intervention: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
E:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Acceptance: The query reads fewer relevant partitions while returning identical filtered rows.
F:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Inspect query profile scanned partitions, predicate form and representative date distribution.
Incorrect Answers
B:Change: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:Result: Each group has the intended ranked or rolling values while original row-level detail is preserved. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:Check: Count conversion failures, review source examples and reconcile accepted versus quarantined records. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 701
A Snowflake engineer is examining this platform situation: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data. A proposed shortcut is: Add multi-column clustering keys to every small lookup table without profiling its scan behavior. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Use QUERY_HISTORY and Query Profile metrics to compare consistent workloads, warehouse changes and time periods.
- Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions.
- Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
- Add multi-column clustering keys to every small lookup table without profiling its scan behavior.
Explanation
Correct Answer
C:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Applied case: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data.
Incorrect Answers
A:Use QUERY_HISTORY and Query Profile metrics to compare consistent workloads, warehouse changes and time periods. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
B:Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
Question 702
A Snowflake implementation review finds: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data. The team proposes: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- 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.
- Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
- Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses.
Explanation
Correct Answer
B:Demonstrable success requires: Important queries improve enough to justify ongoing clustering expense without changing business results. Applied case: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data.
Incorrect Answers
A: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.
C:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Question 703
The data platform team has not yet approved changes. Current evidence indicates: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
- Compare task throughput, credits per successful run, latency and error rates before and after each change.
- Compare query profile, clustering depth, maintenance credits and representative workload results.
- Compare average execution versus queue time and cluster saturation during a simulated peak.
Explanation
Correct Answer
C:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Confirm afterward that: Important queries improve enough to justify ongoing clustering expense without changing business results.
Incorrect Answers
A:Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
B:Compare task throughput, credits per successful run, latency and error rates before and after each change. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Compare average execution versus queue time and cluster saturation during a simulated peak. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Question 704
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data. 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 chosen configuration meets deadlines while improving or justifying cost per delivered result.
- Important queries improve enough to justify ongoing clustering expense without changing business results.
- A justifiable cache strategy improves repeated reads without keeping idle compute running solely by assumption.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
Explanation
Correct Answer
B:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. The result must be validated using the documented dataset or access context.
Incorrect Answers
A:The chosen configuration meets deadlines while improving or justifying cost per delivered result. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
C:A justifiable cache strategy improves repeated reads without keeping idle compute running solely by assumption. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Repeat eligible queries can avoid recomputing results without returning stale data after changes. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Question 705
An implementation sign-off request identifies the following problem: A massive transaction table is queried almost exclusively by customer and date, but selective queries scan poorly clustered data. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- Change: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table. Verify: Compare query profile, clustering depth, maintenance credits and representative workload results.
- Change: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost. Verify: Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
- 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: Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions. Verify: Compare task throughput, credits per successful run, latency and error rates before and after each change.
Explanation
Correct Answer
A:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. The change is successful only when: Important queries improve enough to justify ongoing clustering expense without changing business results.
Incorrect Answers
B:Change: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost.
C:Change: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
D:Change: Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions.
Question 706
A Snowflake engineer is examining this platform situation: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily. A proposed shortcut is: Add multi-column clustering keys to every small lookup table without profiling its scan behavior. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
- Use appropriate window functions with PARTITION BY and ordered frames for row-level ranking or rolling calculations.
- Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
- Add multi-column clustering keys to every small lookup table without profiling its scan behavior.
Explanation
Correct Answer
A:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. This targeted change addresses the stated technical or governance gap without discarding necessary controls.
Incorrect Answers
B:Use appropriate window functions with PARTITION BY and ordered frames for row-level ranking or rolling calculations. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
C:Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Use this instead: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
Question 707
A Snowflake implementation review finds: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily. The team proposes: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
- Snowflake performance optimization is an economic and service-level tradeoff; faster response alone does not prove improved efficiency.
- Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs.
- Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Explanation
Correct Answer
A:Demonstrable success requires: Important queries improve enough to justify ongoing clustering expense without changing business results. This principle explains the proposed technical approach, which is: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
Incorrect Answers
B:Snowflake performance optimization is an economic and service-level tradeoff; faster response alone does not prove improved efficiency. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
C:Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Question 708
The data platform team has not yet approved changes. Current evidence indicates: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Compare created table schema, row count, retention settings and downstream access after the initial session.
- Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs.
- Compare query profile, clustering depth, maintenance credits and representative workload results.
- Test source-key duplicates, matched row counts and idempotent replay of the same batch.
Explanation
Correct Answer
C:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Applied case: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily.
Incorrect Answers
A:Compare created table schema, row count, retention settings and downstream access after the initial session. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
B:Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
D:Test source-key duplicates, matched row counts and idempotent replay of the same batch. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
Question 709
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily. 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.
- Measured eligible queries improve sufficiently to justify serverless charges.
- Recurring reads improve without unacceptable data freshness or maintenance burden.
- The chosen configuration meets deadlines while improving or justifying cost per delivered result.
- Important queries improve enough to justify ongoing clustering expense without changing business results.
Explanation
Correct Answer
D:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. The approved implementation step was: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
Incorrect Answers
A:Measured eligible queries improve sufficiently to justify serverless charges. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
B:Recurring reads improve without unacceptable data freshness or maintenance burden. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
C:The chosen configuration meets deadlines while improving or justifying cost per delivered result. A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily.
Question 710
For an original SnowPro Core educational scenario, consider: A small dimension table with good natural pruning is given a complex clustering key that raises maintenance costs unnecessarily. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Check: Test row_number or moving-average output for customers with multiple timestamps and ties.
- Add multi-column clustering keys to every small lookup table without profiling its scan behavior.
- Check: Compare query profile, clustering depth, maintenance credits and representative workload results.
- Change: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
- Change: Use QUERY_HISTORY and Query Profile metrics to compare consistent workloads, warehouse changes and time periods.
Explanation
Correct Answer
C:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Acceptance: Important queries improve enough to justify ongoing clustering expense without changing business results.
D:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Evidence: Compare query profile, clustering depth, maintenance credits and representative workload results.
Incorrect Answers
A:Check: Test row_number or moving-average output for customers with multiple timestamps and ties. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
B:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
E:Change: Use QUERY_HISTORY and Query Profile metrics to compare consistent workloads, warehouse changes and time periods. Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
