COF-C03 Exam Simulation — 100 Questions

Question 1

Which TWO statements are true regarding the behavior and management of External Tables in Snowflake?

Accepted Answer

External tables can be manually refreshed or configured to refresh automatically using cloud notifications.. External tables allow Snowflake to query data stored in external cloud storage without first importing it into Snowflake's proprietary storage format. They are read-only and rely on metadata that points to the files. To maintain performance and accuracy, they can be partitioned based on the folder structure and require metadata refreshes to detect new or removed files in the cloud bucket.

Answer

External tables support the same performance optimizations as native tables, including clustering keys.

Answer

Data in external tables can be updated directly using standard DML commands like UPDATE or DELETE.

Answer

External tables store a version of the data in Snowflake's internal storage for faster access.

Question 2

When a query is submitted to Snowflake, which component is responsible for retrieving the metadata required to build the query execution plan?

Accepted Answer

The Cloud Services Layer. The Cloud Services layer maintains the metadata store, which contains information about table structures, file locations (in object storage), and statistics. When a user runs a query, the Cloud Services layer queries this metadata to optimize the execution path. This centralized management ensures that the compute nodes only receive instructions on which files to process, significantly reducing overhead and improving query performance by pruning unnecessary data before it is ever accessed.

Answer

The Virtual Warehouse

Answer

The Database Storage Layer

Answer

The Query Results Cache

Question 3

Refer to the exhibit. A user with the role 'ANALYST_ROLE' cannot see tables inside the 'sales_db.public' schema despite having 'USAGE' on the database. What is the most likely reason for this access issue?

Accepted Answer

The user lacks the 'USAGE' privilege on the schema.. In Snowflake, the USAGE privilege on a database is insufficient to view objects within schemas; the user must also be granted USAGE on the schema and SELECT on the specific tables or views. This multi-layered privilege requirement is a core component of Snowflake's security model, preventing accidental data exposure by requiring explicit grants at every level of the object hierarchy, from the database down to the individual object.

Answer

The user needs the 'OWNERSHIP' privilege on the schema.

Answer

The database is not set as the default database for the user.

Answer

The user is missing the 'MANAGE GRANTS' privilege.

Question 4

A data engineer needs to transform a JSON column stored in a VARIANT type into a relational table. The JSON contains a top-level array of objects, each with keys `id`, `name`, and `tags`, where `tags` is itself an array of strings. The engineer wants each object to become a row, with the `tags` array flattened into a separate column containing one tag per row. Which combination of Snowflake functions will produce one row per tag while preserving `id` and `name`?

Accepted Answer

`LATERAL FLATTEN(input => json_col:tags)` combined with `json_col:id::INT` and `json_col:name::STRING` in the SELECT list.. To explode a nested array within a VARIANT column, `LATERAL FLATTEN` is the correct tool. It takes an array as input and returns one row per element, while the lateral join keeps the original row's other columns accessible. Referencing `json_col:id` and `json_col:name` in the SELECT list preserves those attributes. Other functions either treat the array as a string, operate on object keys, or flatten the wrong level of the JSON structure, so they do not produce the required one-row-per-tag output.

Answer

`ARRAY_TO_STRING(json_col:tags, ',')` followed by `SPLIT_TO_TABLE` on the resulting string.

Answer

`OBJECT_KEYS(json_col)` to extract the array elements, then `GET` to access each tag.

Answer

`PARSE_JSON` on the VARIANT column followed by `FLATTEN` on the entire JSON object.

Question 5

Refer to the exhibit. A user executes a COPY INTO command and then queries the COPY_HISTORY. Based on the output shown, what most likely happened during the load and what is the current state of the data in the SALES_DATA table?

Accepted Answer

