COF-C03 Practice Test 17 — 15 Questions

Question 1

Which Snowflake command is used to upload data files from a local file system to an internal stage?

Accepted Answer

PUT. The PUT command is the primary method for moving local files into the Snowflake cloud environment's internal stages. This command is executed via the SnowSQL CLI or other drivers that support local file access. It handles the secure upload and optional encryption of data before it reaches the Snowflake managed storage.

Answer

COPY INTO

Answer

GET

Answer

INSERT

Question 2

Which component in the Snowflake architecture is responsible for managing the data storage lifecycle, including compaction and file management?

Accepted Answer

The Cloud Services layer. Snowflake is a fully managed service, which means it automatically handles storage optimization tasks. The Cloud Services layer keeps track of all micro-partitions and performs background maintenance, such as coalescing small files into larger, more efficient ones. This 'zero-management' philosophy allows users to focus on querying their data rather than worrying about physical file layout, vacuuming, or manual performance tuning of the underlying storage environment.

Answer

The Virtual Warehouse

Answer

The Object Storage provider

Answer

The Data Loader

Question 3

Which function or command should be used to analyze the execution details of a slow-running query in Snowflake?

Accepted Answer

Query Profile.. The Query Profile is the primary tool for visualizing the execution plan and performance metrics of a query. It provides a breakdown of each stage, including the time spent on data scanning, joins, and aggregations. Accessing this through the Snowflake UI or the `GET_QUERY_OPERATOR_STATS` function is essential for identifying bottlenecks and understanding how the optimizer handled the query, which is a prerequisite for effective performance tuning and optimization efforts.

Answer

DESCRIBE TABLE.

Answer

SHOW WAREHOUSES.

Answer

SYSTEM$ABORT_QUERY.

Question 4

A data engineer is analyzing a slow query and notices that the Query Profile shows a high percentage of time spent in 'TableScan' with many partitions scanned but few rows returned. Which action is most likely to improve performance?

Accepted Answer

Add a clustering key on the columns used in the filter predicates.. The Query Profile indicates excessive partitions scanned, which is a sign of poor partition pruning. Clustering the table on the filter columns can co-locate similar values, allowing the optimizer to skip more partitions. Increasing warehouse size or clusters does not reduce the amount of data read, and enabling Search Optimization on all columns is not a focused fix.

Answer

Enable the Search Optimization Service on all columns.

Answer

Use a larger warehouse with more clusters.

Answer

Increase the size of the virtual warehouse.

Question 5

Which feature allows Snowflake to automatically utilize results from previous queries without consuming compute resources?

Accepted Answer

Query Result Cache. The Query Result Cache stores the results of queries for 24 hours. If an identical query is executed and the underlying data has not changed, Snowflake returns the result from the cache immediately. This avoids the need to spin up or use warehouse compute resources, providing near-instant responses while significantly reducing costs. It is a critical component for optimizing dashboard performance and reducing redundant processing of static data in analytical environments.

Answer

Warehouse Caching

Answer

Metadata Cache

Answer

Micro-partition Pruning

Question 6

Which of the following describes the behavior of Snowflake's 'Query Profile' when encountering a join that produces a large Cartesian product?

Accepted Answer

The Query Profile shows a massive increase in rows at the join operator compared to inputs.. A Cartesian product occurs when a join lacks a proper join condition, causing each row in one table to match every row in the other. This results in a massive explosion of intermediate records. The Query Profile clearly shows this as a 'Join' operator with a high number of output rows compared to the input. Recognizing this pattern is vital for debugging performance issues, as these joins are almost always unintentional and cause severe latency.

Answer

The query optimizer automatically detects the Cartesian product and rejects the SQL.

Answer

The Query Profile automatically suggests a specific WHERE clause to fix the join.

Answer

The Query Profile reports a 'Cartesian Product Error' and halts execution.

Question 7

Refer to the exhibit. Which Snowflake security feature is represented by this JSON definition?

Accepted Answer

Column-level Masking. Masking policies allow for fine-grained access control by obfuscating data based on the user's role. This is a crucial component of Snowflake's column-level security. By applying this policy to a column, sensitive data like PII is automatically hidden from unauthorized users while remaining visible to authorized administrators, ensuring compliance with data privacy regulations without needing multiple versions of the same table.

Answer

Row-level Security

Answer

Access Control List

Answer

Data Projections

Question 8

What is the most efficient way to perform a bulk load of data into Snowflake from a local file system?

Accepted Answer

Use the COPY INTO command to load data from an internal or external stage.. The recommended approach for bulk loading is to use an internal stage or external cloud storage combined with the COPY INTO command. This pattern separates the data staging from the loading process, allowing for parallel processing and robust error handling. Understanding this workflow is fundamental to data engineering on Snowflake, as it optimizes throughput and minimizes the overhead associated with inserting data via individual DML statements, which is inefficient.

Answer

Execute multiple individual INSERT INTO statements in a transaction.

Answer

Use the Snowflake UI 'Load Data' wizard for all production workloads.

Answer

Use an external table to query the data without loading it into Snowflake.

Question 9

