COF-C02 Snowflake SnowPro Core Certification Exam Free Practice Exam Questions (2026 Updated)

The correct function to use with theCOPY INTO < location > statement to convert rows from a relational table into a single variant column and to unload rows into a JSON file isTO VARIANT. TheTO VARIANTfunction is used to explicitly convert a value of any supported data type into a VARIANT data type. This is particularly useful when needing to aggregate multiple columns or complex data structures into a single JSON-formatted string, which can then be unloaded into a file.

In the context of unloading data, theCOPY INTO < location > statement combined withTO VARIANTenables the conversion of structured data from Snowflake tables into a semi-structured VARIANT format, typically JSON, which can then be efficiently exported and stored. This approach is often utilized for data integration scenarios, backups, or when data needs to be shared in a format that is easily consumed by various applications or services that support JSON.

Snowflake supports semi-structured data types that can store complex data structures within a single column. TheVARIANTdata type can hold structured and semi-structured data formats such as JSON, Avro, ORC, Parquet, and XML. TheARRAYdata type is also used for semi-structured data and can store an ordered list of elements. These data types enable users to work with semi-structured data directly in Snowflake without needing to flatten the data into a relational schema first.

The settings shown in the image indicate that the data retention time in days is configured at two different levels: the account level and the object level. At the account level, theMIN_DATA_RETENTION_TIME_IN_DAYSis set to 5 days, and at the object level, theDATA_RETENTION_TIME_IN_DAYSis set to 2 days. Since the object level setting has a lower value, it takes precedence over the account level setting for the specific object. Therefore, the data will be retained for 2 days at the object level.

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.

When a user is granted a custom role , they inherit only the privileges assigned to that role . This is aligned with the principle of least privilege in RBAC.

The correct answer is B. Directory table .

A directory table stores file-level metadata for files in a Snowflake stage. It can be used to see a list of files available in an internal or external stage.

Why B is correct:

Directory tables allow users to query staged file metadata, including file path, file size, last modified timestamp, and file URL information.

Example:

SELECT *

FROM DIRECTORY(@my_stage);

This returns metadata for files stored in the stage.

Why the other options are incorrect:

A. A semantic view is used to define business-friendly semantic relationships for analytics, not to list staged files.

C. A materialized view stores precomputed query results, not stage file metadata.

D. Information Schema contains metadata about Snowflake objects, but the Snowflake object specifically designed to list files in a stage is a directory table.

Official Snowflake documentation reference:

Snowflake documentation describes directory tables as objects that store a catalog of staged files in cloud storage.

The correct answer is B. Use the COPY INTO command using the PATTERN parameter to define the needed files .

The PATTERN parameter in the COPY INTO < table > command allows Snowflake to load only files whose names match a specified regular expression. This is useful when a stage contains many files and the required files can be identified by a naming pattern, such as year or suffix.

Why B is correct:

The user needs to load a subset of staged files from a 5-year collection and wants the least operational overhead. Using PATTERN avoids manually listing files, removing files, or downloading and restaging data.

Example concept:

COPY INTO target_table

FROM @my_stage

PATTERN = ' .*2024.*_01[.]gz ' ;

Why the other options are incorrect:

A. Individually loading files creates more manual work and higher operational overhead.

C. Removing unwanted files is risky and unnecessary. It also changes the staged file set.

D. Downloading files with GET, creating a new stage, and reloading files adds unnecessary operational complexity.

Official Snowflake documentation reference:

Snowflake documentation explains that the PATTERN option in COPY INTO < table > specifies a regular expression pattern used to match staged file names for loading.

When deciding to use a secure view, several considerations come into play, especially concerning security and performance:

A.No details of the query execution plan will be available in the query profiler: Secure views are designed to prevent the exposure of the underlying data and the view definition to unauthorized users. Because of this, the detailed execution plans for queries against secure views are not available in the query profiler. This is intended to protect sensitive data from being inferred through the execution plan.

C.Secure views do not take advantage of the same internal optimizations as standard views: Secure views, by their nature, limit some of the optimizations that can be applied compared to standard views. This is because they enforce row-level security and mask data, which can introduce additional processing overhead and limit the optimizer ' s ability to apply certain efficiencies that are available to standard views.

