Snowflake SnowPro Core COF-C03 Practice Test 44
Question 430
[Ingestion design acceptance] A data engineering team is checking the design before promoting a pipeline. An analyst must stage a small support file used only to load one existing target table, with no need for a shared named-stage contract. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Configure an external table and its stage/integration and maintain the external file metadata as required.
- Use the table's built-in stage where suitable and load into the target using supported commands.
- Use COPY INTO a stage or external location from the approved SELECT with an explicit supported file format.
- Enable or refresh an appropriate directory table on a supported stage and query its file metadata.
Explanation
Correct Answer
B:A table stage gives a convenient table-associated internal landing location without creating a separate named stage. In this ingestion design acceptance setting, the intended validation is: A staged file is visible through the table-stage reference and a controlled COPY loads the intended table.
Incorrect Answers
A:That action is relevant when A data scientist needs governed SQL reads over files remaining in external object storage, without an ordinary Snowflake-managed load step. It is not the primary action for the stated table stage file landing need.
C:That action is relevant when A downstream finance application requires an export of curated query results to an approved cloud storage path for a partner batch job. It is not the primary action for the stated table stage file landing need.
D:That action is relevant when A document processing workflow keeps PDFs on a Snowflake stage and needs a queryable inventory of staged files before extraction. It is not the primary action for the stated table stage file landing need.
Question 431
[Query performance review] A reviewer recommends: Benchmark representative SQL on appropriately sized warehouses while keeping input data and workload conditions comparable. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. A complicated analytical transform has predictable memory pressure, but management wants evidence before raising per-second warehouse consumption. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Auto-suspend releases idle warehouse compute while auto-resume permits later queries; frequency should reflect workload and billing minimums.
- Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster.
- Warehouse resizing changes resources per cluster; benchmarking reveals whether one heavyweight query benefits enough to offset credits.
- Resource monitors help constrain specified warehouse credit use; they are not a universal quota for all storage and serverless charges.
Explanation
Correct Answer
C:In this query performance review context, Warehouse resizing changes resources per cluster; benchmarking reveals whether one heavyweight query benefits enough to offset credits. The recommendation is linked to observable evidence for this exact case: Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns warehouse auto-suspend economics, not the requirement in this case. It does not justify the specific warehouse sizing performance experiment action or its validation path: Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size.
B:It does not justify the specific warehouse sizing performance experiment action or its validation path: Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size.
D:That option concerns resource monitor warehouse budgeting, not the requirement in this case. Resource monitors help constrain specified warehouse credit use; they are not a universal quota for all storage and serverless charges.
Question 432
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. A churn dataset has multiple events with identical timestamps per customer, and selecting the latest without a tie-breaker gives inconsistent winners. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Inspect persisted query result cache eligibility, deterministic SQL, underlying data changes, and result reuse settings.
- Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY.
- Create a scoped SQL user-defined function and grant the appropriate usage privileges to consuming roles.
- Specify a deterministic secondary ordering key in ROW_NUMBER or another suitable window ranking expression.
Explanation
Correct Answer
D:Window ordering must resolve ties to make latest-record selection reproducible across executions. In this peak traffic performance incident setting, the intended validation is: A regression test with tied timestamps always chooses the same event according to the documented tie-breaker.
Incorrect Answers
A:That action is relevant when A team reruns an unchanged SELECT but sometimes receives cached results and sometimes spends compute again after underlying data changes. It is not the primary action for the stated window-function deterministic ranking need.
B:That action is relevant when A table contains several rows per customer and an analyst wants exactly the latest row for each customer after computing ROW_NUMBER. It is not the primary action for the stated window-function deterministic ranking need.
C:That action is relevant when Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. It is not the primary action for the stated window-function deterministic ranking need.
Question 433
[Security control assessment] A quarterly access-control audit requires an evidence-backed answer. A customer table contains national identifiers; privileged reviewers may see full values but ordinary analysts should receive redacted values. 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?
- Create a separate unrestricted duplicate table for analysts and rely on a comment saying identifiers are confidential.
- Grant ACCOUNTADMIN to every analyst to avoid investigating the missing read privilege.
- Assume a dynamic table is updated instantly on every source write regardless of its refresh behavior and target lag.
- Assume labeling a column as sensitive necessarily blocks every SELECT without a masking or access policy.
Explanation
Correct Answer
A:The supportable direction is: Attach a dynamic data masking policy to the sensitive column with conditions appropriate for the approved role model.
Incorrect Answers
B:Under security control assessment conditions, Grant ACCOUNTADMIN to every analyst to avoid investigating the missing read privilege.
C:That is a mistake for a different dynamic table declarative refresh task, where A reporting team wants a transformation query to maintain refreshed output automatically under a target freshness requirement. Under security control assessment conditions, Assume a dynamic table is updated instantly on every source write regardless of its refresh behavior and target lag.
D:That is a mistake for a different automated sensitive-data classification task, where A governance group is auditing hundreds of columns for sensitive categories before attaching masking controls. Under security control assessment conditions, Assume labeling a column as sensitive necessarily blocks every SELECT without a masking or access policy.
Question 434
[Partner onboarding review] The team proposes: Choose a cross-region or cross-cloud listing and fulfillment configuration supported by their accounts and Snowflake edition. Its actual requirement is: A privacy reviewer checks a collaboration rollout before new partner access is granted. 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 release needs an operationally defensible choice without accidentally changing unrelated access or data. Which observation most directly validates success?
- The consumer creates a database from the share and can SELECT only granted objects, with provider revocation reflected in access.
- The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
- An eligible test consumer finds the listing and follows its access or request workflow without unauthorized source-table exposure.
- Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
Explanation
Correct Answer
B:The recipient discovers the listing in its region and can read the expected version after fulfillment completes. This is an outcome test of the specified cross-region data listing fulfillment action under the partner onboarding review conditions. Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access.
Incorrect Answers
A:The consumer creates a database from the share and can SELECT only granted objects, with provider revocation reflected in access. This is an outcome for a different requirement: A Snowflake data provider wants a business partner who already has a Snowflake account to query governed datasets without copying daily CSV exports. It cannot by itself validate the proposed cross-region data listing fulfillment action; the appropriate test is: The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
C:In this partner onboarding review context, An eligible test consumer finds the listing and follows its access or request workflow without unauthorized source-table exposure. This is an outcome for a different requirement: A publisher needs customers to discover and request an approved dataset through a catalog rather than exchange account identifiers manually. It cannot by itself validate the proposed cross-region data listing fulfillment action; the appropriate test is: The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
D:In this partner onboarding review context, Replication status and a controlled secondary-account query confirm the expected dataset and documented lag. This is an outcome for a different requirement: A multinational organization requires a recoverable approved dataset in another supported Snowflake account region for resilience planning. It cannot by itself validate the proposed cross-region data listing fulfillment action; the appropriate test is: The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
Question 435
[Security control assessment] A quarterly access-control audit requires an evidence-backed answer. A pipeline service role has object OWNERSHIP but its replacement needs controlled transfer with minimal risk to existing privilege chains. 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?
- Assume ordinary schema ownership by a developer automatically centralizes every future grant in a managed-access model.
- Resize the warehouse without checking session role activation because compute can substitute for a dormant access role.
- Change only the warehouse's name and assume object ownership follows the newly renamed compute resource.
- Duplicate broad ACCOUNTADMIN grants to every role and assume inheritance cannot be inspected.
Explanation
Correct Answer
C:The supportable direction is: Plan an explicit OWNERSHIP transfer with the appropriate grant-management options and test dependent grants before cutover. The discriminating technical reason is: Ownership is a distinct powerful privilege; transferring it can affect existing grants and operational control.
Incorrect Answers
A:It does not directly defeat the present ownership transfer safety requirement, whose proper treatment is: Plan an explicit OWNERSHIP transfer with the appropriate grant-management options and test dependent grants before cutover.
B:Under security control assessment conditions, Resize the warehouse without checking session role activation because compute can substitute for a dormant access role. That is a mistake for a different primary and secondary role session task, where A user has an approved reporting role and a separate engineering role but a worksheet unexpectedly lacks SELECT even though the second role has the grant.
D:Under security control assessment conditions, Duplicate broad ACCOUNTADMIN grants to every role and assume inheritance cannot be inspected. It does not directly defeat the present ownership transfer safety requirement, whose proper treatment is: Plan an explicit OWNERSHIP transfer with the appropriate grant-management options and test dependent grants before cutover.
Question 436
[Ingestion design acceptance] A data engineering team is checking the design before promoting a pipeline. A cleanup procedure should execute every hour without an operator opening Snowsight, and its success history must be auditable. 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?
- Create a serverless ingestion pipe and expect it to act as a row-level scalar SQL function in every SELECT.
- Use an unrelated Snowflake warehouse as a file-storage namespace and assume tables automatically locate files by warehouse.
- Implement the entire multi-statement DML orchestration as a simple expression-only UDF and assume both objects behave identically.
- Create a stream and expect it to wake up hourly and execute the cleanup procedure without a task or consumer.
Explanation
Correct Answer
D:The discriminating technical reason is: Tasks schedule SQL or procedure execution, which a bare stream or file stage does not perform independently. Create a stream and expect it to wake up hourly and execute the cleanup procedure without a task or consumer.
Incorrect Answers
A:That is a mistake for a different sql udf reusable expression task, where Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. It does not directly defeat the present task scheduled orchestration requirement, whose proper treatment is: Create a properly authorized scheduled task using a supported warehouse or serverless configuration, then resume it.
B:That is a mistake for a different table stage file landing task, where An analyst must stage a small support file used only to load one existing target table, with no need for a shared named-stage contract. Under ingestion design acceptance conditions, Use an unrelated Snowflake warehouse as a file-storage namespace and assume tables automatically locate files by warehouse.
C:That is a mistake for a different stored procedure multi-step operation task, where An operations workflow must execute multiple related SQL statements with validation and controlled operational errors in one invocation. Under ingestion design acceptance conditions, Implement the entire multi-statement DML orchestration as a simple expression-only UDF and assume both objects behave identically.
Question 437
[Ingestion design acceptance] A reviewer recommends: Enable or refresh an appropriate directory table on a supported stage and query its file metadata. The requirement remains: A data engineering team is checking the design before promoting a pipeline. A document processing workflow keeps PDFs on a Snowflake stage and needs a queryable inventory of staged files before extraction. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Directory tables expose stage file metadata for unstructured-file workflows without turning the PDFs into ordinary relational rows by themselves.
- A table stage gives a convenient table-associated internal landing location without creating a separate named stage.
- External tables expose externally stored data through Snowflake-managed metadata rather than copying all rows into a normal native table.
- A file format controls how COPY parses file bytes; mismatched delimiter and quoting rules cause shifted or rejected columns.
Explanation
Correct Answer
A:In this ingestion design acceptance context, Directory tables expose stage file metadata for unstructured-file workflows without turning the PDFs into ordinary relational rows by themselves. The recommendation is linked to observable evidence for this exact case: Directory metadata contains the expected file paths, sizes and update timestamps after a controlled stage refresh. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
B:That option concerns table stage file landing, not the requirement in this case. It does not justify the specific directory table unstructured metadata action or its validation path: Directory metadata contains the expected file paths, sizes and update timestamps after a controlled stage refresh.
C:That option concerns external table over data lake, not the requirement in this case. It does not justify the specific directory table unstructured metadata action or its validation path: Directory metadata contains the expected file paths, sizes and update timestamps after a controlled stage refresh.
D:It does not justify the specific directory table unstructured metadata action or its validation path: Directory metadata contains the expected file paths, sizes and update timestamps after a controlled stage refresh.
Question 438
[Peak traffic performance incident] The team proposes: Evaluate Search Optimization Service for the supported predicate pattern and account edition after reviewing query profile. Its actual requirement is: A BI engineer is investigating the exact bottleneck observed under production-like load. An API workload repeatedly probes a huge fact table for single customer identifiers and needs fast selective lookup response. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which observation most directly validates success?
- Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost.
- The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
- The cluster count rises during bursts and query queue duration falls in matched concurrency tests.
- Queue time falls under concurrent load as cluster counts rise within the configured limits and resource usage remains observable.
Explanation
Correct Answer
B:The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed. This is an outcome test of the specified search optimization selective lookup action under the peak traffic performance incident conditions. Search optimization maintains access paths for selective probes and is not the same as adding a multi-cluster warehouse for concurrency.
Incorrect Answers
A:In this peak traffic performance incident context, Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost. This is an outcome for a different requirement: Analytical reports experience sporadic expensive, selective scan work that can benefit from offloading qualifying fragments. It cannot by itself validate the proposed search optimization selective lookup action; the appropriate test is: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
C:In this peak traffic performance incident context, The cluster count rises during bursts and query queue duration falls in matched concurrency tests. This is an outcome for a different requirement: Thirty concurrent dashboards have low individual runtimes but high wait times, and the account can use multi-cluster warehouses. It cannot by itself validate the proposed search optimization selective lookup action; the appropriate test is: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
D:In this peak traffic performance incident context, Queue time falls under concurrent load as cluster counts rise within the configured limits and resource usage remains observable. This is an outcome for a different requirement: During a product launch dozens of short BI queries queue even though each individual query already finishes quickly once it starts. It cannot by itself validate the proposed search optimization selective lookup action; the appropriate test is: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
Question 439
[Optimization regression test] A team is validating a targeted tuning plan against documented workloads and costs. An operations workflow must execute multiple related SQL statements with validation and controlled operational errors in one invocation. The conclusion must be justified with observable verification rather than an undocumented assumption. Which specific design mistake would most directly defeat the intended result?
- Increase one warehouse cluster's size exclusively while leaving the configured concurrency cluster limit at one.
- Create a stream and expect it to wake up hourly and execute the cleanup procedure without a task or consumer.
- Implement the entire multi-statement DML orchestration as a simple expression-only UDF and assume both objects behave identically.
- Create a serverless ingestion pipe and expect it to act as a row-level scalar SQL function in every SELECT.
Explanation
Correct Answer
C:The supportable direction is: Implement an authorized stored procedure in a supported language and test its transactional and error-handling semantics. The discriminating technical reason is: Stored procedures are suited to multi-statement procedural logic; ordinary scalar UDFs have different side-effect and execution constraints.
Incorrect Answers
A:Under optimization regression test conditions, Increase one warehouse cluster's size exclusively while leaving the configured concurrency cluster limit at one. It does not directly defeat the present stored procedure multi-step operation requirement, whose proper treatment is: Implement an authorized stored procedure in a supported language and test its transactional and error-handling semantics.
B:That is a mistake for a different task scheduled orchestration task, where A cleanup procedure should execute every hour without an operator opening Snowsight, and its success history must be auditable. Under optimization regression test conditions, Create a stream and expect it to wake up hourly and execute the cleanup procedure without a task or consumer.
D:That is a mistake for a different sql udf reusable expression task, where Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. Under optimization regression test conditions, Create a serverless ingestion pipe and expect it to act as a row-level scalar SQL function in every SELECT.
Question 440
[Optimization regression test] A team is validating a targeted tuning plan against documented workloads and costs. A churn dataset has multiple events with identical timestamps per customer, and selecting the latest without a tie-breaker gives inconsistent winners. The conclusion must be justified with observable verification rather than an undocumented assumption. Which specific design mistake would most directly defeat the intended result?
- Set a 90-day retention value in an unsupported edition and presume the statement will override product limits.
- Assume every syntactically similar SELECT always reuses cached results even after source tables are modified.
- Create a serverless ingestion pipe and expect it to act as a row-level scalar SQL function in every SELECT.
- Use ROW_NUMBER ordered only by a nonunique timestamp and claim the resulting winner is guaranteed in every execution.
Explanation
Correct Answer
D:The discriminating technical reason is: Window ordering must resolve ties to make latest-record selection reproducible across executions. This directly undermines window-function deterministic ranking in the specified context.
Incorrect Answers
A:That is a mistake for a different enterprise edition feature selection task, where A risk team must retain a permanent table's recoverable history for more than one day and also wants enterprise governance capabilities. Under optimization regression test conditions, Set a 90-day retention value in an unsupported edition and presume the statement will override product limits.
B:That is a mistake for a different persisted result reuse conditions task, where A team reruns an unchanged SELECT but sometimes receives cached results and sometimes spends compute again after underlying data changes. It does not directly defeat the present window-function deterministic ranking requirement, whose proper treatment is: Specify a deterministic secondary ordering key in ROW_NUMBER or another suitable window ranking expression.
C:That is a mistake for a different sql udf reusable expression task, where Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. It does not directly defeat the present window-function deterministic ranking requirement, whose proper treatment is: Specify a deterministic secondary ordering key in ROW_NUMBER or another suitable window ranking expression.