Snowflake SnowPro Core COF-C03 Practice Test 105
Question 1040
[Query performance review] A performance engineer is benchmarking representative queries before accepting changes. A quarterly metrics report stores one row per region and quarter but the executive view needs a separate column for each quarter. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Plan explicit cross-region collaboration or replication and review cloud and region boundaries before selecting the transfer path.
- Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit.
- Inspect pruning and the overlap of the filter column's values, then tune a supported clustering strategy if warranted.
- Use Snowflake PIVOT on the supported aggregation and quarter categories or an equivalent conditional aggregate.
Explanation
Correct Answer
D:PIVOT turns categorical row values into output columns, making a cross-tab view rather than merging update records. In this query performance review setting, the intended validation is: A validated report has one region row and expected quarter columns with reconciled totals.
Incorrect Answers
A: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. It is not the primary action for the stated pivot aggregate into columns need.
B:That action is relevant when A dashboard repeatedly calculates the same expensive aggregate over a large base table while the source changes incrementally. PIVOT turns categorical row values into output columns, making a cross-tab view rather than merging update records.
C:That action is relevant when A daily operational report filters on a narrow event-time range yet scans nearly the entire multi-terabyte event table. Inspect pruning and the overlap of the filter column's values, then tune a supported clustering strategy if warranted.
Question 1041
[Ingestion design acceptance] The team proposes: Configure Snowpipe with a pipe definition and supported auto-ingest cloud event notifications. Its actual requirement is: A data engineering team is checking the design before promoting a pipeline. Incoming small object-storage files appear several times per hour and should load automatically soon after the object store sends events. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY.
- A staged preview and validation load reconcile embedded delimiter strings and expected column counts across sample rows.
- The exported object files have the expected schema, row counts, and authorized destination path.
- LIST on the stage shows the expected files and COPY INTO loads exactly the reconciled input rows.
Explanation
Correct Answer
A:A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY. This is an outcome test of the specified snowpipe file event auto ingest action under the ingestion design acceptance conditions. Snowpipe uses Snowflake-managed compute to ingest staged files automatically, distinct from warehouse-driven scheduled COPY batches.
Incorrect Answers
B: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 snowpipe file event auto ingest action; the appropriate test is: A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY.
C:In this ingestion design acceptance context, The exported object files have the expected schema, row counts, and authorized destination path. This is an outcome for a different requirement: A downstream finance application requires an export of curated query results to an approved cloud storage path for a partner batch job. It cannot by itself validate the proposed snowpipe file event auto ingest action; the appropriate test is: A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY.
D:In this ingestion design acceptance context, 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 snowpipe file event auto ingest action; the appropriate test is: A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY.
Question 1042
[Production migration readiness] A reviewer recommends: Use Snowflake Time Travel AT or BEFORE syntax with a valid historical reference for the affected table. The requirement remains: A production migration team is preparing a controlled cutover; the solution must preserve existing account isolation. An analyst overwrites a segment mapping at noon and must inspect the table as it existed just before the change while still inside retention. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which explanation best justifies this recommendation?
- Extended retention for permanent objects is edition-dependent; selecting an appropriate edition precedes setting a longer recoverability window.
- Time Travel queries retained historical table versions during configured retention; the latest table state does not replace accessible history immediately.
- Streams track change-data-capture offsets for supported source objects; they are not the schedule or compute executor themselves.
- Dynamic tables express desired query results declaratively and Snowflake orchestrates refresh instead of requiring a bespoke stream/task graph for every stage.
Explanation
Correct Answer
B:In this production migration readiness context, Time Travel queries retained historical table versions during configured retention; the latest table state does not replace accessible history immediately. The recommendation is linked to observable evidence for this exact case: A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That explanation applies to a different enterprise edition feature selection problem: A risk team must retain a permanent table's recoverable history for more than one day and also wants enterprise governance capabilities.
C:Streams track change-data-capture offsets for supported source objects; they are not the schedule or compute executor themselves.
D:For the production migration readiness conditions, Dynamic tables express desired query results declaratively and Snowflake orchestrates refresh instead of requiring a bespoke stream/task graph for every stage. That explanation applies to a different dynamic table declarative refresh problem: A reporting team wants a transformation query to maintain refreshed output automatically under a target freshness requirement. It does not justify the specific time travel historical query action or its validation path: A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys.
Question 1043
[Architecture planning workshop] An internal platform architecture group is assessing the initial design. 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 reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Create a supported zero-copy clone of the database or selected schema and track later divergence.
- Enable or refresh an appropriate directory table on a supported stage and query its file metadata.
- Inspect partition pruning and clustering characteristics before choosing a supported data-organizing strategy.
- Assess clustering depth and query frequency before assigning a clustering key with ongoing maintenance cost.
Explanation
Correct Answer
C:Snowflake automatically stores micro-partition metadata; pruning is effective when filter values narrow which partitions can contain rows. In this architecture planning workshop setting, the intended validation is: Query Profile shows fewer partitions scanned for the date-filtered workload after the chosen organization change.
Incorrect Answers
A:That action is relevant when A product team needs a quick editable test copy of a large database but cannot afford to duplicate all stored bytes immediately. It is not the primary action for the stated micro-partition pruning metadata need.
B: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. Snowflake automatically stores micro-partition metadata; pruning is effective when filter values narrow which partitions can contain rows.
D:That action is relevant when A very large orders table receives steady inserts and selective date filters have poor clustering; the team needs a durable performance-versus-credit decision. Snowflake automatically stores micro-partition metadata; pruning is effective when filter values narrow which partitions can contain rows.
Question 1044
[Peak traffic performance incident] A reviewer recommends: Inspect pruning and the overlap of the filter column's values, then tune a supported clustering strategy if warranted. The requirement remains: A BI engineer is investigating the exact bottleneck observed under production-like load. A daily operational report filters on a narrow event-time range yet scans nearly the entire multi-terabyte event table. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which explanation best justifies this recommendation?
- Search optimization maintains access paths for selective probes and is not the same as adding a multi-cluster warehouse for concurrency.
- Stored procedures are suited to multi-statement procedural logic; ordinary scalar UDFs have different side-effect and execution constraints.
- Automatic Clustering may improve partition organization for repeated filters, but maintenance consumes credits and is not free on every table.
- Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
Explanation
Correct Answer
D:In this peak traffic performance incident context, Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning. The recommendation is linked to observable evidence for this exact case: The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns search optimization selective lookup, not the requirement in this case. It does not justify the specific selective filter micro-partition pruning action or its validation path: The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work.
B:For the peak traffic performance incident conditions, Stored procedures are suited to multi-statement procedural logic; ordinary scalar UDFs have different side-effect and execution constraints. That explanation applies to a different stored procedure multi-step operation problem: An operations workflow must execute multiple related SQL statements with validation and controlled operational errors in one invocation. It does not justify the specific selective filter micro-partition pruning action or its validation path: The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work.
C:For the peak traffic performance incident conditions, Automatic Clustering may improve partition organization for repeated filters, but maintenance consumes credits and is not free on every table. That explanation applies to a different automatic clustering cost tradeoff problem: A very large orders table receives steady inserts and selective date filters have poor clustering; the team needs a durable performance-versus-credit decision. It does not justify the specific selective filter micro-partition pruning action or its validation path: The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work.
Question 1045
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. A team reruns an unchanged SELECT but sometimes receives cached results and sometimes spends compute again after underlying data changes. 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?
- Assume every syntactically similar SELECT always reuses cached results even after source tables are modified.
- Rely on one engineer's user stage as a durable team-owned contract without assigning the required permissions.
- Assume switching on the service guarantees every simple SELECT is accelerated without checking eligibility.
- Conclude that a 4X larger warehouse must be 4X faster for every small query without collecting any timing data.
Explanation
Correct Answer
A:The supportable direction is: Inspect persisted query result cache eligibility, deterministic SQL, underlying data changes, and result reuse settings. Assume every syntactically similar SELECT always reuses cached results even after source tables are modified.
Incorrect Answers
B:That is a mistake for a different named internal stage for file reuse task, where A team needs a stable staging location where several engineers can PUT source files and run repeatable bulk loads for the same project. Under peak traffic performance incident conditions, Rely on one engineer's user stage as a durable team-owned contract without assigning the required permissions.
C:Under peak traffic performance incident conditions, Assume switching on the service guarantees every simple SELECT is accelerated without checking eligibility.
D:Under peak traffic performance incident conditions, Conclude that a 4X larger warehouse must be 4X faster for every small query without collecting any timing data. 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. It does not directly defeat the present persisted result reuse conditions requirement, whose proper treatment is: Inspect persisted query result cache eligibility, deterministic SQL, underlying data changes, and result reuse settings.
Question 1046
[Architecture planning workshop] The team proposes: Evaluate Snowflake's supported Apache Iceberg table options and catalog ownership for the intended interoperability model. Its actual requirement is: An internal platform architecture group is assessing the initial design. A platform team must exchange a lakehouse table through an Apache Iceberg-compatible metadata/catalog workflow instead of migrating every file to proprietary table storage. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- Dynamic table refresh history and data timestamps show the defined query output meeting the practical freshness target.
- A compatible engine discovers the expected Iceberg metadata and Snowflake queries return reconciled rows under documented catalog settings.
- SELECT against the external table returns staged records after location metadata has been refreshed appropriately.
- The table reports TRANSIENT type, and a documented rerun reconstructs the intermediate rows without relying on Fail-safe.
Explanation
Correct Answer
B:A compatible engine discovers the expected Iceberg metadata and Snowflake queries return reconciled rows under documented catalog settings. This is an outcome test of the specified iceberg interoperability decision action under the architecture planning workshop conditions. Iceberg tables provide an open-format table metadata layer; catalog integration and write permissions depend on their chosen management model.
Incorrect Answers
A:In this architecture planning workshop context, Dynamic table refresh history and data timestamps show the defined query output meeting the practical freshness target. This is an outcome for a different requirement: A reporting team wants a transformation query to maintain refreshed output automatically under a target freshness requirement. It cannot by itself validate the proposed iceberg interoperability decision action; the appropriate test is: A compatible engine discovers the expected Iceberg metadata and Snowflake queries return reconciled rows under documented catalog settings.
C:In this architecture planning 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 iceberg interoperability decision action; the appropriate test is: A compatible engine discovers the expected Iceberg metadata and Snowflake queries return reconciled rows under documented catalog settings.
D:The table reports TRANSIENT type, and a documented rerun reconstructs the intermediate rows without relying on Fail-safe. This is an outcome for a different requirement: An ingestion team keeps rebuildable intermediate records with no requirement for Snowflake-managed Fail-safe recovery. It cannot by itself validate the proposed iceberg interoperability decision action; the appropriate test is: A compatible engine discovers the expected Iceberg metadata and Snowflake queries return reconciled rows under documented catalog settings.
Question 1047
[Peak traffic performance incident] The team proposes: Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY. Its actual requirement is: A BI engineer is investigating the exact bottleneck observed under production-like load. A table contains several rows per customer and an analyst wants exactly the latest row for each customer after computing ROW_NUMBER. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which observation most directly validates success?
- SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
- The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work.
- A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria.
- A regression test with tied timestamps always chooses the same event according to the documented tie-breaker.
Explanation
Correct Answer
C:A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria. This is an outcome test of the specified qualify after window expression action under the peak traffic performance incident conditions. QUALIFY filters the results of window functions in Snowflake and is suited to post-window row selection.
Incorrect Answers
A:In this peak traffic performance incident context, SELECT using the function gives the same result as the underlying expression for a boundary-value test set. This is an outcome for a different requirement: Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. It cannot by itself validate the proposed qualify after window expression action; the appropriate test is: A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria.
B:In this peak traffic performance incident context, The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work. This is an outcome for a different requirement: A daily operational report filters on a narrow event-time range yet scans nearly the entire multi-terabyte event table. It cannot by itself validate the proposed qualify after window expression action; the appropriate test is: A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria.
D:In this peak traffic performance incident context, 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 qualify after window expression action; the appropriate test is: A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria.
Question 1048
[Identity and governance change request] A governance team is evaluating a controlled policy rollout without relaxing unrelated account access. Compliance teams reconcile last week's credit spending and access history across the account and need consistent long-range audit views. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Configure an appropriate OAuth authorization flow and token scopes along with Snowflake role privileges.
- Create a clear role hierarchy and grant the reader role to an appropriate higher functional role with the additional privileges.
- Inspect Snowflake Trust Center findings and follow documented remediation and review workflow for the supported account.
- Query appropriate SNOWFLAKE.ACCOUNT_USAGE views with awareness of documented ingestion latency and necessary privileges.
Explanation
Correct Answer
D:Account usage views support historical auditing but can lag live activity; they are distinct from instant session state. In this identity and governance change request setting, the intended validation is: Historical usage matches billed and recorded account activity after the documented latency window has elapsed.
Incorrect Answers
A:It is not the primary action for the stated account_usage audit latency need.
B:That action is relevant when A data science team needs the privileges of its reader role plus a limited set of model-development privileges without maintaining a duplicate grant list. It is not the primary action for the stated account_usage audit latency need.
C:That action is relevant when A security officer needs a centralized view of account configuration risks such as weak account controls and recommended fixes.
Question 1049
[Architecture planning workshop] The team proposes: Configure a suitable AUTO_SUSPEND interval and AUTO_RESUME for the development warehouse after assessing startup cadence. Its actual requirement is: An internal platform architecture group is assessing the initial design. A development warehouse completes a short test query and then remains idle for hours because the team left its suspend behavior unmanaged. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which observation most directly validates success?
- Warehouse events show suspension during idle periods and subsequent query-triggered resume without losing stored data.
- Monitor usage and alert/suspension events track the warehouse's consumed credits around configured thresholds.
- Query Profile shows reduced spill and execution time for the specific join under comparable data and query conditions.
- A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY.
Explanation
Correct Answer
A:Warehouse events show suspension during idle periods and subsequent query-triggered resume without losing stored data. This is an outcome test of the specified warehouse auto-suspend economics action under the architecture planning workshop conditions. Auto-suspend releases idle warehouse compute while auto-resume permits later queries; frequency should reflect workload and billing minimums.
Incorrect Answers
B:In this architecture planning workshop context, Monitor usage and alert/suspension events track the warehouse's consumed credits around configured thresholds. This is an outcome for a different requirement: A sandbox warehouse is used for unpredictable experiments and a manager needs credit alerts and a suspension threshold for that warehouse. It cannot by itself validate the proposed warehouse auto-suspend economics action; the appropriate test is: Warehouse events show suspension during idle periods and subsequent query-triggered resume without losing stored data.
C:In this architecture planning workshop 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 warehouse auto-suspend economics action; the appropriate test is: Warehouse events show suspension during idle periods and subsequent query-triggered resume without losing stored data.
D:In this architecture planning workshop context, A newly uploaded event-notified file appears in pipe load history and target rows without running a manual warehouse COPY. This is an outcome for a different requirement: Incoming small object-storage files appear several times per hour and should load automatically soon after the object store sends events. It cannot by itself validate the proposed warehouse auto-suspend economics action; the appropriate test is: Warehouse events show suspension during idle periods and subsequent query-triggered resume without losing stored data.
Question 1050
[Optimization regression test] A team is validating a targeted tuning plan against documented workloads and costs. A business-intelligence warehouse serves hundreds of short requests and most end-user delay occurs before the queries actually start. The conclusion must be justified with observable verification rather than an undocumented assumption. Which Snowflake action best meets this specific requirement?
- Evaluate Query Acceleration Service eligibility, configuration, and Query Profile signals for the actual workload.
- Inspect queued time and concurrency load, then isolate workloads or add supported multi-cluster capacity.
- Use Snowflake's load metadata/history and carefully controlled COPY retry behavior to identify already ingested files.
- Use a suitably configured multi-cluster warehouse and auto-scale policy where supported to address concurrency.
Explanation
Correct Answer
B:A concurrency bottleneck is principally about simultaneous slots rather than the CPU needs of one query. In this optimization regression test setting, the intended validation is: Queued time drops on the same load test and per-query execution time remains stable while cluster usage is visible.
Incorrect Answers
A:It is not the primary action for the stated query queueing load analysis need.
C:That action is relevant when A nightly pipeline may retry an earlier bulk load after a network timeout and must avoid inadvertently duplicating files that were already committed. A concurrency bottleneck is principally about simultaneous slots rather than the CPU needs of one query.
D: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. It is not the primary action for the stated query queueing load analysis need.