How Many Questions Of COF-C02 Free Practice Test

NEW QUESTION 1

Which file function gives a user or application access to download unstructured data from a Snowflake stage?

• A. BUILD_SCOPED_FILE_URL
• B. BUILD_STAGE_FILE_URL
• C. GET_PRESIGNED_URL
• D. GET STAGE LOCATION

Answer: C

Explanation:
The function that provides access to download unstructured data from a Snowflake stage is:
✑ C. GET_PRESIGNED_URL: This function generates a presigned URL for a single file within a stage. The generated URL can be used to directly access or download the file without needing to go through Snowflake. This is particularly useful for unstructured data such as images, videos, or large text files, where direct access via a URL is needed outside of the Snowflake environment.
Example usage: SELECTGET_PRESIGNED_URL('stage_name','file_path');
This function simplifies the process of securely sharing or accessing files stored in Snowflake stages with external systems or users.
References:
✑ Snowflake Documentation: GET_PRESIGNED_URL Function at Snowflake Documentation

NEW QUESTION 2

Which columns are part of the result set of the Snowflake LATERAL FLATTEN command? (Choose two.)

• A. CONTENT
• B. PATH
• C. BYTE_SIZE
• D. INDEX
• E. DATATYPE

Answer: BD

Explanation:
The LATERAL FLATTEN command in Snowflake produces a result set that includes several columns, among which PATH and INDEX are includedPATH indicates the path to the element within a data structure that needs to be flattened, and INDEX represents the index of the element if it is an array2.

NEW QUESTION 3

What operations can be performed while loading a simple CSV file into a Snowflake table using the COPY INTO command? (Select TWO).

• A. Performing aggregate calculations
• B. Reordering the columns
• C. Grouping by operations
• D. Converting the datatypes
• E. Selecting the first few rows

Answer: BD

Explanation:
When loading a simple CSV file into a Snowflake table using the COPY INTO command, you can perform various transformations and adjustments on the data as part of the loading process. Specifically, you can:
✑ B. Reorder the columns: Specify the order of columns in the COPY INTO command to match the structure of the target table if it differs from the order of columns in the source CSV file.
✑ D. Convert the datatypes: Explicitly convert the datatypes of the data being loaded to match the datatypes of the columns in the target table. This can benecessary when the source data's format does not match the target table's expected datatype.
References:
✑ Snowflake Documentation: Using the COPY INTO Command for Data Loading

NEW QUESTION 4

A company's security audit requires generating a report listing all Snowflake logins (e.g.. date and user) within the last 90 days. Which of the following statements will return the required information?

• A. SELECT LAST_SUCCESS_LOGIN, LOGIN_NAME FROM ACCOUNT_USAGE.USERS;
• B. SELECT EVENT_TIMESTAMP, USER_NAMEFROM table(information_schema.login_history_by_user())
• C. SELECT EVENT_TIMESTAMP, USER_NAME FROM ACCOUNT_USAGE.ACCESS_HISTORY;
• D. SELECT EVENT_TIMESTAMP, USER_NAME FROM ACCOUNT_USAGE.LOGIN_HISTORY;

Answer: D

Explanation:
To generate a report listing all Snowflake logins within the last 90 days, the ACCOUNT_USAGE.LOGIN_HISTORY view should be used. This view provides information about login attempts, including successful and unsuccessful logins, and is suitable for security audits4.

NEW QUESTION 5

Which access control entity in Snowflake can be created as part of a hierarchy within an account?

• A. Securable object
• B. Role
• C. Privilege
• D. User

Answer: B

Explanation:
In Snowflake, a role is an access control entity that can be created as part of a hierarchy within an account. Roles are used to grant and manage privileges in a structured and scalable manner.
✑ Understanding Roles:
✑ Role Hierarchy:
✑ Creating Roles:
✑ Example Usage: CREATE ROLE role1; CREATE ROLE role2;
GRANT ROLE role1 TO role2;
✑ uk.co.certification.simulator.questionpool.PList@546f4af7
✑ Benefits:
References:
✑ Snowflake Documentation: Access Control in Snowflake
✑ Snowflake Documentation: Creating and Managing Roles