A data engineer is configuring Snowpipe to automatically ingest files as they arrive in an external S3 stage. They must ensure the pipe loads only files matching a specific path prefix and that duplicate notifications for the same file do not cause duplicate rows. Which two configurations should they apply? (Choose two.)

Accepted Answer

Define the pipe with a COPY INTO statement that includes a PATTERN option matching the desired path prefix.. Filtering by path is achieved with the PATTERN option in the pipe's COPY statement, which restricts ingestion to matching files. Duplicate prevention comes from Snowpipe's load metadata, which records each loaded file by name and checksum and skips repeats even if notifications are delivered multiple times. Together these two settings satisfy both the prefix restriction and the deduplication requirement.

Answer

Rely on the S3 event notification configuration to prevent duplicate notifications from being sent.

Answer

Set the pipe parameter FORCE = TRUE so that every notification reloads the file.

Answer

Configure the pipe with AUTO_INGEST = TRUE and a notification channel, and set ON_ERROR = 'CONTINUE'.

Question 10

Refer to the exhibit. What happens if a user with the 'ANALYST' role queries the 'ssn' column protected by this policy?

Accepted Answer

The column value is replaced with the masked output.. When a masking policy is applied, Snowflake evaluates the condition at query runtime. If the condition evaluates to FALSE for the current user's role, Snowflake replaces the data with the masked value defined in the policy. This ensures that sensitive data is only revealed to authorized users, protecting the organization from unauthorized data exposure while allowing the schema and query logic to remain unchanged for all users.

Answer

The query fails with an 'Access Denied' error.

Answer

The query returns NULL for all rows.

Answer

The user sees the unmasked ssn data.

Question 11

A provider wants to ensure that a Reader Account they created does not exceed a budget of 50 credits per month. What is the most effective way to implement this control?

Accepted Answer

Create a Resource Monitor in the provider account and assign it to the Reader Account.. Since the provider is responsible for the costs of a Reader Account, they must have tools to manage that expenditure. Resource Monitors are the standard Snowflake mechanism for this. A provider can create a resource monitor and associate it with the Reader Account (or the warehouses within it) to track credit usage and trigger actions like alerting or suspending the warehouse.

Answer

Set a hard limit on the 'READER_ACCOUNT_CREDITS' parameter in the provider account settings.

Answer

Ask the consumer to monitor their own usage and stop querying when they hit 50 credits.

Answer

The provider must manually drop the Reader Account once the 50-credit threshold is reached each month.

Question 12

When using the Snowflake Connector for Python, which method is most efficient for uploading and loading large local CSV files into a Snowflake table?

Accepted Answer

Using the 'write_pandas' function which automates the PUT and COPY INTO commands internally.. The Python Connector offers specialized methods to optimize data movement. For large local files, using the write_pandas method or executing a PUT followed by a COPY command is much more efficient than executing individual INSERT statements, as it leverages Snowflake's bulk loading capabilities and reduces network overhead.

Answer

Iterating through the CSV in Python and executing an INSERT statement for every row found.

Answer

Converting the CSV into a single large SQL string and executing it as one massive INSERT.

Answer

Calling the 'snowflake.load_file' method which bypasses the need for any virtual warehouse.

Question 13

A provider uses a reader account to share data with a client that does not have its own Snowflake account. The client now reports that it cannot see newly added tables that the provider granted to the share. The provider confirms the new tables were added to the share with SELECT grants. Which statement explains why the client cannot see the new tables?

Accepted Answer

The provider must grant USAGE on the schema containing the new tables to the share.. For any table to be visible through a share, the share needs USAGE on the table's database and schema in addition to SELECT on the table. If the new tables reside in a schema that was never granted to the share, the consumer cannot see them. Granting the missing schema-level USAGE makes the new tables appear to the reader account without recreating anything.

Answer

The reader account's shared database must be recreated to pick up new tables.

Answer

Reader accounts require a separate share for each new table.

Answer

Reader accounts can only query tables that existed when the reader account was created.

Question 14

Which TWO of the following statements accurately describe the function of the Snowflake Cloud Services layer?

Accepted Answer

It handles query parsing, compilation, and optimization.. The Cloud Services layer acts as the brain of Snowflake. It handles metadata management, access control, query optimization, and infrastructure coordination. By separating these management tasks from the compute layer, Snowflake ensures that global state is maintained independently of specific compute clusters. Understanding this layer is fundamental to grasping how Snowflake achieves high availability, security, and performance without requiring users to manage underlying hardware or software configurations directly.

Answer

It stores the actual table data in micro-partitions.

Answer

It provides compute resources to execute DML statements.

Answer

It performs the final data shuffling for JOIN operations.

Question 15

What is the primary function of the Snowflake metadata store during the query optimization process?

Accepted Answer

To enable partition pruning.. The metadata store contains critical information about every micro-partition, including the range of values (min/max) for each column. During query optimization, Snowflake uses this metadata to 'prune' partitions—effectively ignoring any micro-partitions that cannot possibly contain the data requested. This process dramatically reduces the amount of data scanned from storage, allowing queries on massive datasets to return results in milliseconds rather than minutes.

Answer

To store the actual data rows.

Answer

To manage the user identity and roles.

Answer

To perform real-time data compression.