COF-R02 SnowPro Core Recertification Exam Questions

test2023-01-17T07:37:00+00:00

The COF-R02 SnowPro Core Recertification exam is available for candidates with an expiring SnowPro Core Certification. By passing the SnowPro Core Recertification exam, a candidate’s SnowPro Core Certification’s status will extend an additional 2 years from the date of passing the recertification exam. Candidates must hold a valid SnowPro Core Certification at the time they take the recertification exam to be eligible for the SnowPro Core Recertification exam.

Our COF-R02 SnowPro Core Recertification Exam Questions are designed to be a realistic representation of the actual exam, and is tailored to the latest exam objectives. It provides you with a chance to identify your strengths and weaknesses, and to focus your study efforts accordingly. By taking our COF-R02 SnowPro Core Recertification Exam Questions, you will be well-prepared to pass the COF-R02 SnowPro Core Recertification exam on your first try.

Page 1 of 9

1. What is the SNOWFLAKE.ACCOUNT_USAGE view that contains information about which objects were read by queries within the last 365 days (1 year)?

VIEWS_HISTORY

OBJECT_HISTORY

ACCESS_HISTORY

LOGIN_HISTORY

2. When is the result set cache no longer available? (Select TWO)

When another warehouse is used to execute the query

When another user executes the query

When the underlying data has changed

When the warehouse used to execute the query is suspended

When it has been 24 hours since the last query

3. Which of the following are best practices for loading data into Snowflake? (Choose three.)

Aim to produce data files that are between 100 MB and 250 MB in size, compressed.

Load data from files in a cloud storage service in a different region or cloud platform from the service or region containing the Snowflake account, to save on cost.

Enclose fields that contain delimiter characters in single or double quotes.

Split large files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse.

When planning which warehouse(s) to use for data loading, start with the largest warehouse possible.

Partition the staged data into large folders with random paths, allowing Snowflake to determine the best way to load each file.

4. True or False: It is possible to query data from an Internal or named External stage without loading the data into Snowflake.

True

False

5. Which feature is only available in the Enterprise or higher editions of Snowflake?

Column-level security

SOC 2 type II certification

Multi-factor Authentication (MFA)

Object-level access control

6. True or False: The user has to specify which cluster a query will run on in multi-clustering Warehouse.

True

False

7. True or False: Bulk unloading of data from Snowflake supports the use of a SELECT statement.

True

False

8. True or False: Reader Accounts incur no additional Compute costs to the Data Provider since they are simply reading the shared data without making changes.

True

False

9. Which privilege is required for a role to be able to resume a suspended warehouse if auto-resume is not enabled?

USAGE

OPERATE

MONITOR

MODIFY

10. A user has 10 files in a stage containing new customer data. The ingest operation completes with no errors, using the following command:

COPY INTO my__table FROM @my__stage;

The next day the user adds 10 files to the stage so that now the stage contains a mixture of new customer data and updates to the previous data. The user did not remove the 10 original files.

If the user runs the same copy into command what will happen?

All data from all of the files on the stage will be appended to the table

Only data about new customers from the new files will be appended to the table

The operation will fail with the error uncertain files in stage.

All data from only the newly-added files will be appended to the table.

Page 2 of 9

11. True or False: A Virtual Warehouse can be resized while suspended.

True

False

12. What is true about sharing data in Snowflake? (Choose two.)

The Data Consumer pays for data storage as well as for data computing.

The shared data is copied into the Data Consumer account, so the Consumer can modify it without impacting the base data of the Provider.

A Snowflake account can both provide and consume shared data.

The Provider is charged for compute resources used by the Data Consumer to query the shared data.

The Data Consumer pays only for compute resources to query the shared data.

13. The Query History in the Snowflake Web Interface (UI) is kept for approximately:

60 minutes

24 hours

14 days

30 days

1 year

14. Which of the following roles is recommended to be used to create and manage users and roles?

SYSADMIN

SECURITYADMIN

PUBLIC

ACCOUNTADMIN

15. Which Snowflake technique can be used to improve the performance of a query?

Clustering

Indexing

Fragmenting

Using INDEX__HINTS

16. In which scenarios would a user have to pay Cloud Services costs? (Select TWO).

Compute Credits = 50 Credits Cloud Services = 10

Compute Credits = 80 Credits Cloud Services = 5

Compute Credits = 10 Credits Cloud Services = 9

Compute Credits = 120 Credits Cloud Services = 10

Compute Credits = 200 Credits Cloud Services = 26

17. A company needs to read multiple terabytes of data for an initial load as part of a Snowflake migration. The company can control the number and size of CSV extract files.

How does Snowflake recommend maximizing the load performance?

