[COF-C02] SnowPro Core Certification Mock Exam - 1

Question 1

Type of function that can operate on a subset of rows within the set of input rows.

Answer 1

Window Function

Question 2

If you recreate a pipe using CREATE OR REPLACE PIPE command. What does happen to load history if the Snowpipe gets recreated?

Answer 2

When you recreate a pipe, if you do CREATE OR REPLACE PIPE, that load history is reset to empty, so Snowflake doesn’t know which files we’ve already loaded.

Question 3

Are Shares available for replication?

Answer 3

Yes

Database and share replication are available in all editions, including the Standard edition.

Replication of all other objects is only available for Business Critical Edition (or higher).

Question 4

If a user is logged in to Snowflake in a federated environment and IdP times out, the current Snowflake sessions are affected?

Answer 4

No

After a specified period of time (defined by the IdP), a user’s session in the IdP automatically times out, but this does not affect their Snowflake sessions. Any Snowflake sessions that are active at the time remain open and do not require re-authentication. However, to initiate any new Snowflake sessions, the user must log into the IdP again.

Question 5

CREATE DIRECTORY TABLE is the command necessary to create a Directory Table

Answer 5

No

A Directory table is not a separate database object; it stores a catalog of staged files in cloud storage.

Roles with sufficient privileges can query a directory table to retrieve file URLs to access the staged files and other metadata.

A directory table can be added explicitly to a stage when the stage is created (using CREATE STAGE) or later (using ALTER STAGE) with supplying directoryTableParams. directoryTableParams (for internal stages) ::= [ DIRECTORY = ( ENABLE = { TRUE | FALSE } [ REFRESH_ON_CREATE = { TRUE | FALSE } ] ) ] ENABLE = TRUE | FALSE Specifies whether to add a directory table to the stage. When the value is TRUE, a directory table is created with the stage.

Question 6

Monica has successfully created a task with the 5 minutes schedule. It has been 30 minutes, but the task did not run. What could be the reason?

Answer 6

Monica should run the ALTER TASK command to RESUME the task

The first time we create the TASK, we need to run the ALTER TASK command to RESUME the task.

Question 7

The major benefits of defining Clustering Keys

Answer 7

Defining clustering keys for very large tables (in the multi-terabyte range) helps optimize table maintenance and query performance. Small tables are not a good candidate for clustering.

Question 8

An account-level resource monitor overrides the resource monitor assignment for individual warehouses. (True/False)

Answer 8

False

An account-level resource monitor does not override resource monitor assignments for individual warehouses.

If either the account resource monitor or the warehouse resource monitor reaches its defined threshold and a suspend action has been defined, the warehouse is suspended.

Question 9

If an account has federated authentication enabled. Can Snowflake admins still maintain user IDs and passwords in Snowflake?

Answer 9

Yes

With federated authentication enabled on an account, Snowflake still allows maintaining and using Snowflake user credentials (login name and password).

Question 10

Time Travel can be disabled for an account by ACCOUNTADMIN. (True/False)

Answer 10

False

Time Travel cannot be disabled for an account.

A user with the ACCOUNTADMIN role can set DATA_RETENTION_TIME_IN_DAYS to 0 at the account level, which means that all databases (and subsequently all schemas and tables) created in the account have no retention period by default; however, this default can be overridden at any time for any database, schema, or table

Question 11

Which languages does Snowflake support for writing UDFs (User-Defined Functions)?

Answer 11

• Python
• JS
• Java
• SQL
• Scala

Question 12

What options are available for data transformation while loading data into a table using the COPY command?

Answer 12

Snowflake supports transforming data while loading it into a table using the COPY command. Options include:

• Column reordering
• Column omission
• Casts
• Truncating text strings that exceed the target column length

Question 13

What is the purpose of option LOAD_UNCERTAIN_FILES

Answer 13

Boolean that specifies to load files for which the load status is unknown. The COPY command skips these files by default.

The load status is unknown if all of the following conditions are true:

• The file’s LAST_MODIFIED date (i.e. date when the file was staged) is older than 64 days.
• The initial set of data was loaded into the table more than 64 days earlier.
• If the file was already loaded successfully into the table, this event occurred more than 64 days earlier.

To force the COPY command to load all files regardless of whether the load status is known, use the FORCE option instead.

Question 14

What are the supported file formats for data unloading in Snowflake?

Answer 14

Currently, only JSON and Parquet file formats are supported for data unloading.

Other formats are not supported at this time. However,other formats are supported for data loading.

Question 15

Dynamic Data Masking is supported in which version

Answer 15

Dynamic Data Masking features require Enterprise Edition (or higher).

Question 16

Which systems function can help find the overlap depth of a table’s micro-partitions?

Answer 16

SYSTEM$CLUSTERING_DEPTH

SYSTEM$CLUSTERING_INFORMATION

Question 17

In what situations should you consider User-Managed Tasks over Serverless Tasks?

Answer 17

User-managed Tasks is recommended when you can fully utilize a single warehouse by scheduling multiple concurrent tasks to take advantage of available compute resources. Also, recommended when adherence to the schedule interval is less critical.

Question 18

In what situations should you consider Serverless Tasks over User-Managed Tasks ?

Answer 18

Serverless Tasks is recommended when you cannot fully utilize a warehouse because too few tasks run concurrently or they run to completion quickly (in less than 1 minute).

Also, recommended when adherence to the schedule interval is critical.

Question 19

The data objects stored by Snowflake are not directly visible nor accessible by customers; they are only accessible through SQL query operations run using Snowflake. (True/False)

Answer 19

True

The data objects stored by Snowflake are not directly visible nor accessible by customers; they are only accessible through SQL query operations run using Snowflake.

Question 20

Snowflake Query history page allows you to view the details of all the queries executed in the last 31 days

(True/False)

Answer 20

False

Snowflake Query history page allows you to view the details of all the queries executed in the last 14 days.

Question 21

At what frequency does Snowflake rotate the object keys?

Answer 21

30 Days

All Snowflake-managed keys are automatically rotated by Snowflake when they are more than 30 days old. Active keys are retired, and new keys are created.

Question 22

Which SQL command determines whether a network policy is set on the account or for a specific user

Answer 22

SHOW PARAMETERS

Question 23

Which database objects can be shared using the Snowflake Secure Data Sharing feature?

Answer 23

The following Snowflake database objects can be shared:

• Tables
• External tables
• Secure views
• Secure materialized views
• Secure UDFs

Question 24

A user cannot view the result set from a query that another user executed except for the ACCOUNTADMIN role.

(True / False)

Answer 24

False

A user cannot view the result set from a query that another user executed. Even a user with the ACCOUNTADMIN role cannot view the results for a query run by another user.

Question 25

Only the user who generated the scoped URL can use the URL to access the referenced file.

(True/False)

Answer 25

True

Only the user who generated the scoped URL can use the URL to access the referenced file. I case of File URL, any role that has sufficient privileges on the stage can access the file.

Question 26

Which algorithm does Snowflake use to estimate the approximate number of distinct values in a data set?

Answer 26

HyperLogLog

Question 27

What is the purpose of VALIDATION_MODE in the COPY INTO <table> command?

Answer 27

VALIDATION_MODE is used to validate the load file for errors instead of loading it into the specified table.; i.e., the COPY command tests the files for errors but does not load them.