Free Practice Questions for Snowflake COF-C03 Exam

In Snowflake, when dealing with semi-structured data such as a JSON document stored in aVARIANTcolumn, the proper syntax to extract a value is to use the column name followed by the path to the specific element. Since arrays in JSON are zero-indexed, the first element is referenced with[0]. Therefore, to extract thenamefrom the first element of theelementsarray, the correct syntax isSource_column:elements[0] .name.

The correct answer is D. DataFrame .

In Snowpark, a DataFrame represents a lazily evaluated relational data set. It provides methods to operate on data, such as selecting columns, filtering rows, joining data sets, grouping data, and applying transformations.

Why D is correct:

A Snowpark DataFrame is the primary abstraction for working with data in Snowpark. It represents data in Snowflake and lets developers build transformations using supported programming languages such as Python, Java, or Scala.

Example concept:

df = session.table( " CUSTOMERS " )

filtered_df = df.filter(df[ " COUNTRY " ] == " USA " )

The DataFrame represents the data set, and operations on it are translated into SQL that runs in Snowflake.

Why the other options are incorrect:

A. A list is a general programming data structure, not the primary Snowpark data set abstraction.

B. A table stores data in Snowflake, but in Snowpark, the object used to represent and operate on a data set programmatically is a DataFrame.

C. An array is a data structure or semi-structured type, not the main Snowpark abstraction for data operations.

Official Snowflake documentation reference:

Snowflake documentation describes Snowpark DataFrames as objects that represent a lazily evaluated relational data set and provide methods for transforming and analyzing data.

In Snowflake, when an object is created, it is owned by the role that is currently active. This active role is the one that is being used to execute the creation command. Ownership implies full control over the object, including the ability to grant and revoke access privileges. This is specified in Snowflake ' s documentation under the topic of Access Control, which states that " the role in use at the time of object creation becomes the owner of the object. "

Snowflake Documentation: Object Ownership

Snowflake supports structured data in standard tables and semi-structured data in VARIANT, OBJECT, and ARRAY columns.

Users can load and query both using standard SQL functions , without schema constraints for semi-structured formats.

The correct answer is D. ACCOUNT_USAGE.TABLE_STORAGE_METRICS .

ACCOUNT_USAGE.TABLE_STORAGE_METRICS provides detailed storage metrics for tables, including active bytes, Time Travel bytes, Fail-safe bytes, and retained-for-clone bytes. This view is more detailed for table-level storage than SHOW TABLES or DESC TABLE.

Why D is correct:

TABLE_STORAGE_METRICS is designed specifically to report table-level storage usage.

Why the other options are incorrect:

A. SHOW TABLES provides table metadata, but not the most detailed storage breakdown.

B. DESC TABLE describes the table structure, such as columns and data types, not detailed storage consumption.

C. ACCOUNT_USAGE.STORAGE_USAGE provides account-level storage usage, not the most detailed table-level storage metrics.

Official Snowflake documentation reference:

Snowflake documentation describes ACCOUNT_USAGE.TABLE_STORAGE_METRICS as a view that provides storage information for tables, including active, Time Travel, Fail-safe, and retained-for-clone storage.

Enterprise Edition includes features such as multi-cluster warehouses and column-level security features. Multi-cluster warehouses are used for high concurrency, and column-level security includes features such as masking policies.

Why the others are incorrect:

Network policies are available below Enterprise. Private connectivity and Tri-Secret Secure require Business Critical or higher, not merely Enterprise.

In Snowsight, Snowflake ' s web interface, user preferences can be customized to enhance the user experience. Among these preferences, users can set a default database and default schema. These settings streamline the user experience by automatically selecting the specified database and schema when the user initiates a new session or query, reducing the need to manually specify these parameters for each operation. This feature is particularly useful for users who frequently work within a specific database or schema context.

The correct answer is D. When the system estimates that there is enough query load to keep the new cluster busy for at least 6 minutes .

In Snowflake, a multi-cluster warehouse can use either a Standard or Economy scaling policy. The Economy scaling policy is more conservative. It starts additional clusters only when Snowflake estimates that the extra cluster will have enough work to justify the additional compute cost.

Why D is correct:

With the Economy scaling policy, Snowflake starts another cluster only if the system estimates there is enough query load to keep the new cluster busy for approximately 6 minutes. This helps reduce credit consumption by avoiding unnecessary short-lived cluster starts.

Why the other options are incorrect:

A. The condition is not based on all active clusters running queries for more than 6 minutes.

B. A single queued query for more than 6 minutes is not the exact Economy scaling rule.

C. The rule is not specifically based on 3 or more queued queries.

D. This matches Snowflake’s documented behavior for the Economy scaling policy.

Official Snowflake documentation reference:

Snowflake documentation explains that the Economy scaling policy conserves credits by starting additional clusters only when Snowflake estimates enough query load exists to keep the cluster busy for at least 6 minutes.

The correct answers are A. Query pruning and E. Operations using Data Manipulation Language, or DML .

Snowflake automatically gathers and maintains metadata about micro-partitions. This metadata includes information such as the range of values in columns, distinct value counts, and other statistics. Snowflake uses this metadata to optimize query execution and table operations.

Why A is correct:

Query pruning is one of the most important uses of micro-partition metadata. Snowflake can skip micro-partitions that do not contain values needed by a query predicate.

Example:

SELECT *

FROM sales

WHERE sale_date = ' 2026-01-01 ' ;

If Snowflake knows from micro-partition metadata that certain micro-partitions do not contain sale_date = ' 2026-01-01 ' , those micro-partitions can be skipped.

