Snowflake SnowPro Core Practice Test 72
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.
Question 711
A Snowflake engineer is examining this platform situation: Analysts perform repeated point lookups on a huge table using rare account identifiers and see high scan cost. A proposed shortcut is: Assume search optimization automatically speeds every large GROUP BY or full-table aggregation. 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.
- Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
- Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs.
- Assume search optimization automatically speeds every large GROUP BY or full-table aggregation.
Explanation
Correct Answer
B:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. 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. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
C:Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
Question 712
A Snowflake implementation review finds: Analysts perform repeated point lookups on a huge table using rare account identifiers and see high scan cost. The team proposes: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
- Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step.
- Local or remote spill indicates execution pressure and can be affected by memory limits, join cardinality and data distribution.
- Snowflake performance optimization is an economic and service-level tradeoff; faster response alone does not prove improved efficiency.
Explanation
Correct Answer
A:Relevant validation before approval: Compare eligible predicates, row selectivity, search access path use and service credit cost. Demonstrable success requires: Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
Incorrect Answers
B:Clustering can improve locality and pruning but consumes maintenance resources and is not a universal tuning step. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
C:Local or remote spill indicates execution pressure and can be affected by memory limits, join cardinality and data distribution. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Snowflake performance optimization is an economic and service-level tradeoff; faster response alone does not prove improved efficiency. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 713
The data platform team has not yet approved changes. Current evidence indicates: Analysts perform repeated point lookups on a huge table using rare account identifiers and see high scan cost. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Compare bytes scanned from cache, suspend-resume behavior and warehouse IDs for repeated queries.
- Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs.
- Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
Explanation
Correct Answer
C:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Confirm afterward that: Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
Incorrect Answers
A:Compare bytes scanned from cache, suspend-resume behavior and warehouse IDs for repeated queries. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
B:Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 714
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: Analysts perform repeated point lookups on a huge table using rare account identifiers and see high scan cost. 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 pipeline processes supported data within Snowflake while downloading only necessary results.
- The join yields expected grain and aggregate totals reconcile to source transactions.
- Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
- Exactly the intended ranked records remain with deterministic tie rules.
Explanation
Correct Answer
C:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. The result must be validated using the documented dataset or access context.
Incorrect Answers
A:The pipeline processes supported data within Snowflake while downloading only necessary results. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
B:The join yields expected grain and aggregate totals reconcile to source transactions. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Exactly the intended ranked records remain with deterministic tie rules. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 715
An implementation sign-off request identifies the following problem: Analysts perform repeated point lookups on a huge table using rare account identifiers and see high scan cost. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- 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.
- 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 Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. Verify: Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Change: Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost. Verify: Compare bytes scanned from cache, suspend-resume behavior and warehouse IDs for repeated queries.
Explanation
Correct Answer
C:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. The change is successful only when: Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
Incorrect Answers
A:Change: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
B:Change: Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
D:Change: Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 716
A Snowflake engineer is examining this platform situation: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity. A proposed shortcut is: Assume search optimization automatically speeds every large GROUP BY or full-table aggregation. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
- Assume search optimization automatically speeds every large GROUP BY or full-table aggregation.
- Use LATERAL FLATTEN and explicit path and value casts to transform arrays into relational rows.
- Evaluate concurrency scaling or workload separation and review queued overload time before rewriting correct SQL.
Explanation
Correct Answer
A:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Applied case: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity.
Incorrect Answers
B:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Use this instead: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
C:Use LATERAL FLATTEN and explicit path and value casts to transform arrays into relational rows. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Evaluate concurrency scaling or workload separation and review queued overload time before rewriting correct SQL. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 717
A Snowflake implementation review finds: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity. The team proposes: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- TRY-style conversions can yield NULL for invalid input instead of stopping an entire transform, but silently ignoring invalid values is still a quality risk.
- Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
- High concurrency can cause queuing independently of individual query efficiency; multi-cluster compute targets concurrent demand.
- Snowpark can push supported operations to Snowflake compute; premature data collection or unsupported runtime assumptions add cost and risk.
Explanation
Correct Answer
B:Relevant validation before approval: Compare eligible predicates, row selectivity, search access path use and service credit cost. Applied case: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity.
Incorrect Answers
A:TRY-style conversions can yield NULL for invalid input instead of stopping an entire transform, but silently ignoring invalid values is still a quality risk. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
C:High concurrency can cause queuing independently of individual query efficiency; multi-cluster compute targets concurrent demand. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Snowpark can push supported operations to Snowflake compute; premature data collection or unsupported runtime assumptions add cost and risk. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 718
The data platform team has not yet approved changes. Current evidence indicates: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Check source counts, grouping keys and a small reference reconciliation with known customer transactions.
- Count conversion failures, review source examples and reconcile accepted versus quarantined records.
- Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Compare generated SQL plans, data transfer volumes and managed package availability for a sample transform.
Explanation
Correct Answer
C:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. This check tests the actual data, compute or access boundary before an operationally risky change.
Incorrect Answers
A:Check source counts, grouping keys and a small reference reconciliation with known customer transactions. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
B:Count conversion failures, review source examples and reconcile accepted versus quarantined records. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
D:Compare generated SQL plans, data transfer volumes and managed package availability for a sample transform. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 719
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity. 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 bottleneck is reduced with comparable results and defensible credit use.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
- Recurring reads improve without unacceptable data freshness or maintenance burden.
- Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
Explanation
Correct Answer
D:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. The approved implementation step was: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
Incorrect Answers
A:The bottleneck is reduced with comparable results and defensible credit use. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
B:Repeat eligible queries can avoid recomputing results without returning stale data after changes. A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity.
C:Recurring reads improve without unacceptable data freshness or maintenance burden. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Question 720
For an original SnowPro Core educational scenario, consider: A product owner enables search optimization on every column of a bulk aggregate table despite low selectivity. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Assume search optimization automatically speeds every large GROUP BY or full-table aggregation.
- Check: Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Change: Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions.
- 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.
Explanation
Correct Answer
B:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Acceptance: Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
D:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Evidence: Compare eligible predicates, row selectivity, search access path use and service credit cost.
Incorrect Answers
A:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
C:Change: Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
E:Check: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration. Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