Exactly 500 rows were successfully loaded into the table, and the 2 errors were ignored.. The COPY_HISTORY result showing 'PARTIALLY_LOADED' with specific row errors indicates that the ON_ERROR parameter was likely set to 'CONTINUE'. In this mode, Snowflake skips rows that contain errors but proceeds to load all valid records into the table. This results in a successful transaction for the 500 valid rows, while the 2 erroneous rows are logged but not ingested.

Answer

The load failed completely, and no rows were added to the table because of the 2 errors.

Answer

The 2 errors represent duplicate files that were already loaded in a previous session.

Answer

The entire file was skipped because the number of errors exceeded the default threshold.

Question 6

Which approach is most effective for optimizing an aggregation query that performs a 'GROUP BY' on a high-cardinality column?

Accepted Answer

Use a materialized view to pre-aggregate the data.. High-cardinality columns can cause memory bottlenecks during aggregation because the distinct values cannot fit into the memory of a single node, leading to disk spilling. By using techniques like pre-aggregation or creating a materialized view that groups the data by the high-cardinality column, you reduce the workload. These strategies move the compute-heavy grouping operation to a more efficient time or structure, thereby preventing memory exhaustion and significantly improving the performance of the aggregation query.

Answer

Increase the warehouse size to add more nodes.

Answer

Reduce the number of columns in the SELECT clause.

Answer

Change the table type to transient.

Question 7

Refer to the exhibit. If a user with the 'ANALYST' role queries a table protected by this policy, what will they see?

Accepted Answer

The string '***@***.com'.. The MASKING POLICY uses the CURRENT_ROLE() function to determine visibility. Since the 'ANALYST' role is not included in the 'IN' clause of the CASE statement, the query will evaluate to the ELSE condition. Consequently, the sensitive email data will be replaced by the literal string '***@***.com'. This demonstrates how attribute-based access control works in Snowflake, ensuring that sensitive data exposure is restricted based on the user's active role.

Answer

The actual email addresses.

Answer

An error message indicating insufficient privileges.

Answer

NULL values.

Question 8

A Snowflake data provider creates a Reader Account for a consumer who does not have a Snowflake account. Who is responsible for the compute costs incurred by the queries executed within this Reader Account?

Accepted Answer

The provider, who is billed for all virtual warehouse usage within the Reader Account.. Reader accounts are a feature designed to allow providers to share data with organizations that are not yet Snowflake customers. Because the Reader Account is created and owned by the provider, the provider assumes all financial responsibility for the resources consumed. This includes the credits used by virtual warehouses within the Reader Account for querying the shared data.

Answer

The consumer, who is billed directly by Snowflake via a credit card registered to the Reader Account.

Answer

Snowflake, as part of the free tier benefits for new data consumers.

Answer

The cost is split equally between the provider and the consumer at the end of each billing cycle.

Question 9

A data administrator wants to ensure that all data access is audited. Where can they find a list of all tables accessed by a specific user?

Accepted Answer

ACCESS_HISTORY.. The ACCESS_HISTORY view, introduced to enhance Snowflake's governance capabilities, provides a comprehensive log of every object access event. It records which columns were queried and which tables were accessed by specific users. This tool is essential for compliance, allowing administrators to audit who accessed sensitive data and when, fulfilling regulatory requirements like GDPR or HIPAA that demand strict tracking of data usage.

Answer

QUERY_HISTORY.

Answer

INFORMATION_SCHEMA.TABLES.

Answer

LOGIN_HISTORY.

Question 10

Which feature of Snowflake allows for the rapid creation of a near-zero-copy clone of a database, schema, or table without consuming additional storage?

Accepted Answer

Zero-Copy Cloning. Zero-Copy Cloning is a powerful feature that creates a reference to the existing micro-partitions rather than copying the data. This enables near-instantaneous creation of development or testing environments that are identical to production. Since it shares the underlying storage until changes are made, it is extremely storage-efficient and provides a foundation for modern DevOps practices within the Snowflake data platform.

Answer

Time Travel

Answer

Data Sharing

Answer

Materialized Views

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.