Free COF-C02 Practice Test

Snowflake's best practices for access control recommend a hierarchical role structure to enforce the principle of least privilege and separation of duties. The SYSADMIN role is designated for creating account-level objects like databases and warehouses. For objects within databases (e.g., schemas, tables), the recommended approach is to create them using a custom role. This custom role, which owns the objects, is then granted to the SYSADMIN role. This model separates object ownership from the administrative privileges of SYSADMIN, creating a more manageable and secure environment. Using ACCOUNTADMIN for object creation is strongly discouraged as it is the most powerful role, intended only for account-level management.

A. The ACCOUNTADMIN role is overly privileged for routine object creation; its use should be limited to high-level account management tasks.
C. The SECURITYADMIN role's purpose is to manage users, roles, and grants, not to own or create database objects.
E. Managed access schemas centralize privilege management but are not exclusively for objects owned by ACCOUNTADMIN; in fact, ACCOUNTADMIN should not own such objects.

1. Snowflake Documentation: Configuring Access Control. This guide explicitly recommends the model described in option D. It states, "In this model, custom roles... are created and used to create objects in the database. These object-owning roles are then granted to the SYSADMIN role, creating a hierarchy of roles."

2. Snowflake Documentation: System-Defined Roles. This document outlines the intended purpose of each system role. It specifies that SYSADMIN "has privileges to create warehouses and databases in an account," supporting option B. It also clarifies that ACCOUNTADMIN is for top-level control and SECURITYADMIN is for user and role management, invalidating options A and C.

User-Defined Functions (UDFs) in Snowflake are categorized by the type of value they return. A UDF can return a single value, such as an integer, string, or variant; this is known as a scalar function. Alternatively, a UDF can return a set of rows, effectively behaving like a table in the FROM clause of a query; this is known as a tabular function, or User-Defined Table Function (UDTF). These are the two fundamental return types for UDFs.

A. Dictionary is a data structure in languages like Python. In Snowflake, this would be represented by the OBJECT data type, making 'Object' the more correct term.
B. List is a data structure in programming languages. The equivalent SQL data type in Snowflake is ARRAY, which is a type of scalar value.
C. An OBJECT is a specific data type that can be returned by a scalar UDF. 'Scalar' is the broader, correct category that encompasses OBJECT, INTEGER, VARCHAR, etc.

1. Snowflake Documentation. (n.d.). Introduction to User-Defined Functions. In Development. Retrieved from https://docs.snowflake.com/en/developer-guide/udf/udf-overview. "A user-defined function (UDF) returns either a single (scalar) value or a set of rows (tabular value)."

2. Snowflake Documentation. (n.d.). SQL UDFs. In Development. Retrieved from https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql. The documentation distinguishes between "Scalar UDFs (UDFs)" and "Tabular UDFs (UDTFs)".

Snowflake's Secure Data Sharing enables data providers to share live, read-only data with consumers without creating copies. The primary mechanism for securely exposing this data is through secure views. A provider creates a share and grants it privileges on specific database objects. By using a secure view, the provider can control exactly which columns and rows are visible to the consumer. Crucially, secure views hide the underlying query logic and base table information from the consumer, providing a robust layer of security and abstraction. This makes them the standard and recommended method for sharing data that resides in a provider's database.

A. External tables: External tables are used to query data stored in external cloud storage (e.g., S3, Azure Blob) as if it were in Snowflake, not for sharing data between Snowflake accounts.
C. Materialized views: Materialized views are primarily a performance optimization feature that pre-computes query results. While they can be included in a share, secure views are the specific tool designed for secure data abstraction in sharing scenarios.
D. User-Defined Functions (UDFs): UDFs are used to share custom logic or code, not the underlying datasets (tables/views) themselves. A provider shares data via tables or views, and can optionally share UDFs. ---

1. Snowflake Documentation: Introduction to Secure Data Sharing

Reference: "Using secure objects

particularly secure views and secure UDFs

you can share data with consumers while preventing them from seeing the definitions of the objects."

Source: Snowflake Inc.

"Introduction to Secure Data Sharing"

https://docs.snowflake.com/en/user-guide/data-sharing-intro.

2. Snowflake Documentation: Secure Views

Reference: Under the section "Data Sharing and Secure Views"

it states: "When you share a secure view

the view definition is not exposed to the data sharing consumer. This is useful if you want to hide the complexities of the underlying table(s) and view definition

