Snowflake COF-C02 Practice Test - Questions Answers

TheSTRIP_OUTER_ARRAYfile format option in Snowflake is used when loading JSON documents that are composed of a large array containing multiple records. When this option is enabled, it removes the outer array structure, which allows each record within the array to be loaded as a separate row in the table.This is particularly useful for efficiently loading JSON data that is structured as an array of records1.

References:

Snowflake Documentation on JSON File Format

[COF-C02] SnowPro Core Certification Exam Study Guide

Thelist @my_stagecommand in Snowflake can be used to verify if data has been uploaded to an external stage namedmy_stage.This command provides a list of files that are present in the specified stage2.

The Snowflake Cloud Services layer performs a variety of tasks, including the management of metadata and the parsing and optimization of queries.This layer is responsible for coordinating activities across Snowflake, including user session management, security, and query compilation3.

In Snowflake's data sharing model, any full Snowflake account can both provide and consume shared data. Additionally, the data consumer pays only for the compute resources used to query the shared data.No actual data is copied or transferred between accounts, and shared data does not take up any storage in a consumer account, so the consumer does not pay for data storage1.

References =Introduction to Secure Data Sharing | Snowflake Documentation

In Snowflake, to extract a specific element from a JSON stored in a VARIANT column, the correct syntax is to use the dot notation. Therefore, the queryselect src.dealership from car_sales;will return the dealership information contained within each JSON object in thesrccolumn.

References: For a detailed explanation, please refer to the Snowflake documentation on querying semi-structured data.

The Search Optimization Service significantly improves the performance of selective point lookup queries on tables by creating and maintaining a persistent data structure called a search access path, which allows some micro-partitions to be skipped when scanning the table

Transient tables in Snowflake have a default Time Travel retention period of 1 day, which allows users to access historical data within the last 24 hours. However, transient tables do not have a Fail-safe period. Fail-safe is an additional layer of data protection that retains data beyond the Time Travel period for recovery purposes in case of extreme data loss.Since transient tables are designed for temporary or intermediate workloads with no requirement for long-term durability, they do not include a Fail-safe period by default1.

References:

Snowflake Documentation on Storage Costs for Time Travel and Fail-safe

Assigning the custom role to the SYSADMIN role is considered a best practice because it allows the SYSADMIN role to manage objects created by the custom role. This is important for maintaining proper access control and ensuring that the SYSADMIN can perform necessary administrative tasks on objects created by users with the custom role.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Section 1.3 - SnowPro Core Certification Study Guide1

Fail-safe is a feature in Snowflake that provides an additional layer of data protection. After the Time Travel retention period ends, Fail-safe offers a non-configurable 7-day period during which historical data may be recoverable by Snowflake. This period is designed to protect against accidental data loss and is not intended for customer access.

References:Understanding and viewing Fail-safe | Snowflake Documentation