Use auto-ingest Snowpipes to load large files in a serverless model.

Produce the largest files possible, reducing the overall number of files to process.

Produce a larger number of smaller files and process the ingestion with size Small virtual warehouses.

Use an external tool to issue batched row-by-row inserts within BEGIN TRANSACTION and COMMIT commands.

18. Which snowflake objects will incur both storage and cloud compute charges? (Select TWO)

Materialized view

Sequence

Secure view

Transient table

Clustered table

19. Which parameter can be used to instruct a COPY command to verify data files instead of loading them into a specified table?

STRIP_NULL_VALUES

SKIP_BYTE_ORDER_MARK

REPLACE_INVALID_CHARACTERS

VALIDATION_MODE

20. What occurs when a pipe is recreated using the CREATE OR REPLACE PIPE command?

The Pipe load history is reset to empty.

The REFRESH command is executed.

The stage will be purged.

The destination table is truncated.

Page 3 of 9

21. Which Snowflake feature will allow small volumes of data to continuously load into Snowflake and will incrementally make the data available for analysis?

COPY INTO

CREATE PIPE

INSERT INTO

TABLE STREAM

22. True or False: When you create a custom role, it is a best practice to immediately grant that role to ACCOUNTADMIN.

True

False

23. How can a user change which columns are referenced in a view?

Modify the columns in the underlying table

Use the ALTER VIEW command to update the view

Recreate the view with the required changes

Materialize the view to perform the changes

24. By default, which Snowflake role is required to create a share?

ORGADMIN

SECURITYADMIN

SHAREADMIN

ACCOUNTADMIN

25. What do the terms scale up and scale out refer to in Snowflake? (Choose two.)

Scaling out adds clusters of the same size to a virtual warehouse to handle more concurrent queries.

Scaling out adds clusters of varying sizes to a virtual warehouse.

Scaling out adds additional database servers to an existing running cluster to handle more concurrent queries.

Snowflake recommends using both scaling up and scaling out to handle more concurrent queries.

Scaling up resizes a virtual warehouse so it can handle more complex workloads.

Scaling up adds additional database servers to an existing running cluster to handle larger workloads.

26. Which query profile statistics help determine if efficient pruning is occurring? (Choose two.)

Bytes sent over network

Percentage scanned from cache

Partitions total

Bytes spilled to local storage

Partitions scanned

27. The number of queries that a Virtual Warehouse can concurrently process is determined by: Choose 2 answers

The complexity of each query

The CONCURRENT_QUERY_UMIT parameter set on the Snowflake account

The size of the data required for each query

The tool that s executing the query

28. Which of the following features, associated with Continuous Data Protection (CDP), require additional Snowflake-provided data storage? (Choose two.)

Tri-Secret Secure

Time Travel

Fail-safe

Data encryption

External stages

29. What are the three things customers want most from their enterprise data warehouse solution? Choose 3 answers

On-premise availability

Simplicity

Open source based

Concurrency

Performance

30. Query parsing and compilation occurs in which architecture layer of the Snowflake Cloud Data Platform?

Cloud services layer

Compute layer

Storage layer

Cloud agnostic layer

Page 4 of 9

31. Which data types does Snowflake support when querying semi-structured data? (Select TWO)

VARIANT

ARRAY

VARCHAR

XML

BLOB

32. Which stage type can be altered and dropped?

Database stage

External stage

Table stage

User stage

33. True or False: It is possible for a user to run a query against the query result cache without requiring an active Warehouse.

True

False

34. Credit charges for Snowflake virtual warehouses are calculated based on which of the following considerations? (Choose two.)

The number of queries executed

The number of active users assigned to the warehouse

The size of the virtual warehouse

The length of time the warehouse is running

The duration of the queries that are executed

35. Which of the following indicates that it may be appropriate to use a clustering key for a table? (Select TWO).

The table contains a column that has very low cardinality

DML statements that are being issued against the table are blocked

The table has a small number of micro-partitions

Queries on the table are running slower than expected

The clustering depth for the table is large

36. What is the default File Format used in the COPY command if one is not specified?

CSV

JSON

Parquet

XML

37. Which of the following describes a Snowflake stored procedure?

They can be created as secure and hide the underlying metadata from the user.

They can only access tables from a single database.

They can contain only a single SQL statement.

They can be created to run with a caller's rights or an owner's rights.

38. Which statement describes how Snowflake supports reader accounts?

A reader account can consume data from the provider account that created it and combine it with its own data.

A consumer needs to become a licensed Snowflake customer as data sharing is only supported between Snowflake accounts.

The users in a reader account can query data that has been shared with the reader account and can perform DML tasks.

The SHOW MANAGED ACCOUNTS command will view all the reader accounts that have been created for an account.

