Ace the Snowflake Core Certification Exam with ITPrepare's COF-C02 Exam Questions V10.02

Ace the Snowflake Core Certification Exam with ITPrepare’s COF-C02 Exam Questions V10.02

Eleanor2023-02-08T01:44:48+00:00

Are you still looking for valid exam resources to prepare for the Snowflake Core Certification Exam (COF-C02)? Then you can come to ITPrepare to get its latest COF-C02 exam questions V10.02. There are 263 practice questions and answers in ITPrepare’s COF-C02 exam dumps. With these Q&As, you will have a comprehensive understanding of all the exam objectives and contents. We understand the importance of staying current in the ever-evolving world of technology and data management. That’s why our COF-C02 exam questions are regularly updated to reflect the latest industry standards and advancements. Our aim is to provide you with the most up-to-date and comprehensive information to help you succeed in your Snowflake Core Certification Exam.

Snowflake Core Certification Exam COF-C02 Free Dumps Demo

Page 1 of 10

1. Which of the following Snowflake capabilities are available in all Snowflake editions? (Select TWO)

Customer-managed encryption keys through Tri-Secret Secure

Automatic encryption of all data

Up to 90 days of data recovery through Time Travel

Object-level access control

Column-level security to apply data masking policies to tables and views

2. Which command is used to unload data from a Snowflake table into a file in a stage?

COPY INTO

GET

WRITE

EXTRACT INTO

3. 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

4. What transformations are supported in a CREATE PIPE ... AS COPY ... FROM (....)statement? (Select TWO.)

Data can be filtered by an optional where clause

Incoming data can be joined with other tables

Columns can be reordered

Columns can be omitted

Row level access can be defined

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

STRUCT

OBJECT

BINARY

ARRAY

CLOB

6. How often are encryption keys automatically rotated by Snowflake?

30 Days

60 Days

90 Days

365 Days

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

True

False

8. What are the default Time Travel and Fail-safe retention periods for transient tables?

Time Travel - 1 day. Fail-safe - 1 day

Time Travel - 0 days. Fail-safe - 1 day

Time Travel - 1 day. Fail-safe - 0 days

Transient tables are retained in neither Fail-safe nor Time Travel

9. Which of the following are benefits of micro-partitioning? (Select TWO)

Micro-partitions cannot overlap in their range of values

Micro-partitions are immutable objects that support the use of Time Travel.

Micro-partitions can reduce the amount of I/O from object storage to virtual warehouses

Rows are automatically stored in sorted order within micro-partitions

Micro-partitions can be defined on a schema-by-schema basis

10. Which of the following commands cannot be used within a reader account?

CREATE SHARE

ALTER WAREHOUSE

DROP ROLE

SHOW SCHEMAS

DESCRBE TABLE

Page 2 of 10

11. When reviewing a query profile, what is a symptom that a query is too large to fit into the memory?

A single join node uses more than 50% of the query time

Partitions scanned is equal to partitions total

An AggregateOperacor node is present

The query is spilling to remote storage

12. How long is Snowpipe data load history retained?

As configured in the create pipe settings

Until the pipe is dropped

64 days

14 days

13. Which of the following describes how multiple Snowflake accounts in a single organization relate to various cloud providers?

Each Snowflake account can be hosted in a different cloud vendor and region.

Each Snowflake account must be hosted in a different cloud vendor and region

All Snowflake accounts must be hosted in the same cloud vendor and region

Each Snowflake account can be hosted in a different cloud vendor, but must be in the same region.

14. 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.

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

VARIANT

ARRAY

VARCHAR

XML

BLOB

16. In which use cases does Snowflake apply egress charges?

Data sharing within a specific region

Query result retrieval

Database replication

Loading data into Snowflake

17. What are two ways to create and manage Data Shares in Snowflake? (Choose two.)

Via the Snowflake Web Interface (Ul)

Via the data_share=true parameter

Via SQL commands

Via Virtual Warehouses

18. 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

19. Which of the following conditions must be met in order to return results from the results cache? (Select TWO).

The user has the appropriate privileges on the objects associated with the query

Micro-partitions have been reclustered since the query was last run

