Snowflake SnowPro Core Practice Test 86
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.
Question 851
A Snowflake engineer is examining this platform situation: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT. A proposed shortcut is: Assume every CTAS table is automatically a dynamic view that refreshes whenever source data changes. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
- 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.
- Assume every CTAS table is automatically a dynamic view that refreshes whenever source data changes.
Explanation
Correct Answer
A:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Applied case: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT.
Incorrect Answers
B:Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:Evaluate Snowflake Search Optimization Service for supported highly selective predicates and scoped table columns. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. For this best change assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it.
Question 852
A Snowflake implementation review finds: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT. The team proposes: Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- Grouping defines output grain and aggregate semantics, so duplicates and non-grouped columns can invalidate or distort totals.
- 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.
- 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.
Explanation
Correct Answer
B:Demonstrable success requires: Required transformed datasets remain available for the intended lifecycle without accidental duplication. Applied case: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (Aggregations and grouping correctness) rather than the documented CTAS and transient transformation tables decision.
C:This option addresses a different Snowflake technical topic (Clustering keys and maintenance costs) rather than the documented CTAS and transient transformation tables decision.
D:This option addresses a different Snowflake technical topic (TRY_CAST and malformed source records) rather than the documented CTAS and transient transformation tables decision.
Question 853
The data platform team has not yet approved changes. Current evidence indicates: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT. 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 an equivalent subquery with QUALIFY and test tie handling using ordered timestamps.
- Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs.
- Inspect profile operator times, spilled bytes, join row counts and warehouse configuration.
Explanation
Correct Answer
A:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Confirm afterward that: Required transformed datasets remain available for the intended lifecycle without accidental duplication.
Incorrect Answers
B:Compare an equivalent subquery with QUALIFY and test tie handling using ordered timestamps. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:Inspect profile operator times, spilled bytes, join row counts and warehouse configuration. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
Question 854
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT. 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.
- Required transformed datasets remain available for the intended lifecycle without accidental duplication.
- The join yields expected grain and aggregate totals reconcile to source transactions.
- The pipeline processes supported data within Snowflake while downloading only necessary results.
Explanation
Correct Answer
B:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. 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. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:The join yields expected grain and aggregate totals reconcile to source transactions. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:The pipeline processes supported data within Snowflake while downloading only necessary results. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
Question 855
An implementation sign-off request identifies the following problem: A data engineer needs a reproducible transformed snapshot for downstream testing but repeatedly reruns a large SELECT. Which paired technical change and validation should close the implementation review? Select ONE best answer.
- 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 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: 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 CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management. Verify: Compare created table schema, row count, retention settings and downstream access after the initial session.
Explanation
Correct Answer
D:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. The change is successful only when: Required transformed datasets remain available for the intended lifecycle without accidental duplication.
Incorrect Answers
A:Change: Tune appropriate suspension and workload locality after measuring warehouse data-cache benefit and idle credit cost. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
B:Change: Evaluate a supported materialized view for a repeatable eligible query with justified refresh and storage cost. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:Change: Track query duration, warehouse and serverless credits, data volume and service objectives together for tuning decisions. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
Question 856
A Snowflake engineer is examining this platform situation: A team creates temporary tables for downstream reports that must outlive the session. A proposed shortcut is: Assume every CTAS table is automatically a dynamic view that refreshes whenever source data changes. What should the Snowflake engineer recommend as the narrowly scoped correction? Select ONE best answer.
- Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
- Assume every CTAS table is automatically a dynamic view that refreshes whenever source data changes.
- 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.
Explanation
Correct Answer
A:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Applied case: A team creates temporary tables for downstream reports that must outlive the session.
Incorrect Answers
B:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Use this instead: Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
C:Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:Use selective predicates on suitable stored columns and evaluate micro-partition pruning before adding other optimizations. A team creates temporary tables for downstream reports that must outlive the session.
Question 857
A Snowflake implementation review finds: A team creates temporary tables for downstream reports that must outlive the session. The team proposes: Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management. Which platform principle most strongly supports the recommended response? Select ONE best answer.
- MERGE performs conditional updates, deletes or inserts from source rows; nondeterministic source matches can cause errors or incorrect outcomes.
- CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
- 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: Required transformed datasets remain available for the intended lifecycle without accidental duplication. Applied case: A team creates temporary tables for downstream reports that must outlive the session.
Incorrect Answers
A:This option addresses a different Snowflake technical topic (MERGE incremental upserts) rather than the documented CTAS and transient transformation tables decision.
C:Snowflake may reuse prior query results subject to eligibility and validity conditions; this cache is different from a warehouse's local data cache. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:Local warehouse cache is associated with running compute and does not equal reusable persisted query results across warehouses. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
Question 858
The data platform team has not yet approved changes. Current evidence indicates: A team creates temporary tables for downstream reports that must outlive the session. 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.
- Test row_number or moving-average output for customers with multiple timestamps and ties.
- Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges.
- Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs.
Explanation
Correct Answer
A:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. This check tests the actual data, compute or access boundary before an operationally risky change.
Incorrect Answers
B:Test row_number or moving-average output for customers with multiple timestamps and ties. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:Check query eligibility, refresh behavior, query rewrite usage and storage or maintenance charges. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
D:Compare source key uniqueness, pre-join counts and resulting row counts for representative business IDs. A team creates temporary tables for downstream reports that must outlive the session.
Question 859
A candidate correction has been tested and the platform team requires a post-change acceptance check. Original problem: A team creates temporary tables for downstream reports that must outlive the session. 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.
- Required transformed datasets remain available for the intended lifecycle without accidental duplication.
- The chosen configuration meets deadlines while improving or justifying cost per delivered result.
- The join yields expected grain and aggregate totals reconcile to source transactions.
- Exactly the intended ranked records remain with deterministic tie rules.
Explanation
Correct Answer
A:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. The approved implementation step was: Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
Incorrect Answers
B:The chosen configuration meets deadlines while improving or justifying cost per delivered result. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:The join yields expected grain and aggregate totals reconcile to source transactions. A team creates temporary tables for downstream reports that must outlive the session.
D:Exactly the intended ranked records remain with deterministic tie rules. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
Question 860
For an original SnowPro Core educational scenario, consider: A team creates temporary tables for downstream reports that must outlive the session. Select TWO complementary steps: the targeted correction and its strongest prechange validation.
- Change: Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation.
- Assume every CTAS table is automatically a dynamic view that refreshes whenever source data changes.
- Check: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration.
- Check: Compare created table schema, row count, retention settings and downstream access after the initial session.
- Change: Use CREATE TABLE AS SELECT with an appropriate table type and explicit lifecycle or dependency management.
Explanation
Correct Answer
D:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Acceptance: Required transformed datasets remain available for the intended lifecycle without accidental duplication.
E:CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type. Evidence: Compare created table schema, row count, retention settings and downstream access after the initial session.
Incorrect Answers
A:Change: Define correct join keys and grain, deduplicate where required and validate output cardinality before aggregation. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
B:For this select two assessment, the shortcut changes or bypasses the necessary correctness, governance or operational boundary rather than correcting it. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
C:Check: Inspect profile operator times, spilled bytes, join row counts and warehouse configuration. CTAS materializes query results into a new table; its persistence and storage cost depend on the target table type.
