Snowflake SnowPro Core COF-C03 Practice Test 45
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.
Question 441
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. A dashboard repeatedly calculates the same expensive aggregate over a large base table while the source changes incrementally. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit.
- Account for local warehouse data-cache loss across suspension, and test steady-state cost/performance without assuming persistence.
- Publish an appropriately designed secure view and grant only the needed access to the consumer role.
- Use Snowflake PIVOT on the supported aggregation and quarter categories or an equivalent conditional aggregate.
Explanation
Correct Answer
A:Materialized views persist eligible precomputed results, reducing repeated calculation at the expense of refresh storage and compute. In this peak traffic performance incident setting, the intended validation is: The dashboard query can benefit from the maintained aggregate and maintenance costs fit the service budget.
Incorrect Answers
B:That action is relevant when A repeated large scan runs faster on the same warm warehouse, but the speedup disappears after the warehouse has been suspended. It is not the primary action for the stated materialized view precomputation need.
C:That action is relevant when A data provider must hide sensitive query definition details while exposing a restricted projection of source data to consumers. It is not the primary action for the stated materialized view precomputation need.
D:That action is relevant when A quarterly metrics report stores one row per region and quarter but the executive view needs a separate column for each quarter. It is not the primary action for the stated materialized view precomputation need.
Question 442
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. Analytical reports experience sporadic expensive, selective scan work that can benefit from offloading qualifying fragments. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit.
- Evaluate Query Acceleration Service eligibility, configuration, and Query Profile signals for the actual workload.
- Test a larger warehouse size or a more efficient query plan to give the expensive join adequate resources.
- Inspect queued time and concurrency load, then isolate workloads or add supported multi-cluster capacity.
Explanation
Correct Answer
B:Query Acceleration Service can offload eligible query components to shared compute; it does not replace base warehouse execution for every operation.
Incorrect Answers
A:That action is relevant when A dashboard repeatedly calculates the same expensive aggregate over a large base table while the source changes incrementally. It is not the primary action for the stated query acceleration service suitability need.
C: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. It is not the primary action for the stated query acceleration service suitability need.
D:That action is relevant when A business-intelligence warehouse serves hundreds of short requests and most end-user delay occurs before the queries actually start. It is not the primary action for the stated query acceleration service suitability need.
Question 443
[Provider and consumer workshop] 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 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 observation most directly validates success?
- The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
- Only authorized member organizations see the private listings and permitted data products after membership checks.
- The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
- Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
Explanation
Correct Answer
C: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 provider and consumer workshop conditions. Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access.
Incorrect Answers
A:In this provider and consumer workshop context, The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider. This is an outcome for a different requirement: A small research partner needs curated data but has no existing Snowflake account and the provider wants a controlled hosted-consumer path. 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.
B:In this provider and consumer workshop context, Only authorized member organizations see the private listings and permitted data products after membership checks. This is an outcome for a different requirement: Several subsidiaries must exchange curated datasets inside a controlled organizational community rather than list them publicly. 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 provider and consumer workshop 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 444
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. A dashboard repeatedly calculates the same expensive aggregate over a large base table while the source changes incrementally. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which specific design mistake would most directly defeat the intended result?
- Run a daily full-table INSERT without keys and assume it is equivalent to applying the stream's change history.
- Use MERGE to rewrite base table records as the only available method for a cross-tab SELECT.
- Treat the suspended warehouse's local cache as a permanent table copy that must remain available after shutdown.
- Use a plain view with an identical SELECT and assume it stores refreshed aggregate rows permanently at no cost.
Explanation
Correct Answer
D:The supportable direction is: Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit. This directly undermines materialized view precomputation in the specified context.
Incorrect Answers
A:That is a mistake for a different stream cdc consumption task, where An orders table changes frequently and a downstream merge process must apply inserts and updates since its last committed checkpoint. Under peak traffic performance incident conditions, Run a daily full-table INSERT without keys and assume it is equivalent to applying the stream's change history.
B:That is a mistake for a different pivot aggregate into columns task, where A quarterly metrics report stores one row per region and quarter but the executive view needs a separate column for each quarter. Under peak traffic performance incident conditions, Use MERGE to rewrite base table records as the only available method for a cross-tab SELECT.
C:That is a mistake for a different warehouse local data cache task, where A repeated large scan runs faster on the same warm warehouse, but the speedup disappears after the warehouse has been suspended. It does not directly defeat the present materialized view precomputation requirement, whose proper treatment is: Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit.
Question 445
[Production migration readiness] The team proposes: Use Snowsight with the permitted role and warehouse to manage worksheets and inspect account-visible objects. Its actual requirement is: A production migration team is preparing a controlled cutover; the solution must preserve existing account isolation. An analyst needs a browser-based place to run worksheets, inspect query history, and explore Snowflake objects without provisioning an IDE. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which observation most directly validates success?
- The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight.
- SHOW GRANTS shows only the intended USAGE and SELECT path, and a tested UPDATE against the base table is denied.
- Query Profile shows reduced spill and execution time for the specific join under comparable data and query conditions.
- Representative business questions produce reviewable SQL aligned with the approved semantic definitions and permitted result rows.
Explanation
Correct Answer
A:The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight. This is an outcome test of the specified snowsight analyst interface action under the production migration readiness conditions. Snowsight is Snowflake's graphical interface and surfaces SQL authoring and activity inspection based on the session's access.
Incorrect Answers
B:SHOW GRANTS shows only the intended USAGE and SELECT path, and a tested UPDATE against the base table is denied. This is an outcome for a different requirement: A finance analyst needs SELECT on a curated reporting view but must not modify the underlying transactions table or own the database. It cannot by itself validate the proposed snowsight analyst interface action; the appropriate test is: The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight.
C:In this production migration readiness context, Query Profile shows reduced spill and execution time for the specific join under comparable data and query conditions. This is an outcome for a different requirement: A warehouse consistently spills intermediate data on a single memory-intensive analytical join while the queue length is close to zero. It cannot by itself validate the proposed snowsight analyst interface action; the appropriate test is: The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight.
D:In this production migration readiness context, Representative business questions produce reviewable SQL aligned with the approved semantic definitions and permitted result rows. This is an outcome for a different requirement: Business users want to ask plain-language questions against governed structured metrics with defined business terminology. It cannot by itself validate the proposed snowsight analyst interface action; the appropriate test is: The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight.
Question 446
[Ingestion design acceptance] A data engineering team is checking the design before promoting a pipeline. A Snowflake SQL process must invoke an allowed remote service through an established API gateway, with credentials controlled outside analyst worksheets. 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?
- Grant access to a nonexistent consumer account identifier and assume Snowflake creates a working user interface automatically.
- Grant SELECT on the source table and assume this automatically authorizes unrestricted outbound HTTPS calls to any public API.
- Assume an external table is identical to a mutable standard Snowflake table with unrestricted DELETE support.
- Paste a long-lived cloud access secret into every COPY INTO statement and claim it is equivalent to an IAM trust integration.
Explanation
Correct Answer
B:The discriminating technical reason is: API integrations let approved Snowflake operations call specific external endpoints without exposing arbitrary raw credentials in every SQL statement. The supportable direction is: Use a supported API integration and external function pattern after confirming cloud/platform and security requirements.
Incorrect Answers
A:That is a mistake for a different reader account for external consumer task, where A small research partner needs curated data but has no existing Snowflake account and the provider wants a controlled hosted-consumer path. Under ingestion design acceptance conditions, Grant access to a nonexistent consumer account identifier and assume Snowflake creates a working user interface automatically.
C:Under ingestion design acceptance conditions, Assume an external table is identical to a mutable standard Snowflake table with unrestricted DELETE support.
D:That is a mistake for a different external stage cloud identity integration task, where A cloud operations group needs to load CSVs from a controlled S3 bucket but security prohibits permanent cloud storage secrets in SQL scripts. Under ingestion design acceptance conditions, Paste a long-lived cloud access secret into every COPY INTO statement and claim it is equivalent to an IAM trust integration.
Question 447
[Provider and consumer workshop] The team proposes: Evaluate and provision a Snowflake reader account with authorized shares and documented provider billing responsibilities. Its actual requirement is: A data collaboration team is defining the permitted provider and consumer relationship. A small research partner needs curated data but has no existing Snowflake account and the provider wants a controlled hosted-consumer path. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
- The consumer creates a database from the share and can SELECT only granted objects, with provider revocation reflected in access.
- The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
- SELECT against the external table returns staged records after location metadata has been refreshed appropriately.
Explanation
Correct Answer
C:Reader accounts can let consumers without their own full Snowflake account access provider-shared data, subject to provisioning and cost controls. This is an outcome test of the specified reader account for external consumer action under the provider and consumer workshop conditions.
Incorrect Answers
A:In this provider and consumer workshop 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 reader account for external consumer action; the appropriate test is: The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
B: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 reader account for external consumer action; the appropriate test is: The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
D:In this provider and consumer workshop context, SELECT against the external table returns staged records after location metadata has been refreshed appropriately. This is an outcome for a different requirement: A data scientist needs governed SQL reads over files remaining in external object storage, without an ordinary Snowflake-managed load step. It cannot by itself validate the proposed reader account for external consumer action; the appropriate test is: The reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
Question 448
[Pipeline incident investigation] An on-call ingestion engineer needs a targeted fix that preserves source-to-target reconciliation. A cloud operations group needs to load CSVs from a controlled S3 bucket but security prohibits permanent cloud storage secrets in SQL scripts. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Configure an external table and its stage/integration and maintain the external file metadata as required.
- Create an appropriately permissioned named internal stage and define a compatible file format for the incoming files.
- Use COPY INTO a stage or external location from the approved SELECT with an explicit supported file format.
- Configure an authorized storage integration and external stage with the required cloud trust and path restrictions.
Explanation
Correct Answer
D:In this pipeline incident investigation setting, the intended validation is: A constrained LIST or COPY INTO test succeeds for the permitted bucket path while prohibited paths stay inaccessible.
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. Storage integrations provide an identity-based bridge for supported cloud storage access and are safer than embedding long-lived secrets.
B:That action is relevant when A team needs a stable staging location where several engineers can PUT source files and run repeatable bulk loads for the same project. It is not the primary action for the stated external stage cloud identity integration 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. Use COPY INTO a stage or external location from the approved SELECT with an explicit supported file format.
Question 449
[Identity and governance change request] A governance team is evaluating a controlled policy rollout without relaxing unrelated account access. A sensitive integration requires clients to use supported private cloud networking instead of routing all traffic through public endpoints. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Configure the available private connectivity option for the eligible edition and cloud, then validate DNS and routing.
- Configure a supported federated SSO security integration and verify identity, certificates, and account routing.
- Evaluate Snowflake Data Exchange or a supported private listing arrangement with approved membership and governance.
- Create and attach an appropriate Snowflake network policy or network rule-based policy for the specified identities and IP ranges.
Explanation
Correct Answer
A:Private endpoint routing is an infrastructure-level feature independent of Snowflake object permissions. In this identity and governance change request setting, the intended validation is: Network-path evidence shows traffic using the configured private endpoint and public-route attempts are not used.
Incorrect Answers
B:That action is relevant when An enterprise uses a corporate identity provider and wants centrally controlled employee login instead of unmanaged separate passwords. It is not the primary action for the stated private network endpoint verification need.
C:That action is relevant when Several subsidiaries must exchange curated datasets inside a controlled organizational community rather than list them publicly. It is not the primary action for the stated private network endpoint verification need.
D:That action is relevant when An organization must restrict logins to approved corporate egress ranges even when credentials are correct. It is not the primary action for the stated private network endpoint verification need.
Question 450
[Query performance review] The team proposes: Create a scoped SQL user-defined function and grant the appropriate usage privileges to consuming roles. Its actual requirement is: A performance engineer is benchmarking representative queries before accepting changes. Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- The procedure reports a controlled result after applying the permitted sequence and handles the simulated error path safely.
- SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
- A staged preview and validation load reconcile embedded delimiter strings and expected column counts across sample rows.
- TASK_HISTORY contains scheduled executions and the target cleanup effects match the hourly schedule.
Explanation
Correct Answer
B: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 query performance review conditions.
Incorrect Answers
A:In this query performance review context, The procedure reports a controlled result after applying the permitted sequence and handles the simulated error path safely. This is an outcome for a different requirement: An operations workflow must execute multiple related SQL statements with validation and controlled operational errors in one invocation. 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.
C: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 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 query performance review 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.