The new query is run using the same virtual warehouse as the previous query

The query includes a User Defined Function (UDF)

The query has been run within 24 hours of the previously-run query

20. A user has an application that writes a new Tile to a cloud storage location every 5 minutes.

What would be the MOST efficient way to get the files into Snowflake?

Create a task that runs a copy into operation from an external stage every 5 minutes

Create a task that puts the files in an internal stage and automate the data loading wizard

Create a task that runs a GET operation to intermittently check for new files

Set up cloud provider notifications on the Tile location and use Snowpipe with auto-ingest

Page 3 of 10

21. 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

22. When reviewing the load for a warehouse using the load monitoring chart, the chart indicates that a high volume of Queries are always queuing in the warehouse

According to recommended best practice, what should be done to reduce the Queue volume? (Select TWO).

Use multi-clustered warehousing to scale out warehouse capacity.

Scale up the warehouse size to allow Queries to execute faster.

Stop and start the warehouse to clear the queued queries

Migrate some queries to a new warehouse to reduce load

Limit user access to the warehouse so fewer queries are run against it.

23. 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

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

Masking policy

Stored procedure

Task

External table

25. A marketing co-worker has requested the ability to change a warehouse size on their medium virtual warehouse called mktg__WH.

Which of the following statements will accommodate this request?

ALLOW RESIZE ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;

GRANT MODIFY ON WAREHOUSE MKTG WH TO ROLE MARKETING;

GRANT MODIFY ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;

GRANT OPERATE ON WAREHOUSE MKTG WH TO ROLE MARKETING;

26. What happens to the underlying table data when a CLUSTER BY clause is added to a Snowflake table?

Data is hashed by the cluster key to facilitate fast searches for common data values

Larger micro-partitions are created for common data values to reduce the number of partitions that must be scanned

Smaller micro-partitions are created for common data values to allow for more parallelism

Data may be colocated by the cluster key within the micro-partitions to improve pruning performance

27. Which services does the Snowflake Cloud Services layer manage? (Select TWO).

Compute resources

Query execution

Authentication

Data storage

Metadata

28. During periods of warehouse contention which parameter controls the maximum length of time a warehouse will hold a query for processing?

STATEMENT_TIMEOUT__IN__SECONDS

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

MAX_CONCURRENCY__LEVEL

QUERY_TIMEOUT_IN_SECONDS

29. Which cache type is used to cache data output from SQL queries?

Metadata cache

Result cache

Remote cache

Local file cache

30. Which stage type can be altered and dropped?

Database stage

External stage

Table stage

User stage

Page 4 of 10

31. What Snowflake features allow virtual warehouses to handle high concurrency workloads? (Select TWO)

The ability to scale up warehouses

The use of warehouse auto scaling

The ability to resize warehouses

Use of multi-clustered warehouses

The use of warehouse indexing

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

Cluster keys

Time Travel

Fail-safe

Cloning

33. A user is loading JSON documents composed of a huge array containing multiple records into Snowflake. The user enables the strip__outer_array file format option

What does the STRIP_OUTER_ARRAY file format do?

It removes the last element of the outer array.

It removes the outer array structure and loads the records into separate table rows,

It removes the trailing spaces in the last element of the outer array and loads the records into separate table columns

It removes the NULL elements from the JSON object eliminating invalid data and enables the ability to load the records

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

Variant

Object

Geometry

Geography

35. 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

36. Which of the following are valid methods for authenticating users for access into Snowflake? (Select THREE)

SCIM

Federated authentication

TLS 1.2

Key-pair authentication

OAuth

OCSP authentication

37. What is a responsibility of Snowflake's virtual warehouses?

Infrastructure management

Metadata management

Query execution

Query parsing and optimization

Management of the storage layer

38. A user needs to create a materialized view in the schema MYDB.MYSCHEMA.

Which statements will provide this access?

GRANT ROLE MYROLE TO USER USER1; CREATE MATERIALIZED VIEW ON SCHEMA MYD

MYSCHEMA TO ROLE MYROLE;

GRANT ROLE MYROLE TO USER USER1; CREATE MATERIALIZED VIEW ON SCHEMA MYD