Why E is correct:

DML operations such as UPDATE, DELETE, and MERGE can also benefit from micro-partition metadata. Snowflake can use metadata to identify which micro-partitions are affected by the operation instead of scanning unnecessary partitions.

Why the other options are incorrect:

B. The result cache returns previously computed query results. It avoids re-executing the query and therefore does not rely on micro-partition pruning in the same way.

C. Local disk cache stores data on warehouse compute resources after access, but it is not the main feature that uses micro-partition metadata.

D. “Remote disk cache” is not the standard Snowflake concept tested here. Snowflake has remote storage, warehouse cache, and result cache, but query pruning is the metadata-driven feature.

Official Snowflake documentation reference:

Snowflake documentation explains that micro-partition metadata is used for efficient query pruning and optimization. It also supports efficient table maintenance and DML operations by helping Snowflake identify relevant micro-partitions.

The correct answer is A. OBJECT_VIEWER.

Snowflake provides system-defined database roles for the shared SNOWFLAKE database. These roles allow users to access different categories of metadata and usage information in schemas such as ACCOUNT_USAGE.

The OBJECT_VIEWER database role is used to view object metadata. This includes metadata about databases, schemas, tables, views, and other Snowflake objects.

Why A is correct:

The ACCOUNT_USAGE.TABLES view contains information about tables in the account. Access to object metadata, including lists of tables, is associated with the OBJECT_VIEWER database role.

Why the other options are incorrect:

B. USAGE_VIEWER is used for usage and billing-related information, such as metering and warehouse usage, not object lists.

C. GOVERNANCE_VIEWER is used for governance-related metadata, such as tags, masking policies, and related governance features.

D. SECURITY_VIEWER is used for security-related metadata, such as users, roles, grants, and access history information.

Official Snowflake documentation reference:

Snowflake documentation describes system-defined database roles in the shared SNOWFLAKE database. The OBJECT_VIEWER role provides access to object metadata views in ACCOUNT_USAGE, including tables.

The function used to determine the kind of value stored in a VARIANT column in Snowflake is TYPEOF.

Understanding VARIANT Data Type:

VARIANT is a flexible data type in Snowflake that can store semi-structured data, such as JSON, Avro, and XML.

This data type can hold values of different types, including strings, numbers, objects, arrays, and more.

Using TYPEOF Function:

The TYPEOF function returns the data type of the value stored in a VARIANT column.

It helps in identifying the type of data, which is crucial for processing and transforming semi-structured data accurately.

Example Usage:

SELECT TYPEOF(variant_column)

FROM my_table;

This query retrieves the type of data stored invariant_columnfor each row inmy_table.

Possible return values include ' OBJECT ' , ' ARRAY ' , ' STRING ' , ' NUMBER ' , etc.

Benefits:

Simplifies data processing: Knowing the data type helps in applying appropriate transformations and validations.

Enhances query accuracy: Ensures that operations on VARIANT columns are performed correctly based on the data type.

Snowflake Documentation: TYPEOF

Snowflake Documentation: VARIANT Data Type

Parquet is a columnar storage file format that is optimized for efficiency in both storage and processing. It supports compression and encoding schemes that significantly reduce the storage space needed and speed up data retrieval operations, making it ideal for handling large volumes of data. Unlike JSON or TSV, which are row-oriented and typically uncompressed, Parquet is designed specifically for use with big data frameworks, offering advantages in terms of performance and cost when storing and querying semi-structured data.

A Reader account in Snowflake can be used to share data with a consumer who does not have a Snowflake account. Reader accounts are a type of shared account provided by data providers to external data consumers, allowing them to access and query shared data using Snowflake ' s web interface without needing their own Snowflake account.

Snowflake supports incremental refresh for many common query constructs, including supported uses of DISTINCT, GROUP BY, and UNION ALL.

General lateral joins are not supported for incremental dynamic-table refresh. A documented exception exists for supported uses of LATERAL FLATTEN.

When a dynamic table is created with REFRESH_MODE = AUTO, Snowflake analyzes the defining query. When the query cannot be refreshed incrementally, Snowflake can choose full refresh mode.

When REFRESH_MODE = INCREMENTAL is explicitly requested and the query contains an unsupported construct, dynamic-table creation can fail instead of silently changing the requested refresh mode.

If any compute resources for a virtual warehouse fail to provision during startup, Snowflake will attempt to restart those failed resources. The system is designed to automatically handle transient issues that might occur during the provisioning of compute resources. By restarting the failed resources, Snowflake aims to ensure that the virtual warehouse has the necessary compute capacity to handle the user ' s workloads without manual intervention.

The correct answer is B. PARTITION BY .

The PARTITION BY option in COPY INTO < location > partitions unloaded data into separate files based on the expression specified. This is commonly used to organize unloaded files by values such as date, region, or another column.

Why B is correct:

PARTITION BY controls how unloaded rows are grouped into different output paths/files.

Example:

COPY INTO @my_stage/sales/

FROM sales

PARTITION BY (TO_DATE(sale_date))

FILE_FORMAT = (TYPE = CSV);

This unloads rows into separate output paths based on the sale_date expression.

Why the other options are incorrect:

A. DETAILED_OUTPUT controls the amount of unload result information returned by the command.

C. FILE_FORMAT controls the format of the unloaded files, such as CSV, JSON, or Parquet.

D. HEADER controls whether column headings are included in unloaded output files.

Official Snowflake documentation reference:

Snowflake documentation for COPY INTO < location > describes PARTITION BY as the option used to partition unloaded rows into separate files based on an expression.