Snowflake SnowPro Core Practice Test 74
Question 730
For an original SnowPro Core educational scenario, consider: A team creates a materialized view for a one-off exploratory query and ignores refresh maintenance consumption. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Change: Use appropriate window functions with PARTITION BY and ordered frames for row-level ranking or rolling calculations.
- Assume any arbitrary multi-join SQL statement is supported inside a materialized view.
- Check: Inspect INPUT, INDEX, VALUE, missing paths and resulting expanded row counts.
- Change: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost.
- Check: Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
Explanation
Correct Answer
D:Acceptance: Recurring reads improve without unacceptable data freshness or maintenance burden.
E:Required intervention: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost.
Incorrect Answers
A:Change: Use appropriate window functions with PARTITION BY and ordered frames for row-level ranking or rolling calculations. Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs.
B:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. Applied case: A team creates a materialized view for a one-off exploratory query and ignores refresh maintenance consumption.
C:Check: Inspect INPUT, INDEX, VALUE, missing paths and resulting expanded row counts. Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs.
Question 731
A Snowflake engineer is examining this platform situation: A report repeats the same deterministic query on unchanged data but executes expensive compute each time. A proposed shortcut is: Assume RESULT_SCAN or query-result caching permanently freezes every table's answer. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Assume RESULT_SCAN or query-result caching permanently freezes every table's answer.
- Evaluate concurrency scaling or workload separation and review queued overload time before rewriting correct SQL.
- Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table.
- Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
Explanation
Correct Answer
D:Validate the premise using: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. Applied case: A report repeats the same deterministic query on unchanged data but executes expensive compute each time.
Incorrect Answers
A:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
B:Evaluate concurrency scaling or workload separation and review queued overload time before rewriting correct SQL. A report repeats the same deterministic query on unchanged data but executes expensive compute each time.
C:Evaluate clustering depth and selective query benefit before choosing a supported clustering key on a sufficiently large table. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 732
A Snowflake implementation review finds: A report repeats the same deterministic query on unchanged data but executes expensive compute each time. The team proposes: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. 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.
- Query latency and credit use can change for multiple reasons including plan, data volume, queuing and warehouse resources.
- FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations.
- 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
D:Relevant validation before approval: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. This principle explains the proposed technical approach, which is: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
Incorrect Answers
A: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.
B:This option addresses a different Snowflake technical topic (Query History and performance regression) rather than the documented Persisted query result cache decision.
C:FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations. A report repeats the same deterministic query on unchanged data but executes expensive compute each time.
Question 733
The data platform team has not yet approved changes. Current evidence indicates: A report repeats the same deterministic query on unchanged data but executes expensive compute each time. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Inspect query eligibility and acceleration history plus incremental service credits for representative workloads.
- Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
- Inspect profile operator times, spilled bytes, join row counts and warehouse configuration.
- Compare eligible predicates, row selectivity, search access path use and service credit cost.
Explanation
Correct Answer
B:If confirmed, the proportionate intervention is: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Applied case: A report repeats the same deterministic query on unchanged data but executes expensive compute each time.
Incorrect Answers
A:Inspect query eligibility and acceleration history plus incremental service credits for representative workloads. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
C:Inspect profile operator times, spilled bytes, join row counts and warehouse configuration. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
D:Compare eligible predicates, row selectivity, search access path use and service credit cost. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 734
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A report repeats the same deterministic query on unchanged data but executes expensive compute each time. 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.
- Aggregated measures reconcile to the intended grain and avoid fanout inflation.
- Important queries improve enough to justify ongoing clustering expense without changing business results.
- Valid values are typed correctly and bad data is isolated rather than silently misreported.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
Explanation
Correct Answer
D:The approved implementation step was: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Applied case: A report repeats the same deterministic query on unchanged data but executes expensive compute each time.
Incorrect Answers
A:Aggregated measures reconcile to the intended grain and avoid fanout inflation. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
B:Important queries improve enough to justify ongoing clustering expense without changing business results. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
C:Valid values are typed correctly and bad data is isolated rather than silently misreported. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 735
An implementation sign-off request identifies the following problem: A report repeats the same deterministic query on unchanged data but executes expensive compute each time. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- 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.
- Change: Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing. Verify: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration.
- Change: Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation. Verify: Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs.
- Change: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Verify: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
Explanation
Correct Answer
D:The change is successful only when: Repeat eligible queries can avoid recomputing results without returning stale data after changes.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (Monitoring cost and performance together) rather than the documented Persisted query result cache decision.
B:Change: Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing.
C:Change: Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 736
A Snowflake engineer is examining this platform situation: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes. A proposed shortcut is: Assume RESULT_SCAN or query-result caching permanently freezes every table's answer. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
- Assume RESULT_SCAN or query-result caching permanently freezes every table's answer.
- Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
Explanation
Correct Answer
D:Validate the premise using: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. Applied case: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (QUALIFY filtering window expressions) rather than the documented Persisted query result cache decision.
B:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. Use this instead: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
C:This option addresses a different Snowflake technical topic (Micro-partition pruning with selective predicates) rather than the documented Persisted query result cache decision.
Question 737
A Snowflake implementation review finds: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes. The team proposes: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Query acceleration can offload eligible portions of selected workloads to serverless compute; not every query or operator benefits.
- Snowpark can push supported operations to Snowflake compute; premature data collection or unsupported runtime assumptions add cost and risk.
- Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
- Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans.
Explanation
Correct Answer
C:Relevant validation before approval: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. Applied case: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
Incorrect Answers
A:Query acceleration can offload eligible portions of selected workloads to serverless compute; not every query or operator benefits. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
B:This option addresses a different Snowflake technical topic (Snowpark pushdown and Python UDF boundaries) rather than the documented Persisted query result cache decision.
D:Search optimization maintains a search access path for eligible lookup patterns, with storage and maintenance costs distinct from warehouse scans. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 738
The data platform team has not yet approved changes. Current evidence indicates: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Test source-key duplicates, matched row counts and idempotent replay of the same batch.
- Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Check source counts, grouping keys and a small reference reconciliation with known customer transactions.
- Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
Explanation
Correct Answer
D:If confirmed, the proportionate intervention is: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Applied case: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
Incorrect Answers
A:Test source-key duplicates, matched row counts and idempotent replay of the same batch. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
B:Compare eligible predicates, row selectivity, search access path use and service credit cost. An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
C:Check source counts, grouping keys and a small reference reconciliation with known customer transactions. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 739
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes. 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.
- Valid values are typed correctly and bad data is isolated rather than silently misreported.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
- The join yields expected grain and aggregate totals reconcile to source transactions.
Explanation
Correct Answer
C:The approved implementation step was: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism. Applied case: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
Incorrect Answers
A:The bottleneck is reduced with comparable results and defensible credit use. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
B:Valid values are typed correctly and bad data is isolated rather than silently misreported. An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
D:The join yields expected grain and aggregate totals reconcile to source transactions. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
Question 740
For an original SnowPro Core educational scenario, consider: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Check: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
- Check: Compare eligible predicates, row selectivity, search access path use and service credit cost.
- Change: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost.
- Assume RESULT_SCAN or query-result caching permanently freezes every table's answer.
- Change: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
Explanation
Correct Answer
A:Required intervention: Evaluate persisted query result reuse conditions, including unchanged underlying data and eligible query determinism.
E:Evidence: Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
Incorrect Answers
B:Check: Compare eligible predicates, row selectivity, search access path use and service credit cost. Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
C:This option addresses a different Snowflake technical topic (Materialized views for repeated aggregation) rather than the documented Persisted query result cache decision.
D:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. Applied case: An analyst changes session settings and underlying tables and expects persisted results to be reused regardless of those changes.
