COF-C03 Practice Test 2 — 10 Questions

Question 1

Which Snowflake feature allows a user to retrieve the results of a query that was executed 10 minutes ago without consuming additional virtual warehouse credits?

Accepted Answer

Result Set Cache. The Result Set Cache stores the results of queries for 24 hours. If the same query is executed again, the underlying data has not changed, and the query meets certain criteria, Snowflake returns the result directly from the cache. This bypasses the virtual warehouse, resulting in faster response times and zero compute cost.

Answer

Metadata Cache

Answer

Virtual Warehouse Local Cache

Answer

Search Optimization Service

Question 2

Which of the following describes the purpose of 'Time Travel' from a data governance perspective?

Accepted Answer

To provide a mechanism for restoring deleted or altered data.. Time Travel allows for the recovery of data that was accidentally deleted or modified, serving as a critical safety net for data governance. By enabling the retention of historical data, Snowflake empowers administrators to restore states after human error, minimizing data loss and operational downtime. This functionality is essential for maintaining data integrity and business continuity, ensuring that the organization can reliably recover from unintended data lifecycle events without needing to restore from full backups.

Answer

To improve query performance by caching historical results.

Answer

To hide sensitive data from users who do not have access.

Answer

To manage the lifecycle of virtual warehouses automatically.

Question 3

An organization requires that specific sensitive columns in a table be masked for all users except those in the 'DATA_STEWARD' role. Which mechanism should the architect implement to enforce this policy efficiently?

Accepted Answer

Apply a masking policy using the IS_ROLE_IN_SESSION function.. Dynamic Data Masking (DDM) provides a centralized way to protect sensitive data by applying masking policies to columns. By using the IS_ROLE_IN_SESSION function within the policy, the system evaluates the user's active role dynamically during query execution. This ensures that only members of the DATA_STEWARD role see unmasked data, while others see the masked output, maintaining governance consistency without needing to physically alter the underlying data storage or create multiple filtered views.

Answer

Apply a row-level security policy to the table.

Answer

Create secure views that use a CASE statement to filter columns.

Answer

Use data replication to create a separate table for stewards.

Question 4

A provider's account is named PROVIDER_ACCT and it has created a share named PARTNER_SHARE that already contains a secure view. The provider now runs: ALTER SHARE PARTNER_SHARE ADD ACCOUNTS = CONSUMER_ACCT; What is the effect of this command in the provider's environment?

Accepted Answer

It grants CONSUMER_ACCT the ability to read the share, but a database must still be created in CONSUMER_ACCT before any shared data is visible there.. A share is only an authorization container; adding a consumer account simply permits that account to see an inbound share. Consumers must then create a database from the share with a role that has CREATE DATABASE before any tables become queryable. The provider cannot perform that step on the consumer's behalf, which is why the data is not instantly usable after the ALTER SHARE command.

Answer

It fails because the ADD ACCOUNTS clause is only valid at share creation time and cannot be used on an existing share.

Answer

It immediately creates a read-only database in CONSUMER_ACCT containing all objects in the share, usable without any further consumer action.

Answer

It converts the share into a listing that is publicly discoverable on the Snowflake Marketplace by all Snowflake accounts.

Question 5

Which Snowflake feature provides an 'always-on' mechanism to allow users to instantly query the state of data as it existed at any point in the past within a defined retention period?

Accepted Answer

Time Travel. Time Travel is a core Snowflake feature that maintains historical data states, allowing users to query data as it existed at a specific timestamp or offset. This feature is vital for data recovery, auditing, and comparing current data against historical trends. By leveraging the metadata-heavy architecture of Snowflake, it provides near-instantaneous access to previous data versions without requiring costly manual backups or complex database snapshots.

Answer

Database Snapshots

Answer

Materialized Views

Answer

Data Sharing

Question 6

A query that previously ran in 5 seconds now takes 2 minutes. The Query Profile shows that most of the time is spent in 'Remote Disk I/O'. What is the most likely cause for this performance degradation?

Accepted Answer

The warehouse cache was cleared or the data was not in the local cache.. Understanding where time is spent in the Query Profile is critical for troubleshooting performance issues. Remote Disk I/O indicates that the virtual warehouse is reading data from cloud storage rather than its local SSD cache. This usually happens when the cache is 'cold' or when the data volume exceeds the cache capacity.

Answer

The warehouse is under-provisioned and needs to be scaled up.

Answer

The query is experiencing resource contention from other users.

Answer

The table has too many micro-partitions and needs to be deleted.

Question 7

A provider is preparing to share data with a consumer via a direct share. The provider wants to ensure that the consumer can access the data but cannot see the underlying table structure or any other objects in the database. Which two actions should the provider take? (Choose two.)

Accepted Answer

Ensure the share contains only the secure view and no other objects.. To share data while hiding the underlying structure, the provider should create a secure view and add it to the share, and ensure the share contains only that view. Secure views prevent consumers from seeing the base table definitions, and limiting the share to the view ensures no other objects are exposed. Other options involve direct table access or ineffective grants that do not meet the security requirements.

Answer

Grant USAGE on the database to the consumer's role.

Answer

Create a database role and grant it to the consumer.

Answer

Add the table directly to the share and grant SELECT on the table to the consumer.

Question 8

A query is failing with the error 'Can\'t compile the query as it is too large'. Which action is most likely to resolve this issue while maintaining the query's logical intent?

Accepted Answer

Break the query into smaller parts using temporary tables or CTEs.. Snowflake has limits on the complexity of a single SQL statement's compilation. Very large queries with thousands of lines or deeply nested subqueries can hit memory limits in the Cloud Services layer. Simplifying the query structure or breaking it into smaller pieces is the standard approach to resolving compilation errors.

Answer

Increase the size of the virtual warehouse to 4X-Large.

Answer

Use the Search Optimization Service on the underlying tables.

Answer

Disable the use of micro-partition pruning for that specific session.

Question 9

A data provider wants to share a subset of data with a specific consumer while ensuring the consumer cannot see any other tables in the same database. What is the most secure and efficient method to achieve this?

Accepted Answer

Use a Secure View in a dedicated share-ready schema.. Secure Views are essential for sharing specific data subsets because they hide the underlying query logic and schema structure from the consumer. By using a Secure View, the provider maintains granular control over column and row visibility, ensuring data privacy across account boundaries. This approach prevents unauthorized metadata discovery, which is critical in multi-tenant data sharing environments where isolation between different business units or external partners must be strictly maintained for compliance.

Answer

Grant usage on the entire database to the consumer's role.

Answer

Create a shared database and grant access to the base tables.

Answer

Enable Secure Data Sharing and grant SELECT on all tables.

Question 10

What is the purpose of the PURGE = TRUE option in a COPY INTO command?

Accepted Answer

It removes data files from the stage after they are successfully loaded.. The PURGE parameter provides an automated way to manage the lifecycle of files in a stage. In many workflows, once a file has been successfully loaded into Snowflake, it is no longer needed in the staging area. Automating the deletion helps maintain a clean environment and can reduce storage costs in internal stages.

Answer

It deletes all records from the target table before starting the load.

Answer

It clears the Snowflake metadata cache to allow for a full reload of the data.

Answer

It removes only the files that failed to load due to formatting errors.