COF-C03 Practice Test 22 — 15 Questions

Question 1

A company's security team wants to ensure that when a user with the role PII_ANALYST queries a table, only rows where the region column equals 'US' are returned, but they do not want to create separate copies of the table for each region. Which Snowflake feature should they implement?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Row access policy. A row access policy is designed to filter rows based on a condition evaluated at query time, such as the user's role. By attaching a row access policy to the table, the security team can ensure that PII_ANALYST sees only rows where region equals 'US', while other roles may see different rows or all rows. This avoids data duplication and centralizes access control.

Answer

Object tagging

Answer

Secure view

Answer

Column-level masking policy

Question 2

An administrator needs to implement a data governance strategy that includes tracking sensitive data and masking PII. Which TWO Snowflake features should be used to achieve this?

Accepted Answer

Object Tagging. Snowflake provides built-in governance features like Object Tagging and Dynamic Data Masking. Object Tagging allows administrators to categorize data (e.g., tagging a column as 'PII'), while Dynamic Data Masking uses policies to redact or obscure sensitive information at query time based on the user's role. Together, these features provide a robust framework for managing data privacy and compliance.

Answer

Search Optimization Service

Answer

Query Profile

Answer

Resource Monitors

Question 3

A data engineer is designing a pipeline for a high-frequency stream of small JSON files arriving every minute in an S3 bucket. Why would Snowpipe be preferred over a scheduled COPY INTO command running on a dedicated virtual warehouse?

Accepted Answer

Virtual warehouses are billed per second with a one-minute minimum, making them expensive for frequent small loads.. Snowpipe provides a serverless compute model that automatically scales based on the volume of data being ingested, which is ideal for small, frequent files. Using a dedicated virtual warehouse for a scheduled COPY command often leads to underutilization or excessive costs because the warehouse remains active for the minimum billing period even if the load finishes in seconds.

Answer

Snowpipe requires manual intervention to trigger loads using the REST API for every single file arriving in the bucket.

Answer

The COPY INTO command cannot process JSON files directly and requires an intermediate stage to convert data to CSV.

Answer

Scheduled COPY commands are limited to executing once every hour, which would create significant latency for real-time pipelines.

Question 4

A data analyst wants to explore semi-structured JSON data stored in a Snowflake table column. They need to extract specific fields and flatten nested arrays. Which Snowflake feature is best suited for this task?

Accepted Answer

VARIANT data type with dot notation and FLATTEN function. Snowflake's VARIANT data type is designed for semi-structured data such as JSON. Analysts can access fields using dot notation (e.g., column:field) and use the FLATTEN function to expand arrays into multiple rows. This native support eliminates the need for external processing and allows SQL-based exploration. The other options reference incompatible data types or functions from other systems.

Answer

OBJECT data type with LATERAL VIEW EXPLODE

Answer

ARRAY data type with UNNEST function

Answer

String data type with JSON_EXTRACT_PATH_TEXT function

Question 5

Which Snowflake feature allows a user to download data from a Snowflake table into a local folder on their computer using the SnowSQL command-line interface?

Accepted Answer

The GET command fetches files from an internal stage and saves them to the local directory.. The GET command is specifically designed to transfer files from an internal Snowflake stage to a local directory on a client machine. This is the inverse of the PUT command and is essential for retrieving data that has been unloaded from tables into internal stages for local analysis or archival purposes.

Answer

The PUT command is used to move files from the local file system into an internal stage.

Answer

The COPY INTO <location> command moves data from a table directly to a local hard drive.

Answer

The DOWNLOAD function is a built-in SQL utility that can be called from any worksheet.

Question 6

A Snowflake analyst runs a query that aggregates sales by region over the last 30 days. The query takes 40 seconds on an X-Small warehouse. The analyst then reruns the exact same query 10 minutes later without any data changes. It completes in 1 second. Which Snowflake feature explains this behavior?

Accepted Answer

Result Cache. The dramatic speedup on an identical query with unchanged data is the signature of Result Cache. Snowflake stores the result set in the Cloud Services layer, so a repeat query is served without engaging the virtual warehouse. Local disk and metadata caches accelerate data scanning and pruning but still require query execution, and auto-suspend does not cache results.

Answer

Warehouse Auto-Suspend

Answer

Metadata Cache

Answer

Local Disk Cache

Question 7

A data architect is designing a solution that requires zero-copy cloning of a production database for testing purposes. The clone must be created quickly and should not duplicate storage until changes are made. Which Snowflake feature should they use?

Accepted Answer

CREATE DATABASE ... CLONE. CREATE DATABASE ... CLONE performs a zero-copy clone, which duplicates only metadata and references existing micro-partitions. This allows rapid creation of a clone without immediate storage costs. Subsequent changes to the clone or source create new micro-partitions, preserving isolation. Other options either copy data physically or serve different purposes.

Answer

UNDROP DATABASE

Answer

CREATE DATABASE ... FROM SHARE

Answer

CREATE DATABASE ... AS SELECT

Question 8

A data engineer runs a query that joins a large fact table with a small dimension table. The query takes 12 minutes. The engineer notices that the small dimension table is broadcast to all compute nodes, and the fact table is redistributed across nodes. Which Snowflake feature is primarily responsible for this behavior, and what is its main benefit in this scenario?

Accepted Answer

Query optimizer's join strategy selection, which chooses broadcast for small tables to minimize data movement and improve performance.. The Snowflake query optimizer evaluates join inputs and selects the most efficient distribution method. When one side of the join is small, broadcasting it to all nodes eliminates the need to redistribute the larger table, reducing network traffic and improving performance. This optimization is automatic and based on table statistics and heuristics.