B.Once created, there is no way to determine if a view is secure or notis incorrect because metadata about whether a view is secure can be retrieved from the INFORMATION_SCHEMA views or by using theSHOW VIEWScommand.

D.It is not possible to create secure materialized viewsis incorrect because the limitation is not on the security of the view but on the fact that Snowflake currently does not support materialized views with the same dynamic data masking and row-level security features as secure views.

E.The view definition of a secure view is still visible to users by way of the information schemais incorrect because secure views specifically hide the view definition from users who do not have the privilege to view it, ensuring that sensitive information in the definition is not exposed.

TheSnowpark-optimized warehouseis designed with significantly higher memory per node, making it ideal formachine learning (ML) trainingand other memory-intensive workloads. Snowpark-optimized warehouses provide the necessary computational power and memory for complex, large-scale data processing tasks.

Standard and multi-cluster warehouses do not offer the same memory advantage, while the 6XL warehouse is large but not specifically optimized for memory-intensive operations.

The correct answer is A. By inspecting the statistics pane in the Query Profile .

The Query Profile provides execution details for a query. To evaluate micro-partition pruning, users can inspect scan statistics, including partitions scanned versus total partitions. This helps determine whether Snowflake is efficiently skipping unnecessary micro-partitions.

Why A is correct:

The Query Profile statistics pane can show how many partitions were scanned and how many total partitions existed. Efficient pruning means Snowflake scanned only a small portion of the total relevant micro-partitions.

Example interpretation:

Partitions scanned: 100

Partitions total: 10,000

This indicates strong pruning because Snowflake skipped most micro-partitions.

Why the other options are incorrect:

B. Query details in Snowsight provide useful metadata such as duration, warehouse, and query text, but the Query Profile statistics pane is the best place to validate pruning efficiency.

C. SYSTEM$CLUSTERING_INFORMATION() gives clustering information for a table, such as clustering depth and overlap, but it does not directly show whether a specific query pruned efficiently.

D. INFORMATION_SCHEMA.TABLES provides table metadata, not query-level partition pruning details.

Official Snowflake documentation reference:

Snowflake documentation explains that Query Profile provides detailed execution information, including table scan statistics such as partitions scanned and partitions total. These statistics help validate pruning effectiveness.

The correct answer is A. GET_RELATIVE_PATH .

The GET_RELATIVE_PATH function is used to return the relative path of a staged file. The relative path identifies the file name and path of the file within the stage.

Why A is correct:

A file stored in a stage can be referenced by its relative path. GET_RELATIVE_PATH retrieves that relative path, which includes the file name.

Why the other options are incorrect:

B. GET_STAGE_LOCATION returns the URL or location of a stage, not the filename of a specific staged file.

C. BUILD_STAGE_FILE_URL creates a URL for accessing a staged file, but it is not used simply to retrieve the filename.

D. GET_OBJECT_REFERENCES is related to object references, not staged file names.

Official Snowflake documentation reference:

Snowflake documentation describes GET_RELATIVE_PATH as a function that returns the relative path for a staged file.

The correct answers are A. Document AI and E. Cortex Analyst .

Snowflake Cortex is Snowflake’s integrated AI and machine learning capability set. It includes features for working with language models, document extraction, natural language analytics, and AI-powered application development.

Why A is correct:

Document AI is part of Snowflake Cortex. It is used to extract information from unstructured documents using AI.

Why E is correct:

Cortex Analyst is part of Snowflake Cortex. It enables natural language questions over structured data by using semantic models and Cortex capabilities.

Why the other options are incorrect:

B. Snowpipe is a data ingestion service, not part of the Cortex AI suite.

C. Streams are change data capture objects used to track table changes, not Cortex AI components.

D. Secure views are security/governance objects used to protect view definitions and support secure data sharing, not Cortex AI components.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake Cortex as a set of AI and ML capabilities and includes features such as Document AI and Cortex Analyst. Snowpipe, streams, and secure views belong to other Snowflake feature areas.