NEW QUESTION 6

What features does Snowflake Time Travel enable?

• A. Querying data-related objects that were created within the past 365 days
• B. Restoring data-related objects that have been deleted within the past 90 days
• C. Conducting point-in-time analysis for Bl reporting
• D. Analyzing data usage/manipulation over all periods of time

Answer: BC

Explanation:
Snowflake Time Travel is a powerful feature that allows users to access historical data within a defined period. It enables two key capabilities:
✑ B. Restoring data-related objects that have been deleted within the past 90 days:
Time Travel can be used to restore tables, schemas, and databases that have been accidentally or intentionally deleted within the Time Travel retention period.
✑ C. Conducting point-in-time analysis for BI reporting: It allows users to query
historical data as it appeared at a specific point in time within the Time Travel retention period, which is crucial for business intelligence and reporting purposes.
While Time Travel does allow querying of past data, it is limited to the retention period set for the Snowflake account, which is typically 1 day for standard accounts and can be extended up to 90 days for enterprise accounts. It does not enable querying or restoring objects created or deleted beyond the retention period, nor does it provide analysis over all periods of time.
References:
✑ Snowflake Documentation on Time Travel
✑ SnowPro® Core Certification Study Guide

NEW QUESTION 7

For non-materialized views, what column in Information Schema and Account Usage identifies whether a view is secure or not?

• A. CHECK_OPTION
• B. IS_SECURE
• C. IS_UPDATEABLE
• D. TABLE_NAME

Answer: B

Explanation:
In the Information Schema and Account Usage, the column that identifies whether a view is secure or not is IS_SECURE2.

NEW QUESTION 8

Why would a Snowflake user decide to use a materialized view instead of a regular view?

• A. The base tables do not change frequently.
• B. The results of the view change often.
• C. The query is not resource intensive.
• D. The query results are not used frequently.

Answer: A

Explanation:
A Snowflake user would decide to use a materialized view instead of a regular view primarily when the base tables do not change frequently. Materialized views store the result of the view query and update it as the underlying data changes, making them ideal for situations where the data is relatively static and query performance is critical. By precomputing and storing the query results, materialized views can significantly reduce query execution times for complex aggregations, joins, and calculations.
References:
✑ Snowflake Documentation: Materialized Views

NEW QUESTION 9

What is it called when a customer managed key is combined with a Snowflake managed key to create a composite key for encryption?

• A. Hierarchical key model
• B. Client-side encryption
• C. Tri-secret secure encryption
• D. Key pair authentication

Answer: C

Explanation:
Tri-secret secure encryption is a security model employed by Snowflake that involves combining a customer-managed key with a Snowflake-managed key to create a composite key for encrypting data. This model enhances data security by requiring both the customer-managed key and the Snowflake-managed key to decrypt data, thus ensuring that neither party can access the data independently. It represents a balanced approach to key management, leveraging both customer control and Snowflake's managed services for robust data encryption.
References:
✑ Snowflake Documentation: Encryption and Key Management

NEW QUESTION 10

What is the default access of a securable object until other access is granted?

• A. No access
• B. Read access
• C. Write access
• D. Full access

Answer: A