Answer

Virtual warehouse scaling, which adds compute resources to handle the join operation more quickly.

Answer

Result caching, which stores the query results for subsequent identical queries, reducing execution time.

Answer

Micro-partitioning, which automatically divides tables into small chunks and enables pruning during scans.

Question 9

Which statement best describes the 'Snowflake Data Cloud' architecture's approach to scalability?

Accepted Answer

It supports independent scaling of compute and storage.. Snowflake's architecture features a unique separation of storage and compute. This allows storage to grow independently as data is added and compute resources to be scaled independently as workloads change. Because compute resources (virtual warehouses) can be added, removed, or resized instantly without affecting the data in storage, Snowflake provides near-infinite, elastic scalability that is perfectly suited for modern, high-demand cloud analytical applications.

Answer

It requires manual sharding of data across compute nodes.

Answer

Storage and compute are scaled together as a single unit.

Answer

Scalability is limited by the physical hardware of the server.

Question 10

When designing a role-based access control (RBAC) model, which THREE of the following are recommended best practices?

Accepted Answer

Grant privileges to roles, not directly to users.. A robust RBAC model relies on hierarchical structures to simplify management and minimize errors. By granting privileges to roles rather than users, and nesting roles logically, you create a scalable security architecture. These best practices ensure that permissions are easy to audit, follow the principle of least privilege, and prevent 'privilege creep,' where users accumulate excess access rights that are never revoked over time as their roles change.

Answer

Use the ACCOUNTADMIN role for daily data analysis.

Answer

Assign all users the same 'PUBLIC' role for simplicity.

Question 11

A data engineer is loading JSON data from an external stage into a Snowflake table using COPY INTO with a JSON file format. The JSON records contain nested arrays and objects. The engineer wants to load specific elements into separate columns. Which approach should the engineer use?

Accepted Answer

Load the entire JSON record into a single VARIANT column, then use a view with dot notation and array indexing to extract the desired elements.. Snowflake provides robust support for semi-structured data through the VARIANT data type. Loading JSON into a VARIANT column and then using dot notation and array indexing in a view or query allows flexible extraction of nested elements. This approach leverages Snowflake's built-in functions and avoids the need for complex transformations or external processing.

Answer

Use the COPY INTO command with a transformation that uses the SPLIT_TO_TABLE function to flatten the arrays into rows before loading.

Answer

Define the target table with columns matching the JSON keys and use the MATCH_BY_COLUMN_NAME option in the file format to automatically map nested elements to columns.

Answer

Convert the JSON to CSV using an external tool before loading, then use a standard CSV file format with column mapping.

Question 12

What is the benefit of using clustering keys for a table that is queried using range filters?

Accepted Answer

It enables efficient partition pruning.. Range filters require scanning segments of data defined by start and end values. Without proper clustering, the engine must perform a full table scan. With clustering keys, the table data is physically sorted or organized into micro-partitions based on the key values. This allows the query optimizer to identify and read only the specific micro-partitions that fall within the range, drastically decreasing I/O and improving query speed for range-based analytical tasks.

Answer

It eliminates the need for a virtual warehouse.

Answer

It forces the query to use the result cache.

Answer

It speeds up DML operations like INSERT.

Question 13

Which Snowflake feature allows for the creation of a 'zero-copy' clone of a database or table?

Accepted Answer

Zero-copy Cloning. Cloning in Snowflake creates a metadata-only reference to the existing data. Because it does not copy the actual underlying micro-partitions, it is instantaneous and incurs no additional storage costs until the data in the clone is modified. This is a powerful feature for development, testing, and production environments, allowing users to create fully isolated, writable copies of production data in seconds without duplicating large datasets.

Answer

Data Replication

Answer

Table Partitioning

Answer

Time Travel

Question 14

A user needs to load data from a CSV file stored in an external stage into a Snowflake table. The CSV file has a header row and uses a pipe (|) as the field delimiter. The user wants to ensure the header row is skipped and the pipe delimiter is recognized. Which FILE_FORMAT options should be specified in the COPY INTO command?

Accepted Answer

TYPE = 'CSV', FIELD_DELIMITER = '|', SKIP_HEADER = 1. For a CSV file with a pipe delimiter and a header row, the FILE_FORMAT should specify TYPE = 'CSV', FIELD_DELIMITER = '|', and SKIP_HEADER = 1. This ensures the header is ignored and fields are correctly separated. Using the wrong delimiter or not skipping the header would result in parsing errors or unwanted data.

Answer

TYPE = 'CSV', FIELD_DELIMITER = '|', SKIP_HEADER = 0

Answer

TYPE = 'CSV', RECORD_DELIMITER = '|', SKIP_HEADER = 1

Answer

TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_HEADER = 1

Question 15

To publish a data listing on the Snowflake Marketplace and make it available to all Snowflake customers, which TWO requirements must the provider fulfill?

Accepted Answer

The provider must have a Business Profile that has been approved by Snowflake.. Publishing to the Snowflake Marketplace is a formal process that requires more than just technical setup. Providers must have a verified business profile to establish trust and must adhere to Snowflake's provider policies. These requirements ensure that the Marketplace remains a high-quality environment for data consumers and that providers are legitimate entities capable of supporting their data offerings.

Answer

The provider must use a Business Critical edition account or higher.

Answer

The provider must pay an annual Marketplace listing fee of $5,000.

Answer

The provider must share the data exclusively through the Marketplace and not through Direct Shares.