SnowPro Core COF-C03 Practice Test 36
Question 350
An architecture reviewer proposes the following solution: Use MERGE with defined match keys and explicit update and insert branches. The business and technical requirement is: A load contains changed customer records and the target dimension should update matches while inserting new identifiers. Which explanation most accurately justifies this choice?
- MERGE combines conditional matched and unmatched DML paths in one operation.
- Incorrect join predicates can multiply intermediate rows, causing poor performance regardless of warehouse size.
- Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources.
- Result caching can return compatible prior query results without reexecuting a full warehouse scan.
Explanation
Correct Answer
A:MERGE combines conditional matched and unmatched DML paths in one operation. This mechanism explains why the proposed action fits the scenario: Use MERGE with defined match keys and explicit update and insert branches. Confirm the design against supported platform behavior.
Incorrect Answers
B:This mechanism explains why the proposed action fits the scenario: Inspect join cardinality, join keys and query plan before simply adding compute. That explains the role of “Incorrect join predicates can multiply intermediate rows, causing poor performance regardless of warehouse size”, but the present requirement calls for “MERGE combines conditional matched and unmatched DML paths in one operation”: This mechanism explains why the proposed action fits the scenario: Use MERGE with defined match keys and explicit update and insert branches.
C:This mechanism explains why the proposed action fits the scenario: Scale out with an appropriate multi-cluster warehouse rather than only increasing one cluster's size. That explains the role of “Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources”, but the present requirement calls for “MERGE combines conditional matched and unmatched DML paths in one operation”: This mechanism explains why the proposed action fits the scenario: Use MERGE with defined match keys and explicit update and insert branches.
D:This mechanism explains why the proposed action fits the scenario: Inspect Snowflake persisted query result reuse and conditions that invalidate it. That explains the role of “Result caching can return compatible prior query results without reexecuting a full warehouse scan”, but the present requirement calls for “MERGE combines conditional matched and unmatched DML paths in one operation”: This mechanism explains why the proposed action fits the scenario: Use MERGE with defined match keys and explicit update and insert branches.
Question 351
An architecture reviewer proposes the following solution: Use a supported VS Code Snowflake extension or integration with repository version control. The business and technical requirement is: A data engineering team wants to collaborate on SQL and Python scripts using an editor rather than browser-only development. Which explanation most accurately justifies this choice?
- IDE integration supports developer workflows while Snowflake connection settings still govern execution privileges.
- Scaling out clusters addresses concurrency queues rather than simply accelerating one expensive statement.
- Snowflake provisions and maintains platform infrastructure and service upgrades, leaving customers responsible for data and access design.
- VARIANT holds flexible structured values without forcing one rigid input schema for every record.
Explanation
Correct Answer
A:IDE integration supports developer workflows while Snowflake connection settings still govern execution privileges. This mechanism explains why the proposed action fits the scenario: Use a supported VS Code Snowflake extension or integration with repository version control. Confirm the design against supported platform behavior.
Incorrect Answers
B:This mechanism explains why the proposed action fits the scenario: Use an appropriately configured multi-cluster warehouse to increase concurrent capacity. That explains the role of “Scaling out clusters addresses concurrency queues rather than simply accelerating one expensive statement”, but the present requirement calls for “IDE integration supports developer workflows while Snowflake connection settings still govern execution privileges”: This mechanism explains why the proposed action fits the scenario: Use a supported VS Code Snowflake extension or integration with repository version control.
C:This mechanism explains why the proposed action fits the scenario: Use the fully managed Snowflake service rather than installing a self-hosted database cluster. That explains the role of “Snowflake provisions and maintains platform infrastructure and service upgrades, leaving customers responsible for data and access design”, but the present requirement calls for “IDE integration supports developer workflows while Snowflake connection settings still govern execution privileges”: This mechanism explains why the proposed action fits the scenario: Use a supported VS Code Snowflake extension or integration with repository version control.
D:This mechanism explains why the proposed action fits the scenario: Store semi-structured payloads in VARIANT and extract typed fields in downstream queries. That explains the role of “VARIANT holds flexible structured values without forcing one rigid input schema for every record”, but the present requirement calls for “IDE integration supports developer workflows while Snowflake connection settings still govern execution privileges”: This mechanism explains why the proposed action fits the scenario: Use a supported VS Code Snowflake extension or integration with repository version control.
Question 352
An architecture reviewer proposes the following solution: Combine role-based access with monitored egress controls and approved data governance policies. The business and technical requirement is: A company must prevent unauthorized data exports from a secure analysis environment through copied query results. Which explanation most accurately justifies this choice?
- Network policies constrain permitted source addresses independently from SQL object grants.
- Protecting data requires governing who can query and transfer it, not relying only on warehouse compute isolation.
- Account activity history connects query operations to principals and execution context for investigation.
- Idle warehouse compute incurs credit costs until suspended; configuration can prevent recurring waste.
Explanation
Correct Answer
B:Protecting data requires governing who can query and transfer it, not relying only on warehouse compute isolation. This mechanism explains why the proposed action fits the scenario: Combine role-based access with monitored egress controls and approved data governance policies. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Apply a tested Snowflake network policy with narrowly scoped allowed IP ranges. That explains the role of “Network policies constrain permitted source addresses independently from SQL object grants”, but the present requirement calls for “Protecting data requires governing who can query and transfer it, not relying only on warehouse compute isolation”: This mechanism explains why the proposed action fits the scenario: Combine role-based access with monitored egress controls and approved data governance policies.
C:This mechanism explains why the proposed action fits the scenario: Inspect Snowflake access and query history using properly privileged monitoring views. That explains the role of “Account activity history connects query operations to principals and execution context for investigation”, but the present requirement calls for “Protecting data requires governing who can query and transfer it, not relying only on warehouse compute isolation”: This mechanism explains why the proposed action fits the scenario: Combine role-based access with monitored egress controls and approved data governance policies.
D:This mechanism explains why the proposed action fits the scenario: Use warehouse auto-suspend with monitored billing rather than depending on someone to stop it manually. That explains the role of “Idle warehouse compute incurs credit costs until suspended; configuration can prevent recurring waste”, but the present requirement calls for “Protecting data requires governing who can query and transfer it, not relying only on warehouse compute isolation”: This mechanism explains why the proposed action fits the scenario: Combine role-based access with monitored egress controls and approved data governance policies.
Question 353
An architecture reviewer proposes the following solution: Check pipe status, stage path and cloud notification integration for missed messages. The business and technical requirement is: A serverless ingestion process has unexpectedly high latency because event notifications are not reaching Snowflake. Which explanation most accurately justifies this choice?
- Name-based matching can align supported file columns without depending on file position alone.
- File arrival alone does not trigger auto-ingest unless the configured event route delivers notifications to the pipe.
- Bulk COPY INTO uses a specified warehouse to load staged files into Snowflake tables in batches.
- Load history provides observable file-level execution and error outcomes for ingestion investigations.
Explanation
Correct Answer
B:File arrival alone does not trigger auto-ingest unless the configured event route delivers notifications to the pipe. This mechanism explains why the proposed action fits the scenario: Check pipe status, stage path and cloud notification integration for missed messages. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Evaluate MATCH_BY_COLUMN_NAME options consistent with the source and target schema. That explains the role of “Name-based matching can align supported file columns without depending on file position alone”, but the present requirement calls for “File arrival alone does not trigger auto-ingest unless the configured event route delivers notifications to the pipe”: This mechanism explains why the proposed action fits the scenario: Check pipe status, stage path and cloud notification integration for missed messages.
C:This mechanism explains why the proposed action fits the scenario: Use COPY INTO a target table from a configured external stage and CSV file format. That explains the role of “Bulk COPY INTO uses a specified warehouse to load staged files into Snowflake tables in batches”, but the present requirement calls for “File arrival alone does not trigger auto-ingest unless the configured event route delivers notifications to the pipe”: This mechanism explains why the proposed action fits the scenario: Check pipe status, stage path and cloud notification integration for missed messages.
D:This mechanism explains why the proposed action fits the scenario: Inspect COPY_HISTORY or supported load history and detailed COPY results. That explains the role of “Load history provides observable file-level execution and error outcomes for ingestion investigations”, but the present requirement calls for “File arrival alone does not trigger auto-ingest unless the configured event route delivers notifications to the pipe”: This mechanism explains why the proposed action fits the scenario: Check pipe status, stage path and cloud notification integration for missed messages.
Question 354
An architecture reviewer proposes the following solution: Use RANK or DENSE_RANK with a partitioned window expression appropriate to tie behavior. The business and technical requirement is: Analysts want to rank products by sales within each region without collapsing detail rows. Which explanation most accurately justifies this choice?
- Materialized views store selected precomputed results that can reduce repeated execution work.
- Typed extraction converts flexible input data into stable columns for reporting and validation.
- Directory tables maintain a queryable file inventory instead of scanning raw object storage listings manually.
- Window functions compute rankings across a partition while retaining the underlying row granularity.
Explanation
Correct Answer
D:Window functions compute rankings across a partition while retaining the underlying row granularity. This mechanism explains why the proposed action fits the scenario: Use RANK or DENSE_RANK with a partitioned window expression appropriate to tie behavior. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Evaluate a materialized view for the stable aggregation with monitored storage and maintenance costs. That explains the role of “Materialized views store selected precomputed results that can reduce repeated execution work”, but the present requirement calls for “Window functions compute rankings across a partition while retaining the underlying row granularity”: This mechanism explains why the proposed action fits the scenario: Use RANK or DENSE_RANK with a partitioned window expression appropriate to tie behavior.
B:This mechanism explains why the proposed action fits the scenario: Extract VARIANT paths and cast them into explicit target types in SQL. That explains the role of “Typed extraction converts flexible input data into stable columns for reporting and validation”, but the present requirement calls for “Window functions compute rankings across a partition while retaining the underlying row granularity”: This mechanism explains why the proposed action fits the scenario: Use RANK or DENSE_RANK with a partitioned window expression appropriate to tie behavior.
C:This mechanism explains why the proposed action fits the scenario: Enable or refresh supported stage directory tables and inspect their file metadata. That explains the role of “Directory tables maintain a queryable file inventory instead of scanning raw object storage listings manually”, but the present requirement calls for “Window functions compute rankings across a partition while retaining the underlying row granularity”: This mechanism explains why the proposed action fits the scenario: Use RANK or DENSE_RANK with a partitioned window expression appropriate to tie behavior.
Question 355
An architecture reviewer proposes the following solution: Inspect session context through SQL functions and connection metadata. The business and technical requirement is: An enterprise must audit which session role and warehouse a desktop tool is using for Snowflake queries. Which explanation most accurately justifies this choice?
- Materialized views persist selected computed results but impose storage and maintenance costs.
- Connection and authentication failures precede warehouse execution and are not solved by adding compute capacity.
- Snowflake executes SQL and supported application code close to governed data through its managed compute and developer interfaces.
- Client preferences can set active role or warehouse, and implicit defaults may surprise administrators.
Explanation
Correct Answer
D:Client preferences can set active role or warehouse, and implicit defaults may surprise administrators. This mechanism explains why the proposed action fits the scenario: Inspect session context through SQL functions and connection metadata. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Evaluate a materialized view for the stable expensive aggregation pattern and quantify upkeep cost. That explains the role of “Materialized views persist selected computed results but impose storage and maintenance costs”, but the present requirement calls for “Client preferences can set active role or warehouse, and implicit defaults may surprise administrators”: This mechanism explains why the proposed action fits the scenario: Inspect session context through SQL functions and connection metadata.
B:This mechanism explains why the proposed action fits the scenario: Check the cloud services authentication and network access path before tuning warehouse size. That explains the role of “Connection and authentication failures precede warehouse execution and are not solved by adding compute capacity”, but the present requirement calls for “Client preferences can set active role or warehouse, and implicit defaults may surprise administrators”: This mechanism explains why the proposed action fits the scenario: Inspect session context through SQL functions and connection metadata.
C:This mechanism explains why the proposed action fits the scenario: Combine Snowflake SQL with supported Snowpark and native application development capabilities. That explains the role of “Snowflake executes SQL and supported application code close to governed data through its managed compute and developer interfaces”, but the present requirement calls for “Client preferences can set active role or warehouse, and implicit defaults may surprise administrators”: This mechanism explains why the proposed action fits the scenario: Inspect session context through SQL functions and connection metadata.
Question 356
An architecture reviewer proposes the following solution: Attach Snowflake governance policies at the data object instead of implementing separate app-only filters. The business and technical requirement is: A dataset owner wants policy enforcement when several analytics apps query the same customer table. Which explanation most accurately justifies this choice?
- Retained historical data can contribute to storage billing even when active row counts are stable.
- Data-level controls travel with queries across clients and minimize inconsistent authorization behavior.
- Account activity history connects query operations to principals and execution context for investigation.
- Accessing a table generally requires appropriate usage through the containing database and schema in addition to table rights.
Explanation
Correct Answer
B:Data-level controls travel with queries across clients and minimize inconsistent authorization behavior. This mechanism explains why the proposed action fits the scenario: Attach Snowflake governance policies at the data object instead of implementing separate app-only filters. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Review database storage usage and Time Travel or Fail-safe storage footprint where applicable. That explains the role of “Retained historical data can contribute to storage billing even when active row counts are stable”, but the present requirement calls for “Data-level controls travel with queries across clients and minimize inconsistent authorization behavior”: This mechanism explains why the proposed action fits the scenario: Attach Snowflake governance policies at the data object instead of implementing separate app-only filters.
C:This mechanism explains why the proposed action fits the scenario: Inspect Snowflake access and query history using properly privileged monitoring views. That explains the role of “Account activity history connects query operations to principals and execution context for investigation”, but the present requirement calls for “Data-level controls travel with queries across clients and minimize inconsistent authorization behavior”: This mechanism explains why the proposed action fits the scenario: Attach Snowflake governance policies at the data object instead of implementing separate app-only filters.
D:This mechanism explains why the proposed action fits the scenario: Grant the necessary ancestor object USAGE privileges to the intended role. That explains the role of “Accessing a table generally requires appropriate usage through the containing database and schema in addition to table rights”, but the present requirement calls for “Data-level controls travel with queries across clients and minimize inconsistent authorization behavior”: This mechanism explains why the proposed action fits the scenario: Attach Snowflake governance policies at the data object instead of implementing separate app-only filters.
Question 357
An architecture reviewer proposes the following solution: Evaluate supported dynamic table INCREMENTAL refresh or streams-and-tasks for the transformation. The business and technical requirement is: An incremental transformation should avoid full rescans when only a few source rows change. Which explanation most accurately justifies this choice?
- Bulk COPY INTO uses a specified warehouse to load staged files into Snowflake tables in batches.
- COPY loading error behavior is configurable; silent skipping without review can hide data loss.
- Change-based processing limits recomputation when the SQL pattern and source changes support incremental work.
- JSON loading should preserve nested structure instead of assuming fixed CSV column positions.
Explanation
Correct Answer
C:Change-based processing limits recomputation when the SQL pattern and source changes support incremental work. This mechanism explains why the proposed action fits the scenario: Evaluate supported dynamic table INCREMENTAL refresh or streams-and-tasks for the transformation. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Use COPY INTO a target table from a configured external stage and CSV file format. That explains the role of “Bulk COPY INTO uses a specified warehouse to load staged files into Snowflake tables in batches”, but the present requirement calls for “Change-based processing limits recomputation when the SQL pattern and source changes support incremental work”: This mechanism explains why the proposed action fits the scenario: Evaluate supported dynamic table INCREMENTAL refresh or streams-and-tasks for the transformation.
B:This mechanism explains why the proposed action fits the scenario: Choose an appropriate COPY INTO ON_ERROR setting and inspect load results. That explains the role of “COPY loading error behavior is configurable; silent skipping without review can hide data loss”, but the present requirement calls for “Change-based processing limits recomputation when the SQL pattern and source changes support incremental work”: This mechanism explains why the proposed action fits the scenario: Evaluate supported dynamic table INCREMENTAL refresh or streams-and-tasks for the transformation.
D:This mechanism explains why the proposed action fits the scenario: Define a JSON file format and load records into a VARIANT-capable table layout. That explains the role of “JSON loading should preserve nested structure instead of assuming fixed CSV column positions”, but the present requirement calls for “Change-based processing limits recomputation when the SQL pattern and source changes support incremental work”: This mechanism explains why the proposed action fits the scenario: Evaluate supported dynamic table INCREMENTAL refresh or streams-and-tasks for the transformation.
Question 358
An architecture reviewer proposes the following solution: Use LAG in a partitioned, ordered window expression. The business and technical requirement is: A report needs last month's revenue next to each month to compute changes across time. Which explanation most accurately justifies this choice?
- Scaling out adds active clusters as workload concurrency rises to reduce queued requests.
- QAS can offload parts of supported long-running queries to shared compute resources.
- LAG accesses preceding rows in a defined sequence without an expensive self-join for each offset.
- Document AI can extract structured information from unstructured documents but needs confidence and quality checks.
Explanation
Correct Answer
C:LAG accesses preceding rows in a defined sequence without an expensive self-join for each offset. This mechanism explains why the proposed action fits the scenario: Use LAG in a partitioned, ordered window expression. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Configure multi-cluster auto-scale within supported edition limits. That explains the role of “Scaling out adds active clusters as workload concurrency rises to reduce queued requests”, but the present requirement calls for “LAG accesses preceding rows in a defined sequence without an expensive self-join for each offset”: This mechanism explains why the proposed action fits the scenario: Use LAG in a partitioned, ordered window expression.
B:This mechanism explains why the proposed action fits the scenario: Consider Query Acceleration Service for eligible selective large scans and aggregations. That explains the role of “QAS can offload parts of supported long-running queries to shared compute resources”, but the present requirement calls for “LAG accesses preceding rows in a defined sequence without an expensive self-join for each offset”: This mechanism explains why the proposed action fits the scenario: Use LAG in a partitioned, ordered window expression.
D:This mechanism explains why the proposed action fits the scenario: Evaluate supported Snowflake document processing AI features with human validation gates. That explains the role of “Document AI can extract structured information from unstructured documents but needs confidence and quality checks”, but the present requirement calls for “LAG accesses preceding rows in a defined sequence without an expensive self-join for each offset”: This mechanism explains why the proposed action fits the scenario: Use LAG in a partitioned, ordered window expression.
Question 359
An architecture reviewer proposes the following solution: Use Snowsight for manual exploration and a Snowflake CLI or supported connector for repeatable jobs. The business and technical requirement is: A team wants to separate interactive ad-hoc exploration from automated daily SQL deployment jobs. Which explanation most accurately justifies this choice?
- VPS is a specialized Snowflake deployment model for organizations with heightened isolation requirements.
- Streamlit supports rapid Python application user interfaces with Snowflake-backed data and access controls.
- Larger warehouse sizes add processing and memory resources that can reduce spill for a single heavy query.
- Interactive and automated interfaces suit different workflows but share Snowflake's security and object model.
Explanation
Correct Answer
D:Interactive and automated interfaces suit different workflows but share Snowflake's security and object model. This mechanism explains why the proposed action fits the scenario: Use Snowsight for manual exploration and a Snowflake CLI or supported connector for repeatable jobs. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Evaluate Virtual Private Snowflake and its dedicated infrastructure requirements. That explains the role of “VPS is a specialized Snowflake deployment model for organizations with heightened isolation requirements”, but the present requirement calls for “Interactive and automated interfaces suit different workflows but share Snowflake's security and object model”: This mechanism explains why the proposed action fits the scenario: Use Snowsight for manual exploration and a Snowflake CLI or supported connector for repeatable jobs.
B:This mechanism explains why the proposed action fits the scenario: Use Streamlit in Snowflake for the approved internal application pattern. That explains the role of “Streamlit supports rapid Python application user interfaces with Snowflake-backed data and access controls”, but the present requirement calls for “Interactive and automated interfaces suit different workflows but share Snowflake's security and object model”: This mechanism explains why the proposed action fits the scenario: Use Snowsight for manual exploration and a Snowflake CLI or supported connector for repeatable jobs.
C:This mechanism explains why the proposed action fits the scenario: Evaluate scaling the warehouse up after confirming memory spill in Query Profile. That explains the role of “Larger warehouse sizes add processing and memory resources that can reduce spill for a single heavy query”, but the present requirement calls for “Interactive and automated interfaces suit different workflows but share Snowflake's security and object model”: This mechanism explains why the proposed action fits the scenario: Use Snowsight for manual exploration and a Snowflake CLI or supported connector for repeatable jobs.
Question 360
An architecture reviewer proposes the following solution: Restrict grants and implement an approved secure projection with scoped consumer roles. The business and technical requirement is: An administrator is tempted to publish a view exposing raw sensitive fields to the PUBLIC role for convenience. Which explanation most accurately justifies this choice?
- Query metadata supports operational reporting of status, user, role and execution context.
- Clear compute ownership and query identification improve chargeback compared with account totals alone.
- Broad public grants undermine least privilege and a view must be explicitly governed for sensitive data.
- SQL execution requires an accessible running or resumable warehouse, separate from table read rights.
Explanation
Correct Answer
C:Broad public grants undermine least privilege and a view must be explicitly governed for sensitive data. This mechanism explains why the proposed action fits the scenario: Restrict grants and implement an approved secure projection with scoped consumer roles. Confirm the design against supported platform behavior.
Incorrect Answers
A:This mechanism explains why the proposed action fits the scenario: Use authorized QUERY_HISTORY and related account monitoring views. That explains the role of “Query metadata supports operational reporting of status, user, role and execution context”, but the present requirement calls for “Broad public grants undermine least privilege and a view must be explicitly governed for sensitive data”: This mechanism explains why the proposed action fits the scenario: Restrict grants and implement an approved secure projection with scoped consumer roles.
B:This mechanism explains why the proposed action fits the scenario: Use independent warehouses or supported attribution metadata and query tags for the workloads. That explains the role of “Clear compute ownership and query identification improve chargeback compared with account totals alone”, but the present requirement calls for “Broad public grants undermine least privilege and a view must be explicitly governed for sensitive data”: This mechanism explains why the proposed action fits the scenario: Restrict grants and implement an approved secure projection with scoped consumer roles.
D:This mechanism explains why the proposed action fits the scenario: Assign an available warehouse with USAGE to the connecting role and update the client context. That explains the role of “SQL execution requires an accessible running or resumable warehouse, separate from table read rights”, but the present requirement calls for “Broad public grants undermine least privilege and a view must be explicitly governed for sensitive data”: This mechanism explains why the proposed action fits the scenario: Restrict grants and implement an approved secure projection with scoped consumer roles.