39. True or False: An active warehouse is required to run a COPY INTO statement.

True

False

40. Which data types are supported by Snowflake when using semi-structured data? (Choose two.)

VARIANT

VARRAY

STRUCT

ARRAY

QUEUE

Page 5 of 9

41. Which SQL commands, when committed, will consume a stream and advance the stream offset? (Choose two.)

UPDATE TABLE FROM STREAM

SELECT FROM STREAM

INSERT INTO TABLE SELECT FROM STREAM

ALTER TABLE AS SELECT FROM STREAM

BEGIN COMMIT

42. True or False: The COPY command must specify a File Format in order to execute.

True

False

43. What tasks can be completed using the copy command? (Select TWO)

Columns can be aggregated

Columns can be joined with an existing table

Columns can be reordered

Columns can be omitted

Data can be loaded without the need to spin up a virtual warehouse

44. Which of the following statements describe features of Snowflake data caching? (Choose two.)

When a virtual warehouse is suspended, the data cache is saved on the remote storage layer.

When the data cache is full, the least-recently used data will be cleared to make room.

A user can only access their own queries from the query result cache.

A user must set USE_METADATA_CACHE to TRUE to use the metadata cache in queries.

The RESULT_SCAN table function can access and filter the contents of the query result cache.

45. True or False: A single database can exist in more than one Snowflake account.

True

False

46. Which of the following significantly improves the performance of selective point lookup queries on a table?

Clustering

Materialized Views

Zero-copy Cloning

Search Optimization Service

47. When loading data into Snowflake, how should the data be organized?

Into single files with 100-250 MB of compressed data per file

Into single files with 1-100 MB of compressed data per file

Into files of maximum size of 1 GB of compressed data per file

Into files of maximum size of 4 GB of compressed data per file

48. True or False: Snowflake’s data warehouse was built from the ground up for the cloud in lieu of using an existing database or a platform, like Hadoop, as a base.

True

False

49. Which privilege must be granted to a share to allow secure views the ability to reference data in multiple databases?

CREATE_SHARE on the account

SHARE on databases and schemas

SELECT on tables used by the secure view

REFERENCE_USAGE on databases

50. Where would a Snowflake user find information about query activity from 90 days ago?

account__usage . query history view

account__usage.query__history__archive View

information__schema . cruery_history view

information__schema - query history_by_ses s i on view

Page 6 of 9

51. True or False: A Snowflake account is charged for data stored in both Internal and External Stages.

True

False

52. Which Snowflake feature is used for both querying and restoring data?

Cluster keys

Time Travel

Fail-safe

Cloning

53. Which data type can be used to store geospatial data in Snowflake?

Variant

Object

Geometry

Geography

54. True or False: Fail-safe can be disabled within a Snowflake account.

True

False

55. True or False: During data unloading, only JSON and CSV files can be compressed.

True

False

56. What is a limitation of a Materialized View?

A Materialized View cannot support any aggregate functions

A Materialized View can only reference up to two tables

A Materialized View cannot be joined with other tables

A Materialized View cannot be defined with a JOIN

57. Which of the following can be used when unloading data from Snowflake? (Choose two.)

When unloading semi-structured data, it is recommended that the STRIP_OUTER_ARRAY option be used.

Use the ENCODING file format option to change the encoding from the default UTF-8.

The OBJECT_CONSTRUCT function can be used to convert relational data to semi-structured data.

By using the SINGLE = TRUE parameter, a single file up to 5 GB in size can be exported to the storage layer.

Use the PARSE_JSON function to ensure structured data will be unloaded into the VARIANT data type.

58. Which of the following statement is true of Snowflake?

It was built specifically for the cloud

it was built as an on-premises solution and then potted to the cloud

It was designed as a hybrid database to allow customers to store data either on premises or in the cloud

It was built for Hadoop architecture

It's based on an Oracle Architecture

59. What file formats does Snowflake support for loading semi-structured data? (Choose three.)

TSV

JSON

PDF

Avro

Parquet

JPEG

60. Which is true of Snowflake network policies? A Snowflake network policy: (Choose two.)

Is available to all Snowflake Editions

Is only available to customers with Business Critical Edition

Restricts or enables access to specific IP addresses

Is activated using an “ALTER DATABASE” command

Page 7 of 9

61. Query compilation occurs in which architecture layer of the Snowflake Cloud Data Platform?

Compute layer

Storage layer

Cloud infrastructure layer

Cloud services layer

62. What affects whether the query results cache can be used?

If the query contains a deterministic function

If the virtual warehouse has been suspended

If the referenced data in the table has changed

If multiple users are using the same virtual warehouse

63. What data is stored in the Snowflake storage layer? (Select TWO).