or for security reasons."

Source: Snowflake Inc.

"Working with Secure Views"

https://docs.snowflake.com/en/user-guide/views-secure#data-sharing-and-secure-views.

3. Snowflake Documentation: Working as a Data Provider

Reference: Step 2 of the provider workflow is: "Determine which existing database objects (tables

secure views

and secure UDFs) you wish to share." This explicitly lists secure views as a primary object type for sharing.

Source: Snowflake Inc.

"Working as a Data Provider"

https://docs.snowflake.com/en/user-guide/data-sharing-providers.

Snowflake's COPY INTO command is used to unload (export) data from a table into files in an internal or external stage. The command explicitly supports three file formats for this operation: CSV (the default), JSON, and Parquet. These formats are specified using the TYPE file format option within the command. While Snowflake supports loading data from other formats like ORC, AVRO, and XML, these are not available as output formats for unloading operations.

A. XML: Snowflake supports loading semi-structured XML data but does not support unloading data into the XML format.
D. ORC: ORC is a supported format for loading data into Snowflake tables, but it is not a supported format for unloading data.
E. AVRO: AVRO is a supported format for loading data into Snowflake tables, but it is not a supported format for unloading data.

1. Snowflake Documentation. (n.d.). COPY INTO . Retrieved from https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.

Reference Details: In the "Format Type Options (formatTypeOptions)" section

under the TYPE = fileformattype parameter

the documentation explicitly lists the supported values for unloading as CSV

JSON

and PARQUET.

2. Snowflake Documentation. (n.d.). Introduction to Data Unloading. Retrieved from https://docs.snowflake.com/en/user-guide/data-unload-overview.

Reference Details: The "Supported File Formats for Unloading" section states: "The COPY INTO command supports unloading data from a table into files in the following formats: CSV (comma-delimited)

JSON

Parquet."

To allow a user to change the size of a virtual warehouse, the MODIFY privilege is required. This privilege grants the ability to alter a warehouse's properties, including its size, auto-suspend/resume settings, and other parameters. According to Snowflake's Role-Based Access Control (RBAC) model, it is a best practice to grant privileges to roles rather than directly to individual users. The statement in option B correctly grants the MODIFY privilege on the specified warehouse to a role, which aligns with the principle of least privilege and manageable access control.

A. ALLOW is not a valid SQL command for granting privileges in Snowflake; GRANT is used. Also, RESIZE is not a valid, grantable privilege.
C. While this command would technically work, granting privileges directly to a user is not a recommended best practice in Snowflake's RBAC model. Privileges should be assigned to roles for better management and scalability.
D. The OPERATE privilege allows a role to start, stop, suspend, or resume a warehouse, but it does not grant permission to alter its properties, such as changing its size.

1. Snowflake Documentation: Access Control Privileges - Virtual Warehouse Privileges

Section: Virtual Warehouse Privileges

Content: The documentation explicitly lists the privileges for virtual warehouses. It states that the MODIFY privilege "Enables altering a warehouse. This includes changing its size...". It also defines the OPERATE privilege as enabling the changing of a warehouse's state (stop

start

suspend

resume)

confirming it is not the correct privilege for resizing.

2. Snowflake Documentation: Overview of Access Control

Section: Key Concepts Related to Access Control - Roles

Content: This section emphasizes that "Privileges are granted to roles

which are in turn assigned to users. All users in a role inherit the privileges granted to that role." This supports why granting to a ROLE (Option B) is the standard and preferred method over granting to a USER (Option C).

3. Snowflake Documentation: GRANT

Section: Syntax

Content: The official syntax for the GRANT command is provided

confirming that GRANT ON WAREHOUSE TO ROLE ; is the correct structure. This invalidates the syntax used in option A (ALLOW...RESIZE).

Snowflake's native Multi-Factor Authentication (MFA) feature is implemented through a direct, built-in integration with the Duo Security service. This integration allows users to enhance their login security by providing a second factor of authentication. When MFA is enabled for a user, Snowflake leverages Duo to facilitate authentication methods such as Duo Push notifications, Time-based One-Time Passwords (TOTP), and SMS passcodes. This is a distinct feature from federated authentication or Single Sign-On (SSO) and is managed directly within Snowflake user properties.