MYSCHEMA TO USER USER1;

GRANT ROLE MYROLE TO USER USER1; CREATE MATERIALIZED VIEW ON SCHEMA MYD

MYSCHEMA TO USER1;

GRANT ROLE MYROLE TO USER USER1; CREATE MATERIALIZED VIEW ON SCHEMA MYD

MYSCHEMA TO MYROLE;

39. What is a machine learning and data science partner within the Snowflake Partner Ecosystem?

Informatica

Power Bl

Adobe

Data Robot

40. The fail-safe retention period is how many days?

1 day

7 days

45 days

90 days

Page 5 of 10

41. 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

42. Which of the following are best practice recommendations that should be considered when loading data into Snowflake? (Select TWO).

Load files that are approximately 25 MB or smaller.

Remove all dates and timestamps.

Load files that are approximately 100-250 MB (or larger)

Avoid using embedded characters such as commas for numeric data types

Remove semi-structured data types

43. Which account__usage views are used to evaluate the details of dynamic data masking? (Select TWO)

ROLES

POLICY_REFERENCES

QUERY_HISTORY

RESOURCE_MONIT ORS

ACCESS_HISTORY

44. Which Snowflake object enables loading data from files as soon as they are available in a cloud storage location?

Pipe

External stage

Task

Stream

45. Which of the following is a valid source for an external stage when the Snowflake account is located on Microsoft Azure?

An FTP server with TLS encryption

An HTTPS server with WebDAV

A Google Cloud storage bucket

A Windows server file share on Azure

46. 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

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

CSV

JSON

Parquet

XML

48. Which command can be used to stage local files from which Snowflake interface?

SnowSQL

Snowflake classic web interface (Ul)

Snowsight

.NET driver

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

True

False

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

Compute layer

Storage layer

Cloud infrastructure layer

Cloud services layer

Page 6 of 10

51. What are ways to create and manage data shares in Snowflake? (Select TWO)

Through the Snowflake web interface (Ul)

Through the DATA_SHARE=TRUE parameter

Through SQL commands

Through the enable__share=true parameter

Using the CREATE SHARE AS SELECT * TABLE command

52. How would you determine the size of the virtual warehouse used for a task?

Root task may be executed concurrently (i.e. multiple instances), it is recommended to leave some margins in the execution window to avoid missing instances of execution

Querying (select) the size of the stream content would help determine the warehouse size. For example, if querying large stream content, use a larger warehouse size

If using the stored procedure to execute multiple SQL statements, it's best to test run the stored procedure separately to size the compute resource first

Since task infrastructure is based on running the task body on schedule, it's recommended to configure the virtual warehouse for automatic concurrency handling using Multi-cluster warehouse (MCW) to match the task schedule

53. What happens when a cloned table is replicated to a secondary database? (Select TWO)

A read-only copy of the cloned tables is stored.

The replication will not be successful.

The physical data is replicated

Additional costs for storage are charged to a secondary account

Metadata pointers to cloned tables are replicated

54. What Snowflake role must be granted for a user to create and manage accounts?

ACCOUNTADMIN

ORGADMIN

SECURITYADMIN

SYSADMIN

55. Which of the following compute resources or features are managed by Snowflake? (Select TWO).

Execute a COPY command

Updating data

Snowpipe

AUTOMATIC__CLUSTERING

Scaling up a warehouse

56. Which of the following describes external functions in Snowflake?

They are a type of User-defined Function (UDF).

They contain their own SQL code.

They call code that is stored inside of Snowflake.

They can return multiple rows for each row received

57. Which of the following can be executed/called with Snowpipe?

A User Defined Function (UDF)

A stored procedure

A single copy_into statement

A single insert__into statement

58. A company strongly encourages all Snowflake users to self-enroll in Snowflake's default Multi-Factor Authentication (MFA) service to provide increased login security for users connecting to Snowflake.

Which application will the Snowflake users need to install on their devices in order to connect with MFA?

Okta Verify

Duo Mobile

Microsoft Authenticator

Google Authenticator

59. What is the purpose of an External Function?

To call code that executes outside of Snowflake

To run a function in another Snowflake database

