Snowflake SnowPro Core Practice Test 85
Question 840
For an original SnowPro Core educational scenario, consider: An ETL script blindly casts ISO date strings even when a few records contain impossible dates. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Replace invalid numeric values with zero without logging or analyzing the data-quality impact.
- Change: Express supported filters and transformations as Snowpark operations and use governed execution runtimes for Python-specific steps.
- Check: Count conversion failures, review source examples and reconcile accepted versus quarantined records.
- Check: Compare actual refresh lag, failures, dependency graphs and credits against required reporting SLA.
- Change: Use suitable TRY_TO_NUMBER or TRY_TO_DATE style conversions with separate bad-record detection and remediation.
Explanation
Correct Answer
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. Acceptance: Valid values are typed correctly and bad data is isolated rather than silently misreported.
E: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. Evidence: Count conversion failures, review source examples and reconcile accepted versus quarantined records.
Incorrect Answers
A:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. 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.
B:Change: Express supported filters and transformations as Snowpark operations and use governed execution runtimes for Python-specific steps. 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.
D:Check: Compare actual refresh lag, failures, dependency graphs and credits against required reporting SLA. 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.
Question 841
A Snowflake engineer is examining this platform situation: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates. A proposed shortcut is: Append every CDC record without a key strategy and call the result an upsert. 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.
- Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
- Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs.
- Append every CDC record without a key strategy and call the result an upsert.
Explanation
Correct Answer
B:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. This targeted change addresses the stated technical or governance gap without discarding necessary controls.
Incorrect Answers
A:Use QUERY_HISTORY and Query Profile metrics to compare consistent workloads, warehouse changes and time periods. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
C:Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
Question 842
A Snowflake implementation review finds: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates. The team proposes: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- SQL joins combine matching records according to relational cardinality; incorrect relationships multiply rows and distort measures.
- Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache.
- Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals.
- MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
Explanation
Correct Answer
D:Demonstrable success requires: The target reflects one authorized current record per business key after updates and inserts. This principle explains the proposed technical approach, which is: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
Incorrect Answers
A:SQL joins combine matching records according to relational cardinality; incorrect relationships multiply rows and distort measures. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
B:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
C:Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
Question 843
The data platform team has not yet approved changes. Current evidence indicates: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates. 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.
- Test source-key duplicates, matched row counts and idempotent replay of the same batch.
- Compare task throughput, credits per successful run, latency and error rates before and after each change.
- Inspect query eligibility and acceleration history plus incremental service credits for representative workloads.
Explanation
Correct Answer
B:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. Applied case: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates.
Incorrect Answers
A:Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
C:This option addresses a different Snowflake technical topic (Monitoring cost and performance together) rather than the documented MERGE incremental upserts decision.
D:This option addresses a different Snowflake technical topic (Query Acceleration Service eligibility) rather than the documented MERGE incremental upserts decision.
Question 844
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates. 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.
- 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.
- The target reflects one authorized current record per business key after updates and inserts.
- Each group has the intended ranked or rolling values while original row-level detail is preserved.
Explanation
Correct Answer
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. The result must be validated using the documented dataset or access context.
Incorrect Answers
A:Repeat eligible queries can avoid recomputing results without returning stale data after changes. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
B:The join yields expected grain and aggregate totals reconcile to source transactions. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:Each group has the intended ranked or rolling values while original row-level detail is preserved. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
Question 845
An implementation sign-off request identifies the following problem: A daily customer feed includes both updates to existing IDs and new customers, and straight INSERT produces duplicates. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- Change: Use suitable TRY_TO_NUMBER or TRY_TO_DATE style conversions with separate bad-record detection and remediation. Verify: Count conversion failures, review source examples and reconcile accepted versus quarantined records.
- 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.
- Change: Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs. Verify: Compare actual refresh lag, failures, dependency graphs and credits against required reporting SLA.
Explanation
Correct Answer
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. The change is successful only when: The target reflects one authorized current record per business key after updates and inserts.
Incorrect Answers
A:Change: Use suitable TRY_TO_NUMBER or TRY_TO_DATE style conversions with separate bad-record detection and remediation.
B:This option addresses a different Snowflake technical topic (Snowpark pushdown and Python UDF boundaries) rather than the documented MERGE incremental upserts decision.
D:Change: Review dynamic table refresh history, eligibility and resource use, then set an achievable target lag for business freshness needs.
Question 846
A Snowflake engineer is examining this platform situation: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled. A proposed shortcut is: Append every CDC record without a key strategy and call the result an upsert. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost.
- Use the query profile to identify spill or join skew, then consider query changes and supported warehouse resizing.
- Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
- Append every CDC record without a key strategy and call the result an upsert.
Explanation
Correct Answer
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. Applied case: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled.
Incorrect Answers
A:Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
B:This option addresses a different Snowflake technical topic (Query profile spilling and repartitioning) rather than the documented MERGE incremental upserts decision.
D:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. Use this instead: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
Question 847
A Snowflake implementation review finds: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled. The team proposes: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness. 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.
- MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
- Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs.
- 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: The target reflects one authorized current record per business key after updates and inserts. Applied case: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (CTAS and transient transformation tables) rather than the documented MERGE incremental upserts decision.
C:Materialized views retain precomputed query results and have feature restrictions and maintenance tradeoffs. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
Question 848
The data platform team has not yet approved changes. Current evidence indicates: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled. Which evidence best tests the technical diagnosis before approving a disruptive change? Select ONE best answer.
- Compare query profile, clustering depth, maintenance credits and representative workload results.
- Check source counts, grouping keys and a small reference reconciliation with known customer transactions.
- Test source-key duplicates, matched row counts and idempotent replay of the same batch.
- Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test.
Explanation
Correct Answer
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. This check tests the actual data, compute or access boundary before an operationally risky change.
Incorrect Answers
A:Compare query profile, clustering depth, maintenance credits and representative workload results. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
B:Check source counts, grouping keys and a small reference reconciliation with known customer transactions. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:Compare QUERY_HISTORY result reuse and query text or underlying data changes in a controlled test. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
Question 849
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled. 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.
- Repeat eligible queries can avoid recomputing results without returning stale data after changes.
- Important queries improve enough to justify ongoing clustering expense without changing business results.
- The target reflects one authorized current record per business key after updates and inserts.
- Targeted point-lookups accelerate while maintenance costs remain proportional to business value.
Explanation
Correct Answer
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. The approved implementation step was: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
Incorrect Answers
A:Repeat eligible queries can avoid recomputing results without returning stale data after changes. A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled.
B:Important queries improve enough to justify ongoing clustering expense without changing business results. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:Targeted point-lookups accelerate while maintenance costs remain proportional to business value. A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled.
Question 850
For an original SnowPro Core educational scenario, consider: A streaming CDC pipeline replays a batch and overwrites newer target values because source ordering is not controlled. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Change: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
- Append every CDC record without a key strategy and call the result an upsert.
- Check: Test source-key duplicates, matched row counts and idempotent replay of the same batch.
- Change: Use QUALIFY to filter on the result of window functions after proper PARTITION BY and ORDER BY.
- Check: Inspect query profile scanned partitions, predicate form and representative date distribution.
Explanation
Correct Answer
A:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. Evidence: Test source-key duplicates, matched row counts and idempotent replay of the same batch.
C:MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes. Required intervention: Use an appropriate MERGE with deterministic matched and unmatched clauses and validated source uniqueness.
Incorrect Answers
B:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
D:This option addresses a different Snowflake technical topic (QUALIFY filtering window expressions) rather than the documented MERGE incremental upserts decision.
E:Check: Inspect query profile scanned partitions, predicate form and representative date distribution. MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
