Snowflake SnowPro Core COF-C03 Practice Test 97
Question 960
[Production compliance review] A compliance reviewer is checking which safeguard actually enforces the stated boundary. Compliance teams reconcile last week's credit spending and access history across the account and need consistent long-range audit views. The conclusion must be justified with observable verification rather than an undocumented assumption. Which Snowflake action best meets this specific requirement?
- Configure an appropriate OAuth authorization flow and token scopes along with Snowflake role privileges.
- Inspect the active primary role and secondary-role configuration rather than assuming all granted roles participate in each statement.
- Evaluate the current supported Snowpipe Streaming SDK or high-performance architecture with an appropriate channel design.
- 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 production compliance review setting, the intended validation is: Historical usage matches billed and recorded account activity after the documented latency window has elapsed. The answer targets the described requirement rather than an unrelated feature.
Incorrect Answers
A:That action is relevant when A managed business application requires scoped delegated Snowflake access on behalf of an authorized person without collecting their database password. Account usage views support historical auditing but can lag live activity; they are distinct from instant session state.
B:That action is relevant when 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. Account usage views support historical auditing but can lag live activity; they are distinct from instant session state.
C:That action is relevant when An IoT product emits row events continuously and needs lower-latency ingestion than file-batched loading provides. It is not the primary action for the stated account_usage audit latency need.
Question 961
[Peak traffic performance incident] A reviewer recommends: Evaluate Search Optimization Service for the supported predicate pattern and account edition after reviewing query profile. The requirement remains: A BI engineer is investigating the exact bottleneck observed under production-like load. An API workload repeatedly probes a huge fact table for single customer identifiers and needs fast selective lookup response. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which 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.
- Partition metadata lets Snowflake skip micro-partitions whose ranges cannot satisfy the filter, but scattered values limit effective pruning.
- Query Profile provides evidence about actual execution instead of assuming all slow SQL has the same cause.
- Query Acceleration Service can offload eligible query components to shared compute; it does not replace base warehouse execution for every operation.
Explanation
Correct Answer
A:In this peak traffic performance incident 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
B: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:That option concerns query profile bottleneck localization, not the requirement in this case. 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:That option concerns query acceleration service suitability, not the requirement in this case. 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 962
[Post-rollout service review] An operations group is reassessing the installed data platform after a representative workload trial. A risk team must retain a permanent table's recoverable history for more than one day and also wants enterprise governance capabilities. The conclusion must be justified with observable verification rather than an undocumented assumption. Which Snowflake action best meets this specific requirement?
- Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY.
- Confirm that the account edition and object support the requested extended Time Travel retention, then set a supported retention period.
- Use the account, database, schema, and object hierarchy and reference the table using database.schema.table where appropriate.
- 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:Extended retention for permanent objects is edition-dependent; selecting an appropriate edition precedes setting a longer recoverability window. In this post-rollout service review setting, the intended validation is: Account edition and the table's retention parameter demonstrate a supported value, and a within-window historical read succeeds.
Incorrect Answers
A:That action is relevant when A table contains several rows per customer and an analyst wants exactly the latest row for each customer after computing ROW_NUMBER. Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY.
C:That action is relevant when A developer tries to locate a table by naming a warehouse as its parent and cannot determine where the object should be created. Extended retention for permanent objects is edition-dependent; selecting an appropriate edition precedes setting a longer recoverability window.
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. Extended retention for permanent objects is edition-dependent; selecting an appropriate edition precedes setting a longer recoverability window.
Question 963
[Query performance review] A reviewer recommends: Evaluate Query Acceleration Service eligibility, configuration, and Query Profile signals for the actual workload. The requirement remains: A performance engineer is benchmarking representative queries before accepting changes. Analytical reports experience sporadic expensive, selective scan work that can benefit from offloading qualifying fragments. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- A concurrency bottleneck is principally about simultaneous slots rather than the CPU needs of one query.
- A single heavyweight query's memory and compute needs are a scale-up or query-tuning problem, not inherently a scale-out problem.
- Query Acceleration Service can offload eligible query components to shared compute; it does not replace base warehouse execution for every operation.
- Materialized views persist eligible precomputed results, reducing repeated calculation at the expense of refresh storage and compute.
Explanation
Correct Answer
C:In this query performance review context, Query Acceleration Service can offload eligible query components to shared compute; it does not replace base warehouse execution for every operation. The recommendation is linked to observable evidence for this exact case: Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:For the query performance review conditions, A concurrency bottleneck is principally about simultaneous slots rather than the CPU needs of one query. That explanation applies to a different query queueing load analysis problem: A business-intelligence warehouse serves hundreds of short requests and most end-user delay occurs before the queries actually start. It does not justify the specific query acceleration service suitability action or its validation path: Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost.
B:For the query performance review conditions, A single heavyweight query's memory and compute needs are a scale-up or query-tuning problem, not inherently a scale-out problem. That explanation applies to a different warehouse scale-up for complex query problem: A warehouse consistently spills intermediate data on a single memory-intensive analytical join while the queue length is close to zero. It does not justify the specific query acceleration service suitability action or its validation path: Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost.
D:Materialized views persist eligible precomputed results, reducing repeated calculation at the expense of refresh storage and compute.
Question 964
[Production compliance review] A compliance reviewer is checking which safeguard actually enforces the stated boundary. A data platform security assessment finds interactive users have weak password-only sign-in and require a stronger authentication factor. The conclusion must be justified with observable verification rather than an undocumented assumption. Which specific design mistake would most directly defeat the intended result?
- Grant USAGE on a warehouse and assume that alone restricts the connecting client's source IP address.
- Increase warehouse size repeatedly without checking role grants or the denied object privilege.
- Store an administrator's plaintext password in every CI job and assume a network policy makes it equivalent to key authentication.
- Set a masking policy on a table column and assume that supplies a second authentication factor at login.
Explanation
Correct Answer
D:The discriminating technical reason is: MFA protects interactive sign-in beyond knowledge of a single password; it is separate from table permissions. Set a masking policy on a table column and assume that supplies a second authentication factor at login.
Incorrect Answers
A:That is a mistake for a different network policy address restrictions task, where An organization must restrict logins to approved corporate egress ranges even when credentials are correct. It does not directly defeat the present multifactor authentication enrollment requirement, whose proper treatment is: Enforce and verify appropriate Snowflake MFA or federated IdP MFA controls according to the active policy.
B:Under production compliance review conditions, Increase warehouse size repeatedly without checking role grants or the denied object privilege.
C:That is a mistake for a different workload identity key-pair authentication task, where An unattended ingestion service needs noninteractive authentication without embedding an employee's reusable password into scripts. It does not directly defeat the present multifactor authentication enrollment requirement, whose proper treatment is: Enforce and verify appropriate Snowflake MFA or federated IdP MFA controls according to the active policy.
Question 965
[Post-rollout service review] A reviewer recommends: Use Snowflake Time Travel AT or BEFORE syntax with a valid historical reference for the affected table. The requirement remains: An operations group is reassessing the installed data platform after a representative workload trial. 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 conclusion must be justified with observable verification rather than an undocumented assumption. Which explanation best justifies this recommendation?
- Time Travel queries retained historical table versions during configured retention; the latest table state does not replace accessible history immediately.
- A single heavyweight query's memory and compute needs are a scale-up or query-tuning problem, not inherently a scale-out problem.
- Dynamic tables express desired query results declaratively and Snowflake orchestrates refresh instead of requiring a bespoke stream/task graph for every stage.
- Time Travel supports self-service historical access during configured retention; Fail-safe is not an ordinary user-queryable history store.
Explanation
Correct Answer
A:In this post-rollout service review 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
B:For the post-rollout service review conditions, A single heavyweight query's memory and compute needs are a scale-up or query-tuning problem, not inherently a scale-out problem. That explanation applies to a different warehouse scale-up for complex query problem: A warehouse consistently spills intermediate data on a single memory-intensive analytical join while the queue length is close to zero. 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.
C:For the post-rollout service review 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.
D:For the post-rollout service review conditions, Time Travel supports self-service historical access during configured retention; Fail-safe is not an ordinary user-queryable history store. That explanation applies to a different time travel versus fail-safe recoverability problem: 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 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 966
[Ingestion design acceptance] A data engineering team is checking the design before promoting a pipeline. A 20GB batch contains uncertain date strings and the operator needs to discover load errors without committing partially incorrect target records. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which Snowflake action best meets this specific requirement?
- Use Snowflake's load metadata/history and carefully controlled COPY retry behavior to identify already ingested files.
- Run COPY INTO with an appropriate VALIDATION_MODE or targeted preflight to inspect errors before committing the production load.
- Use zero-copy cloning for an authorized internal copy and distinguish it from a cross-account secure share.
- Use COPY INTO a stage or external location from the approved SELECT with an explicit supported file format.
Explanation
Correct Answer
B:Validation mode checks parsing and load conditions for supported files without making the ordinary production table mutation. In this ingestion design acceptance setting, the intended validation is: Validation output identifies the same malformed records that can be corrected before the final committed load.
Incorrect Answers
A: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. It is not the primary action for the stated copy into validation preflight need.
C:That action is relevant when A data owner must provide its internal analytics team with an independently modifiable copy of a large dataset for an experiment. It is not the primary action for the stated copy into validation preflight need.
D:That action is relevant when A downstream finance application requires an export of curated query results to an approved cloud storage path for a partner batch job. It is not the primary action for the stated copy into validation preflight need.
Question 967
[Peak traffic performance incident] A BI engineer is investigating the exact bottleneck observed under production-like load. Several analytics teams need the same small deterministic transformation that returns one scalar value and is naturally expressed in SQL. The release needs an operationally defensible choice without accidentally changing unrelated access or data. Which Snowflake action best meets this specific requirement?
- Use ROW_NUMBER with PARTITION BY and an ordered window, then filter the window result with QUALIFY.
- Specify a deterministic secondary ordering key in ROW_NUMBER or another suitable window ranking expression.
- Create a scoped SQL user-defined function and grant the appropriate usage privileges to consuming roles.
- Open the query's profile and compare execution operators, partitions scanned, bytes processed, and spill indicators before tuning.
Explanation
Correct Answer
C:A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row. In this peak traffic performance incident setting, the intended validation is: SELECT using the function gives the same result as the underlying expression for a boundary-value test set.
Incorrect Answers
A:That action is relevant when A table contains several rows per customer and an analyst wants exactly the latest row for each customer after computing ROW_NUMBER. A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row.
B:That action is relevant when A churn dataset has multiple events with identical timestamps per customer, and selecting the latest without a tie-breaker gives inconsistent winners. It is not the primary action for the stated sql udf reusable expression need.
D:That action is relevant when 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. A SQL UDF encapsulates reusable expression logic, whereas a scheduled task is meant for orchestration, not a scalar call per row.
Question 968
[Ingestion design acceptance] A reviewer recommends: Evaluate the current supported Snowpipe Streaming SDK or high-performance architecture with an appropriate channel design. The requirement remains: A data engineering team is checking the design before promoting a pipeline. An IoT product emits row events continuously and needs lower-latency ingestion than file-batched loading provides. The reviewer must choose a supported mechanism for the precise requirement, not a generic product feature. Which explanation best justifies this recommendation?
- Hybrid tables support row-oriented transactional use cases that differ from purely analytical columnar standard tables.
- Snowpipe uses Snowflake-managed compute to ingest staged files automatically, distinct from warehouse-driven scheduled COPY batches.
- A file format controls how COPY parses file bytes; mismatched delimiter and quoting rules cause shifted or rejected columns.
- Snowpipe Streaming ingests rows via streaming APIs without requiring each small batch to first become a staged cloud file.
Explanation
Correct Answer
D:In this ingestion design acceptance context, Snowpipe Streaming ingests rows via streaming APIs without requiring each small batch to first become a staged cloud file. The recommendation is linked to observable evidence for this exact case: End-to-end event timestamps show data arriving within the required streaming latency under peak event load. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:Hybrid tables support row-oriented transactional use cases that differ from purely analytical columnar standard tables.
B:That option concerns snowpipe file event auto ingest, not the requirement in this case. That explanation applies to a different snowpipe file event auto ingest problem: Incoming small object-storage files appear several times per hour and should load automatically soon after the object store sends events.
C:For the ingestion design acceptance conditions, A file format controls how COPY parses file bytes; mismatched delimiter and quoting rules cause shifted or rejected columns. That explanation applies to a different reusable csv file format problem: 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 does not justify the specific snowpipe streaming low latency action or its validation path: End-to-end event timestamps show data arriving within the required streaming latency under peak event load.
Question 969
[Optimization regression test] The team proposes: Open the query's profile and compare execution operators, partitions scanned, bytes processed, and spill indicators before tuning. Its actual requirement is: 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 observation most directly validates success?
- A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
- Remote spill bytes and elapsed time decrease on a representative workload after the targeted change.
- Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size.
- Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost.
Explanation
Correct Answer
A:A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data. This is an outcome test of the specified query profile bottleneck localization action under the optimization regression test conditions. Query Profile provides evidence about actual execution instead of assuming all slow SQL has the same cause.
Incorrect Answers
B:In this optimization regression test context, Remote spill bytes and elapsed time decrease on a representative workload after the targeted change. This is an outcome for a different requirement: A large GROUP BY and join spills extensively to remote storage and takes much longer than expected even without query queueing. It cannot by itself validate the proposed query profile bottleneck localization action; the appropriate test is: A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
C:Comparable runs show a meaningful runtime improvement and an acceptable compute cost for the chosen warehouse size. This is an outcome for a different requirement: A complicated analytical transform has predictable memory pressure, but management wants evidence before raising per-second warehouse consumption. It cannot by itself validate the proposed query profile bottleneck localization action; the appropriate test is: A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
D:In this optimization regression test context, Query insights and billing show qualifying accelerated query work with net latency benefit at an acceptable credit cost. This is an outcome for a different requirement: Analytical reports experience sporadic expensive, selective scan work that can benefit from offloading qualifying fragments. It cannot by itself validate the proposed query profile bottleneck localization action; the appropriate test is: A before-and-after profile identifies the operator that improved and shows lower elapsed execution time on comparable data.
Question 970
[Peak traffic performance incident] A reviewer recommends: Inspect persisted query result cache eligibility, deterministic SQL, underlying data changes, and result reuse settings. The requirement remains: 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 explanation best justifies this recommendation?
- QUALIFY filters the results of window functions in Snowflake and is suited to post-window row selection.
- Result reuse depends on strict eligibility and unchanged relevant data; it is distinct from local warehouse data-cache reuse.
- Query Acceleration Service can offload eligible query components to shared compute; it does not replace base warehouse execution for every operation.
- Warehouses isolate compute and cache resources, so the two teams can query the same persisted data without competing for one active warehouse.
Explanation
Correct Answer
B:In this peak traffic performance incident context, Result reuse depends on strict eligibility and unchanged relevant data; it is distinct from local warehouse data-cache reuse. The recommendation is linked to observable evidence for this exact case: Two eligible unchanged executions show a reuse indicator, while changed data causes fresh evaluation under documented conditions. Merely choosing a nearby feature would not establish the needed capability.
Incorrect Answers
A:It does not justify the specific persisted result reuse conditions action or its validation path: Two eligible unchanged executions show a reuse indicator, while changed data causes fresh evaluation under documented conditions.
C:That option concerns query acceleration service suitability, not the requirement in this case. It does not justify the specific persisted result reuse conditions action or its validation path: Two eligible unchanged executions show a reuse indicator, while changed data causes fresh evaluation under documented conditions.
D:Warehouses isolate compute and cache resources, so the two teams can query the same persisted data without competing for one active warehouse.