Snowflake SnowPro Core Practice Test 70
Question 690
For an original SnowPro Core educational scenario, consider: Operations sees successful Snowpipe status yet a subset of files has parsing errors and no alert reaches the owner. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Change: Use COPY INTO <location> with a supported file format, target stage or secure external location and governed permissions.
- Change: Correlate COPY_HISTORY, pipe diagnostics, stage listings and target row checks to isolate load versus transformation failures.
- Assume a green pipeline dashboard proves every source row was loaded successfully.
- Check: Inspect recent load history, file status and error messages with a controlled problem file.
- Check: Check client-channel status, offset or commit acknowledgement and measured end-to-end event latency.
Explanation
Correct Answer
B:Ingestion observability requires per-file and per-row evidence; event receipt alone does not prove records reached the destination. Acceptance: Operators can explain each expected file as loaded, pending or rejected and resolve the actual cause.
D:Ingestion observability requires per-file and per-row evidence; event receipt alone does not prove records reached the destination. Required intervention: Correlate COPY_HISTORY, pipe diagnostics, stage listings and target row checks to isolate load versus transformation failures.
Incorrect Answers
A:Change: Use COPY INTO <location> with a supported file format, target stage or secure external location and governed permissions. Operations sees successful Snowpipe status yet a subset of files has parsing errors and no alert reaches the owner.
C:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. Ingestion observability requires per-file and per-row evidence; event receipt alone does not prove records reached the destination.
E:Check: Check client-channel status, offset or commit acknowledgement and measured end-to-end event latency. Ingestion observability requires per-file and per-row evidence; event receipt alone does not prove records reached the destination.
Question 691
A Snowflake engineer is examining this platform situation: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition. A proposed shortcut is: Assume creating a traditional B-tree index on every native Snowflake table is the normal pruning mechanism. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Assume creating a traditional B-tree index on every native Snowflake table is the normal pruning mechanism.
- Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing.
- Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Evaluate supported Query Acceleration Service on eligible queries using its estimate and cost controls.
Explanation
Correct Answer
C:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Applied case: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition.
Incorrect Answers
A:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
B:Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:Evaluate supported Query Acceleration Service on eligible queries using its estimate and cost controls. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 692
A Snowflake implementation review finds: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition. The team proposes: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
- High concurrency can cause queuing independently of individual query efficiency; multi-cluster compute targets concurrent demand.
- 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.
- FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations.
Explanation
Correct Answer
A:Relevant validation before approval: Inspect query profile scanned partitions, predicate form and representative date distribution. Applied case: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition.
Incorrect Answers
B:High concurrency can cause queuing independently of individual query efficiency; multi-cluster compute targets concurrent demand. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C: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. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:FLATTEN expands array elements, while VARIANT values often require explicit casts for typed calculations. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 693
The data platform team has not yet approved changes. Current evidence indicates: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition. 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.
- Inspect query profile scanned partitions, predicate form and representative date distribution.
- Inspect query eligibility and acceleration history plus incremental service credits for representative workloads.
- Compare average execution versus queue time and cluster saturation during a simulated peak.
Explanation
Correct Answer
B:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Confirm afterward that: The query reads fewer relevant partitions while returning identical filtered rows.
Incorrect Answers
A:Compare created table schema, row count, retention settings and downstream access after the initial session. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:Inspect query eligibility and acceleration history plus incremental service credits for representative workloads. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:Compare average execution versus queue time and cluster saturation during a simulated peak. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 694
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition. 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.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
- A justifiable cache strategy improves repeated reads without keeping idle compute running solely by assumption.
- The query reads fewer relevant partitions while returning identical filtered rows.
Explanation
Correct Answer
D:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. The result must be validated using the documented dataset or access context.
Incorrect Answers
A:Measured eligible queries improve sufficiently to justify serverless charges. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
B:Repeat eligible queries can avoid recomputing results without returning stale data after changes. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:A justifiable cache strategy improves repeated reads without keeping idle compute running solely by assumption. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 695
An implementation sign-off request identifies the following problem: A fact table holds years of orders but most reports filter one week and still scan almost every micro-partition. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- 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: 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.
- 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: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. Verify: Inspect query profile scanned partitions, predicate form and representative date distribution.
Explanation
Correct Answer
D:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. The change is successful only when: The query reads fewer relevant partitions while returning identical filtered rows.
Incorrect Answers
A:Change: Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation.
B:Change: Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:Change: Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns.
Question 696
A Snowflake engineer is examining this platform situation: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination. A proposed shortcut is: Assume creating a traditional B-tree index on every native Snowflake table is the normal pruning mechanism. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions.
- Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation.
- Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
- Assume creating a traditional B-tree index on every native Snowflake table is the normal pruning mechanism.
Explanation
Correct Answer
C:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Applied case: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination.
Incorrect Answers
A:Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
B:Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. Use this instead: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
Question 697
A Snowflake implementation review finds: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination. The team proposes: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Query latency and credit use can change for multiple reasons including plan, data volume, queuing and warehouse resources.
- Snowpark can push supported operations to Snowflake compute; premature data collection or unsupported runtime assumptions add cost and risk.
- Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
- Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses.
Explanation
Correct Answer
C:Relevant validation before approval: Inspect query profile scanned partitions, predicate form and representative date distribution. Applied case: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination.
Incorrect Answers
A:Query latency and credit use can change for multiple reasons including plan, data volume, queuing and warehouse resources. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
B:Snowpark can push supported operations to Snowflake compute; premature data collection or unsupported runtime assumptions add cost and risk. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D:Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
Question 698
The data platform team has not yet approved changes. Current evidence indicates: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Inspect query profile scanned partitions, predicate form and representative date distribution.
- Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
- Compare generated SQL plans, data transfer volumes and managed package availability for a sample transform.
- Count conversion failures, review source examples and reconcile accepted versus quarantined records.
Explanation
Correct Answer
A:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. This check tests the actual data, compute or access boundary before an operationally risky change.
Incorrect Answers
B:Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:Compare generated SQL plans, data transfer volumes and managed package availability for a sample transform. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
D: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 699
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A developer filters an indexed-looking date string after applying a function that prevents effective partition elimination. 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 refreshes reliably within an accepted practical interval and alerts on failures.
- Valid values are typed correctly and bad data is isolated rather than silently misreported.
- The chosen configuration meets deadlines while improving or justifying cost per delivered result.
- The query reads fewer relevant partitions while returning identical filtered rows.
Explanation
Correct Answer
D:Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning. The approved implementation step was: Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations.
Incorrect Answers
A:The pipeline refreshes reliably within an accepted practical interval and alerts on failures. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
B:Valid values are typed correctly and bad data is isolated rather than silently misreported. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
C:The chosen configuration meets deadlines while improving or justifying cost per delivered result. Snowflake metadata can skip micro-partitions whose ranges do not satisfy filters; poor layout or non-sargable expressions reduce pruning.
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.