To share data in Snowflake with external parties

To ingest data from on-premises data sources

60. A user unloaded a Snowflake table called mytable to an internal stage called mystage.

Which command can be used to view the list of files that has been uploaded to the staged?

list @mytable;

list @%raytable;

list @ %m.ystage;

list @mystage;

Page 7 of 10

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

Clustering

Indexing

Fragmenting

Using INDEX__HINTS

62. In the query profiler view for a query, which components represent areas that can be used to help optimize query performance? (Select TWO)

Bytes scanned

Bytes sent over the network

Number of partitions scanned

Percentage scanned from cache

External bytes scanned

63. 1.Will data cached in a warehouse be lost when the warehouse is resized?

Possibly, if the warehouse is resized to a smaller size and the cache no longer fits.

Yes. because the compute resource is replaced in its entirety with a new compute resource.

No. because the size of the cache is independent from the warehouse size

Yes. became the new compute resource will no longer have access to the cache encryption key

64. Which semi-structured file formats are supported when unloading data from a table? (Select TWO).

ORC

XML

Avro

Parquet

JSON

65. 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

66. What SQL command would be used to view all roles that were granted to user.1?

show grants to user USER1;

show grants of user USER1;

describe user USER1;

show grants on user USER1;

67. 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

68. What is the minimum Snowflake edition required to create a materialized view?

Standard Edition

Enterprise Edition

Business Critical Edition

Virtual Private Snowflake Edition

69. True or False: A 4X-Large Warehouse may, at times, take longer to provision than a X-Small Warehouse.

True

False

70. What is a best practice after creating a custom role?

Create the custom role using the SYSADMIN role.

Assign the custom role to the SYSADMIN role

Assign the custom role to the PUBLIC role

Add_CUSTOM to all custom role names

Page 8 of 10

71. Which copy INTO command outputs the data into one file?

SINGLE=TRUE

MAX_FILE_NUMBER=1

FILE_NUMBER=1

MULTIPLE=FAISE

72. A sales table FCT_SALES has 100 million records.

The following Query was executed

SELECT COUNT (1) FROM FCT__SALES;

How did Snowflake fulfill this query?

Query against the result set cache

Query against a virtual warehouse cache

Query against the most-recently created micro-partition

Query against the metadata excite

73. A user has unloaded data from Snowflake to a stage

Which SQL command should be used to validate which data was loaded into the stage?

list @file__stage

show @file__stage

view @file__stage

verify @file__stage

74. A virtual warehouse's auto-suspend and auto-resume settings apply to which of the following?

The primary cluster in the virtual warehouse

The entire virtual warehouse

The database in which the virtual warehouse resides

The Queries currently being run on the virtual warehouse

75. Which of the following Snowflake objects can be shared using a secure share? (Select TWO).

Materialized views

Sequences

Procedures

Tables

Secure User Defined Functions (UDFs)

76. True or False: Loading data into Snowflake requires that source data files be no larger than 16MB.

True

False

77. What is a key feature of Snowflake architecture?

Zero-copy cloning creates a mirror copy of a database that updates with the original

Software updates are automatically applied on a quarterly basis

Snowflake eliminates resource contention with its virtual warehouse implementation

Multi-cluster warehouses allow users to run a query that spans across multiple clusters

Snowflake automatically sorts DATE columns during ingest for fast retrieval by date

78. What is the default character set used when loading CSV files into Snowflake?

UTF-8

UTF-16

ISO S859-1

ANSI_X3.A

79. Which command can be used to load data into an internal stage?

LOAD

copy

GET

PUT

80. Which is the MINIMUM required Snowflake edition that a user must have if they want to use AWS/Azure Privatelink or Google Cloud Private Service Connect?

Standard

Premium

Enterprise

Business Critical

Page 9 of 10

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

Internal stages

Incremental backups

Time Travel

Zero-copy clones

Fail-safe

82. Which of the following describes how clustering keys work in Snowflake?

Clustering keys update the micro-partitions in place with a full sort, and impact the DML operations.

Clustering keys sort the designated columns over time, without blocking DML operations

Clustering keys create a distributed, parallel data structure of pointers to a table's rows and columns

