Snowflake SnowPro Core COF-C03 Practice Test 3
Question 20
[Production cutover rehearsal] A reviewer recommends: Create a properly authorized scheduled task using a supported warehouse or serverless configuration, then resume it. The requirement remains: A data integration group runs a controlled replay with representative files or events. A cleanup procedure should execute every hour without an operator opening Snowsight, and its success history must be auditable. The conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- Named internal stages support managed file staging and reusable load references across authorized sessions.
- Stored procedures are suited to multi-statement procedural logic; ordinary scalar UDFs have different side-effect and execution constraints.
- A table stage gives a convenient table-associated internal landing location without creating a separate named stage.
- Tasks schedule SQL or procedure execution, which a bare stream or file stage does not perform independently.
Explanation
Correct Answer
D:In this production cutover rehearsal context, Tasks schedule SQL or procedure execution, which a bare stream or file stage does not perform independently. The recommendation is linked to observable evidence for this exact case: TASK_HISTORY contains scheduled executions and the target cleanup effects match the hourly schedule. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns named internal stage for file reuse, not the requirement in this case. It does not justify the specific task scheduled orchestration action or its validation path: TASK_HISTORY contains scheduled executions and the target cleanup effects match the hourly schedule.
B:That option concerns stored procedure multi-step operation, not the requirement in this case. 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.
C:That option concerns table stage file landing, not the requirement in this case. A table stage gives a convenient table-associated internal landing location without creating a separate named stage.
Question 21
[Provider and consumer workshop] The team proposes: Configure supported database or account replication and plan promotion or failover procedures for the desired recovery objectives. Its actual requirement is: A data collaboration team is defining the permitted provider and consumer relationship. A multinational organization requires a recoverable approved dataset in another supported Snowflake account region for resilience planning. 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 reader account signs in and queries the intended shared database while billing and warehouse usage are monitored by the provider.
- The consumer creates a database from the share and can SELECT only granted objects, with provider revocation reflected in access.
- The test clone is independently writable and initial storage growth is small compared with a full reload of every source table.
Explanation
Correct Answer
A:Replication status and a controlled secondary-account query confirm the expected dataset and documented lag. This is an outcome test of the specified replication across regions for availability action under the provider and consumer workshop conditions. Replication moves supported data and metadata across account or region boundaries and differs from a local zero-copy clone.
Incorrect Answers
B: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 replication across regions for availability action; the appropriate test is: Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
C:In this provider and consumer workshop context, 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 replication across regions for availability action; the appropriate test is: Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
D:In this provider and consumer workshop context, The test clone is independently writable and initial storage growth is small compared with a full reload of every source table. This is an outcome for a different requirement: A product team needs a quick editable test copy of a large database but cannot afford to duplicate all stored bytes immediately. It cannot by itself validate the proposed replication across regions for availability action; the appropriate test is: Replication status and a controlled secondary-account query confirm the expected dataset and documented lag.
Question 22
[Post-rollout service review] An operations group is reassessing the installed data platform after a representative workload trial. An ingestion team keeps rebuildable intermediate records with no requirement for Snowflake-managed Fail-safe recovery. The conclusion must be justified with observable verification rather than an undocumented assumption. Which Snowflake action best meets this specific requirement?
- Use an appropriate permanent table with supported Time Travel retention and understand its Fail-safe protection.
- Use a transient table for rebuildable intermediate storage after documenting its shorter recovery protections.
- Build a supported dynamic table with a SELECT definition, warehouse, and achievable TARGET_LAG setting.
- Use applicable Time Travel UNDROP or historical recovery inside retention, recognizing that Fail-safe is a separate Snowflake-managed last resort.
Explanation
Correct Answer
B:Transient tables avoid the Fail-safe period and are appropriate where the pipeline can safely recreate the data. In this post-rollout service review setting, the intended validation is: The table reports TRANSIENT type, and a documented rerun reconstructs the intermediate rows without relying on Fail-safe.
Incorrect Answers
A:That action is relevant when An audited production dataset requires recovery mechanisms beyond a short staging lifecycle, and accidental deletion is a realistic threat. It is not the primary action for the stated transient staging table lifecycle need.
C:That action is relevant when A reporting team wants a transformation query to maintain refreshed output automatically under a target freshness requirement. It is not the primary action for the stated transient staging table lifecycle need.
D:That action is relevant when A production permanent table was dropped within its configured retention period, and an operator needs a self-service recovery plan rather than a support escalation. It is not the primary action for the stated transient staging table lifecycle need.
Question 23
[Post-rollout service review] A reviewer recommends: Keep data in managed database storage and resume compute only when processing is needed. The requirement remains: An operations group is reassessing the installed data platform after a representative workload trial. An administrator suspends every reporting warehouse overnight and the operations manager fears that previously committed tables will be erased. The conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- Warehouses provide compute; they are not parents of schemas or tables within the Snowflake object catalog.
- Unqualified object resolution depends on active session context; the warehouse alone does not specify which schema is targeted.
- The database storage layer persists committed table data independently from the lifecycle of virtual warehouse clusters.
- External tables expose externally stored data through Snowflake-managed metadata rather than copying all rows into a normal native table.
Explanation
Correct Answer
C:In this post-rollout service review context, The database storage layer persists committed table data independently from the lifecycle of virtual warehouse clusters. The recommendation is linked to observable evidence for this exact case: Queries return the same committed rows after a warehouse resumes, while compute credit consumption stops during suspension. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns account and schema object hierarchy, not the requirement in this case. Warehouses provide compute; they are not parents of schemas or tables within the Snowflake object catalog.
B:That option concerns session database and role context, not the requirement in this case. Unqualified object resolution depends on active session context; the warehouse alone does not specify which schema is targeted.
D:That option concerns external table over data lake, not the requirement in this case. External tables expose externally stored data through Snowflake-managed metadata rather than copying all rows into a normal native table.
Question 24
[Architecture planning workshop] An internal platform architecture group is assessing the initial design. An analyst needs a browser-based place to run worksheets, inspect query history, and explore Snowflake objects without provisioning an IDE. 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 temporary table for the session-scoped scratch result and avoid relying on it after disconnect.
- Use Snowflake Time Travel AT or BEFORE syntax with a valid historical reference for the affected table.
- Evaluate Cortex Analyst with a suitable semantic model or semantic view over authorized structured data.
- Use Snowsight with the permitted role and warehouse to manage worksheets and inspect account-visible objects.
Explanation
Correct Answer
D:Snowsight is Snowflake's graphical interface and surfaces SQL authoring and activity inspection based on the session's access. In this architecture planning workshop setting, the intended validation is: The analyst runs a permitted worksheet and sees its query details and object navigation in Snowsight.
Incorrect Answers
A:That action is relevant when A SQL worksheet needs a scratch table only for calculations performed in a single session and must not expose it as a durable shared object. It is not the primary action for the stated snowsight analyst interface need.
B:That action is relevant when An analyst overwrites a segment mapping at noon and must inspect the table as it existed just before the change while still inside retention. It is not the primary action for the stated snowsight analyst interface need.
C:That action is relevant when Business users want to ask plain-language questions against governed structured metrics with defined business terminology. It is not the primary action for the stated snowsight analyst interface need.
Question 25
[Pipeline incident investigation] An on-call ingestion engineer needs a targeted fix that preserves source-to-target reconciliation. An IoT product emits row events continuously and needs lower-latency ingestion than file-batched loading provides. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Evaluate the current supported Snowpipe Streaming SDK or high-performance architecture with an appropriate channel design.
- Evaluate hybrid tables for the transactional access pattern and verify feature and account availability.
- Configure Snowpipe with a pipe definition and supported auto-ingest cloud event notifications.
- Use the table's built-in stage where suitable and load into the target using supported commands.
Explanation
Correct Answer
A:Snowpipe Streaming ingests rows via streaming APIs without requiring each small batch to first become a staged cloud file. In this pipeline incident investigation setting, the intended validation is: End-to-end event timestamps show data arriving within the required streaming latency under peak event load.
Incorrect Answers
B:That action is relevant when A customer application requires low-latency keyed row updates with enforced transactional constraints that fit a supported hybrid-table design. It is not the primary action for the stated snowpipe streaming low latency need.
C:That action is relevant when Incoming small object-storage files appear several times per hour and should load automatically soon after the object store sends events. It is not the primary action for the stated snowpipe streaming low latency need.
D:That action is relevant when 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. It is not the primary action for the stated snowpipe streaming low latency need.
Question 26
[Query performance review] A reviewer recommends: Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. A table contains several rows per customer and an analyst wants exactly the latest row for each customer after computing ROW_NUMBER. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
- QUALIFY filters the results of window functions in Snowflake and is suited to post-window row selection.
- FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item.
- Result reuse depends on strict eligibility and unchanged relevant data; it is distinct from local warehouse data-cache reuse.
Explanation
Correct Answer
B:In this query performance review context, QUALIFY filters the results of window functions in Snowflake and is suited to post-window row selection. The recommendation is linked to observable evidence for this exact case: A duplicate-key test returns one newest row per customer with deterministic tie-breaking criteria. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns selective filter micro-partition pruning, not the requirement in this case. Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
C:That option concerns flatten nested array rows, not the requirement in this case. FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item.
D:That option concerns persisted result reuse conditions, not the requirement in this case. Result reuse depends on strict eligibility and unchanged relevant data; it is distinct from local warehouse data-cache reuse.
Question 27
[Query performance review] The team proposes: Use Snowflake PIVOT on the supported aggregation and quarter categories or an equivalent conditional aggregate. Its actual requirement is: 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 observation most directly validates success?
- The report's partition-scan fraction falls and total bytes scanned improve after justified clustering work.
- Separate user-role tests return only the authorized region's row keys while total source data remains unchanged.
- A validated report has one region row and expected quarter columns with reconciled totals.
- The dashboard query can benefit from the maintained aggregate and maintenance costs fit the service budget.
Explanation
Correct Answer
C:A validated report has one region row and expected quarter columns with reconciled totals. This is an outcome test of the specified pivot aggregate into columns action under the query performance review conditions. PIVOT turns categorical row values into output columns, making a cross-tab view rather than merging update records.
Incorrect Answers
A:In this query performance review 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 pivot aggregate into columns action; the appropriate test is: A validated report has one region row and expected quarter columns with reconciled totals.
B:In this query performance review context, Separate user-role tests return only the authorized region's row keys while total source data remains unchanged. This is an outcome for a different requirement: Regional managers share a table but must see only the geographic rows permitted by their assigned region. It cannot by itself validate the proposed pivot aggregate into columns action; the appropriate test is: A validated report has one region row and expected quarter columns with reconciled totals.
D:In this query performance review context, The dashboard query can benefit from the maintained aggregate and maintenance costs fit the service budget. This is an outcome for a different requirement: A dashboard repeatedly calculates the same expensive aggregate over a large base table while the source changes incrementally. It cannot by itself validate the proposed pivot aggregate into columns action; the appropriate test is: A validated report has one region row and expected quarter columns with reconciled totals.
Question 28
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. A complicated analytical transform has predictable memory pressure, but management wants evidence before raising per-second warehouse consumption. 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.
- Configure a suitable AUTO_SUSPEND interval and AUTO_RESUME for the development warehouse after assessing startup cadence.
- Set appropriate min/max cluster limits and an autoscaling policy rather than only increasing single-cluster size.
- Benchmark representative SQL on appropriately sized warehouses while keeping input data and workload conditions comparable.
Explanation
Correct Answer
D:Warehouse resizing changes resources per cluster; benchmarking reveals whether one heavyweight query benefits enough to offset credits. In this peak traffic performance incident setting, the intended validation is: Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size.
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 warehouse sizing performance experiment need.
B:That action is relevant when A development warehouse completes a short test query and then remains idle for hours because the team left its suspend behavior unmanaged. It is not the primary action for the stated warehouse sizing performance experiment need.
C:That action is relevant when Thirty concurrent dashboards have low individual runtimes but high wait times, and the account can use multi-cluster warehouses. It is not the primary action for the stated warehouse sizing performance experiment need.
Question 29
[Partner onboarding review] 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 Snowflake action best meets this specific requirement?
- Choose a cross-region or cross-cloud listing and fulfillment configuration supported by their accounts and Snowflake edition.
- Configure supported database or account replication and plan promotion or failover procedures for the desired recovery objectives.
- Create a secure direct share for eligible objects and grant it to the approved consumer account under supported region and cloud conditions.
- Create and govern a suitable Snowflake Marketplace listing with the intended visibility, terms, and fulfillment model.
Explanation
Correct Answer
A:Cross-region data product fulfillment has specific replication and availability requirements distinct from same-region share access. In this partner onboarding review setting, the intended validation is: The recipient discovers the listing in its region and can read the expected version after fulfillment completes.
Incorrect Answers
B:That action is relevant when A multinational organization requires a recoverable approved dataset in another supported Snowflake account region for resilience planning. It is not the primary action for the stated cross-region data listing fulfillment need.
C:That action is relevant when A Snowflake data provider wants a business partner who already has a Snowflake account to query governed datasets without copying daily CSV exports. It is not the primary action for the stated cross-region data listing fulfillment need.
D:That action is relevant when A publisher needs customers to discover and request an approved dataset through a catalog rather than exchange account identifiers manually. It is not the primary action for the stated cross-region data listing fulfillment need.
Question 30
[Optimization regression test] A team is validating a targeted tuning plan against documented workloads and costs. A 70-second analytics query has become slow after a schema change, and the team wants to find whether scanning, joining, or spilling dominates its runtime. The conclusion must be justified with observable verification rather than an undocumented assumption. Which Snowflake action best meets this specific requirement?
- Evaluate Search Optimization Service for the supported predicate pattern and account edition after reviewing query profile.
- Open the query's profile and compare execution operators, partitions scanned, bytes processed, and spill indicators before tuning.
- Benchmark representative SQL on appropriately sized warehouses while keeping input data and workload conditions comparable.
- Test a larger warehouse size or a more efficient query plan to give the expensive join adequate resources.
Explanation
Correct Answer
B:Query Profile provides evidence about actual execution instead of assuming all slow SQL has the same cause. In this optimization regression test setting, the intended validation is: A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
Incorrect Answers
A:That action is relevant when An API workload repeatedly probes a huge fact table for single customer identifiers and needs fast selective lookup response. It is not the primary action for the stated query profile bottleneck localization need.
C:That action is relevant when A complicated analytical transform has predictable memory pressure, but management wants evidence before raising per-second warehouse consumption. It is not the primary action for the stated query profile bottleneck localization need.
D: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 profile bottleneck localization need.