Explanation:
In Snowflake, the default access level for any securable object (such as a table, view, or schema) is "No access" until explicit access is granted. This means that when an object is created, only the owner of the object and roles with the necessary privileges can access it. Other users or roles will not have any form of access to the object until it is explicitly granted.
This design adheres to the principle of least privilege, ensuring that access to data is tightly controlled and that users and roles only have the access necessary for their functions. To grant access, the owner of the object or a role with the GRANT option can use the GRANT statement to provide specific privileges to other users or roles.
For example, to grant SELECT access on a table to a specific role, you would use a command similar to:
GRANTSELECTONTABLEmy_tableTOROLE my_role;
Reference: Snowflake Documentation on Access Control (https://docs.snowflake.com/en/user-guide/security-access-control-overview.html)

NEW QUESTION 11

Which transformation is supported by a COPY INTO <table> command?

• A. Filter using a where clause
• B. Filter using a limit keyword
• C. Cast using a SELECT statement
• D. Order using an ORDER BY clause

Answer: C

Explanation:
The COPY INTO <table> command in Snowflake supports transformations such as casting using a SELECT statement. This allows for the transformation of data types as the data is being loaded into the table, which can be particularly useful when the data types in the source files do not match the data types in the target table

NEW QUESTION 12

Which Snowflake objects can be shared with other Snowflake accounts? (Choose three.)

• A. Schemas
• B. Roles
• C. Secure Views
• D. Stored Procedures
• E. Tables
• F. Secure User-Defined Functions (UDFs)

Answer: ACF

Explanation:
In Snowflake, you can share several types of objects with other Snowflake accounts. These include schemas, secure views, and secure user-defined functions (UDFs). Sharing these objects allows for collaboration and data access across different Snowflake accounts while maintaining security and governance controls4.

NEW QUESTION 13

What are the main differences between the account usage views and the information schema views? (Select TWO).

• A. No active warehouse to needed to query account usage views but one is needed to query information schema views.
• B. Account usage views do not contain data about tables but information schema views do.
• C. Account issue views contain dropped objects but information schema views do not.
• D. Data retention for account usage views is 1 year but is 7 days to 6 months forinformation schema views, depending on the view.
• E. Information schema views are read-only but account usage views are not.

Answer: CD

Explanation:
The account usage views in Snowflake provide historical usage data about the Snowflake account, and they retain this data for a period of up to 1 year. These views include information about dropped objects, enabling audit and tracking activities. On the other hand, information schema views provide metadata about database objects currently in use, such as tables and views, but do not include dropped objects. The retention of data in information schema views varies, but it is generally shorter than the retention for account usage views, ranging from 7 daysto a maximum of 6 months, depending on the specific view.References:Snowflake Documentation on Account Usage and Information Schema

NEW QUESTION 14

To use the overwrite option on insert, which privilege must be granted to the role?

• A. truncate
• B. DELETE
• C. UPDATE
• D. SELECT

Answer: B

Explanation:
To use the overwrite option on insert in Snowflake, theDELETEprivilege must be granted to the role. This is because overwriting data during an insert operation implicitly involves deleting the existing data before inserting the new data.
✑ Understanding the Overwrite Option:The overwrite option (INSERT OVERWRITE) allows you to replace existing data in a table with new data. This operation is particularly useful for batch-loading scenarios where the entire dataset needs to be refreshed.
✑ Why DELETE Privilege is Required:Since the overwrite operation involves removing existing rows in the table, the executing role must have theDELETEprivilege to carry out both the deletion of old data and the insertion of new data.
✑ Granting DELETE Privilege:
sqlCopy code GRANTDELETEONTABLEmy_tableTOROLE my_role;
Reference:For additional details on inserting data with the overwrite option and the required privileges, consult the Snowflake documentation on data loading: https://docs.snowflake.com/en/sql-reference/sql/insert.html

NEW QUESTION 15

Which data types can be used in a Snowflake table that holds semi-structured data? (Select TWO).

• A. ARRAY
• B. BINARY
• C. TEXT
• D. VARIANT
• E. VARCHAK

Answer: AD

Explanation:
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 arelational schema first.References:Snowflake Documentation on Semi-Structured Data Types

NEW QUESTION 16

A JSON file, that contains lots of dates and arrays, needs to be processed in Snowflake. The user wants to ensure optimal performance while querying the data.
How can this be achieved?

• A. Flatten the data and store it in structured data types in a flattened tabl
• B. Query the table.
• C. Store the data in a table with a variant data typ
• D. Query the table.
• E. Store the data in a table with a vai : ant data type and include STRIP_NULL_VALUES while loading the tabl
• F. Query the table.
• G. Store the data in an external stage and create views on top of i
• H. Query the views.

Answer: B

Explanation:
Storing JSON data in a table with a VARIANT data type is optimal for querying because it allows Snowflake to leverage its semi-structured data capabilities. This approach enables efficient storage and querying without the need for flattening the data, which can be performance-intensive1.

NEW QUESTION 17

A user needs to create a materialized view in the schema MYDB.MYSCHEMA. Which statements will provide this access?

• A. GRANT ROLE MYROLE TO USER USER1;CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO ROLE MYROLE;
• B. GRANT ROLE MYROLE TO USER USER1;CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER USER1;
• C. GRANT ROLE MYROLE TO USER USER1;CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER1;
• D. GRANT ROLE MYROLE TO USER USER1;CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE;

Answer: D

Explanation:
In Snowflake, to create a materialized view, the user must have the necessary privileges on the schema where the view will be created. These privileges are granted through roles, not directly to individual users. Therefore, the correct process is to grant the role to the user and then grant the privilege to create the materialized view to the role itself.
The statement GRANT ROLE MYROLE TO USER USER1; grants the specified role to the user, allowing them to assume that role and exercise its privileges. The subsequent statement CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO
MYROLE; grants the privilege to create a materialized view within the specified schema to the role MYROLE. Any user who has been granted MYROLE can then create materialized views in MYDB.MYSCHEMA.
References:
✑ Snowflake Documentation on Roles
✑ Snowflake Documentation on Materialized Views

NEW QUESTION 18

How does the search optimization service help Snowflake users improve query performance?

• A. It scans the micro-partitions based on the joins used in the queries and scans only join columns.
• B. II maintains a persistent data structure that keeps track of the values of the table's columns m each of its micro-partitions.
• C. It scans the local disk cache to avoid scans on the tables used in the Query.
• D. It keeps track of running queries and their results and saves those extra scans on the table.

Answer: B

Explanation:
The search optimization service in Snowflake enhances query performance by maintaining a persistent data structure. This structure indexes the values of table columns across micro-partitions, allowing Snowflake to quickly identify which micro- partitions contain relevant data for a query. By efficiently narrowing down the search space, this service reduces the amount of data scanned during query execution, leading to faster response times and more efficient use of resources.References:Snowflake Documentation on Search Optimization Service

NEW QUESTION 19

Snowflake's hierarchical key mode includes which keys? (Select TWO).

• A. Account master keys
• B. Database master keys
• C. File keys
• D. Secure view keys
• E. Schema master keys

Answer: AC

Explanation:
Snowflake??s hierarchical key model includes several levels of keys, where Account master keys and File keys are part of this hierarchy. Account master keys are used to encrypt all the data within an account, while File keys are used to encrypt individual files within the database2.

NEW QUESTION 20

How does the Access_History view enhance overall data governance pertaining to read and write operations? (Select TWO).

• A. Shows how the accessed data was moved from the source lo the target objects
• B. Provides a unified picture of what data was accessed and when it was accessed
• C. Protects sensitive data from unauthorized access while allowing authorized users to access it at query runtime
• D. Identifies columns with personal information and tags them so masking policies can be applied to protect sensitive data
• E. Determines whether a given row in a table can be accessed by the user by filtering the data based on a given policy

Answer: BE

Explanation:
TheACCESS_HISTORYview in Snowflake is a powerful tool for enhancing data governance, especially concerning monitoring and auditing data access patterns for both read and write operations. The key ways in whichACCESS_HISTORYenhances overall data governance are:
✑ B. Provides a unified picture of what data was accessed and when it was accessed:This view logs details about query executions, including the objects (tables, views) accessed and the timestamps of these accesses. It's instrumental in auditing and compliance scenarios, where understanding the access patterns to sensitive data is critical.
✑ E. Determines whether a given row in a table can be accessed by the user by filtering the data based on a given policy:While this option is a bit of a misinterpretation of whatACCESS_HISTORYdirectly offers, it indirectly supports data governance by providing the information necessary to analyze access patterns. This analysis can then inform policy decisions, such as implementing Row-Level Security (RLS) to restrict access to specific rows based on user roles or attributes.
ACCESS_HISTORYdoes not automatically apply data masking or tag columns with personal information. However, the insights derived from analyzingACCESS_HISTORY can be used to identify sensitive data and inform the application of masking policies or other security measures.
References:
✑ Snowflake Documentation onACCESS_HISTORY: Access History

NEW QUESTION 21
......