Free Practice Questions for Snowflake COF-C02 Exam

The minimum Snowflake edition that supports database replication is the Enterprise edition. Database replication allows data to be replicated between different Snowflake accounts or regions, providing high availability and disaster recovery capabilities.

Snowflake Documentation: Database Replication

The correct answer is C. Query acceleration service .

The Query Acceleration Service is a Snowflake performance optimization feature that can temporarily provide additional compute resources to accelerate eligible queries. It is designed for queries that scan large amounts of data and would otherwise take longer or consume significant warehouse resources.

Why C is correct:

The Query Acceleration Service can offload portions of query processing to shared compute resources provided by Snowflake. This helps speed up expensive or long-running queries without permanently resizing the virtual warehouse.

Why the other options are incorrect:

A. Resource monitor controls and monitors credit usage. It can suspend warehouses or send alerts, but it does not scale compute for expensive queries.

B. Materialized view stores precomputed query results to improve performance for repeated query patterns, but it does not temporarily scale a warehouse.

D. Search optimization service improves point lookup and selective query performance on large tables, but it does not temporarily scale virtual warehouse compute.

Official Snowflake documentation reference:

Snowflake documentation describes the Query Acceleration Service as a feature that can improve the performance of eligible queries by using additional compute resources. It is commonly associated with accelerating parts of large scans and reducing the impact of outlier queries.

TheMIN_DATA_RETENTION_TIME_IN_DAYSparameter is set at theAccountlevel in Snowflake. This parameter specifies the minimum number of days Snowflake retains the historical data for time travel, which allows users to access and query data as it existed at previous points in time.

Here ' s how to understand and adjust this parameter:

Purpose of MIN_DATA_RETENTION_TIME_IN_DAYS:This parameter is crucial for managing data lifecycle and compliance requirements within Snowflake. It determines the minimum time frame for which you can perform operations like restoring deleted objects or accessing historical versions of data.

Setting the Parameter:Only account administrators can set or modify this parameter. It is done at the account level, impacting all databases and schemas within the account. The setting can be adjusted based on the organization ' s data retention policy.

Adjusting the Parameter:

To view the current setting, use:

SHOWPARAMETERSLIKE ' MIN_DATA_RETENTION_TIME_IN_DAYS ' ;

To change the setting, an account administrator can execute:

ALTERACCOUNTSETMIN_DATA_RETENTION_TIME_IN_DAYS = < number_of_days > ;

Snowflake network policies are a security feature that allows administrators to control access to Snowflake by specifying allowed and blocked IP address ranges. These policies apply to all editions of Snowflake, making them widely applicable across different Snowflake environments. They are specifically designed to restrict or enable access based on the originating IP addresses of client requests, adding an extra layer of security.

Network policies are not applied to roles but are set at the account or user level. They are not activated usingALTER DATABASESQL commands but are managed throughALTER ACCOUNTorALTER NETWORK POLICYcommands. The management of network policies does not exclusively require theORGADMINrole; instead, they can be managed by users with the necessary privileges on the account.

A directory table in Snowflake is an implicit object layered on a stage, whether internal or external. It allows users to query the contents of a stage as if it were a table, providing metadata about the files stored in the stage, such as filenames, file sizes, and last modified timestamps.

Snowflake Documentation: Directory Tables

The correct answer is D. The volume of the data .

The NULL_COUNT Data Metric Function returns the number of NULL values in a column. This type of metric is used to verify data quality by measuring the amount, or volume, of missing values.

Why D is correct:

NULL_COUNT counts how many rows contain NULL in a specified column. Since it produces a count, it is used to evaluate the volume of missing data.

Example concept:

SELECT SNOWFLAKE.CORE.NULL_COUNT(

SELECT column_name FROM my_table

);

This returns the number of NULL values found in the selected column.

Why the other options are incorrect:

A. Accuracy verifies whether data values are correct, but NULL_COUNT only counts missing values.

B. Uniqueness is verified with metrics that check duplicate or distinct values, not NULL_COUNT.

C. Freshness measures how current or recent the data is, not how many nulls exist.

Official Snowflake documentation reference:

Snowflake documentation describes NULL_COUNT as a system Data Metric Function that returns the number of NULL values in a column.

