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

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.

The Search Optimization Service can improve highly selective searches against supported values stored in semi-structured columns such as VARIANT, OBJECT, and ARRAY. It maintains a search-access structure that helps Snowflake locate qualifying micro-partitions without scanning all candidate data.

A materialized view can precompute frequently used expressions or extracted values from semi-structured data. Repeated queries can then access the materialized representation instead of repeatedly performing expensive extraction or transformation operations against the full source table.

A clustering key cannot simply be defined directly on a VARIANT, OBJECT, or ARRAY value. In some designs, a supported expression that extracts and converts a scalar value can be used for clustering, but option B as written is not generally correct.

Query Acceleration Service can help certain eligible scan-intensive queries, but it is not the semi-structured-data-specific optimization represented by this question. Multi-cluster warehouses improve concurrency rather than the execution speed of one query.

In Snowflake, aTemporarytable is a type of table that is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is automatically dropped at the end of the session in which it was created. Temporary tables are designed for transient data processing needs, where data is needed for the duration of a specific task or session but not beyond. Since they are automatically cleaned up at the end of the session, they help manage storage usage efficiently and ensure that sensitive data is not inadvertently persisted.

Zero-copy cloning initially shares micro-partitions between the source object and the clone. However, when data is modified in either the original or cloned object, Snowflake creates new micro-partitions for the changed data. Those modified micro-partitions consume additional storage.

Snowsight, the Snowflake user interface, supports a variety of chart types for data visualization, including line charts and pie charts. These chart types help users analyze and interpret data more effectively.

Line Charts:Useful for showing trends over time or comparing different data sets.

Pie Charts:Ideal for displaying proportions and percentages within a dataset.

Under the Economy scaling policy, Snowflake delays starting a new cluster until it ' s sure the queries will utilize it effectively for ~6 minutes . This reduces costs but may slightly delay query execution.

Snowflake implements Discretionary Access Control (DAC) by using a role-based access control model. In this model, access privileges are not directly assigned to individual objects or users but are encapsulated within roles. These roles are then assigned to users, effectively granting them the access privileges contained within the role. This approach allows for granular control over database access, making it easier to manage permissions in a scalable and flexible manner.

When loading data into Snowflake, the file format options specified in theCOPY INTO < table > statement take the highest precedence over other locations such as the stage or table definitions. This ensures that any specific settings for a particular load operation are applied correctly.

File Format Hierarchy:

Stage Definition:Specifies default file format options for files staged in the location.

Table Definition:Can specify default file format options associated with the table.

COPY INTO Statement:Overrides both the stage and table definitions with the file format options specified directly in the statement.

Example Usage:

COPY INTO my_table

FROM @my_stage

FILE_FORMAT = (FORMAT_NAME = ' my_format ' FIELD_OPTIONALLY_ENCLOSED_BY = ' " ' );

The correct answer is D. To return the first non-NULL expression in an argument .

COALESCE evaluates its arguments from left to right and returns the first expression that is not NULL. If all expressions are NULL, it returns NULL.

Why D is correct:

SELECT COALESCE(phone_mobile, phone_home, phone_work, ' No phone ' ) AS contact_phone

FROM customers;

This returns the first available non-NULL phone number. If all phone columns are NULL, it returns ' No phone ' .

Why the other options are incorrect:

A. Splitting strings is handled by functions such as SPLIT.

B. Removing duplicates is handled with DISTINCT, grouping, or deduplication logic.

C. Joining column values is usually done with concatenation functions or operators, such as CONCAT.

Official Snowflake documentation reference:

Snowflake documentation describes COALESCE as a conditional expression that returns the first non-NULL expression among its arguments.

The MERGE command in Snowflake is used for incremental loading based on column values in a source table or subquery. It enables the insertion, updating, or deletion of records in a target table depending on whether matching rows are found, making it ideal for loading data that changes incrementally, such as daily updates or modifications.

The correct answers are A, B, and D , assuming SNOWFLAKE is a table or queryable object.

Important exam-note correction:

This question is written as a single-answer question, but more than one listed command can consume virtual warehouse credits. Any SELECT query that scans or processes table data generally requires a running virtual warehouse and can consume warehouse credits.

Why A, B, and D are correct:

These are SELECT statements against a table-like object. Query execution that scans or aggregates data uses compute resources from a virtual warehouse.

Examples:

SELECT MAX(UUID) FROM SNOWFLAKE;

SELECT COUNT(FLAKE_ID) FROM SNOWFLAKE GROUP BY UUID;

SELECT COUNT(*) FROM SNOWFLAKE;

These involve SQL query processing and therefore require warehouse compute unless the result is fully served from cache.

Why C is incorrect:

SHOW commands are metadata commands handled by Snowflake’s cloud services layer. They do not require an active virtual warehouse in the same way table-scanning SELECT queries do.

Official Snowflake documentation reference:

Snowflake documentation explains that virtual warehouses provide the compute resources required to execute SQL queries and DML operations. Metadata commands such as SHOW are handled by the cloud services layer.

Auto-suspend ensures that a warehouse automatically suspends when idle, reducing compute costs.

Statement timeout prevents long-running queries from consuming excessive credits.

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.