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

The correct answer is B. A string value should be specified to represent NULL values using the NULL_IF option .

When unloading data from Snowflake into files using the COPY INTO < location > command, Snowflake needs a textual representation for SQL NULL values in the unloaded files. This can be controlled by the NULL_IF file format option.

Why B is correct:

The NULL_IF option specifies one or more string values that represent SQL NULL values. During unloading, Snowflake uses this option to determine how NULL values should appear in the output files.

For example, a file format could specify:

NULL_IF = ( ' NULL ' )

This means SQL NULL values can be written using the string NULL in the unloaded file.

Why the other options are incorrect:

A. Snowflake does not always automatically convert NULL values to empty strings in all unload scenarios. The representation depends on file format settings.

C. NULL values do not cause the unload process to stop with an error by default.

D. NULL values are not automatically skipped during unloading. They are included in the output using the configured representation.

Official Snowflake documentation reference:

Snowflake documentation for file format options explains that NULL_IF specifies the string used to represent SQL NULL values when loading or unloading data.

 Even if a developer is granted ownership of a table with a masking policy, they will not be able to modify the table to read the masked data if their role does not have the necessary permissions. Ownership of a table does not automatically confer the ability to alter masking policies, which are designed to protect sensitive data. Masking policies are applied at the schema level and requirespecific privileges to modify12.

A Directory Table stores metadata about unstructured data files in a stage, such as size, last-modified timestamp, and file URL. This metadata helps query or track files without ingesting them into a table.

TheSearch Optimization Servicein Snowflake enhances query performance by creating apersistent data structurethat enables faster access to specific data, particularly for queries with selective filters oncolumns not often used in clustering. This persistent structure accelerates data retrieval without depending on clustering or caching, thereby improving response times for targeted queries.

Snowflake ' s micro-partitioning automatically manages table structure, but search optimization allows further enhancement for certain high-frequency, specific access patterns.

 A Pre-signed URL should be used to access files stored in a Snowflake stage without requiring authentication into Snowflake. Pre-signed URLs are simple HTTPS URLs that provide temporary access to a file via a web browser, using a pre-signed access token. The expiration time for the access token is configurable, and this type of URL allows users or applications to directly access or download the fileswithout needing to authenticate into Snowflake5.

The search optimization service in Snowflake is designed to improve the performance of selective point lookup queries on large tables, which is ideal for scenarios where a query returns a small amount of data from a very large base table1. References: [COF-C03] SnowPro Core Certification Exam Study Guide

In Snowflake, a tabular User-Defined Function (UDF) is defined with a return clause that includes the keyword “TABLE.” This indicates that the UDF will return a set of rows, which can be used in the FROM clause of a query. References: Based on my internal knowledge as of 2021.

Pruning in Snowflake refers to the process of filtering or disregarding micro-partitions that are not needed to satisfy the conditions of a query. This optimization technique helps reduce the amount ofdata scanned, thereby improving query performance

 The OBJECT_CONSTRUCT function is used in Snowflake to create a JSON object from relational data, which is useful when unloading semi-structured data. The SINGLE = TRUE parameter is used when unloading data to ensure that the data is exported as a single file, which can be up to 5 GB in size. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The DATA_SHARING_USAGE schema includes views that display information about listings published in the Snowflake Marketplace or a data exchange, which includes DATA_TRANSFER_HISTORY and LISTING_TELEMETRY_DAILY2.

The correct answer is C. Casts the value from a VARIANT to a specified data type .

In Snowflake, the :: operator is the cast operator. It converts a value from one data type to another. When working with semi-structured data, it is commonly used to cast values extracted from a VARIANT column into standard SQL data types such as STRING, NUMBER, BOOLEAN, or TIMESTAMP.

Why C is correct:

A VARIANT value often needs to be explicitly cast before it is used in comparisons, calculations, or formatted output.

Example:

SELECT src:customer_id::NUMBER,

src:name::STRING

FROM my_table;

In this example:

src:customer_id::NUMBER

extracts customer_id from a VARIANT column and casts it to NUMBER.

Why the other options are incorrect:

A. :: does not specifically convert every VARIANT into an object. It casts to whichever data type is specified.

B. Extracting a value from a VARIANT uses path notation or functions such as GET and GET_PATH; :: performs casting.

D. :: has nothing to do with random sampling.

Official Snowflake documentation reference:

Snowflake documentation describes :: as the cast operator and shows its use for converting semi-structured VARIANT values to explicit SQL data types.

 When the DROP MANAGED ACCOUNT command is executed in Snowflake, it removes the managed account, including all objects created within the account, and access to the account is immediately restricted2.

To determine which user has accessed a database object containing sensitive information, a Snowflake administrator can query the ACCESS_HISTORY view in the ACCOUNT_USAGE schema, which provides information about access to database objects3.

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.

The Activity area of Snowsight includes the Query History page, which allows users to monitor and view details about queries executed in their account, including performance data1. It also includesthe Automatic Clustering History, which provides insights into the automatic clustering operations performed on tables2.