Free Practice Questions for Snowflake COF-C02 Exam

 Transient tables in Snowflake have a minimum Fail-safe retention time period of 0 days. This means that once the Time Travel retention period ends, there is no additional Fail-safe period for transient tables

The UNDROP command in Snowflake can be used to directly restore Views and Tables. These objects, when dropped, are moved to a ‘Recycle Bin’ where they can be restored within a time limit before they are permanently deleted. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 Snowpipe retains load history for 14 days. This allows users to view and audit the data that has been loaded into Snowflake using Snowpipe within this time frame3.

 The supported file formats for unloading data from Snowflake include JSON, Parquet, and CSV. These formats are commonly used for their flexibility and compatibility with various data processing tools

 In Snowflake, each size increase in virtual warehouses doubles the number of servers. Therefore, if a sizeSmall virtual warehouse is made up of two servers, a Large warehouse, which is two sizes larger, would be made up of eight servers (2 servers for Small, 4 for Medium, and 8 for Large)2.

Size specifies the amount of compute resources available per cluster in a warehouse. Snowflake supports the following warehouse sizes:

https://docs.snowflake.com/en/user-guide/warehouses-overview.html

 In Snowflake, a schema is a logical grouping of database objects, which can include streams and external tables. A stream is an object that allows users to query data that has changed in specified tables or views, and an external table is a table that references data stored outside of Snowflake. Roles, warehouses, users, and shares are not contained within a schema. References: SHOW OBJECTS, Database, Schema, & Share DDL

The responsibilities of Snowflake’s Cloud Service layer include authentication (A), which ensures secure access to the platform; resource management (B), which involves allocating and managing compute resources; and query parsing and optimization (D), which improves the efficiency and performance of SQL query execution3.

 In Snowflake’s access control model, the default owner of an object is the role that was used to create the object. This role has the OWNERSHIP privilege on the object and can grant access to other roles1

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

The parse_json() function in Snowflake interprets an input string as a JSON document and produces a VARIANT value containing the JSON document. This function is specifically designed for parsing strings that contain valid JSON information3.

Best practices for using the ACCOUNTADMIN role include ensuring that all users with this role use Multi-factor Authentication (MFA) for added security. Additionally, it is recommendedto assign the ACCOUNTADMIN role to at least two users to avoid delays in case of password recovery issues, but to as few users as possible to maintain strict control over account-level operations4.

 Snowflake’s architecture is described as a multi-cluster, shared data architecture. This design combines the simplicity of a shared-disk architecture with the performance and scale-out benefits of a shared-nothing architecture, using a central repository accessiblefrom all compute nodes2.

References = [COF-C02] SnowPro Core Certification Exam Study Guide, Snowflake Documentation

To go back to the first version of a transient table created three days prior, one can use Time Travel if the DATA_RETENTION_TIME_IN_DAYS was set to at least 3 days. This allows the user to access historical data within the specified retention period. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 Snowflake stored procedures can be created to execute with the privileges of the role that owns the procedure (owner’s rights) or with the privileges of the role that calls the procedure (caller’s rights). This allows for flexibility in managing security and access control within Snowflake1.

 The GET command is used to download files from a Snowflake stage to a local folder on a client’s machine2.

 The COPY INTO < location >  command is used to unload data from a Snowflake database table into one or more files in a Snowflake stage1.

 To traverse semi-structured data in Snowflake, a user can insert a colon (:) between the VARIANT column name and any first-level element. This path syntax is used to retrieve elements in a VARIANT column4.

To reduce data spilling in Snowflake, using a larger virtual warehouse is effective because it provides more memory and local disk space, whichcan accommodate larger data operations and minimize the need to spill data to disk or remote storage1. References: [COF-C02] SnowPro Core Certification Exam Study Guide

In Snowflake, to extract a specific value from a VARIANT column, you use the column name followed by a colon and then the key. The keys are case-sensitive. Therefore, to extract the employee’s name from the “Data” column, the correct syntax is data:employee.name.

Views can be used with Secure Data Sharing in Snowflake. Materialized views, external tables, and UDFs are not typically shared directly for security and performance reasons2.