Snowflake parameters

Micro-partitions

Query history

Persisted query results

Standard and secure view results

64. A user has unloaded data from a Snowflake table to an external stage.

Which command can be used to verify if data has been uploaded to the external stage named my_stage?

view @my_stage

list @my_stage

show @my_stage

display @my_stage

65. What are the correct parameters for time travel and fail-safe in the Snowflake Enterprise Edition?

Default Time Travel Retention is set to 0 days. Maximum Time Travel Retention is 30 days. Fail Safe retention time is 1 day.

Default Time Travel Retention is set to 1 day. Maximum Time Travel Retention is 365 days. Fail Safe retention time is 7 days.

Default Time Travel Retention is set to 0 days. Maximum Time Travel Retention is 90 days. Fail Safe retention time is 7 days.

Default Time Travel Retention is set to 1 day. Maximum Time Travel Retention is 90 days. Fail Safe retention time is 7 days.

Default Time Travel Retention is set to 7 days. Maximum Time Travel Retention is 1 day. Fail Safe retention time is 90 days.

Default Time Travel Retention is set to 90 days. Maximum Time Travel Retention is 7 days. Fail Safe retention time is 356 days.

66. A running virtual warehouse is suspended.

What is the MINIMUM amount of time that the warehouse will incur charges for when it is restarted?

1 second

60 seconds

5 minutes

60 minutes

67. If a Small Warehouse is made up of 2 servers/cluster, how many servers/cluster make up a Medium Warehouse?

128

68. Which Snowflake function will interpret an input string as a JSON document, and produce a VARIANT value?

parse_json()

json_extract_path_text()

object_construct()

flatten

69. When scaling out by adding clusters to a multi-cluster warehouse, you are primarily scaling for improved:

Concurrency

Performance

70. Which of the following objects can be shared through secure data sharing?

Masking policy

Stored procedure

Task

External table

Page 8 of 9

71. Which of the following statements are true about Schemas in Snowflake? (Choose two.)

A Schema may contain one or more Databases

A Database may contain one or more Schemas

A Schema is a logical grouping of Database Objects

Each Schema is contained within a Warehouse

72. What are the responsibilities of Snowflake's Cloud Service layer? (Choose three.)

Authentication

Resource management

Virtual warehouse caching

Query parsing and optimization

Query execution

Physical storage of micro-partitions

73. What type of query benefits the MOST from search optimization?

A query that uses only disjunction (i.e., OR) predicates

A query that includes analytical expressions

A query that uses equality predicates or predicates that use IN

A query that filters on semi-structured data types

74. What COPY INTO SQL command should be used to unload data into multiple files?

SINGLE=TRUE

MULTIPLE=TRUE

MULTIPLE=FALSE

SINGLE=FALSE

75. Which of the following Snowflake features provide continuous data protection automatically? (Select TWO).

Internal stages

Incremental backups

Time Travel

Zero-copy clones

Fail-safe

76. A company needs to allow some users to see Personally Identifiable Information (PII) while limiting other users from seeing the full value of the PII.

Which Snowflake feature will support this?

Row access policies

Data masking policies

Data encryption

Role based access control

77. Which statements reflect key functionalities of a Snowflake Data Exchange? (Choose two.)

If an account is enrolled with a Data Exchange, it will lose its access to the Snowflake Marketplace.

A Data Exchange allows groups of accounts to share data privately among the accounts.

A Data Exchange allows accounts to share data with third, non-Snowflake parties.

Data Exchange functionality is available by default in accounts using the Enterprise edition or higher.

The sharing of data in a Data Exchange is bidirectional. An account can be a provider for some datasets and a consumer for others.

78. In an auto-scaling multi-cluster virtual warehouse with the setting SCALING_POLICY = ECONOMY enabled, when is another cluster started?

When the system has enough load for 2 minutes

When the system has enough load for 6 minutes

When the system has enough load for 8 minutes

When the system has enough load for 10 minutes

79. True or False: It is possible to load data into Snowflake without creating a named File Format object.

True

False

80. What are value types that a VARIANT column can store? (Select TWO)

STRUCT

OBJECT

BINARY

ARRAY

CLOB

Page 9 of 9

81. What can be used to view warehouse usage over time? (Select Two).

The load HISTORY view

The Query history view

The show warehouses command

The WAREHOUSE_METERING__HISTORY View

The billing and usage tab in the Snowflake web Ul

82. Which of the following objects is not covered by Time Travel?

Tables

Schemas

Databases

Stages

83. Why would a customer size a Virtual Warehouse from an X-Small to a Medium?

To accommodate more queries

To accommodate more users

To accommodate fluctuations in workload

To accommodate a more complex workload