Free Snowflake COF-C02 Exam Dumps

Snowflake stores metadata for rows in micro-partitions, including the range of values for each column and the number of distinct values1.

Data Encryption in Snowflake:

A . After micro-partitioning: Data stored in Snowflake is encrypted at rest after it is organized into micro-partitions.

C . After loading into an internal stage: Snowflake encrypts all data stored in internal stages automatically.

Why Other Options Are Incorrect:

B . After loading into a table: Encryption occurs during the micro-partitioning stage, which precedes this step.

D . External stage: Data encryption in external stages depends on the external storage provider, not Snowflake.

E . Only when using an encrypted stage: This is incorrect because encryption is automatic for internal stages and tables.

Data Encryption in Snowflake

In SQL, NULL values are not equal to other NULL values. To make comparisons work in join operations, the NVL function can replace NULL with a non-NULL value.

Example:

SELECT *

FROM table1 t1

JOIN table2 t2

ON NVL(t1.col, 'default') = NVL(t2.col, 'default');

Why Other Options Are Incorrect:

B . Equal_null function: No such function exists in Snowflake SQL.

C . NULLIF function: Used to return NULL when two values are equal, not for handling NULL in joins.

D . IFNULL function: Similar to NVL but less commonly used in this context.

NVL Function Documentation

Transient Tables:

These are storage objects designed to store data beyond a single session.

They do not incur Fail-safe costs, making them more cost-effective than permanent tables for temporary or intermediate data storage.

Why Other Options Are Incorrect:

A . Permanent table: Supports Fail-safe and incurs additional costs.

B . External table: Stores metadata for external data but does not store actual data.

C . Temporary table: Data persists only within a session and is not suitable for multi-session use.

Transient Tables Documentation

Object-level access control is available in all Snowflake editions.

This feature allows administrators to grant and revoke permissions on specific objects (e.g., tables, schemas, databases) to control access at a granular level.

Other features like data masking policies and customer-managed encryption keys are available only in higher Snowflake editions.

Snowflake Documentation: Object-Level Access Control

Snowflake Editions Comparison

Stored procedures can execute both DDL (Data Definition Language) and DML (Data Manipulation Language) statements.

Example: A stored procedure can create a table (DDL) and insert data into it (DML) in the same execution block.

Other objects like UDFs and UDTFs are limited to returning values or sets of rows, and cannot perform DDL/DML operations.

Snowflake Documentation: Stored Procedures

To automate virtual warehouse operations for optimal credit consumption:

A . Auto-resume: Automatically resumes a suspended warehouse when a query is in a queue.

E . Auto-suspend: Automatically suspends the warehouse after a period of inactivity, reducing unnecessary credit usage.

Snowflake Documentation: Virtual Warehouse Automation

Warehouse Auto-Suspend and Auto-Resume

The TABLE_STORAGE_METRICS view in Snowflake provides details about table storage, including owned storage (storage exclusively used by the table) and referenced storage (shared storage due to cloning or Time Travel).

This view helps distinguish between data directly owned by the table and data shared with other tables through features like cloning.

Why Other Options Are Incorrect:

A . DATABASE_STORAGE_USAGE_HISTORY: Tracks storage usage at the database level, not specific to tables or cloning.

B . STAGE_STORAGE_USAGE_HISTORY: Tracks storage usage for stages, not tables.

D . STORAGE_USAGE: Provides high-level account storage details, not specific to cloning or tables.

TABLE_STORAGE_METRICS Documentation

Owner's Rights Stored Procedures:

When a stored procedure with owner's rights is called, it runs with the privileges of the owner, not the caller.

This ensures that the procedure executes with access to objects the owner has privileges for, regardless of the caller's permissions.

Why Other Options Are Incorrect:

B . The owner can set the caller's session variables: Stored procedures cannot modify session variables of the caller.

C . The owner will inherit the caller's current virtual warehouse: The procedure uses the session's current warehouse, not inherited settings.

D . The owner can view the caller's session variables: Session variables are private to the caller and not visible to the owner.

Stored Procedure Privileges Documentation