B. OAuth: OAuth is an open standard for access delegation, commonly used to grant websites or applications access to information on other websites without giving them the passwords. It is an authorization framework, not an MFA service.
C. Okta: Okta is an Identity Provider (IdP) that can be configured for federated authentication and Single Sign-On (SSO) with Snowflake. While Okta provides its own MFA, it operates as part of an SSO solution, which is an alternative to Snowflake's native authentication, not an enhancement to it.
D. Single Sign-On (SSO): SSO is an authentication scheme that allows a user to log in with a single set of credentials to multiple independent software systems. It is a method of authentication, not a specific service that enhances Snowflake's built-in MFA.

1. Snowflake Documentation: Multi-factor Authentication (MFA).

Section: Overview of Multi-factor Authentication (MFA)

Quote: "To provide more authentication security

Snowflake supports multi-factor authentication (MFA). Snowflake uses the Duo Security service to provide MFA

which can be enabled for individual users."

2. Snowflake Documentation: Managing Multi-factor Authentication (MFA).

Section: Enabling MFA for Your User Account

Quote: "After your account administrator enables MFA for your user

you must enroll in MFA through the web interface. Snowflake’s built-in integration with Duo Security allows using Duo Push

TOTP passcodes

and SMS passcodes for the second factor of authentication."

Dynamic Data Masking is an advanced security feature in Snowflake that allows for the selective masking of plain-text data in table and view columns at query time. According to the official Snowflake documentation, this feature is not available in the Standard edition. It is introduced starting with the Enterprise Edition and is consequently available in all higher editions, including Business Critical and Virtual Private Snowflake (VPS). Therefore, Enterprise is the minimum edition required to implement and use Dynamic Data Masking policies.

A. Standard: The Standard edition provides core Snowflake functionality but lacks advanced security features like Dynamic Data Masking and Row-level Security.
C. Business Critical: While Business Critical edition includes Dynamic Data Masking, it is not the minimum edition required, as the feature is available in the less expensive Enterprise edition.
D. Virtual Private Snowflake (VPS): VPS is the highest-tier edition and includes all features of Business Critical, including Dynamic Data Masking, but it is not the minimum requirement.

1. Snowflake Documentation: Summary of Features by Edition.

Reference: In the table under the "Security and Data Protection" section

the row for "Dynamic Data Masking" is checked for Enterprise

Business Critical

and VPS editions

but not for the Standard edition. This explicitly shows Enterprise as the minimum requirement.

Link: https://docs.snowflake.com/en/user-guide/intro-editions#summary-of-features-by-edition

2. Snowflake Documentation: Introduction to Dynamic Data Masking.

Reference: The first paragraph of this documentation page explicitly states: "Dynamic Data Masking is an Enterprise Edition (or higher) feature."

Link: https://docs.snowflake.com/en/user-guide/security-column-ddm-intro

Defining a named file format is a recommended practice when unloading data. A named file format is a reusable, schema-level object that encapsulates all the necessary format-specific information (e.g., TYPE, COMPRESSION, FIELDDELIMITER). Using a named file format promotes consistency across multiple unload operations, simplifies the COPY INTO command by referencing the object instead of listing options inline, and improves overall manageability and governance of data pipelines. This approach decouples the file format definition from the data movement logic, making the process more modular and easier to maintain.

A. Set SINGLE = true for larger files This is incorrect. Setting SINGLE = true forces the unload operation into a single thread, which severely degrades performance, especially for larger files. The recommended practice for large datasets is to unload in parallel to multiple files.
B. Use OBJECTCONSTRUCT ( ) when using Parquet This is incorrect. Parquet is a columnar format. Using OBJECTCONSTRUCT() would combine all columns into a single VARIANT object, negating the benefits of Parquet's columnar structure and query performance.
C. Avoid the use of the CAST function This is incorrect. The CAST function and other transformation functions are frequently necessary during unloading to format data, convert data types, or restructure the output to meet the requirements of the target system. ---

1. Correct Answer (D): Snowflake Documentation

"Preparing to Unload Data". This section outlines the prerequisite steps for unloading

which includes creating named file formats. It states

"Create a named file format that describes the format of your data files... You can also specify file format options in the COPY INTO command." Creating the object beforehand is presented as a standard

reusable approach.

Source: Snowflake Documentation > Data Loading & Unloading > Unloading Data > Preparing to Unload Data.

2. Incorrect Option (A): Snowflake Documentation