The correct answer is A. JSON .

When unloading floating-point or high-precision numeric values, users must consider whether the target file format can preserve the values as needed. JSON stores values in a textual semi-structured representation and can preserve numeric values without forcing them into a fixed Parquet numeric precision representation.

Why A is correct:

For values that exceed a precision/scale such as (15,9) and must be preserved during unloading, JSON is the safest option among the listed choices because it can represent the values textually in the output.

Why the other options are incorrect:

B. PARQUET has limitations for certain numeric precision/scale representations during unload. Snowflake documentation notes precision-related considerations for Parquet unloads.

C. AVRO is a semi-structured file format, but it is not the best documented answer for preserving float values beyond this precision scenario.

D. ORC is not the best answer for this Snowflake unload precision scenario.

Official Snowflake documentation reference:

Snowflake documentation for unloading data explains that file format choice can affect how numeric values are represented and preserved. JSON is commonly used when preserving semi-structured or text-based representations is required.

The correct answer is A. The table data does not require Fail-safe .

Transient tables are designed for data that does not need the same level of data recovery protection as permanent tables. Unlike permanent tables, transient tables do not have a Fail-safe period.

Why A is correct:

Fail-safe storage can add cost because Snowflake retains historical data for recovery by Snowflake support after the Time Travel retention period ends. Since transient tables do not have Fail-safe, they can reduce storage costs for data that does not require this additional protection.

Common use cases include:

Temporary ETL data

Staging data

Intermediate transformation data

Reproducible data

Non-critical working data

Why the other options are incorrect:

B. Long-term critical storage is usually better suited for permanent tables because they support Fail-safe.

C. Frequent access and modification alone does not make a transient table the best cost choice. The key factor is whether Fail-safe is needed.

D. Sharing data with external accounts is not the primary cost-effective use case for transient tables.

Official Snowflake documentation reference:

Snowflake documentation explains that transient tables persist until explicitly dropped, support Time Travel with limited retention, and do not have a Fail-safe period.

TheVALIDATEcommand in Snowflake is used to check for errors that occurred during the execution of aCOPY INTO < table > statement. This command helps users identify and resolve data loading issues.

Run the COPY INTO Statement:Execute theCOPY INTO < table > command to load data from a stage into a table.

COPY INTO my_table

FROM @my_stage

FILE_FORMAT = (FORMAT_NAME = ' my_format ' );

Validate the Load:Use theVALIDATEfunction to see if there were any errors during the data load.

SELECT *

FROM TABLE(VALIDATE(my_table, JOB_ID = > ' my_copy_job_id ' ));

Review Errors:TheVALIDATEfunction will return details about any errors that occurred, such as parsing errors or data type mismatches.

The Activity area of Snowsight, Snowflake ' s web interface, allows users to perform several important tasks related to query management and performance analysis. Among the options provided, the correct ones are:

B. Explore each step of an executed query:Snowsight provides detailed insights into query execution, including the ability to explore the execution plan of a query. This helps users understand how a query was processed, identify performance bottlenecks, and optimize query performance.

C. Monitor queries executed by users in an account:The Activity area enables users to monitor the queries that have been executed by users within the Snowflake account. This includes viewing the history of queries, their execution times, resources consumed, and other relevant metrics.

These features are crucial for effective query performance tuning and ensuring efficient use of Snowflake ' s resources.

The correct answer is C. Query Acceleration Service .

The Query Acceleration Service allows Snowflake to use additional compute resources to accelerate eligible queries. It is especially useful for queries that scan large amounts of data or include expensive operations that can benefit from extra parallel processing.

Why C is correct:

Query Acceleration Service temporarily uses additional Snowflake-managed compute resources for eligible queries. This can improve performance without permanently resizing the virtual warehouse.

Why the other options are incorrect:

A. Search Optimization Service improves highly selective lookup queries by maintaining search access paths, but it does not temporarily scale compute for expensive queries.

B. A warehouse scaling policy controls how multi-cluster warehouses add or remove clusters, but it is not the feature designed to accelerate individual expensive queries.

