Free Snowflake COF-C03 Practice Exam with Questions & Answers | Set: 4

When unloading data in Snowflake, it is essential to differentiate between empty strings and NULL values to preserve data integrity. The parameters FIELD_OPTIONALLY_ENCLOSED_BY and EMPTY_FIELD_AS_NULL are used together to address this:

FIELD_OPTIONALLY_ENCLOSED_BY: This parameter specifies the character used to enclose fields, which can differentiate between empty strings (as enclosed fields) and NULLs.

EMPTY_FIELD_AS_NULL: By setting this parameter, Snowflake interprets empty fields as NULL values when unloading data, ensuring accurate representation of NULLs versus empty strings.

These parameters are crucial when exporting data for systems that need explicit differentiation between NULL and empty string values.

The correct answer is C. Snowflake Cortex .

Snowflake Cortex provides AI and large language model capabilities inside Snowflake. It includes features that support natural language interaction, text analysis, document processing, search, summarization, classification, and other AI-powered workflows.

Why C is correct:

Snowflake Cortex is the Snowflake AI feature family used for natural language and unstructured data use cases. Cortex capabilities can be used to analyze text and other unstructured data using natural language functions and AI services.

Why the other options are incorrect:

A. Snowsight is Snowflake’s web interface. It can expose AI-assisted features, but the underlying AI capability is Snowflake Cortex.

B. Snowflake ML supports machine learning workflows but is not the primary feature family for natural language querying of unstructured data.

D. Snowflake Horizon Catalog is focused on governance, discovery, and catalog capabilities, not natural language analysis of unstructured data.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake Cortex as the set of intelligent, AI-powered features that use large language models and ML to analyze data, including unstructured text data.

The GET_STAGE_LOCATION function retrieves the full external URL associated with a named stage. It is useful for accessing the storage location of external stages configured in Snowflake.

Snowflake bills data storage monthly in units of compressed terabytes (TB) . Usage below 1 TB is prorated.

The correct answer is A. CREATE TABLE CUSTOMERS_CLONE LIKE CUSTOMERS; .

The CREATE TABLE ... LIKE command creates a new table with the same column definitions as an existing table, but it does not copy the data from the source table.

Why A is correct:

CREATE TABLE CUSTOMERS_CLONE LIKE CUSTOMERS; creates an empty table with the same structure as CUSTOMERS.

Why the other options are incorrect:

B. CREATE TABLE ... CLONE creates a zero-copy clone of the source table, including access to the source table’s existing data at the time of cloning.

C. This creates a temporary table with one column named CLONE; it does not create a table based on the structure of CUSTOMERS.

D. CREATE TABLE ... AS SELECT creates a new table and inserts the result of the query, so it copies data from CUSTOMERS.

Official Snowflake documentation reference:

Snowflake documentation explains that CREATE TABLE ... LIKE creates a new table with the same column definitions as another table, but without copying data.

TheREMOVEcommand is used in Snowflake to delete files from a stage after they have been successfully ingested into Snowflake tables. This command helps manage storage by allowing users to clean up staged files that are no longer needed, ensuring that the stage does not accumulate unnecessary data over time. UnlikeDELETE,DROP, orTRUNCATEcommands, which are used for managing data within Snowflake tables or dropping objects,REMOVEspecifically targets the management of files in stages.

The correct answer is C. The ACCOUNT_USAGE schema does not provide real-time results .

The ACCOUNT_USAGE schema contains historical metadata and usage information for the Snowflake account, but its views have latency. This means recently executed queries may not appear immediately.

Corrected wording:

The object is typically referred to as the QUERY_HISTORY view in the ACCOUNT_USAGE schema, not QUERY_HISTORY_VIEW.

Why C is correct:

If queries ran only 10 minutes ago, they may not yet appear in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY because ACCOUNT_USAGE views are not real-time. For more immediate query history, users can use the INFORMATION_SCHEMA.QUERY_HISTORY table function, subject to its scope and retention limits.

Why the other options are incorrect:

A. Running queries on a multi-cluster warehouse does not prevent query history from being recorded.

B. Queries using the result cache can still appear in query history.

D. If the user lacked privileges, they would generally be unable to query the view or would have limited visibility, but the key clue is that the queries ran in the past 10 minutes.

Official Snowflake documentation reference:

Snowflake documentation states that ACCOUNT_USAGE views can have latency and should not be expected to return real-time data.

The correct answer is C. Role_A can grant or revoke access to the role .

In Snowflake, the OWNERSHIP privilege on an object gives full control over that object. A role can own another role. If Role_A owns Role_B, then Role_A has control over Role_B, including the ability to manage grants involving that role.

Why C is correct:

Ownership of a role allows the owning role to manage the role. This includes granting the role to users or other roles and revoking those grants.

Why the other options are incorrect:

A. Ownership of a role does not automatically mean the owner inherits all privileges assigned to that role. Privilege inheritance happens through role grants in the role hierarchy, not merely through ownership.

B. Role_A can use Role_B privileges only if Role_B is granted to Role_A in the role hierarchy. Ownership alone is not the same as inheritance.

D. A task requires appropriate task privileges, such as OPERATE or ownership, and account-level task execution privileges where applicable. Ownership of a role does not automatically allow executing tasks assigned to that role.

Official Snowflake documentation reference:

Snowflake documentation explains that roles are securable objects and that the OWNERSHIP privilege grants full control over an object. Role inheritance is separate and occurs when one role is granted to another role.

The ORGADMIN role in Snowflake is responsible for managing organization-level administrative functions, which include managing billing and credit data. This role has the highest level of administrative privileges and can oversee multiple Snowflake accounts within an organization.

Snowflake Documentation: Account and Organization Roles

To load local files into Snowflake:

First, use the PUT command to upload files from local storage to an internal stage .

Then, use the COPY INTO < table > command to load data from the internal stage into the table.

The correct answer is A. Use the OBJECT_CONSTRUCT function with the COPY INTO < location > command .

When unloading relational data to JSON, each relational row must be transformed into a single semi-structured object. OBJECT_CONSTRUCT creates a JSON-style object from the row’s columns, and COPY INTO < location > unloads the result to a stage.

Why A is correct:

Snowflake requires unloaded JSON data to be represented as a single VARIANT, OBJECT, or ARRAY value per row. OBJECT_CONSTRUCT converts relational columns into an object suitable for JSON unload.

Example:

COPY INTO @my_stage/output/

FROM (

SELECT OBJECT_CONSTRUCT(*)

FROM customers

)

FILE_FORMAT = (TYPE = JSON);

Why the other options are incorrect:

B. Setting the file format to JSON alone is not sufficient for relational rows with multiple columns. The rows must be converted into a single semi-structured column.

C. PUT uploads local files to an internal stage. It does not unload table data.

D. GET downloads files from an internal stage to a local machine. It does not unload table data from Snowflake.

Official Snowflake documentation reference:

Snowflake documentation explains that to unload relational data to JSON, the query should return a single column containing semi-structured data, commonly produced with OBJECT_CONSTRUCT.

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 Search Optimization Service is useful for selective point lookup or highly selective filter queries. Since one team frequently filters on region and retrieves a small number of rows from a very large table, search optimization is the best targeted solution.