"COPY INTO ". The description for the SINGLE copy option explicitly warns against its use for large data sets. It states

"Note that the unload operation will be slower [with SINGLE = TRUE]. For the best performance

unload to multiple files."

Source: Snowflake Documentation > SQL Reference > DML Commands > COPY INTO > Optional Parameters > SINGLE = TRUE | FALSE.

3. Incorrect Option (B): Snowflake Documentation

"Unloading data into a Parquet file". The examples show selecting individual columns to be mapped to columns in the Parquet file

preserving the columnar structure. There is no recommendation to use OBJECTCONSTRUCT()

which would be counter-intuitive for this format.

Source: Snowflake Documentation > Data Loading & Unloading > Unloading Data > Unloading data into a Parquet file.

4. Incorrect Option (C): Snowflake Documentation

"Data Unloading Considerations". This page discusses transforming data during an unload. It provides examples of using functions within the SELECT statement of the COPY command to format the data

which often involves CAST or similar functions (e.g.

TOCHAR

TOVARCHAR).

Source: Snowflake Documentation > Data Loading & Unloading > Unloading Data > Data Unloading Considerations > Transforming Data During an Unload.

Snowflake calculates storage costs based on the average daily storage of compressed data. This includes not only active data but also data retained for recovery purposes. When data is deleted or modified, it first enters the Time Travel retention period, and storage costs continue to accrue. After the Time Travel period ends, the data enters a non-configurable 7-day Fail-safe period. Storage costs are still charged to the user's account during this Fail-safe period. The user's responsibility for the storage cost ends only when the data is permanently purged from the system, which occurs after the Fail-safe period expires.

A. Data expires from Time Travel: After data expires from Time Travel, it enters the Fail-safe period, and the user is still responsible for the storage costs.
C. Data is deleted from a table: Deleting data simply marks it for removal and moves it into the Time Travel retention period; storage costs continue to apply.
D. Data is truncated from a table: Truncating a table also moves the historical data into Time Travel, and the user remains responsible for its storage cost.

1. Snowflake Documentation. (2024). Understanding Data Storage Costs. In "Introduction to Warehouses". See the section "Data Storage Costs". It explicitly states: "Storage costs are calculated monthly based on the average number of on-disk bytes stored each day for: ... Data maintained in Time Travel. ... Data maintained in Fail-safe."

2. Snowflake Documentation. (2024). Time Travel & Fail-safe. In "Using Snowflake". See the section "Fail-safe". It describes Fail-safe as the period after Time Travel, confirming the data lifecycle and the continuation of storage.

The ACCESSHISTORY view in the ACCOUNTUSAGE schema is specifically designed for compliance auditing. It logs every instance of data being read through a query and every data modification operation (e.g., INSERT, UPDATE, DELETE, MERGE). This view provides a detailed record of which user accessed which specific tables and columns, and when the access occurred. This level of granularity is essential for auditors to track access to sensitive data, verify that data handling policies are being followed, and meet regulatory requirements.

B. COPYHISTORY: This view tracks the history of data loaded into tables using the COPY INTO command, not user access to the data for querying or analysis.
C. QUERYHISTORY: While this view logs all executed queries, it is less direct for auditing data access. ACCESSHISTORY is derived from QUERYHISTORY but is purpose-built to simplify tracking which data objects were accessed.
D. ROW ACCESS POLICIES: This is a security feature (a schema-level object) used to control which rows are visible in a query result, not a view for auditing historical access events.

1. Snowflake Documentation: ACCESSHISTORY View.

Section: ACCESSHISTORY View

Content: "This Account Usage view can be used to query what data was accessed

when it was accessed

and how. The common use cases for this view include: ... Regulatory and compliance auditing: Determine which users have been accessing sensitive data." This directly confirms the view's primary purpose for compliance auditing.

2. Snowflake Documentation: Introduction to Access Control.

Section: Auditing User Activity and Login

Content: "Snowflake provides a rich set of views in the ACCOUNTUSAGE schema to help with auditing. ... For example

the ACCESSHISTORY view provides a history of which data was read." This positions ACCESSHISTORY as a key auditing tool.

3. Snowflake Documentation: QUERYHISTORY View.

Section: QUERYHISTORY View

Content: "The ACCESSHISTORY view provides a better user experience for data auditing use cases than the QUERYHISTORY view." This explicitly states that ACCESSHISTORY is the superior and more appropriate tool for this specific task.