Snowflake’s Time Travel feature allows users to access historical data within a specific period. This feature supports the restoration of various objects, including databases and schemas, to their previous states. Time Travel can be used for recovering dropped objects, undoing accidental changes, or analyzing data changes over time. However, it does not support user or role objects like Users and Roles, or compute resources like Virtual Warehouses.

The correct answer is C. To securely authenticate a user or an application .

Key-pair authentication uses a public/private key pair to authenticate to Snowflake. It is commonly used for service accounts, automation, applications, and client tools that need secure authentication without relying on passwords.

Why C is correct:

In key-pair authentication, the private key is used by the client, and the public key is associated with the Snowflake user. Snowflake verifies the authentication request using the public key.

Why the other options are incorrect:

A. Key-pair authentication is about authentication, not encrypting Snowflake data for access by one of two keys.

B. Secure data sharing is a separate Snowflake feature and is not the purpose of key-pair authentication.

D. MFA is a separate authentication control. Key-pair authentication does not itself enforce MFA.

Official Snowflake documentation reference:

Snowflake documentation describes key-pair authentication as an authentication method using a public/private key pair for users and applications.

The correct answer is A. VARCHAR .

A clustering key can be defined on supported table columns or expressions to help Snowflake organize micro-partitions and improve pruning for certain query patterns. Standard scalar data types such as VARCHAR are supported.

Why A is correct:

VARCHAR is a standard scalar data type and can be used in a clustering key.

Example:

CREATE TABLE customer_orders (

customer_id VARCHAR,

order_date DATE,

amount NUMBER

)

CLUSTER BY (customer_id);

Why the other options are incorrect:

B. VARIANT stores semi-structured data. Clustering directly on raw semi-structured columns is not the best answer here; clustering keys should use supported scalar columns or expressions.

C. OBJECT is a semi-structured data type and is not the correct choice for a clustering key column in this question.

D. GEOGRAPHY is not the correct supported clustering key data type in this context.

Official Snowflake documentation reference:

Snowflake documentation explains that clustering keys can be defined using table columns or expressions, and standard scalar columns such as string, number, and date/time columns are commonly used for clustering.

The correct answer is C. To record changes made to table data and metadata, so that the changed data can be processed .

A Snowflake stream is a change tracking object. It records data manipulation language, or DML, changes made to a source table, including inserts, updates, and deletes. Streams are commonly used with tasks to build incremental data pipelines.

Why C is correct:

Streams allow users to identify what changed in a table since the stream was last consumed. This makes it possible to process only changed rows rather than reprocessing the entire table.

A stream exposes metadata columns such as:

METADATA$ACTION

METADATA$ISUPDATE

METADATA$ROW_ID

These metadata columns help identify the type of change and the affected row.

Why the other options are incorrect:

A. This describes real-time ingestion, which is closer to Snowpipe, connectors, or streaming ingestion rather than streams.

B. Snowflake streams are not media streaming services.

D. This describes a general streaming data processing system, but a Snowflake stream itself does not process data. It records changes so other SQL processes can consume them.

Official Snowflake documentation reference:

Snowflake documentation describes streams as objects that record DML changes made to tables, including metadata about each change, so the changes can be consumed in downstream processing.

Two key metrics in Snowflake’sQuery Profilethat provide insights for performance improvement are:

Remote Disk IO: This measures the time the query spends waiting on remote disk access, indicating potential performance issues related to I/O bottlenecks.

Pruning: This metric reflects how effectively Snowflake’s micro-partition pruning is reducing the data scanned. Better pruning (more partitions excluded) leads to faster query performance, as fewer micro-partitions need to be processed.

These metrics are essential for identifying and addressing inefficiencies in data retrieval and storage access, optimizing overall query performance.

The USAGE_VIEWER role is the minimum required to view metadata and definitions of governance-enabled or secure objects like secure materialized views .

Fail-safe is a non-configurable feature in Snowflake that provides an additional layer of data protection beyond Time Travel. Time Travel allows users to access historical data within a configurable period (up to 90 days), while Fail-safe provides an additional 7-day period during which Snowflake retains historical data to recover from significant data loss or corruption incidents. This period is not accessible by users but can be used by Snowflake support to assist in data recovery efforts.