SnowPro Core COF-C03 Practice Test 71
Question 700
During a pilot, engineers pursued an unrelated workaround: Create the intended role hierarchy using role-to-role grants. The required outcome remained unsatisfied: A company already uses enterprise identity federation and wants employee Snowflake sign-ins tied to its central identity provider. Which correction should the team prioritize?
- Create a least-privileged custom account role with only the required database and object grants.
- Enforce the supported MFA and authentication policy for the target user population.
- Configure supported federated single sign-on with the organization's identity provider.
- Create the intended role hierarchy using role-to-role grants.
Explanation
Correct Answer
C:Federated SSO centralizes login assurance and deprovisioning for interactive employees. The corrective step addresses the missing requirement: A company already uses enterprise identity federation and wants employee Snowflake sign-ins tied to its central identity provider. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Snowflake RBAC places permissions on roles that are granted to users, avoiding broad system administration rights. That explains the role of “Create a least-privileged custom account role with only the required database and object grants”, but the present requirement calls for “Configure supported federated single sign-on with the organization's identity provider”: Federated SSO centralizes login assurance and deprovisioning for interactive employees.
B:MFA raises assurance beyond a single compromised password or reused secret. That explains the role of “Enforce the supported MFA and authentication policy for the target user population”, but the present requirement calls for “Configure supported federated single sign-on with the organization's identity provider”: Federated SSO centralizes login assurance and deprovisioning for interactive employees.
D:Role inheritance supplies lower-role privileges to authorized higher roles without copying every object grant manually. That explains the role of “Create the intended role hierarchy using role-to-role grants”, but the present requirement calls for “Configure supported federated single sign-on with the organization's identity provider”: Federated SSO centralizes login assurance and deprovisioning for interactive employees.
Question 701
During a pilot, engineers pursued an unrelated workaround: Capture supported METADATA$ filename and load timestamp information during staging ingestion. The required outcome remained unsatisfied: A batch load should continue with valid files while collecting rejected-row details under an agreed error policy. Which correction should the team prioritize?
- Choose an appropriate COPY INTO ON_ERROR setting and inspect load results.
- Configure the CSV file format with the correct delimiter and enclosure options.
- Capture supported METADATA$ filename and load timestamp information during staging ingestion.
- Define a JSON file format and load records into a VARIANT-capable table layout.
Explanation
Correct Answer
A:COPY loading error behavior is configurable; silent skipping without review can hide data loss. The corrective step addresses the missing requirement: A batch load should continue with valid files while collecting rejected-row details under an agreed error policy. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
B:File-format parsing rules must match real source encoding and quote conventions. That explains the role of “Configure the CSV file format with the correct delimiter and enclosure options”, but the present requirement calls for “Choose an appropriate COPY INTO ON_ERROR setting and inspect load results”: COPY loading error behavior is configurable; silent skipping without review can hide data loss.
C:Stage metadata pseudo-columns can support provenance without depending on source columns containing file identifiers. That explains the role of “Capture supported METADATA$ filename and load timestamp information during staging ingestion”, but the present requirement calls for “Choose an appropriate COPY INTO ON_ERROR setting and inspect load results”: COPY loading error behavior is configurable; silent skipping without review can hide data loss.
D:JSON loading should preserve nested structure instead of assuming fixed CSV column positions. That explains the role of “Define a JSON file format and load records into a VARIANT-capable table layout”, but the present requirement calls for “Choose an appropriate COPY INTO ON_ERROR setting and inspect load results”: COPY loading error behavior is configurable; silent skipping without review can hide data loss.
Question 702
During a pilot, engineers pursued an unrelated workaround: Inspect partition pruning and consider appropriate clustering of the filter key for a large table. The required outcome remained unsatisfied: An ETL query spills gigabytes to remote storage during a hash join. Which correction should the team prioritize?
- Inspect join cardinality, join keys and query plan before simply adding compute.
- Inspect partition pruning and consider appropriate clustering of the filter key for a large table.
- Compare search optimization and targeted materialized views against actual filter patterns.
- Inspect Query Profile spill indicators and evaluate warehouse memory or SQL join strategy.
Explanation
Correct Answer
D:Large intermediate joins that exceed memory can create costly remote spill and longer runtimes. The corrective step addresses the missing requirement: An ETL query spills gigabytes to remote storage during a hash join. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Incorrect join predicates can multiply intermediate rows, causing poor performance regardless of warehouse size. That explains the role of “Inspect join cardinality, join keys and query plan before simply adding compute”, but the present requirement calls for “Inspect Query Profile spill indicators and evaluate warehouse memory or SQL join strategy”: Large intermediate joins that exceed memory can create costly remote spill and longer runtimes.
B:Poor data locality can force scans of irrelevant partitions even for selective predicates. That explains the role of “Inspect partition pruning and consider appropriate clustering of the filter key for a large table”, but the present requirement calls for “Inspect Query Profile spill indicators and evaluate warehouse memory or SQL join strategy”: Large intermediate joins that exceed memory can create costly remote spill and longer runtimes.
C:Clustering optimizes data locality for one key expression, while other services can target distinct access paths. That explains the role of “Compare search optimization and targeted materialized views against actual filter patterns”, but the present requirement calls for “Inspect Query Profile spill indicators and evaluate warehouse memory or SQL join strategy”: Large intermediate joins that exceed memory can create costly remote spill and longer runtimes.
Question 703
During a pilot, engineers pursued an unrelated workaround: Explain that Fail-safe is Snowflake-assisted disaster recovery, not a user-queryable historical archive. The required outcome remained unsatisfied: An application should retrieve the historical state from a particular Snowflake statement instead of estimating wall-clock seconds. Which correction should the team prioritize?
- Use Time Travel AT or BEFORE with an appropriate statement identifier where supported.
- Explain that Fail-safe is Snowflake-assisted disaster recovery, not a user-queryable historical archive.
- Use a transient table only after approving its limited data-recovery guarantees.
- Use supported UNDROP SCHEMA within the configured Time Travel window.
Explanation
Correct Answer
A:Statement-based history offers precise temporal reference points for eligible Snowflake objects. The corrective step addresses the missing requirement: An application should retrieve the historical state from a particular Snowflake statement instead of estimating wall-clock seconds. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
B:Fail-safe is a limited recovery layer after Time Travel for eligible permanent tables, not direct SQL Time Travel. That explains the role of “Explain that Fail-safe is Snowflake-assisted disaster recovery, not a user-queryable historical archive”, but the present requirement calls for “Use Time Travel AT or BEFORE with an appropriate statement identifier where supported”: Statement-based history offers precise temporal reference points for eligible Snowflake objects.
C:Transient tables omit Fail-safe and differ from permanent tables in their recovery characteristics. That explains the role of “Use a transient table only after approving its limited data-recovery guarantees”, but the present requirement calls for “Use Time Travel AT or BEFORE with an appropriate statement identifier where supported”: Statement-based history offers precise temporal reference points for eligible Snowflake objects.
D:Time Travel object recovery can restore eligible dropped schema structures while retention is valid. That explains the role of “Use supported UNDROP SCHEMA within the configured Time Travel window”, but the present requirement calls for “Use Time Travel AT or BEFORE with an appropriate statement identifier where supported”: Statement-based history offers precise temporal reference points for eligible Snowflake objects.
Question 704
During a pilot, engineers pursued an unrelated workaround: Assign each workload its own virtual warehouse while retaining the common tables. The required outcome remained unsatisfied: A new analytics project needs scalable ingestion, SQL transformation and reporting with consistently enforced access restrictions. Which correction should the team prioritize?
- Keep central Snowflake-managed database storage while running the teams on independent virtual warehouses.
- Assign each workload its own virtual warehouse while retaining the common tables.
- Design one Snowflake platform workflow with staged ingestion, transform compute, and role-governed datasets.
- Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region.
Explanation
Correct Answer
C:The platform combines managed storage and compute with authentication and object privileges across its data lifecycle. The corrective step addresses the missing requirement: A new analytics project needs scalable ingestion, SQL transformation and reporting with consistently enforced access restrictions. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Snowflake decouples shared persisted data from workload compute, allowing isolated resource allocation over one controlled source of truth. That explains the role of “Keep central Snowflake-managed database storage while running the teams on independent virtual warehouses”, but the present requirement calls for “Design one Snowflake platform workflow with staged ingestion, transform compute, and role-governed datasets”: The platform combines managed storage and compute with authentication and object privileges across its data lifecycle.
B:Independent warehouses isolate compute usage and query queues even though both read the same storage objects. That explains the role of “Assign each workload its own virtual warehouse while retaining the common tables”, but the present requirement calls for “Design one Snowflake platform workflow with staged ingestion, transform compute, and role-governed datasets”: The platform combines managed storage and compute with authentication and object privileges across its data lifecycle.
D:Capabilities and security assurances can differ by edition, making an unverified default subscription an unsafe assumption. That explains the role of “Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region”, but the present requirement calls for “Design one Snowflake platform workflow with staged ingestion, transform compute, and role-governed datasets”: The platform combines managed storage and compute with authentication and object privileges across its data lifecycle.
Question 705
During a pilot, engineers pursued an unrelated workaround: Inspect CURRENT_ROLE and effective primary or secondary role grants for the session. The required outcome remained unsatisfied: Security wants a second authentication factor for interactive account access. Which correction should the team prioritize?
- Inspect CURRENT_ROLE and effective primary or secondary role grants for the session.
- Grant USAGE and SELECT through reader roles while limiting MANAGE GRANTS and ownership authority.
- Enforce the supported MFA and authentication policy for the target user population.
- Correct the Snowflake role inheritance direction using explicit GRANT ROLE relationships.
Explanation
Correct Answer
C:MFA raises assurance beyond a single compromised password or reused secret. The corrective step addresses the missing requirement: Security wants a second authentication factor for interactive account access. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:The active session role context determines which object privileges are available. That explains the role of “Inspect CURRENT_ROLE and effective primary or secondary role grants for the session”, but the present requirement calls for “Enforce the supported MFA and authentication policy for the target user population”: MFA raises assurance beyond a single compromised password or reused secret.
B:Read-only business access should be separated from grant management and object modification privileges. That explains the role of “Grant USAGE and SELECT through reader roles while limiting MANAGE GRANTS and ownership authority”, but the present requirement calls for “Enforce the supported MFA and authentication policy for the target user population”: MFA raises assurance beyond a single compromised password or reused secret.
D:Privilege flow follows the role hierarchy and cannot be inferred from role names alone. That explains the role of “Correct the Snowflake role inheritance direction using explicit GRANT ROLE relationships”, but the present requirement calls for “Enforce the supported MFA and authentication policy for the target user population”: MFA raises assurance beyond a single compromised password or reused secret.
Question 706
During a pilot, engineers pursued an unrelated workaround: Use COPY INTO a target table from a configured external stage and CSV file format. The required outcome remained unsatisfied: A daily batch rerun reuses the same file names and unexpectedly does not reload previously ingested data. Which correction should the team prioritize?
- Inspect Snowflake load metadata and avoid forcing duplicates unless intentional.
- Define the correct PARQUET file format and compatible target column mapping.
- Use LIST on the intended stage and file path with appropriate privileges.
- Use COPY INTO a target table from a configured external stage and CSV file format.
Explanation
Correct Answer
A:COPY uses file load metadata to prevent ordinary repeat loads; deliberate reload policies require safeguards. The corrective step addresses the missing requirement: A daily batch rerun reuses the same file names and unexpectedly does not reload previously ingested data. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
B:Parquet carries structured column data and should not be parsed as plain delimited text. That explains the role of “Define the correct PARQUET file format and compatible target column mapping”, but the present requirement calls for “Inspect Snowflake load metadata and avoid forcing duplicates unless intentional”: COPY uses file load metadata to prevent ordinary repeat loads; deliberate reload policies require safeguards.
C:Listing stage contents validates location, pattern and access independently of a COPY operation. That explains the role of “Use LIST on the intended stage and file path with appropriate privileges”, but the present requirement calls for “Inspect Snowflake load metadata and avoid forcing duplicates unless intentional”: COPY uses file load metadata to prevent ordinary repeat loads; deliberate reload policies require safeguards.
D:Bulk COPY INTO uses a specified warehouse to load staged files into Snowflake tables in batches. That explains the role of “Use COPY INTO a target table from a configured external stage and CSV file format”, but the present requirement calls for “Inspect Snowflake load metadata and avoid forcing duplicates unless intentional”: COPY uses file load metadata to prevent ordinary repeat loads; deliberate reload policies require safeguards.
Question 707
During a pilot, engineers pursued an unrelated workaround: Prioritize clustering only where measured pruning gains justify reclustering costs. The required outcome remained unsatisfied: A BI warehouse receives hundreds of concurrent short SELECT statements and queue time dominates response latency. Which correction should the team prioritize?
- Compare search optimization and targeted materialized views against actual filter patterns.
- Inspect the Snowsight Query Profile and operator-level runtime statistics.
- Prioritize clustering only where measured pruning gains justify reclustering costs.
- Scale out with an appropriate multi-cluster warehouse rather than only increasing one cluster's size.
Explanation
Correct Answer
D:Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources. The corrective step addresses the missing requirement: A BI warehouse receives hundreds of concurrent short SELECT statements and queue time dominates response latency. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Clustering optimizes data locality for one key expression, while other services can target distinct access paths. That explains the role of “Compare search optimization and targeted materialized views against actual filter patterns”, but the present requirement calls for “Scale out with an appropriate multi-cluster warehouse rather than only increasing one cluster's size”: Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources.
B:Query Profile reveals scans, joins, aggregations, spill and data movement within execution plans. That explains the role of “Inspect the Snowsight Query Profile and operator-level runtime statistics”, but the present requirement calls for “Scale out with an appropriate multi-cluster warehouse rather than only increasing one cluster's size”: Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources.
C:Reclustering creates maintenance compute and storage overhead and does not improve every query. That explains the role of “Prioritize clustering only where measured pruning gains justify reclustering costs”, but the present requirement calls for “Scale out with an appropriate multi-cluster warehouse rather than only increasing one cluster's size”: Concurrency contention is addressed by more parallel clusters, while scale-up primarily improves individual query resources.
Question 708
During a pilot, engineers pursued an unrelated workaround: Use UNDROP TABLE under appropriate privileges within the Time Travel recovery window. The required outcome remained unsatisfied: An operator assumes deleting a permanent table will instantly remove all stored historical bytes from Snowflake billing. Which correction should the team prioritize?
- Review the Time Travel and Fail-safe retention windows and storage effects.
- Use a Time Travel query with an appropriate AT or BEFORE timestamp or statement marker.
- Use UNDROP TABLE under appropriate privileges within the Time Travel recovery window.
- Configure eligible extended Time Travel retention up to the edition's supported maximum.
Explanation
Correct Answer
A:Historical storage can remain recoverable or billable according to the table type and retention rules. The corrective step addresses the missing requirement: An operator assumes deleting a permanent table will instantly remove all stored historical bytes from Snowflake billing. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
B:Verify its use in the approved role and runtime context. That explains the role of “Use a Time Travel query with an appropriate AT or BEFORE timestamp or statement marker”, but the present requirement calls for “Review the Time Travel and Fail-safe retention windows and storage effects”: Historical storage can remain recoverable or billable according to the table type and retention rules.
C:Time Travel supports restoration of recently dropped eligible objects while their retention remains valid. That explains the role of “Use UNDROP TABLE under appropriate privileges within the Time Travel recovery window”, but the present requirement calls for “Review the Time Travel and Fail-safe retention windows and storage effects”: Historical storage can remain recoverable or billable according to the table type and retention rules.
D:Extended retention may require Enterprise Edition or higher and incurs storage tradeoffs. That explains the role of “Configure eligible extended Time Travel retention up to the edition's supported maximum”, but the present requirement calls for “Review the Time Travel and Fail-safe retention windows and storage effects”: Historical storage can remain recoverable or billable according to the table type and retention rules.
Question 709
During a pilot, engineers pursued an unrelated workaround: Correct the design to use independent warehouses reading shared Snowflake tables. The required outcome remained unsatisfied: An architect wants an accountable method to compare service editions before promising a regulated data feature to stakeholders. Which correction should the team prioritize?
- Assign each workload its own virtual warehouse while retaining the common tables.
- Keep central Snowflake-managed database storage while running the teams on independent virtual warehouses.
- Correct the design to use independent warehouses reading shared Snowflake tables.
- Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region.
Explanation
Correct Answer
D:Capabilities and security assurances can differ by edition, making an unverified default subscription an unsafe assumption. The corrective step addresses the missing requirement: An architect wants an accountable method to compare service editions before promising a regulated data feature to stakeholders. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Independent warehouses isolate compute usage and query queues even though both read the same storage objects. That explains the role of “Assign each workload its own virtual warehouse while retaining the common tables”, but the present requirement calls for “Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region”: Capabilities and security assurances can differ by edition, making an unverified default subscription an unsafe assumption.
B:Snowflake decouples shared persisted data from workload compute, allowing isolated resource allocation over one controlled source of truth. That explains the role of “Keep central Snowflake-managed database storage while running the teams on independent virtual warehouses”, but the present requirement calls for “Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region”: Capabilities and security assurances can differ by edition, making an unverified default subscription an unsafe assumption.
C:Warehouse separation is a compute isolation strategy, not a requirement to duplicate each underlying table. That explains the role of “Correct the design to use independent warehouses reading shared Snowflake tables”, but the present requirement calls for “Check feature availability and compliance requirements against the chosen Snowflake edition and deployment region”: Capabilities and security assurances can differ by edition, making an unverified default subscription an unsafe assumption.
Question 710
During a pilot, engineers pursued an unrelated workaround: Configure Snowflake-supported key-pair or approved token authentication and rotate credentials securely. The required outcome remained unsatisfied: A Snowflake account should accept application connections only from approved office egress IP addresses. Which correction should the team prioritize?
- Inspect Snowflake access and query history using properly privileged monitoring views.
- Apply a tested Snowflake network policy with narrowly scoped allowed IP ranges.
- Configure Snowflake-supported key-pair or approved token authentication and rotate credentials securely.
- Revoke their assigned access roles or object grants through the controlled role hierarchy.
Explanation
Correct Answer
B:Network policies constrain permitted source addresses independently from SQL object grants. The corrective step addresses the missing requirement: A Snowflake account should accept application connections only from approved office egress IP addresses. A different Snowflake feature would not directly resolve this failure.
Incorrect Answers
A:Account activity history connects query operations to principals and execution context for investigation. That explains the role of “Inspect Snowflake access and query history using properly privileged monitoring views”, but the present requirement calls for “Apply a tested Snowflake network policy with narrowly scoped allowed IP ranges”: Network policies constrain permitted source addresses independently from SQL object grants.
C:Non-password authentication reduces secrets exposure and supports controlled service identity lifecycle. That explains the role of “Configure Snowflake-supported key-pair or approved token authentication and rotate credentials securely”, but the present requirement calls for “Apply a tested Snowflake network policy with narrowly scoped allowed IP ranges”: Network policies constrain permitted source addresses independently from SQL object grants.
D:Authorization revocation separates identity lifecycle from destructive changes to stored data. That explains the role of “Revoke their assigned access roles or object grants through the controlled role hierarchy”, but the present requirement calls for “Apply a tested Snowflake network policy with narrowly scoped allowed IP ranges”: Network policies constrain permitted source addresses independently from SQL object grants.