COF-C03 Practice Test 21 — 15 Questions

Question 1

Which feature in Snowflake allows you to track and audit all SQL queries executed across the entire account?

Accepted Answer

QUERY_HISTORY.. The QUERY_HISTORY table function and the ACCOUNT_USAGE.QUERY_HISTORY view are the primary methods for auditing query activity. These objects record every query submitted, who submitted it, the warehouse used, and the execution time. This is critical for data governance because it provides a forensic trail for compliance, troubleshooting, and understanding how data is accessed and modified over time by different users.

Answer

ACCESS_HISTORY.

Answer

SESSION_HISTORY.

Answer

DATA_TRANSFER_HISTORY.

Question 2

What is the primary role of the Query Optimizer in Snowflake?

Accepted Answer

To create an efficient execution plan.. The Query Optimizer analyzes the SQL submitted by a user and generates the most efficient execution plan based on the data's metadata. By evaluating join orders, data pruning, and the distribution of data across compute nodes, it ensures that the query consumes minimal resources. This is a crucial function of the Cloud Services layer, making Snowflake accessible to SQL users of all levels while maintaining 'expert-level' performance without needing manual tuning or hints.

Answer

To index data manually.

Answer

To compress data stored in the database.

Answer

To manage user credentials.

Question 3

A retail provider shares a secure view that joins a table in its SALES database with a table in its INVENTORY database. A consumer reports that queries against the shared view fail with an authorization error, even though the view itself is granted to the share. The provider confirms both underlying tables exist and the view definition is valid. Which issue most likely explains the failure?

Accepted Answer

The provider did not grant USAGE on the databases and schemas containing the underlying tables to the share.. When a shared view references objects in multiple databases, the share must carry USAGE on every database and schema that contains a referenced object, plus SELECT on the view. Granting only the view leaves the underlying containers inaccessible, so the consumer's query cannot be authorized. Adding the missing container grants resolves the error without changing the view.

Answer

The consumer must be granted the ACCOUNTADMIN role to query cross-database views.

Answer

The share was not associated with the consumer account.

Answer

Secure views cannot reference objects in more than one database.

Question 4

Which TWO conditions must be met for the Query Acceleration Service (QAS) to boost the performance of a query?

Accepted Answer

The query must be identified by the system as having a large scan component.. The Query Acceleration Service (QAS) acts like a 'turbocharger' by offloading parts of a query to a shared pool of compute resources. It is specifically designed for queries that are bottlenecked by scanning large amounts of data or performing heavy filtering, and the warehouse must have QAS enabled with a valid scale factor.

Answer

The query must involve complex window functions or recursive CTEs.

Answer

The table being queried must be a clustered table.

Answer

The query results must be larger than 100 GB.

Question 5

A data engineer has a CSV file on their local machine and wants to load it into a Snowflake table. They do not have access to an external cloud storage bucket and want the simplest path that does not require creating a named internal stage. Which command should they use?

Accepted Answer

PUT file:///tmp/data.csv @%my_table; then COPY INTO my_table FROM @%my_table. Every table has an implicit table stage accessible as @%table_name, which requires no manual creation. Uploading the local file with PUT to the table stage and then running COPY INTO from that stage is the simplest way to load a single local CSV without provisioning a named internal stage. This satisfies the no-named-stage constraint directly.

Answer

COPY INTO my_table FROM 'file:///tmp/data.csv'

Answer

PUT file:///tmp/data.csv @my_table

Answer

COPY INTO my_table FROM @my_named_stage

Question 6

A consumer has created a database from a share. They want to grant their 'ANALYST' role the ability to query the tables within this shared database. Which privilege should they grant to the role?

Accepted Answer

GRANT IMPORTED PRIVILEGES ON DATABASE <shared_db> TO ROLE ANALYST;. When a consumer mounts a share, the resulting database is special. To allow other roles in their account to use it, they use the 'IMPORTED PRIVILEGES' grant. This is a bulk grant that conveys the necessary permissions (like USAGE and SELECT) on all objects within the shared database to a local role, simplifying the permission management for the consumer.

Answer

GRANT SELECT ON ALL TABLES IN DATABASE <shared_db> TO ROLE ANALYST;

Answer

GRANT USAGE ON DATABASE <shared_db> TO ROLE ANALYST;

Answer

GRANT OWNERSHIP ON DATABASE <shared_db> TO ROLE ANALYST;

Question 7

An organization wants to simplify its data pipeline by automatically updating target tables whenever new data arrives in the source tables, without manually managing tasks or streams. Which Snowflake feature is designed for this architectural pattern?

Accepted Answer

Dynamic Tables. Dynamic Tables are a declarative way to define data transformations. Instead of writing complex code to manage streams and tasks, users provide a SQL query that defines the desired end state. Snowflake's architecture then automatically manages the scheduling and incremental processing required to keep the target table synchronized with the source data based on a target 'lag'.

Answer

Materialized Views

Answer

Snowpipe Streaming

Answer

External Functions

Question 8

When sharing data via the Snowflake Marketplace, what architectural component ensures that the consumer sees the most up-to-date data without the provider having to re-send files?

Accepted Answer

Snowflake's metadata-driven architecture that shares pointers to micro-partitions.. Snowflake Data Sharing is built on a multi-tenant metadata architecture. When a provider shares a database, they are not moving or copying the physical data. Instead, they are sharing the metadata that points to the underlying micro-partitions. This means the consumer always queries the live data stored in the provider's account, ensuring real-time access and consistency.

Answer

The use of Secure Views to filter data for the consumer.

Answer

Automatic replication of the shared database to the consumer's account.

