COF-C03 Exam Dumps - Snowflake SnowPro Core Certification Questions and Answers

The Parquet file format is known for preserving the precision of floating-point numberswhen data is unloaded, preventing truncation of the values3.

 To monitor data storage for individual tables, the commands and objects that can be used are ‘SHOW STORAGE BY TABLE;’ and the Information Schema view ‘TABLE_STORAGE_METRICS’. These tools provide detailed information about the storage utilization for tables. References: Snowflake Documentation

Snowflake recommends using columns with very high cardinality and those that are most actively used in selective filters as clustering keys. High cardinality columns have a wide range of unique values, which helps in evenly distributing the data across micro-partitions. Columns used in selective filters help in pruning the number of micro-partitions to scan, thus improving query performance. References: Based on general database optimization principles.

 Snowflake supports three types of internal stages: Named, User, and Table stages. These stages are used for staging data files to be loaded into Snowflake tables. Schema, Stream, and Database stages are not supported as internal stages inSnowflake. References: Snowflake Documentation1.

 Snowsight automatically detects if a target account is in a different region and enables cross-cloud auto-fulfillment when using a paid listing on the Snowflake Marketplace. This feature allows Snowflake to manage the replication of data products to consumer regions as needed, without manual intervention1.

To find out which numeric values are present in both tables, the INTERSECT set operator should be used. This operator returns rows from one query’s result set which also appear in another query’s result set, effectively finding the common elements between the two tables45.

The bulk data load history available after a COPY statement is stored in the metadata of the pipe and is retained for 64 days1.

The user property ‘MINS TO BYPASS MFA’ in Snowflake allows temporary bypass of MFA for a user,which can be set by an account administrator without contacting Snowflake Support2.

When a tag object is assigned to a table, using the statement CREATE TABLE < table_name > AS SELECT * FROM TABLE_A will automatically assign the TABLE_A tag to the newly created table2.

OPERATE. In Snowflake, to pause or resume a pipe, theOPERATEprivilege is required on the pipe object. TheOPERATEprivilege allows users to perform operational tasks on specific objects such as pipes, tasks, and streams. Specifically, for a pipe, theOPERATEprivilege enables the user to execute theALTER PIPE ... SET PIPE_EXECUTION_PAUSED=TRUEorALTER PIPE ... SET PIPE_EXECUTION_PAUSED=FALSEcommands, which are used to pause or resume the pipe, respectively.

Here ' s a step-by-step explanation and reference:

Understanding Pipe Operations:Pipes in Snowflake are used for continuous data loading from staging areas into Snowflake tables. Managing pipes involves operations such as creating, monitoring, pausing, and resuming.

Privileges for Pipe Operations:TheOPERATEprivilege is essential for pausing and resuming pipes. This privilege is more specific than general object access privileges likeSELECTorUSAGEand is tailored for operational control.

Granting the OPERATE Privilege:To grant theOPERATEprivilege on a pipe, an administrator or a user with the necessary grants can execute the SQL command:

GRANTOPERATEONPIPE < pipe_name > TOROLE < role_name > ;

Pausing and Resuming Pipes:Once theOPERATEprivilege is granted, the user or role can pause the pipe using:

ALTERPIPE < pipe_name > SETPIPE_EXECUTION_PAUSED=TRUE;

To resume the pipe, they use:

ALTERPIPE < pipe_name > SETPIPE_EXECUTION_PAUSED=FALSE;

A materialized view in Snowflake gets suspended when structural changes that could impact the view ' s integrity are made to the base table, such asB. When a column is dropped from the base table. Dropping a column from the base table on which a materialized view is defined can invalidate the view ' s data, as the view might rely on the column that is being removed. To maintain data consistency and prevent the materialized view from serving stale or incorrect data, Snowflake automatically suspends the materialized view.

Upon suspension, the materialized view does not reflect changes to the base table until it is refreshed or re-created. This ensures that only accurate and current data is presented to users querying the materialized view.

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.

Theparameter DATA_RETENTION_TIME_IN_DAYS can be set at the account level to define the minimum number ofdays Snowflake retains historical data for Time Travel1.

 Snowflake supports several semi-structured data formats for loading data. The supported formats include JSON, Avro, and Parquet12. These formats allow for efficient storage and querying of data that does not conform to a traditional relational database schema.

The semi-structured data function that interprets an input string as a JSON document and produces a VARIANT value is PARSE_JSON. This function is used to parse a JSON formatted string and return it as a VARIANT data type, which can then beused for further processing within Snowflake3.

 When deciding on a clustering key for a table, Snowflake recommends using the columns that are most actively used in the select filters. This is because clustering by these columns can improve the performance of queries that filter on these values, leading to more efficient scans and better overall query performance2. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 In a multi-cluster warehouse with an economy scaling policy, queries will wait in the queue for 2 minutes before another cluster is started. This is to minimize costs by allowing queries to queue up for a short period before adding additional compute resources. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 Snowflake’s column level security features include Dynamic Data Masking and External Tokenization. Dynamic Data Masking uses masking policies to selectively mask data at query time, while External Tokenization allows for the tokenization of data before loading it into Snowflake and detokenizing it at query runtime5.

 In theInformation Schema and Account Usage, the column that identifies whether a view is secure or not is IS_SECURE2.