Snowflake SnowPro Core COF-C03 Practice Test 87
Question 860
[Architecture planning workshop] The team proposes: Create an appropriate stream on the table and consume its change records transactionally through a downstream process. Its actual requirement is: An internal platform architecture group is assessing the initial design. A warehouse engineer needs to process only changes to an operational table since the last successfully consumed offset. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- After controlled source updates, the stream returns the expected delta, and a committed MERGE advances the consumption checkpoint.
- Table type and retention settings match the policy and a controlled within-retention restoration test succeeds.
- A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys.
- A controlled INSERT/UPDATE leads to expected stream records, and the offset advances after the consuming transaction commits.
Explanation
Correct Answer
D:A controlled INSERT/UPDATE leads to expected stream records, and the offset advances after the consuming transaction commits. This is an outcome test of the specified stream object change tracking action under the architecture planning workshop conditions. Streams track change-data-capture offsets for supported source objects; they are not the schedule or compute executor themselves.
Incorrect Answers
A:After controlled source updates, the stream returns the expected delta, and a committed MERGE advances the consumption checkpoint. This is an outcome for a different requirement: An orders table changes frequently and a downstream merge process must apply inserts and updates since its last committed checkpoint. It cannot by itself validate the proposed stream object change tracking action; the appropriate test is: A controlled INSERT/UPDATE leads to expected stream records, and the offset advances after the consuming transaction commits.
B:In this architecture planning workshop context, Table type and retention settings match the policy and a controlled within-retention restoration test succeeds. This is an outcome for a different requirement: An audited production dataset requires recovery mechanisms beyond a short staging lifecycle, and accidental deletion is a realistic threat. It cannot by itself validate the proposed stream object change tracking action; the appropriate test is: A controlled INSERT/UPDATE leads to expected stream records, and the offset advances after the consuming transaction commits.
C:A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys. This is an outcome for a different requirement: An analyst overwrites a segment mapping at noon and must inspect the table as it existed just before the change while still inside retention. It cannot by itself validate the proposed stream object change tracking action; the appropriate test is: A controlled INSERT/UPDATE leads to expected stream records, and the offset advances after the consuming transaction commits.
Question 861
[Security control assessment] A reviewer recommends: Define and attach a row access policy that evaluates approved regional role or mapping criteria. The requirement remains: A quarterly access-control audit requires an evidence-backed answer. Regional managers share a table but must see only the geographic rows permitted by their assigned region. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Row access policies constrain which rows are returned at query time; a column masking policy does not filter rows.
- Masking evaluates the policy at query time so access to rows need not imply visibility of the underlying sensitive value.
- FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item.
- Network restrictions add connection-layer allow/deny rules; SQL role grants do not enforce origin address policy.
Explanation
Correct Answer
A:In this security control assessment context, Row access policies constrain which rows are returned at query time; a column masking policy does not filter rows. The recommendation is linked to observable evidence for this exact case: Separate user-role tests return only the authorized region's row keys while total source data remains unchanged. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
B:That option concerns dynamic data masking policy, not the requirement in this case. It does not justify the specific row access policy filtering action or its validation path: Separate user-role tests return only the authorized region's row keys while total source data remains unchanged.
C:FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item.
D:That option concerns network policy address restrictions, not the requirement in this case. It does not justify the specific row access policy filtering action or its validation path: Separate user-role tests return only the authorized region's row keys while total source data remains unchanged.
Question 862
[Production migration readiness] A reviewer recommends: Evaluate hybrid tables for the transactional access pattern and verify feature and account availability. The requirement remains: A production migration team is preparing a controlled cutover; the solution must preserve existing account isolation. A customer application requires low-latency keyed row updates with enforced transactional constraints that fit a supported hybrid-table design. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which explanation best justifies this recommendation?
- Row access policies constrain which rows are returned at query time; a column masking policy does not filter rows.
- Hybrid tables support row-oriented transactional use cases that differ from purely analytical columnar standard tables.
- Permanent tables support Time Travel and a Fail-safe period; choosing transient storage would change recoverability guarantees.
- Transient tables avoid the Fail-safe period and are appropriate where the pipeline can safely recreate the data.
Explanation
Correct Answer
B:In this production migration readiness context, Hybrid tables support row-oriented transactional use cases that differ from purely analytical columnar standard tables. The recommendation is linked to observable evidence for this exact case: Keyed updates and constraint checks behave as designed under representative concurrent application traffic. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:It does not justify the specific hybrid table transactional access action or its validation path: Keyed updates and constraint checks behave as designed under representative concurrent application traffic.
C:That option concerns permanent table recovery choice, not the requirement in this case. It does not justify the specific hybrid table transactional access action or its validation path: Keyed updates and constraint checks behave as designed under representative concurrent application traffic.
D:That option concerns transient staging table lifecycle, not the requirement in this case. It does not justify the specific hybrid table transactional access action or its validation path: Keyed updates and constraint checks behave as designed under representative concurrent application traffic.
Question 863
[Peak traffic performance incident] The team proposes: Create a scoped SQL user-defined function and grant the appropriate usage privileges to consuming roles. Its actual requirement is: A BI engineer is investigating the exact bottleneck observed under production-like load. Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which observation most directly validates success?
- A regression test with tied timestamps always chooses the same event according to the documented tie-breaker.
- TASK_HISTORY contains scheduled executions and the target cleanup effects match the hourly schedule.
- SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
- A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
Explanation
Correct Answer
C:A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row. This is an outcome test of the specified sql udf reusable expression action under the peak traffic performance incident conditions.
Incorrect Answers
A:A regression test with tied timestamps always chooses the same event according to the documented tie-breaker. This is an outcome for a different requirement: A churn dataset has multiple events with identical timestamps per customer, and selecting the latest without a tie-breaker gives inconsistent winners. It cannot by itself validate the proposed sql udf reusable expression action; the appropriate test is: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
B:In this peak traffic performance incident context, TASK_HISTORY contains scheduled executions and the target cleanup effects match the hourly schedule. This is an outcome for a different requirement: A cleanup procedure should execute every hour without an operator opening Snowsight, and its success history must be auditable. It cannot by itself validate the proposed sql udf reusable expression action; the appropriate test is: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
D:In this peak traffic performance incident context, A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data. This is an outcome for a different requirement: A 70-second analytics query has become slow after a schema change, and the team wants to find whether scanning, joining, or spilling dominates its runtime. It cannot by itself validate the proposed sql udf reusable expression action; the appropriate test is: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
Question 864
[Security control assessment] A quarterly access-control audit requires an evidence-backed answer. A sandbox warehouse is used for unpredictable experiments and a manager needs credit alerts and a suspension threshold for that warehouse. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which specific design mistake would most directly defeat the intended result?
- Add idle multi-cluster capacity without investigating the one query's spill or plan shape.
- Conclude that a 4X larger warehouse must be 4X faster for every small query without collecting any timing data.
- Leave the warehouse running indefinitely to preserve tables, even when no compute-intensive activity remains.
- Assume a warehouse resource monitor automatically prevents every possible Snowflake storage and serverless service charge.
Explanation
Correct Answer
D:The discriminating technical reason is: Resource monitors help constrain specified warehouse credit use; they are not a universal quota for all storage and serverless charges.
Incorrect Answers
A:That is a mistake for a different warehouse scale-up for complex query task, where A warehouse consistently spills intermediate data on a single memory-intensive analytical join while the queue length is close to zero. Under security control assessment conditions, Add idle multi-cluster capacity without investigating the one query's spill or plan shape.
B:That is a mistake for a different warehouse sizing performance experiment task, where A complicated analytical transform has predictable memory pressure, but management wants evidence before raising per-second warehouse consumption. Under security control assessment conditions, Conclude that a 4X larger warehouse must be 4X faster for every small query without collecting any timing data.
C:That is a mistake for a different warehouse auto-suspend economics task, where A development warehouse completes a short test query and then remains idle for hours because the team left its suspend behavior unmanaged. Under security control assessment conditions, Leave the warehouse running indefinitely to preserve tables, even when no compute-intensive activity remains.
Question 865
[Optimization regression test] A reviewer recommends: Create a scoped SQL user-defined function and grant the appropriate usage privileges to consuming roles. The requirement remains: A team is validating a targeted tuning plan against documented workloads and costs. Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. The conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row.
- Stored procedures are suited to multi-statement procedural logic; ordinary scalar UDFs have different side-effect and execution constraints.
- QUALIFY filters the results of window functions in Snowflake and is suited to post-window row selection.
- Tasks schedule SQL or procedure execution, which a bare stream or file stage does not perform independently.
Explanation
Correct Answer
A:In this optimization regression test context, A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row. The recommendation is linked to observable evidence for this exact case: SELECT using the function gives the same result as the underlying expression for a boundary-value test set. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
B:It does not justify the specific sql udf reusable expression action or its validation path: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
C:That option concerns qualify after window expression, not the requirement in this case. It does not justify the specific sql udf reusable expression action or its validation path: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
D:That option concerns task scheduled orchestration, not the requirement in this case. It does not justify the specific sql udf reusable expression action or its validation path: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
Question 866
[Provider and consumer workshop] A data collaboration team is defining the permitted provider and consumer relationship. A provider and a consumer operate in different supported regions and want catalog-based access without assuming a same-region direct share works unchanged. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Create and govern a suitable Snowflake Marketplace listing with the intended visibility, terms, and fulfillment model.
- Choose a cross-region or cross-cloud listing and fulfillment configuration supported by their accounts and Snowflake edition.
- Plan explicit cross-region collaboration or replication and review cloud and region boundaries before selecting the transfer path.
- Create a secure direct share for eligible objects and grant it to the approved consumer account under supported region and cloud conditions.
Explanation
Correct Answer
B:Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access. In this provider and consumer workshop setting, the intended validation is: The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
Incorrect Answers
A:That action is relevant when A publisher needs customers to discover and request an approved dataset through a catalog rather than exchange account identifiers manually. Create and govern a suitable Snowflake Marketplace listing with the intended visibility, terms, and fulfillment model.
C:That action is relevant when A company operates accounts on two clouds and wants to access a dataset from the second account without assuming storage instantly appears in both regions. Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access.
D:That action is relevant when A Snowflake data provider wants a business partner who already has a Snowflake account to query governed datasets without copying daily CSV exports. Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access.
Question 867
[Query performance review] A performance engineer is benchmarking representative queries before accepting changes. Thirty concurrent dashboards have low individual runtimes but high wait times, and the account can use multi-cluster warehouses. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Test a larger warehouse size or a more efficient query plan to give the expensive join adequate resources.
- Use a suitably configured multi-cluster warehouse and auto-scale policy where supported to address concurrency.
- Set appropriate min/max cluster limits and an autoscaling policy rather than only increasing single-cluster size.
- Evaluate Search Optimization Service for the supported predicate pattern and account edition after reviewing query profile.
Explanation
Correct Answer
C:Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster. In this query performance review setting, the intended validation is: The cluster count rises during bursts and query queue duration falls in matched concurrency tests.
Incorrect Answers
A:That action is relevant when A warehouse consistently spills intermediate data on a single memory-intensive analytical join while the queue length is close to zero. Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster.
B:It is not the primary action for the stated multi-cluster scale out versus scale up need. That action is relevant when During a product launch dozens of short BI queries queue even though each individual query already finishes quickly once it starts.
D:That action is relevant when An API workload repeatedly probes a huge fact table for single customer identifiers and needs fast selective lookup response. Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster.
Question 868
[Pipeline incident investigation] An on-call ingestion engineer needs a targeted fix that preserves source-to-target reconciliation. A platform receives Kafka events and wants to land them in Snowflake with a connector rather than write a bespoke polling script. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Use the compatible Snowflake Spark connector with the correct authentication, pushdown, and connector settings.
- Install and configure the appropriate Snowflake JDBC or ODBC driver, connection parameters, and least-privilege role.
- Evaluate the current supported Snowpipe Streaming SDK or high-performance architecture with an appropriate channel design.
- Use Snowflake's supported Kafka connector or streaming integration with configured topic mappings and authentication.
Explanation
Correct Answer
D:The connector bridges Kafka message streams and Snowflake ingestion; the mapping and delivery properties are explicit deployment settings. In this pipeline incident investigation setting, the intended validation is: Topic offsets and target ingestion counts reconcile after a test publish and planned connector restart.
Incorrect Answers
A:That action is relevant when A Spark transformation job must exchange large Snowflake datasets efficiently with a supported connector rather than retrieve millions of rows through a GUI export. The connector bridges Kafka message streams and Snowflake ingestion; the mapping and delivery properties are explicit deployment settings.
B:That action is relevant when A third-party reporting product accepts standard database connectivity drivers and needs Snowflake queries under a service identity. Install and configure the appropriate Snowflake JDBC or ODBC driver, connection parameters, and least-privilege role.
C:That action is relevant when An IoT product emits row events continuously and needs lower-latency ingestion than file-batched loading provides. It is not the primary action for the stated kafka connector topic ingestion need.
Question 869
[Production cutover rehearsal] The team proposes: Use Snowflake's load metadata/history and carefully controlled COPY retry behavior to identify already ingested files. Its actual requirement is: A data integration group runs a controlled replay with representative files or events. A nightly pipeline may retry an earlier bulk load after a network timeout and must avoid inadvertently duplicating files that were already committed. The conclusion must be justified with observable verification rather than an undocumented assumption. Which observation most directly validates success?
- Load history and target counts confirm expected files were not ingested twice after simulated network retry.
- A staged preview and validation load reconcile embedded delimiter strings and expected column counts across sample rows.
- LIST on the stage shows the expected files and COPY INTO loads exactly the reconciled input rows.
- Validation output identifies the same malformed records that can be corrected before the final committed load.
Explanation
Correct Answer
A:Load history and target counts confirm expected files were not ingested twice after simulated network retry. This is an outcome test of the specified load idempotence and history action under the production cutover rehearsal conditions. Bulk COPY tracks loaded file metadata within documented limits; rerun policies and file identity matter for duplicate prevention.
Incorrect Answers
B:In this production cutover rehearsal context, A staged preview and validation load reconcile embedded delimiter strings and expected column counts across sample rows. This is an outcome for a different requirement: One source feed uses pipe-delimited UTF-8 files with a header and quoted embedded delimiters, and every daily load must interpret them consistently. It cannot by itself validate the proposed load idempotence and history action; the appropriate test is: Load history and target counts confirm expected files were not ingested twice after simulated network retry.
C:LIST on the stage shows the expected files and COPY INTO loads exactly the reconciled input rows. This is an outcome for a different requirement: A team needs a stable staging location where several engineers can PUT source files and run repeatable bulk loads for the same project. It cannot by itself validate the proposed load idempotence and history action; the appropriate test is: Load history and target counts confirm expected files were not ingested twice after simulated network retry.
D:In this production cutover rehearsal context, Validation output identifies the same malformed records that can be corrected before the final committed load. This is an outcome for a different requirement: A 20GB batch contains uncertain date strings and the operator needs to discover load errors without committing partially incorrect target records. It cannot by itself validate the proposed load idempotence and history action; the appropriate test is: Load history and target counts confirm expected files were not ingested twice after simulated network retry.
Question 870
[Post-rollout service review] A reviewer recommends: Inspect partition pruning and clustering characteristics before choosing a supported data-organizing strategy. The requirement remains: An operations group is reassessing the installed data platform after a representative workload trial. A fact table stores billions of events, yet a selective date predicate often scans nearly every partition because records are scattered across the table. The conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- External tables expose externally stored data through Snowflake-managed metadata rather than copying all rows into a normal native table.
- Snowflake automatically stores micro-partition metadata; pruning is effective when filter values narrow which partitions can contain rows.
- Iceberg tables provide an open-format table metadata layer; catalog integration and write permissions depend on their chosen management model.
- Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
Explanation
Correct Answer
B:In this post-rollout service review context, Snowflake automatically stores micro-partition metadata; pruning is effective when filter values narrow which partitions can contain rows. The recommendation is linked to observable evidence for this exact case: Query Profile shows fewer partitions scanned for the date-filtered workload after the chosen organization change. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns external table over data lake, not the requirement in this case. It does not justify the specific micro-partition pruning metadata action or its validation path: Query Profile shows fewer partitions scanned for the date-filtered workload after the chosen organization change.
C:That option concerns iceberg interoperability decision, not the requirement in this case. It does not justify the specific micro-partition pruning metadata action or its validation path: Query Profile shows fewer partitions scanned for the date-filtered workload after the chosen organization change.
D:Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.