Answer

The Cloud Services layer caching the shared results for the consumer.

Question 9

A Snowflake administrator is configuring a new virtual warehouse for a data science team. The team requires the ability to run multiple concurrent queries without queuing, and they want to minimize credit consumption during idle periods. The administrator needs to configure the warehouse with appropriate settings. Which two actions should the administrator take? (Choose two.)

Accepted Answer

Enable multi-cluster warehouse with auto-scaling.. To handle multiple concurrent queries without queuing, enabling multi-cluster warehouse with auto-scaling allows Snowflake to add clusters dynamically as needed. To minimize credit consumption during idle periods, setting a short auto-suspend period ensures the warehouse stops when not in use. Together, these settings provide both concurrency and cost efficiency. Other options either do not address the requirements or could hinder performance.

Answer

Configure the warehouse to use a smaller size to reduce credits.

Answer

Set the warehouse to auto-resume when a query is submitted.

Answer

Enable the query result cache to avoid re-execution.

Question 10

What is the primary benefit of using a Search Optimization Service (SOS) in Snowflake?

Accepted Answer

It significantly improves the performance of point-lookup queries.. The Search Optimization Service is designed to accelerate point-lookup queries that return a single row or a small subset of rows. By maintaining a persistent index of the data in the background, SOS allows the query optimizer to skip irrelevant micro-partitions and navigate directly to the specific data requested. This is crucial for applications that require low-latency response times for highly specific lookups on massive datasets, significantly reducing the compute load for those specific query patterns.

Answer

It speeds up complex joins and aggregations.

Answer

It automatically compresses data to reduce storage costs.

Answer

It allows multiple users to write to the same table simultaneously.

Question 11

A data engineer is optimizing a complex query that joins multiple large tables and applies several aggregations. The Query Profile shows significant time spent in the Join and Aggregate operators, and the warehouse is sized appropriately. Which TWO actions should the engineer take to improve performance? (Choose two.)

Accepted Answer

Collect statistics on the join keys and filter columns.. The query is experiencing bottlenecks in join and aggregation operations despite adequate warehouse size. Collecting statistics on join keys and filter columns gives the optimizer the information it needs to generate a more efficient plan, such as choosing the optimal join order and aggregation method. Additionally, ensuring that join keys have the same data type avoids implicit casting, which can hinder performance by preventing the use of efficient join algorithms. Together, these actions address the root causes of the performance issue.

Answer

Use a larger virtual warehouse to increase compute resources.

Answer

Add search optimization service to the tables involved in the join.

Answer

Rewrite the query to use temporary tables for intermediate results.

Question 12

A data scientist wants to create a copy of a 100 TB production table to test a new transformation logic. How does Snowflake’s Zero-copy Cloning architecture handle this request?

Accepted Answer

The new table shares the same metadata and micro-partitions as the original table.. Zero-copy cloning is a metadata-only operation in Snowflake. Instead of physically duplicating the data, Snowflake creates new metadata entries that point to the existing micro-partitions of the source table. This allows for instantaneous creation of large clones without consuming additional storage space until changes are made to the clone, at which point new micro-partitions are created.

Answer

Snowflake creates a physical copy of all 100 TB of data in a new storage bucket.

Answer

The clone is a 'read-only' view of the original table and cannot be modified.

Answer

Cloning requires a running virtual warehouse to copy the data blocks to the new table.

Question 13

A data engineer is optimizing a transformation pipeline and wants to reduce compute cost and improve performance for queries that repeatedly scan the same large table with different filters. Which TWO Snowflake features or techniques directly support this goal? (Choose two.)

Accepted Answer

Create a materialized view that pre-aggregates the filtered results.. Clustering and materialized views both attack the cost of repeated scans. Clustering reduces the micro-partitions read by improving pruning on filtered columns, while materialized views precompute and maintain aggregated results so recurring queries avoid touching the base table. Together they cut compute for the described pattern, whereas timeout settings, cache disabling, and transient storage do not improve scan efficiency.

Answer

Disable the result cache at the account level to force fresh execution.

Answer

Convert the table to a transient table to avoid fail-safe storage charges.

Answer

Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter for the session.

Question 14

What is the primary benefit of the Snowflake Query Result Cache compared to a standard warehouse cache?

Accepted Answer

It provides instant performance without compute costs.. The Query Result Cache is managed by the Cloud Services layer and stores the exact result of a query for 24 hours (provided the underlying data hasn't changed). Unlike the warehouse cache, which is local to a specific virtual warehouse and requires the warehouse to be running, the Result Cache is globally accessible. This provides instantaneous performance for repeat queries without incurring any compute credit costs, regardless of which warehouse or user is requesting the data.

Answer

It persists data for longer than 24 hours.

Answer

It allows for user-defined schema updates.

Answer

It requires the warehouse to be active.

Question 15

A provider has created a share and added a table. The consumer reports that they can see the shared database and schema but cannot see the table. The provider confirms the table was added with ALTER SHARE ... ADD TABLE. Which additional privilege must the provider grant to the share so the consumer can query the table?

Accepted Answer

SELECT on the table, granted to the share.. When a table is added to a share, the provider must also grant SELECT on that table to the share. The consumer already has visibility of the database and schema, so the missing element is table-level read access. Granting SELECT to the share is the documented step that enables queries. Other privileges like USAGE, REFERENCES, or OWNERSHIP do not provide the needed row-level read permission.

Answer

REFERENCES on the table, granted to the share.

Answer

OWNERSHIP on the table, transferred to the share.

Answer

USAGE on the database and schema containing the table, granted to the share.