Clustering keys establish a hashed key on each node of a virtual warehouse to optimize joins at run-time

83. 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?

SELECT LAST_SUCCESS_LOGIN, LOGIN_NAME FROM ACCOUNT_USAG

USERS;

SELECT EVENT_TIMESTAMP, USER_NAME FROM table(information_schema.login_history_by_user())

SELECT EVENT_TIMESTAMP, USER_NAME FROM ACCOUNT_USAG

ACCESS_HISTORY;

SELECT EVENT_TIMESTAMP, USER_NAME FROM ACCOUNT_USAG

LOGIN_HISTORY;

84. A developer is granted ownership of a table that has a masking policy. The developer's role is not able to see the masked data. Will the developer be able to modify the table to read the masked data?

Yes, because a table owner has full control and can unset masking policies.

Yes, because masking policies only apply to cloned tables.

No, because masking policies must always reference specific access roles.

No, because ownership of a table does not include the ability to change masking policies

85. What happens when an external or an internal stage is dropped? (Select TWO).

When dropping an external stage, the files are not removed and only the stage is dropped

When dropping an external stage, both the stage and the files within the stage are removed

When dropping an internal stage, the files are deleted with the stage and the files are recoverable

When dropping an internal stage, the files are deleted with the stage and the files are not recoverable

When dropping an internal stage, only selected files are deleted with the stage and are not recoverable

86. What types of data listings are available in the Snowflake Data Marketplace? (Choose two.)

Reader

Consumer

Vendor

Standard

Personalized

87. Which Snowflake partner specializes in data catalog solutions?

Alation

DataRobot

dbt

Tableau

88. Which Snowflake objects track DML changes made to tables, like inserts, updates, and deletes?

Pipes

Streams

Tasks

Procedures

89. True or False: Reader Accounts are able to extract data from shared data objects for use outside of Snowflake.

True

False

90. User-level network policies can be created by which of the following roles? (Select TWO).

ROLEADMIN

ACCOUNTADMIN

SYSADMIN

SECURITYADMIN

USERADMIN

Page 10 of 10

91. What happens when a virtual warehouse is resized?

When increasing the size of an active warehouse the compute resource for all running and queued queries on the warehouse are affected

When reducing the size of a warehouse the compute resources are removed only when they are no longer being used to execute any current statements.

The warehouse will be suspended while the new compute resource is provisioned and will resume automatically once provisioning is complete.

Users who are trying to use the warehouse will receive an error message until the resizing is complete

92. What is the recommended file sizing for data loading using Snowpipe?

A compressed file size greater than 100 MB, and up to 250 MB

A compressed file size greater than 100 GB, and up to 250 GB

A compressed file size greater than 10 MB, and up to 100 MB

A compressed file size greater than 1 GB, and up to 2 GB

93. What features does Snowflake Time Travel enable?

Querying data-related objects that were created within the past 365 days

Restoring data-related objects that have been deleted within the past 90 days

Conducting point-in-time analysis for Bl reporting

Analyzing data usage/manipulation over all periods of time

94. What is the MOST performant file format for loading data in Snowflake?

CSV (Unzipped)

Parquet

CSV (Gzipped)

ORC

95. When unloading to a stage, which of the following is a recommended practice or approach?

Set SINGLE: = true for larger files

Use OBJECT_CONSTRUCT ( * ) when using Parquet

Avoid the use of the CAST function

Define an individual file format

96. Which statement about billing applies to Snowflake credits?

Credits are billed per-minute with a 60-minute minimum

Credits are used to pay for cloud data storage usage

Credits are consumed based on the number of credits billed for each hour that a warehouse runs

Credits are consumed based on the warehouse size and the time the warehouse is running

97. The Information Schema and Account Usage Share provide storage information for which of the following objects? (Choose three.)

Users

Tables

Databases

Internal Stages

98. What feature can be used to reorganize a very large table on one or more columns?

Micro-partitions

Clustering keys

Key partitions

Clustered partitions

99. 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

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

True

False

Ace the Snowflake Core Certification Exam with ITPrepare’s COF-C02 Exam Questions V10.02