D. A multi-cluster warehouse improves concurrency by adding clusters, but it does not split or accelerate a single complex query in the way Query Acceleration Service can.

Official Snowflake documentation reference:

Snowflake documentation describes Query Acceleration Service as a feature that can improve query performance by using additional compute resources for eligible parts of queries.

In Snowflake, the parameter strip_outer_array = true can be set in the COPY INTO < table > command to remove the outer array structure from a semi-structured file. This parameter is useful for JSON files or similar data formats where an array might wrap the data, allowing Snowflake to directly load the inner elements as table rows.

Session policies allow account administrators to define rules such as session timeout , requiring users to re-authenticate after inactivity . This is different from password or network policies, which manage login credentials and IP filtering.

The correct answer is B. Only one refresh will run at a time .

Failover groups can be refreshed on a schedule to replicate supported account objects and database objects to target accounts. Snowflake does not run overlapping refresh operations for the same failover group.

Why B is correct:

If a scheduled refresh is still running, Snowflake does not start another concurrent refresh for the same failover group. Refresh operations are managed so that only one refresh runs at a time.

Why the other options are incorrect:

A. Multiple refreshes for the same failover group do not run concurrently.

C. Refreshes still require the appropriate privileges and configured replication/failover group permissions.

D. A delayed or long-running refresh does not mean the next refresh is automatically canceled in the way stated. The key behavior is that refreshes do not overlap.

Official Snowflake documentation reference:

Snowflake documentation for replication and failover groups explains that scheduled refreshes are managed by Snowflake and that refresh operations do not run concurrently for the same group.

When loading data into Snowflake, it is recommended to:

C. Load files that are approximately 100-250 MB (or larger): This size is optimal for parallel processing and can help to maximize throughput. Smaller files can lead to overhead that outweighs the actual data processing time.

D. Avoid using embedded characters such as commas for numeric data types: Embedded characters can cause issues during data loading as they may be interpreted incorrectly. It’s best to clean the data of such characters to ensure accurate and efficient data loading.

These best practices are designed to optimize the data loading process, ensuring that data is loaded quickly and accurately into Snowflake.

 The correct statement to accommodate the request for a marketing co-worker to change the size of their medium virtual warehouse called mktg__WH is to grant the MODIFY privilege on the warehouse to the ROLE MARKETING. This privilege allows the role to change the warehouse size among other properties.

 User-level network policies in Snowflake can be created by roles with the necessary privileges to manage security and account settings. The ACCOUNTADMIN role has the highest level of privileges across the account, including the ability to manage network policies. The SECURITYADMIN role is specifically responsible for managing security objects within Snowflake, which includes the creation and management of network policies.

Column-level security is a feature that allows fine-grained control over access to specific columns within a table. This is particularly useful for managing sensitive data and ensuring that only authorized users can view or manipulate certain pieces of information. According to my last update, this feature was available in the Enterprise Edition or higher editions of Snowflake.

The PUT command is used to load data into an internal stage in Snowflake. This command uploads data files from a local file system to a named internal stage, making the data available for subsequent loading into a Snowflake table using the COPY INTO command.

 Clustering is a technique used in Snowflake to improve the performance of queries. It involves organizing the data in a table into micro-partitions based on the values of one or more columns. This organization allows Snowflake to efficiently prune non-relevant micro-partitions during a query, which reduces the amount of data scanned and improves query performance.

Snowflake credits are the unit of measure for the compute resources used in Snowflake. The number of credits consumed depends on the size of the virtual warehouse and the time it is running. Larger warehouses consume more credits per hour than smaller ones, and credits are billed for the time the warehouse is active, regardless of the actual usage within that time.

 Snowflake is designed to optimize query performance by utilizing metadata for certain types of queries. When executing a COUNT query, Snowflake can often fulfill the request by accessing metadata about the table’s row count, rather than scanning the entire table or micro-partitions. This is particularly efficient for large tables like FCT_SALES with a significant number of records. The metadata layer maintains statistics about the table, including the row count, which enables Snowflake to quickly return the result of a COUNT query without the need to perform a full scan.