Snowflake SnowPro Core COF-C03 Practice Test 104
Question 1030
[Query performance review] A reviewer recommends: Load the payload into a VARIANT column and use supported path extraction and casts for downstream typed calculations. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. A mobile application sends JSON documents with evolving optional fields, and developers want to retain the nested payload without predefining every scalar column. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- PIVOT turns categorical row values into output columns, making a cross-tab view rather than merging update records.
- VARIANT supports semi-structured values and path access, unlike a single plain numeric column for arbitrarily nested data.
- FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item.
- Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
Explanation
Correct Answer
B:In this query performance review context, VARIANT supports semi-structured values and path access, unlike a single plain numeric column for arbitrarily nested data. The recommendation is linked to observable evidence for this exact case: SELECT of nested attributes returns expected scalar values and missing optional fields are handled according to the SQL logic. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:For the query performance review conditions, PIVOT turns categorical row values into output columns, making a cross-tab view rather than merging update records. That explanation applies to a different pivot aggregate into columns problem: A quarterly metrics report stores one row per region and quarter but the executive view needs a separate column for each quarter. It does not justify the specific variant semi-structured access action or its validation path: SELECT of nested attributes returns expected scalar values and missing optional fields are handled according to the SQL logic.
C:For the query performance review conditions, FLATTEN expands nested arrays or objects into tabular rows; ordinary JSON string extraction does not iterate every item. That explanation applies to a different flatten nested array rows problem: An event JSON record contains a purchases array and analysts need one relational row per purchase item while retaining the parent event identifier. It does not justify the specific variant semi-structured access action or its validation path: SELECT of nested attributes returns expected scalar values and missing optional fields are handled according to the SQL logic.
D:For the query performance review conditions, Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning. That explanation applies to a different selective filter micro-partition pruning problem: A daily operational report filters on a narrow event-time range yet scans nearly the entire multi-terabyte event table. It does not justify the specific variant semi-structured access action or its validation path: SELECT of nested attributes returns expected scalar values and missing optional fields are handled according to the SQL logic.
Question 1031
[Pipeline incident investigation] An on-call ingestion engineer needs a targeted fix that preserves source-to-target reconciliation. A financial batch is required to fail atomically when any unexpected malformed source record appears rather than silently omit data. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Use zero-copy cloning for an authorized internal copy and distinguish it from a cross-account secure share.
- Use Snowflake's load metadata/history and carefully controlled COPY retry behavior to identify already ingested files.
- Select a strict COPY INTO error policy such as ABORT_STATEMENT and reconcile source counts before releasing downstream results.
- Create a supported zero-copy clone of the database or selected schema and track later divergence.
Explanation
Correct Answer
C:COPY error behavior determines whether load execution aborts or continues past bad rows; strict policy fits completeness-sensitive feeds. In this pipeline incident investigation setting, the intended validation is: The malformed test file causes the controlled load to abort without accepting an incomplete batch of financial rows.
Incorrect Answers
A:It is not the primary action for the stated copy into error-handling choice need.
B: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. COPY error behavior determines whether load execution aborts or continues past bad rows; strict policy fits completeness-sensitive feeds.
D: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 copy into error-handling choice need.
Question 1032
[Ingestion design acceptance] A data engineering team is checking the design before promoting a pipeline. An orders table changes frequently and a downstream merge process must apply inserts and updates since its last committed checkpoint. 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 properly authorized scheduled task using a supported warehouse or serverless configuration, then resume it.
- Define a supported dynamic table with a target lag and valid refresh mode for the transformation query.
- Evaluate a supported materialized view for the recurring aggregation and quantify maintenance and query benefit.
- Create a stream on the supported source and consume its delta into the target within a transactionally safe workflow.
Explanation
Correct Answer
D:A stream tracks table-change records and an offset, while the downstream DML determines how those changes are materialized. In this ingestion design acceptance setting, the intended validation is: After controlled source updates, the stream returns the expected delta, and a committed MERGE advances the consumption checkpoint.
Incorrect Answers
A:That action is relevant when A cleanup procedure should execute every hour without an operator opening Snowsight, and its success history must be auditable. A stream tracks table-change records and an offset, while the downstream DML determines how those changes are materialized.
B:That action is relevant when A pipeline repeatedly joins newly ingested transactions to reference data and needs a declarative freshness objective instead of hand-managed CDC steps. It is not the primary action for the stated stream cdc consumption need.
C: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 stream cdc consumption need.
Question 1033
[Query performance review] A reviewer recommends: Use a deterministic MERGE condition on a true business key with MATCHED UPDATE and NOT MATCHED INSERT branches. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. A CRM staging feed has new customers and changed addresses and needs an idempotent update-or-insert into the curated customer table. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- MERGE reconciles source and target records under explicit key conditions and avoids blind duplicate inserts on retries.
- Key-pair authentication supports service access while avoiding hard-coded interactive user passwords, subject to secure private-key handling.
- A stream tracks table-change records and an offset, while the downstream DML determines how those changes are materialized.
- Result reuse depends on strict eligibility and unchanged relevant data; it is distinct from local warehouse data-cache reuse.
Explanation
Correct Answer
A:In this query performance review context, MERGE reconciles source and target records under explicit key conditions and avoids blind duplicate inserts on retries. The recommendation is linked to observable evidence for this exact case: A repeated staging batch produces stable row counts and correctly updates changed addresses without duplicated business keys. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
B:That option concerns workload identity key-pair authentication, not the requirement in this case. It does not justify the specific merge keyed upsert action or its validation path: A repeated staging batch produces stable row counts and correctly updates changed addresses without duplicated business keys.
C:That option concerns stream cdc consumption, not the requirement in this case. It does not justify the specific merge keyed upsert action or its validation path: A repeated staging batch produces stable row counts and correctly updates changed addresses without duplicated business keys.
D:It does not justify the specific merge keyed upsert action or its validation path: A repeated staging batch produces stable row counts and correctly updates changed addresses without duplicated business keys.
Question 1034
[Query performance review] A performance engineer is benchmarking representative queries before accepting changes. A repeated large scan runs faster on the same warm warehouse, but the speedup disappears after the warehouse has been suspended. 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 every syntactically similar SELECT always reuses cached results even after source tables are modified.
- Treat the suspended warehouse's local cache as a permanent table copy that must remain available after shutdown.
- Treat the suspended warehouse's local cache as the authoritative durable copy of the data.
- Leave the warehouse running indefinitely to preserve tables, even when no compute-intensive activity remains.
Explanation
Correct Answer
B:The supportable direction is: Account for local warehouse data-cache loss across suspension, and test steady-state cost/performance without assuming persistence. The discriminating technical reason is: Warehouse cache is local compute storage and is not the durable persisted-query-result cache or underlying table storage.
Incorrect Answers
A: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 warehouse local data cache requirement, whose proper treatment is: Account for local warehouse data-cache loss across suspension, and test steady-state cost/performance without assuming persistence.
C:That is a mistake for a different durable database storage separation task, where An administrator suspends every reporting warehouse overnight and the operations manager fears that previously committed tables will be erased. Under query performance review conditions, Treat the suspended warehouse's local cache as the authoritative durable copy of the data.
D:Under query performance review conditions, Leave the warehouse running indefinitely to preserve tables, even when no compute-intensive activity remains.
Question 1035
[Post-rollout service review] The team proposes: Use an appropriate permanent table with supported Time Travel retention and understand its Fail-safe protection. Its actual requirement is: An operations group is reassessing the installed data platform after a representative workload trial. An audited production dataset requires recovery mechanisms beyond a short staging lifecycle, and accidental deletion is a realistic threat. The conclusion must be justified with observable verification rather than an undocumented assumption. Which observation most directly validates success?
- Account edition and the table's retention parameter demonstrate a supported value, and a within-window historical read succeeds.
- Keyed updates and constraint checks behave as designed under representative concurrent application traffic.
- Table type and retention settings match the policy and a controlled within-retention restoration test succeeds.
- A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys.
Explanation
Correct Answer
C:Table type and retention settings match the policy and a controlled within-retention restoration test succeeds. This is an outcome test of the specified permanent table recovery choice action under the post-rollout service review conditions. Permanent tables support Time Travel and a Fail-safe period; choosing transient storage would change recoverability guarantees.
Incorrect Answers
A:In this post-rollout service review context, Account edition and the table's retention parameter demonstrate a supported value, and a within-window historical read succeeds. This is an outcome for a different requirement: A risk team must retain a permanent table's recoverable history for more than one day and also wants enterprise governance capabilities. It cannot by itself validate the proposed permanent table recovery choice action; the appropriate test is: Table type and retention settings match the policy and a controlled within-retention restoration test succeeds.
B:In this post-rollout service review context, Keyed updates and constraint checks behave as designed under representative concurrent application traffic. This is an outcome for a different requirement: A customer application requires low-latency keyed row updates with enforced transactional constraints that fit a supported hybrid-table design. It cannot by itself validate the proposed permanent table recovery choice action; the appropriate test is: Table type and retention settings match the policy and a controlled within-retention restoration test succeeds.
D:A historical SELECT returns pre-change mapping values while the current SELECT returns the new values for the same keys. This is an outcome for a different requirement: 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 cannot by itself validate the proposed permanent table recovery choice action; the appropriate test is: Table type and retention settings match the policy and a controlled within-retention restoration test succeeds.
Question 1036
[Post-rollout service review] A reviewer recommends: Create a temporary table for the session-scoped scratch result and avoid relying on it after disconnect. The requirement remains: An operations group is reassessing the installed data platform after a representative workload trial. 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. The conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- Snowsight is Snowflake's graphical interface and surfaces SQL authoring and activity inspection based on the session's access.
- Possessing roles is distinct from activating them; Snowflake authorization depends on session role context and enabled secondary roles.
- The database storage layer persists committed table data independently from the lifecycle of virtual warehouse clusters.
- Temporary tables are session-scoped and suited to disposable intermediate data, unlike persistent shared tables.
Explanation
Correct Answer
D:In this post-rollout service review context, Temporary tables are session-scoped and suited to disposable intermediate data, unlike persistent shared tables. The recommendation is linked to observable evidence for this exact case: The scratch object is visible in its creator session and ceases to be a usable persistent dataset after the session ends. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:That option concerns snowsight analyst interface, not the requirement in this case. Snowsight is Snowflake's graphical interface and surfaces SQL authoring and activity inspection based on the session's access.
B:For the post-rollout service review conditions, Possessing roles is distinct from activating them; Snowflake authorization depends on session role context and enabled secondary roles. That explanation applies to a different primary and secondary role session problem: 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. It does not justify the specific temporary session-bound data action or its validation path: The scratch object is visible in its creator session and ceases to be a usable persistent dataset after the session ends.
C:For the post-rollout service review conditions, The database storage layer persists committed table data independently from the lifecycle of virtual warehouse clusters. That explanation applies to a different durable database storage separation problem: An administrator suspends every reporting warehouse overnight and the operations manager fears that previously committed tables will be erased. It does not justify the specific temporary session-bound data action or its validation path: The scratch object is visible in its creator session and ceases to be a usable persistent dataset after the session ends.
Question 1037
[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 specific design mistake would most directly defeat the intended result?
- Treat ACCOUNT_USAGE as a zero-latency feed and conclude recent events never occurred when rows are delayed.
- Duplicate broad ACCOUNTADMIN grants to every role and assume inheritance cannot be inspected.
- Write one object-storage CSV per row and assume ordinary hourly COPY INTO is a direct streaming interface.
- Send a static ACCOUNTADMIN password to every client and describe the process as OAuth delegation.
Explanation
Correct Answer
A:The discriminating technical reason is: Account usage views support historical auditing but can lag live activity; they are distinct from instant session state. The supportable direction is: Query appropriate SNOWFLAKE.ACCOUNT_USAGE views with awareness of documented ingestion latency and necessary privileges.
Incorrect Answers
B:Under identity and governance change request conditions, Duplicate broad ACCOUNTADMIN grants to every role and assume inheritance cannot be inspected. It does not directly defeat the present account_usage audit latency requirement, whose proper treatment is: Query appropriate SNOWFLAKE.ACCOUNT_USAGE views with awareness of documented ingestion latency and necessary privileges.
C:That is a mistake for a different snowpipe streaming low latency task, where An IoT product emits row events continuously and needs lower-latency ingestion than file-batched loading provides. Under identity and governance change request conditions, Write one object-storage CSV per row and assume ordinary hourly COPY INTO is a direct streaming interface.
D:It does not directly defeat the present account_usage audit latency requirement, whose proper treatment is: Query appropriate SNOWFLAKE.ACCOUNT_USAGE views with awareness of documented ingestion latency and necessary privileges.
Question 1038
[Query performance review] A reviewer recommends: Evaluate Search Optimization Service for the supported predicate pattern and account edition after reviewing query profile. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. An API workload repeatedly probes a huge fact table for single customer identifiers and needs fast selective lookup response. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Query Profile provides evidence about actual execution instead of assuming all slow SQL has the same cause.
- Search optimization maintains access paths for selective probes and is not the same as adding a multi-cluster warehouse for concurrency.
- Adding clusters addresses concurrent workload pressure, whereas making one cluster larger is not the same as increasing cluster count.
- Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster.
Explanation
Correct Answer
B:In this query performance review context, Search optimization maintains access paths for selective probes and is not the same as adding a multi-cluster warehouse for concurrency. The recommendation is linked to observable evidence for this exact case: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:For the query performance review conditions, Query Profile provides evidence about actual execution instead of assuming all slow SQL has the same cause. That explanation applies to a different query profile bottleneck localization problem: 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. It does not justify the specific search optimization selective lookup action or its validation path: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
C:For the query performance review conditions, Adding clusters addresses concurrent workload pressure, whereas making one cluster larger is not the same as increasing cluster count. That explanation applies to a different multi-cluster concurrency scaling problem: During a product launch dozens of short BI queries queue even though each individual query already finishes quickly once it starts. It does not justify the specific search optimization selective lookup action or its validation path: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
D:For the query performance review conditions, Scaling out adds clusters to handle concurrency, while scaling up changes resources for each individual cluster. That explanation applies to a different multi-cluster scale out versus scale up problem: Thirty concurrent dashboards have low individual runtimes but high wait times, and the account can use multi-cluster warehouses. It does not justify the specific search optimization selective lookup action or its validation path: The targeted equality-lookup workload becomes faster at acceptable maintenance cost while full scans are separately assessed.
Question 1039
[Collaboration operating audit] A platform owner must demonstrate that the proposed sharing model respects its access and recovery boundary. Two advertisers want to calculate joint audience overlap while preventing either company from freely inspecting the other's raw person-level records. The conclusion must be justified with observable verification rather than an undocumented assumption. Which specific design mistake would most directly defeat the intended result?
- Assume an external table is identical to a mutable standard Snowflake table with unrestricted DELETE support.
- Assume granting the consumer's account name in a remote region is always identical to a local share without any additional setup.
- Provide each participant unrestricted SELECT and export privileges on the other company's full customer table.
- Publish every regulated subsidiary dataset to an unrestricted public marketplace listing to approximate membership control.
Explanation
Correct Answer
C:The supportable direction is: Evaluate a Snowflake Data Clean Room with approved collaboration templates and privacy controls for the intended aggregate analysis. Provide each participant unrestricted SELECT and export privileges on the other company's full customer table.
Incorrect Answers
A:That is a mistake for a different external table over data lake task, where A data scientist needs governed SQL reads over files remaining in external object storage, without an ordinary Snowflake-managed load step. Under collaboration operating audit conditions, Assume an external table is identical to a mutable standard Snowflake table with unrestricted DELETE support.
B:Under collaboration operating audit conditions, Assume granting the consumer's account name in a remote region is always identical to a local share without any additional setup. That is a mistake for a different cross-region data listing fulfillment task, where A provider and a consumer operate in different supported regions and want catalog-based access without assuming a same-region direct share works unchanged. It does not directly defeat the present privacy-preserving data clean room requirement, whose proper treatment is: Evaluate a Snowflake Data Clean Room with approved collaboration templates and privacy controls for the intended aggregate analysis.
D:That is a mistake for a different private data exchange collaboration task, where Several subsidiaries must exchange curated datasets inside a controlled organizational community rather than list them publicly. Under collaboration operating audit conditions, Publish every regulated subsidiary dataset to an unrestricted public marketplace listing to approximate membership control.
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.